Sqlmancer integration as backing layer for our runtime

[danielrearden]

@wtrocki I'm opening this issue so we can continue the discussion around potentially integrating Sqlmancer into Graphback.

Adding Sqlmancer under the hood would potentially provide a lot of extra functionality and go a long way to addressing existing issues like #1031, #1095, #1122, #1256, #1265 and #1260.

However, there's a good bit of incongruence between the two projects to work through. I will try to take some time to dig through the graphback codebase and try to outline the bigger obstacles. I know you said you looked through the Sqlmancer docs, so if you have additional ideas or comments in the meantime, let me know.

[wtrocki]

I have evaluated the library and really like it however we are at the phase where we refine our top level API for developers. We are aware of the obstacles already and we will be happy to work with you on this. Myself and @craicoverflow will be back to you with more details next week.

The biggest difference I see at the moment is CRUD spec - we spent a lot of time on getting the format we think is extensible and standard enough to be extended in future for situations like deltas etc. We have been talking with other CRUD tools providers and hope to release them at https://graphqlcrud.org in the near future. Would you be interested in bringing the extensive knowledge of the crud into this spec?

Second is our usage of metadata instead of directives. For context see: https://blog.logrocket.com/graphql-directives-are-underrated/

[craicoverflow]

It's worth adding that GraphQLCRUD.org has not been update so the CRUD patterns you see there are not a reflection of how CRUD will be implemented. https://github.com/aerogear/graphback/issues/1031 is a more close depiction of how this will look.

As for metadata versus annotations - yes we do not really use directives. The blog @wtrocki is good for context becauuse it really shows for directives are a fantastic and powerful thing for providing additional runtime transformations.

We use metadata to bootstrap the server during startup, and once it is running the directives are no longer used.

[danielrearden]

Thanks for the quick follow-up. I'm excited to see the spec and would love to contribute in any way I can. I'm also happy to see you're not using the OpenCRUD/Prisma pattern for filtering and aligning more with how Hasura does it, which is what I did as well. I'm open to tweaking some of Sqlmancer's API to align it with the spec, we just need to identify the necessary changes. I might have some suggestions as well if you're open to input.

The main reason Sqlmancer uses (schema) directives is because they are mechanism for applying schema transformations at build time that's already supported by most libraries in the Node.js ecosystem -- apollo-server, graphql-modules, neo4j-graphql-js, etc. I actually considered using graphql-metadata when I first stumbled across Graphback and saw it in action. However, decorating the schema that way instead of using directives would mean having to add an extra step for explicitly transforming the schema and at the time I wasn't sure how well that would play with some of those existing libraries (GraphQL Modules in particular).

While I'm open to possibly moving away from using directives, it may not strictly be necessary just to take advantage of Sqlmancer's query engine. Under the hood, Sqlmancer extracts the metadata from the relevant directives into a config object and then uses that object to build out the client and its TypeScript type definitions. So Graphback can use graphql-metadata and we can similarly convert that metadata into a Sqlmancer config object and build a client from that.

[danielrearden]

FWIW, I've split off a branch to experiment with replacing directives with annotations. Using annotations would potentially make Sqlmancer compatible with code-first libraries like graphql-compose and Nexus, so I think it's definitely worthwhile.

[wtrocki]

Yes that is the main reason why we moved out of the directives. We could get to use tools like GraphQL-inspector on the models out of the box etc.

[danielrearden]

The one obstacle I've hit so far is the lack of multi-line string support in the current implementation of graphql-metadata. I don't know if there's an easy way to implement that. I made it work with doctrine and json5 for now.

[craicoverflow]

One of our team members also encountered this limitation recently! It should be easy enough I think, we would just need to have some special parentheses the parser can identify as a JSON object, which can be single or multine.

""" @model """
type Note {
  id: ID!
  title: String!
  description: String
  """
  @oneToMany field: 'note'
  @multiline ({
    hello: {
      world: [
        1,
        2,
        3
      ]
    }
  })
  """
  comments: [Comment]!
}

Hey @wtrocki - I might take a look at this today at some stage.

