Drizzle re-select on read only replica

[hayes]

Hey all!

Was going to post an issue but remembered this was here! I've found a solution to my problem, but wanted to post here as info for someone that may stumble upon this as well, and also to get it across the eyes of Hayes if you think something needs to be changed or if you think its good as is!

---

I encountered an error after performing an insert, which I discovered was related to a read-after-write inconsistency wherein drizzle inserted the record into the primary (write) database instance, and, immediately after, performed a read to return the newly created record from the read replica instance. Because of the replication lag between our (aws rds) instances in the cluster (~10-100ms), the read couldn't find the fresh record as it was not yet in the read replica.This resulted in the following error:

Model foo(foo's id) not found

My solution was just to override the specific drizzle query to use the primary db for both the read and write like so:

await db.$primary // $primary is the only addition
.insert(foo)
.values({
    ...values
})
.returning();

After looking into this some more, we've realized that it's actually likely drizzleWithFieldInput that's causing this extra select, as we have an update mutation that's doing very similar logic, but just with fieldWithInput (which does not make an extra select)...

Originally posted by @MMShep97 in #1439 (comment)

[hayes]

I think to properly diagnose this, I need some more more info on whats happening.

I can describe at a high level how the plugin is supposed to work:

1. In a drizzle field, pothos looks at the what was selected in the original graphql query, and creates query that includes as much of the data needed to resolve that query as possible

• Part of this process includes tracking what is and is not part of that initial selection
• not using that query in your resolver to return the right data will lead to bugs

2. The resolver is supposed to use that query as part if a drizzle call, and return the selected data
3. As pothos resolves nested fields, it looks to see what was supposed to have been already loaded based on that initial query, and also checks if that data is actually there. If the data is present, and was expected to be provided by the initial query, it can just return it
4. For fields where either the data could not be included as part of the initial selection, or the data was not present for some other reason:

• Pothos will re-load the parent object, adding in any missing selections
• This re-selection processes uses the primary keys for the table in question

An example of this might be a query like:

query {
  user(id: 1) {
    id
   name
    posts(limit: 1) {
      id
    }
    morePosts: posts(limit: 2) {
      id
      title
    }
  }
}

In this example, you can't select both posts(limit: 1) and morePosts(limit: 2) in the initial resolver for a user.

So the initial query might be something like

{
  where: { id: 1 },
  columns: { id: true, name: true },
  with: { posts: { limit: 1, columns: { id: true } }
}

When morePosts is resolved, it will create another query like:

{
  where: { id: 1 },
  columns: { id: true }, // only select the primary key for additional selections
  with: { posts: { limit: 2, columns: { id: true, title: true } } // select the posts needed for `morePosts field`
}

In your case, it looks like you are using a mutation, which isn't supported in rqbv2, which suggests to me that you are NOT using the query in your resolver.

To fully understand the issue, knowing what fields are selected in the query, and how those fields are defined would be helpful

[MMShep97]

Thanks for the response!! JFYI we're still on 0.8.1; haven't moved to using the RQBV2 changes yet.

So we did some more debugging into the internals and figured out that it is indeed only making that under-the-hood select query for our mutation that is doing a db insert w/o using the pothos query arg at all. If we instead explicitly utilize the provided query fn to fetch our inserted record, the internal select won't occur, and we can override the db client to use our primary node instead of the read replica, which was causing us issues.

For a better view, here's our dumbed down code:

export const posts = pgTable(
    'posts',
    {
        id: uuid().primaryKey().defaultRandom(),
        // random post values... 
        createdAt: timestamp().defaultNow().notNull(),
        updatedAt: timestamp()
            .defaultNow()
            .notNull()
            .$onUpdate(() => new Date()),
        deletedAt: timestamp(),
    }
);

export const createMutation = (client: DBClient, t: MutationFieldBuilder) => {
    const {db} = client;

    return t.drizzleFieldWithInput({
        authScopes: {
            edit: true,
        },
        type: 'posts',
        input: createMutationInputs(t.input),
        resolve: async (query, _root, {input}, ctx) => {
            // validateInputs(...)

            const inserted = await db
                .insert(posts)
                .values({
                    ...input,
                    // ...
                })
                .returning({id: posts.id}); // previously just returning()

            // BELOW IS the added code to work around our issue

            const createdId = inserted[0]?.id;
            invariant(createdId, 'Inserted post must have an ID');

            // if we don't explicitly query, pothos will query behind the scenes
            // Using $primary to ensure we are querying the write database
            return db.$primary.query.posts.findFirst(
                query({
                    where: (post, {eq}) => eq(post.id, createdId),
                }),
            );
        },
    });
};

This re-selection processes uses the primary keys for the table in question

It sounds like this is probably what was happening. We are simply inserting a record and getting back that record to display to the user (ideally in one sql query). Not sure if there's a better way to be doing our use case though so we don't have to make that 2nd database call -- our api isn't that sensitive, so this is probably fine, but more for curiosity and doing the most ideal thing :P

[hayes]

Calling query is what registers the fields that have been selected with pothos for the current query.

If you don't call the query function, pothos assumes that none of the data returned from the resolver is safe to use when resolving the nested fields.

If you call query, but don't actually use the resulting query object in a drizzle call, pothos will incorrectly assume you loaded everything expected, and just attempt to use the data returned by the resolver to resolve the rest of the query.

I think there are a number of different things you can do, depending on how correct you want this to be.

Just call query and don't use it

    return t.drizzleFieldWithInput({
        authScopes: {
            edit: true,
        },
        type: 'posts',
        input: createMutationInputs(t.input),
        resolve: async (query, _root, {input}, ctx) => {
           // Call query, so pothos thinks we selected the right data
           void query({}) 
            const inserted = await db
                .insert(posts)
                .values({
                    ...input,
                    // ...
                })
                .returning({id: posts.id}); // previously just returning()
    });
};

This will work as long as long the nested query does not select any relations, or fields with custom selections not present in inserted.

If the GraphQL query selects additional relations, pothos may attempt to re-select the returned post with the requested relations which will fail against the read replica if it hasn't been synchronized in time.

Insert, and then re-query in the resolver

This is the pattern you suggested as a fix.

This is a big improvement over the solution mentioned above. This will correctly handle MOST nested selections, but can still run into issues in some edge cases.

For example, if posts have comments and the GraphQL query selections comments twice with different limits, only 1 of the comments queries can be resolved at once. Since this is a new post, it would not have comments, but pothos doesn't know that.

It will select comments(limit: 1) initially, and then when it goes to resolve the second selection of comments, it will re-query the post with comments(limit: 2). This second query would fail.

Force pothos to use the the write client for mutations

In addition to re-querying in the resolver, you can customize which client pothos uses:

This can be a little more complicated but you could do something like this:

const builder = new SchemaBuilder<PothosTypes>({
    plugins: [DrizzlePlugin],
    drizzle: {
      client: (ctx) => ctx.isMutation ? writeClient : readOnlyClient
      getTableConfig,
      relations,
    },
  });

export default createYoga({
  schema: builder.toSchema({}),
  graphqlEndpoint: '/api/graphql',
  context: (ctx) =>
    {
      const parsedQuery = parse(ctx.params.query!).definitions[0]
      return {
      ...restOfContext,
      isMutation: parsedQuery.kind === Kind.OPERATION_DEFINITION && parsedQuery.operation === 'mutation'
    }
  },
});

Maybe a better way to do this is to explicitly switch to the write client:

```ts
const builder = new SchemaBuilder<PothosTypes>({
    plugins: [DrizzlePlugin],
    drizzle: {
      client: (ctx) => ctx.getClient()
      getTableConfig,
      relations,
    },
  });

export default createYoga({
  schema: builder.toSchema({}),
  graphqlEndpoint: '/api/graphql',
  context: (ctx) =>
    {
     let currentClient = readOnlyClient
      return {
      ...restOfContext,
      getClient: () => currentClient,
      setClient: (client) => currentClient = client,
    }
  },
});

    return t.drizzleFieldWithInput({
        authScopes: {
            edit: true,
        },
        type: 'posts',
        input: createMutationInputs(t.input),
        resolve: async (query, _root, {input}, ctx) => {
            ctx.setClient(writeClient)
            // run mutation
    });
};

[hayes]

JFYI we're still on 0.8.1;

The latest version of the plugin only works with RQBV2, and previous versions of this plugin had a LOT of issues that have since been resolved. I would highly recommend upgrading before investing too much in improving things with the older version

[MMShep97]

Thanks for offering so many suggestions! And yep, we're planning on updating to the latest version asap.