Use synthetic data to test AI generated database changes

An AI assistant writes a migration and tests it with three tidy records. Every record has all required fields, every email is unique, and every timestamp uses the same zone. The tests pass. Real data would include missing fields, conflicting identities, deleted parents, and records changed while the migration runs. Copying production data into the prompt is not the answer; representative synthetic fixtures can expose those failure modes without carrying customer information.

This walkthrough assumes a Node application and MongoDB. It focuses on testing generated data changes, including migrations, indexes, query refactors, and lifecycle operations. Synthetic data is useful when it models the invariants and distributions that matter. Random volume alone is not representativeness.

Start from the invariant

For a slug migration, write that existing valid slugs remain unchanged and new slugs are unique. For email normalization, write that collisions are reported without merging accounts. For deletion, write that no accessible child remains after the chosen lifecycle completes. The fixture should challenge these statements directly.

List assumptions made by the generated code. It may assume every author exists, every date parses, every key is present, or every write succeeds. Turn each assumption into a fixture category. A missing relation can be more informative than ten thousand normal records.

Ask the assistant to identify those assumptions from the exact code, with file and field evidence. Do not accept a generic list of edge cases unrelated to the transformation. The goal is a small dataset that makes a wrong implementation observably wrong.

Build deterministic relationships

Create fixed identifiers, stable timestamps, and invented labels. Use two organizations with different members and documents. Add one missing parent, one soft-deleted parent, and one record with private extras. Keep relationships readable so a reviewer can understand why a row belongs in the expected result.

Use a seeded generator only when larger distributions matter, such as index selectivity or page boundaries. Save the seed and generation rules. If a test fails only with an unrecorded random dataset, diagnosis becomes harder and the evidence less reusable.

Avoid real-looking personal data copied from logs. Invented addresses on reserved example domains and clearly synthetic content usually suffice. Do not assume replacing names anonymizes a production record; rare combinations of dates and relationships can still identify people.

Include legacy and malformed shapes

Model absent fields, null values, empty strings, old field names, and unexpected types according to the storage system's realistic history. Test them separately because they can have different query and index behavior. An absent key is not always equivalent to a key whose value is null.

For uniqueness changes, create explicit collisions under the proposed normalization policy. Preserve different related records so an accidental merge loses something the test can detect. For date grouping, place instants around midnight and daylight transitions. For pagination, create more than one page of equal timestamps.

Do not make every malformed record eligible for repair. Some should be reported and skipped because the transformation lacks enough information. The expected outcome should include unresolved cases rather than forcing the generated script to invent values.

Test concurrency as a schedule

A synthetic dataset alone cannot expose a race if operations run sequentially. Use barriers or controlled hooks to pause after reads and before writes. For duplicate creation, let both requests observe absence before continuing. For migration, change a record revision after preview and before write.

Assert the invariant after both operations finish. One success response is not sufficient if a denied request still wrote data. Check counts, stored values, and operation states. Use an isolated real database when the claim concerns unique indexes, transactions, or atomic conditional updates.

Provider doubles can simulate external timeouts and ambiguous acceptance, but they do not prove a real vendor's contract. Keep those test scopes separate. Third-party recovery describes the uncertainty windows worth modeling.

Exercise failures between steps

For a multi-write change, inject failure after each meaningful operation. Verify rollback when a transaction is intended and recoverable partial state when a staged workflow is intended. Test a restart or rerun rather than assuming the script will finish uninterrupted.

For a migration, run dry mode, inspect proposals, run write mode, and run it again. Add a concurrent revision change and a unique-key rejection. The script should distinguish skipped preconditions, data conflicts, and operational failures in its report.

For connection caching, reject the first initialization attempt and allow the next one. Assert concurrent callers share the attempt and a later caller can recover. Pair that deterministic unit test with an isolated driver-level check if network recovery is part of the claim.

Compare before and after contracts

Save expected public response shapes and storage invariants. A query optimization should preserve visibility and ordering. A privacy repair should omit private fields even when the source fixture contains them. A normalization change should preserve account ownership and related records.

Use exact assertions for important identifiers and fields, not only snapshots of large objects. A snapshot can become accepted without understanding a leaked field. A targeted assertion that private marker text is absent communicates the policy directly.

Check the old implementation fails at least one meaningful case and the repaired implementation passes it. If the test passes both, it may still be useful coverage, but it is not evidence that the specific defect was fixed. Explain that distinction in the review.

Review AI-generated tests for shortcuts

An assistant may mock the repository method that contains the bug, assert only that a function was called, or catch every error and keep the test green. Read the test's execution path and identify which boundary it exercises.

Ask it to explain why each fixture exists and what incorrect implementation the assertion rejects. Remove redundant normal records that add volume without a new behavior. Add missing categories based on the code's assumptions rather than increasing randomness.

Keep generated fixtures separate from application seed data that might run during deployment. A test cleanup should target a dedicated database or namespace and verify that target before deleting records. Synthetic data does not make a broad cleanup command harmless if it points at the wrong environment.

Match data scale to the claim

Small deterministic fixtures are strong for correctness. Performance and index claims need representative cardinality and distribution, plus measured plans or timings. State the synthetic workload and avoid extrapolating it into an invented production benchmark.

Checked migrations covers preview and recovery, while plan-driven indexes covers representative query work. The value of synthetic testing is that a reviewer can understand the failure, reproduce it, and inspect the repair without handling private production records.