Generating Massive Test Data with SQL (No Scripts Required)

You've designed a great schema and wrote a clever query. But testing it on 5 rows isn't enough. You need to know how it performs with 10,000 or 100,000 rows.

Does this mean you need to write a Python script to generate CSVs and import them?

No! You can generate massive, realistic datasets purely with SQL using Recursive CTEs.

A Real Business Scenario: Reproducing a Slow Query Before Release

Suppose you are about to ship a reporting page that groups users by signup month and activity bucket.

The query looks fine on your laptop with 20 rows. The problem is that production will have:

• tens of thousands of users
• uneven signup dates
• skewed activity distributions

If you test only on tiny hand-written samples, you miss the real risks:

• an index that looks unnecessary until row counts grow
• a sort that spills once the result is wide enough
• pagination or charts that behave badly with realistic volume

Synthetic SQL-generated data helps you pressure-test the exact schema and query shape you already have, without context-switching into a separate scripting tool for every experiment.

The Magic of WITH RECURSIVE

A Recursive CTE (Common Table Expression) allows a query to refer to itself. This is perfect for generating series (1, 2, 3...) which can then be transformed into data.

Generating a Number Series

Here is the "Hello World" of test data:

WITH RECURSIVE generate_series(value) AS (
  SELECT 1  -- Initial value
  UNION ALL
  SELECT value + 1 FROM generate_series
  WHERE value + 1 <= 10 -- Stop condition
)
SELECT value FROM generate_series;

Generating Random Data

Once you have a sequence of numbers, you can use SQLite's math functions to generate random attributes.

• Random Integer (1-100): ABS(RANDOM()) % 100 + 1
• Random Date: DATE('now', '-' || (ABS(RANDOM()) % 365) || ' days')
• Random String: Use CASE or substring logic (though SQL is a bit limited here, we can fake it).

A Common Mistake: Random But Unrealistic Data

It is easy to generate random rows that technically fill a table but fail to simulate the production patterns you care about.

For example:

• perfectly uniform dates, even though real traffic spikes on weekdays
• totally random foreign keys, even though a few entities dominate activity
• complete data with no NULLs, even though production imports are messy

That kind of data is fine for smoke tests, but weak for debugging query plans or UI behavior. Good test data should reflect the constraints and weirdness of the real system, not just the column types.

If your production data is skewed, sparse, or seasonal, your synthetic dataset should be too.

Interactive Example: Creating 1,000 Users

Let's generate a users table with 1,000 rows. We'll give them:

• An ID
• A random "Group" (A, B, C, D, E)
• A random Signup Date within the last year
• A random Activity Score (0-5000)

Why random data matters

Generating random data is crucial for:

1. Index Testing: An index behaves differently with 10 rows vs 100,000 rows.
2. Query Optimization: You can't see "Slow Query" warnings if your query runs in 0.001ms on an empty table.
3. UI Stress Testing: See how your frontend handles pagination, sorting, and large numbers.

Boundary and Performance Notes

Recursive generation is powerful, but it has limits.

• Large recursive CTEs can be slower than bulk-loading from a file if you need millions of rows across many related tables.
• Pure randomness is hard to reproduce unless you fix the generation logic carefully. If a failing case matters, deterministic fixture patterns are easier to debug than constantly changing random values.
• If tables have foreign keys, uniqueness rules, or realistic correlations, you often need staged inserts instead of one giant statement.
• Massive local seed scripts are useful for testing, but they should stay out of production migration paths unless you explicitly want them there.

In practice, SQL generation is best for quick performance experiments, reproducible demos, and lightweight fixtures that stay close to the schema under test.

Generating Dates

A common requirement is generating a continuous range of dates (e.g., "every day in 2024").

WITH RECURSIVE dates(date) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT DATE(date, '+1 day')
  FROM dates
  WHERE date < '2024-12-31'
)
SELECT * FROM dates;

This is invaluable for "Filling the gaps" in charts where you have no sales for a specific day but still want the day to appear on the X-axis.

When NOT to Generate Data This Way

Do not force everything into recursive SQL if another method fits the job better.

Examples:

• you need realistic names, addresses, or localized content
• you need cross-table correlations that are easier to express in application code
• you are testing privacy-sensitive workflows and should instead mask a production-like extract

Recursive SQL is great when the point is volume, sequence generation, or quick schema-aligned fixtures. It is less ideal when realism depends on domain-specific logic or external libraries.

Official References

• SQLite WITH clause documentation for recursive CTE syntax, rules, and caveats.
• SQLite core functions for random() and related built-in behavior used in synthetic data generation.
• PostgreSQL set-returning functions for a contrasting major-engine reference where generate_series is built in rather than simulated recursively.

Related Articles

• Mastering CTEs: Writing Cleaner, Better SQL for the underlying WITH mental model behind recursive series generation.
• Optimizing Large Dataset Queries for what to test once the synthetic row counts are finally large enough to reveal real bottlenecks.
• Data Masking and Anonymization Techniques in SQL for the cases where you should transform production-like data safely instead of generating everything from scratch.

Conclusion

You don't need external tools to populate your database. With SQLite's WITH RECURSIVE, you have a powerful factory for synthetic data right at your fingertips.

Next time you need to test performance, don't guess—generate!