Everything but the answer
SydraDB, my time-series database in Zig, and the week I taught it to pass for Postgres.
I started SydraDB with a sensible plan: a small time-series database in Zig, one static binary, a write-ahead log in front of columnar segments, and a tiny query language of its own called sydraQL. Clients would talk to it over simple wire APIs, HTTP first and Arrow later, and a full SQL surface was on my list of things not to build yet.
The next night I wrote a long plan for passing as PostgreSQL. SydraDB would speak the Postgres wire protocol and look like a Postgres server to the clients that already exist: psql, libpq, JDBC, Npgsql, pgx, Prisma, Django, Rails. Every language and most tools already have a Postgres driver. If SydraDB could pass for Postgres, they'd all work on it without changing a line, and nobody would have to learn sydraQL to try it.
I built three layers of that plan. They're easiest to follow in the order a query meets them, from the client inwards.
The first eight bytes are already a negotiation. A Postgres connection normally opens with a length-prefixed startup packet whose first field is the protocol version, 3.0, sent as 3 << 16. A client that wants TLS sends something else first: an eight-byte packet whose "version" is 80877103, a number the protocol docs say "is chosen to contain 1234 in the most significant 16 bits, and 5679 in the least significant 16 bits." A cancel request is 1234 and 5678. They're fake version numbers, picked so they can never collide with a real one, and the server has to recognise them before it knows what kind of conversation it's in:
pub const ssl_request_code: u32 = 80877103;
pub const cancel_request_code: u32 = 80877102;
pub const protocol_version_3: u32 = 3 << 16; // 3.0 (196608)
SydraDB answers the TLS question with a single byte, N, and the client carries on in plain text. Then come the startup parameters and the message loop, where a simple query arrives as a Q. (Version 3.0 of the protocol is the one described in the Postgres 17 docs; newer servers also speak 3.2.)
Before most tools send a query of their own, they ask the database about itself: what schemas exist, which tables, which columns, of which types. psql's \d does it, and so does every ORM that reads a schema. The questions are queries against Postgres's system catalog, pg_namespace, pg_class, pg_attribute, pg_type, and they expect Postgres's own answers. Types in particular are identified by number. A driver decoding a result row sees a column of type 20 and decodes an int8, sees 1184 and decodes a timestamptz, so a fake Postgres that invents its own numbers gets its values misread. My catalog hands out the real ones:
// excerpt from the type table; every row also carries .namespace = "pg_catalog"
.{ .name = "bool", .oid = 16, .length = 1, .by_value = true, .category = 'B', .array_type_oid = 1000 },
.{ .name = "int8", .oid = 20, .length = 8, .by_value = true, .category = 'N', .array_type_oid = 1016 },
.{ .name = "float8", .oid = 701, .length = 8, .by_value = true, .category = 'N', .array_type_oid = 1022 },
.{ .name = "text", .oid = 25, .length = -1, .by_value = false, .category = 'S', .array_type_oid = 1009 },
.{ .name = "timestamptz", .oid = 1184, .length = 8, .by_value = true, .category = 'D', .array_type_oid = 1185 },
The numbers for my own tables and namespaces had to stay put across restarts too, so that a cast like 'users'::regclass means the same thing tomorrow.
The query itself goes to a translator from SQL to sydraQL. sydraQL puts from first, so most of the work is moving clauses around. I kept the cases in a JSONL file, SQL in and expected sydraQL out:
SELECT name FROM users WHERE id = 42 -> from users where id = 42 select name
INSERT, UPDATE and DELETE came next, with RETURNING, and whatever the translator couldn't handle came back as a Postgres error with a real SQLSTATE.
So by the third night a client could connect (the one in my tests, at least), be told no to TLS, send SELECT name FROM users WHERE id = 42, and have it translated correctly. The extended protocol, which many drivers use for prepared statements, answered "not implemented yet". A simple query, once translated, got this:
try protocol.writeErrorResponse(writer.*, "ERROR", "0A000", "execution bridge not implemented yet");
0A000 is Postgres's code for "feature not supported". SydraDB itself could take writes and answer range queries over its own HTTP API, but nothing behind the Postgres door could run a sydraQL query, because there was no sydraQL to run. Its parser was a file with one comment in it. The language I was translating into existed in exactly one place, the right-hand side of my test cases.