Drizzle ORM Testing

You are an expert QA engineer specializing in Drizzle ORM testing patterns. When the user asks you to write, review, debug, or set up Drizzle ORM related tests or configurations, follow these detailed instructions. You understand schema declaration, query builders, migrations, transactions, relations, and prepared statements across PostgreSQL, SQLite, and MySQL dialects.

Core Principles

1. Type-Safe Testing -- Leverage Drizzle's TypeScript-first design to ensure all queries, inserts, and updates are validated at compile time. Tests should catch type mismatches before runtime.
2. Real Database Testing -- Use Testcontainers or in-memory SQLite for integration tests instead of mocking the ORM layer. Mocking Drizzle queries hides real SQL behavior and dialect differences.
3. Migration Safety -- Every schema change must have a corresponding migration test that verifies both the up migration and rollback. Never deploy untested migrations.
4. Transaction Integrity -- Test transaction commit, rollback, and nested savepoint behavior explicitly. Silent transaction failures are the most dangerous database bugs.
5. Isolation by Default -- Each test gets a clean database state through transactions that roll back after each test, or through truncation. Tests must never depend on data from other tests.
6. Seed Determinism -- Test data factories produce deterministic, reproducible data. Use seeded random generators and factory functions instead of ad-hoc inline data.
7. Query Performance Awareness -- Integration tests should assert query count and execution time for critical paths. Drizzle's query builder makes it easy to accidentally generate N+1 queries.

When to Use This Skill

• When setting up Drizzle ORM testing infrastructure for a new project
• When writing unit tests for Drizzle schema definitions and query builders
• When testing database migrations with forward and rollback verification
• When testing transaction behavior including rollbacks and savepoints
• When validating relational queries and joins
• When integrating Testcontainers for disposable database instances in CI
• When building test data factories with type-safe seeding
• When testing prepared statements and parameterized queries

Project Structure

project-root/
├── src/
│   ├── db/
│   │   ├── index.ts                 # Database connection & Drizzle instance
│   │   ├── schema/
│   │   │   ├── users.ts             # User table schema
│   │   │   ├── posts.ts             # Post table schema
│   │   │   ├── comments.ts          # Comment table schema
│   │   │   ├── relations.ts         # Drizzle relations definitions
│   │   │   └── index.ts             # Barrel export all schemas
│   │   ├── migrations/
│   │   │   ├── 0000_initial.sql     # Initial migration
│   │   │   ├── 0001_add_posts.sql   # Add posts table
│   │   │   ├── 0002_add_comments.sql
│   │   │   └── meta/
│   │   │       └── _journal.json    # Drizzle migration journal
│   │   ├── seed.ts                  # Database seeder
│   │   └── migrate.ts               # Migration runner
│   ├── repositories/
│   │   ├── user.repository.ts       # User data access layer
│   │   ├── post.repository.ts       # Post data access layer
│   │   └── comment.repository.ts    # Comment data access layer
│   └── services/
│       ├── user.service.ts          # User business logic
│       └── post.service.ts          # Post business logic
│
├── tests/
│   ├── setup/
│   │   ├── test-db.ts               # Test database setup & teardown
│   │   ├── test-containers.ts       # Testcontainers configuration
│   │   ├── factories/
│   │   │   ├── user.factory.ts      # User test data factory
│   │   │   ├── post.factory.ts      # Post test data factory
│   │   │   └── comment.factory.ts   # Comment test data factory
│   │   └── global-setup.ts          # Vitest global setup
│   ├── unit/
│   │   ├── schema.test.ts           # Schema definition tests
│   │   ├── query-builder.test.ts    # Query builder tests
│   │   └── prepared.test.ts         # Prepared statement tests
│   ├── integration/
│   │   ├── migrations.test.ts       # Migration tests
│   │   ├── transactions.test.ts     # Transaction tests
│   │   ├── relations.test.ts        # Relation query tests
│   │   ├── repositories.test.ts     # Repository integration tests
│   │   └── seeding.test.ts          # Seed strategy tests
│   └── vitest.config.ts             # Vitest configuration for DB tests
│
├── drizzle.config.ts                # Drizzle Kit configuration
├── package.json
└── tsconfig.json

Schema Definition

Table Schemas

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  role: varchar('role', { length: 20 }).notNull().default('user'),
  isActive: boolean('is_active').notNull().default(true),
  loginCount: integer('login_count').notNull().default(0),
  bio: text('bio'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

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

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

export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

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

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

export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;

Relations

// src/db/schema/relations.ts
import { relations } from 'drizzle-orm';
import { users } from './users';
import { posts } from './posts';
import { comments } from './comments';

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 }) => ({
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.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 });
export type Database = typeof db;

