Drizzle ORM: The Lightweight Alternative

For .NET engineers who know: Dapper, ADO.NET, and the tradeoff between ORM abstraction and SQL control You’ll learn: How Drizzle sits closer to SQL than Prisma — you write TypeScript that looks like SQL — and when to choose it over Prisma Time: 15-20 min read

The .NET Way (What You Already Know)

You have used both Dapper and EF Core, and you know why each exists. Dapper is thin: you write SQL, pass parameters, and get back typed objects. EF Core is thick: it manages your schema, generates SQL, tracks changes, and gives you LINQ. The tradeoff is control vs. productivity.

Dapper is the right tool when:

• Your queries are complex enough that EF Core’s generated SQL is wrong or slow
• You are working with an existing database you do not own
• You need stored procedures, table-valued parameters, or multi-result-set queries
• Performance is critical and you want to see exactly what SQL is running

EF Core is the right tool when:

• You are building a new schema and want migrations managed for you
• Your queries are straightforward CRUD with relations
• You want to move fast without writing SQL for every operation

Drizzle occupies the same space as Dapper in this tradeoff, but it is not a thin ADO.NET wrapper. It is a full query builder that:

• Lets you define schemas in TypeScript (no separate schema file)
• Generates SQL that looks exactly like the SQL you would write by hand
• Infers TypeScript types from your schema definitions
• Has a migration tool (drizzle-kit) that generates SQL migration files

Think of it as Dapper with typed schema definitions and a query builder API.

The Drizzle Way

Installation

npm install drizzle-orm
npm install drizzle-kit --save-dev

# PostgreSQL driver
npm install postgres
# or
npm install pg @types/pg

Drizzle supports multiple drivers: postgres (postgres.js), pg (node-postgres), @neondatabase/serverless (Neon’s HTTP driver for serverless), @planetscale/database (PlanetScale MySQL), better-sqlite3 (SQLite), and others. You pick the driver; Drizzle wraps it.

Defining Schemas in TypeScript

This is Drizzle’s most distinctive feature compared to Prisma. There is no separate .prisma file. Your schema is TypeScript code in .ts files — typically in a src/db/schema.ts file or split across domain files.

// src/db/schema.ts
import {
  pgTable,
  serial,
  text,
  varchar,
  numeric,
  boolean,
  timestamp,
  integer,
  uuid,
  index,
  uniqueIndex,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const customers = pgTable(
  'customers',
  {
    id: serial('id').primaryKey(),
    externalRef: uuid('external_ref').defaultRandom().notNull(),
    fullName: text('full_name').notNull(),
    email: varchar('email', { length: 320 }).notNull(),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (table) => ({
    emailIdx: uniqueIndex('uq_customers_email').on(table.email),
  })
);

export const orders = pgTable(
  'orders',
  {
    id: serial('id').primaryKey(),
    customerId: integer('customer_id')
      .notNull()
      .references(() => customers.id),
    total: numeric('total', { precision: 12, scale: 2 }).notNull(),
    status: text('status').notNull().default('pending'),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (table) => ({
    customerIdx: index('idx_orders_customer_id').on(table.customerId),
  })
);

export const orderItems = pgTable('order_items', {
  id: serial('id').primaryKey(),
  orderId: integer('order_id')
    .notNull()
    .references(() => orders.id),
  productId: integer('product_id')
    .notNull()
    .references(() => products.id),
  quantity: integer('quantity').notNull(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
});

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  sku: varchar('sku', { length: 50 }).notNull().unique(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
});

// Relations — for the query builder's join inference
export const customersRelations = relations(customers, ({ many }) => ({
  orders: many(orders),
}));

export const ordersRelations = relations(orders, ({ one, many }) => ({
  customer: one(customers, {
    fields: [orders.customerId],
    references: [customers.id],
  }),
  items: many(orderItems),
}));

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
  }),
  product: one(products, {
    fields: [orderItems.productId],
    references: [products.id],
  }),
}));

Drizzle column types for PostgreSQL:

Setting Up the Client

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const connectionString = process.env.DATABASE_URL!;

// For queries (with connection pool)
const queryClient = postgres(connectionString);
export const db = drizzle(queryClient, { schema });

// For migrations only (single connection)
export const migrationClient = postgres(connectionString, { max: 1 });

The schema object passed to drizzle() enables relational queries (the with API described below). Without it, you can still use the query builder but not the relational API.

