Drizzle ORM Has a Free API That Makes SQL Type-Safe Without the Magic

#typescript #javascript #sql #database

Drizzle ORM is the SQL-first TypeScript ORM. No magic — if you know SQL, you know Drizzle. Its API surface gives you full SQL power with complete type safety.

Schema Definition: SQL as TypeScript

import { pgTable, serial, text, integer, timestamp, jsonb, index } from "drizzle-orm/pg-core";

export const products = pgTable("products", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  price: integer("price").notNull(),
  url: text("url").notNull().unique(),
  metadata: jsonb("metadata").$type<{ category: string; tags: string[] }>(),
  scrapedAt: timestamp("scraped_at").defaultNow(),
}, (table) => ({
  titleIdx: index("title_idx").on(table.title),
  priceIdx: index("price_idx").on(table.price),
}));

export const reviews = pgTable("reviews", {
  id: serial("id").primaryKey(),
  productId: integer("product_id").references(() => products.id),
  rating: integer("rating").notNull(),
  comment: text("comment"),
});

Query Builder: SQL That Types Itself

import { db } from "./db";
import { products, reviews } from "./schema";
import { eq, gt, lt, and, desc, sql, count, avg } from "drizzle-orm";

// Select with conditions
const cheapProducts = await db
  .select()
  .from(products)
  .where(and(gt(products.price, 10), lt(products.price, 50)))
  .orderBy(desc(products.scrapedAt))
  .limit(20);

// Joins
const productsWithReviews = await db
  .select({
    title: products.title,
    price: products.price,
    avgRating: avg(reviews.rating),
    reviewCount: count(reviews.id),
  })
  .from(products)
  .leftJoin(reviews, eq(products.id, reviews.productId))
  .groupBy(products.id)
  .having(gt(count(reviews.id), 5));

Relational Queries API

import { relations } from "drizzle-orm";

export const productsRelations = relations(products, ({ many }) => ({
  reviews: many(reviews),
}));

export const reviewsRelations = relations(reviews, ({ one }) => ({
  product: one(products, {
    fields: [reviews.productId],
    references: [products.id],
  }),
}));

// Query with nested relations
const result = await db.query.products.findMany({
  with: {
    reviews: {
      where: gt(reviews.rating, 3),
      limit: 5,
    },
  },
  where: gt(products.price, 20),
});

Migrations: drizzle-kit

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push directly (dev)
npx drizzle-kit push

# Studio — visual DB browser
npx drizzle-kit studio

Raw SQL When You Need It

const result = await db.execute(sql`
  SELECT title, price,
    price - LAG(price) OVER (ORDER BY scraped_at) as price_change
  FROM products
  WHERE url = ${targetUrl}
  ORDER BY scraped_at DESC
  LIMIT 10
`);

Prepared Statements

const findByPrice = db
  .select()
  .from(products)
  .where(and(gt(products.price, sql.placeholder("min")), lt(products.price, sql.placeholder("max"))))
  .prepare("find_by_price");

const results = await findByPrice.execute({ min: 10, max: 50 });