Test Schemas for Legacy Databases: A Recovery Playbook

You inherit an app. The migrations folder is incomplete, out of date, or both. The tests, when they exist, mock the database so heavily that they no longer prove much about how the application behaves against the real schema. You sit down to add an integration test and hit the practical problem: the schema may exist in production, and you may even be able to inspect it, but it is not available as a repeatable test dependency. There is no trusted schema artifact that CI can create, refresh, compare, and use to validate application behavior.

This is common in older systems: applications that predate framework migrations, databases managed by hand, release processes that changed over time, and migration histories that no longer represent reality. The standard advice is to run the real database in CI and load a production schema dump. That is the right destination. It is often not a usable first step. Being able to inspect production DDL is not the same as having a clean export, a repeatable way to refresh it, and enough test coverage to prove the setup works.

What follows is a recovery pattern for the cases where you do not have those things yet. It is temporary infrastructure: useful while you regain control, and replaceable once you have a better source of truth. The point is not to maintain a hand-written schema forever. The point is to use it to create enough real tests that you can converge on one trusted schema source and a codebase whose important behavior is covered.

Why the obvious advice doesn’t work

To dump production and replay it reliably, you need three things at the same time:

• a clean schema export
• the same database engine available in CI
• at least one integration test that can prove the pipeline is wired correctly

Healthy codebases often have all three. Legacy codebases often have none. You may be able to query production DDL, but that still leaves the work of turning it into a committed artifact or reproducible CI setup. The production engine may be awkward to run locally or difficult to license in CI. The integration tests may not exist, which means even a correctly built pipeline has no useful signal at first.

At that point, the practical question is not “what is the ideal setup?” It is “what is the smallest step that lets us write a real test?”

The pattern, in plain language

Create a single test-schema builder. Call it TestSchema, LegacySchema, or whatever fits your project. Inside it, put the DDL for the tables your tests actually touch. Stand up a cheap database for tests and create the schema at the start of the test run.

Add tables and columns as tests need them. Do not try to recreate the entire production schema in one pass. That turns a testing task into a stalled archaeology project. Capture the slice of schema needed for the next test, make the behavior pass, and move on.

The rule that keeps this from becoming another dead artifact is simple: every schema addition must be attached to an application behavior test. The test is the asset. The schema builder exists to make the test possible.

Start with three files:

• tests/Support/TestSchema.php: the temporary schema builder
• tests/Integration/IntegrationTestCase.php: the base class that installs the schema
• tests/Integration/CreateOrderTest.php: the first workflow test

For example, in Laravel, the first schema builder can be this small:

// tests/Support/TestSchema.php

final class TestSchema
{
    public static function migrate(): void
    {
        Schema::dropAllTables();

        self::orders();
    }

    private static function orders(): void
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('cust_id');
            $table->dateTime('placed_at');
        });
    }
}

Then wire it into your integration test base:

// tests/Integration/IntegrationTestCase.php

abstract class IntegrationTestCase extends TestCase
{
    protected function setUp(): void
    {
        parent::setUp();

        TestSchema::migrate();
    }
}

Now the first workflow test has somewhere real to run:

// tests/Integration/CreateOrderTest.php

final class CreateOrderTest extends IntegrationTestCase
{
    public function test_it_creates_an_order_for_a_customer(): void
    {
        $customer = Customer::factory()->create();

        $order = app(CreateOrder::class)->handle($customer->id);

        $this->assertDatabaseHas('orders', [
            'id' => $order->id,
            'cust_id' => $customer->id,
        ]);
    }
}

That test may fail three times while you discover missing columns. Good. Each failure tells you the next smallest schema change required to exercise the behavior.

Suppose your first integration test touches an orders table. You write a factory. The test fails:

SQLSTATE[42S22]: Unknown column 'customer_id' in 'field list'

You check production and find the column is cust_id, not customer_id. Update the fixture just enough for the behavior under test:

Schema::create('orders', function ($table) {
    $table->id();
    $table->unsignedBigInteger('cust_id');
    $table->dateTime('placed_at');
});

