GraphQL API generated from database schema

[karatakis]

Design Document

Purpose

This document's purpose is to document technical details, the system architecture and the development steps of the proposed
project.

Audience

Every developer with a good knowledge of Rust, GraphQL, SQL, software architecture and good will is encouraged to participate in the discussion.

Description

A tool that discovers the database schema and generates a GraphQL API that supports
essential CRUD operations.

Benefits

This project is a great opportunity to:

• Show how powerful is rust and the SeaQL ecosystem
• Improve and explore new (edge) use cases for existing projects (sea_schema, sea_query, juniper)
• Stress test the design of sea_schema and sea_query APIs design on real world scenarios

Risks

• Rust's GraphQL libraries not mature enough
• Rust compiler too strict with dynamic types
• Being unable to understand and develop complex code that solves the problem (ex. generating dynamic types)

Resources

Related resources, material and projects.

Dependencies

Libraries our project will depend on:

• https://github.com/SeaQL/sea-schema
• https://github.com/SeaQL/sea-query
• https://github.com/graphql-rust/juniper
• https://github.com/seanmonstar/warp
• https://github.com/launchbadge/sqlx
• https://github.com/tokio-rs/tokio

Reference Documentation

Important documentation material:

• https://docs.rs/sea-schema
• https://docs.rs/sea-query
• https://docs.rs/juniper

Articles / Issues / Discussions

Related articles, issues and discussions regarding our projects:

• How to create an entity where (most) fields and the type are only known at runtime? graphql-rust/juniper#957

  They are discussing how to implement dynamic GraphQL schema using juniper
  library

• Supporting Dynamic, Programmatic Schema async-graphql/async-graphql#495

  Discussion regarding dynamic GraphQL schema generation using async-graphql
  library. It is impossible to use non runtime types, a workaround is to use macro
  code.

• http://www.petecorey.com/blog/2017/06/12/graphql-nosql-injection-through-json-types

• https://hasura.io/docs/latest/graphql/core/databases/postgres/queries/query-filters.html

• https://hasura.io/blog/building-a-graphql-to-sql-compiler-on-postgres-ms-sql-and-mysql

• https://dgraph.io/docs/graphql/queries/search-filtering/#filter-a-query-for-a-range-of-objects-with-between

• https://graphql.org/learn/queries

• https://docs.fauna.com/fauna/current/api/graphql/relationships

• https://www.apollographql.com/blog/graphql/filtering/how-to-search-and-filter-results-with-graphql

• https://docs.fauna.com/fauna/current/api/graphql/relationships

Similar projects

• https://github.com/ForetagInc/Alchemy

  ArangoDB to GraphQL API generator

• https://github.com/graphile/postgraphile

• https://github.com/hasura/graphql-engine

System Overview

The application will be separated logically into 3 modules: database exploring, schema generation and execution engine.

Database exploring

Database exploring is responsible to explore the database schema and generate a database definition schema the next steps will depend on. An example output of this step is located here for the sample database

Schema generation

Is responsible to generate GraphQL types, enumerations, queries and mutations from the database definition schema. Enumerations will be generated whenever possible (mysql, pgsql). For every table type (Row) we will have the following generated queries: single row query, filtered by primary key, and paginated query using filters. Regarding mutations for every table we will support: single insert* , batch inserts*, single update**, batch updates** based on filters, single delete using ID and batch deletes based on filters. The juniper Registry will be used to store the generated items.
* optional create with relations
** optional update with atomic operations (ex. x = x + 1)

Execution engine

Is responsible to generate SQL queries from incoming GraphQL query/mutation requests, execute them into the database and return the appropriate response back. sea_query will be the main library used tin the execution engine. The execution engine will perform SQL queries with joins, filters, pagination and aggregator functions. In addition to queries, the engine will be able to perform SQL insert, batch inserts, update, batch updates, delete, and batch deletes.

Example

We will use the following database to demonstrate the application. Note we use only albums and artists tables with the publishedAt (date) field added to the albums table. I did some tinkering on my own on the following repo, its a WIP proof of concept!!!

1. We produce the database schema definition, you can find it here.

