Build and Deploy a GraphQL API to the Edge with MySQL and PlanetScale — Part 9

Fetching everything from the database once it grows will become very expensive and add unnecessary load to your backend. Let's instead explore adding cursor-based pagination to the products query.

We'll first update the products query to accept multiple arguments;

• first

• last

• before

• after

We can use these arguments to fetch the first or last X records before or after an entry. The value of first and last will be the ID of the record.

g.query('products', {
  args: {
    first: g.int().optional(),
    last: g.int().optional(),
    before: g.string().optional(),
    after: g.string().optional()
  },
  resolver: 'products/all',
  returns: g.ref(product).optional().list().optional()
})

Next, we will update the ProductsAll function to destructure first, last, before and after from the args and execute the applicable SQL statement depending on what combination of arguments are passed to the query.

import { connect } from '@planetscale/database'
import { GraphQLError } from 'graphql'
import { config, options } from '../../lib'

const conn = connect(config)

export default async function ProductsAll(_, { first, last, before, after }) {
  try {
    let results

    if (first !== undefined && after !== undefined) {
      results = await conn.execute(
        'SELECT * FROM products WHERE id > ? ORDER BY id ASC LIMIT ?',
        [after, first],
        options
      )
    } else if (last !== undefined && before !== undefined) {
      results = await conn.execute(
        `SELECT * FROM (
          SELECT * FROM products WHERE id < ? ORDER BY id DESC LIMIT ?
        ) AS sub ORDER BY id ASC`,
        [before, last],
        options
      )
    } else if (first !== undefined) {
      results = await conn.execute(
        'SELECT * FROM products ORDER BY id ASC LIMIT ?',
        [first],
        options
      )
    } else if (last !== undefined) {
      results = await conn.execute(
        `SELECT * FROM (
          SELECT * FROM products ORDER BY id DESC LIMIT ?
        ) AS sub ORDER BY id ASC`,
        [last],
        options
      )
    } else {
      throw new GraphQLError(
        'You must provide one of the following arguments: first, last, (first and after), or (last and before)'
      )
    }

    return results?.rows || []
  } catch (error) {
    console.log(error)

    return []
  }
}

Give it a try! You will now need to pass the argument first when querying for products:

{
  products(first: 5) {
    id
    name
    slug
    price
    onSale
  }
}

Update the query to fetch the first 5 products after the ID 2:

{
  products(first: 5, after: "2") {
    id
    name
    slug
    price
    onSale
  }
}

That's it! We've successfully built a GraphQL API using MySQL and PlanetScale.

👉 Continue to Part 10