HN Explorer

Show HN: Pg-typesafe – Strongly typed queries for PostgreSQL and TypeScript (github.com)

69 points by n_e ↗ HN
Throughout my career, I tried many tools to query PostgreSQL, and in the end, concluded that for what I do, the simplest is almost always the best: raw SQL queries.

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.

18 comments

[ 2.8 ms ] story [ 36.7 ms ] thread
barishnamazov
How is this different than kysely + kysely-codegen (or hand-made types)?
semiquaver
How is this different from sqlc with sqlc-gen-typescript?
dbbk
Just use Drizzle
afidrya
Is there a way to make this work with https://github.com/porsager/postgres ? It's very close to raw SQL but with safe substitutions. I use it with Zod, but it would be great to have the types derived automatically.
noduerme
Somewhat off topic, as someone who hasn't used PostgreSQL and only has experience with mysql/MariaDB... I've never liked writing queries with numbered parameters from an array with placeholders like $1 in this example. I find them much easier to read and debug when I pass them with string keys, basically:

`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?

WorldMaker
A sometimes easier solution now in TS/JS is to use a simple template interpolation function (template tag) to inline the parameters and let the library autonumber them.

    const x = 42
    const id = 'example'
    const { rows } = pgquery`Update t set x = ${x} where id = ${id}`
The pgquery function can convert that into the $1 and $2 that Postgres expects, but the source code is a little easier to read and has named parameters.

Of course, it potentially makes debugging the query from the database side a little harder because the query itself in its running form is still going to show the $1 and $2 placeholders so you'd have to count "holes" to figure which is which when trying to grep which source line is generating that query. I know that's why some frown on this template-based auto-placeholders because they want the code to better resemble the queries as they run in Postgres.

(Also yeah, it might be nice if Postgres also supported named placeholders like some of the other SQL databases do.)

johnfn
Seems very cool, if it works :) My suggestion here would be to provide a TS playground environment where I can inspect and poke around to see that the types really do work. I'd also love to see some more complex queries with joins, etc.
MuffinFlavored
any reason why you did

    const { rows } = client.query(
      "select id, name, last_modified from tbl where id = $1",
      [42],
    );
instead of

    const { rows } = client.query(
      "select id, name, last_modified from tbl where id = :id",
      { id: 42 },
    );
netghost
Thank you. This looks like a nice improvement on pgtyped, and another good option.

I'm curious if there are any good patterns for dealing with dynamic query building or composing queries?

kristiandupont
Very nice! I am the author of Kanel* which generates types for each table, view, etc. I tend to use it together with Knex which gives me strong type safety from simple selects and updates and sporadic type safety from complex queries with joins etc.

The advantage to your approach (I guess) is increased type safety for complex queries, trading off the loss of "fundamental" types with, say, branded ID types? I guess the two approaches are quite complementary, perhaps I should try that.

* https://github.com/kristiandupont/kanel

retropragma
The lack of composability makes this a hard no for me
blue_pants
How do you guys solve the problem of conforming DB schema to TS interfaces (or vice versa depending on what you like)?

Do you manually keep them in-sync (that's what I'm leaning into as the most practical solution)? Do you introspect the DB schema? Or maybe use something like Drizzle which autogenerates sql migration to keep the db schema in-sync