Modern Database Design Patterns with Drizzle ORM

Drizzle ORM brings type-safe database queries to TypeScript applications. Learn how to design efficient database schemas and optimize queries for production applications.

Why Drizzle ORM?

Drizzle stands out for several reasons:

• Type-safe queries with full TypeScript support
• Zero runtime overhead - generates plain SQL
• Excellent performance - as fast as raw SQL
• Intuitive API - easy to learn and use
• Migration support - built-in schema migrations

Schema Design

Defining Tables

Create type-safe table schemas:

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(),
  avatarUrl: text('avatar_url'),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export const workspaces = pgTable('workspaces', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
  ownerId: integer('owner_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow(),
});

Relationships

Define relationships between tables:

import { relations } from 'drizzle-orm';

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

export const workspacesRelations = relations(workspaces, ({ one, many }) => ({
  owner: one(users, {
    fields: [workspaces.ownerId],
    references: [users.id],
  }),
  members: many(workspaceMembers),
}));

Query Patterns

Basic Queries

Type-safe CRUD operations:

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

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

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

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

Advanced Queries

Complex queries with joins and filters:

import { eq, and, or, like, gt } from 'drizzle-orm';

const result = await db
  .select({
    user: users,
    workspace: workspaces,
  })
  .from(users)
  .leftJoin(workspaces, eq(workspaces.ownerId, users.id))
  .where(
    and(
      eq(users.emailVerified, true),
      gt(users.createdAt, new Date('2025-01-01'))
    )
  );

Performance Optimization

Indexing Strategy

Add indexes for frequently queried columns:

import { index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  // ... columns
}, (table) => ({
  emailIdx: index('email_idx').on(table.email),
  createdAtIdx: index('created_at_idx').on(table.createdAt),
}));

Query Optimization

Use prepared statements for repeated queries:

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

// Execute with parameters
const user = await getUserByEmail.execute({ email: 'user@example.com' });

Connection Pooling

Configure connection pooling for production:

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!, {
  max: 10,
  idle_timeout: 20,
  connect_timeout: 10,
});

export const db = drizzle(client);

Migrations

Creating Migrations

Generate migrations from schema changes:

npx drizzle-kit generate:pg

Running Migrations

Apply migrations to your database:

import { migrate } from 'drizzle-orm/postgres-js/migrator';

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

Best Practices

1. Use transactions for related operations
2. Implement soft deletes instead of hard deletes
3. Add timestamps to all tables
4. Use enums for fixed value sets
5. Normalize data appropriately
6. Monitor query performance in production

Conclusion

Drizzle ORM provides the perfect balance between type safety and performance. By following these patterns, you can build robust, scalable database layers for your applications.

Explore our SaaS boilerplate to see these patterns in production.