Metadata-Version: 2.4
Name: sowdb
Version: 0.1.0
Summary: Generate realistic, referentially-consistent test data for PostgreSQL databases from their own schema.
Project-URL: Homepage, https://github.com/aldanlt/sowdb
Project-URL: Repository, https://github.com/aldanlt/sowdb
Project-URL: Issues, https://github.com/aldanlt/sowdb/issues
Project-URL: Changelog, https://github.com/aldanlt/sowdb/blob/main/CHANGELOG.md
Author: Aldan Lema Touza
License-Expression: MIT
License-File: LICENSE
Keywords: cli,database,faker,fixtures,postgresql,test-data
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Testing
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: faker>=24.0
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: pydantic>=2.5
Requires-Dist: rich>=13.0
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: typer>=0.12
Provides-Extra: dev
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pre-commit>=3.7; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Description-Content-Type: text/markdown

# sowdb

**Generate realistic, referentially-consistent test data for PostgreSQL — straight
from the schema that's already there.**

Seeding a database for local development, demos, or load testing usually means
either writing brittle fixture scripts by hand or letting a generic faker
library spray random values that ignore your foreign keys, `UNIQUE`
constraints, and column lengths. `sowdb` reads your PostgreSQL schema,
figures out a safe insertion order from the foreign-key graph, and generates
values that actually respect `NOT NULL`, `UNIQUE`, `VARCHAR` lengths, and
foreign keys — so every row it produces is one Postgres will actually accept.

<!-- TODO: demo.gif showing `sowdb inspect` + `sowdb generate` against examples/blog.sql -->

## Install

```bash
pip install sowdb
```

Requires Python 3.11+ and a PostgreSQL database to introspect.

## Quickstart

```bash
# Point sowdb at a database (or set PGHOST/PGUSER/PGPASSWORD/PGDATABASE instead of --dsn)
export SOWDB_DSN="postgresql+psycopg://user:password@localhost:5432/mydb"

# See the tables sowdb found and the order it would insert them in
sowdb inspect --dsn "$SOWDB_DSN"

# Generate 100 rows per table, reproducibly, without touching the database
sowdb generate --dsn "$SOWDB_DSN" --rows 100 --seed 42 --dry-run

# Actually insert it
sowdb generate --dsn "$SOWDB_DSN" --rows 100 --seed 42

# Or write portable SQL / CSV instead of inserting directly
sowdb generate --dsn "$SOWDB_DSN" --rows 100 --output sql --out-dir ./out
sowdb generate --dsn "$SOWDB_DSN" --rows 100 --output csv --out-dir ./out
```

Try it against the bundled example schemas:

```bash
createdb sowdb_demo
psql sowdb_demo -f examples/blog.sql
sowdb generate --dsn "postgresql+psycopg://localhost/sowdb_demo" --rows 30 --seed 1
```

## How it works

1. **Introspect** (`sowdb.introspect`) — reflects tables, columns, types,
   nullability, `UNIQUE` constraints, primary keys and foreign keys via
   SQLAlchemy.
2. **Order** (`sowdb.graph`) — builds a dependency graph from foreign keys and
   computes a topological insertion order, so a table is only populated after
   every table it references. Cycles between tables raise a clear,
   actionable error instead of silently producing broken data.
3. **Generate** (`sowdb.generate` + `sowdb.providers`) — for each column,
   picks a value provider: first by column *name* (`email`, `created_at`,
   `price`, ...), falling back to the column's SQL *type*. Foreign keys are
   always resolved to a value already generated for the referenced table.
4. **Write** (`sowdb.writers`) — inserts in batches directly into the
   database, or exports to a portable `.sql` file or per-table CSV files.

## CLI reference

```
sowdb inspect --dsn DSN [--schema SCHEMA] [--include-table TABLE ...]

sowdb generate --dsn DSN
               [--rows N]                  # default 10
               [--rows-for table=N ...]    # per-table override, repeatable
               [--seed N]                  # reproducible generation
               [--batch-size N]            # default 500
               [--output db|sql|csv]       # default db
               [--out-dir PATH]            # required for sql/csv
               [--dry-run]                 # generate + validate, write nothing
               [--schema SCHEMA]
               [--include-table TABLE ...]
               [--locale LOCALE]           # Faker locale, default en_US
               [--verbose]
```

`sowdb` can also be used as a library — `cli.py` only parses arguments and
delegates to `sowdb.introspect`, `sowdb.graph`, `sowdb.generate` and
`sowdb.writers`, all of which are plain, dependency-free-of-Typer Python.

## Adding a custom value provider

Providers are matched in priority order: higher priority runs first. Register
a new one without touching any core module:

```python
from sowdb.providers import ColumnProvider, GenerationContext, register_provider
from sowdb.introspect import ColumnInfo

@register_provider(priority=100)
class IsbnProvider(ColumnProvider):
    def matches(self, column: ColumnInfo) -> bool:
        return column.name.lower() == "isbn"

    def generate(self, column: ColumnInfo, context: GenerationContext) -> object:
        return context.faker.isbn13()
```

## Known limitations (v0.1)

- **PostgreSQL only.** Other engines are out of scope for this release.
- **`GENERATED ALWAYS AS IDENTITY` columns aren't supported.** Postgres
  rejects explicit inserts into them without `OVERRIDING SYSTEM VALUE`. Use
  `GENERATED BY DEFAULT AS IDENTITY` (or classic `SERIAL`) instead, or
  exclude the table with `--include-table`.
- **Multi-table cycles of `NOT NULL` foreign keys can't be ordered.** `sowdb`
  detects them and tells you which tables are involved; make one of the
  foreign keys nullable or exclude a table to break the cycle.
- **Composite primary keys** are supported (`sowdb` retries until the tuple
  is unique), but **composite `UNIQUE` constraints** spanning more than one
  column are not currently enforced during generation.
- **No statistical distributions.** Values are plausible per-column, not
  correlated across columns (e.g. `city` and `country` won't necessarily
  match).
- The whole generated dataset's primary/unique key values are kept in memory
  for the duration of a run, to guarantee referential integrity.

## Development

```bash
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
pre-commit install

ruff check .
ruff format --check .
mypy --strict src
pytest -m "not integration"       # unit tests, no database needed

docker compose up -d               # starts a throwaway Postgres on :5433
pytest -m integration              # end-to-end tests against a real database
docker compose down
```

See [CONTRIBUTING.md](CONTRIBUTING.md) for more.

## License

MIT — see [LICENSE](LICENSE).
