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") }

<span style="color:#66d9ef">const</span> <span style="color:#a6e22e">q</span> = <span style="color:#e6db74">`

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()

March 6, 2021, 8:49 p.m.

TIL: Sqlite Foreign Key Support (with Go)

Further Reading