How to Generate Realistic Mock Data from SQL Schemas

Writing realistic test data by hand is one of the most tedious parts of development. Hard-coded test fixtures with names like "Test User" and emails like "test@test.com" don't represent real-world data distributions and often miss edge cases. A SQL schema-based mock data generator solves this by creating contextually accurate test rows directly from your database structure.

What is Schema-Based Mock Data Generation?

Instead of writing test fixtures manually, you provide your CREATE TABLE DDL and the generator:

1. Reads each column's type to determine what kind of data to generate
2. Reads the column name to add context (e.g., a column named email gets email addresses)
3. Respects NOT NULL constraints (always generates a value)
4. Handles nullable columns by occasionally returning null (~20% of the time for realism)
5. Maintains referential integrity between foreign key relationships
6. Generates multiple rows in a single output

Column Type to Mock Data Mapping

SQL Type	Generated Mock Data	Example
SERIAL / INT AUTO_INCREMENT	Sequential integer	1, 2, 3
INTEGER	Random integer in range	42, 1337
DECIMAL(10,2)	Float with 2dp	99.95, 1234.50
VARCHAR(n) — name column	Full name	"Jane Smith"
VARCHAR(n) — email column	Email address	"jane.smith@example.com"
VARCHAR(n) — phone column	Phone number	"+1-555-0147"
VARCHAR(n) — url/website	URL	"https://example.com"
TEXT	Lorem ipsum paragraph	"Lorem ipsum..."
BOOLEAN / TINYINT(1)	true or false	true
TIMESTAMP / DATETIME	ISO 8601 datetime	"2024-03-15T09:41:22Z"
DATE	ISO 8601 date	"2024-03-15"
UUID	UUID v4	"a1b2c3d4-e5f6-..."
JSONB / JSON	Nested JSON object	{"key": "value"}
BYTEA / BLOB	Base64 string	"SGVsbG8="

Context-Aware Column Name Detection

The most powerful feature is column name inference. The generator detects common column name patterns and generates appropriate data:

-- These column names trigger specific Faker generators:
first_name, last_name, full_name, name  → Person names
email, email_address                    → Email addresses
phone, phone_number, mobile             → Phone numbers
address, street_address                 → Street addresses
city                                    → City names
country                                 → Country names
zip_code, postal_code                   → Postal codes
company, company_name, organization     → Company names
website, url, homepage                  → URLs
description, bio, about                 → Lorem paragraphs
price, amount, total, cost              → Monetary amounts
quantity, count, num_                   → Integer counts
ip_address, ip                          → IP addresses
user_agent                              → Browser user agent strings

Example: Schema to Mock Data

Given this schema:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(30),
  company VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE
);

The generator produces realistic JSON output like:

[
  {
    "id": 1,
    "first_name": "Marcus",
    "last_name": "Chen",
    "email": "marcus.chen@example.net",
    "phone": "+1-415-555-0189",
    "company": "Apex Systems LLC",
    "created_at": "2024-02-14T11:23:45.000Z",
    "is_active": true
  },
  {
    "id": 2,
    "first_name": "Sarah",
    "last_name": "O'Brien",
    "email": "sarah.obrien@workplace.io",
    "phone": null,
    "company": null,
    "created_at": "2024-03-01T08:15:00.000Z",
    "is_active": true
  }
]

Notice: phone and company are nullable columns, so some rows have null values for realism.

Use Cases for Schema-Based Mock Data

1. Unit and Integration Testing

Generate test fixtures that look like real data. Tests that use "test@test.com" miss real-world edge cases like apostrophes in names, international characters, and long string values.

2. Frontend Development Before the Backend is Ready

Frontend developers can generate realistic JSON responses matching the exact schema the API will eventually return, enabling parallel development.

3. Load and Performance Testing

Generate thousands of rows for load testing without needing a production data dump. The data distributions are realistic enough to test query performance and caching behavior.

4. Database Seeding

Seed a development or staging database with realistic data for demos, QA testing, or developer onboarding.

5. API Documentation and Examples

Generate realistic example JSON for API documentation that shows what real responses look like, instead of placeholder data.

Generate Mock Data from Your SQL Schema

Use SchemaForge's free SQL mock data generator to generate realistic JSON from your CREATE TABLE scripts. Supports PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and MariaDB. Choose how many rows to generate, then copy the output directly into your tests or seed scripts.

Everything runs in your browser — your schema never leaves your machine, making it safe to use with production or confidential table structures.