There are four main ways to generate test data for a database: writing it by hand, generating it from rules, deriving it from production data through masking and subsetting, and generating it synthetically from your schema with AI. The right method depends on whether you have usable production data, how much volume and realism your tests need, and the privacy constraints on the source data. Most teams combine approaches — masking and subsetting a production database for realism, then generating synthetic records to fill gaps or scale volume.
The four ways to generate test data for a database
Every method for filling a test database is an answer to the same question: how do you get realistic, safe data into a schema without spending days on it? Four approaches dominate test data generation, and they differ mainly in where the data comes from and how much of the work you do by hand.
- Hand-writing — you write the
INSERTstatements or fixtures yourself, row by row. - Rules-based generation — a generator produces rows from field-level rules and sampled distributions you define.
- Masking and subsetting production data — you take a real database, replace the sensitive values, and carve out a smaller, coherent slice.
- AI-native synthesis — you describe or point at a schema, and a model generates data that fits it.
One constraint separates a database from a spreadsheet, and every method has to satisfy it: referential integrity, the guarantee that the relationships between tables stay valid — every foreign key still points at a row that exists, so an order never references a customer who isn't there. Break it and the application errors out before a test even runs.
Three questions decide which approach fits. Do you have usable production data to start from? How much volume and realism do the tests actually need? And what privacy constraints govern the source data? Your answers, more than any single tool, point to the method — and, as the different types of test data show, most teams end up combining more than one.
| Method | Realism | Volume ceiling | Privacy-safety | Setup effort | Best for |
|---|---|---|---|---|---|
| Hand-writing | Low | Very low | High — no real data | Low per row, high at scale | A handful of rows; early schema work |
| Rules-based generation | Low–moderate | High | High — no real data | Moderate; you own every relationship | Greenfield and simple, non-relational shapes |
| Masking + subsetting production (Tonic Structural) | High — production, transformed | Bounded by production | High when done correctly | Moderate; automated by the tool | You have production data and need realism |
| AI-native synthesis (Tonic Fabricate) | High | Very high | High — no production dependency | Low with an agent: describe, then generate | No usable production data, or scaling beyond it |
Hand-writing and rules-based generation
The two simplest methods start from nothing but your schema, and both have a real place before a database gets large or relational.
Hand-writing test data means composing the rows yourself — literal INSERT statements, YAML fixtures, or seed scripts. For a handful of records it's the fastest thing there is, and it's ideal for a specific case: a bug reproduction, a fixture for a unit test, the three users a new feature needs. But hand-written data doesn't scale and it drifts. Every schema change means editing fixtures by hand, and twenty carefully crafted rows tell you nothing about how a query behaves over a million.
Rules-based generation automates the volume. A test data generator — an open-source faker-style library, or a rules engine — produces rows from field-level rules and sampled distributions you specify: this column is a valid email, that one is a price between 10 and 500, this date falls in the last year. For greenfield work and simple table shapes it's productive and completely privacy-safe, since no real data is involved.
Where it strains is the relational part. The generator fills columns; it doesn't understand your schema, so you own every cross-table relationship by hand:
- Foreign keys that have to resolve to rows that actually exist
- Distributions that match reality, not just uniform random noise
- Consistency across tables, so a customer's orders belong to that customer
On a small, flat schema that's manageable. On a real relational database with dozens of interdependent tables, keeping it all correct becomes its own engineering project — which is the tradeoff at the center of rules-based vs. AI-generated approaches.
Deriving test data from production: masking and subsetting
When you already have a production database, the most direct way to get realistic test data is to derive it from what's there — nothing you generate from scratch matches production as faithfully as production itself, transformed to make it safe.
Two techniques do that work together. Data masking replaces sensitive values — names, emails, account numbers — with realistic substitutes while preserving each field's type and format, so a masked email is still a valid email and a masked date is still a plausible date. Database subsetting pulls a coherent, referentially intact slice of the database instead of copying the whole thing: a few gigabytes that still hang together, rather than a multi-terabyte clone no laptop can hold. Done together, they turn a sensitive production database into a smaller, privacy-safe copy that behaves like the real thing.
This is the method behind Tonic Structural. You connect Structural to the source database, configure how each sensitive field is masked or de-identified, and subset to the slice a given environment needs; the output is a production-realistic copy with no exposed sensitive data. Its patented subsetter is the part that makes subsetting safe at scale — it follows foreign keys through the schema to shrink petabytes down to gigabytes without breaking referential integrity, so the slice you get is complete rather than a pile of orphaned rows. Configuration is roughly 80% of the work in a test data project, and the Structural Agent compresses that from hours to minutes by letting you set sensitivity, generators, and subsetting rules in natural language. Patterson reported a 75% reduction in test data provisioning time after automating their pipeline this way.
The Tonic Advantage: a safe copy of production, provisioned fast. Masking and subsetting only help if the result stays usable, and the thing that breaks first is referential integrity. Tonic Structural's subsetter walks the foreign-key graph so the slice it produces is complete and consistent, and the Structural Agent turns configuration that used to take hours into a short conversation. The result is a production-realistic, privacy-safe database a developer can pull on demand instead of filing a ticket and waiting on it.
Generating synthetic data from your schema
When there's no usable production data to derive from — a greenfield service with nothing in it, a source too sensitive to touch even after masking, or a scenario production doesn't contain — you generate synthetic test data from the schema instead.
Tonic Fabricate takes this approach, and it generates in three ways depending on your starting point. From scratch, it builds a dataset to match a schema you define, with no underlying data required. By seeding from an existing database, it models the real schema, patterns, and distributions and produces new records that behave like them without copying any rows. Or from a plain-language prompt, where you describe the data you want and Fabricate plans and generates it. Across all three, it maintains referential integrity across complex, multi-database schemas — the same foreign-key correctness that hand-writing and rules-based generation force you to manage by hand.
The difference from the older path is who owns the relationships. Scripting and rules make you encode every constraint yourself; agentic generation inverts that — you describe the data you want, and the agent plans the schema-aware generation and fills it in, edge cases and cross-table relationships included. Because it's driven by a specification rather than a copy of production, it carries no privacy dependency — there's no sensitive source to mask. And it runs where developers already work: the Fabricate MCP server exposes generation inside the AI coding clients on the desktop, so producing test data doesn't mean leaving the editor.
Fabricate is a test data solution in its own right, not a test data management platform; it's the method to reach for when deriving from production isn't an option, and the natural home for the synthetic test data question of when to generate rather than mask.
The Tonic Advantage: describe the data, don't script it. The hard part of generating relational test data has always been the relationships — keeping foreign keys valid and distributions realistic across many tables. Tonic Fabricate handles that as part of generation: you describe the dataset you need, from scratch or modeled on an existing schema, and it produces a referentially intact result with no production data required. For a developer, that turns test data from a scripting task into a request they can make from the tools they already use.
Scaling volume and keeping data referentially intact
The two production-grade methods aren't in competition; they compose. The most demanding problem is volume — load and performance testing need far more records than production can safely provide — and the strongest answer combines derivation and synthesis, in order.
Start with Tonic Structural: mask and subset production into a safe, referentially intact baseline that reflects the real schema and distributions. Then bring in Tonic Fabricate to scale that baseline up, generating additional synthetic records modeled on the de-identified data until you reach the volume a load test demands. Structural does the de-identification and subsetting first; Fabricate scales what Structural produced. Fabricate isn't a standalone provisioning step in this workflow — it works from the baseline Structural establishes, which is what lets you scale test data volume synthetically without reintroducing sensitive data or copying more of production. It's a common shape for testing and QA environments that need production-scale load.
Underneath all four methods runs a single requirement: referential integrity. Hand-written rows, generated rows, a masked subset, a synthetically scaled dataset — each is only as useful as its ability to keep primary- and foreign-key relationships intact across tables. It is also the first thing naive masking and subsetting break: mask a customer ID in one table but not the orders that reference it, or subset orders without their customers, and the copy fails the moment a join runs. Whichever methods you combine, integrity across tables is the property to protect.
How to choose the right approach
Knowing how to create test data comes down to matching the method to your starting point, then adjusting for a few practical constraints.
- You have usable production data → mask and subset it. It's the fastest route to production realism, and it keeps sensitive data out of lower environments.
- You need more volume than production can safely provide → mask and subset for a realistic baseline, then scale it up with synthesis.
- You have no usable or accessible production data — greenfield, or a locked-down source you can't touch → generate synthetically from the schema.
- You need a few specific rows → hand-write them, or use rules-based generation for simple, non-relational shapes.
Three modifiers shift the choice. Privacy and compliance constraints raise the bar on anything derived from production — the stricter the regime, the more synthesis's lack of a sensitive source counts in its favor. Refresh cadence matters too: data that regenerates on every CI/CD run needs a method you can automate and run unattended, not one that leans on hand-edited fixtures. And realism should be sized to the test — a smoke test tolerates rough data, while performance work and realistic test data for integration testing demand production-grade fidelity and volume.
Most teams don't pick once. They mask and subset what they have, generate what they lack, and revisit the mix as the schema and the tests evolve — the through-line running across the broader test data management tools landscape.