[wtrocki]

@craicoverflow Let's create issue in the metadata repo. People using this outside graphback and it will be nice to see if we get some comments.

Also I will personally prefer

@multiline(hello: {
})

It is easy to write a parser for this

[wtrocki]

Also we need to add motivation to the readme so it will be easier for people to understand why they can use it. Sorry for offtopic. Let's continue conversation on the new issue.

On SQLmancer I will try to take look on spikes on the weekend.

[craicoverflow]

I've created https://github.com/aerogear/graphql-metadata/issues/6 where we can continue the discussion

[danielrearden]

@wtrocki Here is the branch with my initial implementation of annotations. You can see example usage here. The exact syntax can tweaked to reflect graphql-metadata if those changes are implemented.

[wtrocki]

@danielrearden Very impressive work. I really like the simplicity of the Sqlmancer and how it resolves a couple of problems we have.

@craicoverflow would you have time to quickly take look into this so we could be on the same page.

[wtrocki]

Closed by mistake.

My thoughts are:

• Like that developers get control over crud names this gives a lot of the capabilities. We actually plan to have something like this for the https://graphqlcrud.org

• Relationships are no brainer. Simple to use (not sure about the cases we have)

• There is a lots of magic happening in the schema that is up to developer to like it

• It has very advanced data connections:

"""
    @relate {
      on: [{ from: "actor_id", to: "actor_id" }, { from: "film_id", to: "film_id" }],
      through: "film_actor",
      pagination: "OFFSET",
    }
    @paginate
    @many 

[wtrocki]

Knowing the amount of directives that are used it seems like we should prioritize building multiline directive for metadata. Instead of inventing new standard we can simply use directive format:

relate(on: etc)

[danielrearden]

FWIW, I agree -- extracting out the actual description and then parsing the rest as valid GraphQL syntax would be pretty simple and could then leverage GraphQL's existing tools for parsing and validation. Each annotation can have a faux directive definition associated with it that the parsed AST could be validated against.

[wtrocki]

@danielrearden Thank you so much for doing such amazing work with this PR! We are trying to do following things;

• Enable directive syntax in metadata
• Evaluate your library - you can expect some comments or PR (we can help you to review this lib etc.
• Propose possible integration overtime - the format is unknown - we have different approaches and target audience.
• Ping, you on the GraphQLCRUD spec so you can take look if you are interested.

Many different areas we can work together so thank you so much for reaching out. Graphback is not the only project we do so from outside things might look little bit slow at the moment.

[danielrearden]

No worries and thanks for your feedback so far. That branch is still very much a WIP -- I'm still iterating over some things and I'm going to be blocked by the changes to graphql-metadata anyway.

If we can align on the CRUD spec at some point, that'd be awesome so I can make the necessary changes to Sqlmancer's API.

Also, as you're looking over the library, please also be aware there's still a handful of features I'm hoping to add in the near future.

[wtrocki]

Sqlmancer is SQL oriented (mostly postgress) We have made our release (0.14.0) and going to refine things now on runtime level. Daniel is the most prolific GraphQL personas and will maintain SQLMancer

Update from backlog cleanup meeting.

1. Sqlmancer is one of the best libraries out there for direct data access and crud/graphql level
2. It addresses most of the problems in very comprehensive way.
3. We can actually learn and adjust GraphQL CRUD based on what SQLmancer has.
4. Graphback has perfect architecture to utilize SQLmancer - We can reuse data layers.

Risks

• Diverge between MongoDB and Postgres - Mongo will require in house data layers which is ok
• Sqlmancer seems to be evolving now towards different architecture.

Suggested way to move forward

Investigate if we can dissect SQLmancer into a smaller packages that can be reused within graphback that will deal with the data and service layers. We can work closely on data mapping requirements and annotations/directives.

[danielrearden]

Thanks for the update @wtrocki. FWIW, I'm not necessarily opposed to adding support for MongoDB (and maybe even other NoSQL databases like Neo4j). It wouldn't require huge changes to the database client API since the code that actually uses Knex is pretty isolated and could be swapped out fairly easily.