Then keep going until the test asserts the behavior you care about. The useful outcome is not that cust_id is now documented. The useful outcome is that order creation, retrieval, or whatever workflow you were testing now runs through real persistence.

A week later, another test fails because orders.status is not the string enum you assumed. It is a tinyint.

$table->tinyInteger('status')->default(0);

Now the fixture is doing more than making tests pass. It is creating a ratchet: every new workflow test forces another piece of the application to run against a real schema. If the DDL is wrong, a test fails. If the behavior changes, a test fails. That feedback is what lets you improve the codebase safely.

You may also want a small escape hatch, something like addMissingColumnsAndTables, that forward-extends the schema as new tests reveal new requirements. Do not rewrite the whole fixture every time you add coverage. Append what the new test needs, keep the change tied to that test, and let the file grow incrementally until you are ready to replace it with a better source.

Keep that escape hatch boring:

// tests/Support/TestSchema.php

final class TestSchema
{
    public static function addMissingColumnsAndTables(): void
    {
        if (! Schema::hasTable('orders')) {
            self::orders();
        }

        Schema::table('orders', function (Blueprint $table) {
            if (! Schema::hasColumn('orders', 'status')) {
                $table->tinyInteger('status')->default(0);
            }

            if (! Schema::hasColumn('orders', 'total_cents')) {
                $table->integer('total_cents')->default(0);
            }
        });
    }
}

The method is not a migration system. It is a pressure valve so adding the next integration test does not require solving the whole schema story first.

Be honest about what this is

This pattern has costs the polished alternative does not.

Your test database may not behave exactly like production. Operators differ. Constraints differ. Type coercion differs. Transaction behavior differs. Some real bugs will still pass the test suite and show up only against the real database. Hand-translated DDL can drift when production changes underneath it. The schema file can also get long; in an active codebase, a thousand lines is not unusual.

Those costs are real. They are still better than having no integration tests indefinitely. This pattern lets you start writing tests against a real, if simplified, database while you work toward a better setup. Each test puts one more slice of application behavior under executable control.

The goal is modest at first: not redesigning the database, not migrating the application, not solving schema management in one pass. You are capturing reality one table at a time until you know enough to take the next step.

Choose the right test database

The pattern is not tied to a specific database. The right test database depends on what you are trying to learn.

Early on, speed matters. A lightweight local database is often enough to prove that the application can run real persistence code in tests. It lets you build factories, exercise repositories, and replace broad database mocks with something concrete. That is valuable even when the test database is only an approximation of production.

Later, fidelity matters. Once the first layer of tests exists, add a second job that runs the same tests against a more production-like database. That might mean the same engine as production in a container. It might mean the candidate engine for a migration. It might mean a managed test instance if containers are not practical.

As fidelity increases, the setup usually gets heavier. You may stop recreating the database per test and instead build it once per CI run. You may wrap each test in a transaction and roll it back at the end. You may pre-bake the schema into a reusable image or snapshot so CI pays only the boot cost. You may refresh that image or snapshot on a schedule from a production schema export.

If you cannot get a clean production export, keep a checked-in schema.sql or schema-builder class and grow it iteratively. The operating principle stays the same regardless of engine: extend the schema only when a test needs it, and replace the temporary schema once a reliable source of truth exists.

Model the topology too

The schema is not only tables and columns. In older systems, the database topology is often part of the behavior.

A PostgreSQL app may use multiple schemas inside one database: public, billing, audit, reporting. A legacy PHP/MySQL app may connect to three or four separate databases and join the results in application code: main, inventory, billing, crm. If your test fixture flattens all of that into one namespace, the tests become easier to write but less useful. You lose the connection boundaries that the application actually depends on.

For PostgreSQL, keep the schema names visible in the fixture:

// tests/Support/TestSchema.php

final class TestSchema
{
    public static function migrate(): void
    {
        DB::statement('CREATE SCHEMA IF NOT EXISTS billing');
        DB::statement('CREATE SCHEMA IF NOT EXISTS audit');

        Schema::create('public.customers', function (Blueprint $table) {
            $table->id();
            $table->string('email');
        });

        Schema::create('billing.invoices', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('customer_id');
            $table->integer('total_cents');
            $table->string('status');
        });

