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.

Not supported, 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), pg_catalog introspection (so \dt and DBeaver’s schema browser stay empty), and binary result format for a column whose stored values disagree about their type across documents.

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.

run

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

translate

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