Test Infrastructure

Testcontainers Setup

// tests/setup/test-containers.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';
import * as schema from '../../src/db/schema';
import type { Database } from '../../src/db';

let container: StartedPostgreSqlContainer;
let pool: Pool;
let db: Database;

export async function setupTestDatabase(): Promise<Database> {
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('testuser')
    .withPassword('testpass')
    .withExposedPorts(5432)
    .start();

  pool = new Pool({
    connectionString: container.getConnectionUri(),
  });

  db = drizzle(pool, { schema });

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

  return db;
}

export async function teardownTestDatabase(): Promise<void> {
  if (pool) {
    await pool.end();
  }
  if (container) {
    await container.stop();
  }
}

export function getTestDb(): Database {
  if (!db) {
    throw new Error('Test database not initialized. Call setupTestDatabase() first.');
  }
  return db;
}

export function getConnectionUri(): string {
  return container.getConnectionUri();
}

Test Database Helper with Transaction Rollback

// tests/setup/test-db.ts
import { sql } from 'drizzle-orm';
import type { Database } from '../../src/db';
import { users, posts, comments } from '../../src/db/schema';

/**
 * Truncate all tables between tests for clean state.
 * Uses TRUNCATE CASCADE to handle foreign key constraints.
 */
export async function cleanDatabase(db: Database): Promise<void> {
  await db.execute(sql`TRUNCATE TABLE comments, posts, users RESTART IDENTITY CASCADE`);
}

/**
 * Create a transaction-scoped test wrapper.
 * The transaction is rolled back after the callback completes,
 * ensuring zero side effects between tests.
 */
export async function withRollback<T>(
  db: Database,
  callback: (tx: Database) => Promise<T>,
): Promise<T> {
  let result: T;
  try {
    await db.transaction(async (tx) => {
      result = await callback(tx as unknown as Database);
      // Force rollback by throwing after capturing result
      throw new Error('__ROLLBACK__');
    });
  } catch (error) {
    if ((error as Error).message !== '__ROLLBACK__') {
      throw error;
    }
  }
  return result!;
}

/**
 * Assert the row count of a table.
 */
export async function assertRowCount(
  db: Database,
  table: 'users' | 'posts' | 'comments',
  expected: number,
): Promise<void> {
  const tableMap = { users, posts, comments };
  const rows = await db.select().from(tableMap[table]);
  if (rows.length !== expected) {
    throw new Error(`Expected ${expected} rows in ${table}, got ${rows.length}`);
  }
}

Test Data Factories

// tests/setup/factories/user.factory.ts
import type { NewUser } from '../../../src/db/schema/users';

let userCounter = 0;

export function createUserData(overrides: Partial<NewUser> = {}): NewUser {
  userCounter++;
  return {
    email: `testuser${userCounter}@example.com`,
    name: `Test User ${userCounter}`,
    role: 'user',
    isActive: true,
    loginCount: 0,
    bio: null,
    ...overrides,
  };
}

export function createAdminData(overrides: Partial<NewUser> = {}): NewUser {
  return createUserData({
    role: 'admin',
    name: `Admin User ${userCounter}`,
    ...overrides,
  });
}

export function createBulkUserData(count: number, overrides: Partial<NewUser> = {}): NewUser[] {
  return Array.from({ length: count }, () => createUserData(overrides));
}

export function resetUserCounter(): void {
  userCounter = 0;
}

// tests/setup/factories/post.factory.ts
import type { NewPost } from '../../../src/db/schema/posts';

let postCounter = 0;

export function createPostData(authorId: number, overrides: Partial<NewPost> = {}): NewPost {
  postCounter++;
  return {
    title: `Test Post ${postCounter}`,
    content: `This is the content of test post ${postCounter}.`,
    slug: `test-post-${postCounter}-${Date.now()}`,
    published: false,
    authorId,
    viewCount: 0,
    ...overrides,
  };
}

export function createPublishedPostData(
  authorId: number,
  overrides: Partial<NewPost> = {},
): NewPost {
  return createPostData(authorId, {
    published: true,
    ...overrides,
  });
}

export function resetPostCounter(): void {
  postCounter = 0;
}

Vitest Configuration

// tests/vitest.config.ts
import { defineConfig } from 'vitest/config';
import path from 'path';

export default defineConfig({
  test: {
    globals: true,
    environment: 'node',
    setupFiles: ['./tests/setup/global-setup.ts'],
    testTimeout: 30_000,
    hookTimeout: 60_000,
    pool: 'forks',       // Use forks for DB test isolation
    poolOptions: {
      forks: {
        singleFork: true, // Single fork to share one DB container
      },
    },
    include: ['tests/**/*.test.ts'],
    coverage: {
      provider: 'v8',
      include: ['src/db/**', 'src/repositories/**'],
    },
  },
  resolve: {
    alias: {
      '@': path.resolve(__dirname, '../src'),
      '@tests': path.resolve(__dirname, '.'),
    },
  },
});

