CODEWITHSUNDEEP
node.jsgraphqlapollo-serverpg-promise
Dan Fairaizl
Dan Fairaizl

Reputation: 2170

Sharing a pg-promise task across parts of an http request

I am using pg-promise in a GraphQL application and because of the nested/iterative nature of the resolvers each HTTP request makes a lot of database queries.

So I am wondering is there is a more effective way to share a connection from the connection pool as the resolvers are collecting data?

I understand that a pg-promise task is only alive for the callback of the function and I don't see any other way to chain the queries (as documented here).

Example

GraphQL Query:

{
  users {
    files {
      name
      date
    }
  }
}

Resolvers example when using Apollo Server

Query: {
    users: (obj, args, context, info) => {
      return context.db.manyOrNone('select id from users')
    }
 }

and 

Users: {
  files: (obj, args, context, info) => {
    const userId = obj.id;
    return context.db.manyOrNone('select * from files where user_id = $1', userId);
  }
}

This will generate lots of SQL queries if there are lots of users for instance.

NOTE

I'm aware of techniques like dataloader to address problems like N+1 Select but I cannot afford to rearchitect this application at the moment and simply being more efficient with database connections would be a huge performance win.

Thank you.

Upvotes: 0

Views: 251

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

Each HTTP endpoint and each database connection from the pool are meant to be asynchronous.

If you attempt to reuse the same database connection across multiple HTTP endpoints, those will be blocking each other, whenever they need access to the database, which is not good.

And if the number of connections in the pool is less than the number of HTTP endpoints that access the database, you've got yourself a poorly-scalable HTTP service. You need the number of connections at least to match that of the HTTP endpoints.

So what you are looking for - sharing a database connection across multiple HTTP endpoints is a bad idea to begin with.

And if you want to group multiple data resolvers within a single HTTP request, you can unify the processing logic within a single task (see Tasks).

There is also manual connection, via method connect, but I wouldn't recommend it for general connection reuse, as it is there for specific cases, can be error-prone otherwise and invalidates the idea of automated connections.

Upvotes: 1

Related Questions