How to properly use transactions (relational db) with N+1 queries

[stringbeans]

Suppose I have a mutation:

mutation {
  updateStudent(studentId: 5, firstname: "joe") {
    studentId,
    firstname,
    studentTags {
      value
    }
  }
}

Notice that after the mutation I want to get back the Student object and embedded in the Student object is a one-to-many relationship with studentTags.

Now, to properly handle this we'd want to wrap the update operation as well as the following fetch query in a transaction...

however following graphql's principle, the fetch after the insert SHOULD only fetch the corresponding student and not the relations. Eventually, the resolver for the StudentTag object will run which will then fetch the studentTags for the student object. This is problematic however because now its outside of the transaction (assuming i use a transaction closure) OR i simply dont know when to call transaction.commit() because I don't know when the parent Student node has been fully resolved with all its children.

Am I missing something? Is the only option to parse the graphql mutation query after the insert to determine what relations I need to eagerly load? Is there a hook I can use for when all children nodes of a parent node have been fully resolved so I can call a transaction.commit() ?

I suspect anyone who uses a relational db with graphql will eventually run into this issue... I'm currently using Sequelize as my ORM if that helps.

Thanks in advance :)

[smolinari]

I am also interested in the answer to this question.

Scott

[migueloller]

@stringbeans,

I'd love to understand the problem you're facing a bit more. 😄

Are transactions fully necessary here? Could you potentially use the RETURNING clause to get back the student and then use DataLoader to batch requests for each tag? Why is a transaction needed to update a student? If you don't want to use DataLoader (or do it in 1 request instead of 2) could you achieve the desired result with both RETURNING and JOIN?

[stringbeans]

hi @migueloller !

the RETURNING clause doesn't exist in mysql unfortunately :(

regarding why we want this update operation to be in a transaction, consider the following... when we update a student we're doing a couple things:

1. Perform update operation on the student
2. Return the newly updated student object from the database

We want this in a transaction because this should be an atomic action. Consider if you have a master/slave setup, the data you read in step #2 could potentially not match the data updated in step #1

This issue is outside of what the dataloader is meant to do

[wincent]

Consider if you have a master/slave setup, the data you read in step 2 could potentially not match the data updated in step 1

I think one way this is traditionally handled (independently of the use of GraphQL or not) is at the database adapter layer: route reads to the master instead of slave after any write, for the duration of the connection.

[stringbeans]

@wincent thats interesting ive never actually heard of that strategy before. i dont want master/slave replication to be a concern in our code base, so we use a mysql proxy (proxysql) to configure what reads/writes go where... but i do not believe our code can ensure the same connection is used between the write and read with connection pooling

[migueloller]

@stringbeans, what are you using for pooling and handling server requests? Perhaps if the database adapter layer is instantiated for each request and the connection is forced to be the same the issue might be solved?

[stringbeans]

@migueloller we are using the Sequelize orm to manage connection pooling. instantiating a connection for each request somewhat goes against what connection pooling is meant to achieve..

[migueloller]

@stringbeans, you don't need to open a new connection for each request but simply ensure you use the same one for the life of the request. This way you still take advantage of connection pooling by having future request reuse idle pools. What's important is to make sure 1 request uses the same pool for its entire lifespan.

Sequelize's query has an option to force use of the write pool. Perhaps you can force the SELECT right after the UPDATE to use the write pool? See the useMaster option here.

[stringbeans]

@migueloller thanks i will look into that! do you have any suggestions on how to pass the connection used in the mutation to the sub queries that happen after it? is through the context object the best way?

[migueloller]

@stringbeans, context sounds like the best way to pass down the connection (or your database adapter). You can also pass the session (if you need to handle authorization) from the incoming request. I'm not sure if Sequelize will give you access to the connection in use though.

[stringbeans]

@migueloller yeah, im beginning to rethink my architecture...

originally, i wanted our mysql proxy to handle the read/write splitting such that the code base wouldnt have to even care about whether things should go to master/slave. however, it sounds like the code MUST make this decision in order for subsequent queries to hit master after an update

[migueloller]

@stringbeans, hmm I see. I can't really make specific comments without knowing your architecture. But at the risk of pointing out the obvious... Have you tried putting the transaction together into an SQL procedure and calling that (if MySQL has those)? What if you took a look at the SQL in the proxy and then routed the query to the appropriate server based on that? Perhaps the queries can be prepared and what you're doing from the code base is just picking the right ones and the proxy can handle the rest?

[stringbeans]

@migueloller its a bit trickier than using a procedure... because of the dynamic nature of graphql (one of the positives!), after the UPDATE/INSERT operation graphql will kickoff a query operation to fetch fields specified by the user. that query will then fire off the resolvers on different objects depending on whats requested and so i cannot eagerly load the relations all in one query after the UPDATE/INSERT operation. does that make sense?

i think my solution right now is to trigger a flag in the context for all mutations so that subsequent queries performed AFTER the mutation will know to use master for all the fetches

[wmertens]

@stringbeans you can also let the mutation return a fully resolved object, and in the subresolvers you check if the root object already has the data…