How To Build a GraphQL API Connected to a MySQL Database Using StepZen

This post was originally published at StepZen.

GraphQL makes your life easy. As opposed to using a REST paradigm to access your datasource, you don't have to filter out data you don't need.

You ask for what you want:

    {
    fruit(id: 1){
        name
        isPoisonous
        }
    }

and you get back exactly that.

    {
    "fruit": {
        "name": "banana",
        "isPoisonous": false
        }
    }

Now, if you add StepZen to the mix, you can query your database in a similar way — and for simple queries, you won't have to write a line of SQL.

You'll also save on the codespace you need to set up your connection. Let's see how.

Getting Set Up

To follow along with the steps in this post, you'll first need to sign up for a StepZen account. That way, you'll be able to access your StepZen admin and API keys from your account page.

Then, you'll need to have a database that is deployed to Heroku. If you don't have one, you can follow these instructions to get one set up:

1. Download MySQL workbench before you start. You should also have MySQL installed on your machine in order to use the workbench.

2. Sign up for a Heroku account. It can be on the free tier, but you'll need to add a credit card to your account to use the add-ons that you need (it won't be charged as a part of this tutorial). Once you've done that, you can proceed.

3. Go to your dashboard and click 'New', then 'Create New App'. Type in your app name and hit 'Create App'.

4. Click on the 'Resources' tab and search for 'ClearDB MySQL' under 'Add-ons'. Click on it and select the 'Ignite-Free' plan and then 'Submit Order Form'.

5. Now click 'Settings' and 'Reveal Config Vars'. For the value of CLEARDB_DATABASE_URL you should see a string formatted like mysql://USERNAME:PASSWORD@HOSTNAME/DB_NAME. You'll use this info for setting up a new connection in your MySQL Workbench in the next few steps.

NOTE: in the DB_NAME, leave off the ? and everything after it.

Seeding your database from MySQL Workbench

If you've already got your own database with data in it, then you can follow along with our instructions using it. If you don't have data in your database, you'll need to seed it with data first by following these instructions:

1. Open up MySQL workbench.
2. Click the home icon at the top left corner.
3. Next to 'MySQL Connections' click the + button, then name your connection whatever you want
4. Enter the USERNAME and HOSTNAME from your heroku account. You can leave the port as it is.
5. Click 'Store in Keychain'and add your PASSWORD.
6. Click 'Test Connection'. You won't need a default schema.
7. Once you hit 'OK' on the notification that your connection was successful, hit 'OK' again.

8. Click on your connection to open it.

