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

21–30 of 90 posts

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

#21

Post by renke1 »

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

Thanks! A grouping feature will definitely be useful, I have been thinking about a good way to add it to pgtyped. Will grouping fields by tables they belong to good enough? Or is there some different grouping logic you have in mind?

That's hard to say, because I am not sure what I really want.

…but let's say I have this result (from an arbitrary query).

  | user.id | user.name | post.id | post.title |
  +---------+-----------+---------+------------+
  | 1       | renke1    | 1       | first      |
  | 2       | alde      | 2       | second     |
  | 2       | alde      | 3       | third      |
Now I would like to tell the library: hey, an user can have many posts (1:n), please map this to nested objects.

Of course I don't want to write `SELECT user.id, user.name … FROM …` but just `SELECT * FROM …` (because a table may have a dozen of columns and I don't want to spell out every single one). So the query might have to be rewritten on-the-fly to make the correct projection (otherwise it would be hard to know to which object a value belongs).

I am not sure if that's something your library should do though.

And thinking even more about it, I think this approach wouldn't really work for views (and probably other things) where it's not really clear from which tables the data actually comes from (at least not by only looking at the query).

I guess what I really want is library that takes my SQL query, reads my mind and gives me back some nested objects… and let's not talk about inserts…

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

#22

Post by gigatexal »

renke1 wrote: post #21
alde wrote: post #13

Earlier quoted context omitted.

Thanks! A grouping feature will definitely be useful, I have been thinking about a good way to add it to pgtyped. Will grouping fields by tables they belong to good enough? Or is there some different grouping logic you have in mind?

That's hard to say, because I am not sure what I really want. …but let's say I have this result (from an arbitrary query). | user.id | user.name | post.id | post.title | +---------+-----------+---------+------------+ | 1 | renke1 | 1 | first | | 2 | alde | 2 | second | | 2 | alde | 3 | third | Now I would like to tell the library: hey, an user can have many posts (1:n), please map this to nested objects. Of course I…

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.

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

#23

Post by bijection »

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

If you were willing to give up a bit of magic, you could probably build this as a thin layer over PgTyped.

The API could be something like this:

Query.sql

  SELECT * FROM user LEFT JOIN post ON user.id = post.id
Application.ts

  const results = await Query()
  const nested = nest(results, {
    parentFields: ['userId', 'name'],
    childFields: ['postId', 'title'],
    childName: 'posts'
  )
If you wanted, you wouldn't really have to specify child fields, since they'd just whatever wasn't a parent field. It'd take a bit more work to get it to do multiple levels of nesting, but after a point it doesn't make sense to write queries that return so much duplicate data anyway.

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

#24

Post by chrischen »

renke1 wrote: post #17
goliatone wrote: post #15

Earlier quoted context omitted.

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/

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.

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

#25

Post by garrybelka »

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

GrahpQL might be an answer.Though an incomplete answer at this point due to a mismatch between plain GraphQL and SQL;

And the real issue is how to define and where to place a single source of truth for the schema an operations. So far we saw approaches where:

- GraphQL schema is generated from SQL tables. Makes total sense for a project or a company that looks to capitalize on customers with existing databases (e.g., PostGraphile, Hasura);

- SQL schema is generated from a GraphQL schema;

- SQL schema and TypeScript CRUD resolvers are generated from GraphQL schema (graphback);

- a language is introduced and GraphQL and SQL are generated from that language (Prisma);

- a library and a set of decorators are used to define both GraphQL schema and a typed ORM schema within a standard language (e.g, TypeGraphQL + TypeScript + some ORM such as TypeORM).

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

#26

Post by chrischen »

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

See https://typeorm.io/#/

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

#28

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

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

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

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

#30

Post by renke1 »

gigatexal wrote: post #22
renke1 wrote: post #21

Earlier quoted context omitted.

That's hard to say, because I am not sure what I really want. …but let's say I have this result (from an arbitrary query). | user.id | user.name | post.id | post.title | +---------+-----------+---------+------------+ | 1 | renke1 | 1 | first | | 2 | alde | 2 | second | | 2 | alde | 3 | third | Now I would like to tell the library: hey, an user can have many posts (1:n), please map this to nested objects. Of course I…

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 queries which are then rewritten in an intelligent manner.

So something like query(`SELECT STAR FROM user LEFT JOIN post …`, UserWithPostsModel). Since the library would know the target model, it could rewrite the `SELECT STAR` to something that only asks for the data it needs.

In other words I want to execute arbitrary queries that a mapped into ad-hoc models (unlike typical ORMs where the model usually maps directly to tables).

  STAR = *
Post reply on HN