GORM Testing With SQLite in Memory and Testcontainers

Tests that touch the database are the slowest, flakiest tests in most Go projects, and the shortcut everyone reaches for is SQLite in memory. It is a good shortcut and a dangerous one. The tests are fast and the CI needs nothing, and they pass on code that fails in production, because SQLite is a different database. This article runs the same store code against SQLite and against PostgreSQL 18.6, shows three places where they disagree, and sets up the approach that gives real behaviour at nearly the same speed: a schema per test.

It follows the GORM tutorial and the dialect comparison in GORM with MySQL and SQLite. The general techniques are in testing with SQLite in memory, transaction rollback in tests and testing with pgx.

The code under test

A Store with Team and Member models. A member belongs to a team with OnDelete:RESTRICT. FindByEmail uses ilike, a PostgreSQL operator, because the production database is PostgreSQL and the search should ignore case.

package main

import "gorm.io/gorm"

type Team struct {
	ID   uint
	Name string `gorm:"uniqueIndex"`
}

type Member struct {
	ID     uint
	TeamID uint
	Team   Team `gorm:"constraint:OnDelete:RESTRICT"`
	Email  string
}

type Store struct{ db *gorm.DB }

func NewStore(db *gorm.DB) (*Store, error) {
	return &Store{db}, db.AutoMigrate(&Team{}, &Member{})
}

func (s *Store) AddMember(teamID uint, email string) error {
	return s.db.Create(&Member{TeamID: teamID, Email: email}).Error
}

func (s *Store) FindByEmail(substr string) ([]Member, error) {
	var ms []Member
	err := s.db.Where("email ilike ?", "%"+substr+"%").Find(&ms).Error
	return ms, err
}

func (s *Store) DeleteTeam(id uint) error { return s.db.Delete(&Team{}, id).Error }

Two ways to get a database

package main

import (
	"fmt"
	"sync/atomic"
	"testing"

	"github.com/glebarez/sqlite"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var n atomic.Int64

// sqliteStore gives every test its own in-memory database.
func sqliteStore(t *testing.T, foreignKeys bool) *Store {
	t.Helper()
	dsn := fmt.Sprintf("file:t%d?mode=memory&cache=shared", n.Add(1))
	if foreignKeys {
		dsn += "&_pragma=foreign_keys(1)"
	}
	db, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{Logger: logger.Discard, TranslateError: true})
	if err != nil {
		t.Fatal(err)
	}
	s, err := NewStore(db)
	if err != nil {
		t.Fatal(err)
	}
	return s
}

// pgStore gives every test its own schema on a shared server, so tests can run in parallel.
func pgStore(t *testing.T) *Store {
	t.Helper()
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	if err != nil {
		t.Fatal(err)
	}
	schema := fmt.Sprintf("l0725_t%d", n.Add(1))
	admin.Exec("create schema " + schema)
	t.Cleanup(func() {
		admin.Exec("drop schema " + schema + " cascade")
		sqlDB, _ := admin.DB()
		sqlDB.Close()
	})
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), &gorm.Config{Logger: logger.Discard, TranslateError: true})
	if err != nil {
		t.Fatal(err)
	}
	s, err := NewStore(db)
	if err != nil {
		t.Fatal(err)
	}
	return s
}

func TestSQLiteIgnoresForeignKeysByDefault(t *testing.T) {
	s := sqliteStore(t, false)
	err := s.AddMember(999, "[email protected]") // no such team
	t.Logf("sqlite, default: insert member of team 999 -> %v", err)
	if err != nil {
		t.Fatal("expected sqlite to accept the orphan")
	}
}

func TestSQLiteWithForeignKeysOn(t *testing.T) {
	s := sqliteStore(t, true)
	err := s.AddMember(999, "[email protected]")
	t.Logf("sqlite, foreign_keys on: insert member of team 999 -> %v", err)
	if err == nil {
		t.Fatal("expected a foreign key error")
	}
}

func TestSQLiteCaseInsensitiveLikeVersusIlike(t *testing.T) {
	s := sqliteStore(t, true)
	s.db.Create(&Team{Name: "core"})
	s.AddMember(1, "[email protected]")
	_, err := s.FindByEmail("ada")
	t.Logf("sqlite, ilike query -> %v", err)
	if err == nil {
		t.Fatal("expected sqlite to reject ilike")
	}
}

