Skip to content
CanteenCrowd

By Shrin5 min readDraft

No ORM, and a CI that won't let the database drift

CanteenCrowd's Go backend has 82 migrations and 321 SQL queries, and no ORM. Here's how one CI job keeps the schema, the SQL and the Go code in step on every push.

CanteenCrowd is a multi-tenant SaaS for canteen operators: staff, diners, menus, events, purchasing, inventory, an operations hub and a support desk with live chat. The backend is Go on Postgres.

There’s no ORM. The queries are plain SQL in .sql files, and the Go that runs them is generated. That only works if three things never drift apart: the schema, the queries, and the Go code made from them. Keeping them in step is one CI job’s work, on every push, and that job is what this post is about.

Why no ORM

An ORM hides what the database does, or makes me fight it. That’s a fine trade when the database is just storage. In CanteenCrowd it isn’t, so the queries are plain SQL, and three things get better:

  1. What I review is what runs. No lazy loading, no surprise N+1 queries, no guessing what SQL a method call turns into.
  2. The backend stays light. No mapping layer at run time: the generated code calls pgx and scans each column into a struct field, one by one.
  3. Mistakes show up early. If a query uses a column that doesn’t exist, sqlc generate fails on my machine, long before production.

Here’s the work the database does, which an ORM would get in the way of:

  • Row-level security keeps every customer’s data apart. Each tenant-owned table has a policy keyed on a per-transaction setting, so a query that forgets its tenant sees zero rows instead of someone else’s.
  • Row locks (FOR UPDATE, FOR SHARE): a vendor can’t be deleted while an order that uses it is being saved.
  • Advisory locks stop two people taking the same order number, or booking the same crew member for two events at once.
  • JSONB operators, triggers and SECURITY DEFINER functions for sign-in.

What can drift, and what catches it

The setup, in brief: the tables are declared in backend/db/schema/*.sql, and Atlas writes each migration from the difference, keeping a hash of every migration file in atlas.sum. The queries are plain SQL in .sql files, and sqlc turns each one into a typed Go method (make sqlc). Every query runs inside WithTenantTx, one helper that opens a transaction and sets the tenant with set_config first, so row-level security knows whose data it is.

With this setup, four things can silently go wrong:

  • A migration is edited after it was applied. atlas.sum no longer matches the files.
  • The migrations don’t replay on a fresh database: an ordering mistake, or an RLS migration that refers to a table that doesn’t exist yet.
  • The generated Go is stale. Someone changed a query and forgot make sqlc, or edited the generated code by hand.
  • The tests pass for the wrong reason. A superuser and a table’s owner normally skip row-level security, so tests run as one of them would never notice a missing policy.

The CI job (GitHub Actions) checks each one, on every push to develop and main and on every pull request:

# .github/workflows/ci.yml, trimmed
jobs:
  backend:
    name: Backend (Go, migrations, sqlc)
    runs-on: ubuntu-24.04
    steps:
      - uses: actions/checkout@v5
      - name: Environment from .env.example
        run: cp .env.example .env
      - name: Start Postgres
        run: docker compose up -d --wait postgres

      # atlas.sum matches the migration files, and the files replay on a dev database.
      - name: Validate migrations
        run: atlas migrate validate --env local
      - name: Apply migrations
        run: make migrate-apply

      # Generated code must be committed exactly as sqlc produces it.
      - name: sqlc output is up to date
        run: |
          make sqlc
          git diff --exit-code -- backend/internal/database

      - name: Lint
        run: make lint
      # DB tests run as the app role against the migrated database (RLS applies).
      - name: Test
        run: make test

The steps, in order:

  1. No secrets. The database is a throwaway Postgres container in the runner. Its settings come from .env.example, the same placeholder file a new developer starts from.
  2. atlas migrate validate checks atlas.sum against the files, and replays every migration on a scratch Postgres 18.
  3. make migrate-apply runs all 82 migrations on the runner’s database, with the same command I use locally.
  4. make sqlc then git diff --exit-code. If running sqlc changes even one line of the generated package, the job fails. Stale or hand-edited generated code can’t reach develop.
  5. golangci-lint, then go test ./.... The database tests connect as ccms_app, the same role the API uses, so row-level security applies. Each test runs inside one transaction that is rolled back, and the real service code runs on that transaction: every WithTenantTx becomes a savepoint. Nothing is committed, and a test that reaches for another tenant’s data sees what production would: nothing.

A second job lints, tests and builds the React frontend, and does a dry run of the Cloudflare Worker deploy. A newer push to the same branch cancels the run in progress.

The trade-offs

It isn’t free:

  • More SQL to write. Every list page has its own query. I’ve stopped seeing this as a cost: the queries are short, and each one is easy to read on its own.
  • Dynamic filters are a pattern, not a library. Optional filters are written into the SQL: an empty or NULL argument switches a condition off. A truly dynamic query, built from arbitrary input, would need something else. CanteenCrowd hasn’t needed one yet.
  • One transaction per read. Even a plain list runs BEGIN, one set_config, the query and COMMIT. The tenant and the acting user are set in one statement to save a round trip; the rest is the price of row-level security.
  • Atlas Community doesn’t see RLS. Every new tenant-owned table needs its policies in a migration Atlas doesn’t generate, and that’s on me to remember. The tests running as the app role are what catch it when I forget.

It works with Claude Code:

  • The extra SQL stops costing much. I build CanteenCrowd with Claude Code. Writing a lot of SQL is the usual argument against this setup; with an assistant writing most of it, a new list page’s query, its generated method and the handler come in one change. The 321 queries are cheap to write and, being plain SQL, quick to review.
  • The stack keeps the assistant in check. An assistant makes the mistakes a new teammate makes, and here each one lands on a check before it reaches anyone:
    • a guessed column name: sqlc generate fails, because sqlc reads the real schema;
    • a query changed without regenerating, or generated code patched directly: CI regenerates and fails on the diff;
    • a migration edited after the fact, or written in the wrong order: atlas.sum and the replay on a fresh database fail;
    • a new table without its row-level security policy: the database tests run as the app role, the role the policies apply to, so a tenant-isolation test on that table fails instead of passing quietly.

The speed comes from the assistant; the stability comes from a stack that refuses to accept a wrong guess. The next post is about the rest of that setup.

A small open-source sample of the backend, Shrin210-98/CanteenCrowd-go-api, is written without Claude Code.

Written by Shrin, who builds CanteenCrowdGitHubLinkedIn