Get Started with SQLBoiler [SQLite]

Over the last year I've been learning the Go programming language and overall it's been a pretty positive experience- except when it comes to quickly and easily interfacing with databases. While Go does include a 'sql' package targeted at low level interaction with database backends, out of the box you are not provided with something higher level (think rails ActiveRecord). For higher level abstraction there are many community supported packages available, which makes it time consuming to try/test each one and see if it fits your needs. Today I'm writing about SQLBoiler, specifically about working with its SQLite integration.

Update (2018-10-25): Now includes instructions for building sqlboiler as well as sqlboiler-sqlite3 and ensuring both of them are in the same directory or system path before usage.

References

• SQLBoiler [github.com/volatiletech]
• SQLite integration [github.com/volatiletech]
• SQLite Support [github.com/volatiletech]
• Ann: SQLBoiler v3 - Generate an ORM [groups.google.com/golang-nuts]
• Go database/sql tutorial [go-database-sql.org]

What is SQLBoiler? And Why Would I Care about SQLite?

SQLBoiler has a 'database first' approach as opposed to a 'code first' approach to ORM. You can read more about that on their github page. For me, SQLBoiler makes it super easy to interact with existing databases using its code generator that creates an ORM based on the schema. It's pretty slick once you get used to reversing the way you think about interacting with databases.

One 'catch' to SQLBoiler is that out of the box it does not support SQLite in its main repository. Instead, SQLite support has been relegated to its own repository with very little easy to read/use documentation. I care about SQLite since it makes it super easy for me to prototype locally. A lot of smaller projects I work on won't ever need to migrate to a bigger database, so I took some time to work up a sample getting started example for SQLBoiler v3's SQLite support.

Getting started with SQLBoiler SQLite

First, you'll need to get the packages and dependencies:

# Get the stuff
go get -u -t github.com/volatiletech/sqlboiler
go get -u github.com/volatiletech/sqlboiler-sqlite3
go get -u github.com/mattn/go-sqlite3

Next up is building the sqlite code generator plugin. I built it and copied it to my project directory:

# build the code generator
cd github.com/volatiletech/sqlboiler-sqlite3
go build
cp sqlboiler-sqlite3 <TO YOUR PROJECT DIR!>

Now, you'll need to build sqlboiler itself and copy it to your project directory (this needs access to the sqlboiler-sqlite3 binary, which should either be in the same directory or in the same PATH):

# build the code generator
cd github.com/volatiletech/sqlboiler
go build
cp sqlboiler <TO YOUR PROJECT DIR!>

Then you will need to configure a sqlboiler.toml file that configures access to the SQLite database you will be connecting to:

# Configure sqlboiler to read from your sqlite database
cd <TO YOUR PROJECT DIR!>
vim sqlboiler.toml
    [sqlite3]
    dbname = "boiler.sqlite3"

# Generate the models/ORM for working with your database
./sqlboiler sqlite3

Sample code in go that pulls from the example database I setup:

package main

import (
    "context"
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
    "fmt"
    "github.com/volatiletech/sqlboiler/boil"
    "scratches/sqlboiler-test/models"
)

func main(){
    // Get a handle to the SQLite database, using mattn/go-sqlite3
    db, err := sql.Open("sqlite3", "./test.sqlite3")
    if err != nil{
        panic(err)
    }

    // Configure SQLBoiler to use the sqlite database
    boil.SetDB(db)

    // Need to set a context for purposes I don't understand yet
    ctx := context.Background()     // Dark voodoo magic, https://golang.org/pkg/context/#Background

    // This pulls 'all' of the books from the books table in the sample database (see schema below!)
    books, _ := models.Books().All(ctx, db)
    for _, book := range books{
        fmt.Println(book.Title)
    }
}

Schema for the SQLite sample database I used above:

CREATE TABLE `books` (
    `UID`   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    `title` TEXT,
    `author`    TEXT,
    `subject`   TEXT
);

So far it seems to work pretty well- I am looking for an easy way to generate database tables from code now to avoid having to hand craft them. :)