Database Testing

Cloudflare D1 Testing Guide: Workers, Miniflare, and Vitest

HelpMeTest

HelpMeTest

— 7 min read
Cloudflare D1 Testing Guide: Workers, Miniflare, and Vitest

Cloudflare D1 is a serverless SQL database built for the edge — designed to run alongside Cloudflare Workers with zero-latency reads and automatic global replication. But testing D1-backed Workers presents a distinct challenge: your code runs in a Cloudflare Workers runtime environment, not a standard Node.js process, and the D1 bindings are Cloudflare-specific APIs that don't exist in a regular test environment.

The solution is Miniflare — Cloudflare's local simulator for Workers. Combined with Vitest's Workers pool, you can run comprehensive tests for D1-backed Workers without ever hitting Cloudflare's production infrastructure.

How D1 Testing Works

When you write a D1-backed Worker, it uses the D1 binding injected into the env object:

// src/index.ts
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);
    
    if (url.pathname === '/users') {
      const { results } = await env.DB.prepare(
        'SELECT * FROM users ORDER BY created_at DESC'
      ).all();
      return Response.json(results);
    }
    
    return new Response('Not Found', { status: 404 });
  },
};

export interface Env {
  DB: D1Database;
}

In tests, Miniflare provides a local D1 implementation backed by SQLite that behaves identically to the production D1 binding. Your test code never knows it's not talking to real Cloudflare D1.

Setting Up the Test Environment

Install the required dependencies:

npm install --save-dev \
  vitest \
  @cloudflare/vitest-pool-workers \
  wrangler

Configure Vitest to use the Workers pool:

// vitest.config.ts
import { defineConfig } from 'vitest/config';
import { defineWorkersConfig } from '@cloudflare/vitest-pool-workers/config';

export default defineWorkersConfig({
  test: {
    poolOptions: {
      workers: {
        wrangler: { configPath: './wrangler.toml' },
        miniflare: {
          d1Databases: ['DB'],
        },
      },
    },
  },
});

Your wrangler.toml defines the D1 binding:

name = "my-worker"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id-here"

[env.test]
[[env.test.d1_databases]]
binding = "DB"
database_name = "my-database-test"
database_id = "local"

Writing D1 Migration SQL

D1 uses SQL migration files. Keep them in a migrations/ directory:

-- migrations/0001_initial.sql
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  published INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);

Testing Worker Handlers Directly

The @cloudflare/vitest-pool-workers package provides a SELF object that represents your Worker as an HTTP client, and lets you access bindings directly:

// src/index.test.ts
import { describe, it, expect, beforeEach } from 'vitest';
import { SELF, env } from 'cloudflare:test';
import { readFileSync } from 'fs';

// Apply migrations before each test
async function applyMigrations(db: D1Database) {
  const sql = readFileSync('./migrations/0001_initial.sql', 'utf8');
  await db.exec(sql);
}

async function clearTables(db: D1Database) {
  await db.exec('DELETE FROM posts; DELETE FROM users;');
}

describe('Worker D1 Integration', () => {
  beforeEach(async () => {
    await applyMigrations(env.DB);
    await clearTables(env.DB);
  });

  it('GET /users returns empty array when no users exist', async () => {
    const response = await SELF.fetch('http://example.com/users');
    expect(response.status).toBe(200);
    
    const data = await response.json();
    expect(data).toEqual([]);
  });

  it('GET /users returns created users', async () => {
    // Insert test data directly via D1 binding
    await env.DB.prepare(
      'INSERT INTO users (email, name) VALUES (?1, ?2), (?3, ?4)'
    ).bind('alice@test.com', 'Alice', 'bob@test.com', 'Bob').run();

    const response = await SELF.fetch('http://example.com/users');
    const users = await response.json() as any[];

    expect(users).toHaveLength(2);
    expect(users.map((u: any) => u.name)).toContain('Alice');
  });

  it('POST /users creates a new user', async () => {
    const response = await SELF.fetch('http://example.com/users', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ email: 'new@test.com', name: 'New User' }),
    });

    expect(response.status).toBe(201);
    
    const user = await response.json() as any;
    expect(user.email).toBe('new@test.com');

    // Verify in database
    const { results } = await env.DB.prepare(
      'SELECT * FROM users WHERE email = ?'
    ).bind('new@test.com').all();
    
    expect(results).toHaveLength(1);
    expect(results[0].name).toBe('New User');
  });

  it('POST /users returns 409 for duplicate email', async () => {
    await env.DB.prepare(
      'INSERT INTO users (email, name) VALUES (?1, ?2)'
    ).bind('exists@test.com', 'Existing').run();

    const response = await SELF.fetch('http://example.com/users', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ email: 'exists@test.com', name: 'Duplicate' }),
    });

    expect(response.status).toBe(409);
  });
});

