Drizzle

Testing Drizzle ORM: Query Testing, Schema Validation, and CI Migrations

HelpMeTest

HelpMeTest

— 8 min read
Testing Drizzle ORM: Query Testing, Schema Validation, and CI Migrations

Drizzle ORM's SQL-first design makes it highly testable: you can run queries against in-memory SQLite for fast unit tests, swap in a real Postgres container for integration tests with minimal code changes, and validate your schema definitions statically. This post walks through all three layers and shows how to wire them into a CI pipeline.

Key Takeaways

Drizzle's dialect abstraction makes SQLite unit tests viable Because Drizzle generates standard SQL, most queries work identically against SQLite and Postgres — use SQLite for speed, Postgres for correctness.

Test schema definitions at the type level Drizzle's inferred types ($inferSelect, $inferInsert) let you write compile-time assertions that catch schema regressions without running any queries.

Use drizzle-kit push for migration tests in isolation In a TestContainers environment, drizzle-kit push applies your schema directly without migration files — fast and reliable for testing the target state.

Seed helpers should return typed records Write seed functions that return the created records so tests can reference inserted IDs without extra queries, keeping tests readable.

Pin your SQLite version in CI Different better-sqlite3 versions behave differently with INTEGER PRIMARY KEY and RETURNING — pin the version and test on the same Node version locally and in CI.

Drizzle ORM has grown rapidly as a TypeScript-first alternative to Prisma, trading the magic of a query engine for explicit SQL generation that you can read and reason about. That explicitness is a testing advantage: because Drizzle is just building SQL strings, you can run the same query code against different databases with minimal ceremony. This post shows you how to exploit that.

Project Structure and Setup

Assume a blog application with the following Drizzle schema:

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

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

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

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

// Export inferred types — useful for testing
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Install test dependencies:

npm install --save-dev jest ts-jest @types/jest
npm install --save-dev better-sqlite3 @types/better-sqlite3
npm install --save-dev @testcontainers/postgresql
npm install drizzle-orm better-sqlite3

Unit Tests with In-Memory SQLite

Drizzle supports multiple dialects. For unit tests, swap Postgres for SQLite in-memory — zero startup time, no Docker required.

SQLite Schema Mirror

Create SQLite equivalents of your Postgres tables. The column types map closely:

// src/db/schema.sqlite.ts
import {
  sqliteTable,
  integer,
  text,
  real,
} from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: text('created_at')
    .notNull()
    .$defaultFn(() => new Date().toISOString()),
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content'),
  published: integer('published', { mode: 'boolean' }).default(false).notNull(),
  authorId: integer('author_id').notNull().references(() => users.id),
  createdAt: text('created_at')
    .notNull()
    .$defaultFn(() => new Date().toISOString()),
});

Test Database Factory

// src/test-utils/createTestDb.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from '../db/schema.sqlite';

export function createTestDb() {
  const sqlite = new Database(':memory:');

  // Create tables directly — no migration files needed for unit tests
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      content TEXT,
      published INTEGER NOT NULL DEFAULT 0,
      author_id INTEGER NOT NULL REFERENCES users(id),
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  const db = drizzle(sqlite, { schema });

  return { db, sqlite };
}

Service Layer to Test

