drizzle-orm

name	Drizzle ORM
description	Expert guidance for Drizzle ORM including schema definition, queries, relations, migrations, TypeScript integration with SQLite/PostgreSQL, and Drizzle Studio. Use this when working with type-safe database operations, schema management, or ORM queries.

Drizzle ORM

Expert assistance with Drizzle ORM - TypeScript ORM for SQL databases.

Overview

Drizzle ORM is a lightweight TypeScript ORM:

• Type-Safe: Full TypeScript type inference
• SQL-Like: Familiar SQL syntax, not a new query language
• Performant: Zero overhead, generates efficient SQL
• Multiple Databases: PostgreSQL, MySQL, SQLite support
• Migrations: Built-in migration system
• Drizzle Studio: Visual database browser

Installation

# Core packages
npm install drizzle-orm
npm install --save-dev drizzle-kit

# Database driver (choose one)
npm install better-sqlite3              # For SQLite
npm install @types/better-sqlite3 --save-dev

# Or for PostgreSQL
npm install postgres                     # For PostgreSQL
npm install pg                           # Alternative PostgreSQL driver

Quick Start (SQLite)

1. Define Schema

// src/db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

2. Create Database Client

// src/db/client.ts
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 });

3. Use in Application

import { db } from './db/client';
import { users, posts } from './db/schema';
import { eq } from 'drizzle-orm';

// Insert
const newUser = await db.insert(users).values({
  id: '1',
  name: 'John Doe',
  email: '[e-mail]',
}).returning();

// Query
const allUsers = await db.select().from(users);
const user = await db.select().from(users).where(eq(users.id, '1'));

// Update
await db.update(users)
  .set({ name: 'Jane Doe' })
  .where(eq(users.id, '1'));

// Delete
await db.delete(users).where(eq(users.id, '1'));

Schema Definition

Column Types (SQLite)

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

export const examples = sqliteTable('examples', {
  // Text
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),

  // Integer
  age: integer('age'),
  count: integer('count').default(0),

  // Boolean (stored as integer 0/1)
  isActive: integer('is_active', { mode: 'boolean' }).default(true),

  // Timestamp (stored as integer unix epoch)
  createdAt: integer('created_at', { mode: 'timestamp' }),
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' }), // milliseconds

  // Real (floating point)
  price: real(),

  
  : (, { :  }),

  
  : (, { :  }).<{ : ; :  }>(),
});

Constraints

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

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(), // Unique constraint
  name: text('name').notNull(), // Not null
  age: integer('age').default(18), // Default value
}, (table) => ({
  // Composite unique constraint
  emailNameUnique: unique().on(table.email, table.name),
  // Index
  emailIdx: index('email_idx').on(table.email),
  // Composite index
  nameAgeIdx: index('name_age_idx').on(table.name, table.age),
}));

// Composite primary key
export  userRoles = (, {
  : ().(),
  : ().(),
},  ({
  : ({ : [table., table.] }),
}));

Check Constraints

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

export const certificates = sqliteTable('certificates', {
  id: text('id').primaryKey(),
  status: text('status').notNull(),
  serialNumber: text('serial_number').notNull(),
}, (table) => ({
  // Check constraint
  statusCheck: check('status_check', sql`${table.status} IN ('active', 'revoked', 'expired')`),
}));

Foreign Keys

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',  // Delete posts when user is deleted
      onUpdate: 'cascade',  // Update posts when user id changes
    }),
  title: text('title').notNull(),
});

// Self-referencing foreign key
export const categories = sqliteTable('categories', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  parentId: text('parent_id').references((): AnyPgColumn => categories.id),
});

Default Values

import { sql } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),

  // SQL default
  createdAt: integer('created_at').default(sql`(unixepoch())`),

  // TypeScript default function
  id: text('id').$defaultFn(() => crypto.randomUUID()),
  createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
});

Queries

Select

import { eq, and, or, gt, gte, lt, lte, like, inArray } from 'drizzle-orm';

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

// Select specific columns
const names = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// Where clauses
const user = await db.select().from(users).where(eq(users.id, '1'));

// Multiple conditions
const activeAdults = await db.select().from(users).where(
  and(
    eq(users.isActive, true),
    gte(users.age, 18)
  )
);

// Or conditions
const results = await db.select().from(users).where(
  or(
    eq(users.role, 'admin'),
    eq(users., )
  )
);


 johns =  db.().(users).((users., ));


 specificUsers =  db.().(users).(
  (users., [, , ])
);


 adults =  db.().(users).((users., ));
 minors =  db.().(users).((users., ));


 sorted =  db.().(users).(users.);
 descending =  db.().(users).((users.));


 paginated =  db.().(users).().();


 user =  db.().(users).((users., )).();

Joins

