Postgres Datastore

PostgreSQL datastore and model type for swamp. Swamp model schemas (Zod) map directly to PostgreSQL table schemas (DDL) with native column types. Every data mutation is automatically versioned using SQL:2011 SYSTEM VERSIONING via the periods extension.

Quick Start

1. Start PostgreSQL with the periods extension:

  -e POSTGRES_USER=swamp -e POSTGRES_PASSWORD=swamp -e POSTGRES_DB=swamp \
  -p 5432:5432 \
  pgxn/pgxn-tools:latest \
  bash -c "docker-entrypoint.sh postgres & sleep 3 && psql -U swamp -d swamp -c 'CREATE EXTENSION IF NOT EXISTS periods;' && wait"

2. Configure swamp to use the datastore:

  type: "@svendowideit/postgres-datastore"
  config:
    connectionString: "postgres://swamp:swamp@localhost:5432/swamp"
    schema: "swamp"
    ssl: "disable"

3. Verify it's healthy: bash swamp datastore status # Expected: healthy: true, periods: available 4. Create a model and a versioned table:

  --global-args '{"connectionString":"postgres://swamp:swamp@localhost:5432/swamp","schema":"swamp"}'

swamp model method run servers create --args '{
  "tableName": "servers",
  "schema": {
    "id": "string.uuid",
    "hostname": "string",
    "ip": "string",
    "region": {"type": "string", "optional": true},
    "tags": "json",
    "created_at": "string.datetime"
  }
}' ```
**5. Insert, query, and version data:**
```bash # Insert rows swamp model method run servers run --args '{
  "tableName": "servers", "operation": "insert",
  "data": [
    {"id":"a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11","hostname":"web-01","ip":"10.0.1.10","region":"us-east-1","tags":["production"],"created_at":"2026-01-15T10:00:00Z"}
  ]
}'
# Update (creates a new version automatically) swamp model method run servers run --args '{
  "tableName": "servers", "operation": "update",
  "data": [{"id":"a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11","region":"eu-west-1"}]
}'
# Query current data swamp model method run servers query --args '{"tableName":"servers"}'
# List versions swamp model method run servers list_versions --args '{"tableName":"servers"}'
# Query historical data at a specific version swamp model method run servers query --args '{"tableName":"servers","versionId":"<uuid-from-list_versions>"}' ```
**6. Import an existing PostgreSQL table:**
```bash swamp model method run servers import_table --args '{
  "sourceSchema": "public",
  "sourceTable": "legacy_inventory",
  "mode": "readonly"
}' ```
## What You Get
Each swamp model becomes a typed PostgreSQL table with native column types (UUID, TEXT, BIGINT, BOOLEAN, TIMESTAMPTZ, JSONB, etc.) and automatic version history via a companion `_history` table. You can query your data directly with any PostgreSQL client:
```sql SELECT * FROM swamp.servers;                    -- current rows SELECT * FROM swamp.servers__as_of('2026-01-15 10:02:00+00');  -- point-in-time SELECT * FROM swamp.servers_history WHERE id = '...';          -- full audit trail SELECT * FROM swamp._versions WHERE table_name = 'servers';    -- version log ```
## Prerequisites
- PostgreSQL 15+ with the `periods` extension installed - The datastore auto-creates the required schema and installs the extension
  on first use (requires CREATE EXTENSION privileges)

See the README for full documentation on schema migration, backup/restore, security, upgrades, and troubleshooting.