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.

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).

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.

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.

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!