Test Against SQLite in Memory in Go and Its Limits

An in-memory SQLite database is the fastest realistic database a Go test can have. There is no server to start, no port, no cleanup: sql.Open returns a database that disappears with the process. For code that issues ordinary SQL, that is a good deal. It is also easy to get wrong in four specific ways, each of which the lab below demonstrates, and it is a poor model of PostgreSQL or MySQL in ways you should know before trusting a green run.

The runs are on Go 1.25.6 with the pure Go driver modernc.org/sqlite. The alternative that runs the real production database is in testing against a real Postgres, and the containerised version of the same idea is testcontainers.

A private database per test

package main

import (
	"database/sql"
	"fmt"
	"sync/atomic"

	_ "modernc.org/sqlite"
)

const schema = `
CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
CREATE TABLE entries (
	id INTEGER PRIMARY KEY,
	account_id INTEGER NOT NULL REFERENCES accounts(id),
	cents INTEGER NOT NULL
);`

var seq atomic.Int64

// openMemory returns a private in-memory database. One connection only:
// every extra connection to ":memory:" would be a separate, empty database.
func openMemory(foreignKeys bool) (*sql.DB, error) {
	db, err := sql.Open("sqlite", fmt.Sprintf("file:t%d?mode=memory", seq.Add(1)))
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	if foreignKeys {
		if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
			return nil, err
		}
	}
	if _, err := db.Exec(schema); err != nil {
		return nil, err
	}
	return db, nil
}

func Balance(db *sql.DB, account int) (int, error) {
	var sum int
	err := db.QueryRow(`SELECT COALESCE(SUM(cents), 0) FROM entries WHERE account_id = ?`, account).Scan(&sum)
	return sum, err
}

func main() {}

openMemory builds a DSN with a counter, file:t1?mode=memory, file:t2?mode=memory, and so on. Each name is a separate database, so tests never share state. It then does three things that matter.

It caps the pool at one connection. With the default database/sql pool, a second concurrent query opens a second connection, and for a plain in-memory database each connection gets its own empty database. The symptom is "no such table" appearing in a query that ran fine a moment earlier. SetMaxOpenConns(1) makes every query use the same connection and therefore the same data. The cost is that two queries cannot overlap in one test, which is fine unless the test is about concurrency.

It switches foreign keys on. SQLite does not enforce them by default. The pragma is per connection, which is another reason to keep one connection: with a pool, a new connection would come up with enforcement off.

It creates the schema. In a project this is where your migration runs, ideally the same migration files production uses, as far as the dialect allows.

package main

import (
	"database/sql"
	"testing"
)

func mustOpen(t *testing.T, fk bool) *sql.DB {
	t.Helper()
	db, err := openMemory(fk)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { db.Close() })
	return db
}

func TestBalance(t *testing.T) {
	t.Parallel()
	db := mustOpen(t, true)
	db.Exec(`INSERT INTO accounts (id, email) VALUES (1, '[email protected]')`)
	for _, c := range []int{500, -120, 70} {
		if _, err := db.Exec(`INSERT INTO entries (account_id, cents) VALUES (1, ?)`, c); err != nil {
			t.Fatal(err)
		}
	}
	if got, err := Balance(db, 1); err != nil || got != 450 {
		t.Fatalf("got %d, %v", got, err)
	}
}

func TestIsolationBetweenTests(t *testing.T) {
	for _, name := range []string{"one", "two", "three"} {
		t.Run(name, func(t *testing.T) {
			t.Parallel()
			db := mustOpen(t, true)
			// the same id in every test: they cannot see each other
			if _, err := db.Exec(`INSERT INTO accounts (id, email) VALUES (1, '[email protected]')`); err != nil {
				t.Fatal(err)
			}
		})
	}
}

// What SQLite lets through that PostgreSQL does not.

func TestForeignKeysAreOptIn(t *testing.T) {
	off := mustOpen(t, false)
	_, err := off.Exec(`INSERT INTO entries (account_id, cents) VALUES (99, 1)`)
	t.Logf("foreign_keys off, orphan row: err=%v", err)

	on := mustOpen(t, true)
	_, err = on.Exec(`INSERT INTO entries (account_id, cents) VALUES (99, 1)`)
	t.Logf("foreign_keys on,  orphan row: err=%v", err)
}

func TestTypesAreAdvisory(t *testing.T) {
	db := mustOpen(t, true)
	db.Exec(`INSERT INTO accounts (id, email) VALUES (1, '[email protected]')`)
	_, err := db.Exec(`INSERT INTO entries (account_id, cents) VALUES (1, 'not a number')`)
	var typ string
	db.QueryRow(`SELECT typeof(cents) FROM entries`).Scan(&typ)
	t.Logf("text into an INTEGER column: err=%v, stored as %s", err, typ)
}

