Skip to content

README.md

Latest commit

 

History

History
206 lines (165 loc) · 8.57 KB

README.md

File metadata and controls

206 lines (165 loc) · 8.57 KB
Raw

Client Compatibility Tests

Tests duckgres against real PostgreSQL client libraries across multiple languages. Each client connects over TLS, runs a shared query catalog, then exercises driver-specific features (DDL/DML, prepared statements, COPY, ORM, etc.).

Results are collected by a central HTTP server, stored in DuckDB, and exported as a timestamped .duckdb database for analysis.

Prerequisites

  • Docker and Docker Compose v2
  • just command runner
  • (Optional) DuckDB CLI for querying results

Quick Start

# Run all clients and produce a report
just all

# Query the results database
just query-last-run

Commands

Command Description
just all Build and run all clients, produce report
just build Build all container images
just rebuild Rebuild all container images from scratch
just <client> Run a single client standalone (no report)
just down Tear down containers and volumes
just clean Remove the results/ directory
just overview One-line per-client overview with real/stub/client-specific coverage
just summary Pass/fail counts by client and suite
just client-results <client> Full results for a specific client
just view-last-run Print the full results table
just query-last-run Open the latest results database in DuckDB CLI
just web Open the latest results database in the DuckDB web UI

Available single-client targets: psycopg, pgx, psql, jdbc, tokio-postgres, node-postgres, sqlalchemy, dbt, pgadmin.

Clients

Client Language Library Image Base
psycopg Python 3.12 psycopg2-binary python:3.12-slim
pgx Go 1.24 jackc/pgx/v5 golang:1.24
psql Bash psql CLI postgres:17
jdbc Java 17 org.postgresql:postgresql 42.7 maven:3-eclipse-temurin-17
tokio-postgres Rust 1.84 tokio-postgres 0.7 rust:1.84-bookworm
node-postgres Node.js 22 pg 8.x node:22-bookworm-slim
sqlalchemy Python 3.12 SQLAlchemy 2.x + psycopg2 python:3.12-slim
dbt Python 3.12 dbt-postgres (psycopg2) python:3.12-slim
pgadmin Python 3.12 pgAdmin 4 query replay via psycopg2 python:3.12-slim

Architecture

                                    +-----------------+
                                    | results-gatherer|
                                    | (HTTP + DuckDB) |
                                    +-------+---------+
                                            |
            POST /result                    | POST /shutdown
            (per test)                      | (from justfile)
                |                           |
    +-----------+---------------------------+-----------+
    |           |           |           |           |   |
 psycopg      pgx        psql       jdbc  tokio-postgres  sqlalchemy
    |           |           |           |           |   |
    +-----------+-----------+-----------+-----------+---+
                            |
                      compat-duckgres
                  (control-plane mode)

Lifecycle

  1. docker compose up -d starts duckgres, the results gatherer, and all clients
  2. Duckgres and the results gatherer expose healthchecks; clients block on depends_on: service_healthy
  3. Each client runs its tests, POSTing results to the gatherer via POST /result
  4. The justfile runs docker wait on all client containers
  5. Once all clients have exited (pass or crash), the justfile POSTs /shutdown to the gatherer
  6. The gatherer prints the report, exports results, and exits
  7. docker compose down -v tears everything down

This means the gatherer never hangs waiting for clients that crashed without reporting.

Standalone Mode

Running a single client (just psycopg, etc.) uses docker compose run, which starts only duckgres and the target client. The results gatherer is not involved; the client just prints to stdout. The HTTP reporting calls are fire-and-forget, so they silently fail when the gatherer isn't running.

Test Structure

Every client runs two categories of tests:

Shared Queries (queries.yaml)

A YAML catalog of 91 queries across 6 suites that every client executes. A query passes if it executes without error (row counts are reported but not validated).

Queries may be tagged stub: true to indicate the underlying function or view returns a hardcoded dummy value (empty string, NULL, 0, false, or empty result set) rather than meaningful data. This distinguishes "won't crash your client" from "returns useful information" in the results.

