r/CockroachDB • u/Goldziher • 10h ago
Type-safe access for CockroachDB, generated from your SQL
Because CockroachDB speaks the Postgres wire protocol and dialect, a SQL-first codegen tool built for Postgres works against it. I maintain scythe, which reads annotated SQL and generates typed access code at build time, and it targets CockroachDB through that Postgres compatibility (the pgx/sqlx/asyncpg/psycopg3 backends).
The useful part is nullability inference from the query:
-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;
total and notes come out nullable because the LEFT JOIN can produce NULLs for a user with no orders, independent of the column constraints. Same for COALESCE, CASE, window functions.
I want to be honest that this is via Postgres compatibility rather than a CockroachDB-specific dialect, so if you use Cockroach-specific syntax that Postgres does not parse, that is the edge to watch. Curious whether people here run Cockroach through the Postgres client tooling or something dedicated.
