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

61–70 of 90 posts

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

#61

Post by Dowwie »

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…

While the scenario you present is a legitimate reason for using a query builder, it doesn't justify the investment in using a query builder for the vast majority of db calls. The edge case is not an appropriate goal to impose the cost of using a DSL for the majority of cases. Instead, custom roll an implementation for those complicated calls and stick with a parameterized sql library.

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

#63

Post by barnabee »

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

Postgres is pretty good at JSON. I think this would work for your example (assuming the implied schema), presumably in PgTyped too:

SELECT user.*, json_agg(post) as posts FROM user LEFT JOIN post ON user.id = post.id GROUP BY user.id

It's not as simple as you'd like (and you have to wrap the whole thing in a subquery + another json_agg if you really want Postgres to return a single JSON string) but it's not actually a lot more complicated, and avoids having to rely on magic that's likely brittle and hard to debug (or only really useful in a small subset of cases).

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

#65

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

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

#66

Post by yakshaving_jgt »

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…

> While nice to have, preventing bugs with static SQL is usually easy to do by writing a few tests

Why not both?

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

#67

Post by mekster »

cyral wrote: post #55
timmy-turner wrote: post #31

Earlier quoted context omitted.

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 `jso…

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.

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

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

#68

Post by mekster »

gmac wrote: post #49
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://…

I just don't get why people write queries like that in a convoluted way which is hard to decipher and possibly optimize with so many noises whose knowledge becomes useless on the next language/framework of choice than a straight forward SQL whose knowledge can live for decades.

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

#69

Post by goofiw »

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…

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

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

#70

Post by AlphaSite »

alde wrote: post #38
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…

I may just not have enough context on the issue, but i've not had significant issues with SqlAlchemy's hybrid ORM/Query Builder approach.
Post reply on HN