Database Test Data Comparison: SQL Inserts, Factories, or Tools?
Database Test Data Comparison: SQL Inserts, Factories, or Tools?
When a test suite starts to flake, the first suspect is usually the code under test. In practice, the root cause is often the data that feeds the test. A test that passes on a developer’s laptop but fails in CI because a foreign‑key constraint is violated, or a performance test that runs 10× slower because the seed script inserts millions of rows one‑by‑one—these are everyday symptoms of a test‑data strategy that never got a deliberate design review.
Choosing how to create database test data is a buying decision, even when the “product” is an open‑source library or a home‑grown script. The three most common categories are:
| Category | Typical representatives | What you actually write/maintain |
|---|---|---|
| Raw SQL inserts | Hand‑crafted INSERT … VALUES scripts, COPY/BULK INSERT files | Every column, every relationship, every edge case |
| Factory libraries | Factory Bot (Ruby), Factory Boy (Python), Django‑factory‑boy, Go‑factory, .NET AutoFixture | Declarative model definitions, traits, sequences, callbacks |
| Dedicated test‑data tools | Commercial platforms (e.g., Redgate SQL Data Generator, Tonic), open‑source generators (e.g., dbforge, QA3’s free generator at /tools/test-data-generator) | Schema‑aware UI or DSL, referential‑integrity enforcement, data‑masking, versioned data sets |
Below is a buyer‑focused guide that walks through the decision criteria, a worked example, common pitfalls, and a concrete next step you can take today.
1. Problem‑aware hook: why the choice matters
| Symptom | Likely cause | Cost if ignored |
|---|---|---|
| Tests pass locally, fail in CI | Seed script assumes a specific database state (e.g., auto‑increment values) | Lost confidence, wasted debugging cycles |
| Test suite runtime > 30 min | One‑by‑one inserts for large reference tables | Slow feedback, reduced deployment frequency |
| Flaky tests after schema change | Hard‑coded column lists in SQL scripts | Regression risk, manual script updates |
| Production‑like data needed for performance or security testing | No tooling to generate realistic distributions | Inadequate load testing, compliance gaps |
| Team spends > 20 % of sprint on test‑data maintenance | No shared abstraction, each test writes its own data | Opportunity cost, burnout |
If any of those rows look familiar, the test‑data strategy is a bottleneck worth fixing.
2. Decision criteria – what to evaluate before you commit
| Criterion | Why it matters | SQL inserts | Factories | Dedicated tools |
|---|---|---|---|---|
| Schema complexity (FK depth, triggers, computed columns) | Determines how much manual wiring you need | High effort – you must honor every constraint | Medium – factories can express relationships, but you still write callbacks for triggers | Low – tool reads schema and builds a valid graph automatically |
| Data volume (rows per table, total size) | Affects insert speed and memory usage | COPY/BULK INSERT can be fast, but you must craft files | Factories usually emit one INSERT per object → slower at scale | Bulk generation, parallel workers, streaming output |
| Determinism vs. realism | Unit tests need repeatable data; perf tests need realistic distributions | Fully deterministic if you write exact values | Traits/sequences give deterministic IDs; random traits add realism | Configurable distributions (Gaussian, Zipf, custom) + seedable RNG |
| CI/CD integration | Must run in ephemeral containers, no manual steps | Simple – just run a script | Requires language runtime (Ruby, Python, etc.) in pipeline | Often provides Docker image or CLI; can output SQL files for any runner |
| Team skill set | Learning curve vs. |
productivity | Low barrier (SQL is universal) | Requires familiarity with the factory DSL | UI/DSL may be new, but many tools ship with tutorials | | Maintenance overhead | Schema changes, new tables, new test scenarios | Every change = edit scripts | Centralized model definitions reduce duplication | Schema‑aware regeneration; versioned data sets | | Compliance / PII masking | Production‑like data must not leak real identifiers | Manual anonymisation → error‑prone | Can embed masking logic in factories | Built‑in masking, synthetic data generation, policy enforcement | | Extensibility (custom generators, plugins) | Domain‑specific values (e.g., valid IBAN, HL7 messages) | Write your own functions in SQL | Add custom callbacks/sequences | Plugin architecture or scriptable APIs |.
Takeaway: No single column wins across the board. The right choice is the one that satisfies the high‑priority criteria for your context.
3. Evaluation workflow – a repeatable path for any team
-
Capture requirements – Write a short “test‑data charter” (1‑page) listing:
- Target environments (local, CI, staging, perf)
- Must‑have constraints (FK, triggers, partitioning)
- Volume targets (rows, GB)
- Determinism vs. realism split (e.g., 80 % deterministic for unit, 20 % realistic for load)
- Compliance flags (PII, GDPR, PCI)
-
Prototype the top two candidates –
- Pick the two approaches that score highest on your charter.
- Build a minimum viable data set for a single bounded context (e.g., “order‑processing”).
- Measure: script length, runtime, flakiness, onboarding time for a new dev.
-
Score against the charter – Use a weighted matrix (weights = business priority). Example weights: schema complexity 30 %, volume 20 %, determinism 15 %, CI integration 15 %, compliance 10 %, maintenance 10 %.
-
Decision gate – If the winner scores ≥ 80 % of the max possible, adopt it. Otherwise, iterate: add a third candidate or refine the charter.
-
Document the contract – Record:
- Generation entry point (CLI command, rake task, CI step)
- Versioning scheme for data sets (git tag, artifact version)
- Ownership (who updates factories when schema changes)
-
Roll out incrementally – Replace one test suite at a time, monitor flakiness and runtime, then expand.
4. Worked example – an e‑commerce core schema
4.1 Schema snapshot (PostgreSQL)
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL CHECK (price_cents > 0),
active BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
total_cents INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price_cents INTEGER NOT NULL
);
4.2 Goal: generate a data set that satisfies:
| Requirement | Detail |
|---|---|
| Referential integrity | Every order points to an existing user; every order_item points to an existing order and product. |
| Deterministic IDs for unit tests | users.id = 1..10, products.id = 1..20, orders.id = 1..30. |
| Realistic price distribution for load test | price_cents follows a log‑normal curve (median ≈ $29.99). |
| PII masking | email and full_name must be synthetic, never real. |
| Volume | 10 k users, 5 k products, 50 k orders, 150 k order_items (≈ 30 MB). |
4.3 Approach A – Raw SQL inserts (hand‑crafted COPY files)
File: users.csv (10 k rows)
id,email,full_name,created_at
1,user1@example.com,User One,2024-01-01 00:00:00+00
2,user2@example.com,User Two,2024-01-01 00:00:01+00
...
10000,user10000@example.com,User Ten Thousand,2024-01-01 02:46:40+00
File: products.csv (5 k rows) – price generated offline with a Python script that writes a log‑normal column.
File: orders.csv – requires a join to pick a valid user_id. You either pre‑compute the join in the script or write a multi‑statement SQL script that inserts users, then orders, then order_items.
Pros
- Pure SQL, runs in any client (
psql \copy,COPY FROM STDIN). - Zero runtime dependencies.
COPYis the fastest bulk load path in Postgres.
Cons
- Maintaining the join logic across four files is error‑prone.
- Adding a new column (e.g.,
users.phone) means editing every CSV and the load script. - No built‑in masking – you must guarantee synthetic data yourself.
- Deterministic IDs are easy, but realistic distributions require external scripts.
Typical maintenance cost – ~2 h per schema change (add column, new FK, new enum value).
4.4 Approach B – Factory library (Factory Bot / Factory Boy)
# spec/factories/users.rb
FactoryBot.define do
factory :user do
sequence(:email) { |n| "user#{n}@example.com" }
sequence(:full_name) { |n| "User #{n}" }
created_at { Time.zone.now }
end
end
# spec/factories/products.rb
FactoryBot.define do
factory :product do
sequence(:sku) { |n| "SKU-%06d" % n }
sequence(:name) { |n| "Product #{n}" }
price_cents { LogNormalGenerator.call(median: 2999, sigma: 0.6).to_i }
active { true }
end
end
# spec/factories/orders.rb
FactoryBot.define do
factory :order do
association :user, factory: :user
status { %w[new paid shipped cancelled].sample }
created_at { Time.zone.now }
after(:build) do |order|
order.total_cents = order.order_items.sum { |i| i.quantity * i.unit_price_cents }
end
end
end
# spec/factories/order_items.rb
FactoryBot.define do
factory :order_item do
association :order, factory: :order
association :product, factory: :product
quantity { rand(1..5) }
unit_price_cents { product.price_cents }
end
end
Generating the data set (Rake task)
namespace :test_data do
desc "Create deterministic seed for CI"
task seed: :environment do
10_000.times { create(:user) }
5_000.times { create(:product) }
50_000.times { create(:order) } # each order creates 3 items on average
end
end
Pros
- Centralized model definitions; adding a column updates one place.
- Traits let you express “paid order”, “cancelled order”, “inactive product” without new factories.
- Deterministic sequences give stable IDs for unit tests.
- Ruby/Python runtime already present in most CI images.
Cons
- One
INSERTper object → 215 k round‑trips; ~30 s on a modest CI runner. - Random traits (
sample,rand) break determinism unless you seed the RNG globally. - Complex distributions (log‑normal) need custom generators or callbacks.
- No built‑in PII masking – you must ensure synthetic data yourself.
Typical maintenance cost – ~30 min per schema change (add attribute, adjust trait).
4.5 Approach C – Dedicated test‑data generator (QA3 free generator)
The generator at /tools/test-data-generator reads the Postgres schema (via pg_dump --schema-only or a live connection) and lets you declare a data‑spec in YAML:
# qa3-spec.yaml
version: 1
tables:
users:
count: 10000
columns:
email: {type: synthetic_email}
full_name: {type: synthetic_name}
created_at: {type: timestamp, start: "2024-01-01", end: "2024-01-02"}
products:
count: 5000
columns:
sku: {type: sequence, prefix: "SKU-", width: 6}
name: {type: synthetic_word, count: 2}
price_cents: {type: lognormal, median: 2999, sigma: 0.6}
active: {type: constant, value: true}
orders:
count: 50000
columns:
user_id: {type: fk, table: users, column: id}
status: {type: enum, values: [new, paid, shipped, cancelled]}
created_at: {type: timestamp, start: "2024-01-01", end: "2024-01-02"}
total_cents: {type: computed, expr: "sum(order_items.quantity * order_items.unit_price_cents)"}
order_items:
count: 150000
columns:
order_id: {type: fk, table: orders, column: id}
product_id: {type: fk, table: products, column: id}
quantity: {type: uniform_int, min: 1, max: 5}
unit_price_cents: {type: fk, table: products, column: price_cents}
Running it
docker run --rm -v $(pwd):/work qa3io/test-data-generator \
--spec /work/qa3-spec.yaml \
--out /work/seed.sql \
--dialect postgres
The tool emits a single seed.sql that uses COPY for
Read more
GraphQL Test Data Generation for Queries and Mutations
A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Postman Test Data Generation with CSV and JSON Files
A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Generate API Test Data Directly from an OpenAPI Specification
A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.