        Schema::create('audit.invoice_events', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('invoice_id');
            $table->string('event_type');
            $table->timestamp('created_at');
        });
    }
}

Then write the workflow test against the real qualified tables:

public function test_closing_an_invoice_writes_an_audit_event(): void
{
    $customerId = DB::table('public.customers')->insertGetId([
        'email' => 'customer@example.com',
    ]);

    $invoiceId = DB::table('billing.invoices')->insertGetId([
        'customer_id' => $customerId,
        'total_cents' => 12900,
        'status' => 'open',
    ]);

    app(CloseInvoice::class)->handle($invoiceId);

    $this->assertDatabaseHas('billing.invoices', [
        'id' => $invoiceId,
        'status' => 'closed',
    ]);

    $this->assertDatabaseHas('audit.invoice_events', [
        'invoice_id' => $invoiceId,
        'event_type' => 'invoice.closed',
    ]);
}

For a legacy MySQL/PHP app that talks to multiple databases, mirror the connections instead of pretending there is only one:

mysql -e "CREATE DATABASE IF NOT EXISTS test_main"
mysql -e "CREATE DATABASE IF NOT EXISTS test_inventory"
mysql -e "CREATE DATABASE IF NOT EXISTS test_billing"

// config/database.php

'connections' => [
    'legacy_main' => [
        'driver' => 'mysql',
        'database' => env('DB_MAIN_DATABASE', 'test_main'),
    ],

    'legacy_inventory' => [
        'driver' => 'mysql',
        'database' => env('DB_INVENTORY_DATABASE', 'test_inventory'),
    ],

    'legacy_billing' => [
        'driver' => 'mysql',
        'database' => env('DB_BILLING_DATABASE', 'test_billing'),
    ],
],

The test schema should create tables on the same logical connections the application uses:

final class TestSchema
{
    public static function migrate(): void
    {
        Schema::connection('legacy_main')->create('orders', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('customer_id');
            $table->unsignedBigInteger('product_id');
        });

        Schema::connection('legacy_inventory')->create('products', function (Blueprint $table) {
            $table->id();
            $table->integer('available_qty');
        });

        Schema::connection('legacy_billing')->create('charges', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('order_id');
            $table->integer('amount_cents');
        });
    }
}

Now a test can prove that the workflow crosses the same boundaries as production:

public function test_checkout_reserves_inventory_and_creates_charge(): void
{
    DB::connection('legacy_inventory')->table('products')->insert([
        'id' => 10,
        'available_qty' => 3,
    ]);

    $orderId = app(Checkout::class)->handle(customerId: 5, productId: 10);

    $this->assertDatabaseHas('orders', [
        'id' => $orderId,
        'product_id' => 10,
    ], 'legacy_main');

    $this->assertDatabaseHas('products', [
        'id' => 10,
        'available_qty' => 2,
    ], 'legacy_inventory');

    $this->assertDatabaseHas('charges', [
        'order_id' => $orderId,
    ], 'legacy_billing');
}

This is where the temporary fixture earns its keep. You are not only adding columns until a test stops crashing. You are making the application prove that it can still coordinate the real persistence shape: multiple schemas, multiple connections, and the boundaries between them. Later, when you converge on a generated baseline or real migrations, those same tests protect the topology.

Outside PHP, the shape is the same

Nothing about this pattern depends on PHP. The implementation changes, but the structure does not:

• create a temporary schema builder
• install it in the integration test setup
• write workflow tests against real persistence
• extend the schema only when a test requires it
• promote stable schema into the real schema source later

In a Node/TypeScript service using pg, the temporary schema can be plain SQL executed before the suite:

// test/support/testSchema.ts

import { Pool } from "pg";

export async function installTestSchema(pool: Pool): Promise<void> {
  await pool.query(`
    drop schema if exists public cascade;
    create schema public;

    create table customers (
      id bigserial primary key,
      email text not null
    );

    create table orders (
      id bigserial primary key,
      customer_id bigint not null,
      placed_at timestamp not null
    );
  `);
}

