Building Type-Safe APIs with Drizzle ORM
Why SQL-First Type Safety Matters
Many ORMs hide SQL behind heavy runtime abstractions, which makes query tuning, joins, and transactions harder to reason about in production. Drizzle ORM takes the opposite approach: your schema lives in plain TypeScript, queries mirror standard SQL syntax, and there is zero hidden magic at runtime.
In projects like the Portfolio Backend API, pairing Drizzle ORM with PostgreSQL and Bun + ElysiaJS keeps database queries fast while catching schema bugs at compile time.
Defining Your Schema in TypeScript
Every table, column constraint, and index is declared explicitly in TypeScript:
import { pgTable, serial, text, boolean, timestamp } from "drizzle-orm/pg-core";
export const projects = pgTable("projects", {
id: serial("id").primaryKey(),
slug: text("slug").notNull().unique(),
title: text("title").notNull(),
excerpt: text("excerpt").notNull(),
featured: boolean("featured").default(false).notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
});
export type ProjectRow = typeof projects.$inferSelect;
export type NewProjectRow = typeof projects.$inferInsert;Notice $inferSelect and $inferInsert: your API responses and repository functions automatically stay synchronized with your database columns.
Writing Predictable Queries
Drizzle's query builder reads almost identically to SQL, so you always know what queries hit PostgreSQL:
import { desc, eq } from "drizzle-orm";
import { db } from "./db";
import { projects } from "./schema";
export async function getFeaturedProjects() {
return db
.select({
slug: projects.slug,
title: projects.title,
excerpt: projects.excerpt,
})
.from(projects)
.where(eq(projects.featured, true))
.orderBy(desc(projects.createdAt))
.limit(6);
}Because you explicitly select only the columns needed for list views, you avoid over-fetching large HTML or JSON columns on high-traffic endpoints.
Handling Transactions and Migrations Safely
- Atomic multi-step writes: Wrap financial records, wallet balance updates, or multi-table inserts inside
await db.transaction(async (tx) => { ... })so partial writes never corrupt state. - Versioned SQL migrations: Generate reviewable SQL files with
drizzle-kit generateand commit them to Git before applying withdrizzle-kit migratein CI/CD. - Connection pooling: Reuse a single PostgreSQL pool across requests instead of opening new connections per handler, especially in containerized deployments.
Key Takeaway
If you want full SQL control without giving up TypeScript inference, Drizzle ORM gives you both—and pairs naturally with lightweight runtimes like Bun.