@repo/db

@repo/db is the central persistence layer. It uses Drizzle ORM for schema definitions and migrations, and Kysely as the runtime query builder for full SQL control with type safety. All database access goes through typed repository objects whose methods return Safe<T> results.

Dependencies

graph TD
  db["@repo/db"] --> errors["@repo/errors"]
  db --> logger["@repo/logger"]
  db --> safe["@repo/safe"]
  db -.-> typescript_config["@repo/typescript-config"]
  db -.-> vitest_config["@repo/vitest-config"]

Architecture

graph LR
subgraph schema [Schema Layer]
Drizzle["Drizzle ORM"]
Migrations["drizzle-kit migrations"]
end

subgraph runtime [Runtime Layer]
Kysely["Kysely Query Builder"]
Repos["Repository Objects"]
end
subgraph bridge [Type Bridge]
D2K["DrizzleToKyesely"]
end
Drizzle --> D2K
D2K --> Kysely
Kysely --> Repos
Drizzle --> Migrations

• Drizzle defines tables, columns, enums, and relations in src/drizzle/schema/. It also drives migrations via drizzle-kit.
• Kysely is the runtime query builder. A shared db singleton (Kysely<DrizzleToKyesely>) connects to Postgres via pg.Pool.
• DrizzleToKyesely is an auto-generated type bridge that maps Drizzle table definitions to Kysely's type system (with snake_case column names matching Postgres).
• Repos are plain objects created with createDbRepo(). Each method uses Kysely to run queries, and the wrapper automatically converts results into Safe<T>.

Exports

Usage

import { patientsRepo } from "@repo/db";

const result = await patientsRepo.getById(patientId);
if (result.error) {
  // handle error -- result.error is a CircleError
  return;
}
const patient = result.data;
Copy

Creating a New Repo

Repos follow a consistent pattern using createDbRepo. Here is the simplest real example (entities.repo.ts):

// src/repos/my-thing.repo.ts
import type { MyThingId, MyThingInsertParams } from "../drizzle/schema/tables/my-thing/my-thing.table";
import { createDbRepo } from "../lib/db-repo";

export const myThingRepo = createDbRepo("myThing", ({ db }) => ({
  getById: async (id: MyThingId) => {
    return await db
      .selectFrom("my_thing")
      .selectAll()
      .where("id", "=", id)
      .executeTakeFirstOrThrow();
  },

  create: async (params: MyThingInsertParams) => {
    return await db
      .insertInto("my_thing")
      .values(params)
      .returningAll()
      .executeTakeFirstOrThrow();
  },
}));
Copy

Then register it in src/repos/index.ts:

export * from "./my-thing.repo";
Copy

Key points:

1. Import branded ID types and insert/update param types from the Drizzle table definition.
2. Call createDbRepo("name", ({ db }) => ({ ... })) -- the db parameter is a typed Kysely instance.
3. Write methods as plain async functions that use Kysely. Don't wrap in try/catch -- createDbRepo does that automatically and returns Safe<T>.
4. Export the repo and add it to the barrel file.

Repo Method Patterns

Get by ID

Use executeTakeFirstOrThrow() -- if no row is found, createDbRepo converts the NoResultError into a DbNotFoundError.

getById: async (id: PatientId) => {
  return await db
    .selectFrom("patient")
    .selectAll()
    .where("patient_id", "=", id)
    .executeTakeFirstOrThrow();
},
Copy

List with filters and pagination

Use execute() for lists. Build queries conditionally for optional filters.

listInOrganization: async (params: ListFacilitiesParams) => {
  const { entityId, search, limit = 50, offset = 0 } = params;
  let query = db
    .selectFrom("facilities")
    .where("entity_id", "=", entityId)
    .select(["id", "name"]);

  if (search?.trim()) {
    query = query.where("name", "ilike", `%${search.trim()}%`);
  }

  return await query.orderBy("name").limit(limit).offset(offset).execute();
},
Copy

Create