import { eq } from 'drizzle-orm';

// Inner join
const usersWithPosts = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(posts.userId, users.id));

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

// Select specific columns from joined tables
const results = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id));

// Multiple joins
const data = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .leftJoin(comments, eq(comments.postId, posts.id));

Aggregations

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

// Count
const userCount = await db.select({ count: count() }).from(users);

// Count with condition
const activeCount = await db
  .select({ count: count() })
  .from(users)
  .where(eq(users.isActive, true));

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

// Multiple aggregations
const stats = await db
  .select({
    total: count(),
    avgAge: avg(users.age),
    minAge: min(users.age),
    maxAge: max(users.age),
  })
  .from(users);

// Having clause
const activeUsers =  db
  .({
    : posts.,
    : (),
  })
  .(posts)
  .(posts.)
  .( (postCount, ));

Subqueries

import { sql } from 'drizzle-orm';

// Subquery in WHERE
const sq = db.select({ userId: posts.userId }).from(posts).groupBy(posts.userId);

const activePosters = await db
  .select()
  .from(users)
  .where(inArray(users.id, sq));

// Subquery as column
const usersWithPostCount = await db
  .select({
    id: users.id,
    name: users.name,
    postCount: sql<number>`(
      SELECT COUNT(*)
      FROM ${posts}
      WHERE ${posts.userId} = ${users.id}
    )`,
  })
  .from(users);

Insert

Single Insert

// Insert one
await db.insert(users).values({
  id: '1',
  name: 'John',
  email: '[e-mail]',
});

// Insert with returning
const newUser = await db.insert(users)
  .values({
    id: '2',
    name: 'Jane',
    email: '[e-mail]',
  })
  .returning();

// Return specific columns
const user = await db.insert(users)
  .values({ id: '3', name: 'Bob', email: '[e-mail]' })
  .returning({ id: users.id, name: users.name });

Bulk Insert

// Insert multiple
await db.insert(users).values([
  { id: '1', name: 'John', email: '[e-mail]' },
  { id: '2', name: 'Jane', email: '[e-mail]' },
  { id: '3', name: 'Bob', email: '[e-mail]' },
]);

// Bulk insert with returning
const newUsers = await db.insert(users)
  .values([
    { id: '4', name: 'Alice', email: '[e-mail]' },
    { id: '5', name: 'Charlie', email: '[e-mail]' },
  ])
  .returning();

Upsert (Insert or Update)

// SQLite 3.24+ (ON CONFLICT)
await db.insert(users)
  .values({ id: '1', name: 'John', email: '[e-mail]' })
  .onConflictDoUpdate({
    target: users.id,
    set: { name: 'John Updated', email: '[e-mail]' },
  });

// Do nothing on conflict
await db.insert(users)
  .values({ id: '1', name: 'John', email: '[e-mail]' })
  .onConflictDoNothing();

// Update specific columns
await db.insert(users)
  .values({ id: '1', name: 'John', email: '[e-mail]' })
  .onConflictDoUpdate({
    target: users.id,
    set: { updatedAt: sql`CURRENT_TIMESTAMP` },
  });

Update

import { eq } from 'drizzle-orm';

// Update single row
await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, '1'));

// Update multiple columns
await db.update(users)
  .set({
    name: 'Jane Smith',
    email: '[e-mail]',
  })
  .where(eq(users.id, '2'));

// Update with returning
const updated = await db.update(users)
  .set({ name: 'Bob Updated' })
  .where(eq(users.id, '3'))
  .returning();

// Update with SQL expression
await db.update(users)
  .set({ age: sql`${users.age} + 1` })
  .where(eq(users.id, '1'));

// Conditional update
await db.update(users)
  .({ :  })
  .((
    (users., ),
    (users.,  ())
  ));

Delete

// Delete single row
await db.delete(users).where(eq(users.id, '1'));

// Delete multiple rows
await db.delete(users).where(inArray(users.id, ['1', '2', '3']));

// Delete with condition
await db.delete(users).where(lt(users.createdAt, new Date('2023-01-01')));

// Delete with returning
const deleted = await db.delete(users)
  .where(eq(users.id, '1'))
  .returning();

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

Transactions

// Simple transaction
await db.transaction(async (tx) => {
  await tx.insert(users).values({ id: '1', name: 'John', email: '[e-mail]' });
  await tx.insert(posts).values({ id: '1', title: 'First Post', userId: '1' });
});

// Transaction with rollback
try {
  await db.transaction(async (tx) => {
    await tx.insert(users).values({ id: '1', name: 'John', email: '[e-mail]' });

    // This will cause transaction to rollback
    throw new Error('Rollback!');

    await tx.insert(posts).values({ id: '1', title: 'Post', userId: '1' });
  });
} catch (error) {
  console.(, error);
}


 db.( (tx1) => {
   tx1.(users).({ : , : , :  });

   tx1.( (tx2) => {
     tx2.(posts).({ : , : , :  });
  });
});

