Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

github.com

Post reply on HN
Print view View original

41–50 of 90 posts

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#41

Post by tonyhb »

This is similar to sqlc for Golang: 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) 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.

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#42

Post by status_quo69 »

K0nserv wrote: post #9
zelly wrote: post #3

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.

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/ 1: https://twitter.com/sgrif

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.

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#43

Post by status_quo69 »

status_quo69 wrote: post #42
K0nserv wrote: post #9

Earlier quoted context omitted.

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/ 1: https://twitter.com/sgrif

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 pr…

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.

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#44

Post by sradman »

the_duke wrote: post #28

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. Appro…

Actually, after my quick scan of the readme, I think pgTyped takes a different approach than the other tools listed. It is a YeSQL-style tool with build-time code generation. The code generation is based on prepared statement metadata rather than table metadata. Like other YeSQL tools, the function name comes from a DocComment annotation in the .sql but the query params and the result set columns come from the prepared statement.

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#45

Post by 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 :)

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#46

Post by soulnothing »

the_duke wrote: post #28

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. Appro…

I'd be curious to hear more about the issues with joins, and dynamic conditionals. I've been working on a type provider lib for sql in kotlin[1].

The join problems I've seen are either the joined table has changed, or altered. The return type of a field may change.

The hard problem I encountered was doing things like json aggs, multiple joins, etc. I'm trying to address this by doing type safe aggregate/join functions. Secondly is query compilation. Compiling the output record of advanced queries into an automatic data class.

1: https://gitlab.com/AnimusDesign/kotlin-frm

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#47

Post by smallnamespace »

Vinnl wrote: post #45

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,…

As usual for Babel, there’s a plugin for that: https://github.com/leonardfactory/babel-plugin-transform-typ...

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#48

Post by gmac »

alde wrote: post #39
eyelidlessness wrote: post #8

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 [2]: https://jawj.github.io/zapatos/

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

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

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#49

Post by gmac »

renke1 wrote: post #10

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,…

Zapatos can generate the hairy JSON stuff for you, including lateral joins that are equivalent to your example query, which would be:

    const result = await db.select(‘user’, db.all, { lateral: { posts: db.select(‘post’, { userId: db.parent(‘id’) }) } }).run(pool);
And result will have the structure you asked for, and be automatically typed as such.

(Sorry, can’t manage helpful indentation from my phone).

See: https://jawj.github.io/zapatos/index.html#joins-as-nested-js...

Re: Show HN: PgTyped – Typesafe SQL in TypeScript and Postgres

#50

Post by ggregoire »

adriancooney wrote: post #11

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 lea…

Same, I really like this approach. Some of the benefits are:

- better separation of concerns

- better integration with SQL tools (syntax highlighting, autocompletion, etc)

- way easier to run/test/debug your queries into a database client

- better languages analysis of your projects (e.g. % of SQL in your GitHub/GitLab repo)

--

If anyone interested in applying this approach in your Python projects, I recommend this package: https://github.com/mcfunley/pugsql

Post reply on HN