json2sql in CI/CD: Automate Database Seeding from JSON Fixtures

Stop committing hand-written .sql seed files. Generate INSERT statements from JSON fixtures on every CI run — deterministic test databases, zero manual SQL, and pipe-friendly workflows for PostgreSQL, MySQL, and SQLite.

If your test database seed scripts live in a seeds/ folder full of hand-written .sql files, you already know the problem: they go stale. Someone adds a column to the schema but forgets the INSERT statement. Someone changes an enum value but the seed data still references the old one. Integration tests pass locally (with your hand-curated data) but fail in CI because the seed SQL doesn't match the latest schema.

There's a better pattern: store your test data as JSON and let a tool generate the SQL on every CI run. That way your seed data always matches your fixtures, and the SQL is never out of date because it's generated, not maintained.

The Problem with Committed Seed SQL

Most projects seed their test databases one of three ways:

1. Hand-written INSERT statements — brittle, drift from schema changes
2. ORM fixtures — tied to a specific ORM, not portable
3. Database-specific dumps — can't switch between PostgreSQL, MySQL, and SQLite

All three share the same flaw: the seed data format is coupled to the database, not the source of truth. Your API responses are JSON. Your test fixtures are JSON. But your seed scripts are SQL — a manual translation that inevitably drifts.

The Pattern: JSON Fixtures → SQL on Every Run

The fix is simple: keep your data in JSON and generate SQL at seed time:

# Instead of: psql test_db < seeds/users.sql
# Do this:       json2sql convert fixtures/users.json --dialect postgres | psql test_db

Every CI run regenerates the SQL from the current JSON fixtures. No drift, no stale INSERT statements, no manual maintenance.

Step 1: Organize Your JSON Fixtures

Structure your test data alongside your tests:

tests/
├── fixtures/
│   ├── users.json
│   ├── orders.json
│   └── products.json
├── test_api.py
└── conftest.py

Your fixture files are just JSON — the same format your API returns:

// tests/fixtures/users.json
[
  { "id": 1, "name": "Alice Johnson", "email": "alice@example.com", "active": true },
  { "id": 2, "name": "Bob Chen", "email": "bob@example.com", "active": false },
  { "id": 3, "name": "Carol Wu", "email": "carol@example.com", "active": true }
]

Step 2: Add json2sql to Your CI Pipeline

GitHub Actions

