Getting Started with Drizzle ORM

Drizzle ORM provides type-safe database access with SQL-like syntax. Here's how to use it.

Setup#

npm install drizzle-orm
npm install drizzle-kit -D
npm install @libsql/client # or pg for PostgreSQL

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

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

Schema Definition#

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name').notNull(),
  password: text('password').notNull(),
  role: varchar('role', { length: 50 }).default('user'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
}, (table) => ({
  emailIdx: uniqueIndex('email_idx').on(table.email),
}));

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

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

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

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

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

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

Database Connection#

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

// For Turso/LibSQL
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({
  url: process.env.DATABASE_URL!,
  authToken: process.env.DATABASE_AUTH_TOKEN,
});

export const db = drizzle(client, { schema });

Basic Queries#

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

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

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

// Where clause
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// Multiple conditions
const filteredUsers = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, 'admin'),
      gt(users.createdAt, new Date('2024-01-01'))
    )
  );

// OR conditions
const searchUsers = await db
  .select()
  .from(users)
  .where(
    or(
      like(users.email, '%@gmail.com'),
      like(users.email, '%@outlook.com')
    )
  );

// Order and limit
const recentUsers = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// Find one
const user = await db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .limit(1);

Insert Operations#

// Single insert
const newUser = await db
  .insert(users)
  .values({
    email: 'john@example.com',
    name: 'John Doe',
    password: hashedPassword,
  })
  .returning();

// Multiple insert
const newUsers = await db
  .insert(users)
  .values([
    { email: 'user1@example.com', name: 'User 1', password: 'hash1' },
    { email: 'user2@example.com', name: 'User 2', password: 'hash2' },
  ])
  .returning();

// On conflict (upsert)
const upserted = await db
  .insert(users)
  .values({
    email: 'john@example.com',
    name: 'John Doe',
    password: hashedPassword,
  })
  .onConflictDoUpdate({
    target: users.email,
    set: {
      name: 'John Doe Updated',
      updatedAt: new Date(),
    },
  })
  .returning();

// On conflict do nothing
await db
  .insert(users)
  .values({ email: 'existing@example.com', name: 'Test', password: 'hash' })
  .onConflictDoNothing();

Update Operations#

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

// Update with returning
const updated = await db
  .update(users)
  .set({
    isActive: false,
    updatedAt: new Date(),
  })
  .where(eq(users.email, 'john@example.com'))
  .returning();

// Conditional update
await db
  .update(posts)
  .set({ published: true })
  .where(
    and(
      eq(posts.authorId, userId),
      eq(posts.published, false)
    )
  );

// Increment
await db
  .update(posts)
  .set({
    views: sql`${posts.views} + 1`,
  })
  .where(eq(posts.id, postId));

Delete Operations#

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

// Delete with returning
const deleted = await db
  .delete(users)
  .where(eq(users.isActive, false))
  .returning();

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

Joins and Relations#

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

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

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

// Nested relations
const postsWithAll = await db.query.posts.findMany({
  with: {
    author: true,
    postTags: {
      with: {
        tag: true,
      },
    },
  },
  where: eq(posts.published, true),
  orderBy: desc(posts.createdAt),
  limit: 10,
});

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

Transactions#

// Basic transaction
await db.transaction(async (tx) => {
  const user = await tx
    .insert(users)
    .values({ email: 'new@example.com', name: 'New', password: 'hash' })
    .returning();

  await tx.insert(posts).values({
    title: 'First Post',
    content: 'Hello!',
    authorId: user[0].id,
  });
});

// With rollback
await db.transaction(async (tx) => {
  try {
    await tx.insert(users).values({ ... });
    await tx.insert(posts).values({ ... });

    if (someCondition) {
      tx.rollback();
    }
  } catch (error) {
    // Transaction automatically rolled back
    throw error;
  }
});

Migrations#

# Generate migration
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema (development)
npx drizzle-kit push

# Open Drizzle Studio
npx drizzle-kit studio

// Custom migration
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { db } from './db';

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

Best Practices#

Schema:
✓ Use proper column types
✓ Define relations explicitly
✓ Add indexes for queried columns
✓ Use timestamps for audit

Queries:
✓ Use prepared statements
✓ Select only needed columns
✓ Use transactions for multi-ops
✓ Handle errors appropriately

Performance:
✓ Add indexes
✓ Use connection pooling
✓ Batch inserts
✓ Profile slow queries

Drizzle ORM provides type-safe database access with a SQL-like API. Its schema-first approach with TypeScript gives excellent DX while maintaining full control over queries. Use relations for complex queries and transactions for data consistency.