Source: https://www.hype-stack.dev/docs/backend/prisma-kysely

# 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. Models live as split files under `prisma/schema/`, one per domain, which is also how
packs contribute theirs. Prisma handles:

- **Migrations** with `prisma migrate dev` / `prisma migrate deploy`, wrapped as `migration:create` / `migration:latest`
- **Seeding** with `prisma db seed`
- **Client generation** for TypeScript types
- **Prisma Studio** for visual data browsing

```prisma
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](/docs/backend/migrations).

## Kysely: typed queries

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

```ts
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

| Task                          | Use                           |
| ----------------------------- | ----------------------------- |
| Define schema, add tables     | Prisma (`schema.prisma`)      |
| Run migrations                | Prisma (`prisma migrate dev`) |
| Insert records                | Prisma client                 |
| Read data (queries)           | Kysely                        |
| Complex writes (transactions) | Kysely                        |
| Seed data                     | Prisma                        |

## 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:

```ts
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.