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

In this guide we'll build a GraphQL API with Grafbase, and persist data to a MySQL database managed by PlanetScale.

Grafbase simplifies the process of creating and implementing your personalised GraphQL API at the edge while ensuring complete end-to-end type safety.

By using resolvers and connectors, Grafbase allows seamless integration with any data source. Additionally, you can take advantage of features such as edge caching, authentication and permission rules configuration, serverless search functionality, and more.

Grafbase's versatility extends to local development as well, thanks to the Grafbase CLI. Each Git branch comes with its own preview deployment, facilitating convenient testing and collaboration.

PlanetScale simplifies database management and scaling. It ensures high availability, seamless replication, and effortless deployment across multiple regions.

With features like automatic sharding, intelligent load balancing, and instant schema changes, PlanetScale simplifies integration and offers robust security measures, including end-to-end encryption and comprehensive access controls.

From startups to enterprises, PlanetScale provides a reliable and scalable solution for managing databases, allowing you to focus on building innovative applications and services.

Let's begin by creating a new Grafbase project. If you have an existing frontend application that you want to create a GraphQL API for, you must run that inside of that project's root directory.

npx grafbase init

This command will ask whether you want to create a Grafbase project using GraphQL SDL or TypeScript as it's configuration type.

We'll next install the graphql dependency so we can properly throw GraphQLError's inside resolvers later on.

npm install graphql

That's it! You now have a Grafbase project setup and ready to use.

PlanetScale is the best place to serve and scale your MySQL Database. You'll need to create an account, create a database, add a products table, and obtain the connection details to continue.

1. Sign up for a PlanetScale account
2. Create a new database
3. Create a new branch and open the web console to execute the following SQL statement:

CREATE TABLE `products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `price` INT NOT NULL DEFAULT 0,
  `onSale` BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (`id`),
  INDEX slug_index (`slug`),
);

4. Enable safe migrations for the main branch

5. Open a deploy request and merge the changes into main

We're going to use the serverless PlanetScale database driver inside our Grafbase Edge Resolvers. Run the following command to install the @planetscale/database dependency:

npm install @planetscale/database

Now open the connection settings inside your PlanetScale database and obtain copy the values.

Now create the file grafbase/.env and paste the contents from above:

DATABASE_HOST=
DATABASE_USERNAME=
DATABASE_PASSWORD=

Finally create the file grafbase/lib.ts and add the following config export:

const config = {
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
}

We'll use this next in our first mutation resolver!

A GraphQL mutation is a type of operation in GraphQL that modifies data on the server. In this guide we'll use GraphQL mutations to create, update and delete records from our PlanetScale database.

A GraphQL mutation typically looks like this:

mutation {
  doSomething(a: String, b: Int) {
    someField
  }
}

• doSomething is the name of the mutation
• a and b are names of the arguments passed to mutations
• String and Int are the data types of the arguments
• someField is the name of a field returned by the mutation

We'll be creating a GraphQL API to store products in our PlanetScale database.

Let's begin by creating a mutation to create new products in the database.

If you selected TypeScript as the configuration type when using grafbase init you will have the file grafbase/grafbase.config.ts. Inside here we will add the type for Product with the following fields:

• id
• name
• slug
• price
• onSale

import { config, graph } from '@grafbase/sdk'

const g = graph.Standalone()

const product = g.type('Product', {
  id: g.id(),
  name: g.string(),
  slug: g.string(),
  price: g.int(),
  onSale: g.boolean(),
})

export default config({
  graph: g,
})

Below the Product definition we can now add the createProduct mutation. We'll need to create an input type used by the mutation and configure the mutation itself, which points to the resolver file:

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

g.mutation('productCreate', {
  args: { input: g.inputRef(productCreateInput) },
  resolver: 'products/create',
  returns: g.ref(product).optional(),
})

Now let's create the code that runs when the GraphQL mutation productCreate is executed. This code is known as a GraphQL resolver.

Create the file grafbase/resolvers/products/create.ts and begin by exporting a default async function, it can be named whatever you like but we'll call it ProductsCreate.

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

const conn = connect(config)

export default async function ProductsCreate(_, { input }) {
  const fields: string[] = []
  const placeholders: string[] = []
  const values: (string | number | boolean)[] = []
}

In the code above we will destructure input from the second arguments passed to the resolver function. This input argument will be populated by the fields passed to the GraphQL mutation productCreate.

Also in this code we create three new variables — fields, placeholders and values that we will use to collect the necessary data to pass onto the database request.

Next for each of the entries in the input object we will check to see if the value is one of the expected types — string, number or boolean. These values match the field types of the Product type we created previously.

If the value is present and one of those types then we will push the field name onto the fields array, add a new placeholder into the placeholders array and add the value to the values array.

Object.entries(input).forEach(([field, value]) => {
  if (
    value !== undefined &&
    value !== null &&
    (typeof value === 'string' ||
      typeof value === 'number' ||
      typeof value === 'boolean')
  ) {
    fields.push(`\`${field}\``)
    placeholders.push('?')
    values.push(value)
  }
})