The Query Builder API: SQL in TypeScript

Drizzle’s query API is designed to mirror SQL syntax. If you know SQL, the query builder feels immediately familiar — more so than Prisma’s nested object style.

import { db } from './db';
import { orders, customers, orderItems } from './db/schema';
import { eq, gt, and, desc, sql, count, sum } from 'drizzle-orm';

// SELECT * FROM orders WHERE id = 1
const order = await db.select().from(orders).where(eq(orders.id, 1));

// SELECT with specific columns
const result = await db
  .select({
    id: orders.id,
    total: orders.total,
    customerName: customers.fullName,
  })
  .from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id))
  .where(and(gt(orders.total, '100'), eq(orders.status, 'active')))
  .orderBy(desc(orders.createdAt))
  .limit(20)
  .offset(40);

// INSERT
const [newOrder] = await db
  .insert(orders)
  .values({
    customerId: 1,
    total: '249.99',
    status: 'pending',
  })
  .returning(); // returns the inserted row — like EF Core's SaveChanges + re-read

// INSERT multiple rows
const inserted = await db
  .insert(products)
  .values([
    { name: 'Widget A', sku: 'WGT-001', price: '9.99' },
    { name: 'Widget B', sku: 'WGT-002', price: '19.99' },
  ])
  .returning({ id: products.id, sku: products.sku });

// UPDATE
const [updated] = await db
  .update(orders)
  .set({ status: 'shipped' })
  .where(eq(orders.id, 1))
  .returning();

// DELETE
const deleted = await db
  .delete(orders)
  .where(eq(orders.id, 1))
  .returning();

// Aggregates
const [stats] = await db
  .select({
    orderCount: count(orders.id),
    totalRevenue: sum(orders.total),
  })
  .from(orders)
  .where(eq(orders.status, 'completed'));

The operators (eq, gt, and, desc, etc.) are imported from drizzle-orm and compose cleanly. This is much closer to writing SQL by hand than Prisma’s object-based API.

Joins

Drizzle handles joins explicitly, unlike Prisma’s include. You write the join yourself and select the columns you want:

import { eq, and } from 'drizzle-orm';

// INNER JOIN
const ordersWithCustomers = await db
  .select({
    orderId: orders.id,
    total: orders.total,
    customerName: customers.fullName,
    customerEmail: customers.email,
  })
  .from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id))
  .where(eq(orders.status, 'active'));

// LEFT JOIN (Dapper equivalent: you handle the null yourself)
const customersWithOrders = await db
  .select({
    customerId: customers.id,
    customerName: customers.fullName,
    orderId: orders.id,      // will be null for customers with no orders
    orderTotal: orders.total,
  })
  .from(customers)
  .leftJoin(orders, eq(customers.id, orders.customerId));

// Multiple joins
const orderDetails = await db
  .select({
    orderId: orders.id,
    total: orders.total,
    customerName: customers.fullName,
    productName: products.name,
    quantity: orderItems.quantity,
    itemPrice: orderItems.price,
  })
  .from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id))
  .innerJoin(orderItems, eq(orderItems.orderId, orders.id))
  .innerJoin(products, eq(orderItems.productId, products.id))
  .where(eq(orders.id, 42));

Relational Query API (the with API)

Writing joins manually is powerful but verbose for simple cases. Drizzle also provides a higher-level relational API that looks more like Prisma when you pass schema to the drizzle() constructor:

// Uses the relations you defined in schema.ts
const orderWithRelations = await db.query.orders.findFirst({
  where: eq(orders.id, 42),
  with: {
    customer: true,
    items: {
      with: {
        product: true,
      },
    },
  },
});

// Result is fully typed — TypeScript knows the shape
// orderWithRelations.customer.fullName
// orderWithRelations.items[0].product.name

The with API is similar to Prisma’s include. Use the join-based query builder when you need precise column selection, aggregation, or complex conditions. Use the with API when you want to load an entity graph without writing joins manually.

Subqueries

Drizzle supports subqueries natively — something Prisma cannot express without raw SQL:

import { sql, inArray } from 'drizzle-orm';

// Subquery in WHERE
const topCustomerIds = db
  .select({ id: customers.id })
  .from(customers)
  .where(eq(customers.isActive, true))
  .limit(10)
  .as('top_customers'); // alias the subquery

