Drizzle ORM Testing

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();
  });

  it('should create the users table with correct columns', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });
    await migrate(db, { migrationsFolder: './src/db/migrations' });

    const result = await db.execute(sql`
      SELECT column_name, data_type, is_nullable
      FROM information_schema.columns
      WHERE table_name = 'users'
      ORDER BY ordinal_position
    `);

    const columns = result.rows.map((r: any) => r.column_name);
    expect(columns).toContain('id');
    expect(columns).toContain('email');
    expect(columns).toContain('name');
    expect(columns).toContain('role');
    expect(columns).toContain('is_active');
    expect(columns).toContain('created_at');
  });

  it('should create foreign key constraints', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });
    await migrate(db, { migrationsFolder: './src/db/migrations' });

    const result = await db.execute(sql`
      SELECT tc.constraint_name, tc.table_name, kcu.column_name,
             ccu.table_name AS foreign_table_name,
             ccu.column_name AS foreign_column_name
      FROM information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
      JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
      WHERE tc.constraint_type = 'FOREIGN KEY'
      ORDER BY tc.table_name
    `);

    const fks = result.rows as any[];
    const postAuthorFk = fks.find(
      (fk) => fk.table_name === 'posts' && fk.column_name === 'author_id',
    );
    expect(postAuthorFk).toBeDefined();
    expect(postAuthorFk.foreign_table_name).toBe('users');
    expect(postAuthorFk.foreign_column_name).toBe('id');
  });

  it('should create unique constraints', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });
    await migrate(db, { migrationsFolder: './src/db/migrations' });

    const result = await db.execute(sql`
      SELECT tc.constraint_name, kcu.column_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
      WHERE tc.constraint_type = 'UNIQUE' AND tc.table_name = 'users'
    `);

    const uniqueColumns = (result.rows as any[]).map((r) => r.column_name);
    expect(uniqueColumns).toContain('email');
  });

  it('should create indexes for performance', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });
    await migrate(db, { migrationsFolder: './src/db/migrations' });

    const result = await db.execute(sql`
      SELECT indexname, tablename
      FROM pg_indexes
      WHERE schemaname = 'public'
      ORDER BY tablename, indexname
    `);

    expect(result.rows.length).toBeGreaterThan(0);
  });

  it('should be idempotent when run multiple times', async () => {
    pool = new Pool({ connectionString: container.getConnectionUri() });
    const db = drizzle(pool, { schema });

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

Transaction Tests

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

describe('Transaction Behavior', () => {
  let db: Database;

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

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

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

  it('should commit transaction when all operations succeed', async () => {
    await db.transaction(async (tx) => {
      const [user] = await tx.insert(users).values(createUserData()).returning();
      await tx.insert(posts).values(createPostData(user.id)).returning();
    });

    const allUsers = await db.select().from(users);
    const allPosts = await db.select().from(posts);
    expect(allUsers).toHaveLength(1);
    expect(allPosts).toHaveLength(1);
  });

  it('should rollback transaction when an operation fails', async () => {
    // Pre-insert a user to trigger unique constraint violation
    await db.insert(users).values(createUserData({ email: 'duplicate@test.com' }));

    try {
      await db.transaction(async (tx) => {
        // This succeeds
        await tx.insert(users).values(createUserData({ email: 'new@test.com' }));
        // This fails due to duplicate email
        await tx.insert(users).values(createUserData({ email: 'duplicate@test.com' }));
      });
    } catch {
      // Expected to fail
    }

    // Only the pre-inserted user should exist
    const allUsers = await db.select().from(users);
    expect(allUsers).toHaveLength(1);
    expect(allUsers[0].email).toBe('duplicate@test.com');
  });

  it('should support nested transactions via savepoints', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();

    await db.transaction(async (tx) => {
      await tx.insert(posts).values(createPostData(user.id, { title: 'Post 1' })).returning();

      try {
        await tx.transaction(async (nestedTx) => {
          await nestedTx
            .insert(posts)
            .values(createPostData(user.id, { title: 'Post 2' }))
            .returning();
          throw new Error('Force nested rollback');
        });
      } catch {
        // Nested transaction rolled back, outer continues
      }

      await tx.insert(posts).values(createPostData(user.id, { title: 'Post 3' })).returning();
    });

    const allPosts = await db.select().from(posts);
    // Post 1 and Post 3 committed, Post 2 rolled back
    expect(allPosts).toHaveLength(2);
    expect(allPosts.map((p) => p.title)).toContain('Post 1');
    expect(allPosts.map((p) => p.title)).toContain('Post 3');
    expect(allPosts.map((p) => p.title)).not.toContain('Post 2');
  });

  it('should isolate concurrent transactions', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();

    // Run two transactions concurrently
    const results = await Promise.allSettled([
      db.transaction(async (tx) => {
        await tx.insert(posts).values(
          createPostData(user.id, { slug: 'unique-slug' }),
        );
      }),
      db.transaction(async (tx) => {
        await tx.insert(posts).values(
          createPostData(user.id, { slug: 'unique-slug' }),
        );
      }),
    ]);

    // One should succeed, one should fail due to unique constraint
    const successes = results.filter((r) => r.status === 'fulfilled');
    const failures = results.filter((r) => r.status === 'rejected');
    expect(successes).toHaveLength(1);
    expect(failures).toHaveLength(1);
  });

  it('should handle transaction with multiple table operations', async () => {
    await db.transaction(async (tx) => {
      const [user] = await tx.insert(users).values(createUserData()).returning();
      const [post] = await tx
        .insert(posts)
        .values(createPostData(user.id))
        .returning();

      // Update user login count within same transaction
      await tx
        .update(users)
        .set({ loginCount: 1 })
        .where(eq(users.id, user.id));

      // Update post view count
      await tx
        .update(posts)
        .set({ viewCount: sql`${posts.viewCount} + 1` })
        .where(eq(posts.id, post.id));
    });

    const [user] = await db.select().from(users);
    const [post] = await db.select().from(posts);
    expect(user.loginCount).toBe(1);
    expect(post.viewCount).toBe(1);
  });

  it('should rollback on unhandled errors', async () => {
    try {
      await db.transaction(async (tx) => {
        await tx.insert(users).values(createUserData());
        throw new Error('Unexpected application error');
      });
    } catch {
      // Expected
    }

    const allUsers = await db.select().from(users);
    expect(allUsers).toHaveLength(0);
  });
});

