Module pgwire 

A PostgreSQL wire-protocol endpoint for NEDB — reads and writes.

What this is

A front door that speaks the PostgreSQL v3 wire protocol well enough that tools built for Postgres — psql, DBeaver, Metabase, Grafana, psycopg, any libpq client — can use a NEDB store with ordinary SQL. A documented subset of SQL is translated to NQL and to engine writes; everything else is refused with an error naming exactly what was not understood.

It is not a claim of Postgres parity. It is a claim that the SQL people actually type works, and that the boundary is stated rather than discovered.

Why writes belong here

The first cut of this module was read-only, on the reasoning that a NEDB write carries caused_by, valid-time bounds and idempotency, and none of that has a natural SQL spelling. That reasoning was wrong, and looking at the mapping is what made it obvious:

SQL	NEDB	and therefore
INSERT	a put	—
UPDATE … WHERE	a NEW VERSION of each match	the prior value stays readable
DELETE … WHERE	a tombstone	the deleted row stays in history

NEDB is append-only, so an UPDATE is already a versioned write and a DELETE is already a tombstone. Nothing is bent to fit. The consequence is the point of the whole endpoint:

UPDATE orders SET total = 999 WHERE _id = 'o1';
SELECT total FROM orders WHERE _id = 'o1';                  -- 999
SELECT total FROM orders AS OF SYSTEM TIME 0 WHERE _id = 'o1';  -- 120

Run the SQL you would run against Postgres, and the tamper-evident history is free. No triggers, no audit table, no application code.

Provenance is reachable too: _caused_by, _valid_from and _valid_to are reserved INSERT columns, lifted out of the payload into the write itself.

Writes are ON by default — that is the parity position. Set NEDBD_PG_READ_ONLY=1 for the deployment where this door must never mutate anything.

Supported SQL

SELECT * | col [, col]* | COUNT(*) | <agg>(col)
  FROM <collection>
  [ AS OF SYSTEM TIME <seq> ]     -- bridges to NQL's AS OF
  [ WHERE <predicate> ]           -- the full NQL predicate surface
  [ GROUP BY <col> ] [ HAVING <predicate> ]
  [ ORDER BY <col> [ASC|DESC] (, ...) ] [ LIMIT <n> ] [ OFFSET <n> ]

INSERT INTO <collection> (c1, c2) VALUES (v1, v2), (…) [RETURNING …]
UPDATE <collection> SET c = v [, …] [WHERE <predicate>] [RETURNING …]
DELETE FROM <collection> [WHERE <predicate>] [RETURNING …]

Single-quoted SQL literals are rewritten to NQL’s double-quoted form and <> to !=. Column projection is applied here, after NQL returns whole documents, because NQL is FROM-first and has no projection clause.

That translation serves user collections. Statements that read the catalogue (pg_catalog.*, information_schema.*) go instead to the real SQL evaluator in sqlselect — joins, subqueries, EXISTS, ARRAY(...), ANY/ALL, UNION, derived tables, LATERAL, aggregates, CASE, scalar functions — because that is what psql’s \d family is written in. Every psql 17 backslash command that can succeed against an empty-of-features Postgres exits 0 here, verified by driving the real binary (tests/test_psql_introspection.py).

Not supported on the user-collection path, each refused by name: JOIN, subqueries, CTEs, window functions, DDL, TRUNCATE, GRANT/REVOKE. INSERT requires an explicit column list, because NEDB is schemaless and there is no declared column order to infer.

Protocol coverage

Both protocols are implemented:

• the simple query protocol (Q) — what psql and libpq’s PQexec use, and therefore psycopg2, which interpolates parameters client-side;
• the extended query protocol (Parse/Bind/Describe/Execute/ Close/Sync/Flush) — what psycopg3, asyncpg and the JDBC driver use for every parameterised statement. Without it those three could not run a single query, so “psql works” was a long way from “your framework works”.

Parameters arrive in text and binary format, prepared statements and portals are per-connection, and a row-capped Execute suspends its portal (PortalSuspended) so a JDBC setFetchSize pages instead of stalling.

Parameter typing in a store with no schema

The extended protocol needs types for $1..$n, which a relational server reads out of its catalogue. NEDB has none — so the types are sampled from the documents already stored, and the stored data is the schema. Where a placeholder sits in a clause rather than beside a column (AS OF SYSTEM TIME $1, LIMIT $1) the grammar supplies the type instead, and an aggregate column is typed from what the aggregate means: a COUNT is an integer, an AVG fractional.

This is not polish. A client that declares its own parameter types (psycopg3, JDBC) is believed and only its unspecified slots are inferred — but asyncpg declares none, asks, and then refuses the call client-side if the answer is wrong. Advertising “text” for everything does not degrade gracefully there; it fails with expected str, got int before a query is ever sent.

SSL is declined (N), so connections are cleartext — hence the loopback default.

Authentication mirrors the HTTP surface: with NEDBD_TOKEN set the password must equal it; otherwise any connection is accepted.

Still outside the boundary, and refused by name: SQL-level cursors (DECLARE/FETCH), window functions, set operations on the user-collection path, and binary result format for a column whose stored values disagree about their type across documents.

What an ORM needs, and what it cost to learn

Speaking psql is not speaking to a framework, and the difference was three defects deep. SQLAlchemy could not CONNECT (its dialect opens with select pg_catalog.version(), which a table of exact spellings missed); its reflection needed GROUP BY and array_agg(x ORDER BY y); and a QUALIFIED column in a WHERE clause returned ZERO ROWS — silently — because NQL looks a field up flat and no document has a field named orders.status. Every ORM qualifies its predicates, so every filtered query lied.

None of that was visible to psql, which is why tests/pgwire_suite.py drives asyncpg, SQLAlchemy and node-postgres against a live daemon on every push.

Structs

Col

One output column: the key to read from the row, and the name to show.

Executed

One executed statement, held apart from any wire encoding.

InsertRow

One row of an INSERT: an explicit id when the statement supplied one, the document body, and optional provenance lifted out of reserved columns.

Enums

Stmt

What a translated statement asks for.

Traits

DbResolver

How a caller resolves a database name to an open Db.

Functions

encode_result

A complete SELECT response: rows plus CommandComplete("SELECT n").

encode_rows

Encode just the rows: T followed by one D per row, and NO CommandComplete.

oid_for_column

The type of col across EVERY row in the result, not just the first.

run

Bind and serve the Postgres read endpoint until the process exits.

translate

Translate one SQL statement into something executable, or explain why not.

version_string

The version() string, for the SQL engine’s version() function.