2. We generate the GraphQL schema types, queries and mutations.

enum StringOps {
  Equals
  Starts
  Ends
  Like
  Null
  In
}

enum NumberOps {
  Equals
  Greater
  GreaterEquals
  Less
  LessEquals
  Null
  In
}

type StringFilter {
  mode: StringOps!
  value: String!
  inverse: Boolean!
}

type IntFilter {
  mode: NumberOps!
  value: Int!
  inverse: Boolean!
}

type FloatFilter {
  mode: NumberOps!
  value: Float!
  inverse: Boolean!
}

type DateFilter {
  mode: NumberOps!
  value: String!
  inverse: Boolean!
}

type AlbumsRow {
  AlbumId: ID!
  Title: String!
  PublishedAt: String
  ArtistId: Int!
  Artists: AlbumsArtistsRow!
}

type AlbumsArtistsRow {
  ArtistId: ID!
  Name: String,
}

type AlbumsFilter {
  AlbumId: [IntFilter!]!
  Title: [StringFilter!]!
  PublishedAt: [StringFilter!]!
  ArtistId: [IntFilter!]!
  Artists: [AlbumsArtistsFilter!]!
}

type AlbumsArtistsFilter {
  ArtistId: [IntFilter!]!
  Name: [StringFilter!]!
}

type AlbumsInput {
  Title: String!
  PublishedAt: String
  ArtistsId: Int!
}

type AlbumsPagination {
  count: Int!
  data: [AlbumsRow!]!
}

type ArtistsRow {
  ArtistId: ID!
  Name: String,
  Albums: [ArtistsAlbumsRow!]!
}

type ArtistsAlbumsRow {
  AlbumId: ID!
  Title: String!
  PublishedAt: String
  ArtistId: Int!
}

type ArtistsFilter {
  ArtistId: [IntFilter!]!
  Name: [StringFilter!]!
  Albums: [ArtistsAlbumsFilter!]!
}

type ArtistsAlbumsFilter {
  AlbumId: [IntFilter!]!
  Title: [StringFilter!]!
  PublishedAt: [StringFilter!]!
  ArtistId: [IntFilter!]!
}

type ArtistsInput {
  Name: String,
  Albums: [ArtistsAlbumsInput!]!
}

type ArtistsAlbumsInput {
  
  Title: String!
  PublishedAt: String
}

type ArtistsPagination {
  count: Int
  data: [ArtistsRow!]!
}


type Query {
  query_albums (filters: [AlbumsFilter!]!): AlbumsPagination!,
  single_albums (id: ID!): AlbumsRow,
  query_artists (filters: [ArtistsFilters]!): ArtistsPagination!,
  single_artists (id: ID!): ArtistsRow
}

type Mutations {
  insert_albums (data: AlbumsInput!): AlbumsRow!
  insert_artists (data: ArtistsInput!): ArtistsRow!
  
  batch_insert_albums (data: [AlbumsInput!]!): [AlbumsRow!]!
  batch_insert_artists (data: [ArtistsInput!]!): [ArtistsRow!]!
  
  update_albums (id: ID!, data: AlbumsInput!): AlbumsRow!
  update_artists (id: ID!, data: ArtistsInput!): ArtistsRow!
  
  batch_update_albums (filters: [AlbumsFilter!]!, data: [AlbumsInput!]!): [AlbumsRow!]!
  batch_update_artists (filters: [ArtistsFilter!]!, data: [ArtistsInput!]!): [ArtistsRow!]!
  
  delete_albums (id: ID!): AlbumsRow!
  delete_artists (id: ID!): ArtistsRow!
  
  batch_delete_albums (filters: [AlbumsFilter!]!): [AlbumsRow!]!
  batch_delete_artists (filters: [ArtistsFilter!]!): [ArtistsRow!]!
}

3. The engine has generic solvers for every query and mutation type, around 8 in total. Single query, Filter Query, Single Insert, Batch Insert, Single Update, Batch Update, Single Delete, Batch Delete.

Ways to approach project

We can approach the problem in 3 different ways:

• Runtime based: the application will read the database schema during runtime
  and generate types, queries and mutations on the fly.
