Posted by n_e 7 hours ago
Until now, I typed the results manually and relied on tests to catch problems. While this is OK in e.g., GoLang, it is quite annoying in TypeScript. First, because of the more powerful type system (it's easier to guess that updated_at is a date than it is to guess whether it's nullable or not), second, because of idiosyncrasies (INT4s are deserialised as JS numbers, but INT8s are deserialised as strings).
So I wrote pg-typesafe, with the goal of it being the less burdensome: you call queries exactly the same way as you would call node-pg, and they are fully typed.
It's very new, but I'm already using it in a large-ish project, where it found several bugs and footguns, and also allowed me to remove many manual type definitions.
`UPDATE t SET x=:x WHERE 1` `{x:42}`
I found that the original node-mysql didn't even allow this, so I wrote my own parser on top of it. But I don't see this style of binding used in examples very often. Is it frowned upon for some reason?
I.e.
Query("select * from MyTable where Id = @Id", myEntity)
The idiom is to use anonymous objects which you can new up inline like “new { id, age = 5 }”, where id is an existing variable that will automatically lend its name. So it’s pretty concise.
The syntax is Sql Server native (which supports named params at the protocol level), but the Npgsql dat provider converts it to PG’s positional system automatically.
In another style, postgres.js uses calls such as sql`select * from t where id = ${variable}` (which is safe because it's a tagged template, not string interpolation).
It would be quite easy to extract the queries to compute the types, but TypeScript doesn't handle tagged template literals well enough to link the query passed to the sql`` template to the return type.
I see they use the same global approach as pg-typed (asking for a ParameterDescription / RowDescription, which aren't usually exposed by the PG drivers), but there are interesting differences in the details. Also this made me realise that I could also type enums automatically.
I'm curious if there are any good patterns for dealing with dynamic query building or composing queries?
For now, I type these manually, which is acceptable for my usage as they are pretty rare compared to static queries.
I've used kysely before creating pg-typesafe, and came to the conclusion that writing SQL directly is more convenient.
A query builder works well for simple cases (db.selectFrom("t").where("id","=","1") looks a lot like the equivalent SQL), however, for more complicated queries it all falls apart. I often had to look at the docs to find how to translate some predicate from SQL to the required idiom. Also, I don't think kysely can automatically infer the return type of PostgreSQL functions, while pg-typed does (it asks PostgreSQL for it).
Regarding sqlc in general, it is focused on having the SQL queries in .sql files, while pg-typed is focused on having the queries inline (though I plan to add .sql file support). I like the latter approach better, as for small queries used in only one place, it is a little cumbersome to add them to a different file and find a name for them.
const { rows } = client.query(
"select id, name, last_modified from tbl where id = $1",
[42],
);
const { rows } = client.query(
"select id, name, last_modified from tbl where id = :id",
{ id: 42 },
);