How to Seed SQLite with Realistic Test Data

The Problem

SQLite is everywhere: local development databases, mobile apps, embedded systems, Electron apps, and edge runtimes. But seeding a SQLite database with realistic data often gets skipped because it feels like overkill for a "simple" database. Developers end up inserting three rows of garbage data and testing against that, missing bugs that only surface with real-world volumes and variety.

The irony is that SQLite is often the database closest to your users (it literally runs on their devices), yet it gets the least realistic test data. Faker libraries can generate values, but you still need to handle table creation, insertion order, and foreign key enforcement with PRAGMA foreign_keys = ON.

The Solution: MockHero API

MockHero generates realistic, relationally-consistent data and returns it as JSON or raw SQL. For SQLite, you can either insert records programmatically with a driver like better-sqlite3, or request SQL output and pipe it directly into the SQLite CLI. Foreign keys are always valid because MockHero resolves ref fields server-side.

Quick Setup

curl -X POST https://api.mockhero.dev/api/v1/generate \
  -H "x-api-key: mh_your_api_key" \
  -H "Content-Type: application/json" \
  -d '{
  "tables": [
    {
      "name": "categories",
      "count": 8,
      "fields": [
        { "name": "id", "type": "integer_id" },
        { "name": "name", "type": "enum", "params": { "values": ["Electronics","Clothing","Books","Home","Sports","Food","Toys","Health"] } },
        { "name": "slug", "type": "slug" }
      ]
    },
    {
      "name": "products",
      "count": 50,
      "fields": [
        { "name": "id", "type": "integer_id" },
        { "name": "category_id", "type": "ref", "params": { "ref": "categories.id" } },
        { "name": "name", "type": "product_name" },
        { "name": "price_cents", "type": "integer", "params": { "min": 99, "max": 99999 } },
        { "name": "in_stock", "type": "boolean" }
      ]
    }
  ],
  "format": "json"
}'

Step-by-Step Guide

1. Install dependencies

npm install better-sqlite3

2. Get your MockHero API key

Sign up at mockhero.dev/sign-up and copy your API key.

3. Write the seed script

Create seed.mjs:

import Database from "better-sqlite3";

const db = new Database("./dev.db");
db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");

// Create tables
db.exec(`
  CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT NOT NULL
  );
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    in_stock INTEGER NOT NULL DEFAULT 1
  );
`);

const res = await fetch("https://api.mockhero.dev/api/v1/generate", {
  method: "POST",
  headers: {
    "x-api-key": process.env.MOCKHERO_API_KEY,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    tables: [
      {
        name: "categories",
        count: 8,
        fields: [
          { name: "id", type: "integer_id" },
          { name: "name", type: "enum", params: { values: ["Electronics","Clothing","Books","Home","Sports","Food","Toys","Health"] } },
          { name: "slug", type: "slug" },
        ],
      },
      {
        name: "products",
        count: 50,
        fields: [
          { name: "id", type: "integer_id" },
          { name: "category_id", type: "ref", params: { ref: "categories.id" } },
          { name: "name", type: "product_name" },
          { name: "price_cents", type: "integer", params: { min: 99, max: 99999 } },
          { name: "in_stock", type: "boolean" },
        ],
      },
    ],
    format: "json",
  }),
});

const { data } = await res.json();

const insertCategory = db.prepare(
  "INSERT INTO categories (id, name, slug) VALUES (?, ?, ?)"
);
const insertProduct = db.prepare(
  "INSERT INTO products (id, category_id, name, price_cents, in_stock) VALUES (?, ?, ?, ?, ?)"
);

const seedAll = db.transaction(() => {
  for (const c of data.categories) {
    insertCategory.run(c.id, c.name, c.slug);
  }
  for (const p of data.products) {
    insertProduct.run(p.id, p.category_id, p.name, p.price_cents, p.in_stock ? 1 : 0);
  }
});

seedAll();
console.log("Seeded", data.categories.length, "categories and", data.products.length, "products");

4. Run the script

node seed.mjs

5. Verify with the SQLite CLI

sqlite3 dev.db "SELECT p.name, c.name FROM products p JOIN categories c ON p.category_id = c.id LIMIT 10;"

Why MockHero vs Faker / Manual Seeds

• Transaction-friendly — insert all MockHero data in a single SQLite transaction for maximum speed.
• Foreign key safe — ref fields ensure every child record references a valid parent, so PRAGMA foreign_keys never complains.
• Works everywhere — the JSON output is driver-agnostic. Use better-sqlite3, sql.js, or @libsql/client.

Get Started

Seed your SQLite database in seconds. Sign up free at mockhero.dev and get 1,000 rows per month, no credit card required.