Relations

Define Relations

// src/db/schema.ts
import { relations } from 'drizzle-orm';
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  userId: text('user_id').notNull().references(() => users.id),
});

export const comments = sqliteTable('comments', {
  id: text('id').primaryKey(),
  content: text('content').notNull(),
  postId: text().().( posts.),
  : ().().( users.),
});


  usersRelations = (users,  ({
  : (posts),
  : (comments),
}));

  postsRelations = (posts,  ({
  : (users, {
    : [posts.],
    : [users.],
  }),
  : (comments),
}));

  commentsRelations = (comments,  ({
  : (posts, {
    : [comments.],
    : [posts.],
  }),
  : (users, {
    : [comments.],
    : [users.],
  }),
}));

Query with Relations

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

// Nested relations
const usersWithPostsAndComments = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
    },
  },
});

// Filter relations
const usersWithRecentPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: (posts, { gte }) => gte(posts.createdAt, new Date('2024-01-01')),
    },
  },
});

// Select specific columns
const data = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      columns: {
        id: true,
        : ,
      },
    },
  },
});

Migrations

Configuration

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

export default {
  schema: './src/db/schema.ts',
  out: './drizzle/migrations',
  driver: 'better-sqlite',
  dbCredentials: {
    url: './sqlite.db',
  },
} satisfies Config;

Generate Migrations

# Generate migration from schema changes
npx drizzle-kit generate:sqlite

# Custom migration name
npx drizzle-kit generate:sqlite --name add_users_table

# Generate with custom config
npx drizzle-kit generate:sqlite --config drizzle.config.ts

Run Migrations

// src/db/migrate.ts
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);

// Run migrations
await migrate(db, { migrationsFolder: './drizzle/migrations' });

console.log('Migrations complete!');
sqlite.close();

Migration Files

-- drizzle/migrations/0001_add_users.sql
CREATE TABLE `users` (
  `id` text PRIMARY KEY NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL UNIQUE,
  `created_at` integer NOT NULL
);

CREATE INDEX `email_idx` ON `users` (`email`);

Drizzle Studio

# Start Drizzle Studio
npx drizzle-kit studio

# Custom port
npx drizzle-kit studio --port 3333

# With custom config
npx drizzle-kit studio --config drizzle.config.ts

Access at: http://localhost:4983

TypeScript Integration

Infer Types

import { InferSelectModel, InferInsertModel } from 'drizzle-orm';
import { users, posts } from './schema';

// Infer select model (what you get from queries)
export type User = InferSelectModel<typeof users>;
export type Post = InferSelectModel<typeof posts>;

// Infer insert model (what you need to insert)
export type InsertUser = InferInsertModel<typeof users>;
export type InsertPost = InferInsertModel<typeof posts>;

// Usage
function createUser(user: InsertUser): Promise<User> {
  return db.insert(users).values(user).returning().get();
}

Typed Queries

// Type-safe query builder
const query = db
  .select({
    id: users.id,
    name: users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id))
  .groupBy(users.id);

// Infer result type
type QueryResult = Awaited<ReturnType<typeof query.execute>>;

Best Practices

1. Use Transactions: Wrap multiple operations in transactions
2. Define Relations: Use relations for easier queries
3. Type Safety: Leverage TypeScript type inference
4. Migrations: Use migration system, don't modify schema directly in production
5. Indexes: Index frequently queried columns
6. Prepared Statements: Drizzle automatically uses prepared statements
7. Connection Management: Reuse database connection
8. Studio: Use Drizzle Studio for visual database exploration
9. Error Handling: Handle constraint violations
10. Performance: Use get() for single results instead of all()[0]

Common Patterns

Repository Pattern

export class UserRepository {
  constructor(private db: ReturnType<typeof drizzle>) {}

  async findById(id: string): Promise<User | undefined> {
    return this.db.select().from(users).where(eq(users.id, id)).get();
  }

  async findAll(): Promise<User[]> {
    return this.db.select().from(users);
  }

  async create(data: InsertUser): Promise<User> {
    return this.db.insert(users).values(data).returning().get();
  }

  async update(id: string, data: Partial<>): < | > {
     ..(users).(data).((users., id)).().();
  }

   (: ): <> {
     result =  ..(users).((users., id)).();
     result. > ;
  }
}

Resources

• Documentation: https://orm.drizzle.team/docs/overview
• GitHub: https://github.com/drizzle-team/drizzle-orm
• Examples: https://github.com/drizzle-team/drizzle-orm/tree/main/examples
• Drizzle Studio: https://orm.drizzle.team/drizzle-studio/overview