const topCustomerOrders = await db
  .select()
  .from(orders)
  .where(inArray(orders.customerId, db.select({ id: topCustomerIds.id }).from(topCustomerIds)));

// Scalar subquery in SELECT
const ordersWithCounts = await db
  .select({
    id: orders.id,
    total: orders.total,
    itemCount: sql<number>`(
      SELECT COUNT(*) FROM ${orderItems} WHERE ${orderItems.orderId} = ${orders.id}
    )`.as('item_count'),
  })
  .from(orders);

CTEs (Common Table Expressions)

import { sql, with as withCte } from 'drizzle-orm';

// Using $with for CTEs
const highValueCustomers = db.$with('high_value_customers').as(
  db
    .select({ customerId: orders.customerId, total: sum(orders.total).as('lifetime_value') })
    .from(orders)
    .groupBy(orders.customerId)
    .having(sql`sum(${orders.total}) > 1000`)
);

const result = await db
  .with(highValueCustomers)
  .select({
    customerName: customers.fullName,
    email: customers.email,
    lifetimeValue: highValueCustomers.total,
  })
  .from(highValueCustomers)
  .innerJoin(customers, eq(customers.id, highValueCustomers.customerId));

Transactions

// Drizzle transaction — wraps a callback, auto-rollback on throw
const result = await db.transaction(async (tx) => {
  const [order] = await tx
    .insert(orders)
    .values({ customerId: 1, total: '99.99', status: 'pending' })
    .returning();

  await tx.insert(orderItems).values({
    orderId: order.id,
    productId: 5,
    quantity: 2,
    price: '49.99',
  });

  // If anything throws here, both inserts are rolled back
  return order;
});

// Savepoints (nested transactions)
await db.transaction(async (tx) => {
  await tx.insert(orders).values({ customerId: 1, total: '50.00', status: 'pending' });

  try {
    await tx.transaction(async (nested) => {
      // This inner transaction can be rolled back independently
      await nested.insert(orderItems).values({ orderId: 999, productId: 5, quantity: 1, price: '50.00' });
    });
  } catch {
    // Inner transaction failed (FK violation, etc.) — outer continues
    console.warn('Item insert failed, continuing without it');
  }
});

Raw SQL

Drizzle provides sql tagged template literal for inline SQL expressions and for fully raw queries:

import { sql } from 'drizzle-orm';

// Inline SQL expression (used within query builder)
const result = await db
  .select({
    id: orders.id,
    formattedTotal: sql<string>`'$' || ${orders.total}::text`,
  })
  .from(orders);

// Fully raw query — typed
const rawResult = await db.execute<{ id: number; total: string }>(
  sql`SELECT id, total FROM orders WHERE created_at > NOW() - INTERVAL '30 days'`
);

// Raw with parameters (safe — parameterized)
const customerId = 42;
const rawOrders = await db.execute<{ id: number; total: string }>(
  sql`SELECT id, total FROM orders WHERE customer_id = ${customerId}`
);

Migrations with drizzle-kit

# drizzle.config.ts — configuration file

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './drizzle',              // where migration files are written
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

# Generate migration SQL from schema changes
npx drizzle-kit generate

# Apply migrations to the database
npx drizzle-kit migrate

# Open Drizzle Studio (browser UI for your data)
npx drizzle-kit studio

# Push schema directly to DB without migration files (prototyping only)
npx drizzle-kit push

# Inspect existing database and generate schema
npx drizzle-kit introspect

Unlike Prisma, drizzle-kit generate only generates the SQL file. It does not apply it. You apply with drizzle-kit migrate or by running the SQL yourself. This gives you more control but requires more steps.

The generated migration file is plain SQL:

