Go basics

pgmem is a Go module. The server runs inside your process; there is no binary to install and nothing is downloaded at run time.

Install

go get github.com/shibukawa/pgmem github.com/jackc/pgx/v5

This installs both pgmem and the commonly used pgx driver shown below.

Note

The first build compiles about 110 MB of generated Go and takes around a minute. Go’s build cache keeps the result, so later builds and go test runs do not pay it again.

Choose a connection for tests

pgmemtest.Fixture exposes three connection forms. Its per-test helpers give each calling test a fresh database; read-only tests can instead share one fork. Prefer *pgxpool.Pool or *sql.DB for test queries: both use an in-process net.Pipe connection and skip loopback TCP. Use the DSN when a tool or client needs a PostgreSQL URL. PgxConn(t) is also available when a single pgx connection is a better fit.

Handle	Fixture method	Connection
*pgxpool.Pool	fx.PgxPool(t)	In-process; recommended for pgx applications
*sql.DB	fx.DB(t)	In-process; recommended for database/sql applications
DSN	fx.DSN(t)	Loopback TCP; works with any client that accepts a URL

The Go testing guide shows how to prepare a database once, share a fork across read-only tests, and give only writing tests their own copies. On small queries, the in-process dialer is about three times faster than TCP; see the benchmark results.

The basic flow

1. Start a server. pgmem.Start returns a running PostgreSQL with its own in-memory data directory.

   s, err := pgmem.Start(ctx, pgmem.Options{Database: "app"})
   if err != nil {
     log.Fatal(err)
   }
   defer s.Close()
   
   fmt.Println(s.DSN()) // postgres://postgres@127.0.0.1:54321/app?sslmode=disable

   Options also takes User, Port (0 picks a free port), Params for postgres -c settings, Log for the server log, and WaitTimeout for how long a connection may wait behind another connection’s idle transaction.

2. Register the schema. Run your migrations against the DSN. Any tool that accepts a connection string works; see migration tools below.

3. Load seed data. Insert the rows every test expects. See seed data.

4. Connect the application. For backend unit tests, let repositories and services accept *sql.DB or *pgxpool.Pool and inject a handle from pgmemtest.Fixture. Those handles use the in-process dialer, so tests avoid loopback TCP. The examples below use a DSN to show the standalone driver APIs; keep that boundary for end-to-end tests, separate processes, or tools that require a PostgreSQL URL.

   pgx

   conn, err := pgx.Connect(ctx, s.DSN())
   if err != nil {
     return err
   }
   defer conn.Close(ctx)
   
   var name string
   err = conn.QueryRow(ctx, "SELECT name FROM users WHERE id = $1", 1).Scan(&name)

   pgxpool

   pool, err := pgxpool.New(ctx, s.DSN())
   if err != nil {
     return err
   }
   defer pool.Close()

   database/sql

   import _ "github.com/jackc/pgx/v5/stdlib"
   
   db, err := sql.Open("pgx", s.DSN())
   if err != nil {
     return err
   }
   defer db.Close()

Skip TCP with the in-process dialer

Server.Dial connects through net.Pipe instead of a loopback socket. Small queries get about three times faster, because a TCP round trip spends most of its time in the kernel, not in PostgreSQL. pgmemtest.Fixture.PgxPool and pgmemtest.Fixture.DB configure this dialer for you.

cfg, _ := pgx.ParseConfig(s.DSN())
cfg.DialFunc = s.Dial
conn, _ := pgx.ConnectConfig(ctx, cfg)

// database/sql: register the config and open it by name
name := stdlib.RegisterConnConfig(cfg)
db, _ := sql.Open("pgx", name)

// For pgxpool, set the same dialer on its parsed config.
poolCfg, _ := pgxpool.ParseConfig(s.DSN())
poolCfg.ConnConfig.DialFunc = s.Dial
pool, _ := pgxpool.NewWithConfig(ctx, poolCfg)

Migration tools

A migration tool only needs the DSN or a *sql.DB. Run it once, before any test gets a copy.

