TIL: Sqlite Foreign Key Support (with Go)

Using foreign key constraints with SQL databases is very common. You can use those constrains to model relationships between tables.

Here is an example:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) -- Must map to an artist.artistid!
);

Sqlite is a small and self-contained implementation of SQL. SQLite databases are used in production all around the world.

There is only a small pitfall with foreign key constraints using SQLite: it’s not enabled per default (for backwards compatibility).

Unfortunately, that means that you have to enable the support manually for each database connection:

sqlite> PRAGMA foreign_keys = ON;

I’ve been using SQLite for my Go web application, so let’s see how we can write a small wrapper for Go.

package database

import (
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	_ "modernc.org/sqlite"
)

// New returns a new database connection pool.
func New(dbName string) (*sqlx.DB, error) {
	db, err := sqlx.Open("sqlite", "database.sqlite")
	if err != nil {
		return nil, errors.Wrap(err, "Unable to open database")
	}
	if err = db.Ping(); err != nil {
		return nil, errors.Wrap(err, "Unable to ping database")
	}

	const q = `
	PRAGMA foreign_keys = ON;
	PRAGMA synchronous = NORMAL;
	PRAGMA journal_mode = 'WAL';
	PRAGMA cache_size = -64000;
	`
	_, err = db.Exec(q)
	if err != nil {
		return nil, errors.Wrap(err, "Unable to set pragmas")
	}

	return db, nil
}

I can now use this function in main.go and be sure that my database connection has the correct settings:

dbName := flag.String("dbName", "database.sqlite", "database name")
	flag.Parse()

db, err := database.New(*dbName)
	if err != nil {
		return errors.Wrap(err, "could not start server")
	}
	defer db.Close()

TIL: Sqlite Foreign Key Support (with Go)

Further Reading