-- drizzle/0000_initial.sql
CREATE TABLE IF NOT EXISTS "customers" (
  "id" serial PRIMARY KEY NOT NULL,
  "external_ref" uuid DEFAULT gen_random_uuid() NOT NULL,
  "full_name" text NOT NULL,
  "email" varchar(320) NOT NULL,
  "is_active" boolean DEFAULT true NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "uq_customers_email" ON "customers" ("email");

You can read, edit, and version-control these files directly. This is both a feature and a responsibility: Drizzle trusts you to know what the SQL does.

Drizzle vs Prisma: Side-by-Side

Same operations expressed in both ORMs. Use this to calibrate which API you prefer for a given task.

Simple find:

// Prisma
const order = await prisma.order.findUnique({ where: { id: 42 } });

// Drizzle
const [order] = await db.select().from(orders).where(eq(orders.id, 42));

Filter and sort:

// Prisma
const results = await prisma.order.findMany({
  where: { status: 'active', total: { gt: 100 } },
  orderBy: { createdAt: 'desc' },
  take: 20,
});

// Drizzle
const results = await db
  .select()
  .from(orders)
  .where(and(eq(orders.status, 'active'), gt(orders.total, '100')))
  .orderBy(desc(orders.createdAt))
  .limit(20);

Join with selected columns:

// Prisma — always returns full models; use select to trim
const results = await prisma.order.findMany({
  select: {
    id: true,
    total: true,
    customer: { select: { fullName: true } },
  },
});

// Drizzle — explicit join, explicit column selection
const results = await db
  .select({
    id: orders.id,
    total: orders.total,
    customerName: customers.fullName,
  })
  .from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id));

Create:

// Prisma
const order = await prisma.order.create({
  data: { customerId: 1, total: 249.99, status: 'pending' },
});

// Drizzle
const [order] = await db
  .insert(orders)
  .values({ customerId: 1, total: '249.99', status: 'pending' })
  .returning();

Upsert:

// Prisma
const customer = await prisma.customer.upsert({
  where: { email },
  create: { email, fullName: name },
  update: { fullName: name },
});

// Drizzle
import { onConflictDoUpdate } from 'drizzle-orm/pg-core';
const [customer] = await db
  .insert(customers)
  .values({ email, fullName: name })
  .onConflictDoUpdate({
    target: customers.email,
    set: { fullName: name },
  })
  .returning();

Aggregate:

// Prisma
const result = await prisma.order.aggregate({
  _sum: { total: true },
  _count: { id: true },
  where: { status: 'completed' },
});

// Drizzle
const [result] = await db
  .select({
    totalRevenue: sum(orders.total),
    orderCount: count(orders.id),
  })
  .from(orders)
  .where(eq(orders.status, 'completed'));

Raw SQL:

// Prisma
const result = await prisma.$queryRaw<{ id: number }[]>`
  SELECT id FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
`;

// Drizzle
const result = await db.execute<{ id: number }>(
  sql`SELECT id FROM orders WHERE created_at > NOW() - INTERVAL '7 days'`
);

Transactions:

// Prisma
await prisma.$transaction(async (tx) => {
  await tx.order.create({ data: { ... } });
  await tx.inventory.update({ ... });
});

// Drizzle
await db.transaction(async (tx) => {
  await tx.insert(orders).values({ ... });
  await tx.update(inventory).set({ ... }).where(eq(inventory.productId, id));
});

When to Choose Drizzle vs Prisma

Both ORMs can handle both categories — this is a spectrum, not a binary. Large projects often use Drizzle for reporting/analytics queries and Prisma for application CRUD, or vice versa. Many projects pick one and use raw SQL for the few cases the ORM cannot handle cleanly.

Key Differences

Gotchas for .NET Engineers

1. db.select() returns an array, not a single object

Drizzle’s select() always returns an array, even when you expect one record. This mirrors SQL’s behavior (a SELECT returns a result set). It will not throw if no rows are found — it returns an empty array.

// Returns Order[] — possibly empty
const result = await db.select().from(orders).where(eq(orders.id, 999));

// Wrong — result is always an array
const order = await db.select().from(orders).where(eq(orders.id, 1));
order.id; // TypeScript error: 'id' does not exist on type 'Order[]'

// Correct — destructure or index
const [order] = await db.select().from(orders).where(eq(orders.id, 1));
if (!order) {
  throw new Error('Order not found');
}

// Or use findFirst from the relational API (returns T | undefined)
const order2 = await db.query.orders.findFirst({
  where: eq(orders.id, 1),
});

This is a consistent departure from EF Core’s FindAsync() / FirstOrDefaultAsync() pattern. You must handle the “not found” case explicitly.

2. Schema changes are not automatically detected — you must run drizzle-kit generate

Unlike Prisma, where editing schema.prisma and running prisma migrate dev creates and applies the migration in one step, Drizzle separates schema definition (TypeScript) from migration generation. Forgetting to run drizzle-kit generate after a schema change means your database and TypeScript types are out of sync, but TypeScript will not tell you — the types reflect your TypeScript code, which you just changed.