• Macro based: the application will read the database schema during compile
  time and generate rust code for the types, mutations and queries.
• Generator based: the application will be a CLI that reads the database schema,
  when a user executes the tool, and writes rust code that is ready to be
  compiled.

Advantages:

• Runtime based: easy to debug, seems easy to code, can be delivered as binary
• Macro based: compile checking available, seems easy to allow user
  extension/modification
• Generator based: compile checking available, easy to debug, easy to allow user
  extension/modification

Disadvantages:

• Runtime based: maybe hard to code (graphql libraries might not allow dynamic
  types), hard to allow user extension/modification
• Macro based: hard to code, hard to debug, needs compile
• Generator based: maybe hard to code (never done something similar), needs compile

Deliverables

If we choose the runtime based project we will have to deliver:

• A repository containing all the source code of the tool, documentation,
  examples, and testing
• A website with the documentation of the project
• Publish the project on crates.io
• A website that markets the project to the public
• A compiled binary file ready to be distributed

List of tasks

• Database exploring and types generation (8 days)
  • Design configuration spec (2 days)
  • Use sea-schema to generate it (1 days)
  • Enumerations (1 day)
  • Types from Tables with Relations (4 days)
• Queries Engine (30 days)
  • Basic queries with pagination (5 days)
  • Relations queries, filtering and pagination (15 days)
  • Aggregator queries COUNT, AVG, etc. (5 days)
  • Quality Assurance, Documentation, Examples (5 days)
• Mutations engine (30 days)
  • Create (5 days)
  • Update (5 days)
  • Delete (5 days)
  • Transactions and Consistency (10 days)
  • Quality Assurance, Documentation, Examples (5 days)
• Quality Assurance (16 days)
  • Tidy documentation (8 days)
  • Organize examples (8 days)

Future work

• Add subscriptions
• Finish optional features (batch insert/updates, relational creates, atomic updates)

[billy1624]

Hey @karatakis, awesome! I think adding authorizarion for GraphQL endpoints will be a good future work. However, it might be out-of-scope and it should be provided by the underlaying GraphQL library

• Is it possible to add authorization system on Juniper graphql-rust/juniper#447

[tyt2y3]

Right I agree

[tyt2y3]

Thank you for the long passage. Here is my feedback:

1. I think there are only 2 modules in this project: schema generation and execution engine. I think database exploring is basically what sea-schema had done already
2. we should discuss the user interaction here: how will users be using these tools? I imagine a command line tool may be the user run sea graphql explore and viola a GraphQL schema generated! What'd be the next step to actually build a complete GraphQL server?
3. we have to define whether the schema will be loaded compile-time or run-time. For runtime, I mean it'd be like StarfishQL where schema information is completely loaded runtime. on the other hand, if the schema is compile-time, we can generate some SeaORM entities (or some structs which impl a bunch of traits) and then have the resolver engine hook into these entities via a dyn interface. I personally prefer the compile-time approach as it will allow users to override / customize the behaviour
4. with that said, we should imagine how the execution engine works. as reference, it could work something like StarfishQL, where there is an Executor with states responsible for resolving relations. other designs are welcomed here
5. if the scope of the project is too large, we can probably cut out the mutation queries. not that they are not important, but a read-only query engine is useful enough on its own. if focusing on one aspect makes better software, I'd rather be more focused

[karatakis]

1. Yeah, sea_schema fits perfect for database exploring. I am going to depend on that.
2. The user interface varies on the way we approach the problem.
   2.1. Runtime: sea graphql -db sqlite://file.db during runtime it explores the database, generates the schema and serves a graphql endpoint that performs queries and mutations to the database
   2.2. Generated: sea graphql file_db -db sqlite://file.db it explores the database and generates a project file_db with entities, queries and mutations ready to be compiled and when run it serves a graphql endpoint
3. I looked the run-time scenario, I made some progress, but the user customization as well as type safety are its drawbacks. I will start a code generator project for the compile time approach and see how it works
4. The executor will generate queries based on schema definition and query arguments, but on compile time version we can utilize sea orm entities
5. If I can tackle basic queries, and basic mutations then the more advanced methods are just more coding. I think we can do both on the same project

