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

This section adds all the code needed to update product records inside the PlanetScale database.

Let's begin by updating the Grafbase Configuration to add a mutation that accepts the following arguments;

by — something we can use to pass an id or slug to target the product we want to update
input — the actual input of the fields we want to update, fields should be optional

Inside grafbase/grafbase.config.ts you will want to add the following:

const productUpdateInput = g.input('ProductUpdateInput', {
  name: g.string().optional(),
  slug: g.string().optional(),
  price: g.int().optional(),
  onSale: g.boolean().optional()
})

const productByInput = g.input('ProductByInput', {
  id: g.id().optional(),
  slug: g.string().optional()
})

g.mutation('productUpdate', {
  args: {
    by: g.inputRef(productByInput),
    input: g.inputRef(productUpdateInput)
  },
  resolver: 'products/update',
  returns: g.ref(product).optional()
})

Next, create the file grafbase/resolvers/products/update.ts and add the following:

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

const conn = connect(config)

export default async function ProductsUpdate(_, { by, input }) {
  let updateClauses: string[] = []
  let params: (string | number | boolean)[] = []
  let selectStatement: string = ''
  let selectParams: (string | number)[] = []
}

In the code above we assigned the arguments by and input to their own variables that we can use later on. We also set 4 new mutable variables:

updateClauses — the field names we want to update
params — the updated value for the fields
selectStatement — the id or slug field passed to the by argument
selectParams — the value that is either the entry id or slug

Next, we will fetch the input and for each of them check that the value matches the allowed types — string, number and boolean.

Object.entries(input).forEach(([field, value]) => {
  if (
    value !== undefined &&
    value !== null &&
    !(typeof value === 'object' && Object.keys(value).length === 0)
  ) {
    if (
      typeof value === 'string' ||
      typeof value === 'number' ||
      typeof value === 'boolean'
    ) {
      updateClauses.push(`${field} = ?`)
      params.push(value)
    }
  }
})

if (params.length === 0) {
  throw new GraphQLError('At least one field to update must be provided.')
}

If there is value we will push the field name and value to updateClases and params respectively. If there is no params at all, we will throw an error.

Next, we will follow the same rules as above for conditionally adding to the SQL statement and values that will be used to target the specific entry we want to update using the WHERE keyword.

let updateStatement = 'UPDATE Products SET ' + updateClauses.join(', ')

const byEntries = Object.entries(by)

if (byEntries.length > 1) {
  throw new GraphQLError('Only one of ID or Slug should be provided')
}

const [field, value] = byEntries[0]

if (
  value !== undefined &&
  value !== null &&
  (typeof value === 'string' || typeof value === 'number')
) {
  updateStatement += ` WHERE ${field} = ?`
  params.push(value)
  selectStatement = `SELECT * FROM Products WHERE ${field} = ?`
  selectParams = [value]
}

if (!selectStatement) {
  throw new GraphQLError('ID or Slug must be provided')
}

Once we're done constructing the SQL statement we can now move to actually executing it. This time we will use a transaction to update the record and select it so we can return the updated document to the user.

We could just return the updated fields instead of fetching them from the database after updating but if the user has requested more fields than those you have passed to update then you will get an error from GraphQL that a field is null when it shouldn't be.

Finally, if there's a row we will return it, otherwise, we'll return an empty list ([]):

const [_, results] = await conn.transaction(async (tx) => {
  const update = await tx.execute(updateStatement, params, options)
  const select = await tx.execute(selectStatement, selectParams, options)

  return [update, select]
})

return results?.rows[0] ?? null

Similar to creating records, we need to pass the custom options function. Instead of copying/pasting this code to every file, you should update the file grafbase/lib.ts and add the export there.

Don't forget to import cast from @planetscale/database:

import { cast } from '@planetscale/database'

export const options = {
  cast(field, value) {
    switch (field.name) {
      case 'id': {
        return String(value)
      }
      case 'onSale': {
        return Boolean(value)
      }
      default: {
        return cast(field, value)
      }
    }
  }
}

Now all that's left to do is update the create.ts and update.ts files to contain the options import:

import { config, options } from '../../lib'

You should now have an update resolver that looks something like this:

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

const conn = connect(config)

export default async function ProductsUpdate(_: unknown, args: any) {
  const { by, input } = args

  let updateClauses: string[] = []
  let params: (string | number | boolean)[] = []
  let selectStatement: string = ''
  let selectParams: (string | number)[] = []

  Object.entries(input).forEach(([field, value]) => {
    if (
      value !== undefined &&
      value !== null &&
      !(typeof value === 'object' && Object.keys(value).length === 0)
    ) {
      if (
        typeof value === 'string' ||
        typeof value === 'number' ||
        typeof value === 'boolean'
      ) {
        updateClauses.push(`${field} = ?`)
        params.push(value)
      }
    }
  })

  if (params.length === 0) {
    throw new GraphQLError('At least one field to update must be provided.')
  }

  let updateStatement = 'UPDATE Products SET ' + updateClauses.join(', ')

  const byEntries = Object.entries(by)

  if (byEntries.length > 1) {
    throw new GraphQLError('Only one of ID or Slug should be provided')
  }

  const [field, value] = byEntries[0]

  if (
    value !== undefined &&
    value !== null &&
    (typeof value === 'string' || typeof value === 'number')
  ) {
    updateStatement += ` WHERE ${field} = ?`
    params.push(value)
    selectStatement = `SELECT * FROM Products WHERE ${field} = ?`
    selectParams = [value]
  }

  if (!selectStatement) {
    throw new GraphQLError('ID or Slug must be provided')
  }

  try {
    const [_, results] = await conn.transaction(async tx => {
      const update = await tx.execute(updateStatement, params, options)
      const select = await tx.execute(selectStatement, selectParams, options)

      return [update, select]
    })

    return results?.rows[0] ?? null
  } catch (error) {
    return null
  }
}

With the Grafbase development server running, open Pathfinder at http://localhost:4000 and execute the following mutation:

mutation {
  productUpdate(by: { id: "1" }, input: { name: "New name" }) {
    id
    name
    slug
    price
    onSale
  }
}

Make sure to pass a valid id or slug to the by argument.

👉 Continue to Part 6

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

productUpdate mutation