9. Click the 'schemas' tab on the top left. Double click the name of your schema on the left to make sure it is selected, and copy and paste this code:
   

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `isPseudonym` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`)
);

CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `originalPublishingDate` date NOT NULL,
  `authorID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`)
);

INSERT INTO `authors` 
VALUES 
(1,'Agatha Christie',0),
(2,'Sir Arthur Conan Doyle',0),
(3,'Edgar Allan Poe',0);

INSERT INTO `books` 
VALUES 
(1,'Murder on the Orient Express','1934-01-01', 1),
(2,'The Mysterious Affair at Styles','1920-10-01', 1),
(3,'The Adventure of the Three Garridebs','1924-10-25', 2),
(4,'The Murders in the Rue Morge','1841-04-04', 3);

1. Make sure it is either 1) all selected or 2) not selected at all and click the little lightning bolt under the Query 1 tab. This will run the SQL code that seeds your database.

2. Query the database to verify the seed data was entered:
   

SELECT * FROM authors

Set yourself up with StepZen

You'll need a StepZen account. If you don't already have one, you can request an invite here.

Install StepZen CLI

The StepZen CLI will allow you to easily upload, deploy and test your GraphQL API. It can be installed globally via npm.

npm install -g stepzen

Login to StepZen account

Before you can deploy to StepZen, the CLI will need your account information, which can be added via the login command.

stepzen login -a [account name]

You'll need your account name and admin key, which are accessible via your "My Account" page.

Let's get started on connecting MySQL

First we need to set some configuration. Create a file called .gitignore In the same folder as this project, make a file called config.yaml Then add config.yaml to your .gitignore to keep it from being committed. It's private info! 🔑

This is what you'll place in it:

configurationset:
  - configuration:
      name: MySQL_config
      dsn: USERNAME:PASSWORD@tcp(HOSTNAME)/DB_NAME

What does this code do? It provides StepZen the information it needs to connect to your database.

What's next? Let's dig into writing GraphQL files.

GraphQL Files

We'll start with book.graphql, which is written in GraphQL Schema Definiton Language.

You're going to see some code starting with the @ symbol. These are GraphQL directives. The two directives below are custom directives that the team at StepZen invented.

Notice the @materializer directive on the type. When we query book, we want to be able to see information on the book's author as well, and we're able to do this by using the materializer to query author and return the name. It will use the authorID field to find each author.

If you've got different data in different tables, you'll need a type for each table, which fields for the data you want to return from each table. You can use @materializer similarly to connect your tables, as well as @dbquery to query your database easily.

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  author: Author
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "authorID"}]
  )
}
type Query {
  book(id: ID!): Book
        @dbquery(type: "mysql", table: "books", configuration: "MySQL_config")
  books(originalPublishingDate: Date!): [Book]
        @dbquery(
            type: "mysql",
            query: "SELECT * FROM books WHERE ? >= DATE '1900-00-00'",
            configuration: "MySQL_config"
        )
}

What's in the query?

The @dbquery directive does a lot here. First, it tells StepZen that we're using a database query and that we're using MySQL.

In the first @dbquery directive, we only supply a table property because the table fields match the properties in our type. StepZen automaticaly assigns the right database field to the right property for us.

In the second directive, we need to customize the data that StepZen gets from the table, so we supply a query property. The query we pass, SELECT * FROM books WHERE ? >= DATE '1900-00-00 is a custom query that returns only books written in the 1900's and beyond. You can supply any type of query you want here, for example joining two tables together to populate a single type.

Other Types

In author.graphql, we follow a similar pattern. You can write a type for each table in your database like this.

type Author {
  id: ID!
  name: String!
  isPseudonym: Boolean!
}
type Query {
  author(id: ID!): Author
        @dbquery(type: "mysql", table: "authors", configuration: "MySQL_config")
  authors(isPseudonym: Boolean!): [Author]
        @dbquery(
            type: "mysql",
            query: "SELECT * FROM authors WHERE isPseudonym = ?",
            configuration: "MySQL_config"
        )
}

But how do we tell StepZen that we want to tie these different schema files together?

Combining the Schema

That's where index.graphql comes in:

schema @sdl(files: ["author.graphql", "book.graphql"]) {
  query: Query
}

This file tells StepZen all of the files to combine to generate your GraphQL schema. And that's the last piece in the puzzle! 🎆

Deploying to StepZen

Upload your config.

stepzen upload configurationset {{PROJECT_FOLDER_NAME}}/config --file=./config.yaml

Upload your schema and deploy to StepZen.

stepzen upload schema {{PROJECT_FOLDER_NAME}}/schema --dir=. &&
stepzen deploy {{PROJECT_FOLDER_NAME}}/api --schema={{PROJECT_FOLDER_NAME}}/schema --configurationsets={{PROJECT_FOLDER_NAME}}/config

The StepZen GraphiQL Query Editor

In your terminal you can now you run:

stepzen start

Name your endpoint, and then StepZen will open up a GraphiQL query editor connected to your database!

So, if you had a database with a book table as well as an author table, pasting this code into your query editor:

{
  book(id: 1) {
    id
    author {
      id
    }
  }
}

Would get you a result like:

Where to go from here

If you have questions about MySQL and StepZen, feel free to consult the @dbquery docs. If you want to download the code from this blog post, check out our sample schema.