hum3/go-postgres

Watch

Fork 0

Implement pgTAP subset for SQL-level testing of go-postgres #2

New issue

Closed

opened 2026-08-07 14:22:04 +00:00 by hum3 · 1 comment

hum3 commented

2026-08-07 14:22:04 +00:00

Owner

Problem

go-postgres translates PostgreSQL syntax to SQLite. There's no SQL-level test framework to verify that the translation behaves correctly. Currently all testing is done from Go, which doesn't exercise the SQL surface the way a user would.

pgTAP is the standard PostgreSQL SQL testing framework (~143 functions), but it requires PL/pgSQL and PostgreSQL extensions — neither available in pglike/SQLite.

Proposal

Implement a small pgTAP-compatible subset as Go-native functions registered in the SQL engine. This gives us SQL-level tests that can run against both go-postgres and real PostgreSQL to verify compatibility.

Minimum useful subset (~20-30 functions)

Infrastructure: plan(), no_plan(), finish(), diag()

Core assertions: ok(), is(), isnt(), pass(), fail()

Result testing: results_eq(), is_empty()

Schema checks: has_table(), has_column(), col_type_is(), col_not_null()

Skip/todo: skip(), todo(), todo_end()

Implementation approach

Schema introspection functions (has_table, has_column, etc.) are just SELECT queries against information_schema — straightforward
Core assertions need session state (test counter, results) — implement as Go-registered functions with in-memory state
Skip cmp_ok() and throws_ok() initially — they require dynamic SQL (EXECUTE)
Test files should be plain .sql that run identically against pglike and real PostgreSQL

What this does NOT include

Full pgTAP (~300 functions with overloads)
Dynamic SQL functions (cmp_ok, throws_ok)
Ownership/privilege checks (not relevant for pglike)
PL/pgSQL implementation

Value

Verifies go-postgres grammar and behaviour at the SQL level
Same test files run against real PostgreSQL for compatibility checking
TAP output is plain text, parseable by standard TAP consumers

CockroachDB note

CockroachDB has no equivalent SQL testing framework. If go-postgres supports pgTAP subset, the same tests could potentially validate CockroachDB compatibility too (CockroachDB has partial PL/pgSQL since v23.2 but lacks EXECUTE).

Migrated from Codeberg: originally #2, opened 2026-03-17.

## Problem go-postgres translates PostgreSQL syntax to SQLite. There's no SQL-level test framework to verify that the translation behaves correctly. Currently all testing is done from Go, which doesn't exercise the SQL surface the way a user would. [pgTAP](https://pgtap.org/) is the standard PostgreSQL SQL testing framework (~143 functions), but it requires PL/pgSQL and PostgreSQL extensions — neither available in pglike/SQLite. ## Proposal Implement a small pgTAP-compatible subset as Go-native functions registered in the SQL engine. This gives us SQL-level tests that can run against both go-postgres **and** real PostgreSQL to verify compatibility. ### Minimum useful subset (~20-30 functions) **Infrastructure:** `plan()`, `no_plan()`, `finish()`, `diag()` **Core assertions:** `ok()`, `is()`, `isnt()`, `pass()`, `fail()` **Result testing:** `results_eq()`, `is_empty()` **Schema checks:** `has_table()`, `has_column()`, `col_type_is()`, `col_not_null()` **Skip/todo:** `skip()`, `todo()`, `todo_end()` ### Implementation approach - Schema introspection functions (`has_table`, `has_column`, etc.) are just `SELECT` queries against `information_schema` — straightforward - Core assertions need session state (test counter, results) — implement as Go-registered functions with in-memory state - Skip `cmp_ok()` and `throws_ok()` initially — they require dynamic SQL (`EXECUTE`) - Test files should be plain `.sql` that run identically against pglike and real PostgreSQL ### What this does NOT include - Full pgTAP (~300 functions with overloads) - Dynamic SQL functions (`cmp_ok`, `throws_ok`) - Ownership/privilege checks (not relevant for pglike) - PL/pgSQL implementation ## Value - Verifies go-postgres grammar and behaviour at the SQL level - Same test files run against real PostgreSQL for compatibility checking - TAP output is plain text, parseable by standard TAP consumers ## CockroachDB note CockroachDB has no equivalent SQL testing framework. If go-postgres supports pgTAP subset, the same tests could potentially validate CockroachDB compatibility too (CockroachDB has partial PL/pgSQL since v23.2 but lacks `EXECUTE`). --- *Migrated from Codeberg: originally [#2](https://codeberg.org/hum3/go-postgres/issues/2), opened 2026-03-17.*

hum3 commented

2026-08-07 14:22:05 +00:00

Author

Owner

Update: v0.4.0 changes the picture

go-postgres switched to ncruces/sqlite in v0.4.0, which supports registering custom Go scalar and aggregate functions directly in SQLite. This makes the implementation approach much more concrete:

Revised implementation approach

Register pgTAP functions as custom SQLite functions via ncruces — plan(), ok(), is(), diag() etc. become Go functions callable from SQL
Session state (test counter, results, todo stack) lives in a Go struct tied to the connection — no need for temp tables or sequences
Schema introspection (has_table, has_column) queries sqlite_master but presents pgTAP-compatible signatures
TAP output returned as query results, same format as real pgTAP

What's now easier

Custom function registration is a first-class ncruces feature — no hacks needed
WASM support (v0.4.2) means pgTAP subset would also work in browser-based testing
shopspring/decimal support (v0.3.1) means NUMERIC type handling is already solid

Remaining considerations

results_eq() needs to execute two queries and compare — may need to be a Go-side helper rather than a pure SQL function
Test files should still target pgTAP-compatible SQL so they run against real PostgreSQL too
Priority: core assertions first (plan, ok, is, isnt, finish, diag), schema checks second

(hum3, 2026-03-18)

## Update: v0.4.0 changes the picture go-postgres switched to **ncruces/sqlite** in v0.4.0, which supports registering custom Go scalar and aggregate functions directly in SQLite. This makes the implementation approach much more concrete: ### Revised implementation approach 1. **Register pgTAP functions as custom SQLite functions** via ncruces — `plan()`, `ok()`, `is()`, `diag()` etc. become Go functions callable from SQL 2. **Session state** (test counter, results, todo stack) lives in a Go struct tied to the connection — no need for temp tables or sequences 3. **Schema introspection** (`has_table`, `has_column`) queries `sqlite_master` but presents pgTAP-compatible signatures 4. **TAP output** returned as query results, same format as real pgTAP ### What's now easier - Custom function registration is a first-class ncruces feature — no hacks needed - WASM support (v0.4.2) means pgTAP subset would also work in browser-based testing - shopspring/decimal support (v0.3.1) means NUMERIC type handling is already solid ### Remaining considerations - `results_eq()` needs to execute two queries and compare — may need to be a Go-side helper rather than a pure SQL function - Test files should still target pgTAP-compatible SQL so they run against real PostgreSQL too - Priority: core assertions first (`plan`, `ok`, `is`, `isnt`, `finish`, `diag`), schema checks second *(hum3, 2026-03-18)*

hum3 closed this issue

2026-08-07 14:22:05 +00:00

hum3 referenced this issue

2026-08-07 14:22:10 +00:00

Dual-target TestCompat pattern: run same SQL against pglike and real PostgreSQL #5

No labels

OnDemand

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

hum3/go-postgres#2

No description provided.

Rows
Columns