How To Build a GraphQL API Connected to a MySQL Database Using StepZen
Tags: graphql, database, discuss, mysql
Published: 2021-04-13 15:17:28

This post was originally published at [StepZen](https://stepzen.com/blog/how-to-build-a-graphql-api-connected-to-mysql-database-using-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:

```graphql

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

```

and you get back exactly that. 

```graphql
    {
    "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](https://stepzen.com/request-invite). 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](https://dev.mysql.com/downloads/). You should also have MySQL installed on your machine in order to use the workbench.

2. Sign up for [a Heroku account](https://signup.heroku.com/). 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. 
* Click the home icon at the top left corner.
* Next to 'MySQL Connections' click the + button, then name your connection whatever you want
* Enter the `USERNAME` and `HOSTNAME` from your heroku account. You can leave the port as it is.  
* Click 'Store in Keychain'and add your `PASSWORD`.
* Click 'Test Connection'.  You won't need a default schema. 
* Once you hit 'OK' on the notification that your connection was successful, hit 'OK' again. 
* Click on your connection to open it. 

2. 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:

```sql
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);
```

3. 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.

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

```sql
SELECT * FROM authors
```

![SQL query response](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ytpjr9pqtppntjcvv8x.png)


### Set yourself up with StepZen

You'll need a StepZen account. If you don't already have one, you can [request an invite here](https://stepzen.com/request-invite).

### 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.

```bash
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.

```bash
stepzen login -a [account name]
```

You'll need your account name and admin key, which are accessible via your ["My Account" page](https://my.stepzen.com/account). 


## 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! :key:

This is what you'll place in it: 

```yaml
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](https://spec.graphql.org/June2018/#sec-Type-System.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. 

```graphql
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.

```graphql
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:

```graphql
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! :fireworks:

## Deploying to StepZen

Upload your config.

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

Upload your schema and deploy to StepZen.

```bash
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:

```bash
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:

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

Would get you a result like:

![GraphQL query response](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/puq3k1sgjczsq4i0tv8o.png)

## Where to go from here
If you have questions about MySQL and StepZen, feel free to [consult the `@dbquery` docs](https://my.stepzen.com/docs/build-a-graphql-endpoint/dbquery). If you want to download the code from this blog post, [check out our sample schema](https://github.com/stepzen-samples/stepzen-mysql).