name: Integration Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_db
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Install json2sql
        run: pip install json2sql-cli

      - name: Seed database from JSON fixtures
        run: |
          for fixture in tests/fixtures/*.json; do
            table=$(basename "$fixture" .json)
            json2sql convert "$fixture" --dialect postgres --table "$table" \
              | psql "postgres://test:test@localhost:5432/test_db"
          done

      - name: Run integration tests
        run: pytest tests/ -v

GitLab CI

test:
  image: python:3.12
  services:
    - postgres:16
  variables:
    POSTGRES_DB: test_db
    POSTGRES_USER: test
    POSTGRES_PASSWORD: test
  script:
    - pip install json2sql-cli pytest
    - |
      for fixture in tests/fixtures/*.json; do
        table=$(basename "$fixture" .json)
        json2sql convert "$fixture" --dialect postgres --table "$table" \
          | psql "postgres://test:test@postgres:5432/test_db"
      done
    - pytest tests/ -v

Step 3: Handle Nested Data with --flatten

Real fixture data often includes nested objects and arrays. The --flatten flag turns them into proper relational tables:

// tests/fixtures/orders.json — nested structure
[
  {
    "id": 101,
    "customer_id": 1,
    "items": [
      { "product_id": 5, "quantity": 2, "price": 29.99 },
      { "product_id": 12, "quantity": 1, "price": 49.99 }
    ],
    "total": 109.97
  }
]

# Flatten nested arrays into separate tables with foreign keys
json2sql convert tests/fixtures/orders.json --flatten --dialect postgres

Output includes two tables — orders and orders_items — linked by the parent's id:

CREATE TABLE "orders" (
 "id" INTEGER,
 "customer_id" INTEGER,
 "total" DOUBLE PRECISION
);

INSERT INTO "orders" ("id", "customer_id", "total")
VALUES (101, 1, 109.97);

CREATE TABLE "orders_items" (
 "orders_id" INTEGER,
 "product_id" INTEGER,
 "quantity" INTEGER,
 "price" DOUBLE PRECISION
);

INSERT INTO "orders_items" ("orders_id", "product_id", "quantity", "price")
VALUES (101, 5, 2, 29.99),
 (101, 12, 1, 49.99);

Step 4: Schema-First Pipeline for Fresh Databases

When your CI creates a fresh database every run, you need DDL before DML. json2sql's --schema-only flag lets you split the pipeline:

# Step 1: Create tables from JSON structure
json2sql convert fixtures/users.json --schema-only --dialect postgres | psql $DATABASE_URL

# Step 2: Insert data
json2sql convert fixtures/users.json --dialect postgres --table users | psql $DATABASE_URL

This is especially useful when you need to add indexes or constraints between table creation and data insertion:

# Generate schema, add custom indexes, then seed
json2sql convert fixtures/users.json --schema-only --dialect postgres | psql $DATABASE_URL
psql $DATABASE_URL -c "CREATE UNIQUE INDEX idx_users_email ON users(email);"
json2sql convert fixtures/users.json --dialect postgres --table users | psql $DATABASE_URL

Step 5: Pipe-Friendly Workflows

json2sql reads from stdin and writes to stdout — it's designed for Unix pipelines:

# Fetch live API data, convert, and seed in one pipeline
curl -s https://api.staging.example.com/users | json2sql convert --dialect postgres --table users | psql $DATABASE_URL

# Convert multiple fixtures and merge into one seed file
for f in tests/fixtures/*.json; do
  table=$(basename "$f" .json)
  json2sql convert "$f" --dialect postgres --table "$table"
done > seed.sql

# Validate the generated SQL before applying
json2sql convert fixtures/users.json --dialect postgres --table users > seed.sql
cat seed.sql  # review
psql $DATABASE_URL < seed.sql

Step 6: Multi-Dialect Testing

If your application supports multiple databases, you can test against all of them from the same JSON fixtures:

# Generate SQL for each dialect from the same JSON
json2sql convert fixtures/users.json --dialect postgres --table users  # PostgreSQL
json2sql convert fixtures/users.json --dialect mysql --table users     # MySQL
json2sql convert fixtures/users.json --dialect sqlite --table users    # SQLite

Key dialect differences handled automatically:

Complete GitHub Actions Example

Here's a full workflow that tests against PostgreSQL, MySQL, and SQLite simultaneously from the same JSON fixtures:

name: Multi-DB Integration Tests

on: [push]

jobs:
  test-postgres:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env: { POSTGRES_DB: test, POSTGRES_USER: test, POSTGRES_PASSWORD: test }
        ports: ['5432:5432']
        options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - run: pip install json2sql-cli
      - run: |
          for f in tests/fixtures/*.json; do
            json2sql convert "$f" --flatten --dialect postgres --table "$(basename "$f" .json)" \
              | psql "postgres://test:test@localhost:5432/test"
          done
      - run: pytest tests/ -v -k postgres

  test-mysql:
    runs-on: ubuntu-latest
    services:
      mysql:
        image: mysql:8
        env: { MYSQL_DATABASE: test, MYSQL_ROOT_PASSWORD: test }
        ports: ['3306:3306']
        options: --health-cmd "mysqladmin ping" --health-interval 10s --health-timeout 5s --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - run: pip install json2sql-cli
      - run: |
          for f in tests/fixtures/*.json; do
            json2sql convert "$f" --flatten --dialect mysql --table "$(basename "$f" .json)" \
              | mysql -h 127.0.0.1 -u root -ptest test
          done
      - run: pytest tests/ -v -k mysql

  test-sqlite:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install json2sql-cli
      - run: |
          for f in tests/fixtures/*.json; do
            json2sql convert "$f" --flatten --dialect sqlite --table "$(basename "$f" .json)" \
              >> seed.sql
          done
          sqlite3 test.db < seed.sql
      - run: pytest tests/ -v -k sqlite

Why This Beats Committed Seed SQL

Getting Started

pip install json2sql-cli
json2sql convert fixtures/users.json --flatten --dialect postgres | psql $DATABASE_URL

json2sql is part of the DevForge developer tool ecosystem — 11 CLI tools built by autonomous AI for autonomous developers.