10 ms·

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

by renke1 6y ago

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

bijection 6y ago
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.
- kevsim 6y ago
  > but after a point it doesn't make sense to write queries that return so much duplicate data anyway This is what I'm constantly wondering. At what point does it stop being good to return the user table results again and again and just switch to, for example, an IN query to get the posts?
alde 6y ago
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?
- renke1 6y ago
  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…
  - gigatexal 6y ago
    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.
    - renke1 6y ago
      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 = *
goliatone 6y ago
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/ https://vincit.github.io/objection.js/
- renke1 6y ago
  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?
  - chrischen 6y ago
    It says it has typescript support.
    - renke1 6y ago
      I didn't check those typings, but I don't think the model itself is type safe from the looks of it.
      - mceachen 6y ago
        It is. See my ggp reply.
- mceachen 6y ago
  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.
hyeomans 6y ago
You never mentioned the language, so maybe Dapper? https://github.com/StackExchange/Dapper/ https://github.com/StackExchange/Dapper/
raziel2p 6y ago
You'd be constrained by whatever is trying to parse SQL queries to figure out what object structure to return instead.
garrybelka 6y ago
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).
- noodlesUK 6y ago
  The system that I’ve most enjoyed using recently has been hasura. It’s basically a plug and play graphql api that sits on top of a Postgres db. I love it. You can call your database directly from your clients, with row level permissions, and real time subscriptions. It’s just great. hasura.io
chrischen 6y ago
See https://typeorm.io/#/ https://typeorm.io/#/
- eyelidlessness 6y ago
  I mean no disrespect to the TypeORM author(s), but I was burned several times by unexpected behavior that resulted in serious data integrity issues. I would caution that anyone adopting this library test any usage heavily, especially anything to do with relations
  - gmac 6y ago
    Yes. My brief experience with TypeORM made me feel like it was written by people who really understand TypeScript, but maybe don’t understand SQL so well. It was a brief experience because I quickly lost trust in it.
  - rhlsthrm 6y ago
    What version did you use? Have the issues been raised and addressed? Curious because I am using it in production and now I'm worried. Would love more information about reproducing the issues as well.
- rak 6y ago
  MongoDB support is a work in progress for them too but otherwise a fun library.
timmy-turner 6y ago
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.
- deleted 6y ago
  [deleted]
- cyral 6y ago
  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 6y ago
    But unfortunately returned values have no type definition unless manually provided.
chatmasta 6y ago
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 https://github.com/graphile/starter
tlarkworthy 6y ago
It exists! You want Scala Slick plain SQL http://scala-slick.org/doc/3.0.0/sql.html http://scala-slick.org/doc/3.0.0/sql.html See tsql string interpolation with typed result sets!
gmac 6y ago
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-json https://jawj.github.io/zapatos/index.html#joins-as-nested-js...
- renke1 6y ago
  Thanks, Zapatos looks really nice. That library seems to be basically what I want (aside from writing SQL for joins, but I think it's just not possible in the way I imagined). The documentation is really nice. Much of what is written in it aligns with my thinking. I'll definitely give it a try!
- mekster 6y ago
  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.
  - gmac 6y ago
    I’m not exactly sure what you’re getting at here. The whole point of this library is that one call = one predictable query, and the SQL it generates is certainly no more convoluted than required to produce the desired result.
    - mekster 6y ago
      What was so hard to grasp with my comment? If your query was meant to say, SELECT * FROM users JOIN posts ON posts.userId = users.id the ORM has so many unnecessary characters just to get to this straight forward SQL and you have to learn how to make sure the way you write will generate the SQL that you want that uses the proper index everytime you change framework is just something I wouldn't do. I didn't say the SQL generated is convoluted but the way you need to write so many characters that doesn't hold a meaning is convoluted.
barnabee 6y ago
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).

by renke1 6y ago

bijection 6y ago

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.

kevsim 6y ago
> but after a point it doesn't make sense to write queries that return so much duplicate data anyway This is what I'm constantly wondering. At what point does it stop being good to return the user table results again and again and just switch to, for example, an IN query to get the posts?

alde 6y ago

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?

renke1 6y ago
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…
- gigatexal 6y ago
  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.
  - renke1 6y ago
    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 = *

goliatone 6y ago

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/ https://vincit.github.io/objection.js/

renke1 6y ago
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?
- chrischen 6y ago
  It says it has typescript support.
  - renke1 6y ago
    I didn't check those typings, but I don't think the model itself is type safe from the looks of it.
    - mceachen 6y ago
      It is. See my ggp reply.
mceachen 6y ago
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.

hyeomans 6y ago

You never mentioned the language, so maybe Dapper? https://github.com/StackExchange/Dapper/ https://github.com/StackExchange/Dapper/

raziel2p 6y ago

You'd be constrained by whatever is trying to parse SQL queries to figure out what object structure to return instead.

garrybelka 6y ago

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

noodlesUK 6y ago
The system that I’ve most enjoyed using recently has been hasura. It’s basically a plug and play graphql api that sits on top of a Postgres db. I love it. You can call your database directly from your clients, with row level permissions, and real time subscriptions. It’s just great. hasura.io

chrischen 6y ago

See https://typeorm.io/#/ https://typeorm.io/#/

eyelidlessness 6y ago
I mean no disrespect to the TypeORM author(s), but I was burned several times by unexpected behavior that resulted in serious data integrity issues. I would caution that anyone adopting this library test any usage heavily, especially anything to do with relations
- gmac 6y ago
  Yes. My brief experience with TypeORM made me feel like it was written by people who really understand TypeScript, but maybe don’t understand SQL so well. It was a brief experience because I quickly lost trust in it.
- rhlsthrm 6y ago
  What version did you use? Have the issues been raised and addressed? Curious because I am using it in production and now I'm worried. Would love more information about reproducing the issues as well.
rak 6y ago
MongoDB support is a work in progress for them too but otherwise a fun library.

timmy-turner 6y ago

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.

deleted 6y ago
[deleted]
cyral 6y ago
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 6y ago
  But unfortunately returned values have no type definition unless manually provided.

chatmasta 6y ago

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 https://github.com/graphile/starter

tlarkworthy 6y ago

It exists! You want Scala Slick plain SQL http://scala-slick.org/doc/3.0.0/sql.html http://scala-slick.org/doc/3.0.0/sql.html See tsql string interpolation with typed result sets!

gmac 6y ago

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-json https://jawj.github.io/zapatos/index.html#joins-as-nested-js...

renke1 6y ago
Thanks, Zapatos looks really nice. That library seems to be basically what I want (aside from writing SQL for joins, but I think it's just not possible in the way I imagined). The documentation is really nice. Much of what is written in it aligns with my thinking. I'll definitely give it a try!
mekster 6y ago
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.
- gmac 6y ago
  I’m not exactly sure what you’re getting at here. The whole point of this library is that one call = one predictable query, and the SQL it generates is certainly no more convoluted than required to produce the desired result.
  - mekster 6y ago
    What was so hard to grasp with my comment? If your query was meant to say, SELECT * FROM users JOIN posts ON posts.userId = users.id the ORM has so many unnecessary characters just to get to this straight forward SQL and you have to learn how to make sure the way you write will generate the SQL that you want that uses the proper index everytime you change framework is just something I wouldn't do. I didn't say the SQL generated is convoluted but the way you need to write so many characters that doesn't hold a meaning is convoluted.

barnabee 6y ago

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