Then call it from the test setup:

// test/setupIntegration.ts

import { pool } from "../src/db";
import { installTestSchema } from "./support/testSchema";

beforeEach(async () => {
  await installTestSchema(pool);
});

The workflow test should go through application code, not direct SQL:

// test/createOrder.test.ts

import { pool } from "../src/db";
import { createOrder } from "../src/orders/createOrder";

test("creates an order for a customer", async () => {
  const customer = await pool.query(
    "insert into customers (email) values ($1) returning id",
    ["customer@example.com"],
  );

  const order = await createOrder({ customerId: customer.rows[0].id });

  const stored = await pool.query(
    "select customer_id from orders where id = $1",
    [order.id],
  );

  expect(stored.rows[0].customer_id).toBe(customer.rows[0].id);
});

If that test fails because orders.status is missing, add status to testSchema.ts. If a second workflow needs order_items, add only enough of order_items for that workflow. The SQL file grows because tests demand it.

In Python with pytest and SQLAlchemy, the same pattern becomes a fixture:

# tests/support/test_schema.py

from sqlalchemy import text


def install_test_schema(engine):
    with engine.begin() as conn:
        conn.execute(text("drop table if exists orders"))
        conn.execute(text("drop table if exists customers"))

        conn.execute(text("""
            create table customers (
                id integer primary key,
                email varchar not null
            )
        """))

        conn.execute(text("""
            create table orders (
                id integer primary key,
                customer_id integer not null,
                placed_at timestamp not null
            )
        """))

Wire it into pytest:

# tests/conftest.py

import pytest
from app.db import engine
from tests.support.test_schema import install_test_schema


@pytest.fixture(autouse=True)
def test_schema():
    install_test_schema(engine)

Then write the workflow test against the service:

# tests/test_create_order.py

from app.orders import create_order
from app.db import session


def test_creates_order_for_customer():
    customer_id = session.execute(
        "insert into customers (email) values (:email) returning id",
        {"email": "customer@example.com"},
    ).scalar_one()

    order = create_order(customer_id)

    stored_customer_id = session.execute(
        "select customer_id from orders where id = :id",
        {"id": order.id},
    ).scalar_one()

    assert stored_customer_id == customer_id

In Go, you usually make the schema installer a helper that receives *sql.DB:

// internal/testdb/schema.go

package testdb

import (
	"database/sql"
	"testing"
)

func InstallSchema(t *testing.T, db *sql.DB) {
	t.Helper()

	statements := []string{
		`drop table if exists orders`,
		`drop table if exists customers`,
		`create table customers (
			id integer primary key,
			email text not null
		)`,
		`create table orders (
			id integer primary key,
			customer_id integer not null,
			placed_at timestamp not null
		)`,
	}

	for _, statement := range statements {
		if _, err := db.Exec(statement); err != nil {
			t.Fatalf("install test schema: %v", err)
		}
	}
}

And use it from a workflow test:

func TestCreateOrder(t *testing.T) {
	db := openTestDB(t)
	testdb.InstallSchema(t, db)

	customerID := insertCustomer(t, db, "customer@example.com")

	order, err := orders.Create(context.Background(), db, customerID)
	if err != nil {
		t.Fatal(err)
	}

	var storedCustomerID int64
	err = db.QueryRow(
		`select customer_id from orders where id = ?`,
		order.ID,
	).Scan(&storedCustomerID)
	if err != nil {
		t.Fatal(err)
	}

	if storedCustomerID != customerID {
		t.Fatalf("customer_id = %d, want %d", storedCustomerID, customerID)
	}
}

Different languages, same discipline. Do not start by designing a complete schema abstraction. Start with one workflow test, create the minimum schema it needs, and let the suite force the next schema addition. Once the same definitions are used across enough tests, promote them into migrations, a baseline SQL file, or a generated schema artifact.

Why this helps database switches

This pattern is especially useful when the long-term plan is to move the application from one database engine to another.