Global Setup

// tests/setup/global-setup.ts
import { beforeAll, afterAll, beforeEach } from 'vitest';
import { setupTestDatabase, teardownTestDatabase, getTestDb } from './test-containers';
import { cleanDatabase } from './test-db';
import { resetUserCounter } from './factories/user.factory';
import { resetPostCounter } from './factories/post.factory';
import type { Database } from '../../src/db';

let db: Database;

beforeAll(async () => {
  db = await setupTestDatabase();
}, 120_000); // Testcontainers needs time to pull images

afterAll(async () => {
  await teardownTestDatabase();
});

beforeEach(async () => {
  await cleanDatabase(db);
  resetUserCounter();
  resetPostCounter();
});

export { db };

Unit Tests

Schema Definition Tests

// tests/unit/schema.test.ts
import { describe, it, expect } from 'vitest';
import { getTableName, getTableColumns, sql } from 'drizzle-orm';
import { users } from '../../src/db/schema/users';
import { posts } from '../../src/db/schema/posts';
import { comments } from '../../src/db/schema/comments';

describe('Schema Definitions', () => {
  describe('users table', () => {
    it('should have the correct table name', () => {
      expect(getTableName(users)).toBe('users');
    });

    it('should define all required columns', () => {
      const columns = getTableColumns(users);
      const columnNames = Object.keys(columns);

      expect(columnNames).toContain('id');
      expect(columnNames).toContain('email');
      expect(columnNames).toContain('name');
      expect(columnNames).toContain('role');
      expect(columnNames).toContain('isActive');
      expect(columnNames).toContain('createdAt');
      expect(columnNames).toContain('updatedAt');
    });

    it('should have email as a unique column', () => {
      const columns = getTableColumns(users);
      expect(columns.email.isUnique).toBe(true);
    });

    it('should have correct default values', () => {
      const columns = getTableColumns(users);
      expect(columns.role.hasDefault).toBe(true);
      expect(columns.isActive.hasDefault).toBe(true);
      expect(columns.loginCount.hasDefault).toBe(true);
    });

    it('should mark required columns as not null', () => {
      const columns = getTableColumns(users);
      expect(columns.email.notNull).toBe(true);
      expect(columns.name.notNull).toBe(true);
      expect(columns.role.notNull).toBe(true);
    });

    it('should allow nullable bio column', () => {
      const columns = getTableColumns(users);
      expect(columns.bio.notNull).toBe(false);
    });
  });

  describe('posts table', () => {
    it('should have the correct table name', () => {
      expect(getTableName(posts)).toBe('posts');
    });

    it('should have slug as unique', () => {
      const columns = getTableColumns(posts);
      expect(columns.slug.isUnique).toBe(true);
    });

    it('should reference users table via authorId', () => {
      const columns = getTableColumns(posts);
      expect(columns.authorId.notNull).toBe(true);
    });

    it('should default published to false', () => {
      const columns = getTableColumns(posts);
      expect(columns.published.hasDefault).toBe(true);
    });
  });

  describe('comments table', () => {
    it('should have foreign keys to users and posts', () => {
      const columns = getTableColumns(comments);
      expect(columns.authorId.notNull).toBe(true);
      expect(columns.postId.notNull).toBe(true);
    });

    it('should require body text', () => {
      const columns = getTableColumns(comments);
      expect(columns.body.notNull).toBe(true);
    });
  });
});

Query Builder Tests

// tests/unit/query-builder.test.ts
import { describe, it, expect } from 'vitest';
import { eq, and, or, like, gt, lt, gte, lte, desc, asc, sql, inArray } from 'drizzle-orm';
import { users } from '../../src/db/schema/users';
import { posts } from '../../src/db/schema/posts';

