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?
This is basically how we do it in .NET. With Dapper it’s particularly neat sometimes because you can just pass an object that you were using anyway, and it will match parameter names to its properties case-insensitively.
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.
PostgreSQL uses the format $1, $2 in the protocol, so I think it's just that nobody has bothered to implement named parameters in clients.
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).
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.
Unfortunately I haven't found a way to make it work.
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.
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.
Kysely is a query builder: you build queries by calling javascript functions, while with pg-typesafe you write SQL directly.
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).
I didn't look too much into sqlc-gen-typescript because the project looks abandoned (no commits in 2 years, many open PRs).
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.
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?
This is basically how we do it in .NET. With Dapper it’s particularly neat sometimes because you can just pass an object that you were using anyway, and it will match parameter names to its properties case-insensitively.
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.
PostgreSQL uses the format $1, $2 in the protocol, so I think it's just that nobody has bothered to implement named parameters in clients.
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).
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.
Unfortunately I haven't found a way to make it work.
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.
If you're into Haskell prior art, there's postgresql-typed https://hackage.haskell.org/package/postgresql-typed-0.6.2.5... where you write queries, it safely(?) puts in your variables, and you automatically get back typed values.
Very interesting, thanks!
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.
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.
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?
I haven’t found a good way to handle dynamic queries in pg-typesafe yet.
For now, I type these manually, which is acceptable for my usage as they are pretty rare compared to static queries.
How is this different than kysely + kysely-codegen (or hand-made types)?
Kysely is a query builder: you build queries by calling javascript functions, while with pg-typesafe you write SQL directly.
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).
It only changes the types; not the code.
How is this different from sqlc with sqlc-gen-typescript?
I didn't look too much into sqlc-gen-typescript because the project looks abandoned (no commits in 2 years, many open PRs).
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.
any reason why you did
instead ofThat is the way node-postgres works. pg-typesafe adds type safety but doesn’t change the node-postgres methods
Just use Drizzle