Chapter 25: Databases with Type Safety

Databases shape the heart of many Node services. When you combine TypeScript’s type system with the structure of your tables, you gain a calmer way to reason about queries, migrations, and data access layers. This chapter looks at typed SQL patterns, gives an overview of popular ORMs and query builders, covers schema aware migrations, and shows how to write predictable tests with lightweight factories.

Type safe SQL access patterns

Writing SQL directly can feel close to the metal, yet type safety is not lost when you use the right tools. Several libraries parse SQL template literals and infer result types through TypeScript. This lets you keep full control over your queries while still enjoying strong compile time guarantees.

Tagged template SQL helpers

Libraries such as slonik or @databases/pg let you mark SQL with a special function. These helpers validate parameters and return rows with inferred shapes. This keeps your queries readable without sacrificing safety.

const rows = await sql<{ id: number; name: string }>`
  select id, name
  from items
  where id = ${itemId}
`;

Parameter binding and injection safety

Using parameters avoids the hazards of string concatenation. Query helpers insert values safely and the database driver handles the binding. The type checker ensures that each placeholder receives the right kind of value.

ORM and query builders overview

Object relational mappers and query builders offer another route to type safety. They generate queries behind the scenes and produce TypeScript types that follow your schema. Each tool balances convenience, performance, and flexibility in different ways.

Prisma and its generated client

Prisma reads a declarative schema file and generates a client that exposes strongly typed CRUD operations. The client tracks relations, nullability, enums, and field types. This approach suits teams that want predictable queries and a clear domain model.

const item = await prisma.item.findUnique({
  where: { id: itemId }
});

Drizzle, Kysely, and other query builders

Query builders like Drizzle and Kysely let you write SQL shaped code with full type inference. They avoid the sometimes heavy runtime of ORMs and give you control over the generated queries. This works well when you want type safety without a high level abstraction layer.

const rows = await db
  .selectFrom("items")
  .select(["id", "name"])
  .where("id", "=", itemId)
  .execute();

Migrations and schema types

Schema changes move in step with your code. Type safe migrations describe how tables evolve over time and let you synchronize your database structure across environments. Many migration tools integrate directly with TypeScript or allow you to attach types to the schema definitions they generate.

Code first migrations

Tools such as Prisma Migrate or Drizzle Kit create migrations based on your schema files. They detect differences, generate SQL, and give you a structured history of changes. This makes upgrades predictable and prevents accidental drift.

npx prisma migrate dev --name add_price_column

Schema types across services

When multiple services share tables or common views, export types that match your schema. These types reflect the authoritative shape of your data and help independent teams coordinate changes.

export interface Item {
  id: number;
  name: string;
  price: number;
}

Testing data layers with factories

Database tests become lighter when you generate fixtures with small factories. These factories create valid records that follow your schema, which keeps test setup quick and expressive. The type system ensures that each record matches the shape of your domain.

Simple factories with overrides

A factory returns an object that fits a table or entity. Optional overrides give you control over edge cases. This avoids duplicating large data blocks across tests.

function makeItem(overrides: Partial<Item> = {}): Item {
  return {
    id: 1,
    name: "Example",
    price: 10,
    ...overrides
  };
}

Seeding the database for integration tests

For integration tests that use a real database, seed the tables with a small number of stable fixtures. Clear the tables between runs so tests do not bleed into one another. The type system makes sure your fixtures stay aligned with the evolving schema.