Quality is not optional. It's our standard. Free QA tools for testers and developers.

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:

CategoryTypical representativesWhat you actually write/maintain
Raw SQL insertsHand‑crafted INSERT … VALUES scripts, COPY/BULK INSERT filesEvery column, every relationship, every edge case
Factory librariesFactory Bot (Ruby), Factory Boy (Python), Django‑factory‑boy, Go‑factory, .NET AutoFixtureDeclarative model definitions, traits, sequences, callbacks
Dedicated test‑data toolsCommercial 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

SymptomLikely causeCost if ignored
Tests pass locally, fail in CISeed script assumes a specific database state (e.g., auto‑increment values)Lost confidence, wasted debugging cycles
Test suite runtime > 30 minOne‑by‑one inserts for large reference tablesSlow feedback, reduced deployment frequency
Flaky tests after schema changeHard‑coded column lists in SQL scriptsRegression risk, manual script updates
Production‑like data needed for performance or security testingNo tooling to generate realistic distributionsInadequate load testing, compliance gaps
Team spends > 20 % of sprint on test‑data maintenanceNo shared abstraction, each test writes its own dataOpportunity 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

CriterionWhy it mattersSQL insertsFactoriesDedicated tools
Schema complexity (FK depth, triggers, computed columns)Determines how much manual wiring you needHigh effort – you must honor every constraintMedium – factories can express relationships, but you still write callbacks for triggersLow – tool reads schema and builds a valid graph automatically
Data volume (rows per table, total size)Affects insert speed and memory usageCOPY/BULK INSERT can be fast, but you must craft filesFactories usually emit one INSERT per object → slower at scaleBulk generation, parallel workers, streaming output
Determinism vs. realismUnit tests need repeatable data; perf tests need realistic distributionsFully deterministic if you write exact valuesTraits/sequences give deterministic IDs; random traits add realismConfigurable distributions (Gaussian, Zipf, custom) + seedable RNG
CI/CD integrationMust run in ephemeral containers, no manual stepsSimple – just run a scriptRequires language runtime (Ruby, Python, etc.) in pipelineOften provides Docker image or CLI; can output SQL files for any runner
Team skill setLearning 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

  1. 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)
  2. 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.
  3. 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 %.

  4. Decision gate – If the winner scores ≥ 80 % of the max possible, adopt it. Otherwise, iterate: add a third candidate or refine the charter.

  5. 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)
  6. 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:

RequirementDetail
Referential integrityEvery order points to an existing user; every order_item points to an existing order and product.
Deterministic IDs for unit testsusers.id = 1..10, products.id = 1..20, orders.id = 1..30.
Realistic price distribution for load testprice_cents follows a log‑normal curve (median ≈ $29.99).
PII maskingemail and full_name must be synthetic, never real.
Volume10 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.
  • COPY is 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 INSERT per 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.