Importing from CSV to SQLite in Go

This is the eighth in a series of articles about writing a small reading list app in Go for personal use.

Sometimes there are operations that need to be done on the app that don’t need to be built into the frontend. Administrative process like backups, database migrations, or – the thing we’re going to do today – bulk data import.

I’ve got a CSV dump of my Goodreads books, and I want to import those into Aklatan’s db. I only need to do this once, so I don’t need to mess around with making a web form for it. It just needs a backend administrative command to perform the import as a one-off process.

By the end of this post we’ll have:

• a command that loads data from the Goodreads CSV export into akalatan.db
• knowledge of how to use raw SQL – we won’t use Gorm for this task

And we’ll do it in less than 75 lines of code.

The Input Data

The program we want to write is simple: it needs to read rows from a CSV file, extract the fields of interest, and insert rows into the sqlite database.

Here’s a sample of the data (apologies for the wide format):

Book Id,Title,Author,Author l-f,Additional Authors,ISBN,ISBN13,My Rating,Average Rating,Publisher,Binding,Number of Pages,Year Published,Original Publication Year,Date Read,Date Added,Bookshelves,Bookshelves with positions,Exclusive Shelf,My Review,Spoiler,Private Notes,Read Count,Recommended For,Recommended By,Owned Copies,Original Purchase Date,Original Purchase Location,Condition,Condition Description,BCID
400354,"Men at Arms (Discworld, #15; City Watch #2)",Terry Pratchett,"Pratchett, Terry",,"=""0061092193""","=""9780061092190""",0,4.37,HarperPrism,Mass Market Paperback,377,2003,1993,,2020/04/26,"to-read, wip-series, owned","to-read (#125), wip-series (#7), owned (#8)",to-read,,,,0,,,0,,,,,
6977769,"61 Hours (Jack Reacher, #14)",Lee Child,"Child, Lee",,"=""0385340583""","=""9780385340588""",0,4.11,Delacorte Press,Hardcover,383,2010,2010,,2021/08/05,"to-read, wip-series","to-read (#204), wip-series (#21)",to-read,,,,0,,,0,,,,,
30413199,Concurrency in Go: Tools and Techniques for Developers,Katherine Cox-Buday,"Cox-Buday, Katherine",,"=""1491941197""","=""9781491941195""",3,4.24,O'Reilly Media,Paperback,238,2017,,,2021/02/15,programming,programming (#42),read,,,,1,,,0,,,,,
16299,And Then There Were None,Agatha Christie,"Christie, Agatha",,"=""0312330871""","=""9780312330873""",4,4.27,St. Martin's Press,Paperback,264,2004,1939,,2021/01/16,,,read,,,,1,,,0,,,,,

Parsing CSV is annoying and filled with booby traps. Fortunately there’s a package in the standard library: encoding/csv.

A Note on Quality

This is a “quick & dirty” importer, representing maybe an hour’s worth of work (including some manual testing and reading the docs to remember how to use certain packages). If this was something I was going to use on an ongoing basis I’d pull code out of main() and add some unit tests.

But it’s straightforward enough that when I need to reuse it I’ll be able to understand the code and make any changes needed at that time. (And I will revisit this command after some upcoming enhancements to Aklatan that add fields to the database.)

The Code

Since this is going to be built as a standalone command, we’ll use Go’s conventional directory structure and put it in ./cmd/importer/importer.go.

Our Makefile has been ready for this all along. We just need to define CMDS at the top. A rule further below will automagically build the command and leave it in the project root directory.

PROJECT := aklatan
CMDS := importer
ALLGO := $(wildcard *.go */*.go cmd/*/*.go)
ALLHTML := $(wildcard templates/*/*.html)

In importer.go, add some imports:

package main

import (
   "database/sql"
   "encoding/csv"
   "errors"
   "flag"
   "io"
   "log"
   "os"

   _ "github.com/mattn/go-sqlite3"
)

Then let’s define some command line arguments to control the input and output files – this is quick & dirty, but we’re not barbarians who would hard code the filenames, right?

func main() {
   var dbName string
   var csvName string
   flag.StringVar(&dbName, "db", "", "SQLite database to import to")
   flag.StringVar(&csvName, "csv", "", "CSV file to import from")
   flag.Parse()
   if dbName == "" || csvName == "" {
           flag.PrintDefaults()
           return
   }

Next, open the database. Note that sql.Open doesn’t actually check that the file exists or can be written – so we call Ping to make sure we’re connected. Also note that we don’t bother with much error handling. If anything goes wrong, we just abort. After verifying the database is open, create the books table if it doesn’t already exist. (This duplicates the logic in the main app, but it’s ok for this command for now. If this was a production-grade command we’d make sure we weren’t duplicating logic.)

   db, err := sql.Open("sqlite3", dbName)
   if err != nil {
           log.Fatal(err)
   }
   defer db.Close()
   err = db.Ping()
   if err != nil {
           log.Fatalf("ping failed: %s", err)
   }
   stmt, err := db.Prepare("create table if not exists books (id integer primary key autoincrement, title text, author text)")
   if err != nil {
           log.Fatalf("prepare failed: %s", err)
   }
   _, err = stmt.Exec()
   if err != nil {
           log.Fatalf("exec failed: %s", err)
   }

Then, open the CSV file. The csv package supports a few options for reading files because CSV isn’t exactly a well-defined standard, but we don’t need anything special in this case.

       f, err := os.Open(csvName)
   if err != nil {
           log.Fatalf("open failed: %s", err)
   }
   r := csv.NewReader(f)
   // Read the header row.
   _, err = r.Read()
   if err != nil {
           log.Fatalf("missing header row(?): %s", err)
   }

And finally we’re ready to loop over the records in the CSV. We grab the title and author, and insert them into a database record.

    for {
           record, err := r.Read()
           if errors.Is(err, io.EOF) {
                   break
           }

           title := record[1]
           author := record[2]

           stmt, err = db.Prepare("insert into books(title, author) values(?, ?)")
           if err != nil {
                   log.Fatalf("insert prepare failed: %s", err)
           }

           _, err = stmt.Exec(title, author)
           if err != nil {
                   log.Fatalf("insert failed(%s): %s", title, err)
           }
   }
} // end of main()

And that’s all there is to it! Run make, grab your export from Goodreads (or use the sample csv above, or even adapt this example to any other similar service – just adjust the field numbers in the loop above), and run:

% ./importer -csv goodreads_library_export.csv -db aklatan.db
% echo 'select count(title) from books;' | sqlite3 aklatan.db
600

Excellent. Now our database is populated with 600 books. We can run aklatan and see these in the book list page.

A Potential Problem to Solve Next Week

With 600 books in the database I see log output like this when loading the book list:

[GIN] 2022/04/19 - 19:50:41 | 200 |    10.89955ms |   127.0.0.1 | GET      "/books/"
[GIN] 2022/04/19 - 19:50:41 | 200 |      37.156µs |   127.0.0.1 | GET      "/static/css/styles.css"

Ten milliseconds isn’t the end of the world, but it’s worth noting that when there are only 3 books in the database, the time is more like 600 microseconds. With complex records and much bigger database that may become an actual problem. Also, even though 10ms isn’t huge, we want to shave down as much as we can to avoid creating user-perceptible delays – because in a real app, both the external network and possibly other components in our deployment may add latency.

The strategy we’ll use to bring that time down by a factor of about 10 will be pagination, which we’ll explore next week.

• gin