Type-safe Postgres queries

We like writing SQL. What we do not like is the gap between a query and the TypeScript that consumes it — the place where a renamed column silently becomes undefined three layers up the stack. Here is how we close that gap without reaching for an ORM.

Why not an ORM

ORMs trade SQL you can read for an abstraction you fight at the edges. For anything past simple CRUD we end up dropping to raw queries anyway. So we keep the SQL and put the types around it.

One row shape, declared once

Every query lives in a function that owns both the SQL and the shape it returns:

type Post = { id: number; slug: string; title: string; draft: boolean };

async function getPost(slug: string): Promise<Post | null> {
  const { rows } = await db.query(
    'SELECT id, slug, title, draft FROM posts WHERE slug = $1',
    [slug]
  );
  return rows[0] ?? null;
}

The annotation is the contract. Callers get a real Post, the "not found" case is explicit, and the parameterized query keeps it injection-safe.

Rules that keep it honest

• Always parameterize. User input goes in the values array, never the query string.
• Select columns explicitly. A wildcard select turns the row type into a guess.
• Map at the boundary. Convert bigint ids and epoch timestamps to numbers once, in the data layer, so the rest of the app sees clean values.

The payoff

When a migration renames a column, the query function stops compiling — not the page that rendered it. That is the whole point: catch the break in a diff, where it happened, instead of in production.