Zum Inhalt springen
1337skills 1337skills - Referenzen

Drizzle ORM Cheat Sheet

Overview

Drizzle ORM is a lightweight, performant TypeScript ORM that provides type-safe database access with a SQL-like query builder. Unlike traditional ORMs that abstract away SQL, Drizzle embraces SQL semantics while adding TypeScript type safety, making it intuitive for developers who already know SQL. It has zero dependencies, works with any JavaScript runtime, and supports PostgreSQL, MySQL, SQLite, and various serverless databases.

Drizzle stands out with its “if you know SQL, you know Drizzle” philosophy. The schema definition doubles as the source of truth for types, migrations, and queries. Drizzle Kit, the companion CLI tool, handles migration generation and database introspection. The ORM supports both a SQL-like query builder and a relational query API for complex nested data fetching, all with full type inference.

Installation

PostgreSQL Setup

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

# Or with postgres.js driver
npm install drizzle-orm postgres
npm install -D drizzle-kit

MySQL Setup

npm install drizzle-orm mysql2
npm install -D drizzle-kit

SQLite Setup

npm install drizzle-orm better-sqlite3
npm install -D drizzle-kit @types/better-sqlite3

# Or with Bun
npm install drizzle-orm
npm install -D drizzle-kit

Schema Definition

PostgreSQL Schema

import { pgTable, serial, text, integer, boolean, timestamp, varchar, uuid, jsonb, pgEnum } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Enum
export const roleEnum = pgEnum('role', ['user', 'admin', 'moderator']);

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  role: roleEnum('role').default('user').notNull(),
  age: integer('age'),
  active: boolean('active').default(true).notNull(),
  metadata: jsonb('metadata').$type<{ theme: string; notifications: boolean }>(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

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

// Comments table
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id').references(() => posts.id, { onDelete: 'cascade' }).notNull(),
  authorId: integer('author_id').references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Relations

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

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

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

Database Connection

PostgreSQL

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, logger: true });

SQLite

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

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

CRUD Operations

Select (Read)

import { eq, ne, gt, gte, lt, lte, like, ilike, between, inArray, and, or, desc, asc, count, sql } from 'drizzle-orm';

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

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

// Where clause
const activeAdmins = await db.select()
  .from(users)
  .where(and(
    eq(users.active, true),
    eq(users.role, 'admin')
  ));

// Complex conditions
const filtered = await db.select()
  .from(users)
  .where(or(
    like(users.name, '%alice%'),
    and(
      gte(users.age, 18),
      lt(users.age, 65)
    )
  ))
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

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

// Aggregations
const userCounts = await db.select({
  role: users.role,
  count: count(),
}).from(users)
  .groupBy(users.role);

// Subqueries
const sq = db.select({ authorId: posts.authorId, postCount: count().as('post_count') })
  .from(posts)
  .groupBy(posts.authorId)
  .as('post_counts');

const usersWithPostCounts = await db.select({
  name: users.name,
  postCount: sq.postCount,
}).from(users)
  .leftJoin(sq, eq(users.id, sq.authorId));

Insert (Create)

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

// Bulk insert
await db.insert(users).values([
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' },
]);

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

// On conflict do nothing
await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice',
}).onConflictDoNothing({ target: users.email });

Update

// Update with returning
const [updated] = await db.update(users)
  .set({ name: 'Alice Smith', updatedAt: new Date() })
  .where(eq(users.id, 1))
  .returning();

// Conditional update
await db.update(users)
  .set({ active: false })
  .where(lt(users.createdAt, new Date('2024-01-01')));

Delete

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

// Bulk delete
await db.delete(posts)
  .where(eq(posts.published, false));

Relational Queries

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

// Nested relations
const fullData = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: {
          with: {
            author: true,
          },
        },
      },
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
  where: eq(users.active, true),
  columns: {
    id: true,
    name: true,
    email: true,
  },
});

// Find one
const user = await db.query.users.findFirst({
  where: eq(users.email, 'alice@example.com'),
  with: { posts: true },
});

Migrations

Drizzle Kit Configuration

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

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

Migration Commands

CommandDescription
npx drizzle-kit generateGenerate migration from schema changes
npx drizzle-kit migrateApply pending migrations
npx drizzle-kit pushPush schema directly (dev only)
npx drizzle-kit pullIntrospect existing DB to schema
npx drizzle-kit studioOpen Drizzle Studio GUI
npx drizzle-kit checkCheck migration consistency
npx drizzle-kit dropDrop a migration

Configuration

Type Inference

import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

// Infer types from schema
type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;

// Use in functions
async function createUser(data: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}

Advanced Usage

Transactions

const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({
    name: 'Alice',
    email: 'alice@example.com',
  }).returning();

  await tx.insert(posts).values({
    title: 'First Post',
    body: 'Content here',
    authorId: user.id,
  });

  return user;
});

// Nested transactions (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'Bob', email: 'bob@ex.com' });
  await tx.transaction(async (nested) => {
    await nested.insert(posts).values({ title: 'Post', body: '...', authorId: 1 });
  });
});

Raw SQL

import { sql } from 'drizzle-orm';

// Raw SQL in select
const result = await db.select({
  id: users.id,
  nameLength: sql<number>`length(${users.name})`,
}).from(users);

// Full raw query
const rows = await db.execute(sql`
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.name
  HAVING COUNT(p.id) > ${5}
`);

// Custom SQL in where
const recent = await db.select()
  .from(posts)
  .where(sql`${posts.createdAt} > NOW() - INTERVAL '7 days'`);

Prepared Statements

const getUserByEmail = db.select()
  .from(users)
  .where(eq(users.email, sql.placeholder('email')))
  .prepare('get_user_by_email');

const user = await getUserByEmail.execute({ email: 'alice@example.com' });

Troubleshooting

ProblemSolution
Type errors on insertCheck required vs optional fields; use InferInsertModel
Relations not loadingDefine relations with relations() function; pass schema to drizzle()
Migration conflictsRun drizzle-kit check; resolve conflicts in migration files
Column type mismatchUse .$type<T>() for custom type mapping
Slow queriesAdd indexes in schema; use .prepare() for repeated queries
drizzle-kit push failsCheck DB connection; ensure schema matches dialect
Join returning wrong typesExplicitly select columns; use aliases for ambiguous names
Cannot find moduleEnsure schema file path correct in drizzle.config.ts