Skip to main content

Postgres

Go from an existing Postgres table to a tested data contract in about five minutes: import the schema straight from Postgres, then test the actual data against it. Works with Postgres and Postgres-compatible databases (e.g. RisingWave).

1. Install​

uv tool install --python python3.11 --upgrade 'datacontract-cli[postgres]'

See Installation for pip, pipx, and Docker.

2. Authenticate​

Create a .env file in your working directory (or export the variables):

# .env
DATACONTRACT_POSTGRES_USERNAME=postgres
DATACONTRACT_POSTGRES_PASSWORD=mysecretpassword

The database user needs USAGE on the schema and SELECT on the tables. import and test use the same two variables.

3. Create a contract from your tables​

Import the table metadata directly from the Postgres catalog. This also generates a ready-to-test servers block:

datacontract import postgres \
  --source localhost \
  --database postgres \
  --schema public \
  --table orders \
  --output datacontract.yaml

--source is the host of your Postgres server. Add --port if it doesn't listen on the default 5432, repeat --table for multiple tables, or omit it to import every table in the schema. --schema defaults to public.

Only have a DDL file? datacontract import sql --source orders.sql --dialect postgres works too, but writes a servers block with placeholder values that you have to fill in by hand.

4. Test the actual data​

datacontract test datacontract.yaml
Testing datacontract.yaml
Server: postgres (type=postgres, host=localhost, port=5432, database=postgres, schema=public)
╭────────┬─────────────────────────────────────────────────┬─────────────────┬─────────╮
│ Result │ Check                                           │ Field           │ Details │
├────────┼─────────────────────────────────────────────────┼─────────────────┼─────────┤
│ passed │ Check that field 'order_id' is present          │ orders.order_id │         │
│ passed │ Check that field order_id has no missing values │ orders.order_id │         │
│  ...   │                                                 │                 │         │
╰────────┴─────────────────────────────────────────────────┴─────────────────┴─────────╯
🟢 data contract is valid. Run 24 checks. Took 2.3 seconds.
No database at hand?

The Quickstart tests a public demo contract against a hosted Postgres database — no setup required.

5. Let it catch a violation​

The contract becomes valuable when it detects drift. Tighten an expectation — for example, add a quality rule to a schema in datacontract.yaml:

schema:
  - name: orders
    # ...
    quality:
      - type: sql
        description: No order has a negative total
        query: SELECT COUNT(*) FROM orders WHERE order_total < 0
        mustBe: 0

Run datacontract test datacontract.yaml again: every violation is listed as an error, and the command exits with code 1 — ready for CI/CD scheduling so you catch drift before your consumers do.

Server reference​

Connection details live in the contract's servers block; host, port, database, and schema come from there:

servers:
  - server: postgres
    type: postgres
    host: localhost
    port: 5432
    database: postgres
    schema: public

Reference​

All authentication options and the data type mappings: Postgres Reference.

Troubleshooting​

  • password authentication failed — check the two environment variables above; note that values from an already-set shell variable take precedence over .env.
  • connection refused — host/port in the servers block are wrong, or the database isn't reachable from your machine (VPN, firewall, pg_hba.conf).
  • relation does not exist — the schema in the servers block doesn't match where the table lives, or the user lacks USAGE/SELECT grants.
  • The import finds no tables — --schema is case-sensitive and must match the schema as stored in the catalog.