[karatakis]

I made a demo to showcase the generated code scenario https://github.com/karatakis/rust-graphql-generator

The demo can do basic query SELECT * FROM table but we can add more features later. The demo depends on sea_orm and sea_orm_generator to build entities and queries.

To run the example:

$ cargo install sea-orm-cli
$ cargo run generated
$ cp chinook.db ./generated/chinook.db
$ cd generated
$ cargo run

Then go to link http://localhost:8000/

Its still WIP, that is why I have some things hard coded.

I love how it works so far and I think I will stick to the code generator scenario.

Feedback will be appreciated

[billy1624]

What a great start! I wonder why you create a wrapper struct for the GraphQL object instead of using the model struct directly? e.g.

#[derive(Debug)]
struct Tracks(generated::orm::tracks::Model);
#[async_graphql::Object]
impl Tracks {
    async fn track_id(&self) -> &i32 {
        &self.0.track_id
    }
    ...
}

[karatakis]

I did some refactoring to solve the issue.

[tyt2y3]

Oh, I definitely need to checkout this during the weekend.

[tyt2y3]

I checked that and it's really impressive! Actually, such a small library can generate all these amount of code with those functionality is unimaginable.

Anyway, going forward, I see lots of repetition in the generated code.

#[derive(async_graphql :: InputObject, Debug)]
pub struct AlbumsAlbumIdFilter {
    pub eq: Option<i32>,
    pub ne: Option<i32>,
    pub gt: Option<i32>,
    pub gte: Option<i32>,
    pub lt: Option<i32>,
    pub lte: Option<i32>,
    pub is_in: Option<Vec<i32>>,
    pub is_not_in: Option<Vec<i32>>,
    pub is_null: Option<bool>,
}

We might probably want to hide these behind a derive macro instead like below to reduce the entropy.

numeric_filter!(AlbumsAlbumIdFilter);

Although it might be too much magic at this level of abstraction.

The goal is to make the generated code concise enough to be readable but still elaborate enough to be understandable, and flexible enough to be customizable.

Awesome work all in all!

[karatakis]

Amazing recommendation, and thanks for taking the time to review the project.

Regarding your concerns I lean towards the following idea. I would create a Input type for each possible filter type (int, double, bool, etc.) and use them in the Model Filters structs. We can latter use derive macros to generate Model Filter structs.

filters.rs

[...]

#[derive(async_graphql :: InputObject, Debug)]
pub struct IntegerFilter {
    pub eq: Option<i32>,
    pub ne: Option<i32>,
    pub gt: Option<i32>,
    pub gte: Option<i32>,
    pub lt: Option<i32>,
    pub lte: Option<i32>,
    pub is_in: Option<Vec<i32>>,
    pub is_not_in: Option<Vec<i32>>,
    pub is_null: Option<bool>,
}

#[derive(async_graphql :: InputObject, Debug)]
pub struct StringFilter {
    pub eq: Option<String>,
    pub ne: Option<String>,
    pub gt: Option<String>,
    pub gte: Option<String>,
    pub lt: Option<String>,
    pub lte: Option<String>,
    pub is_in: Option<Vec<String>>,
    pub is_not_in: Option<Vec<String>>,
    pub is_null: Option<bool>,
}

[...]

entities.rs

[...]

model_filter!(Albums);

[...]

generated code

[...]

#[derive(async_graphql :: InputObject, Debug)]
pub struct AlbumsFilter {
    pub or: Option<Vec<Box<AlbumsFilter>>>,
    pub and: Option<Vec<Box<AlbumsFilter>>>,
    pub album_id: Option<IntegerFilter>,
    pub title: Option<StringFilter>,
    pub artist_id: Option<IntegerFilter>,
}

[...]

Whats your thoughts on that ?

[billy1624]

Is it possible to define the filter over a generic T ?

#[derive(async_graphql :: InputObject, Debug)]
pub struct GenericFilter<T> {
    pub eq: Option<T>,
    pub ne: Option<T>,
    pub gt: Option<T>,
    pub gte: Option<T>,
    pub lt: Option<T>,
    pub lte: Option<T>,
    pub is_in: Option<Vec<T>>,
    pub is_not_in: Option<Vec<T>>,
    pub is_null: Option<bool>,
}