Next we use the fields and placeholders inside of the SQL statement. We'll begin the statement by using INSERT INTO products followed by the fields joined using , and an empty space then add the placeholders (e.g. ?, ?, ?, ?).

const statement = `INSERT INTO products (${fields.join(
  ', ',
)}) VALUES (${placeholders.join(', ')})`

Now we can pass this statement to conn.execute and pass along the values:

const { insertId } = await conn.execute(statement, values)

One thing to note is that the id and onSale values will both be of the an integer type. What we will need to do is cast these values into the correct type and we can do that by passing a third argument to conn.execute():

const { insertId } = await conn.execute(statement, values, {
  cast(field, value) {
    switch (field.name) {
      case 'id': {
        return String(value)
      }
      case 'onSale': {
        return Boolean(value)
      }
      default: {
        return cast(field, value)
      }
    }
  },
})

Once you added a basic try/catch block to wrap the request and surface any database errors, you should have something that looks like this:

import { cast, connect } from '@planetscale/database'
import { config } from '../../lib'

const conn = connect(config)

export default async function ProductsCreate(_, { input }) {
  const fields: string[] = []
  const placeholders: string[] = []
  const values: (string | number | boolean)[] = []

  Object.entries(input).forEach(([field, value]) => {
    if (
      value !== undefined &&
      value !== null &&
      (typeof value === 'string' ||
        typeof value === 'number' ||
        typeof value === 'boolean')
    ) {
      fields.push(`\`${field}\``)
      placeholders.push('?')
      values.push(value)
    }
  })

  const statement = `INSERT INTO products (${fields.join(
    ', ',
  )}) VALUES (${placeholders.join(', ')})`

  try {
    const { insertId } = await conn.execute(statement, values, {
      cast(field, value) {
        switch (field.name) {
          case 'id': {
            return String(value)
          }
          case 'onSale': {
            return Boolean(value)
          }
          default: {
            return cast(field, value)
          }
        }
      },
    })

    return {
      id: insertId,
      ...input,
    }
  } catch (error) {
    console.log(error)

    return null
  }
}

That's all we need to successfully create a product in the database using a GraphQL mutation.

Now run the Grafbase local development server using the command below:

npx grafbase dev

Next open Pathfinder at http://localhost:4000 and execute the following mutation:

mutation {
  productCreate(
    input: { name: "Shoes", slug: "shoes", price: 1000, onSale: true }
  ) {
    id
    name
    slug
    onSale
    price
  }
}

You can repeat this mutation as many times as you like with unique content. Make sure you don't use an slug.

In this section we'll add all of 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.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 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(_: unknown, args: any) {
  const { by, input } = args

  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 a 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 it from the database after updating but if the user has requested more fields that 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 copy/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.

Now let's create the delete mutation resolver. We will first update the Grafbase Configuration to include the mutation productDelete.

This mutation uses the ProductByInput input type we created above for the updateProduct mutation. This mutation will also return a new type — ProdudctDeletePayload that has a deleted field that returns a boolean to represent the success of the deletion request.

const productDeletePayload = g.type('ProductDeletePayload', {
  deleted: g.boolean(),
})

g.mutation('productDelete', {
  args: {
    by: g.inputRef(productByInput),
  },
  resolver: 'products/delete',
  returns: g.ref(productDeletePayload).optional(),
})

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

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

const conn = connect(config)

export default async function ProductsDelete(_, { by }) {
  let statement: string = ''
  let params: (string | number | boolean | {})[] = []
}

Here we import the necessary PlanetScale connection config, establish the connection and export a resolver function for ProductsDelete that contains the following variables:

• statement — the SQL statement that will be executed
• params — the SQL values (id or slug) that will be used

Inside the function ProductsDelete add the following:

Object.entries(by).forEach(([field, value]) => {
  if (
    value !== undefined &&
    value !== null &&
    (typeof value === 'string' || typeof value === 'number')
  ) {
    statement = `DELETE FROM Products WHERE ${field} = ?`
    params = [value]
  }
})

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

This mostly follows the same patterns as above. If you were to use an ORM like Prisma or Drizzle you the code wouldn't look so verbose.

Now we're ready to use the statement and params to execute against the database:

try {
  const results = await conn.execute(statement, params, options)

  if (results.rowsAffected === 1) {
    return { deleted: true }
  }

  return { deleted: false }
} catch (error) {
  return { deleted: false }
}

