db

Almost every app keeps its data in a database. @kitr/db opens the connection when your app starts, hands the same client to every controller, action and job, and closes it cleanly when the server stops.

It ships no ORM. Bring Drizzle, Prisma, Kysely or a plain pg pool, and kitr runs it inside the app lifecycle. It runs on the server only; for browser or device data, use storage.

The examples use Acme's Postgres database with orders and projects tables, read through Drizzle.

Install

kitr add db

Everything kitr add changes

What kitr add does:

• Adds the server package first when missing.
• Installs @kitr/db.
• Adds '@kitr/db' to packages and () => import('@kitr/db') to providers in kitrrc.ts.
• Appends DATABASE_URL=postgres://localhost:5432/app under a # Database comment to .env.example, and to .env when it exists.
• Writes config/db.ts (shown under Configure). An existing file is left alone.
• Regenerates config/index.ts, the config list the standalone server imports.
• Adds a ## kitr: db section to AGENTS.md: "config/db.ts connects the ORM; controllers and actions read it with useDb<AppDb>(ctx.app) or ctx.app.container.use('db'). Migrations are the ORM's own tooling."

kitr add db asks no questions. Pass --dry-run to preview or --no-install to skip the package manager. The server kit includes db.

kitr remove db removes the provider, the packages entry, the dependency, the AGENTS.md section and config/db.ts. It leaves DATABASE_URL, your ORM packages and schema files.

Configure

kitr add db writes this file. connect returns an empty object until you fill in your ORM.

// config/db.ts
import { defineDb } from '@kitr/db'

// Example with Drizzle + postgres.js: `pnpm add drizzle-orm postgres`
// import { drizzle } from 'drizzle-orm/postgres-js'
// import postgres from 'postgres'

export default defineDb({
  connect: async () => {
    // const client = postgres(process.env.DATABASE_URL!)
    // return drizzle(client)
    return {} as unknown
  },
  // disconnect: (db) => db.$client.end(),
})

defineDb takes three functions, each receiving the app.

Prefer defineDb to defineConfig: it infers the client type from connect, so disconnect and onReady receive a typed db.

Connect with Drizzle

Install Drizzle and the postgres.js driver:

pnpm add drizzle-orm postgres
pnpm add -D drizzle-kit

Describe the tables in plain Drizzle.

// server/db/schema.ts
import { integer, pgTable, text, timestamp } from 'drizzle-orm/pg-core'

export const projects = pgTable('projects', {
  id: text('id').primaryKey(),
  teamId: text('team_id').notNull(),
  name: text('name').notNull(),
})

