Drizzle ORM: TypeScript-first database access

Drizzle ORM is a TypeScript-first database toolkit. You define schemas with TypeScript, write queries that look like SQL, and get full type inference. It is lightweight compared to Prisma and gives you direct control over SQL.

Why Drizzle?

Installation

# Core package
npm install drizzle-orm

# Database driver (choose one)
npm install pg                  # PostgreSQL
npm install mysql2              # MySQL
npm install better-sqlite3      # SQLite

# Development tools
npm install -D drizzle-kit

Schema definition

PostgreSQL example

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

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

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

SQLite example

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
});

MySQL example

import { mysqlTable, serial, varchar, timestamp } from 'drizzle-orm/mysql-core';

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

Database connection

PostgreSQL

// 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 });

SQLite

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

const sqlite = new Database('sqlite.db');
export const db = drizzle(sqlite, { schema });

Querying

Select

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

// Select all
const allUsers = await db.select().from(users);

// Select with conditions
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.active, true));

// Select specific columns
const emails = await db
  .select({ email: users.email })
  .from(users);

// Multiple conditions
const results = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      gt(posts.createdAt, new Date('2026-01-01'))
    )
  );

// Ordering and limiting
const recentPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Pattern matching
const searchResults = await db
  .select()
  .from(users)
  .where(like(users.name, '%john%'));

Insert

// Insert one
const newUser = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returning();

// Insert many
await db.insert(users).values([
  { email: 'user1@example.com', name: 'User 1' },
  { email: 'user2@example.com', name: 'User 2' },
]);

// Upsert (insert or update)
await db
  .insert(users)
  .values({ email: 'user@example.com', name: 'Updated Name' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name' },
  });

Update

// Update with condition
await db
  .update(users)
  .set({ name: 'New Name' })
  .where(eq(users.id, 1));

// Update returning
const updated = await db
  .update(posts)
  .set({ published: true })
  .where(eq(posts.id, 5))
  .returning();

Delete

// Delete with condition
await db.delete(posts).where(eq(posts.id, 1));

// Delete all (careful!)
await db.delete(posts);

Joins

// Inner join
const postsWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// Left join
const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Relations

Define relations for easier querying:

// db/schema.ts
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],
  }),
}));

Query with relations:

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

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

Raw SQL

import { sql } from 'drizzle-orm';

// Raw query
const result = await db.execute(sql`
  SELECT * FROM users WHERE email = ${email}
`);

// Raw in select
const usersWithCount = await db
  .select({
    name: users.name,
    postCount: sql<number>`count(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

Migrations

Configuration

Create drizzle.config.ts:

import type { Config } from 'drizzle-kit';

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

Generate migrations

npx drizzle-kit generate

Apply migrations

npx drizzle-kit migrate

Push (development)

# Push schema directly without migrations
npx drizzle-kit push

Drizzle vs Prisma

Choose Drizzle for:

• SQL control
• Minimal bundle size
• No code generation step

Choose Prisma for:

• Maximum productivity
• Rich ecosystem
• Auto-generated types and client

Summary

Drizzle ORM provides:

1. TypeScript-first schemas with inferred types
2. SQL-like API for familiar query syntax
3. Multiple databases supported
4. Minimal overhead compared to heavier ORMs
5. Migration tools via drizzle-kit

The Drizzle documentation has complete guides for each database and advanced patterns.