# Correct workflow after any schema.ts change:
# 1. Edit src/db/schema.ts
# 2. Generate migration SQL
npx drizzle-kit generate
# 3. Review the generated SQL in drizzle/ directory
# 4. Apply it
npx drizzle-kit migrate
# 5. No separate "generate client" step — types update immediately

3. Numeric/Decimal values come back as strings

PostgreSQL’s NUMERIC/DECIMAL type is returned as a string by the underlying pg and postgres.js drivers to preserve precision (JavaScript number cannot represent large decimals exactly). Drizzle passes this through — it does not wrap in a Decimal object like Prisma does.

// Schema definition
const orders = pgTable('orders', {
  total: numeric('total', { precision: 12, scale: 2 }).notNull(),
});

// Query result
const [order] = await db.select().from(orders).where(eq(orders.id, 1));
console.log(typeof order.total); // 'string'
console.log(order.total);        // '249.99'

// You must parse it explicitly
const total = parseFloat(order.total); // loses precision for very large values
const total2 = new Decimal(order.total); // use decimal.js for financial math

// Type annotation is string — TypeScript is correct
const total3: string = order.total; // no error

Add a .$type<Decimal>() modifier if you want TypeScript to know the runtime type is a Decimal (you still need to parse it yourself):

import Decimal from 'decimal.js';

const orders = pgTable('orders', {
  total: numeric('total', { precision: 12, scale: 2 }).$type<Decimal>().notNull(),
});

4. Relations in Drizzle require the schema parameter at init time

The relational query API (db.query.orders.findFirst({ with: ... })) only works if you pass your schema to drizzle(). Forgetting this gives a runtime error, not a compile-time error.

// Wrong — db.query will be empty/undefined
const db = drizzle(client);

// Correct — pass schema
import * as schema from './schema';
const db = drizzle(client, { schema });

// Now this works
await db.query.orders.findFirst({ with: { customer: true } });

5. onConflictDoNothing vs onConflictDoUpdate — know the difference

// Silently skip if the unique constraint fires (no error, no update)
await db
  .insert(customers)
  .values({ email: 'user@example.com', fullName: 'Test User' })
  .onConflictDoNothing();

// Upsert — update specific fields on conflict
await db
  .insert(customers)
  .values({ email: 'user@example.com', fullName: 'Updated Name' })
  .onConflictDoUpdate({
    target: customers.email,        // the conflicting unique column
    set: { fullName: 'Updated Name' }, // what to update
  });

// Common pattern: update everything except the key
await db
  .insert(customers)
  .values(customerData)
  .onConflictDoUpdate({
    target: customers.email,
    set: {
      fullName: sql`excluded.full_name`, // reference the attempted insert values
      isActive: sql`excluded.is_active`,
    },
  });

excluded is a PostgreSQL special reference to the row that was attempted in the INSERT. Use it to say “set the column to whatever we tried to insert.”

Hands-On Exercise

Build the same blog repository from article 5.2 using Drizzle, then compare the two implementations.

Schema:

// src/db/schema.ts
import { pgTable, serial, text, boolean, timestamp, integer, primaryKey } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  published: boolean('published').notNull().default(false),
  authorId: integer('author_id').notNull().references(() => users.id),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
});

