@siu-issiki/vitest-drizzle-mysql

Vitest environment for Drizzle ORM with MySQL — automatic transaction rollback per test case.

MySQL version of @siu-issiki/vitest-drizzle-pg.

Features

• Automatic transaction rollback per test case
• Zero cleanup code needed
• Works with Drizzle ORM + mysql2
• Type-safe with TypeScript
• Fast — rollback is cheaper than truncate

Install

npm install -D @siu-issiki/vitest-drizzle-mysql

Setup

1. Create a setup file

// test/setup.ts
import { setupDrizzleEnvironment } from "@siu-issiki/vitest-drizzle-mysql";
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test_db",
});

const db = drizzle(connection);

setupDrizzleEnvironment({
  client: () => db,
  disconnect: () => connection.end(),
});

2. Configure vitest

// vitest.config.ts
import { defineConfig } from "vitest/config";

export default defineConfig({
  test: {
    globals: true,
    setupFiles: ["./test/setup.ts"],
  },
});

3. Write tests

import { users } from "./schema";

test("creates a user", async () => {
  await vDrizzle.client.insert(users).values({ name: "test" });
  const result = await vDrizzle.client.select().from(users);
  expect(result).toHaveLength(1);
}); // Automatically rolls back — no cleanup needed

test("database is clean", async () => {
  const result = await vDrizzle.client.select().from(users);
  expect(result).toHaveLength(0); // Previous test's data is gone
});

API

setupDrizzleEnvironment(options)

| Option | Type | Required | Description | |--------|------|----------|-------------| | client | () => TDatabase \| Promise<TDatabase> | Yes | Function that returns the Drizzle instance | | setup | (tx) => void \| Promise<void> | No | Runs before each test (inside transaction) | | teardown | (tx) => void \| Promise<void> | No | Runs after each test (before rollback) | | disconnect | () => void \| Promise<void> | No | Cleanup function when test suite ends |

vDrizzle.client

Global variable available in all test files. Returns the current transaction client. Use it instead of your normal db instance.

By default, vDrizzle.client is typed as any. For stricter typing, add a declaration file to your test project:

// vitest-drizzle.d.ts
import type { MySql2Database } from "drizzle-orm/mysql2";
import type { VitestDrizzleContext } from "@siu-issiki/vitest-drizzle-mysql";

declare global {
  var vDrizzle: VitestDrizzleContext<
    Parameters<Parameters<MySql2Database["transaction"]>[0]>[0]
  >;
}

How it works

Uses a "Promise pending" pattern (inspired by jest-prisma):

1. db.transaction() starts before each test
2. A Promise is created inside the transaction callback, and the reject function is held
3. The transaction client is exposed as vDrizzle.client
4. When the test ends, reject() is called, triggering a rollback
5. The error is caught silently

This approach works because MySQL's InnoDB engine supports transactions with rollback.

Limitations

Parallel test execution

This library uses a single transaction slot per setup, so it does not support test.concurrent or parallel test files sharing the same setup. Configure Vitest to run tests sequentially:

// vitest.config.ts
export default defineConfig({
  test: {
    pool: "forks",
    poolOptions: {
      forks: { singleFork: true },
    },
  },
});

MySQL-specific caveats

• DDL statements (CREATE TABLE, ALTER TABLE, etc.) cause an implicit commit in MySQL and cannot be rolled back. Run DDL in beforeAll outside the transaction.
• AUTO_INCREMENT counters are not rolled back. Inserted rows are undone, but the counter continues incrementing.
• TRUNCATE TABLE also causes an implicit commit. Use DELETE FROM if you need to clear data inside a transaction.

Development

# Start MySQL
docker compose up -d

# Install dependencies
npm install

# Build
npm run build

# Run tests
cd test/basic && npm install && npm test

License

MIT