Most database migrations fail in the space between “the schema converted” and “the application still behaves the same.” Tools can translate DDL. They can move rows. They can even catch many type and constraint problems. They cannot tell you whether your application depended on a vendor-specific coercion rule, date behavior, string comparison, trigger, stored procedure, or constraint side effect.

The test schema gives you a smaller, controlled version of that problem. First, you make the existing behavior visible against a known schema. Then you run the same tests against the target engine or a closer approximation of production. The failures are no longer abstract migration risk. They become a list of concrete incompatibilities:

• a query depends on vendor-specific syntax
• a model assumes case-insensitive comparisons
• a date column loses precision in the target engine
• a boolean is really a tinyint
• a transaction behaves differently under the new isolation level
• a fixture passed only because the first test database did not enforce a constraint

That does not make the database switch easy. It makes it measurable. You can decide which failures are test-fixture limitations, which require application changes, and which are real blockers for the migration. More importantly, you can keep running the suite as you fix them, so confidence accumulates instead of resetting after every discovery.

How AI changes the pattern

AI does not change the goal. It changes the speed at which you can move through the loop.

The loop is mechanical:

• choose one workflow
• write or strengthen an integration test for it
• extend the temporary schema only as far as that test requires
• run the test against the current test database
• run the same test against a more production-like or target database when possible
• move stable schema definitions into the real schema source
• delete temporary schema once the real source can support the test

That loop is exactly the kind of repeatable work an AI assistant can help with, but only if you give it the right operating instructions. This is where skills and docs are useful. Not as a second database schema. As a workflow guide for moving the codebase toward the end state.

A useful repo skill for this pattern would tell the assistant how to converge:

• where the temporary test schema lives
• how to add the smallest schema slice needed for a new test
• which test command proves the workflow locally
• which CI job runs against the production-like database
• when a fixture change should be promoted into the real schema source
• when temporary schema can be deleted
• what coverage threshold or workflow list defines progress

For example:

# Legacy Persistence Coverage Skill

Goal: move one workflow at a time from mocked persistence to tested persistence.

Loop:
1. Pick the smallest untested workflow from `docs/persistence-workflows.md`.
2. Add or strengthen one integration test for that workflow.
3. Extend `tests/Support/TestSchema.php` only for columns/tables required by that test.
4. Run `php artisan test --testsuite=Integration`.
5. If the workflow passes locally, run the production-like DB job:
   `php artisan test --testsuite=Integration --env=ci-db`.
6. If three or more tests depend on the same temporary table, propose moving it into
   `database/schema/baseline.sql` or a real migration.
7. Delete temporary schema once the baseline/migration supports the same tests.

Rules:
- Do not add schema without a failing or new test.
- Do not broaden the fixture for future guesses.
- Prefer improving a workflow test over adding unit mocks.
- Treat the temporary schema as debt to retire.

The skill should be biased toward producing executable artifacts. If the assistant discovers that a workflow cannot be tested without adding a table, it should add the table to the temporary schema and add the test. If the same table is now stable across several tests, it should propose moving that definition into the baseline schema or migration source. If a workflow is already covered by a higher-fidelity database job, it should avoid expanding the low-fidelity fixture unnecessarily.

Used this way, AI becomes a coverage ratchet. It can help identify the next untested workflow, add the missing integration test, extend the temporary schema, run the relevant commands, and shrink the temporary layer once the real source of truth catches up. The docs and skills are not the product. The product is a codebase with fewer mocks, clearer persistence boundaries, one trusted schema source, and tests that prove the important behavior.

Where this leads

Once you have meaningful coverage on top of the fixture, you can do work that was previously too risky. You can compare the captured schema against a fresh production dump and see the drift directly. You can replace the hand-written DDL with a real dump when one becomes available, and the existing tests will tell you which assumptions were tied to the simplified version. You can introduce a real migration system, generate a baseline from the captured DDL, and make future schema changes reviewable again.

