Using Drizzle ORM

Install and connect (Postgres example):

Bash
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

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

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
});

TypeScript
// db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

TypeScript
// usage
import { eq } from 'drizzle-orm';
import { db } from './db';
import { users } from './db/schema';

const all = await db.select().from(users);
const one = await db.select().from(users).where(eq(users.id, 1));
await db.insert(users).values({ name: 'Alice', email: 'a@x.com' });

Progress checklist for setting up a new Drizzle project:

• Step 1: Identify the driver/dialect (Postgres, MySQL, SQLite, or serverless variants like Neon/Turso/D1) and install the matching drizzle-orm adapter package
• Step 2: Define schema in db/schema.ts using pgTable/mysqlTable/sqliteTable
• Step 3: Define relations with relations() if using the relational query API (db.query.*)
• Step 4: Create drizzle.config.ts pointing to schema file and migrations output dir
• Step 5: Instantiate db client with the schema passed in for relational queries
• Step 6: Write queries — use SQL-like builder (db.select().from()...) or relational API (db.query.users.findMany())
• Step 7: Generate migrations with drizzle-kit generate, review the SQL, then apply with drizzle-kit migrate (or push for prototyping)
• Step 8: Commit schema + generated migration SQL files together

drizzle.config.ts

TypeScript
import { defineConfig } from 'drizzle-kit';

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

Commands:

Bash
npx drizzle-kit generate   # create SQL migration files from schema diff
npx drizzle-kit migrate    # apply pending migrations
npx drizzle-kit push       # push schema directly (prototyping, skips migration files)
npx drizzle-kit studio     # open Drizzle Studio GUI

Relations (for relational query API)

TypeScript
import { relations } from 'drizzle-orm';

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

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

TypeScript
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

Example 1: Input: "Add a many-to-many relation between posts and tags via a join table postsToTags." Output:

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

export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id').notNull().references(() => posts.id),
  tagId: integer('tag_id').notNull().references(() => tags.id),
}, (t) => ({
  pk: primaryKey({ columns: [t.postId, t.tagId] }),
}));

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

Example 2: Input: "Write a type-safe query to get all posts with author name, filtered by author email." Output:

TypeScript
import { eq } from 'drizzle-orm';

const result = await db
  .select({ title: posts.title, authorName: users.name })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.email, 'a@x.com'));

Example 3: Input: "Schema changed — I added a bio column to users. What's next?" Output: Run npx drizzle-kit generate to create a migration SQL file reflecting the new column, inspect it in ./drizzle, then run npx drizzle-kit migrate to apply it to the database.