func TestSecondConnectionSeesNothing(t *testing.T) {
	db, _ := sql.Open("sqlite", ":memory:") // default pool, no SetMaxOpenConns(1)
	defer db.Close()
	db.SetMaxOpenConns(2)

	c1, _ := db.Conn(t.Context())
	c2, _ := db.Conn(t.Context())
	c1.ExecContext(t.Context(), `CREATE TABLE t (x int)`)
	_, err := c2.ExecContext(t.Context(), `INSERT INTO t VALUES (1)`)
	t.Logf("second connection to :memory:, table created on the first: err=%v", err)
}

TestBalance and TestIsolationBetweenTests are the ordinary kind. The second one runs three parallel subtests that each insert an account with id = 1. They pass because each has its own database, and under a shared database two of them would collide on the primary key. Parallel tests work here without any coordination, and the race detector is on in the recorded run.

What SQLite lets through

go mod tidy >/dev/null 2>&1
go test -count=1 -v -race . 2>&1 | sed -E 's/^ +[a-z_]+\.go:[0-9]+: +/    /' | grep -E '^(--- |    (foreign_keys|text into|second connection)|ok|FAIL)' | grep -vE '/(one|two|three)' | sed -E 's/ \([0-9.]+s\)//; s/^ok.*/ok/'

--- PASS: TestIsolationBetweenTests
    foreign_keys off, orphan row: err=<nil>
    foreign_keys on,  orphan row: err=constraint failed: FOREIGN KEY constraint failed (787)
--- PASS: TestForeignKeysAreOptIn
    text into an INTEGER column: err=<nil>, stored as text
--- PASS: TestTypesAreAdvisory
    second connection to :memory:, table created on the first: err=SQL logic error: no such table: t (1)
--- PASS: TestSecondConnectionSeesNothing
--- PASS: TestBalance
ok

The remaining tests are the limits, and the log lines are the evidence.

Foreign keys are opt in. With the pragma off, inserting an entry for account 99, which does not exist, returned err=<nil>. With it on, the same statement failed with constraint failed: FOREIGN KEY constraint failed (787). A test suite that forgets the pragma has silently stopped checking referential integrity, and the production database will reject the row that your tests accepted.

Column types are advisory. Declaring cents INTEGER does not prevent storing text in it. SQLite uses type affinity: it tries to convert the value, and if it cannot it stores what it was given. The test inserted 'not a number', the insert succeeded, and typeof(cents) returned text. PostgreSQL would have rejected it with an error. SQLite does offer STRICT tables, which enforce types, and using them in the test schema closes this gap, but only if production also uses them for the comparison to mean anything.

A second connection is a different database. The last test opens :memory: with a pool of two, creates a table on one connection and inserts through the other. The error is SQL logic error: no such table: t (1). This is the surprise that makes people think their migration did not run.

Everything else in the file, the unique constraint, the sum, the parallelism, behaves as you would hope.

Where the models diverge

The three failures above are the ones SQLite lets you see. There are others that a green run will hide:

• Dialect. Placeholders are ? in SQLite and $1 in PostgreSQL. Functions differ: now(), ILIKE, array and JSON operators, ON CONFLICT variants, RETURNING support and window function details.
• Types. No native uuid, numeric with exact precision, timestamptz or arrays. Times are stored as text or numbers, so timezone bugs are invisible.
• Concurrency. SQLite allows one writer at a time with file locking, and PostgreSQL uses MVCC with row locks. Tests of locking, SELECT ... FOR UPDATE, isolation levels and deadlocks say nothing about production.
• Migrations. ALTER TABLE support is narrower in SQLite, so a migration that works in one may not parse in the other.
• Query plans. An index that makes a PostgreSQL query fast says nothing about SQLite's planner, and the reverse.

If the application uses an ORM or a query builder that targets several databases, the portable subset is probably safe in both. If it uses PostgreSQL-specific features, you will spend more time translating the test schema than the database would have cost to run.

When it is the right choice

SQLite in memory is a good fit when SQL is simple and the database is incidental: a service that stores sessions, a CLI that keeps state in SQLite in production too, a repository whose interesting logic is in Go and whose queries are plain SELECT and INSERT. It also suits teams that cannot run Docker in CI. For a service whose production database is SQLite, it is exactly right, and you should test with the same file-backed settings (WAL mode, busy timeout) in at least one test.

It is the wrong choice for code that depends on PostgreSQL behaviour. There, use the real thing and pay the start-up cost once, as in the template-database approach. A reasonable split for a PostgreSQL service is fast tests of business logic against an in-memory fake behind a small interface or SQLite where the SQL is portable, and a smaller set against real PostgreSQL for every query that uses its features.

Choosing a driver

modernc.org/sqlite is a translation of SQLite to Go, so it needs no C compiler and cross-compiles. mattn/go-sqlite3 wraps the C library through cgo, which requires a toolchain on every build machine. For tests, build simplicity usually wins. Pin the version: the lab uses v1.50.0, and newer releases of both drivers raise their minimum Go version over time.

Keep the driver behind database/sql and import it only in main and in test helpers, so production code stays independent of which one you chose. The SQLite documentation on in-memory databases, foreign keys and type affinity explains each behaviour shown here, and the Go side is described in the database/sql package documentation.