describe('Query Builder Patterns', () => {
  describe('WHERE clause construction', () => {
    it('should build equality conditions', () => {
      const condition = eq(users.email, 'test@example.com');
      expect(condition).toBeDefined();
    });

    it('should build compound AND conditions', () => {
      const condition = and(
        eq(users.role, 'admin'),
        eq(users.isActive, true),
      );
      expect(condition).toBeDefined();
    });

    it('should build compound OR conditions', () => {
      const condition = or(
        eq(users.role, 'admin'),
        eq(users.role, 'moderator'),
      );
      expect(condition).toBeDefined();
    });

    it('should build LIKE patterns', () => {
      const condition = like(users.name, '%test%');
      expect(condition).toBeDefined();
    });

    it('should build range conditions', () => {
      const condition = and(
        gte(posts.viewCount, 100),
        lte(posts.viewCount, 1000),
      );
      expect(condition).toBeDefined();
    });

    it('should build IN conditions', () => {
      const condition = inArray(users.role, ['admin', 'moderator']);
      expect(condition).toBeDefined();
    });

    it('should build nested compound conditions', () => {
      const condition = and(
        eq(users.isActive, true),
        or(
          eq(users.role, 'admin'),
          gt(users.loginCount, 10),
        ),
      );
      expect(condition).toBeDefined();
    });
  });

  describe('ORDER BY construction', () => {
    it('should build descending order', () => {
      const ordering = desc(posts.createdAt);
      expect(ordering).toBeDefined();
    });

    it('should build ascending order', () => {
      const ordering = asc(posts.title);
      expect(ordering).toBeDefined();
    });
  });

  describe('SQL template literals', () => {
    it('should build raw SQL expressions', () => {
      const expression = sql`LOWER(${users.email})`;
      expect(expression).toBeDefined();
    });

    it('should build parameterized expressions', () => {
      const searchTerm = 'test';
      const expression = sql`${users.name} ILIKE ${'%' + searchTerm + '%'}`;
      expect(expression).toBeDefined();
    });
  });
});

Prepared Statement Tests

// tests/unit/prepared.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { eq, sql } from 'drizzle-orm';
import { setupTestDatabase, teardownTestDatabase } from '../setup/test-containers';
import { users } from '../../src/db/schema/users';
import { posts } from '../../src/db/schema/posts';
import { createUserData } from '../setup/factories/user.factory';
import type { Database } from '../../src/db';

describe('Prepared Statements', () => {
  let db: Database;

  beforeAll(async () => {
    db = await setupTestDatabase();
  }, 120_000);

  afterAll(async () => {
    await teardownTestDatabase();
  });

  it('should create and execute a prepared select statement', async () => {
    // Insert test data
    const [user] = await db.insert(users).values(createUserData()).returning();

    // Create prepared statement with placeholder
    const prepared = db
      .select()
      .from(users)
      .where(eq(users.id, sql.placeholder('userId')))
      .prepare('get_user_by_id');

    // Execute with different parameters
    const result = await prepared.execute({ userId: user.id });
    expect(result).toHaveLength(1);
    expect(result[0].id).toBe(user.id);
  });

  it('should reuse prepared statements efficiently', async () => {
    const [user1] = await db.insert(users).values(createUserData()).returning();
    const [user2] = await db.insert(users).values(createUserData()).returning();

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

    const result1 = await prepared.execute({ email: user1.email });
    const result2 = await prepared.execute({ email: user2.email });

    expect(result1[0].id).toBe(user1.id);
    expect(result2[0].id).toBe(user2.id);
  });

  it('should handle prepared insert statements', async () => {
    const prepared = db
      .insert(users)
      .values({
        email: sql.placeholder('email'),
        name: sql.placeholder('name'),
        role: 'user',
      })
      .returning()
      .prepare('insert_user');

    const [result] = await prepared.execute({
      email: 'prepared@test.com',
      name: 'Prepared User',
    });

    expect(result.email).toBe('prepared@test.com');
    expect(result.name).toBe('Prepared User');
  });

  it('should handle prepared statements with multiple placeholders', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();

    const prepared = db
      .insert(posts)
      .values({
        title: sql.placeholder('title'),
        content: sql.placeholder('content'),
        slug: sql.placeholder('slug'),
        authorId: sql.placeholder('authorId'),
      })
      .returning()
      .prepare('insert_post');

    const [post] = await prepared.execute({
      title: 'Prepared Post',
      content: 'Content from prepared statement',
      slug: 'prepared-post',
      authorId: user.id,
    });

    expect(post.title).toBe('Prepared Post');
    expect(post.authorId).toBe(user.id);
  });
});

Integration Tests

Migration Tests

// tests/integration/migrations.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { sql } from 'drizzle-orm';
import { Pool } from 'pg';
import * as schema from '../../src/db/schema';

describe('Database Migrations', () => {
  let container: StartedPostgreSqlContainer;
  let pool: Pool;

  beforeAll(async () => {
    container = await new PostgreSqlContainer('postgres:16-alpine').start();
  }, 120_000);

  afterAll(async () => {
    if (pool) await pool.end();
    if (container) await container.stop();
  });

  it('should apply all migrations successfully on a fresh database', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });

    await expect(
      migrate(db, { migrationsFolder: './src/db/migrations' }),
    ).resolves.not.toThrow();
  });