DB Workflow

I don’t much like ORMs. They hide the interesting bits, make performance an afterthought, and couple migrations to application frameworks in ways that age badly. SQL is already a very good DSL. I prefer:

• Start from an empty schema; the schema is the API.
• Evolve it with migrations.
• Write queries in raw SQL. There are tools that then let you compile them into type safe functions in whatever language your API is written in.
• Keep “data shape” in the database and “business logic” in the application.

Goose is a good migration tool.

Sqlc is a good codegen tool for compiling SQL queries.

You end up with this file structure in your repository:

/db/migrations/    # goose .sql files, timestamped
/db/queries/       # named *.sql queries for sqlc
/db/schema.sql
/db/sqlc.yaml         # sqlc config
/src/generated/    # sqlc-generated code
/Makefile

The sqlc config looks something like this:

version: "2"
sql:
  - engine: postgresql
    schema: db/schema.sql
    queries: db/queries
    codegen:
      - out: src/db/generated
        plugin: ts
        options:
          runtime: node
          driver: pg
plugins:
  - name: ts
    wasm:
      url: https://downloads.sqlc.dev/plugin/sqlc-gen-typescript_0.1.3.wasm
      sha256: 287df8f6cc06377d67ad5ba02c9e0f00c585509881434d15ea8bd9fc751a9368

The Makefile includes commands that stitch all these things together:

sqlc:
	sqlc generate --file db/sqlc.yaml

migrate-create:
	goose -dir db/migrations create $(name) sql

migrate-up:
	echo "Migrating up $(DATABASE_URL)"
	goose -dir db/migrations postgres $(DATABASE_URL) up
	make db-dump

migrate-down:
	echo "Migrating down $(DATABASE_URL)"
	goose -dir db/migrations postgres $(DATABASE_URL) down
	make db-dump

DB_DUMP_DATE := $(shell date +%Y%m%d%H%M%S)
DUMP_FILE := ${DB_DUMP_DATE}.sql

db-dump:
	@mkdir -p db/dumps
	@if command -v pg_dump >/dev/null; then \
	  { \
	    pg_dump --schema-only --no-owner --no-privileges --no-comments "$(DATABASE_URL)"; \
	    echo; echo "-- goose_db_version state"; \
	    psql "$(DATABASE_URL)" -At -c \
	      "SELECT 'INSERT INTO public.goose_db_version (\"version_id\",\"is_applied\") VALUES ('||version_id||','||is_applied||');' \
	      FROM public.goose_db_version ORDER BY version_id"; \
	  } > db/dumps/${DUMP_FILE}; \
	  cp db/dumps/${DUMP_FILE} db/schema.sql; \
	else echo "pg_dump not found, skipping db-dump"; fi

Workflow

So the workflow when creating a new project would go like this:

1. Scaffold the repo

mkdir -p db/migrations db/queries src/db/generated
touch db/schema.sql db/sqlc.yaml Makefile

2. Create a migration

make migrate-create name=create_accounts

3. Edit the migration file accordingly

-- +goose Up
CREATE TABLE accounts(
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE accounts;

4. Apply the migration

make migrate-up

5. Write queries

-- db/queries/accounts.sql

-- name: CreateAccount :one
INSERT INTO accounts(email) VALUES ($1) RETURNING *;

-- name: GetAccountByEmail :one
SELECT * FROM accounts WHERE email=$1;

-- name: ListAccounts :many
SELECT * FROM accounts ORDER BY created_at DESC LIMIT $1 OFFSET $2;

6. Create types and functions

make sqlc

7. You would then have access to a bunch of generated functions you could use in your app

import { Pool } from "pg";
import * as db from "@/src/db/generated"; // whatever your out path is
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function createUser(email: string) {
  const client = await pool.connect();
  try {
    const q = db.newQuerier(client);
    const a = await q.createAccount({ email });
    return a;
  } finally {
    client.release();
  }
}