Test Data Management for MySQL: Export, Seed, and Refresh

MySQL powers a significant share of the world's web applications, and MySQL test data management has many of the same problems as PostgreSQL: fixtures that drift out of sync with the schema, seed scripts that accumulate without strategy, and CI environments that start from an undefined database state.

Seedmancer supports MySQL with the same workflow as PostgreSQL. The same export, seed, generate-local, and refresh commands work against a mysql:// URL. This guide covers the MySQL-specific setup and the practical patterns for managing test data across local development, staging, and CI.

---

Connecting Seedmancer to MySQL

Initialize Seedmancer in your project:

cd your-project
seedmancer init

During init, provide a mysql:// connection URL. The resulting seedmancer.yaml looks like this:

envs:
  local:
    url: mysql://user:pass@localhost:3306/myapp
  staging:
    url: mysql://user:pass@staging-host:3306/myapp_staging

Multiple environments are optional. You can start with just local and add others when needed.

Verify the connection and export a baseline snapshot:

seedmancer export myapp/baseline

This captures the current schema fingerprint and a CSV snapshot of every table. The result lives in .seedmancer/scenarios/myapp/baseline/revisions/r001/ — a folder of CSVs and a schema.json fingerprint that is safe to commit.

---

Seeding a MySQL database to a known state

Restoring a scenario into MySQL uses the same command as PostgreSQL:

seedmancer seed myapp/baseline --yes

--yes skips the interactive confirmation prompt, which is required for non-interactive environments like CI scripts. Without it, the command waits for keyboard input and the pipeline hangs.

For staging or other named environments:

seedmancer seed myapp/baseline --env staging --yes

---

MySQL-specific considerations

AUTO_INCREMENT reset

Seedmancer restores scenarios by truncating targeted tables and reloading CSVs. In MySQL, TRUNCATE TABLE resets AUTO_INCREMENT counters to 1 by default, which means restored rows get their original IDs from the CSV. If your tests depend on specific primary key values, this behavior is predictable and consistent.

If a test creates new rows after seeding, the next auto-assigned ID will be one higher than the highest ID in the CSV. This is the expected behavior and matches how most MySQL applications work.

Foreign key constraint handling

MySQL enforces foreign key constraints by default. Seedmancer handles table restoration order automatically based on the stored schema fingerprint, so tables with foreign keys to other tables are always restored after the tables they reference.

If your database uses SET FOREIGN_KEY_CHECKS = 0 in existing seed scripts to work around ordering issues, you can remove that workaround when using Seedmancer — the ordering is handled for you.

Character sets and collations

Seedmancer stores data as UTF-8 CSVs. If your MySQL database uses a non-UTF-8 character set, you may need to ensure the connection URL specifies the correct charset:

envs:
  local:
    url: mysql://user:pass@localhost:3306/myapp?charset=utf8mb4

utf8mb4 is the recommended character set for modern MySQL databases — it is what MySQL calls full Unicode support (the utf8 alias in MySQL only covers the Basic Multilingual Plane).

---

Creating scenarios for different test states

Once a baseline exists, create named scenarios for different application states your tests need:

# Export the current state of your local database as a new scenario
seedmancer export billing/pro

# Or generate a scenario from a prompt using the MCP server in Cursor/Claude
# The agent inspects your schema and generates idempotent SQL

For CI-heavy workflows, the most practical setup is:

1. A myapp/baseline scenario with reference data all tests share.
2. Feature-specific scenarios for test suites that need specific application states.
3. A seed step at the top of each CI job:

- name: Seed database
  run: seedmancer seed myapp/baseline --yes
  env:
    SEEDMANCER_DATABASE_URL: ${{ secrets.TEST_DATABASE_URL }}

---

Integrating with popular PHP and Node.js test frameworks

MySQL is common in PHP (Laravel, Symfony) and Node.js (Sequelize, TypeORM, Prisma with MySQL adapter) projects. The seed command integrates naturally with both.

Laravel (PHPUnit)

In tests/TestCase.php or a base test class, add a setUpBeforeClass call:

public static function setUpBeforeClass(): void
{
    exec("seedmancer seed myapp/baseline --yes");
}

Or add it to a PHPUnit bootstrap file:

// tests/bootstrap.php
exec("seedmancer seed myapp/baseline --yes");

Reference it in phpunit.xml:

<phpunit bootstrap="tests/bootstrap.php">

Node.js (Jest or Vitest)

In jest.config.js:

module.exports = {
  globalSetup: "./jest-global-setup.js"
}

In jest-global-setup.js:

const { execSync } = require("child_process")

module.exports = async function () {
  execSync("seedmancer seed myapp/baseline --yes", { stdio: "inherit" })
}

For Vitest, use globalSetup in vitest.config.ts:

import { defineConfig } from "vitest/config"

export default defineConfig({
  test: {
    globalSetup: "./vitest-global-setup.ts"
  }
})

// vitest-global-setup.ts
import { execSync } from "child_process"

export function setup() {
  execSync("seedmancer seed myapp/baseline --yes", { stdio: "inherit" })
}

---

Keeping scenarios current as your schema evolves

When you add a column, rename a table, or change a constraint, the stored scenario becomes incompatible with the new schema. Check before seeding:

seedmancer check myapp/baseline

If a mismatch is detected, refresh the scenario:

seedmancer refresh myapp/baseline

The refresh command (Pro plan) uses the Seedmancer cloud AI to adapt the data. From Cursor or Claude, an MCP agent can do the same thing locally: it reads the saved generation SQL with get_dataset_sql, rewrites it for the new schema, and calls generate_dataset_local. Either way a new revision is created; the previous revisions are preserved.

Commit the updated .seedmancer/scenarios/ folder alongside the migration so CI and other developers always have a compatible scenario ready.

---

Summary

MySQL and PostgreSQL use the same Seedmancer workflow. The differences are in the connection URL scheme (mysql:// vs postgres://) and a few MySQL-specific behaviors around AUTO_INCREMENT and character sets — both of which Seedmancer handles automatically.

The pattern is the same in both cases:

1. seedmancer export captures your current state.
2. seedmancer seed <scenario> --yes restores it in under a second.
3. seedmancer check detects schema drift before it breaks your build.
4. seedmancer refresh updates scenarios when the schema changes.

See the CLI documentation for the full flag reference, environment variable setup, and CI configuration examples.