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

31–40 of 90 posts

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

#31

Post by timmy-turner »

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

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

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.

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

#32

Post by chatmasta »

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

We’ve accomplished this with a combination of postgraphile and graphql-codegen. Our setup is basically the same as the postgraphile starter app. [0]

It’s pretty awesome.

[0] https://github.com/graphile/starter

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

#33

Post by mceachen »

goliatone wrote: post #15
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,…

You should check objection.js[1] as I think it would get you what you describe and probably more. It is a relational query builder built on top of knex, which is a query builder. [1] https://vincit.github.io/objection.js/

I wrote the original typescript bindings for objection. One of the library authors is a contributor to knex.js, which objection uses for query building. Both are quite nice.

With either library, you aren't writing naked SQL, but something like `query.where("updated_at", ">", Date.now())`. Tsc comes into play when you send typed objects into your .where, .insert, ... methods and what you get back from the query.

It's close enough to SQL that you don't lose expressiveness, you're not behind walls of magick due to orm, and you don't have to worry about Bobby Droptables.

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

#34

Post by renke1 »

chrischen wrote: post #24
renke1 wrote: post #17

Earlier quoted context omitted.

Thanks, I'll check it out. But on first glance it doesn't seem to let me write actual SQL queries. Also, it doesn't seem to be typesafe, or is it?

It says it has typescript support.

I didn't check those typings, but I don't think the model itself is type safe from the looks of it.

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

#35

Post by tlarkworthy »

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

It exists! You want Scala Slick plain SQL http://scala-slick.org/doc/3.0.0/sql.html

See tsql string interpolation with typed result sets!

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

#36

Post by rubber_duck »

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…

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

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

#38

Post by alde »

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…

ORMs like Diesel are definitely very useful. The problem I have with them is that their ORM abstraction often leaks. Fixing these abstraction leaks is a hard problem [1]. Ofc, there has been attempts to reconcile relational DBs with OOP like languages, but they are not very popular. [2]

PgTyped and some similar libs try to solve a simpler problem (typing static queries) and can be used to build more complex solutions when needed.

Writing query result/param type assertions by hand and using tests to guarantee type synchronization between DB and code wasn't maintainable on most projects I have seen.

[1] https://en.wikipedia.org/wiki/Object-relational_impedance_mi...

[2] https://en.wikipedia.org/wiki/The_Third_Manifesto

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

#39

Post by alde »

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?

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

#40

Post by gigatexal »

renke1 wrote: post #30
gigatexal wrote: post #22

Earlier quoted context omitted.

but select * is an anti-pattern. SQL queries should only return the columns that you need not that you might need -- usually. Lazy loading and sessions etc., like SQLAlchemy does to get around the N+1 query problem be damned.

I didn't mean the `SELECT STAR` in a literal sense, but more like, please select only the stuff we need. But indeed with PgTyped, as far as I understand it, it wouldn't work because it creates interface from SQL queries. To achieve what I want you need to do it like all the other ORMs where you have some kind of description of your model and how it maps to tables and columns. Unlike the usual ORM I want to write SQL…

Ahh I understand you now.
Post reply on HN