create: async (params: AuditQuestionInsertParams) => {
  return await db
    .insertInto("audit_question")
    .values(params)
    .returningAll()
    .executeTakeFirstOrThrow();
},
Copy

Update

update: async (id: TemplateId, params: TemplateUpdateParams) => {
  return await db
    .updateTable("templates")
    .set({ ...params, last_updated: parseDbDate(new Date().toISOString()) })
    .where("id", "=", id)
    .returningAll()
    .executeTakeFirstOrThrow();
},
Copy

Delete

delete: async (id: AuditQuestionId) => {
  return await db
    .deleteFrom("audit_question")
    .where("id", "=", id)
    .returningAll()
    .executeTakeFirstOrThrow();
},
Copy

Joins

Use leftJoin with onRef and explicit column selections via select and eb.ref(...).as(...):

getDetail: async (id: PatientId) => {
  return await db
    .selectFrom("patient")
    .leftJoin("profiles", (join) =>
      join.onRef("patient.primary_provider_id", "=", "profiles.id"))
    .leftJoin("facilities", (join) =>
      join.onRef("patient.facility_id", "=", "facilities.id"))
    .where("patient.patient_id", "=", id)
    .select(["patient.patient_id", "patient.first_name", "patient.last_name"])
    .select((eb) => [
      eb.ref("profiles.first_name").as("provider_first_name"),
      eb.ref("facilities.name").as("facility_name"),
    ])
    .executeTakeFirstOrThrow();
},
Copy

Transactions

Use db.transaction().execute() for multi-step operations that must be atomic:

duplicate: async (id: TemplateId, userId: UserId) => {
  return await db.transaction().execute(async (trx) => {
    const original = await trx
      .selectFrom("templates")
      .selectAll()
      .where("id", "=", id)
      .executeTakeFirstOrThrow();

    const newTemplate = await trx
      .insertInto("templates")
      .values({ title: `${original.title} Copy`, created_by: userId })
      .returningAll()
      .executeTakeFirstOrThrow();

    const sections = await trx
      .selectFrom("sections")
      .selectAll()
      .where("template_id", "=", id)
      .execute();

    if (sections.length > 0) {
      await trx
        .insertInto("sections")
        .values(sections.map((s) => ({ ...s, template_id: newTemplate.id })))
        .execute();
    }

    return newTemplate;
  });
},
Copy

Branded IDs

The package uses a branded ID system (@repo/db/db.types) to prevent accidentally mixing IDs from different tables:

import { type Id, createIdParser, zId } from "@repo/db/db.types";

// In a table definition:
export type PatientId = Id<"bigint", "PatientId">;
export const parsePatientId = createIdParser<PatientId>("bigint");
export const zPatientId = zId(parsePatientId);
Copy

• Id<Type, TagName> is a branded type that looks like a plain string or number at runtime but is distinct at the type level.
• createIdParser(idType) creates a parser function for "uuid", "bigint", "string", or "number" IDs.
• zId(parser) creates a Zod schema that validates and transforms raw input into the branded ID type -- useful in tRPC input schemas.

Table definitions also export *InsertParams and *UpdateParams types derived from Kysely's Insertable<T> and Updateable<T>.

Error Handling

createDbRepo wraps every repo method so you never need to write try/catch in repo code:

• Success: returns { data: T, error: null } (a SafeSuccess<T>)
• Not found: Kysely's NoResultError (from executeTakeFirstOrThrow) is converted to DbNotFoundError
• Postgres errors: PostgrestError is converted to UnhandledPostgresError
• Connection errors: DbConnectionError is rethrown (not wrapped in Safe) since the app cannot recover from these
• Other errors: wrapped as UnhandledError

Callers handle errors through the Safe<T> pattern:

const result = await patientsRepo.getById(patientId);

if (result.error) {
  if (result.error instanceof DbNotFoundError) {
    // patient does not exist
  }
  // other error
  return;
}

const patient = result.data;
Copy

Scripts