NestJS Postgres boilerplate: Knex, migrations and tests on a real database

Q: Is there a job queue?

Yes, inside Postgres. Jobs are rows claimed with `FOR UPDATE SKIP LOCKED`. No Redis or separate job service is needed.

CrabStack is a NestJS 12 starter kit on PostgreSQL. The backend talks to the database through Knex, ships 20 numbered migrations, and tests its queries against a real Postgres. This page covers how the data layer is built and the trade-offs of that choice.

Why Knex

Knex is a query builder. You write queries in TypeScript that read close to the SQL they produce, and Postgres features stay in reach: transactions, row locks, ON CONFLICT, FOR UPDATE SKIP LOCKED.

There is no ORM layer. Row types are plain TypeScript interfaces in src/db/types.ts, and services inject the Knex instance directly. When an agent writes a query, what it wrote is what runs. When you review it, the SQL is visible in the code.

The cost is real. Knex does not generate types from your schema, so a new column means updating the row interface by hand. There are no model classes, relations or lazy loading. If you search for a NestJS Postgres ORM, this template uses none.

Migrations

Migrations are TypeScript files in backend/migrations/, numbered from 001_initial.ts to 020_api_keys.ts. The commands:

npm run migrate runs knex migrate:latest.
npm run migrate:rollback rolls back the last batch.
A new migration is a copy of the last file, saved as the next number.

A migration that has already run is never edited, renamed or deleted. If one is, Knex reports The migration directory is corrupt.

How queries are written

Multi-table writes run in one transaction: this.db.transaction(async (trx) => { ... }).
Inserts and updates use returning('*') when the caller needs the row.
Work queues claim jobs with FOR UPDATE SKIP LOCKED, so two workers never take the same row. The email queue and the account deletion queue both run this way. There is no Redis.
Workspace data is filtered by workspace_id in the same statement as the id, so one team's rows cannot leak to another.

Tests against a real Postgres

Database code is tested against Postgres itself. A mocked query builder cannot prove that a lock holds or that a conflict resolves the way you expect.

21 integration spec files run when TEST_DATABASE_URL is set.
./verify.sh starts a throwaway Postgres container for them and removes it afterwards.
If Docker is missing and they skip, verify reports the run as not green and exits non-zero, unless you set VERIFY_ALLOW_INTEGRATION_SKIP=1 on purpose.
CI runs the backend suite twice against the same database. The second run catches tests that only pass on an empty database.

A rule for every table

DELETION_POLICY in backend/src/users/deletion-policy.ts states what account deletion does to each table. A spec compares it with the migrated schema. Add a table without a rule and the build fails. An agent cannot add a table and forget that it may hold personal data.

Local setup

Docker Compose runs Postgres 16 on port 5433, so it does not collide with a Postgres you already run on 5432. backend/.env points there after ./setup.sh. CI uses the same Postgres 16 image.

Who it fits

You know some SQL, or want to learn it, and want to read the queries your agent writes.
You want Postgres features without fighting an abstraction.
You want database behaviour tested on a database.

Who it does not fit

You want Prisma, Drizzle or TypeORM, with generated types and relations. Swapping the data layer would mean rewriting every service.
You need MySQL, SQLite or MongoDB. Knex is configured for Postgres only, and the queries use Postgres features.
You want a hosted database SDK in place of SQL.

NestJS SaaS boilerplate: everything that ships
NestJS multi-tenant boilerplate: workspace isolation in the queries
NestJS Stripe boilerplate: billing state in Postgres
NestJS and Next.js boilerplate: how the API reaches the frontend
Docs: Testing, Add a field to an existing table, Worker concurrency and recovery, What ghost deletion erases
Compare: vs ShipFast, vs Makerkit, vs supastarter

FAQ

Does CrabStack use an ORM? No. It uses Knex, a query builder, with hand-written row interfaces. There are no models or relations to configure.

Can I use Prisma or TypeORM instead? You can add one, but every shipped service, migration and integration test uses Knex. Replacing it means rewriting those.

Which Postgres version does it run on? Docker Compose and CI both use the postgres:16-alpine image.

How do I add a column? Copy the last migration as the next number, add the column, update the row interface and the zod schema, set its account deletion rule, then regenerate the OpenAPI spec and frontend types. The docs have a recipe for it.

Do the tests need Docker? The unit tests do not. The integration specs need a Postgres, and ./verify.sh starts one in Docker. Without it they skip and verify says the run is not green.

Is there a job queue? Yes, inside Postgres. Jobs are rows claimed with FOR UPDATE SKIP LOCKED. No Redis or separate job service is needed.