The destination is not a perfect hand-written test schema. The destination is making the hand-written schema unnecessary. Eventually, the schema should come from one trusted source: migrations, a generated baseline, a production-derived schema dump, or whatever source your team can actually keep current. The tests should cover the application behavior that depends on that schema, so schema changes are validated by the same suite that protects the product.

None of that is reachable from zero. The scaffolding is what gets you off zero, and the tests are what tell you when the scaffolding can come down.

Next steps

Do not start by trying to model the whole database. Start with one workflow that matters and turn it into an integration test.

Use this sequence:

1. Pick a workflow: creating an order, importing a customer, closing an invoice, syncing a user from the legacy system.
2. Write the test first, even if it fails on the first missing table.
3. Add the smallest table definition to TestSchema.
4. Run the test again.
5. Add the next missing column only when the test asks for it.
6. Stop when the workflow is covered.
7. Add the workflow to the list of covered persistence paths.

The first pass usually looks like this:

// tests/Integration/CloseInvoiceTest.php

public function test_it_closes_an_invoice(): void
{
    $invoice = Invoice::factory()->open()->create();

    app(CloseInvoice::class)->handle($invoice->id);

    $this->assertDatabaseHas('invoices', [
        'id' => $invoice->id,
        'status' => InvoiceStatus::Closed->value,
    ]);
}

If that fails because invoices does not exist, add only invoices. If it then fails because closed_at is missing, add only closed_at. The test is driving the schema, not the other way around.

Once the first test is running, keep four rules:

• every schema addition is tied to a test
• every temporary fixture shortcut is either promoted or deleted later
• every workflow added to the suite moves you closer to replacing the hand-written schema
• every repeated table definition is a candidate for the real baseline

After a handful of tests, add a drift check. Compare the captured test schema against a production schema export, even if the comparison is manual at first. The goal is not perfect automation on day one. The goal is to learn where your fixture is knowingly simplified and where it is accidentally wrong.

That check can start as a script with an intentionally blunt contract:

php artisan schema:dump --database=legacy --path=storage/schema/prod.sql
php artisan schema:dump --database=testing --path=storage/schema/test.sql
diff -u storage/schema/prod.sql storage/schema/test.sql

It will be noisy. That is fine. The first useful version only needs to tell you which temporary definitions are stable enough to promote, and which ones are still approximations.

Then run the same tests against the next database engine. If you started with a lightweight test database, add a job that runs against the production engine or the target migration engine. Let it fail at first. Keep the failing job non-blocking until the failures are classified. The classification is the work:

• fixture gap
• application bug
• vendor-specific SQL
• data type mismatch
• migration blocker

That list becomes the migration plan. Not a speculative spreadsheet, but a set of failing tests tied to real application behavior. As the failures disappear, the team is not just hoping the database switch will work. It has evidence.

Do the same for AI skills. When a workflow becomes well covered, update the skill so the assistant uses the source file, migration, factory, test command, or CI job that now owns the work. Good skills should get shorter as the codebase gets healthier. They should route the assistant through the convergence loop, not preserve a parallel version of the system.

What you actually walk away with

The captured schema is often the smallest and least polished part of the work. It is also the part you should be trying to make obsolete.

Its value is that it lets you start testing before the real schema pipeline is ready. Once the tests exist, they give you leverage to improve the pipeline itself: generate a baseline, introduce migrations, compare against production, run against the target database, and remove duplicated schema setup.

The fixture also changes how new work happens. Every new integration test requires you to state the part of the schema it depends on. Over time, the database stops being an unknown external dependency and becomes something the team can inspect, discuss, and improve. If a database switch is on the horizon, that understanding becomes the baseline for the migration.

The AI version gives you a repeatable way to keep pushing in that direction. A skill can instruct the assistant to add the next workflow test, extend the temporary schema only when necessary, run the relevant jobs, and promote stable pieces into the real schema source. It should not stay large forever. The end state is a codebase where the schema comes from one trusted place and the important workflows are covered by tests.

Start with one workflow. Write the test you need now. Add the next column only when the next test requires it. Promote stable schema into the real source as soon as you can. The first test is usually the hardest; by the tenth, the team has the beginning of a tested path out of the old structure.