export const orders = pgTable('orders', {
  id: text('id').primaryKey(),
  teamId: text('team_id').notNull(),
  customerEmail: text('customer_email').notNull(),
  totalCents: integer('total_cents').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

Fill in config/db.ts and export the client type as AppDb.

// config/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { defineDb } from '@kitr/db'
import * as schema from '#server/db/schema'

const createDb = () => drizzle(postgres(process.env.DATABASE_URL!), { schema })

/** The client every controller and action gets from useDb<AppDb>(). */
export type AppDb = ReturnType<typeof createDb>

export default defineDb({
  connect: () => createDb(),
  disconnect: (db) => db.$client.end(),
})

Point DATABASE_URL in .env at a running database:

DATABASE_URL=postgres://ada@localhost:5432/acme

Start the server with kitr dev. connect runs during boot, before the first request. If Postgres is not running, the server does not start and the log shows the driver's error, such as ECONNREFUSED.

Query from a controller

In a controller, pass ctx.app to useDb with your client type:

// server/controllers/orders-controller.ts
import { desc, eq } from 'drizzle-orm'
import type { HttpContext } from '@kitr/api'
import { useDb } from '@kitr/db'
import type { AppDb } from '#config/db'
import { orders } from '#server/db/schema'

export default class OrdersController {
  async index({ app, params }: HttpContext<{ teamId: string }>) {
    const db = useDb<AppDb>(app)
    return db
      .select()
      .from(orders)
      .where(eq(orders.teamId, params.teamId))
      .orderBy(desc(orders.createdAt))
  }
}

Register the route as usual with api:

// server/routes.ts
import { defineRoutes } from '@kitr/api'
import OrdersController from '#server/controllers/orders-controller'

export const routes = defineRoutes((router) =>
  router.get('/api/teams/:teamId/orders', [OrdersController, 'index']),
)

A request returns the rows as JSON:

curl http://localhost:3001/api/teams/team_acme/orders

[{ "id": "ord_1", "teamId": "team_acme", "customerEmail": "[email protected]", "totalCents": 4900, "createdAt": "2026-11-02T09:14:00.000Z" }]

Every request shares the same pooled client, not a new connection.

Query from an action

An action handler has no ctx; get the request's app with useRequestApp from @kitr/server:

// app/actions/projects/rename.ts
import { eq } from 'drizzle-orm'
import { z } from 'zod'
import { defineMutation } from '@kitr/actions'
import { useDb } from '@kitr/db'
import { useRequestApp } from '@kitr/server'
import { app } from '#app/app'
import type { AppDb } from '#config/db'
import { projects } from '#server/db/schema'

export const renameProject = defineMutation('projects.rename', {
  input: z.object({ id: z.string(), name: z.string().min(2) }),
  effects: (project) => ({ invalidate: [['projects', 'list']], set: [{ key: ['projects', 'detail', project.id], data: project }] }),
  handler: async ({ input }) => {
    const db = useDb<AppDb>(useRequestApp(app))
    const [project] = await db
      .update(projects)
      .set({ name: input.name })
      .where(eq(projects.id, input.id))
      .returning()
    return project
  },
})

Query from a service or job

Outside a request, such as a scheduled job, pass the root app to useDb:

// server/services/revenue.ts
import { eq, sum } from 'drizzle-orm'
import type { KitrApp } from '@kitr/kitr/app'
import { useDb } from '@kitr/db'
import type { AppDb } from '#config/db'
import { orders } from '#server/db/schema'

/** Total revenue for one team, in cents. */
export async function teamRevenue(app: KitrApp, teamId: string): Promise<number> {
  const db = useDb<AppDb>(app)
  const [row] = await db
    .select({ total: sum(orders.totalCents) })
    .from(orders)
    .where(eq(orders.teamId, teamId))
  return Number(row?.total ?? 0)
}

Taking app as a parameter keeps the function testable (see Testing).

Read the client without a type

useDb<T>(app) is a typed shortcut for app.container.use('db'):

// server/controllers/health-controller.ts
import type { HttpContext } from '@kitr/api'

export default class HealthController {
  async show({ app }: HttpContext) {
    const db = app.container.use('db') // typed as unknown
    return { ok: db !== undefined }
  }
}

Without the provider in providers, both throw No binding named "db".

Await the client before boot finishes

The provider resolves connect during app.boot(), so useDb is synchronous after boot. Code that runs earlier, such as another provider's register, must await the binding:

// app/providers/project_stats.ts
import { defineProvider } from '@kitr/kitr/app'
import type { AppDb } from '#config/db'
import { projects } from '#server/db/schema'

export default defineProvider({
  name: 'projectStats',
  register(app) {
    app.container.singleton('projectStats', async (container) => {
      const db = (await container.make('db')) as AppDb
      return { count: async () => (await db.select().from(projects)).length }
    })
  },
})

Connect other ORMs

Any library works: whatever connect returns is the client.

Prisma

pnpm add @prisma/client
pnpm add -D prisma

// config/db.ts
import { PrismaClient } from '@prisma/client'
import { defineDb } from '@kitr/db'

const createDb = () => new PrismaClient()
export type AppDb = ReturnType<typeof createDb>

export default defineDb({
  connect: async () => {
    const prisma = createDb()
    await prisma.$connect()
    return prisma
  },
  disconnect: (prisma) => prisma.$disconnect(),
})

Calling $connect() makes a bad URL fail boot instead of the first query. See the Prisma Client docs.

Kysely

pnpm add kysely pg
pnpm add -D @types/pg

// config/db.ts
import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'
import { defineDb } from '@kitr/db'
import type { Database } from '#server/db/types'

export type AppDb = Kysely<Database>

export default defineDb({
  connect: (): AppDb =>
    new Kysely<Database>({
      dialect: new PostgresDialect({ pool: new Pool({ connectionString: process.env.DATABASE_URL }) }),
    }),
  disconnect: (db) => db.destroy(),
})

Database is your table interface, by hand or from kysely-codegen. See the Kysely docs.

A plain pg pool

pnpm add pg
pnpm add -D @types/pg

// config/db.ts
import { Pool } from 'pg'
import { defineDb } from '@kitr/db'

export type AppDb = Pool

export default defineDb({
  connect: async () => {
    const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 })
    await pool.query('select 1')
    return pool
  },
  disconnect: (pool) => pool.end(),
})

