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

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

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

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.

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.

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.

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.

It is. See my ggp reply.

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

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.

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

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

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!

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.

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.

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.

The problem is that there's no obvious, simple way to bring the results of this query, structured and typed appropriately (with posts nested inside users), into our running code.

So we have to choose what we think is the least-worst compromise.

Since I wrote the library I am obviously anything but impartial, but the compromise I outlined above is one I'm pretty happy with (and it's much easier to read if you add a few line breaks). It's a bit noisier than your JOIN query, but for the price of that noise you get type safety and an appropriate JSON structure for the result.

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…

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 = *

Ahh I understand you now.

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

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!

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

See https://typeorm.io/#/

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

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.

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.

MongoDB support is a work in progress for them too but otherwise a fun library.