How to Generate Realistic SQL Mock Data for Testing

Reliable database testing requires realistic data. When developing features, tuning query indexes, or testing migration scripts, developers frequently run into two opposing traps:

1. Copying production databases to staging or local laptops: This immediately introduces compliance and privacy violations (GDPR, CCPA, HIPAA), risks credential leaks, and creates massive storage bloat.
2. Hand-crafting minimal dummy records ('test1', 'foo', 'bar'): This produces unrealistic query execution plans. An optimizer behaves completely differently on a 3-row table with single-word strings than on a representative dataset with realistic distributions, date ranges, nullabilities, and indexing selectivity.

Generating synthetic, structured SQL test datasets solves both problems: you get production-like column values and cardinality without touching sensitive customer records.

You can generate customizable datasets in seconds using the free SQL Mock Data Generator.

What Makes Mock Data “Realistic”?

To properly exercise database indexes, foreign keys, and application business logic, synthetic data must reflect actual production characteristics:

• Natural Data Formats: Usernames should be lowercase slugs, email addresses should adhere to valid RFC specifications (e.g., @example.com synthetic domains), and phone numbers should follow standard numeric formatting.
• Realistic Cardinality & Enums: Columns like status or role should draw from realistic bounded sets (e.g. 'pending', 'processing', 'delivered'), allowing index selectivity checks to function as they would in production.
• Plausible Date Distributions: Timestamp and date fields should span realistic historical intervals (such as the past 90, 180, or 365 days) rather than defaulting to hardcoded timestamps like '1970-01-01'.
• Sequential and Key Linkages: Order tables need valid foreign keys pointing to customer IDs, and product SKUs should adhere to realistic catalog prefixing (e.g. SKU-8192-3401).

Example: Seeding an E-Commerce Schema

Consider an e-commerce schema with a customer table and an orders table:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  city VARCHAR(50),
  country VARCHAR(50),
  account_balance DECIMAL(10,2),
  created_at DATE
);

Using the SQL Mock Data Generator, you can select the CRM Customers preset and generate 50 or 500 rows of valid dialect-tailored SQL.

For MySQL, the generated batch INSERT script looks like:

CREATE TABLE IF NOT EXISTS `customers` (
  `id` INT,
  `first_name` VARCHAR(255),
  `last_name` VARCHAR(255),
  `email` VARCHAR(255),
  `phone` VARCHAR(255),
  `city` VARCHAR(255),
  `country` VARCHAR(255),
  `account_balance` DECIMAL(10, 2),
  `created_at` DATE
);

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `email`, `phone`, `city`, `country`, `account_balance`, `created_at`) VALUES
  (1, 'James', 'Miller', '[email protected]', '555-481-9204', 'Chicago', 'United States', 482.50, '2025-11-14'),
  (2, 'Sarah', 'Davis', '[email protected]', '555-312-8741', 'London', 'United Kingdom', 1240.00, '2026-03-02'),
  (3, 'Michael', 'Wilson', '[email protected]', '555-894-1029', 'Toronto', 'Canada', 35.75, '2025-08-29');

Database Dialect Differences in Multi-Row INSERTs

When loading test data via SQL files, database engines differ in identifier escaping, boolean literals, and maximum batch sizes:

1. Identifier Quoting

• MySQL / MariaDB: Uses backticks (`table_name`, `column_name`).
• PostgreSQL: Standard SQL double quotes ("table_name", "column_name").
• SQLite: Double quotes or backticks, standardizing on double quotes ("table_name").
• SQL Server (T-SQL): Square brackets ([table_name], [column_name]).

2. Multi-Row INSERT Limits

• SQL Server Limit (1,000 Rows): SQL Server enforces a strict maximum of 1,000 rows per single INSERT INTO ... VALUES (...) statement (Error 10738: “The number of row value expressions in the INSERT statement exceeds the maximum allowable number of 1000 row values”). The SQL Mock Data Generator automatically breaks datasets larger than 1,000 rows into separate 1,000-row batch chunks.
• MySQL max_allowed_packet: MySQL allows many thousands of rows per INSERT, constrained by the server’s max_allowed_packet variable (typically 64MB).
• SQLite Variable Limits: Older SQLite builds had a 999 parameter ceiling (SQLITE_MAX_VARIABLE_NUMBER). Chunking multi-row statements into batches of 100 to 500 rows ensures reliable execution across all embedded versions.

Extracting Columns Directly from DDL

If you already have existing schema files or migration scripts, you don’t need to rebuild column lists by hand.

1. Open the SQL Mock Data Generator.
2. Expand the “Or Import Columns from existing CREATE TABLE DDL” accordion.
3. Paste your CREATE TABLE statement:

   CREATE TABLE orders (
     id INT PRIMARY KEY,
     order_number VARCHAR(32),
     customer_id INT,
     total_amount DECIMAL(10,2),
     status VARCHAR(20),
     created_at TIMESTAMP
   );
   

4. Click “Extract Columns from DDL”. The tool parses the table name and columns, automatically inferring data generators based on column types and naming patterns.

Instant In-Browser Validation in SQLite Playground

Once your mock data SQL is generated, you can test queries immediately without deploying to a server:

1. Click “Test in SQLite Playground →”.
2. The generated CREATE TABLE and batch INSERT script is transferred seamlessly to the SQLite SQL Playground via secure browser session storage.
3. You can immediately execute analytical queries, test JOIN operations, or verify index coverage:

   SELECT country, COUNT(*) AS customer_count, ROUND(AVG(account_balance), 2) AS avg_balance
   FROM customers
   GROUP BY country
   ORDER BY avg_balance DESC;
   

4. You can also export the populated database as a local .sqlite file for use in your unit test suite.

Summary Checklist for Synthetic Test Data

• Never copy raw PII: Always use synthetic emails, names, and phone numbers.
• Match production dialects: Keep quotes, types, and batch chunk sizes compliant with your target engine.
• Verify schema compatibility: Parse existing DDL to prevent column drift.
• Test queries early: Use in-browser playgrounds to ensure generated datasets test edge cases and query patterns effectively.

Generate your next test dataset now with the SQL Mock Data Generator.