Testing D1 Data Access Logic Directly

You can also test your database logic directly through D1 bindings without going through HTTP, which is faster for pure data layer tests:

// src/db/users.ts
export async function createUser(
  db: D1Database,
  data: { email: string; name: string; role?: string }
) {
  const { results } = await db
    .prepare(
      'INSERT INTO users (email, name, role) VALUES (?1, ?2, ?3) RETURNING *'
    )
    .bind(data.email, data.name, data.role ?? 'user')
    .all();
  
  return results[0] as User;
}

export async function getUsersByRole(db: D1Database, role: string) {
  const { results } = await db
    .prepare('SELECT * FROM users WHERE role = ?1 ORDER BY created_at DESC')
    .bind(role)
    .all();
  return results as User[];
}

export async function getUserWithPosts(db: D1Database, userId: number) {
  const { results: users } = await db
    .prepare('SELECT * FROM users WHERE id = ?1')
    .bind(userId)
    .all();
  
  if (!users[0]) return null;

  const { results: posts } = await db
    .prepare('SELECT * FROM posts WHERE user_id = ?1 ORDER BY created_at DESC')
    .bind(userId)
    .all();

  return { ...users[0], posts };
}
// src/db/users.test.ts
import { describe, it, expect, beforeEach } from 'vitest';
import { env } from 'cloudflare:test';
import { createUser, getUsersByRole, getUserWithPosts } from './users';

describe('D1 User Data Access', () => {
  beforeEach(async () => {
    await env.DB.exec(/* migrations SQL */);
    await env.DB.exec('DELETE FROM posts; DELETE FROM users;');
  });

  it('createUser inserts with default role', async () => {
    const user = await createUser(env.DB, {
      email: 'test@example.com',
      name: 'Test',
    });

    expect(user.email).toBe('test@example.com');
    expect(user.role).toBe('user');
    expect(user.id).toBeDefined();
  });

  it('getUsersByRole filters correctly', async () => {
    await createUser(env.DB, { email: 'a@test.com', name: 'Admin', role: 'admin' });
    await createUser(env.DB, { email: 'u@test.com', name: 'User', role: 'user' });
    await createUser(env.DB, { email: 'g@test.com', name: 'Guest', role: 'guest' });

    const admins = await getUsersByRole(env.DB, 'admin');
    expect(admins).toHaveLength(1);
    expect(admins[0].email).toBe('a@test.com');
  });

  it('getUserWithPosts returns user with associated posts', async () => {
    const user = await createUser(env.DB, {
      email: 'author@test.com',
      name: 'Author',
    });

    await env.DB.prepare(
      'INSERT INTO posts (user_id, title, body) VALUES (?1, ?2, ?3)'
    ).bind(user.id, 'First Post', 'Content here').run();

    const result = await getUserWithPosts(env.DB, Number(user.id));

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

  it('getUserWithPosts returns null for non-existent user', async () => {
    const result = await getUserWithPosts(env.DB, 9999);
    expect(result).toBeNull();
  });
});

Testing D1 Prepared Statement Batching

D1's batch API lets you execute multiple statements in a single network round trip. Test batch behavior thoroughly:

// src/db/batch.ts
export async function bulkInsertUsers(
  db: D1Database,
  users: Array<{ email: string; name: string }>
) {
  const statements = users.map((u) =>
    db.prepare('INSERT INTO users (email, name) VALUES (?1, ?2) RETURNING id')
      .bind(u.email, u.name)
  );

  const results = await db.batch(statements);
  return results.map((r) => r.results[0]);
}
it('bulk inserts multiple users in one batch', async () => {
  const inserted = await bulkInsertUsers(env.DB, [
    { email: 'x@test.com', name: 'X' },
    { email: 'y@test.com', name: 'Y' },
    { email: 'z@test.com', name: 'Z' },
  ]);

  expect(inserted).toHaveLength(3);
  expect(inserted.every((r) => r.id != null)).toBe(true);

  const { results } = await env.DB.prepare('SELECT COUNT(*) as cnt FROM users').all();
  expect(results[0].cnt).toBe(3);
});

CI Configuration for D1 Tests

D1 tests with Miniflare run entirely locally — no Cloudflare account needed:

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

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      
      - run: npm ci
      
      - name: Run D1 Worker tests
        run: npx vitest run
        # No Cloudflare credentials needed — Miniflare handles everything
      
      - name: Upload coverage
        uses: codecov/codecov-action@v4
        if: always()

For tests that do need to verify against real Cloudflare D1 (deployment smoke tests), add a separate job:

  smoke-test:
    runs-on: ubuntu-latest
    needs: test
    if: github.ref == 'refs/heads/main'
    steps:
      - uses: actions/checkout@v4
      - run: npm ci
      - run: npx wrangler d1 execute DB --env production --command "SELECT 1"
        env:
          CLOUDFLARE_API_TOKEN: ${{ secrets.CF_API_TOKEN }}
          CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CF_ACCOUNT_ID }}