func TestPostgresRealBehaviour(t *testing.T) {
	t.Parallel()
	s := pgStore(t)
	s.db.Create(&Team{Name: "core"})
	if err := s.AddMember(1, "[email protected]"); err != nil {
		t.Fatal(err)
	}
	ms, err := s.FindByEmail("ada")
	if err != nil || len(ms) != 1 {
		t.Fatalf("ilike: %v %d", err, len(ms))
	}
	err = s.AddMember(999, "[email protected]")
	t.Logf("postgres: insert member of team 999 -> %v", err)
	if err == nil {
		t.Fatal("expected a foreign key error")
	}
	err = s.DeleteTeam(1)
	t.Logf("postgres: delete team with members -> %v", err)
	if err == nil {
		t.Fatal("expected a restrict error")
	}
}

func TestPostgresParallelSchemas(t *testing.T) {
	for i := 0; i < 4; i++ {
		i := i
		t.Run(fmt.Sprintf("schema_%d", i), func(t *testing.T) {
			t.Parallel()
			s := pgStore(t)
			s.db.Create(&Team{Name: "same name in every schema"})
			var count int64
			s.db.Model(&Team{}).Count(&count)
			if count != 1 {
				t.Fatalf("saw %d teams, tests are leaking into each other", count)
			}
		})
	}
}

=== RUN   TestSQLiteIgnoresForeignKeysByDefault
    main_test.go:63: sqlite, default: insert member of team 999 -> <nil>
--- PASS: TestSQLiteIgnoresForeignKeysByDefault (0.01s)
=== RUN   TestSQLiteWithForeignKeysOn
    main_test.go:72: sqlite, foreign_keys on: insert member of team 999 -> violates foreign key constraint
--- PASS: TestSQLiteWithForeignKeysOn (0.01s)
=== RUN   TestSQLiteCaseInsensitiveLikeVersusIlike
    main_test.go:83: sqlite, ilike query -> SQL logic error: near "ilike": syntax error (1)
--- PASS: TestSQLiteCaseInsensitiveLikeVersusIlike (0.01s)
=== RUN   TestPostgresRealBehaviour
=== PAUSE TestPostgresRealBehaviour
=== RUN   TestPostgresParallelSchemas
=== RUN   TestPostgresParallelSchemas/schema_0
=== PAUSE TestPostgresParallelSchemas/schema_0
=== RUN   TestPostgresParallelSchemas/schema_1
=== PAUSE TestPostgresParallelSchemas/schema_1
=== RUN   TestPostgresParallelSchemas/schema_2
=== PAUSE TestPostgresParallelSchemas/schema_2
=== RUN   TestPostgresParallelSchemas/schema_3
=== PAUSE TestPostgresParallelSchemas/schema_3
=== CONT  TestPostgresParallelSchemas/schema_0
=== CONT  TestPostgresParallelSchemas/schema_1
=== CONT  TestPostgresParallelSchemas/schema_2
=== CONT  TestPostgresParallelSchemas/schema_3
--- PASS: TestPostgresParallelSchemas (0.00s)
    --- PASS: TestPostgresParallelSchemas/schema_1 (0.07s)
    --- PASS: TestPostgresParallelSchemas/schema_3 (0.08s)
    --- PASS: TestPostgresParallelSchemas/schema_0 (0.08s)
    --- PASS: TestPostgresParallelSchemas/schema_2 (0.08s)
=== CONT  TestPostgresRealBehaviour
    main_test.go:101: postgres: insert member of team 999 -> violates foreign key constraint: ERROR: insert or update on table "members" violates foreign key constraint "fk_members_team" (SQLSTATE 23503)
    main_test.go:106: postgres: delete team with members -> ERROR: update or delete on table "teams" violates RESTRICT setting of foreign key constraint "fk_members_team" on table "members" (SQLSTATE 23001)
--- PASS: TestPostgresRealBehaviour (0.06s)
PASS
ok  	example.test/lab	1.640s

sqliteStore opens a named in memory database per test (file:t7?mode=memory&cache=shared), so tests do not share state, and the shared cache keeps the data alive across the pool's connections. pgStore creates a schema with a unique name on the lab server, points search_path at it, migrates, and drops the schema in t.Cleanup. Both return the same *Store.

