
Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres - alde
https://github.com/adelsz/pgtyped
======
the_duke
There are some similar projects, like sqlx [1] for Rust. My problem with these
is that they don't help to solve the actually hard problems.

While nice to have, preventing bugs with static SQL is usually easy to do by
writing a few tests. Most of the SQL related bugs I have encountered were due
to queries with dynamic/conditional joins, filters and sorting - and almost
every project using a database needs those.

Approaches like this don't help there. That requires heavy-weight solutions
that are more cumbersome to use and need a strong type system, like diesel [2]
(Rust), Slick [3] (Scala) and some similar Haskell projects.

[1] [https://github.com/launchbadge/sqlx](https://github.com/launchbadge/sqlx)

[2] [https://github.com/diesel-rs/diesel](https://github.com/diesel-rs/diesel)

[3] [https://scala-slick.org/](https://scala-slick.org/)

~~~
rubber_duck
> preventing bugs with static SQL is usually easy to do by writing a few tests

I've heard the same argument about TypeScript vs JavaScript and it's something
dynamic typing proponents often say but in practice I find immense value in
having the types autocompleted and checked in the editor - and I've worked
plenty on both sides, current project is substantial RoR codebase, I've worked
with Python and node.js backends on mature codebases. Eventually all these
languages have some sort of static type hinting efforts to improve tooling -
typescript being most successful.

The best thing I saw in this space was F# type providers which didn't require
a pre-build step - the language had a mechanism for writing custom type
providers that would look up the data source during compilation -
unfortunately I didn't get to use it on any real world projects.

~~~
Nelkins
F# also has support for analyzers that can achieve similar functionality in
case you don't want to take a dependency on a type provider.

[https://github.com/Zaid-Ajaj/Npgsql.FSharp.Analyzer](https://github.com/Zaid-
Ajaj/Npgsql.FSharp.Analyzer)

[https://github.com/aaronpowell/FSharp.CosmosDb#fsharpcosmosd...](https://github.com/aaronpowell/FSharp.CosmosDb#fsharpcosmosdbanalyzer-)

------
adriancooney
I really like the unique approach of the annotated SQL files and can
definitely see some use cases where it would be good to declutter the SQL from
the code. For me personally, I'd be hesitant to add another build tool to my
already bloated toolchain. Could create a special Babel-style "import" type
that automatically transforms your code (JIT)? It could remove some of the
friction in adoption (for Babel users at least).

Another one in a similar vein with strict typing and really nice SQL
interpolation for Postgres:
[https://github.com/gajus/slonik](https://github.com/gajus/slonik)

~~~
goofiw
I wonder if the JIT compiling will work asynchronously - from the readme it's
getting the types from the live database schema.

~~~
goofiw
Its actually doing the with what looks like a custom async messaging queu.
Pretty cool.

------
eyelidlessness
I came here to mention a similar approach, which last time I looked was a very
compelling experiment[1], but its original author has actually built out a
real library, Zapatos[2] which looks very very good.

[1]: [https://github.com/jawj/mostly-ormless](https://github.com/jawj/mostly-
ormless)

[2]: [https://jawj.github.io/zapatos/](https://jawj.github.io/zapatos/)

~~~
alde
Looks like Zapatos still requires the user to manually specify param/result
types for custom SQL queries?

~~~
gmac
Zapatos author here. Yes, it does. But for most of what you’d use an ORM for,
you probably won’t need custom queries.

~~~
eyelidlessness
Hey I’m not sure this is the best venue, but I’m trying to make the case for
getting my org off of sequelize, and your library is right in line with my
goals. The hardest sell is going to be publicly visible test coverage. Would
you welcome a dedicated effort from an early adopter to introduce tests?

~~~
gmac
Yes, that would be very welcome. I suggest you keep me in the loop from the
start to make sure we end up with something we’re both happy with.

------
renke1
Looks pretty cool. What I really want though is a library that let's me write
plain SQL queries which are then mapped into nested objects in a smart way
without too much manual work (I know Postgres can do JSON stuff, but the
queries look pretty complicated for what little they actually do).

Say `SELECT * FROM user LEFT JOIN post ON user.id = post.id` would be mapped
to `[{userId: 1, name: renke1, posts: [{postId: 2, title: "foo"]]`.

You probably need some kind of meta data to figure out how tables and thus
objects relate to each other though.

Basically, I want to be able to leverage the full power of modern databases
without being constrainted by typical ORM limitations. Also, I don't need
features like lazy loading, sessions, caches and things like that.

A great advantage is that you can (provided you have some test data) easily
test your queries while you develop a new feature (think IntelliJ IDEA where
you can simply execute an SQL query on the fly).

~~~
timmy-turner
Writing literal SQL in JS would IMO need more tools than just a preprocessor
like you describe.

The few times I tried it (mostly in tests to check that the ORM is working
properly) the #1 thing I was missing is a prettier-plugin that automatically
formats SQL in the same way it currently works for `html` tagged templates.

I completely agree to the 'constrained by ORM' and 'useless features' part
though. Postgres `json_agg` is a godsend and I love to be able to reason over
simple joins and queries.

BTW, my own approach to use `json_agg`, `json_build_object` and json columns
within a typesafe query-building DSL is this:
[https://github.com/hoeck/typesafe-query-
builder](https://github.com/hoeck/typesafe-query-builder)

But its mostly for replacing simple ORM fetches, it wont do complex analytical
queries. For that I'd like to write SQL directly as query-DSLs tend to quickly
stop being usable in that situation.

~~~
cyral
One of my favorite features of WebStorm is the (official) database plugin
which highlights SQL queries inside JS strings AND has autocomplete and
refactoring support that actually uses the live database schema.

~~~
mekster
But unfortunately returned values have no type definition unless manually
provided.

------
conroy
As a maintainer of a similar project[0], it's great to see another entry in
this space.

sqlc currently has great support for Go and experimental support for Kotlin.
I'm planning on adding TypeScript support in the future, so it's great to see
that others in the TypeScript community find this workflow useful.

[0] [https://github.com/kyleconroy/sqlc](https://github.com/kyleconroy/sqlc)

------
Vinnl
Lots of comments here about similar projects in a different language, but the
fact that this targets TypeScript is explicitly what makes it interesting to
me. Using regular Javascript database libraries, even ones that have type
definitions, require a lot of double typing.

I've been relatively satisfied with TypeORM, but one thing that's been a
hurdle for me to some extent is its reliance on experimental decorators, and
the resulting incompatibility with Babel - which in turn makes it harder to
integrate with the wider ecosystem, e.g. Next.js.

As far as I can see on first glance, there's nothing here yet that makes it
incompatible with Babel, so my tip would be to make it an explicit goal to
keep it that way :)

~~~
mikewhy
Opening a can of worms for sure, but the reliance on Babel in the JS community
is not a good thing to me. It's another reason why I prefer TypeScript, as in
TSC, not whatever equivalent babel happens to support.

~~~
lioeters
I do agree that sticking to TSC keeps things a lot simpler, and that should be
the default setup.

Pandora's box has been opened though. Majority of the JS community seem to
have adopted Babel in their workflow.

Even as I question it, I must admit it's pretty sweet to have a transpiler in
the buid toolchain. It frees one from the browser/backward compatibility
question, more or less, and opens up the language to be extensible - for
better or worse.

For example, Babel macros ¹ is an interesting concept, using the language to
extend the language during compile time.

To bring it back to the topic at hand (PgTyped, Typesafe SQL) - one thing that
I'd like to see in TSC is a way to output type definitions (either inlined
metadata or external JSON schema), to be consumed for run-time type checking.
That would "complete the circle" for me.

TypeScript "compiler plugins" seem to be on the roadmap, or at least under
consideration ². Some may see that as opening a proverbial can of worms, down
a similar road to Babel. I wouldn't disagree, but it'd be so useful!

¹ [https://github.com/kentcdodds/babel-plugin-
macros](https://github.com/kentcdodds/babel-plugin-macros)

²
[https://github.com/Microsoft/TypeScript/issues/16607](https://github.com/Microsoft/TypeScript/issues/16607)

~~~
alde
You might find io-ts [1] interesting. It allows to write composable parsers
that do both runtime checks and have correct result types.

Opt-in runtime checks for SQL queries are also on the roadmap for PgTyped. You
are welcome to open an issue to track our progress there if you think this
feature will be useful for you.

[1] [https://github.com/gcanti/io-ts](https://github.com/gcanti/io-ts)

~~~
lioeters
Thank you - yes, I've dabbled with io-ts, though I'm still learning how to
wield its power.

> Opt-in runtime checks for SQL queries

Ah right, I do see in the repo for PgTyped that it's on the roadmap. That does
sound useful!

I'm hoping that one of these days, TypeScript will support a more generic
runtime type check, somehow closer to the language/TSC-level rather than
userland libraries, plugins/transforms, or a parallel compiler/build step.

At the moment, I use a fork of typescript-json-schema ¹ for my purposes.

¹ [https://github.com/YousefED/typescript-json-
schema](https://github.com/YousefED/typescript-json-schema)

------
zelly
Is there anything like this for Rust or C++?

I like the idea of code generation instead of doing the work at runtime (like
in ORMs). This is like making your database schema the IDL spec.

~~~
K0nserv
There's Diesel[0] for Rust which is a full ORM. It's by Siân Griffin[1] who,
as I understand it, is also behind a lot of how rail's ActiveRecord works.

0: [https://diesel.rs/](https://diesel.rs/)

1: [https://twitter.com/sgrif](https://twitter.com/sgrif)

~~~
status_quo69
Just to clarify a bit for other readers since I've worked with diesel for
while, diesel isn't a "full" orm, as there are no real helpers provided to you
outside of "we can map the result of a db query into a struct(s) that you
specify" and some really nice guarantees for compile time queries. Other than
that, your struct is a pretty dumb mapped representation and it's on the
implementers of the application code to provide sugar for better access
patterns. For people coming from something like active record, this is (in my
opinion) closer to Arel than ActiveRecord, or closer to sqlalchemy core than
sqlalchemy orm. As an example, you won't necessarily be able to do
`MyStruct.join(OtherStruct)` and have it magically figure out how to query the
database and map the results out of the box.

~~~
status_quo69
Clarification: compile time query building, not querying. Due to inlining from
the compiler, you can almost entirely construct the query at compile time and
shave it down to a few string concatenations.

------
Allezxandre
My favorite SQL library has been Go-Jet in Go: [https://github.com/go-
jet/jet](https://github.com/go-jet/jet)

It has a different approach from PgTyped, which generates type-safe TypeScript
code from SQL, whereas Go-Jet generates type-safe SQL from Go code

I'd love to try something along the lines of PgTyped and see how the two
solutions compare though

------
hombre_fatal
Almost every top-level comment is someone shilling another project, usually in
Golang as if that's even related.

Let's have some Show HN etiquette.

------
tonyhb
This is similar to sqlc for Golang:
[https://github.com/kyleconroy/sqlc](https://github.com/kyleconroy/sqlc)

If you're looking for the ability to generate type-safe SQL – given you write
SQL correctly – this project is pretty good.

Aalso a fan of SQLBoiler
([https://github.com/volatiletech/sqlboiler](https://github.com/volatiletech/sqlboiler))
for Golang, for simple type safety:

`models.Accounts(models.AccountWhere.ID.EQ(id)).One(ctx, db)`.

Though SQLBoiler breaks with left joins, as it auto-generates your structs and
maps results 1-1 with table definitions. In this case you have to custom type
something, either using sqlc or squirrel.

------
ksashikumar
Looks cool! And the header image looks awesome! Did you use any tool to do it?

~~~
alde
Thanks! Not really, just basic vector shapes and an isometric projection grid
to make sure perspective is right.

------
emanuelez
You might also want to check Kanel out!
[https://github.com/kristiandupont/kanel](https://github.com/kristiandupont/kanel)

------
shrumm
Looks a little like the Typescript equivalent of Xo
([https://github.com/xo/xo](https://github.com/xo/xo)) for Go. Especially with
Go, getting help with some initial scaffolding can be a huge timesaver. I'm
assuming it's a similar gain for Typescript.

~~~
tadasv
A better equivalent in Go is
[https://github.com/kyleconroy/sqlc](https://github.com/kyleconroy/sqlc)

~~~
shrumm
thanks! I'll try this - one of my pet peeves with Xo is handling nullable
types and more advanced types like JSONB required editing the generated code
significantly to make it work. Hopefully sqlc solves that.

------
JBReefer
So basically Dapper TS? I’m very interested!

------
xellisx
I sort of have something like this for PHP and MySQL.
[https://github.com/ellisgl/GeekLab-
GLPDO2](https://github.com/ellisgl/GeekLab-GLPDO2)

------
garrybelka
How is it different from Slonik?
[https://github.com/gajus/slonik](https://github.com/gajus/slonik)

------
fmakunbound
It’s cool, and I don’t fault the author for working on something that
obviously gives him joy, but save yourself a bunch of trouble and avoid this
kind of thing. The queries showcased are the least interesting of the set of
queries you’ll ultimately end up with I’m a mature project.

------
garaboncias2
an other lib with light ORM:
[https://www.npmjs.com/package/pogi](https://www.npmjs.com/package/pogi)

------
SenpaiHurricane
lol. This reminds me Hibernate xml mappings :D