Testing D1 Migrations with Wrangler

D1 migrations run via wrangler d1 migrations apply. Test that your migrations apply cleanly in CI before deploying:

// src/db/migrations.test.ts
import { describe, it, expect } from 'vitest';
import { env } from 'cloudflare:test';
import { readdirSync, readFileSync } from 'fs';
import { join } from 'path';

describe('D1 Migrations', () => {
  it('all migration files apply without error', async () => {
    const migrationsDir = join(process.cwd(), 'migrations');
    const files = readdirSync(migrationsDir)
      .filter((f) => f.endsWith('.sql'))
      .sort();

    for (const file of files) {
      const sql = readFileSync(join(migrationsDir, file), 'utf8');
      await expect(env.DB.exec(sql)).resolves.not.toThrow();
    }
  });

  it('expected tables exist after migrations', async () => {
    const migrationsDir = join(process.cwd(), 'migrations');
    const files = readdirSync(migrationsDir).filter((f) => f.endsWith('.sql')).sort();
    
    for (const file of files) {
      const sql = readFileSync(join(migrationsDir, file), 'utf8');
      await env.DB.exec(sql);
    }

    const { results } = await env.DB.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).all();

    const tableNames = results.map((r: any) => r.name);
    expect(tableNames).toContain('users');
    expect(tableNames).toContain('posts');
  });
});

End-to-End Testing with HelpMeTest

Miniflare and Vitest cover the data and handler layers of your D1-backed Worker. But once your Worker is deployed, you need to verify that real users can interact with it through a browser or API. HelpMeTest provides end-to-end test scenarios that run against your deployed Cloudflare Worker, testing complete user flows that exercise D1 through real HTTP requests.

This is particularly useful for D1 apps because edge database behavior — replication, consistency — is only observable in real deployment, not in Miniflare. An E2E test that creates data and immediately reads it back verifies that your consistency model works correctly in production.

Summary

Testing Cloudflare D1 Workers requires embracing the Workers runtime environment rather than fighting it. With Miniflare and @cloudflare/vitest-pool-workers:

  • Access D1 bindings directly in tests via env.DB — the same API your production code uses.
  • Use SELF.fetch() to test complete request/response cycles through your Worker handlers.
  • Test D1 data functions in isolation for faster, more focused feedback on database logic.
  • Verify migrations by executing SQL files in order and asserting on table structure.
  • Keep CI fast by running Miniflare tests without any Cloudflare credentials — save real D1 calls for post-deploy smoke tests.

The combination of Miniflare's faithful D1 simulation and Vitest's test runner gives you a local development loop that closely mirrors production behavior, catching most issues before they ever reach Cloudflare's infrastructure.

Read more