The test run printed everything in one go; the sections below read it by theme.

What SQLite hid

Foreign keys are off by default. TestSQLiteIgnoresForeignKeysByDefault inserted a member of team 999, which does not exist, and got <nil>. SQLite stores the orphan without complaint. The test is named for the surprise: any SQLite test that expects a foreign key error fails until you ask for them. With _pragma=foreign_keys(1) in the DSN, the second test got violates foreign key constraint. The pragma is per connection, and the DSN option applies it to each connection the pool opens. A helper that forgets it makes every constraint test pass vacuously.

Operators are not portable. TestSQLiteCaseInsensitiveLikeVersusIlike ran FindByEmail("ada") and got SQL logic error: near "ilike": syntax error (1). The production query uses a PostgreSQL feature, and SQLite cannot run it. You can rewrite the query as lower(email) like ? to make both work, and you will have chosen a worse query on PostgreSQL (it cannot use a plain index) so the tests could pass. The right fix is to test against the engine you deploy on.

Silent differences are worse than errors. An error tells you the test is invalid. Nothing tells you that SQLite's LIKE is case insensitive and PostgreSQL's is not, that its column types are advisory, or that it stores times at nanosecond precision where PostgreSQL keeps microseconds. A test can pass on SQLite and hide a bug that appears in production. The dialect article lists these.

What PostgreSQL showed

TestPostgresRealBehaviour ran the same flow on a real server:

• FindByEmail("ada") matched [email protected] through ilike.
• Adding a member of team 999 failed with violates foreign key constraint: ERROR: insert or update on table "members" violates foreign key constraint "fk_members_team" (SQLSTATE 23503), and TranslateError wrapped it as GORM's ErrForeignKeyViolated.
• Deleting team 1 while it had a member failed with violates RESTRICT setting of foreign key constraint "fk_members_team" on table "members" (SQLSTATE 23001), which is the behaviour that the OnDelete:RESTRICT tag was written to produce.

These are the assertions that give the tests value, and SQLite either skipped or could not run each of them.

A schema per test

TestPostgresParallelSchemas creates four subtests with t.Parallel(). Each calls pgStore, which makes its own schema, and each inserts a team with the same name, which would break a unique index if the schemas were shared. Each saw exactly one team. Four databases' worth of isolation came from one server and four CREATE SCHEMA statements, in 0.08 s each in the recorded run.

Compared with other isolation strategies:

Schema per test is a good default for GORM, because search_path in the DSN is all that changes and the models migrate into the new schema unchanged. Migrate from the same files you deploy (see migrations) so the schema under test is the production schema, not an AutoMigrate approximation. When the migration is slow, create one template schema and copy it with CREATE DATABASE ... TEMPLATE (a database per test) instead.

Testcontainers

The lab uses a PostgreSQL started once with docker run. In CI you often want the test itself to start it. Testcontainers for Go does this: it pulls an image, starts a container on a random port, waits for readiness and returns a connection string, and it removes the container when the tests end. The pattern is a TestMain that starts one container for the package and runs every test against it, with a schema per test inside. I did not run Testcontainers for this article, so I describe only what its documentation says; the schema isolation shown here is unchanged by how the server is started. See testcontainers in Go for the full setup.

Start the container once per package, not per test. A container takes a second or two to be ready, and a hundred tests would spend minutes starting servers.

When SQLite is the right choice

• Your production database is SQLite.
• The test is about code that does not depend on engine behaviour: mapping, validation, a repository that only does simple CRUD, and you are certain the queries are portable.
• A very early prototype where speed matters more than fidelity.

For everything with constraints, locking, JSON, arrays, upserts or text search, use the real thing. A rule that works: SQLite for the unit tests of pure logic, PostgreSQL for the store tests, and the store tests are most of the value.

Checklist

• Test against the database you run in production.
• If you use SQLite, enable foreign keys in the DSN and know the other differences.
• Give each test its own schema, and drop it in t.Cleanup.
• Run store tests in parallel with the race detector.
• Start one container per package, and migrate from the real migration files.

The testing helpers are covered in the GORM testing notes, and the container setup in the Testcontainers for Go documentation.