Suite Count Purpose
catalog_views 7 Core pg_catalog views (pg_database, pg_namespace, pg_type, etc.)
catalog_funcs 30 PostgreSQL functions (format_type, version(), pg_get_indexdef, size functions, etc.)
info_schema 4 information_schema.tables, .columns, .schemata
catalog_joins 5 Multi-table joins that real tools emit (psql \dt, \dn, \l queries)
catalog_stubs 16 Stub views that must exist but return empty (pg_matviews, pg_policy, etc.)
dbeaver_introspection 29 DBeaver metadata discovery queries (catalog and function coverage seen on connect)

Client-Specific Suites

Each client tests driver-specific features beyond the shared catalog:

Client Extra Suites
psycopg connection, ddl_dml, cursor_metadata, executemany, dict_cursor, copy
pgx connection, ddl_dml, batch (pgx.Batch / SendBatch)
psql psql_commands (\dt, \dn, \l, \di, \dv, \df), ddl_dml, copy
jdbc connection, ddl_dml, metadata (DatabaseMetaData), batch, resultset_metadata, metabase_smoke (version() / worker_version() repetition)
tokio-postgres connection, ddl_dml, prepared
node-postgres connection, ddl_dml, prepared, result_metadata
sqlalchemy connection, core_ddl_dml, orm, inspection, raw_params
dbt dbt_lifecycle (dbt debug, dbt run, dbt test, dbt docs generate)
pgadmin pgadmin_connect (catalog and browser-tree introspection query replay)

Results

After just all, the results/ directory contains:

results/
├── results_20250225_143012.json      # All results as JSON
├── results_20250225_143012.duckdb    # Queryable DuckDB database
├── psycopg/
│   ├── stdout.log
│   └── stderr.log
├── pgx/
│   ├── stdout.log
│   └── stderr.log
└── ...                               # Per-client log directories

Results Database Schema

-- Query catalog (from queries.yaml)
CREATE TABLE queries (
    suite VARCHAR,
    name  VARCHAR,
    sql   VARCHAR,
    stub  BOOLEAN DEFAULT false  -- true = hardcoded dummy return value
);

-- Test outcomes
CREATE TABLE results (
    client    VARCHAR,    -- e.g. 'psycopg', 'jdbc'
    suite     VARCHAR,    -- e.g. 'catalog_views', 'ddl_dml'
    test_name VARCHAR,
    status    VARCHAR,    -- 'pass' or 'fail'
    detail    VARCHAR,    -- row count or error message
    ts        TIMESTAMP
);

-- Coverage: LEFT JOIN of queries to results (shows untested queries)
CREATE VIEW coverage AS
SELECT q.suite, q.name, q.sql, q.stub,
       r.client, r.status, r.detail, r.ts
FROM queries q
LEFT JOIN results r ON q.suite = r.suite AND q.name = r.test_name;

Example Queries

-- Pass/fail summary by client
SELECT client,
       count(*) FILTER (WHERE status = 'pass') AS pass,
       count(*) FILTER (WHERE status = 'fail') AS fail
FROM results GROUP BY client;

-- All failures with details
SELECT client, suite, test_name, detail
FROM results WHERE status = 'fail'
ORDER BY client, suite;

-- Queries no client has tested
SELECT suite, name FROM coverage
WHERE client IS NULL;

Adding a New Client

  1. Create a directory under scripts/client-compat/clients/ (e.g. clients/dotnet/)
  2. Add a Dockerfile that:
    • Copies entrypoint.sh and queries.yaml
    • Sets ENTRYPOINT ["/entrypoint.sh"] and CMD to run your test script
  3. Implement the test script:
    • Wait for duckgres (poll with SELECT 1, retry up to 30 times)
    • Load queries.yaml and run each query, reporting via POST $RESULTS_URL/result
    • Run any driver-specific tests
    • Connect with sslmode=require and accept self-signed certs
  4. Add a service block in docker-compose.yml (copy an existing client, update names/paths)
  5. Add the container name to the clients variable in justfile
  6. Add a standalone just target