Playground¶

The playground/ directory is a local development scratchpad for manually testing pgcraft against a real PostgreSQL database. It is not part of the distributed package — it exists purely for iterative development and experimentation.

It includes:

A SQLAlchemy model (models.py) you can extend freely
An Alembic setup for managing the local database schema
A Justfile with commands wrapping common database operations

Prerequisites¶

You need a running PostgreSQL server. The easiest options are:

Docker (recommended)

docker run -d \
    --name pgcraft-db \
    -p 5432:5432 \
    -e POSTGRES_DB=pgcraft \
    -e POSTGRES_HOST_AUTH_METHOD=trust \
    postgres:17

System install (Linux — Ubuntu/Debian)

See the PostgreSQL downloads page for the most up-to-date instructions for your distribution. On Ubuntu/Debian:

sudo apt install -y postgresql
sudo systemctl start postgresql
sudo systemctl enable postgresql   # start automatically on boot

Then create a local superuser so you can connect without switching to the postgres system user:

sudo -u postgres createuser --superuser $USER

By default, PostgreSQL requires a password for TCP connections. To avoid this, connect via a Unix socket instead — peer auth is passwordless for your local user with no extra configuration:

Warning

Passwordless authentication is only appropriate for local development. Production databases should always require credentials.

Configuration¶

Copy the example env and set your database URL:

cp playground/.env.example playground/.env

Edit playground/.env with your connection details:

# System postgres via Unix socket (passwordless, recommended):
DATABASE_URL=postgresql+psycopg:///pgcraft

# Docker or system postgres via TCP with no password:
DATABASE_URL=postgresql+psycopg://localhost/pgcraft

# System postgres with a user/password:
DATABASE_URL=postgresql+psycopg://user:password@localhost/pgcraft

playground/.env is gitignored and will never be committed.

Setup¶

From the playground/ directory, run:

just init

This creates the database (safe to re-run if it already exists) and applies all pending migrations.

Commands¶

All commands are run from the playground/ directory.

just api <*path>: Query the API root: just api
just current: Show current migration state
just db-shell: Open a psql shell using DATABASE_URL
just downgrade <rev>: Downgrade to a specific revision: just downgrade abc123
just fresh-revision <msg>: Reset and generate a fresh revision: just fresh-revision “add users table” [alias: fr]
just history: Show migration history
just init: Create the database and apply all migrations
just migrate: Apply all pending migrations
just migrate-revision <msg>: Apply migrations then generate a new one: just migrate-revision “add users table” [alias: mr]
just reset: Wipe migrations and drop the database [alias: r]
just revision <msg>: Create a new autogenerated migration: just revision “add users table”
just rollback: Rollback the last migration
just serve: Start PostgREST
just wipe-db: Drop and recreate the database, cleaning up non-system roles [alias: wd]
just wipe-migrations: Delete all migration version files [alias: wm]

Typical workflow¶

Edit models.py to add or change a model

Generate a migration:

just revision "add email_verified to users"

Review the generated file in migrations/versions/
Apply it:
```
just migrate
```
Write a script or use db-shell to verify the result:
```
just db-shell
```
If something is wrong, roll back and adjust:
```
just rollback
```

Adding models¶

Define new models in playground/models.py using the pgcraft factory classes:

from pgcraft.factory import PGCraftSimple

users = PGCraftSimple(
    tablename="users",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String),
    ],
)

The playground already has the PostgREST extension enabled. See playground/models.py for the full setup.

Then generate and apply a migration:

just revision "add users table"
just migrate

Alembic autogenerate compares your models against the live database schema, so it will pick up additions, removals, and column changes automatically.

PostgREST¶

The playground includes a PostgREST configuration for testing the generated API. pgcraft automatically creates API views and grants for each factory-created table.

Prerequisites

Install PostgREST from the official releases and ensure it is on your PATH.

Configuration

The playground includes a postgrest.conf that connects as the authenticator role. The password is read from PGRST_DB_PASSWORD in .env.

Starting the server

just serve

Querying the API

# List all resources
just api

# Get all students
just api students

# Filter students
just api "students?name=eq.Alice"

# Embed related resources (e.g. user for each student)
just api "students?select=*,users(*)"

# Or with curl directly
curl -s "http://localhost:3000/students?select=*,users(*)" | python3 -m json.tool

Resource embedding

PostgREST automatically detects foreign key relationships through views. The db-extra-search-path setting in postgrest.conf includes the public and private schemas so that PostgREST can trace FK relationships from the API views back to their base tables.

The embedding name matches the view/table name, not the column name. Use an alias to rename the embedded resource:

# Column is "user_id", but the resource is called "users"
just api "students?select=*,users(*)"

# Rename to "user" with an alias
just api "students?select=*,user:users(*)"

See the PostgREST resource embedding docs for the full query syntax.

pgcraft

Navigation

Related Topics

Versions