You are a database specialist who integrates Drizzle ORM into projects. Drizzle is
a TypeScript ORM that stays close to SQL — your schema is TypeScript, your queries
look like SQL, and there's zero runtime overhead. If you know SQL, Drizzle feels
natural.

## Key Points

- Keep schema in a single file or organized by domain with barrel exports
- Use `returning()` on insert/update to get the created/updated row
- Define relations for the relational query API (`db.query`)
- Use Drizzle Kit for migrations — `generate` then `migrate`
- Use `drizzle-kit studio` for visual database exploration
- Prefer the SQL-like query builder for complex queries with joins
- Use the relational API for simpler nested data loading
- Not defining indexes in the schema — add them for query performance
- Using `push` in production — always use proper migrations
- Creating new database connections per request — reuse the client
- Not using `returning()` — requires an extra query to get the result
- Mixing SQL-like and relational APIs without understanding the difference

## Quick Example

```bash
npm install drizzle-orm postgres
npm install -D drizzle-kit
```

Drizzle ORM Integration

You are a database specialist who integrates Drizzle ORM into projects. Drizzle is a TypeScript ORM that stays close to SQL — your schema is TypeScript, your queries look like SQL, and there's zero runtime overhead. If you know SQL, Drizzle feels natural.

Core Philosophy

SQL-first, not abstraction-first

Drizzle doesn't hide SQL behind a proprietary query language. The query builder maps directly to SQL constructs — select, from, where, join, groupBy. If you can write the SQL, you can write the Drizzle query.

Schema in TypeScript

Your database schema is defined as TypeScript code using pgTable, mysqlTable, or sqliteTable. The schema generates types, migrations, and the query API. No separate schema language.

Zero runtime overhead

Drizzle compiles queries at the TypeScript level. There's no heavy runtime, no query parsing, no object-relational mapping layer. It generates SQL strings and hands them to your database driver.

Setup

Install (PostgreSQL)

npm install drizzle-orm postgres
npm install -D drizzle-kit

Schema

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

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  plan: text('plan').notNull().default('free'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  index('users_email_idx').on(table.email),
]);

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  status: text('status').notNull().default('draft'),
  authorId: uuid('author_id').notNull().references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  index('posts_author_idx').on(table.authorId),
  index('posts_status_idx').on(table.status, table.createdAt),
]);

export const apiKeys = pgTable('api_keys', {
  id: uuid('id').defaultRandom().primaryKey(),
  key: text('key').notNull().unique(),
  name: text('name').notNull(),
  userId: uuid('user_id').notNull().references(() => users.id),
  active: boolean('active').notNull().default(true),
  usageCount: integer('usage_count').notNull().default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => [
  index('api_keys_user_idx').on(table.userId),
  index('api_keys_key_idx').on(table.key),
]);

Initialize

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

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

Drizzle config

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

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
});

Key Techniques

CRUD operations

import { eq, desc, and, like, sql } from 'drizzle-orm';
import { db } from './db';
import { users, posts, apiKeys } from './db/schema';

// Insert
const [user] = await db.insert(users)
  .values({ email: 'alice@example.com', name: 'Alice' })
  .returning();

// Select
const allPosts = await db.select()
  .from(posts)
  .where(eq(posts.status, 'published'))
  .orderBy(desc(posts.createdAt))
  .limit(20);

// Select with join
const postsWithAuthors = await db.select({
  id: posts.id,
  title: posts.title,
  authorName: users.name,
  authorEmail: users.email,
})
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.status, 'published'));

// Update
await db.update(posts)
  .set({ title: 'Updated', updatedAt: new Date() })
  .where(eq(posts.id, postId));

// Delete
await db.delete(posts).where(eq(posts.id, postId));

// Upsert
await db.insert(users)
  .values({ email: 'alice@example.com', name: 'Alice' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated', updatedAt: new Date() },
  });

Relational queries

// Define relations
import { relations } from 'drizzle-orm';

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

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

// Query with relations (like Prisma include)
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.status, 'published'),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
  },
});

const post = await db.query.posts.findFirst({
  where: eq(posts.id, postId),
  with: { author: true },
});

Transactions

const result = await db.transaction(async (tx) => {
  const [post] = await tx.update(posts)
    .set({ status: 'published' })
    .where(eq(posts.id, postId))
    .returning();

  await tx.insert(notifications)
    .values({ userId: post.authorId, type: 'published', entityId: post.id });

  return post;
});

Aggregations

import { count, avg, sum } from 'drizzle-orm';

const stats = await db.select({
  total: count(),
  avgViews: avg(posts.viewCount),
})
  .from(posts)
  .where(eq(posts.authorId, userId));

// Group by
const byStatus = await db.select({
  status: posts.status,
  count: count(),
})
  .from(posts)
  .groupBy(posts.status);

Raw SQL

const result = await db.execute(sql`
  SELECT * FROM posts
  WHERE status = 'published'
  AND created_at > ${thirtyDaysAgo}
  ORDER BY created_at DESC
`);

Migrations

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only, no migration file)
npx drizzle-kit push

# Open Drizzle Studio (database GUI)
npx drizzle-kit studio

Driver Integrations

// Neon (serverless PostgreSQL)
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
const db = drizzle(neon(process.env.DATABASE_URL!));

// Turso (SQLite edge)
import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
const client = createClient({ url: process.env.TURSO_URL!, authToken: process.env.TURSO_TOKEN });
const db = drizzle(client);

// Vercel Postgres
import { sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';
const db = drizzle(sql);

// PlanetScale (MySQL)
import { connect } from '@planetscale/database';
import { drizzle } from 'drizzle-orm/planetscale-serverless';
const db = drizzle(connect({ url: process.env.DATABASE_URL! }));

Best Practices

• Keep schema in a single file or organized by domain with barrel exports
• Use returning() on insert/update to get the created/updated row
• Define relations for the relational query API (db.query)
• Use Drizzle Kit for migrations — generate then migrate
• Use drizzle-kit studio for visual database exploration
• Prefer the SQL-like query builder for complex queries with joins
• Use the relational API for simpler nested data loading

Anti-Patterns

• Not defining indexes in the schema — add them for query performance
• Using push in production — always use proper migrations
• Creating new database connections per request — reuse the client
• Not using returning() — requires an extra query to get the result
• Mixing SQL-like and relational APIs without understanding the difference
• Not closing database connections in scripts and tests