go / postgres

updated July 26, 2026

I use PostgreSQL with Go when I need a robust relational database with strong concurrency and full SQL support.

Setup

I use github.com/jackc/pgx/v5 as a database driver. It has better performance than lib/pq and is more actively maintained.

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

Use pgxpool for connection pooling:

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) {
	pool, err := pgxpool.New(context.Background(), databaseURL)
	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 handles connection pooling with sensible defaults; it sizes the pool from max_connections. The URL format is postgres://user:pass@host/dbname?sslmode=disable. Every pgx method takes a context.Context, so cancellation threads through.

Migrations

For schema management I use plain SQL files in db/migrate/, applied by a small Go runner. No ORM, no DSL, no external tool. Each version is a pair of files (*.up.sql plus an optional *.down.sql) that own their own BEGIN/COMMIT.

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

Drop the .down.sql to mark a migration non-reversible. Filenames must be <14-digit-stamp>_<name>.{up,down}.sql; anything else is ignored on load.

A transactional migration wraps statements in BEGIN/COMMIT:

BEGIN;

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

COMMIT;

A CREATE INDEX CONCURRENTLY migration omits BEGIN/COMMIT and sets lock_timeout = '0' on the first line, since CONCURRENTLY can't run inside a transaction:

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

The SQL file owns its transaction stance. The runner doesn't wrap migrations.

Runner

runMigrate lints first, opens a dedicated connection, sets safety 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)
}

Use pgx.Connect(ctx, url) directly, not a pool. Migrations rely on session-level SET commands (lock_timeout, statement_timeout) persisting across the whole run; pools can't guarantee that.

Transactional files run as one simple-query batch. Non-transactional files are split into statements and sent one at a time, because PostgreSQL treats multi-statement simple-query batches as implicit transaction blocks, which CONCURRENTLY rejects.

Reset timeouts after each migration. A CONCURRENTLY migration sets lock_timeout = '0'; without re-applying the default, a later transactional migration on the same connection silently runs without the 10s timeout.

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 (create it in your first migration with version text PRIMARY KEY). For transactional files, splice the INSERT before the final COMMIT; so the migration and its bookkeeping commit atomically. CONCURRENTLY files have no COMMIT to splice into, so the INSERT is appended and runs auto-committed, leaving a small crash window that requires manual recovery.

Linter

migrate and rollback lint the directory before opening any connection, so unsafe migrations never reach the database. Rules match unsafe DDL shapes: non-concurrent indexes, foreign keys without NOT VALID, SET NOT NULL, and CHECK without NOT VALID.

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

Matching runs against content with -- line comments stripped, so a commented-out CONCURRENTLY doesn't fool the check. A file-wide check hard-errors when CONCURRENTLY appears with BEGIN/COMMIT. See postgres / safe migrations for why each pattern is unsafe.

Connection guard

A guard refuses to run against 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)
}

The blast radius of an accidental production run is the whole production database. Cheap explicit guard.

Schema dump

After every successful migrate/rollback in development, regenerate db/schema.sql with pg_dump --schema-only and rebuild the test database from it. db/schema.sql is checked in, so reviewers see the full schema diff alongside the migration SQL. The test rebuild targets a fixed port so a stray DATABASE_URL can't redirect 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>

migrate and rollback lint first; genmigration scaffolds the file pair and stops. The --concurrent form prefills a CREATE INDEX CONCURRENTLY skeleton named index_<table>_on_<column>.

Scaling it down

The runner above is sized for a database under real traffic. A smaller service wants less of it.

On a CI coordinator I run, migrations are embedded with //go:embed migrate/*.sql rather than read from disk, so a schema change ships inside the binary that needs it. The deploy tool installs the binary, stops the service, applies pending migrations, and starts it again. Code and schema land together, and there is no separate step to remember.

What I left out there:

• Down files. Nothing has wanted a rollback, and that service's state is largely regenerable.
• The linter. Its rules guard against locks on tables big enough to notice, and those tables hold thousands of rows.
• The schema dump. That schema.sql is hand-written, and its comments carry the design notes a dump would erase.

What I kept: the filename convention, schema_migrations, and splicing the bookkeeping insert into the file's transaction. They cost nothing at either size, and they mean one design to remember when moving between the two codebases.

Inserts with RETURNING

Use RETURNING to get 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
}

Use $1, $2, etc. for parameter placeholders (not ?).

Query organization

As queries grow, keep the SQL next to the Go code that uses it. Each package owns its query files and embeds them with //go:embed:

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

One query per file. A tiny loader reads them at startup; missing SQL is a programmer error, so panic is fine:

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

Call the loaded queries with pgx's generic row helpers, mapping columns 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 codegen step, no generated Go in the repo. Query, SQL file, and call site live together, so refactors stay local: rename a column, update the .sql file, fix compile errors.

Name files by behavior (fetch_<thing>.sql, upsert_<thing>.sql, delete_<thing>.sql), use positional params, and cast when ambiguous ($1::bigint). Format them with cmd / pgfmt:

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

This approach skips codegen, so nothing checks the Go types against the schema the way sqlc does. A pgx scan of a nullable column into a non-nullable Go type panics at runtime on the first NULL row. cmd / nullscan recovers that safety property as a CI check, which makes embedded SQL plus nullscan a lightweight alternative to sqlc for the queries here.

Error handling

Extract structured error information from pgconn.PgError:

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:

• 23505: unique_violation
• 23503: foreign_key_violation
• 23502: not_null_violation
• 23514: check_violation
• 40001: serialization_failure
• 40P01: deadlock_detected
• 55P03: lock_not_available

Testing

Use a test database with automatic cleanup:

import (
	"context"
	"net/http"
	"net/http/httptest"
	"testing"
)

func initTestDB(t *testing.T) *DB {
	t.Helper()

	db, err := NewDB("postgres://app:secret@localhost/app_test?sslmode=disable")
	if err != nil {
		t.Fatalf("Failed to connect: %v", err)
	}
	t.Cleanup(func() { db.Close() })

	// Reset test data
	_, err = db.Exec(context.Background(), "TRUNCATE users RESTART IDENTITY CASCADE")
	if err != nil {
		t.Fatalf("Failed to reset database: %v", err)
	}

	return db
}

func TestHealthCheck(t *testing.T) {
	db := initTestDB(t)
	server := &Server{db: db}

	req, err := http.NewRequest("GET", "/health", nil)
	if err != nil {
		t.Fatalf("Failed to create request: %v", err)
	}

	rr := httptest.NewRecorder()
	http.HandlerFunc(server.health).ServeHTTP(rr, req)

	if rr.Code != 200 {
		t.Errorf("Expected status 200, got %d", rr.Code)
	}

	if rr.Body.String() != "OK" {
		t.Errorf("Expected body 'OK', got '%s'", rr.Body.String())
	}
}

Unlike SQLite tests, there is no :memory: database: I point at a dedicated test database and reset it between tests with TRUNCATE ... CASCADE. RESTART IDENTITY resets the auto-increment sequences so IDs stay predictable.

See postgres / dev test clusters for running test databases on a separate Postgres cluster.

When I reach for Postgres over SQLite

Postgres is my default for anything with multiple application instances, concurrent writes from many users, or a need for replication and high availability. I also lean on its richer SQL: CTEs, window functions, JSONB, and full-text search.

I reach for SQLite instead when I want a single binary with an embedded database, zero external dependencies, and low write concurrency.

← All articles