The select 1 checks the URL during boot, because a Pool opens no connection until its first query. See the pooling docs.

SQLite with Drizzle

pnpm add drizzle-orm better-sqlite3
pnpm add -D @types/better-sqlite3

// config/db.ts
import Database from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { defineDb } from '@kitr/db'
import * as schema from '#server/db/schema'

const createDb = () => drizzle(new Database(process.env.DATABASE_URL ?? 'acme.db'), { schema })
export type AppDb = ReturnType<typeof createDb>

export default defineDb({
  connect: () => createDb(),
  disconnect: (db) => db.$client.close(),
})

Here DATABASE_URL is a file path such as acme.db, and tables come from drizzle-orm/sqlite-core. See Drizzle with SQLite.

Validate DATABASE_URL at startup

A missing process.env.DATABASE_URL! crashes deep inside the driver. Declare it in the typed env so the app refuses to start with a clear message:

// app/env.ts
import { createEnv, env as e } from '@kitr/kitr/env'

export const env = createEnv({
  public: {},
  server: {
    DATABASE_URL: e.url(),
  },
})

// config/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { defineDb } from '@kitr/db'
import { env } from '#app/env'
import * as schema from '#server/db/schema'

const createDb = () => drizzle(postgres(env.DATABASE_URL), { schema })
export type AppDb = ReturnType<typeof createDb>

export default defineDb({
  connect: () => createDb(),
  disconnect: (db) => db.$client.end(),
})

Change settings per environment

Read app.isProduction or app.environment in connect to size the pool or turn on TLS only where it matters:

// config/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { defineDb } from '@kitr/db'
import * as schema from '#server/db/schema'

const createDb = (production: boolean) =>
  drizzle(
    postgres(process.env.DATABASE_URL!, {
      max: production ? 20 : 5,
      ssl: production ? 'require' : false,
    }),
    { schema },
  )
export type AppDb = ReturnType<typeof createDb>

export default defineDb({
  connect: (app) => createDb(app.isProduction),
  disconnect: (db) => db.$client.end(),
})

Check migrations when the server starts

onReady runs once after boot, when the server starts serving. Use it to warn about, or refuse to run with, an outdated schema:

// config/db.ts
import { sql } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { defineDb } from '@kitr/db'
import * as schema from '#server/db/schema'

const createDb = () => drizzle(postgres(process.env.DATABASE_URL!), { schema })
export type AppDb = ReturnType<typeof createDb>

const EXPECTED_MIGRATIONS = 12

export default defineDb({
  connect: () => createDb(),
  disconnect: (db) => db.$client.end(),
  onReady: async (db, app) => {
    const rows = await db.execute(sql`select count(*)::int as count from drizzle.__drizzle_migrations`)
    const applied = Number(rows[0]?.count ?? 0)
    if (applied < EXPECTED_MIGRATIONS) {
      if (app.isProduction) throw new Error(`Database is ${EXPECTED_MIGRATIONS - applied} migrations behind`)
      app.container.use('logger').warn(`Run pnpm db:migrate: ${EXPECTED_MIGRATIONS - applied} pending`)
    }
  },
})