// src/services/postService.ts
import { eq, and, desc, count } from 'drizzle-orm';
import type { NodePgDatabase } from 'drizzle-orm/node-postgres';
import type { BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import * as schema from '../db/schema';

// Accept either db type via a union — or use a common interface
type AnyDb = NodePgDatabase<typeof schema> | BetterSQLite3Database<typeof schema>;

export function createPostService(db: AnyDb) {
  return {
    async getPublishedPosts() {
      return db
        .select()
        .from(schema.posts)
        .where(eq(schema.posts.published, true))
        .orderBy(desc(schema.posts.createdAt));
    },

    async getPostsByAuthor(authorId: number) {
      return db
        .select()
        .from(schema.posts)
        .where(eq(schema.posts.authorId, authorId))
        .orderBy(desc(schema.posts.createdAt));
    },

    async publishPost(postId: number) {
      const result = await db
        .update(schema.posts)
        .set({ published: true })
        .where(and(eq(schema.posts.id, postId), eq(schema.posts.published, false)))
        .returning();

      if (result.length === 0) {
        throw new Error(`Post ${postId} not found or already published`);
      }

      return result[0];
    },

    async getAuthorStats(authorId: number) {
      const [stats] = await db
        .select({ total: count() })
        .from(schema.posts)
        .where(eq(schema.posts.authorId, authorId));

      return { authorId, totalPosts: stats.total };
    },
  };
}

Unit Tests Against SQLite

// src/services/postService.unit.test.ts
import { eq } from 'drizzle-orm';
import { createTestDb } from '../test-utils/createTestDb';
import * as schema from '../db/schema.sqlite';
import { createPostService } from './postService';

let db: ReturnType<typeof createTestDb>['db'];
let service: ReturnType<typeof createPostService>;

beforeEach(() => {
  // Fresh in-memory DB for each test — fast and isolated
  const testDb = createTestDb();
  db = testDb.db;
  service = createPostService(db as any);
});

async function seedUser(email = 'test@example.com', name = 'Test User') {
  const [user] = await db.insert(schema.users).values({ email, name }).returning();
  return user;
}

async function seedPost(authorId: number, published = false) {
  const [post] = await db
    .insert(schema.posts)
    .values({ title: 'Test Post', authorId, published })
    .returning();
  return post;
}

describe('getPublishedPosts', () => {
  it('returns only published posts', async () => {
    const user = await seedUser();
    await seedPost(user.id, true);
    await seedPost(user.id, false);
    await seedPost(user.id, true);

    const posts = await service.getPublishedPosts();

    expect(posts).toHaveLength(2);
    expect(posts.every((p) => p.published)).toBe(true);
  });

  it('returns posts ordered by createdAt descending', async () => {
    const user = await seedUser();
    const first = await seedPost(user.id, true);
    const second = await seedPost(user.id, true);

    const posts = await service.getPublishedPosts();

    // Most recently inserted should come first
    expect(posts[0].id).toBe(second.id);
    expect(posts[1].id).toBe(first.id);
  });

  it('returns empty array when no published posts exist', async () => {
    const user = await seedUser();
    await seedPost(user.id, false);

    const posts = await service.getPublishedPosts();

    expect(posts).toHaveLength(0);
  });
});

describe('publishPost', () => {
  it('marks a draft post as published', async () => {
    const user = await seedUser();
    const post = await seedPost(user.id, false);

    const result = await service.publishPost(post.id);

    expect(result.published).toBe(true);
    expect(result.id).toBe(post.id);
  });

  it('throws when post does not exist', async () => {
    await expect(service.publishPost(9999)).rejects.toThrow('not found or already published');
  });

  it('throws when post is already published', async () => {
    const user = await seedUser();
    const post = await seedPost(user.id, true);

    await expect(service.publishPost(post.id)).rejects.toThrow('not found or already published');
  });
});

describe('getAuthorStats', () => {
  it('counts all posts for an author', async () => {
    const user = await seedUser();
    await seedPost(user.id, true);
    await seedPost(user.id, false);
    await seedPost(user.id, true);

    const stats = await service.getAuthorStats(user.id);

    expect(stats.totalPosts).toBe(3);
  });

  it('returns zero for author with no posts', async () => {
    const user = await seedUser();

    const stats = await service.getAuthorStats(user.id);

    expect(stats.totalPosts).toBe(0);
  });
});

Schema Validation Tests

Drizzle's TypeScript inference lets you write compile-time tests for your schema shape. These catch regressions when you add or rename columns:

// src/db/schema.test.ts
import { users, posts, type User, type NewUser, type Post, type NewPost } from './schema';

// These are compile-time assertions — if the type changes, TypeScript compilation fails
type AssertUserHasEmail = User['email'] extends string ? true : never;
type AssertUserEmailRequired = NewUser['email'] extends string ? true : never;
type AssertPostPublishedIsBoolean = Post['published'] extends boolean ? true : never;
type AssertPostAuthorIdRequired = NewPost['authorId'] extends number ? true : never;

// Confirm optional fields are optional
type AssertPostContentOptional = NewPost['content'] extends string | null | undefined ? true : never;

// Runtime: verify table names match what you expect in SQL
describe('schema table names', () => {
  it('users table is named "users"', () => {
    expect(users[Symbol.for('drizzle:Name')]).toBe('users');
  });

  it('posts table is named "posts"', () => {
    expect(posts[Symbol.for('drizzle:Name')]).toBe('posts');
  });
});

describe('schema column presence', () => {
  it('users table has expected columns', () => {
    const columns = Object.keys(users);
    expect(columns).toContain('id');
    expect(columns).toContain('email');
    expect(columns).toContain('name');
    expect(columns).toContain('createdAt');
  });

  it('posts table references users via authorId', () => {
    const authorIdColumn = (posts as any).authorId;
    expect(authorIdColumn).toBeDefined();
  });
});

Integration Tests with Real Postgres

For queries that use Postgres-specific features — RETURNING, full-text search, JSON operators, ON CONFLICT DO UPDATE — you need the real engine.

TestContainers Setup

// jest.integration.globalSetup.ts
import { PostgreSqlContainer } from '@testcontainers/postgresql';

export default async function setup() {
  const container = await new PostgreSqlContainer('postgres:16-alpine').start();

  process.env.TEST_DATABASE_URL = container.getConnectionUri();
  (global as any).__PG_CONTAINER__ = container;

  // Apply schema using drizzle-kit
  const { execSync } = await import('child_process');
  execSync('npx drizzle-kit push', {
    env: { ...process.env, DATABASE_URL: container.getConnectionUri() },
    stdio: 'inherit',
  });
}

Integration Test with Postgres

// src/services/postService.integration.test.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from '../db/schema';
import { createPostService } from './postService';

let pool: Pool;
let db: ReturnType<typeof drizzle>;
let service: ReturnType<typeof createPostService>;

beforeAll(async () => {
  pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
  db = drizzle(pool, { schema });
  service = createPostService(db);
});

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

beforeEach(async () => {
  await db.delete(schema.posts);
  await db.delete(schema.users);
});

describe('upsert behavior (Postgres-specific)', () => {
  it('handles ON CONFLICT with real constraint', async () => {
    // Insert initial user
    await db.insert(schema.users).values({
      email: 'frank@example.com',
      name: 'Frank',
    });

    // Attempt duplicate — should throw unique violation
    await expect(
      db.insert(schema.users).values({
        email: 'frank@example.com',
        name: 'Frank Duplicate',
      })
    ).rejects.toThrow();
  });

  it('cascade deletes posts when user is deleted', async () => {
    const [user] = await db
      .insert(schema.users)
      .values({ email: 'grace@example.com', name: 'Grace' })
      .returning();

    await db.insert(schema.posts).values([
      { title: 'Post 1', authorId: user.id, published: true },
      { title: 'Post 2', authorId: user.id, published: false },
    ]);

    await db.delete(schema.users).where(eq(schema.users.id, user.id));

    const remainingPosts = await db
      .select()
      .from(schema.posts)
      .where(eq(schema.posts.authorId, user.id));

    expect(remainingPosts).toHaveLength(0);
  });
});

CI Pipeline Configuration

A complete GitHub Actions workflow that tests both layers:

# .github/workflows/test.yaml
name: Test

on: [push, pull_request]

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      - run: npm ci
      - run: npm run test:unit

  integration-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      - run: npm ci
      # TestContainers pulls Postgres automatically — no services: block needed
      - run: npm run test:integration
        env:
          TESTCONTAINERS_RYUK_DISABLED: 'true'  # avoids Docker socket permission issues in some CI envs

Add these scripts to package.json:

{
  "scripts": {
    "test:unit": "jest --selectProjects unit",
    "test:integration": "jest --selectProjects integration --runInBand",
    "test": "npm run test:unit && npm run test:integration"
  }
}

The --runInBand flag for integration tests prevents multiple TestContainers instances from starting simultaneously, which can exhaust Docker's available ports in CI.

When SQLite Is Not Enough

SQLite diverges from Postgres in ways that matter for testing:

  • Type coercion: SQLite coerces types liberally; Postgres does not
  • RETURNING support: SQLite added RETURNING in 3.35.0, but behavior differs in edge cases
  • JSON operators: Postgres's -> and ->> don't exist in SQLite
  • Constraint timing: Postgres supports DEFERRABLE constraints; SQLite does not
  • Full-text search: Completely different syntax

For anything using these features, write integration tests against Postgres. Use the SQLite unit tests for pure query logic — filtering, sorting, aggregations on simple schemas. The two-layer approach gives you the best of both worlds: a sub-second feedback loop for logic tests, and a reliable correctness check for database-specific behavior.

Read more