Dynamic TypeORM query building to resolve any GraphQL request

[david-eos]

For some weeks, I've been wondering if the following would be possible.

The idea

Given a GraphQL request, is it possible to dynamically build a TypeORM query from GraphQLResolveInfo context variable data, that returns all information asked for at once?

One single graphql query, one single resolver, one single sql query. I know that this breaks completely GraphQL philosophy, so I don't know how useful or efficient this is, if it is. But I find it interesting so I want to know what you guys think about it.

I finally managed to get it working. This is how I did it.

Example

This is what I request:

query {
  getUser(userId: 1) {
    userId
    firstName
    lastName
    posts {
      postId
      title
      pages {
        pageId
        content
      }
    }
  }
}

This is the SQL query that would generate:

query: SELECT
    `User`.`userId` AS `User_userId`,
    `User`.`firstName` AS `User_firstName`,
    `User`.`lastName` AS `User_lastName`,
    `User_posts`.`postId` AS `User_posts_postId`,
    `User_posts`.`userId` AS `User_posts_userId`,
    `User_posts`.`title` AS `User_posts_title`,
    `User_posts`.`postId` AS `User_posts_postId`,
    `User_posts`.`title` AS `User_posts_title`,
    `User_posts_pages`.`pageId` AS `User_posts_pages_pageId`,
    `User_posts_pages`.`postId` AS `User_posts_pages_postId`,
    `User_posts_pages`.`content` AS `User_posts_pages_content`,
    `User_posts_pages`.`pageId` AS `User_posts_pages_pageId`,
    `User_posts_pages`.`content` AS `User_posts_pages_content`,
    `User_posts`.`postId`, `User_posts`.`title`, `User_posts_pages`.`pageId`,
    `User_posts_pages`.`content`
FROM `user` `User`
LEFT JOIN `post` `User_posts` ON `User_posts`.`userId`=`User`.`userId`
LEFT JOIN `page` `User_posts_pages` ON `User_posts_pages`.`postId`=`User_posts`.`postId`
WHERE `User`.`userId` = ? -- PARAMETERS: [1]

Imagine user with id 1 has 100 posts, and each one has 200 pages. This would be 1 + 100 resolvers that will result in 101 queries to the database. Using a dataloader you could reduce it to 101 resolvers and 2 queries. This method returns all data in one single query.

Explanation

• Firstly, I take GraphQLResolveInfo context object, and I extract the GraphQL query tree I can work with.
• Secondly, I create a query builder and I recursively build the TypeORM query selecting asked fields and joining relations.
• Finally I execute the query returning all asked data, and no other resolvers are called.

The generated query is built only with the neccessary data. The idea is similar to TypeORM eager relations, loads relation data when the entity is loaded. The key of this method is that it does the same with all relations (whether or not are eager) depending if they are asked in the graphql query, and with which specific fields.

This allows you to transform dynamically any graphql possible request into a SQL single query.

Source code

Check the code and test it yourself here

Notes

Of course, I'm just trying new things. This method may not work in many other cases, and I don't pretend to.

What I'm looking for next

Imagine the possibility not only of building the query, but of adding filters and options as it is built.

Example:

query {
  getUser(userId: 1) {
    userId
    firstName
    lastName
    posts(tag: "important", orderAscBy: "postId") {
      postId
      title
      pages(orderDescBy: "pageId") {
        pageId
        content
      }
    }
  }
}

This would generate:

query: SELECT
    `User`.`userId` AS `User_userId`,
    `User`.`firstName` AS `User_firstName`,
    `User`.`lastName` AS `User_lastName`,
    `User_posts`.`postId` AS `User_posts_postId`,
    `User_posts`.`userId` AS `User_posts_userId`,
    `User_posts`.`title` AS `User_posts_title`,
    `User_posts`.`tag` AS `User_posts_tag`,
    `User_posts`.`postId` AS `User_posts_postId`,
    `User_posts`.`title` AS `User_posts_title`,
    `User_posts`.`tag` AS `User_posts_tag`,
    `User_posts_pages`.`pageId` AS `User_posts_pages_pageId`,
    `User_posts_pages`.`postId` AS `User_posts_pages_postId`,
    `User_posts_pages`.`content` AS `User_posts_pages_content`,
    `User_posts_pages`.`pageId` AS `User_posts_pages_pageId`,
    `User_posts_pages`.`content` AS `User_posts_pages_content`,
    `User_posts`.`tag`, `User_posts`.`postId`, `User_posts`.`title`,
    `User_posts_pages`.`pageId`, `User_posts_pages`.`content`
FROM `user` `User`
LEFT JOIN `post` `User_posts` ON `User_posts`.`userId`=`User`.`userId`
LEFT JOIN `page` `User_posts_pages` ON `User_posts_pages`.`postId`=`User_posts`.`postId`
WHERE `User`.`userId` = ? AND `User_posts`.`tag` = ?
ORDER BY `User_posts`.`postId` ASC, `User_posts_pages`.`pageId` DESC -- PARAMETERS: [1,"important"]