Relation Query Tests

// tests/integration/relations.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from 'vitest';
import { eq } from 'drizzle-orm';
import { setupTestDatabase, teardownTestDatabase } from '../setup/test-containers';
import { cleanDatabase } from '../setup/test-db';
import { users } from '../../src/db/schema/users';
import { posts } from '../../src/db/schema/posts';
import { comments } from '../../src/db/schema/comments';
import { createUserData } from '../setup/factories/user.factory';
import { createPostData, createPublishedPostData } from '../setup/factories/post.factory';
import type { Database } from '../../src/db';

describe('Relational Queries', () => {
  let db: Database;

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

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

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

  it('should query user with their posts using relations', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();
    await db.insert(posts).values([
      createPostData(user.id, { title: 'First Post' }),
      createPostData(user.id, { title: 'Second Post' }),
    ]);

    const result = await db.query.users.findFirst({
      where: eq(users.id, user.id),
      with: {
        posts: true,
      },
    });

    expect(result).toBeDefined();
    expect(result!.posts).toHaveLength(2);
    expect(result!.posts.map((p) => p.title)).toEqual(
      expect.arrayContaining(['First Post', 'Second Post']),
    );
  });

  it('should query post with author and comments', async () => {
    const [author] = await db.insert(users).values(createUserData()).returning();
    const [commenter] = await db.insert(users).values(createUserData()).returning();
    const [post] = await db
      .insert(posts)
      .values(createPostData(author.id))
      .returning();

    await db.insert(comments).values([
      { body: 'Great post!', authorId: commenter.id, postId: post.id },
      { body: 'Thanks for sharing', authorId: commenter.id, postId: post.id },
    ]);

    const result = await db.query.posts.findFirst({
      where: eq(posts.id, post.id),
      with: {
        author: true,
        comments: {
          with: {
            author: true,
          },
        },
      },
    });

    expect(result).toBeDefined();
    expect(result!.author.id).toBe(author.id);
    expect(result!.comments).toHaveLength(2);
    expect(result!.comments[0].author.id).toBe(commenter.id);
  });

  it('should filter related records in relational queries', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();
    await db.insert(posts).values([
      createPublishedPostData(user.id, { title: 'Published Post' }),
      createPostData(user.id, { title: 'Draft Post' }),
    ]);

    const result = await db.query.users.findFirst({
      where: eq(users.id, user.id),
      with: {
        posts: {
          where: eq(posts.published, true),
        },
      },
    });

    expect(result!.posts).toHaveLength(1);
    expect(result!.posts[0].title).toBe('Published Post');
  });

  it('should limit and order related records', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();
    for (let i = 0; i < 10; i++) {
      await db.insert(posts).values(
        createPostData(user.id, { title: `Post ${i}`, viewCount: i * 10 }),
      );
    }

    const result = await db.query.users.findFirst({
      where: eq(users.id, user.id),
      with: {
        posts: {
          orderBy: (posts, { desc }) => [desc(posts.viewCount)],
          limit: 3,
        },
      },
    });

    expect(result!.posts).toHaveLength(3);
    expect(result!.posts[0].viewCount).toBe(90);
    expect(result!.posts[1].viewCount).toBe(80);
    expect(result!.posts[2].viewCount).toBe(70);
  });

  it('should handle cascade delete through relations', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();
    const [post] = await db
      .insert(posts)
      .values(createPostData(user.id))
      .returning();
    await db.insert(comments).values({
      body: 'A comment',
      authorId: user.id,
      postId: post.id,
    });

    // Delete the user -- should cascade to posts and comments
    await db.delete(users).where(eq(users.id, user.id));

    const remainingPosts = await db.select().from(posts);
    const remainingComments = await db.select().from(comments);
    expect(remainingPosts).toHaveLength(0);
    expect(remainingComments).toHaveLength(0);
  });

  it('should query many-to-many through join tables', async () => {
    const [user1] = await db.insert(users).values(createUserData()).returning();
    const [user2] = await db.insert(users).values(createUserData()).returning();
    const [post] = await db
      .insert(posts)
      .values(createPostData(user1.id))
      .returning();

    await db.insert(comments).values([
      { body: 'Comment from user 1', authorId: user1.id, postId: post.id },
      { body: 'Comment from user 2', authorId: user2.id, postId: post.id },
    ]);

    const result = await db.query.posts.findFirst({
      where: eq(posts.id, post.id),
      with: {
        comments: {
          with: {
            author: {
              columns: { id: true, name: true, email: true },
            },
          },
        },
      },
    });

    const commentAuthors = result!.comments.map((c) => c.author.name);
    expect(commentAuthors).toHaveLength(2);
  });

  it('should select specific columns in relational queries', async () => {
    const [user] = await db.insert(users).values(createUserData()).returning();
    await db.insert(posts).values(createPostData(user.id));

    const result = await db.query.users.findFirst({
      columns: { id: true, name: true },
      where: eq(users.id, user.id),
      with: {
        posts: {
          columns: { id: true, title: true },
        },
      },
    });

    expect(result).toHaveProperty('id');
    expect(result).toHaveProperty('name');
    expect(result).not.toHaveProperty('email');
    expect(result!.posts[0]).toHaveProperty('title');
    expect(result!.posts[0]).not.toHaveProperty('content');
  });
});

Repository Integration Tests

// tests/integration/repositories.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from 'vitest';
import { eq, desc, sql, like, and, gte } from 'drizzle-orm';
import { setupTestDatabase, teardownTestDatabase } from '../setup/test-containers';
import { cleanDatabase } from '../setup/test-db';
import { users } from '../../src/db/schema/users';
import { posts } from '../../src/db/schema/posts';
import { createUserData, createBulkUserData } from '../setup/factories/user.factory';
import { createPostData, createPublishedPostData } from '../setup/factories/post.factory';
import type { Database } from '../../src/db';

describe('Repository Patterns', () => {
  let db: Database;

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

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

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

  describe('CRUD Operations', () => {
    it('should insert and return a new user', async () =

…(truncated)