Plain SQL

//go:embed schema.sql
var schema string

if _, err := db.ExecContext(ctx, schema); err != nil {
  return err
}

Several statements in one string are fine.

golang-migrate

import (
  "github.com/golang-migrate/migrate/v4"
  _ "github.com/golang-migrate/migrate/v4/database/postgres"
  _ "github.com/golang-migrate/migrate/v4/source/file"
)

m, err := migrate.New("file://migrations", s.DSN())
if err != nil {
  return err
}
if err := m.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
  return err
}

goose

import "github.com/pressly/goose/v3"

goose.SetDialect("postgres")
if err := goose.Up(db, "migrations"); err != nil { // db is a *sql.DB
  return err
}

Atlas

out, err := exec.CommandContext(ctx, "atlas", "migrate", "apply",
  "--dir", "file://migrations", "--url", s.DSN()).CombinedOutput()
if err != nil {
  return fmt.Errorf("atlas: %v: %s", err, out)
}

ORM

// GORM
gdb, _ := gorm.Open(postgres.Open(s.DSN()), &gorm.Config{})
gdb.AutoMigrate(&User{}, &Order{})

// ent
client, _ := ent.Open("postgres", s.DSN())
client.Schema.Create(ctx)

Note

A server serves every database it has, restarting its backend when a connection switches databases. Tools that create a scratch database on the same server can use it, but each switch costs a backend restart, so keep that to setup rather than test cases.

Seed data

dbtestify

dbtestify loads YAML data sets for seeding. In backend unit tests, it can also compare the resulting table contents with an expected data set and report differences.

testdata/seed.yaml

users:
- { id: 1, name: Frank, email: frank@example.com }
- { id: 2, name: Grace, email: grace@example.com }

orders:
- { id: 1, user_id: 1, amount: 1200 }

import "github.com/shibukawa/dbtestify"

//go:embed testdata
var testdata embed.FS

func seed(ctx context.Context, db *sql.DB) error {
  f, err := testdata.Open("testdata/seed.yaml")
  if err != nil {
    return err
  }
  defer f.Close()
  data, err := dbtestify.ParseYAML(f)
  if err != nil {
    return err
  }
  dbc, err := dbtestify.NewDBConnectorFromDB(db, dbtestify.PostgresDialect)
  if err != nil {
    return err
  }
  return dbtestify.Seed(ctx, dbc, data, dbtestify.SeedOpt{})
}

Plain SQL

_, err := db.ExecContext(ctx, `
  INSERT INTO users (id, name, email) VALUES
    (1, 'Frank', 'frank@example.com'),
    (2, 'Grace', 'grace@example.com');
  INSERT INTO orders (id, user_id, amount) VALUES (1, 1, 1200);
`)

COPY

rows := [][]any{{1, "Frank", "frank@example.com"}, {2, "Grace", "grace@example.com"}}
_, err := conn.CopyFrom(ctx, pgx.Identifier{"users"},
  []string{"id", "name", "email"}, pgx.CopyFromRows(rows))

COPY is the fastest way to load thousands of rows.

Snapshot and fork by hand

The test helpers in the next page wrap these two calls, which you can also use directly:

snap, err := s.Snapshot(ctx, pgmem.SnapshotOptions{MaxForks: 8})
if err != nil {
  return err
}
defer snap.Close()

fork, err := snap.Fork(ctx) // a new server on a copy of the snapshot
if err != nil {
  return err
}
defer fork.Close()

A snapshot checkpoints the server and copies its data directory. A fork copies it again and starts a new backend. Commit or close every connection to s before Snapshot, because it waits for open transactions.

Use Restore to replace a live server’s state with a different snapshot of the same database and user. It keeps the fork’s port and client connections, so a captured DSN or pool remains valid:

if err := fork.Restore(ctx, anotherSnapshot); err != nil {
  return err
}

Reset(ctx) is shorthand for restoring the snapshot that originally created the fork. Snapshot.Close() stops new forks but leaves live forks running; Snapshot.Wait() blocks until those forks close.