kastor with Express

#Project structure

Express has no opinion on where a database client lives, which makes it easy to end up with a pool per router file. A small structure avoids that:

src/
  db/schema.ts            tables and relations
  db/index.ts             the one client
  routes/posts.ts         a router
  errors.ts               error middleware
  server.ts               app, listen, shutdown
kastor.config.ts
kastor/migrations/
.env                      DATABASE_URL (not committed)

npm install kastor-orm pg express
npm install -D tsx @types/express
npx kastor-orm init --db postgres
npx kastor migrate dev --name init

#One client per process

A Node process imports a module once, so a client created at the top level of src/db/index.ts is shared by every route. In development, tsx watch restarts the whole process on change, so there is no stale module cache to guard against; the shutdown handler below closes the old pool on each restart.

import pg from 'pg'
import { kastor } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import { schema, relations } from './schema'

export const db = kastor({
  adapter: postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 })),
  schema,
  relations,
  log: process.env.LOG_SQL === '1',
})

The log option prints every statement with its parameters and duration when LOG_SQL=1, which is handy while building routes.

#Async routes

import { Router } from 'express'
import { db } from '../db'

export const posts = Router()

posts.get('/', async (req, res) => {
  const page = await db.posts.findPage({
    where: { published: true },
    include: { author: { select: { name: true } } },
    orderBy: { id: 'desc' },
    first: Math.min(Number(req.query.first ?? 20), 100),
    after: typeof req.query.after === 'string' ? req.query.after : null,
  })
  res.json(page)
})

posts.get('/:id', async (req, res) => {
  const post = await db.posts.findUniqueOrThrow({
    where: { id: Number(req.params.id) },
    include: { author: true, tags: true },
  })
  res.json(post)
})

posts.post('/', async (req, res) => {
  const { title, authorId, tags = [] } = req.body as { title: string; authorId: number; tags?: string[] }
  const post = await db.posts.create({
    data: { title, authorId, tags: { connectOrCreate: tags.map((name) => ({ where: { name }, create: { name } })) } },
    include: { tags: true },
  })
  res.status(201).json(post)
})

posts.post('/:id/views', async (req, res) => {
  const post = await db.posts.update({ where: { id: Number(req.params.id) }, data: { views: { increment: 1 } } })
  res.json({ views: post.views })
})

In Express 5, a rejected promise in a handler goes straight to the error middleware, so findUniqueOrThrow can simply throw. On Express 4, wrap handlers with a helper that calls next(err).

#Errors and graceful shutdown

import type { NextFunction, Request, Response } from 'express'
import { isKastorError } from 'kastor-orm'

const STATUS: Record<string, number> = { NOT_FOUND: 404, UNIQUE_VIOLATION: 409, FOREIGN_KEY_VIOLATION: 422, NOT_NULL_VIOLATION: 422 }

export function errorHandler(err: unknown, req: Request, res: Response, next: NextFunction) {
  if (res.headersSent) return next(err)
  if (isKastorError(err) && STATUS[err.code]) {
    return res.status(STATUS[err.code]!).json({ error: err.code, message: err.message, hint: err.hint })
  }
  console.error(err)
  res.status(500).json({ error: 'INTERNAL' })
}

import express from 'express'
import { db } from './db'
import { errorHandler } from './errors'
import { posts } from './routes/posts'

const app = express()
app.use(express.json())
app.use('/posts', posts)
app.get('/health', async (req, res) => {
  await db.$sql`select 1`
  res.send('ok')
})
app.use(errorHandler)

const server = app.listen(Number(process.env.PORT ?? 3000))

for (const signal of ['SIGTERM', 'SIGINT'] as const) {
  process.on(signal, () => {
    server.close(async () => {
      await db.$disconnect()
      process.exit(0)
    })
  })
}

On SIGTERM the server stops accepting connections, finishes in flight requests, then closes the pool. Docker, Kubernetes and most platforms send that signal before stopping a container.

#Environment variables, Docker and migrations

Run locally with tsx watch --env-file=.env src/server.ts, or node --env-file=.env dist/server.js after a build. In production, the platform provides DATABASE_URL. Apply migrations before the new version starts. In a container, an entrypoint script keeps the order explicit:

FROM node:22-slim
WORKDIR /app
COPY package*.json ./
RUN npm ci
COPY . .
RUN npx tsc
CMD ["sh", "-c", "npx kastor migrate deploy && node dist/server.js"]

With several replicas, running migrations in every container is safe in the sense that kastor records what it applied, but two containers may race on the same pending migration. A dedicated CI step or a one off release job is cleaner:

- run: npm ci
- run: npx tsc --noEmit
- run: npx kastor migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

#Transactions and query modules

Route handlers stay readable when queries live in small modules per feature, such as src/posts/queries.ts, that export functions like publishedFeed(cursor). Each function returns a kastor query, so its result type is inferred and flows into the handler without interfaces. Handlers then deal with HTTP: parsing input, choosing status codes, shaping the response.

When a request writes to several tables, wrap those writes in db.$transaction(async (tx) => ...). The callback receives a client bound to one connection; every call on tx runs in the transaction, which commits when the callback resolves and rolls back when it throws. Because the error middleware already maps KastorError codes, a unique violation thrown halfway rolls back the transaction and still reaches the client as a 409.

Express also runs on serverless platforms through adapters. The setup stays the same with one change: each function instance has its own pool, so set max to 1 or 2 and connect through a pooler. The Lambda guide covers that in detail.

#Production checklist

Before the first deploy, check these points. Each one prevents a problem that otherwise shows up only under real traffic, usually as exhausted connections, a leaked secret or a schema that does not match the code that is running:

• One client module, imported by every router, created once per process.
• One error middleware that maps KastorError codes to status codes and logs the rest.
• A SIGTERM handler that closes the server and then the pool.
• Request bodies validated before they reach create or update.
• Migrations applied by a single CI step or release job before the new containers start.

#Pitfalls

• Pools per router. Creating kastor() inside a router file or a handler opens a pool each time. Import the shared client.
• Unvalidated bodies. kastor's types protect your code, not the network. Validate req.body before it reaches create.
• Bigint responses. res.json cannot serialize bigint. Convert t.bigId() keys, or set an Express JSON replacer.
• Long transactions. Do not await external HTTP calls inside $transaction; the connection stays checked out the whole time.

The Fastify guide shows the same setup as a plugin, and the Knex and Sequelize guides cover moving an existing Express API.

#Questions people ask

#Related guides