rockyourcode: TIL: Sqlite Foreign Key Support (with Go)
Hello 👋! Thanks for subscribing.
TIL: Sqlite Foreign Key Support (with Go)
Published on: 2021-03-06
tags: TIL, SQL, 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()
Further Reading
Thank you for reading my blog posts.