# Seed a Database with Realistic, Validated Test Data

![banner](https://articles.aux4.blog/data/posts/aux4/seed-a-database-with-realistic-validated-test-data/banner.png)

## Introduction

Empty databases make for bad demos and worse tests, but hand-writing `INSERT` statements full of believable people is tedious. This post builds a repeatable seeding pipeline: `aux4/faker` invents the records, `aux4/validator` drops anything that breaks your rules, and `aux4/db-sqlite` inserts the survivors in a single transaction. Every stage reads stdin and writes stdout, so the whole thing is one chain of pipes you can rerun on demand.

## Install the packages

```bash
aux4 aux4 pkger install aux4/faker aux4/validator aux4/db-sqlite aux4/2table
```

## Step 1: generate realistic data

A single value takes a **category** and a `--type`:

```bash
aux4 fake value person --type firstName              # -> Ivah
aux4 fake value internet --type email                # -> Wilbert_Hahn70@hotmail.com
aux4 fake value number --type int --arg min=18 --arg max=65   # -> 31
```

Run `aux4 fake list` to browse the categories (`person`, `internet`, `location`, `company`, `commerce`, `date`, and more).

For seeding you want whole records. Define the record shape in a `config.yaml` under a named config — here, `newUser`. Each field gets a `fake` spec written as `<category> <type>`:

```yaml
config:
  newUser:
    mapping:
      name:
        fake: person fullName
      email:
        fake: internet email
      age:
        fake:
          category: number
          type: int
          args:
            min: 16
            max: 80
```

`aux4 fake` auto-discovers `config.yaml` in the current directory. Unlike `value`, `fake object` reads stdin (so it can enrich records you pipe in), so redirect from `/dev/null` when you just want fresh data. `--count` returns a JSON array:

```bash
aux4 fake object --config newUser --count 6 < /dev/null
```

```json
[
  { "name": "Frances Steuber MD", "email": "Karianne44@gmail.com", "age": 49 },
  { "name": "Dan Douglas", "email": "Lempi_Denesik@hotmail.com", "age": 54 },
  { "name": "Lucia Cruickshank", "email": "Rudy_Romaguera65@yahoo.com", "age": 29 },
  { "name": "Bert Hauck", "email": "Alison73@hotmail.com", "age": 67 },
  { "name": "Marsha Runte", "email": "Maudie22@gmail.com", "age": 17 },
  { "name": "Mrs. Dora Muller", "email": "Shany.Stamm81@hotmail.com", "age": 53 }
]
```

Notice `Marsha Runte` at `age: 17` — below the minimum of 18 we want in the table (faker produced 16–80). That's exactly the kind of row validation should catch.

## Step 2: keep only valid records

`aux4 validator validate` checks each item against a named rule set and can output only the ones that pass. Add the rules to the same `config.yaml` — each field points at a JSONPath and a pipe-separated list of rules:

```yaml
config:
  data:
    user:
      name:
        path: $.name
        rule: required
      email:
        path: $.email
        rule: required|email
      age:
        path: $.age
        rule: required|integer|min:18
```

Pipe a batch through it. `--onlyValid` keeps only the clean rows, and `--ignore` holds the exit code at `0` so it plays nicely in a pipeline:

```bash
aux4 fake object --config newUser --count 6 < /dev/null \
  | aux4 validator validate --config data --rules user --onlyValid --ignore
```

```json
[
  { "name": "Frances Steuber MD", "email": "Karianne44@gmail.com", "age": 49 },
  { "name": "Dan Douglas", "email": "Lempi_Denesik@hotmail.com", "age": 54 },
  { "name": "Lucia Cruickshank", "email": "Rudy_Romaguera65@yahoo.com", "age": 29 },
  { "name": "Bert Hauck", "email": "Alison73@hotmail.com", "age": 67 },
  { "name": "Mrs. Dora Muller", "email": "Shany.Stamm81@hotmail.com", "age": 53 }
]
```

The under-18 row is gone. Swap in `--onlyInvalid` to see *why* something failed — the offending item plus its errors:

```json
[
  {
    "item": { "name": "Marsha Runte", "email": "Maudie22@gmail.com", "age": 17 },
    "errors": { "age": ["The age must be at least 18."] }
  }
]
```

## Step 3: create the table

`aux4/db-sqlite` adds a `sqlite` driver under the `db` profile. Create the database file and table with `execute`:

```bash
aux4 db sqlite execute \
  --database app.db \
  --query "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT)"
```

## Step 4: stream the survivors into SQLite

Here's where it all connects. `db sqlite` uses `:name` placeholders that get filled from each incoming record, and `--inputStream` reads those records from stdin. `--tx` wraps the whole batch in a single transaction — all rows commit, or none do.

Faker emits a JSON array and validator passes one through, so the whole seed is a single pipe with no glue between the stages:

```bash
aux4 fake object --config newUser --count 50 < /dev/null \
  | aux4 validator validate --config data --rules user --onlyValid --ignore \
  | aux4 db sqlite execute \
      --database app.db \
      --query "INSERT INTO users (name, age, email) VALUES (:name, :age, :email) returning *" \
      --inputStream --tx
```

The `returning *` clause echoes back each inserted row with its assigned `id`:

```json
[
  { "id": 1, "name": "Frances Steuber MD", "age": 49, "email": "Karianne44@gmail.com" },
  { "id": 2, "name": "Dan Douglas", "age": 54, "email": "Lempi_Denesik@hotmail.com" },
  { "id": 3, "name": "Lucia Cruickshank", "age": 29, "email": "Rudy_Romaguera65@yahoo.com" }
]
```

Run it again to top up the table with another fresh, valid batch — the pipeline is fully repeatable.

## Step 5: eyeball the result

Query the table and pipe it to `aux4 2table` for a readable check:

```bash
aux4 db sqlite execute --database app.db \
  --query "SELECT id, name, age, email FROM users LIMIT 5" \
  | aux4 2table id,name,age:"Age",email:"Email"
```

```text
 id  name                Age  Email
  1  Frances Steuber MD   49  Karianne44@gmail.com
  2  Dan Douglas          54  Lempi_Denesik@hotmail.com
  3  Lucia Cruickshank    29  Rudy_Romaguera65@yahoo.com
  4  Bert Hauck           67  Alison73@hotmail.com
  5  Mrs. Dora Muller     53  Shany.Stamm81@hotmail.com
```

`db sqlite execute` returns a JSON array, which is exactly what `2table` expects.

## Conclusion

Three tools, one repeatable seeding pipeline:

- **faker** invents lifelike records from a `mapping` you define,
- **validator** enforces your schema and quietly drops the junk,
- **db-sqlite** inserts the clean batch transactionally, returning the inserted rows.

Because every stage is just stdin-to-stdout, you can reshape any part of it: change the `mapping` to model your real entities, tighten the rules, or point the insert at MySQL or MSSQL by swapping the `db` driver. The generate → validate → load shape stays the same.

## See Also

- [aux4/faker](https://hub.aux4.io/r/public/packages/aux4/faker)
- [aux4/validator](https://hub.aux4.io/r/public/packages/aux4/validator)
- [aux4/db-sqlite](https://hub.aux4.io/r/public/packages/aux4/db-sqlite)
- [aux4/2table](https://hub.aux4.io/r/public/packages/aux4/2table)