I've already done this, and it works but I have some problems with it. Firstly, as second, third, etc resolvers levels are not neccessary, I don't know how to define entity fields arguments. If I create resolvers just for TypeGraphQL to build the schema (resolvers that are supposed to not be used anymore), they are called anyways to get data I already got in my built generated query. The only way I found to define field arguments is in the entity itself.

This way:

@ObjectType()
@Entity()
export class User {

    @Field(() => Int)
    @PrimaryGeneratedColumn({ type: "bigint" })
    public userId: number;

    @Field()
    @Column()
    public firstName: string;

    @Field()
    @Column()
    public lastName: string;

    @Field(() => [Post], { nullable: true })
    @OneToMany(() => Post, (post) => post.author)
    @JoinColumn({ name: "userId", referencedColumnName: "userId" })
    public posts(@Args() {}: PostFilterArgs): Promise<Post[]> { return; }
}

Note that the "fake" resolver it just returns and does nothing. That's what I don't like, and it gives errors sometimes I have no idea what to do about.

You can check this anyway at the same code above just with some small modifications here

Conclusion

I don't know how stupid this idea is, or if is it useful, if already exists, but it worked for me. I want to know your opinion, how could we improve this idea, and if it would be a nice feature.

[MichalLytek]

I really appreciate your work but really don't know what I'm supposed to do with this issue 😕 You should write an article on Medium, share it on Twitter or something.

Generating single query is really against GraphQL design goals and the N+1 problem can be easily solved with dataloader. If you need automatically generated resolvers for relations, you should consider using other frameworks like warthog. The TypeORM integration plugin #44 will solve only the simple automatic relation resolvers, just like vesper.

So once again - it's nice but won't land in type-graphql package. You can still publish it on npm and share the world on twitter and Medium. Closing for now 🔒

[MichalLytek]

This allows you to transform dynamically any graphql possible request into a SQL single query.

Your is similar to join-monster lib: https://github.com/acarl005/join-monster

If you use the TypeORM API properly and support mapping + other stuff, you can advertise your lib as the TypeORM equivalent of the join-monster that works with any GraphQL framework, not only TypeGraphQL 😉

[david-eos]

you should consider using other frameworks like warthog. Your is similar to join-monster lib: https://github.com/acarl005/join-monster

Theese two libraries looks so good but they're not what I'm looking for. Neither vesper. join-monster does what I want but it doesn't integrates with typeorm since I have to redefine entity relation metadata myself again, when I'm trying to re-use typeorm entity metadata and its query builder.

Warthog looks much better, but it doesn't generate dynamic queries joining entities.

At least is there any way in TypeGraphQL to define field GraphQL args without using resolvers?

[david-eos]

Finally I managed to resolve it myself. It works perfect for me now. Thanks anyways.

[MichalLytek]

At least is there any way in TypeGraphQL to define field GraphQL args without using resolvers?

See #339 - the API would be really weird. For kinda schema-first approach you should create the resolver classes and just throw an error as the implementation of the method's body.

[iamgmd]

Finally I managed to resolve it myself. It works perfect for me now. Thanks anyways.

@david-eos Would really like to know how you resolved this, can you share?

[barbieri]

Finally I managed to resolve it myself. It works perfect for me now. Thanks anyways.

@david-eos Would really like to know how you resolved this, can you share?

yeah, if you have a public npm package and github repo, let us know and we can use/contribute. We have a poor's man version (similar to your initial version, but doesn't handle nested args as you did).

[GrayStrider]

@david-eos I keep bumping on this thread, give us something man :D Actually I've implemented dynamic query builder using graphql-fields and some recursion, but there's no pagination and for now I have to manually specify which fields are relations and which are plain properties (one does leftJoin, other addSelect)

[GrayStrider]

Now that I've read the post itself it seem to describe the same thing 🤔

[wesleyyoung]

https://github.com/wesleyyoung/perch-query-builder

Wrote a medium article as well: https://wesley-young.medium.com/combine-graphql-with-typeorm-and-nestjs-using-perch-query-builder-503e6342c35e

[omerman]

@wesleyyoung Im going to try it out soon. I was searching for something like this for a long time. I'll let you know how it goes :)

If we take a look at C#'s HotChoclate library, it gives a fantastic way for wrapping EntityFramework(C#'s number one ORM) building a fully functional Graphql Server and easily add support for db queries with Filters & Pagination & Sorting with just a single line of code, as each one of those are pluggable.. Take a look Its awesome what they've done

[iamgmd]

I moved to C# Hotchocolate in the end folks, it has so much more to offer but one of the main reasons was that I needed the backend to create/edit/merge PDF's and I C# offers better support for that kind of stuff.

[rymanso]

To anyone else who ends up on this thread Nestjs-query might be what you are looking for! :) - there is a fork of it being maintained for typeorm 0.3+