// Explicit many-to-many join table
export const postTags = pgTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id),
  tagId: integer('tag_id').notNull().references(() => tags.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  postTags: many(postTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

Repository:

// src/blog-repository.ts
import { db } from './db';
import { posts, users, tags, postTags } from './db/schema';
import { eq, and, gte, desc, count, sql, inArray } from 'drizzle-orm';

// 1. Get published posts with author, paginated, optional tag filter
export async function getPublishedPosts(options: {
  page: number;
  pageSize: number;
  tag?: string;
}) {
  const { page, pageSize, tag } = options;

  // If filtering by tag, get matching post IDs first
  let postIds: number[] | undefined;
  if (tag) {
    const tagRows = await db
      .select({ postId: postTags.postId })
      .from(postTags)
      .innerJoin(tags, eq(postTags.tagId, tags.id))
      .where(eq(tags.name, tag));
    postIds = tagRows.map((r) => r.postId);
    if (postIds.length === 0) return [];
  }

  return db.query.posts.findMany({
    where: and(
      eq(posts.published, true),
      postIds ? inArray(posts.id, postIds) : undefined
    ),
    with: {
      author: { columns: { id: true, name: true } },
      postTags: { with: { tag: true } },
    },
    orderBy: desc(posts.createdAt),
    offset: page * pageSize,
    limit: pageSize,
  });
}

// 2. Create a post with tags
export async function createPost(data: {
  title: string;
  content: string;
  authorId: number;
  tags: string[];
}) {
  return db.transaction(async (tx) => {
    const [post] = await tx
      .insert(posts)
      .values({
        title: data.title,
        content: data.content,
        authorId: data.authorId,
      })
      .returning();

    if (data.tags.length > 0) {
      // Insert tags (ignore conflict on name)
      await tx
        .insert(tags)
        .values(data.tags.map((name) => ({ name })))
        .onConflictDoNothing();

      // Fetch tag IDs
      const tagRows = await tx
        .select({ id: tags.id })
        .from(tags)
        .where(inArray(tags.name, data.tags));

      // Link tags to post
      await tx.insert(postTags).values(
        tagRows.map((t) => ({ postId: post.id, tagId: t.id }))
      );
    }

    return post;
  });
}

// 3. Get post counts by author
export async function getPostCountByAuthor() {
  return db
    .select({
      authorId: posts.authorId,
      authorName: users.name,
      postCount: count(posts.id),
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .groupBy(posts.authorId, users.name)
    .orderBy(desc(count(posts.id)));
}

Quick Reference

# Setup
npm install drizzle-orm postgres
npm install drizzle-kit --save-dev

# Migration workflow
npx drizzle-kit generate          # generate SQL migration from schema changes
npx drizzle-kit migrate           # apply pending migrations
npx drizzle-kit push              # push schema to DB (no migration file, for prototyping)
npx drizzle-kit studio            # browser UI
npx drizzle-kit introspect        # generate schema from existing DB

// Setup
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
const db = drizzle(postgres(process.env.DATABASE_URL!), { schema });

// Column types
serial('col')                           // SERIAL (auto-increment)
integer('col')                          // INTEGER
text('col')                             // TEXT
varchar('col', { length: n })           // VARCHAR(n)
boolean('col')                          // BOOLEAN
numeric('col', { precision: p, scale: s }) // NUMERIC
uuid('col').defaultRandom()             // UUID
timestamp('col', { withTimezone: true }).defaultNow() // TIMESTAMPTZ
jsonb('col')                            // JSONB

// Query builder
db.select().from(table)
db.select({ id: t.id, name: t.name }).from(table)
db.select().from(t).where(eq(t.id, 1))
db.select().from(t).where(and(eq(t.a, 1), gt(t.b, 100)))
db.select().from(t).where(or(eq(t.a, 1), eq(t.a, 2)))
db.select().from(t).innerJoin(t2, eq(t.fk, t2.id))
db.select().from(t).leftJoin(t2, eq(t.fk, t2.id))
db.select().from(t).orderBy(desc(t.createdAt))
db.select().from(t).limit(20).offset(40)
db.insert(t).values({ ... }).returning()
db.insert(t).values({ ... }).onConflictDoUpdate({ target: t.col, set: { ... } })
db.insert(t).values({ ... }).onConflictDoNothing()
db.update(t).set({ ... }).where(eq(t.id, 1)).returning()
db.delete(t).where(eq(t.id, 1)).returning()

// Operators (import from 'drizzle-orm')
eq(col, val)    ne(col, val)    gt(col, val)    gte(col, val)
lt(col, val)    lte(col, val)   like(col, patt) ilike(col, patt)
and(...conds)   or(...conds)    not(cond)
isNull(col)     isNotNull(col)  inArray(col, arr)  notInArray(col, arr)
count(col)      sum(col)        avg(col)        min(col)   max(col)

// Relational API (requires schema passed to drizzle())
db.query.table.findFirst({ where: ..., with: { relation: true } })
db.query.table.findMany({ where, with, orderBy, limit, offset })

// Transactions
await db.transaction(async (tx) => { await tx.insert(...).values(...) })

// Raw SQL
sql`SELECT * FROM ${table} WHERE id = ${id}`
await db.execute(sql`...`)

Further Reading

• Drizzle ORM Documentation
• Drizzle ORM — PostgreSQL Column Types
• Drizzle ORM — Relational Queries
• Drizzle Kit — Migrations
• Drizzle vs Prisma Comparison
• postgres.js Driver — recommended driver for server-side Node.js