Integrating with Databases

Working with a database with GraphQL can sometimes be a little difficult and some principles have to be followed to avoid performance or safety issues.

In this guide, we will cover a few advices to work with databases. The examples will use the Postgres node client but the concepts apply to every database.

Use Only One Client per Request

The first common error is to use a connection pool directly in the resolvers.

const pool = new Pool({ maxClients: 10 })
 
const resolvers = {
  Query: {
    bills: () => pool.query(SELECT_ALL_BILLS)
  },
  Bill: {
    payer: bill => pool.query({ text: SELECT_PAYER_BY_ID, values: [bill.payer_id] })
  }
}

In this example, you can very quickly totally starve your API with a simple request:

{
  bills {
    payer
  }
}

Because we use directly the pool of connection, each resolver will request a client to do its request. If you have, for example 100 bills, this request can starve the connection pool for other users. The server will be stuck and unresponsive for any incoming request while it is not fully resolved.

The better way to avoid this is to open only one client per request. With Envelop, we can easily create a plugin that adds a client to the context add releases it at the end of the request execution.

import { isAsyncIterable } from '@envelop/core'
import { useSchema } from './use-schema'
 
const pool = new Pool({ maxClients: 10 })
 
const databaseClientPlugin = {
  async onExecute({ extendContext }) {
    extendContext({ client: await pool.getClient() })
    return {
      async onExecuteDone({ result, args: { contextValue } }) {
        if (isAsyncIterable(result)) throw TypeError('Not implemented')
        await contextValue.client.release(result.errors)
      }
    }
  }
}
 
const resolvers = {
  Query: {
    bills: (_, _2, { client }) => client.query(SELECT_ALL_BILLS)
  },
  Bill: {
    payer: (bill, _, { client }) =>
      client.query({ text: SELECT_PAYER_BY_ID, values: [bill.payer_id] })
  }
}
 
const getEnvelop = envelop({
  plugins: [useSchema(/*...*/), databaseClientPlugin]
})

Wrap Every Request in a Transaction

Now that we have one unique client during all the execution phase, we can add some fancy features.

For more safety, every mutation should be encapsulated in a transaction. Doing it by hand is difficult, error-prone and easy to forget. The better way to do it is to automatically wrap every mutation in a transaction.

Let’s see how we can change our plugin to implement this:

const databaseClientPlugin = {
  async onExecute({ extendContext }) {
    const client = await pool.getClient()
    extendContext({ client })
    await client.query('BEGIN')
 
    return {
      async onExecuteDone({ result, args: { contextValue } }) {
        const error = result.errors
        try {
          if (isAsyncIterable(result)) throw TypeError('Not implemented')
          if (result.errors) throw result.errors
          await contextValue.client.query('COMMIT')
        } catch (err) {
          error = err
          await contextValue.client.query('ROLLBACK')
        } finally {
          await contextValue.client.release(error)
        }
      }
    }
  }
}

Enforcing Read-Only Queries

Now that we can wrap every request in a transaction, we can do even better. For now, we are making a transaction for every request, but only mutations should need one.

We can configure transactions differently between query and mutation.

const databaseClientPlugin = {
  async onExecute({ extendContext, args: { contextValue } }) {
    const client = await pool.getClient()
    extendContext({ client })
 
    // We can find in the context the kind of request we are executing
    if (contextValue?.operation?.operation === mutation) {
      await client.query('BEGIN')
    } else {
      await client.query('BEGIN READ ONLY')
    }
 
    return {
      async onExecuteDone({ result, args: { contextValue } }) {
        const error = result.errors
        try {
          if (isAsyncIterable(result)) throw TypeError('Not implemented')
          if (result.errors) throw result.errors
          await contextValue.client.query('COMMIT')
        } catch (err) {
          error = err
          await contextValue.client.query('ROLLBACK')
        } finally {
          await contextValue.client.release(error)
        }
      }
    }
  }
}

Transactions over Multiple Databases

Some servers will need to do some modifications on multiple databases in a single mutation.

We could just add our new client and just begin/release/rollback as usual. But what happens if an error occurs during the commit of the second database? You will have data updated in the first database, and data rollbacked in the second one, causing the system to be in an inconsistent state.

To solve this we can use Tow Phase Commit, which is supported by most databases except a few ones… (yes I’m looking at you Mongo).

import pre from '@changesets/cli/dist/declarations/src/commands/pre'
 
const databaseClientPlugin = {
  async onExecute({ extendContext, args: { contextValue } }) {
    const client1 = await pool1.getClient()
    const client2 = await pool2.getClient()
    const clients = [client1, client2]
    extendContext({ db1: client1, db2: client2, clients })
 
    // We can find in the context the kind of request we are executing
    if (contextValue?.operation?.operation === mutation) {
      await Promise.all(clients.map(client => client.query('BEGIN')))
    } else {
      await Promise.all(clients.map(client => client.query('BEGIN READ ONLY')))
    }
 
    return {
      async onExecuteDone({ result, args: { contextValue } }) {
        const error = result.errors
        const preparedCommits = new Map()
        try {
          if (isAsyncIterable(result)) throw TypeError('Not implemented')
          if (result.errors) throw result.errors
          const commits = Promise.allSettled(
            contextValue.clients.map(async client => {
              const commitId = uuid()
              preparedCommits.set(client, commitId)
              await client.query(`PREPARE COMMIT ${commitId}`)
            })
          )
 
          if (commits.some(commit => commit.some(result => result.status === 'rejected'))) {
            throw Error('Error during commit phase')
          }
        } catch (err) {
          error = err
          Promise.allSettled(
            contextValue.clients.map(async client => {
              const commitId = preparedCommits.get(client)
              if (commitId) await client.query(`ROLLBACK PREPARED ${commitId}`)
              else await client.query('ROLLBACK')
            })
          )
 
          if (commits.some(commit => commit.some(result => result.status === 'rejected'))) {
            throw Error('Error during rollback phase')
          }
        } finally {
          await Promise.all(clients.map(client => client.release))
        }
      }
    }
  }
}

Adding a GraphQL Response Cache