onReady does not run under createTestApp. The logger comes from logger.

Run migrations

Your ORM's migration tools work unchanged. Add scripts to package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio"
  }
}

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './server/db/schema.ts',
  out: './server/db/migrations',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
})

Run pnpm db:migrate in your deploy step, before the new server starts. See Drizzle migrations.

Use a transaction

Transactions belong to your ORM; call its API on the client from useDb:

// server/services/checkout.ts
import { eq } from 'drizzle-orm'
import type { KitrApp } from '@kitr/kitr/app'
import { useDb } from '@kitr/db'
import type { AppDb } from '#config/db'
import { orders, projects } from '#server/db/schema'

export async function placeOrder(app: KitrApp, input: { id: string; teamId: string; projectId: string }) {
  const db = useDb<AppDb>(app)
  return db.transaction(async (tx) => {
    const [project] = await tx.select().from(projects).where(eq(projects.id, input.projectId))
    if (!project) throw new Error(`Project ${input.projectId} not found`)
    const [order] = await tx
      .insert(orders)
      .values({ id: input.id, teamId: input.teamId, customerEmail: '[email protected]', totalCents: 4_900 })
      .returning()
    return order
  })
}

Close the connection on shutdown

On SIGINT or SIGTERM the server stops accepting requests, then calls your disconnect, so open queries finish before the pool closes:

// server/main.ts
import { mountRoutes } from '@kitr/api'
import { createServer } from '@kitr/server'
import { config } from '../config'
import rc from '../kitrrc'
import { routes } from './routes'

export const server = createServer({ providers: rc.providers, config })
mountRoutes(server, routes)

await server.listen({ port: 3001 })
// Ctrl-C logs "SIGINT received, shutting down", then calls disconnect(db, app).

Without disconnect, the database sees connections drop instead of closing.

Errors

Driver errors (ECONNREFUSED, authentication failures) pass through unchanged. These come from a wrong database binding:

Testing

Build the app with createTestApp from testing and give config a db whose connect returns a fake. Code under test calls useDb unchanged. This tests teamRevenue from "Query from a service or job":

// server/services/revenue.test.ts
import { dbProvider, defineDb } from '@kitr/db'
import { createTestApp } from '@kitr/testing'
import { describe, expect, it, vi } from 'vitest'
import type { AppDb } from '#config/db'
import { teamRevenue } from '#server/services/revenue'

describe('teamRevenue', () => {
  it('sums the team orders and closes the client on shutdown', async () => {
    const where = vi.fn(async () => [{ total: '16900' }])
    const fakeDb = { select: () => ({ from: () => ({ where }) }) } as unknown as AppDb
    const disconnect = vi.fn()

    const app = await createTestApp({
      providers: [dbProvider],
      config: { db: defineDb({ connect: () => fakeDb, disconnect }) },
    })

    expect(await teamRevenue(app, 'team_acme')).toBe(16_900)
    expect(where).toHaveBeenCalledOnce()

    await app.shutdown()
    expect(disconnect).toHaveBeenCalledWith(fakeDb, app)
  })
})

createTestApp runs connect but not onReady; call await app.ready() for that. To replace the client in an app that has booted, bind a value over it:

// test/orders.test.ts
import { dbProvider, defineDb, useDb } from '@kitr/db'
import { createTestApp } from '@kitr/testing'
import { expect, it } from 'vitest'

it('uses the stub bound after boot', async () => {
  const app = await createTestApp({
    providers: [dbProvider],
    config: { db: defineDb({ connect: () => ({ name: 'real' }) }) },
  })
  app.container.value('db', { name: 'stub' })
  expect(useDb<{ name: string }>(app)).toEqual({ name: 'stub' })
})

Bind after boot, not before: the provider's register replaces anything bound under db.

For real queries, point DATABASE_URL at a throwaway database in .env.test, or use PGlite, which runs Postgres in-process through drizzle-orm/pglite. To test a controller end to end, call it with createTestClient(server) from testing.

Next steps