Alright, let’s get our hands dirty with Drizzle. Forget the YAML, the JSON configs, the DSLs you have to learn. Drizzle’s core philosophy is simple: your database schema is TypeScript code. You define your tables, relations, and constraints using a familiar, type-safe API, and Drizzle does the heavy lifting of generating the SQL migrations and the client that knows exactly what your data looks like. It’s like having a brilliant, pedantic compiler for your database, and I mean that in the best way possible.

The Core: Defining Your Schema

You don’t create users tables here. You create users objects. You import pgTable from Drizzle (or mysqlTable, sqliteTable—you get the idea) and build your schema declaratively.

Let’s build a simple blog schema. No, not another todo app. We’re better than that.

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

// Define the 'users' table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  fullName: text('full_name').notNull(),
  // Let's be honest, we all just use 'text' for passwords and hash them.
  // Storing plain text is a fireable offense. Don't.
  passwordHash: text('password_hash').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// Define the 'posts' table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  published: boolean('published').default(false).notNull(),
  authorId: integer('author_id').notNull().references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').$onUpdate(() => new Date()),
});

See what’s happening here? The pgTable function takes a table name and an object where the keys are your column names in JS and the values are the column definitions with their types and constraints. serial('id') creates a SERIAL primary key, varchar defines its max length, and notNull() and unique() are delightfully self-explanatory. The $onUpdate function for updatedAt is a thing of beauty—it automatically sets the timestamp on every update. No more forgetting to manually update that field.

Modeling Relationships (The Right Way)

A user has many posts. A post belongs to a user. We all know this. Drizzle lets you define these relations explicitly in your TypeScript code, which it then uses to infer incredibly powerful types for your queries.

// ... in your schema.ts file, after the table definitions

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

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

This might feel a bit verbose compared to some ORMs that “just figure it out.” Trust me, this is a feature. This explicit definition is why Drizzle’s type inference is black magic. It knows what you’re trying to do. Now, when you write a query to fetch a user with their posts, the return type will accurately reflect the nested structure. No more any or manual type casting.

Pushing Your Schema to the Database

You’ve written your schema in code. Great. Now you need to turn that into actual SQL tables. You don’t write the migrations yourself; Drizzle generates them for you. This is where the drizzle-kit CLI comes in.

# Generate a new migration file based on changes in your schema.ts
npx drizzle-kit generate:pg

# Apply all pending migrations to your database
npx drizzle-kit migrate

Run generate, check the generated SQL migration file in the /drizzle folder. Read it. Make sure it’s doing what you expect. This is your chance to catch if Drizzle misinterpreted a change or if you want to add a custom index. Once you’re happy, run migrate. This process gives you the safety of version-controlled migrations without the headache of writing raw SQL for every single alter table statement.

The Payoff: Querying with Full Type Safety

This is where the rubber meets the road. Because your schema is defined in TypeScript, the Drizzle query builder is fully aware of your tables, columns, and their types.

// Import the drizzle client and your schema
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq, desc } from 'drizzle-orm';
import { users, posts } from './schema';

const client = ... // your database client pool
const db = drizzle(client);

// Insert a new user. Try to add a field that doesn't exist? TypeScript will scream at you.
const newUser = await db.insert(users).values({
  email: 'ada@lovelace.com',
  fullName: 'Ada Lovelace',
  passwordHash: 'super_secure_hash',
  // 'createdAt' is auto-generated, so we omit it. TypeScript knows it's optional.
}).returning(); // Get the inserted row back

// Query with relations. Look at this beauty.
const publishedPosts = await db.query.posts.findMany({
  where: eq(posts.published, true),
  with: {
    author: true, // This joins the 'users' table via the relation we defined
  },
  orderBy: desc(posts.createdAt),
});

// The type of 'publishedPosts' is fully known:
// Array<{ id: number; title: string; ... author: { id: number; email: string; ... } }>
console.log(posts[0].author.email); // 'ada@lovelace.com'

The with clause is the killer feature. It’s a declarative way to eager-load relationships, and the return type is perfectly inferred. This is the kind of thing that saves you from runtime errors and makes refactoring a dream. If you change a column name in your schema.ts, every query that uses the old name will immediately show a TypeScript error. That’s not just convenient; it’s a safety net.