Dan's profile photo
GitHub icon LinkedIn icon

go / postgres

updated September 03, 2026

I use PostgreSQL with Go when I need concurrent writes and full SQL.

Setup

I use github.com/jackc/pgx/v5 as the driver. It is faster than lib/pq and maintained.

go mod init server
go get github.com/jackc/pgx/v5

pgxpool pools connections:

import (
	"context"
	"log"
	"net/http"

	"github.com/jackc/pgx/v5/pgxpool"
)

// DB wraps a PostgreSQL connection pool
type DB struct {
	*pgxpool.Pool
}

func NewDB(databaseURL string) (*DB, error) {
	cfg, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return nil, err
	}
	if cfg.ConnConfig.RuntimeParams["plan_cache_mode"] == "" {
		cfg.ConnConfig.RuntimeParams["plan_cache_mode"] = "force_custom_plan"
	}
	pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
	if err != nil {
		return nil, err
	}
	if err := pool.Ping(context.Background()); err != nil {
		pool.Close()
		return nil, err
	}
	return &DB{Pool: pool}, nil
}

type Server struct {
	db *DB
}

func (s *Server) health(w http.ResponseWriter, r *http.Request) {
	if err := s.db.Ping(r.Context()); err != nil {
		http.Error(w, "Database error", 500)
		return
	}
	w.Write([]byte("OK"))
}

func main() {
	db, err := NewDB("postgres://app:secret@localhost/app?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	server := &Server{db: db}
	http.HandleFunc("/health", server.health)
	log.Fatal(http.ListenAndServe(":8080", nil))
}

pgxpool sizes the pool from max_connections. Every pgx method accepts a context.Context for cancellation. The plan_cache_mode line is explained under Plan cache.

Migrations

I keep plain SQL files in db/migrate/ and run them with a small Go runner. Each version is an *.up.sql file and an optional *.down.sql file. Each file manages its own BEGIN and COMMIT.

db/migrate/20260430165330_clear_bad_estimates.up.sql
db/migrate/20260430165330_clear_bad_estimates.down.sql

A migration with no .down.sql file is not reversible. The runner ignores a file that does not match <14-digit-stamp>_<name>.{up,down}.sql.

A transactional migration:

BEGIN;

DELETE FROM tam_estimates
WHERE updated_by_user_id = 1 AND tam_usd >= 200000000000;

COMMIT;

PostgreSQL cannot run CONCURRENTLY inside a transaction block, so a CREATE INDEX CONCURRENTLY migration omits BEGIN and COMMIT:

SET lock_timeout = '0';
CREATE INDEX CONCURRENTLY index_people_on_email
  ON people (email);

Runner

runMigrate lints the files, opens a dedicated connection, sets timeouts, and applies pending migrations:

func runMigrate(ctx context.Context) error {
	if err := lintBeforeRun(); err != nil {
		return err
	}
	conn, err := connectMigrate(ctx)
	if err != nil {
		return err
	}
	defer conn.Close(ctx)

	if err := setMigrateTimeouts(ctx, conn); err != nil {
		return err
	}

	files, err := loadMigrations(migrateDir)
	if err != nil {
		return err
	}
	applied, err := appliedVersions(ctx, conn)
	if err != nil {
		return err
	}

	for _, ver := range pendingVersions(files, applied) {
		sql, err := os.ReadFile(files[ver].upPath)
		if err != nil {
			return err
		}
		if err := applyMigration(ctx, conn, injectBookkeeping(string(sql), ver, false)); err != nil {
			return fmt.Errorf("migrate %s: %w", ver, err)
		}
		if err := setMigrateTimeouts(ctx, conn); err != nil {
			return err
		}
		fmt.Printf("pg migrated %s\n", ver)
	}
	return regenerateSchemaArtifacts(ctx, conn)
}

connectMigrate uses pgx.Connect, not a pool. A pool does not keep a session-level SET across queries.

The runner runs a transactional file as one batch. It splits a non-transactional file into statements, because PostgreSQL treats a batch as an implicit transaction block.

The runner resets the timeouts after each migration, because a CONCURRENTLY migration set lock_timeout = '0' on the same connection.

func setMigrateTimeouts(ctx context.Context, conn *pgx.Conn) error {
	if _, err := conn.Exec(ctx, "SET lock_timeout = '10s'"); err != nil {
		return err
	}
	_, err := conn.Exec(ctx, "SET statement_timeout = '10min'")
	return err
}

Bookkeeping

The runner records each applied version in schema_migrations. The first migration creates that table with version text PRIMARY KEY.

In a transactional file, the runner splices the INSERT before the final COMMIT;, so the migration and its record commit together. A CONCURRENTLY file has no COMMIT, so the runner appends the INSERT and runs it auto-committed. A crash between the two needs manual recovery.

Linter

The linter runs before the runner opens a connection. It matches unsafe DDL: non-concurrent indexes, foreign keys without NOT VALID, SET NOT NULL, and CHECK constraints without NOT VALID.

type lintRule struct {
	name              string
	pattern           *regexp.Regexp
	exclude           *regexp.Regexp
	skipIfCreateTable bool
	message           string
}

The linter strips -- comments before it matches. It also rejects CONCURRENTLY beside BEGIN or COMMIT. See postgres / safe migrations for why each pattern is unsafe.

Connection guard

The runner refuses a non-localhost host unless APP_ENV names a deployed environment:

func guardConnectionTarget(dsn, appEnv string) error {
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return err
	}
	switch cfg.Host {
	case "localhost", "127.0.0.1", "::1":
		return nil
	}
	switch appEnv {
	case "production", "staging":
		return nil
	}
	return fmt.Errorf("refusing to migrate non-localhost host %q; "+
		"set APP_ENV=production or staging", cfg.Host)
}