[karatakis]

I think yes, but it needs a generic where condition for types that implement GraphQLType trait.

But I have to look if async-graphql supports generic object structs

[karatakis]

I found a way to do generic InputTypes and made a GenericFilter for most SeaQL entity types

[karatakis]

My next milestone will be relational queries. For each entity I will utilize its foreign keys and append a getter for its related entity.

Problem:
One problem are cyclic relations. For example a->b->a->b->... can be utilized with malicious intent and generate expensive queries.

Easy Solution:
Utilize a GraphQL cost calculator and depth limiter in order to prevent expensive relational queries.

• https://shopify.engineering/rate-limiting-graphql-apis-calculating-query-complexity
• https://async-graphql.github.io/async-graphql/en/depth_and_complexity.html

Complex Solution:
The plan is to generate a graph of all entities relations. Then use the graph and generate for each entity a tree using BFS/DFS algorithm with the aim to prevent loops and remove cyclic relationship queries. For each entity I will utilize its generated tree and append to its GraphQL object type getters for the related entities. The related entities are new GraphQL objects (ex. a -> a.b and a.b -> a.b.c ).

Resources:

• https://docs.rs/sea-query/latest/sea_query/table/struct.TableCreateStatement.html#method.get_foreign_key_create_stmts
• https://docs.rs/sea-query/latest/sea_query/foreign_key/struct.ForeignKeyCreateStatement.html#method.get_foreign_key
• https://docs.rs/sea-query/0.24.5/sea_query/foreign_key/struct.TableForeignKey.html
• https://docs.rs/sea-orm-codegen/0.8.0/sea_orm_codegen/struct.Relation.html#method.from

[billy1624]

Hey @karatakis, good point! I think the depth limiter provided by async-graphql is good enough.

[karatakis]

the generator now supports related queries but without optimization for (1+N) problem.

Next steps:

• solve (1+N) problem
• allow filters on related queries
• pagination on root entities

[billy1624]

Without optimization, solving (1+N) problem would require (1+N) queries. I'm think what's the plan to optimize it?

[karatakis]

I will use data loaders from async_graphql

[karatakis]

I gave a solution to N+1 problem

Features so far:

• root queries
• filters on root queries
• fetch related items
• solve n+1 problem

Issues solved but not implemented:

• query complexity
• macros to reduce code generation and easier to test and debug

My long term plans are:

• filters related queries
• pagination on root elements
• support mysql, pgsql
• • support enumerations (mysql, pgsql)
• create one/many mutations
• • create related mutations
• update one/many mutations
• • update many related on condition
• delete one/many mutations

[tyt2y3]

I think solving the N+1 problem is not the top priority for now. I'd like to figure out the developer experience first. We can always come back to change things underneath and improve performance!

[karatakis]

Too late, now we have it solved 😅

[frederikhors]

You are turning a dream that I have had for years into a beautiful reality, guys!

I'm not as good at programming in Rust as you are, but I'm picky and precise when it comes to testing even in the enterprise environment.

Can't wait to start testing.

Queries with complex filters are a real joy.

In a few days I will have time to try and would like to use async-graphql.

Thank you very much!

[tyt2y3]

Actually, I invited you to have a peek into what we've been doing so far.

[frederikhors]

Amazing guys,

I'm using gqlgen (like async-graphql or juniper but in Go not in Rust) and Ent as ORM (but is more than an ORM).

I'm writing to you because I just found out an excellent feaure of Ent: GraphQL Field Collection.

Since you are dedicating yourself to this type of work, I thought this feature might interest you to add it among the wonderful pearls of SeaORM.

Basically Ent with this feature offers the possibility to AVOID ALL THE DATALOADERs.

Do you think it can be done? It would be REVOLUTIONARY! As it was with Ent.

Thank you for your work.

[tyt2y3]

I have had a similar idea with a different implementation strategy: basically I want to make a query engine. Though it is still an idea-in-progress now.