Prisma and Kysely

The backend uses both Prisma and Kysely. They solve different problems and work together.

Connection model

At startup the backend attaches a Postgres client and a Kysely query builder to app context (apps/backend/src/db/). Route handlers and feature modules read that context instead of creating their own pools.

Prisma still owns the schema file and the migration engine. Kysely types come from the Prisma-generated client, so a column rename breaks both migrations and typed queries in the same change.

Feature module
      |
      v
  db/queries or db/mutations
      |
      +-- Prisma client  --> inserts / simple writes
      |
      +-- Kysely qb      --> reads, joins, transactions
      |
      v
  PostgreSQL

Prisma: schema and migrations

Prisma owns the database schema. You define models in prisma/schema.prisma (or split files under prisma/schema/ after packs install), and Prisma handles:

• Migrations with prisma migrate dev / prisma migrate deploy
• Seeding with prisma db seed
• Client generation for TypeScript types
• Prisma Studio for visual data browsing

model Project {
  id             String   @id @default(cuid())
  name           String
  organizationId String
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
}

When you install a pack, the CLI merges that pack's Prisma models into your schema and you re-run migrations. See Migrations.

Kysely: typed queries

Kysely is the query builder. It uses the types generated by Prisma for compile-time checked SQL:

import { postgres } from "@backend/context";

export const getProjectById = async ({ projectId }: { projectId: string }) => {
  return postgres.qb.selectFrom("project").selectAll().where("id", "=", projectId).executeTakeFirst();
};

Every column name, table name, and operator is type-checked. Typos break at compile time.

When to use which

File conventions

• Reads go in features/<domain>/db/queries/
• Writes go in features/<domain>/db/mutations/
• Module functions call db functions instead of using ORM/query builder APIs directly

features/projects/
  db/
    queries/
      get-by-id.query.ts
      get-many.query.ts
    mutations/
      create-project.mutation.ts
      update-project.mutation.ts
      delete-project.mutation.ts

Pagination

Use the shared getPaginatedQuery helper for paginated reads:

import { getPaginatedQuery } from "@backend/utils/database/pagination";

const query = postgres.qb.selectFrom("project").selectAll();
return getPaginatedQuery({ query, limit, offset });

Caveats

• After a schema change, re-run the migrate command so the Prisma client regenerates. Stale client types are the usual reason Kysely "lies" about columns.
• Pack installs can add models and enums. Always migrate after compose or template before starting the app.
• Prefer one transaction for multi-table deletes. See the backend deletion cascade rules in the repo agent guidance.