Schema dump

After migrate or rollback in development, the runner regenerates db/schema.sql with pg_dump --schema-only and rebuilds the test database from it. The file is checked in, so a reviewer sees the schema diff beside the migration. The rebuild targets a fixed port, so a stray DATABASE_URL cannot point it at the dev database (see postgres / dev test clusters).

CLI

One binary with subcommands:

go run ./cmd/db migrate              # apply pending
go run ./cmd/db rollback [steps]     # revert last N (default 1)
go run ./cmd/db genmigration <name>
go run ./cmd/db genmigration --concurrent <table> <column>

genmigration --concurrent writes a CREATE INDEX CONCURRENTLY template named index_<table>_on_<column>.

Scaling it down

The runner above is sized for a database under traffic. A smaller service needs less.

A CI coordinator I run embeds the migrations with //go:embed migrate/*.sql, so a schema change ships in the binary. The deploy tool installs the binary, stops the service, migrates, and starts it again.

I left out down files, the linter, and the schema dump. The tables are small, and that schema.sql is hand-written.

I kept the filename convention, schema_migrations, and the spliced INSERT, so one design covers both codebases.

Inserts with RETURNING

RETURNING returns generated values:

func createUser(ctx context.Context, db *DB, email string) (int64, error) {
	var id int64
	err := db.QueryRow(ctx,
		"INSERT INTO users (email) VALUES ($1) RETURNING id",
		email,
	).Scan(&id)
	return id, err
}

pgx uses $1, $2 placeholders, not ?.

Query organization

Each package keeps its SQL files beside the Go code and embeds them with //go:embed:

people/
  queries/
    fetch_person.sql
    upsert_person.sql
  sql.go
  person.go

One query per file. A loader reads them at startup and panics on a missing file:

package people

import "embed"

//go:embed queries/*.sql
var fs embed.FS

func sqlFile(name string) string {
	b, err := fs.ReadFile("queries/" + name + ".sql")
	if err != nil {
		panic(err)
	}
	return string(b)
}

var (
	qFetchPerson  = sqlFile("fetch_person")
	qUpsertPerson = sqlFile("upsert_person")
)

The pgx row helpers map columns to struct fields with db:"column_name" tags:

rows, err := db.Query(ctx, qFetchPerson, email)
if err != nil {
	return nil, err
}
return pgx.CollectRows(rows, pgx.RowToStructByName[PersonRow])

No code generation. The query and the call site share a package, so a rename touches the .sql file and the compile errors it causes.

I name query files by verb: fetch_<thing>.sql, upsert_<thing>.sql. I cast an ambiguous parameter ($1::bigint) and format the files with cmd / pgfmt:

go tool pgfmt -w <pkg>/queries/*.sql

Nothing checks the Go types against the schema as sqlc does. A scan of a nullable column into a non-nullable type panics on the first NULL row. cmd / nullscan checks that in CI.

Plan cache

A query file has no string substitution, so a parameter carries each choice: a sort key in CASE WHEN $3 = 'name' THEN name END, an optional filter behind cardinality($2::text[]) = 0 OR ..., an id array in = ANY ($1), a page size in LIMIT $2.

pgx prepares each query once per connection. Postgres plans the first five executions of a prepared statement with the real values. Then it switches to one generic plan when the generic estimate is close to the average of the five. A custom plan folds $3 = 'name' to a constant and drops the other branches. A generic plan keeps every branch and estimates each parameter with a default. Five broad calls can lock in a plan that a narrow call then runs for the life of the connection.

So NewDB sets plan_cache_mode = force_custom_plan on the pool. Postgres plans every execution with its values. The cost is one plan step per query, under a millisecond on my largest list queries, and never a worse plan. EXPLAIN with literal values in psql shows the plan the app runs. A plan_cache_mode in the URL wins, so one service can go back to auto without a code change.

Error handling

pgconn.PgError carries the SQLSTATE code:

import (
	"errors"

	"github.com/jackc/pgx/v5/pgconn"
)

func isUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23505"
}

func isForeignKeyViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23503"
}

Common error codes:

Testing

Postgres has no :memory: database, unlike SQLite. A test connects to a database on a separate test cluster, inserts rows through fixture helpers, and asserts with is. See go / test for the helpers and postgres / dev test clusters for the cluster.

Postgres or SQLite

I choose Postgres for multiple app instances, concurrent writes, replication, or its richer SQL (CTEs, window functions, JSONB, full-text search). I choose SQLite for a single binary with low write concurrency.

← All articles