With that added, you should now have a delete.ts 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 ProductsDelete(_, { by }) {
  let statement: string = ''
  let params: (string | number | boolean | {})[] = []

  Object.entries(by).forEach(([field, value]) => {
    if (
      value !== undefined &&
      value !== null &&
      (typeof value === 'string' || typeof value === 'number')
    ) {
      statement = `DELETE FROM Products WHERE ${field} = ?`
      params = [value]
    }
  })

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

  try {
    const results = await conn.execute(statement, params, options)

    if (results.rowsAffected === 1) {
      return { deleted: true }
    }

    return { deleted: false }
  } catch (error) {
    return { deleted: false }
  }
}

Now open Pathfinder and execute the following mutation:

mutation {
  productDelete(by: { id: "7" }) {
    deleted
  }
}

Remember to use a valid id or slug.

GraphQL queries are typically used to fetch data and return it in a certain shape. We will create two queries in this section:

• product — fetch a single product by the id or slug value
• products — fetch all products in the database and return as a list

Let's begin by creating the query to fetch a single product by the id or slug value. Inside the Grafbase Configuration file, add the following:

g.query('product', {
  args: { by: g.inputRef(productByInput) },
  resolver: 'products/single',
  returns: g.ref(product).optional(),
})

Now create the file resolvers/products/single.ts and add the following:

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

const conn = connect(config)

export default async function ProductsSingle(_, { by }) {
  // ...
}

Just like before we add the necessary imports and ProductsSingle default export.

Now we'll add a bit more to the file that will:

• Throw an error if both id and slug are passed
• Select the product from the database WHERE the id is of the value passed
• Select the product from the database WHERE the slug is of the value passed
• Throw an error if no id or slug values are passed

The full delete.ts should look 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 ProductsSingle(_, { by }) {
  let results

  try {
    if (by.id !== undefined && by.slug !== undefined) {
      throw new GraphQLError('Only one of ID or Slug should be provided')
    } else if (by.id !== undefined) {
      results = await conn.execute(
        'SELECT * FROM products WHERE id = ? LIMIT 1',
        [by.id],
        options,
      )
    } else if (by.slug !== undefined) {
      results = await conn.execute(
        'SELECT * FROM products WHERE slug = ? LIMIT 1',
        [by.slug],
        options,
      )
    } else {
      throw new GraphQLError('ID or Slug must be provided')
    }

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

    return null
  }
}

Open Pathfinder and execute the following GraphQL query to fetch by id:

{
  product(by: { id: "1" }) {
    id
    name
    slug
    onSale
    price
  }
}

You can also fetch by slug if you update the query to use that argument instead:

{
  product(by: { slug: "shoes" }) {
    id
    name
    slug
    onSale
    price
  }
}

Now let's finish by implementing our final query that will be used to fetch all products from our database.

Inside the grafbase.config.ts file you should add the following query definition:

g.query('products', {
  resolver: 'products/all',
  returns: g.ref(product).optional().list().optional(),
})

Next finish by creating the file grafbase/resolvers/products/all.ts and add the following:

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

const conn = connect(config)

export default async function ProductsAll() {
  try {
    const results = await conn.execute(
      'SELECT * FROM Products',
      undefined,
      options,
    )

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

That's it! You now have a GraphQL query to fetch everything from the database. Open Pathfinder and execute the following GraphQL operation:

{
  products {
    id
    name
    slug
    price
    onSale
  }
}

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.

Grafbase provides support for edge caching, a feature that enhances performance by serving already cached data, thereby eliminating the need to wait for a response from the database.

Within our exported config and schema, we introduce an additional key, cache. This cache object includes the two queries — products and product, for which we want to enable caching. It also defines the maxAge and outlines how cache invalidations should be handled.

We've chosen the entity invalidation option, which implies that the id of a Product returned by both queries will serve as tags in the cached responses. Therefore, if a Product is mutated and the id matches any cached data tag, the data will be invalidated.

export default config({
  graph: g,
  cache: {
    rules: [
      {
        maxAge: 60,
        types: [{ name: 'Query', fields: ['products', 'product'] }],
        mutationInvalidation: 'entity',
      },
    ],
  },
})

With this configuration, we are leveraging Grafbase's edge caching to improve the efficiency of our API operations.

The final step involves deploying your new GraphQL API to the Edge with the help of GitHub. Here's how you can do it:

1. Sign in to your GitHub account, or create one if you don't have it yet.
2. Initiate a new repository via GitHub.
3. In the Grafbase Dashboard, start a new project and link it with the repository you've just created.
4. During the project setup, remember to add the necessary environment variables.
5. Now, deploy your project!

Once deployed, Grafbase will provide an API endpoint and an API key, which you can then utilize in your application. To allow access from your frontend framework or browser, you'll need to configure and enable auth.