How To Connect To a MySQL Database

How to use a MySQL database as a data source for your GraphQL API

StepZen supports two types of databases, MySQL and Postgres. In this tutorial, we'll see how to connect to a MySQL database.

Getting Set Up

To follow along with the steps, you'll need to have a database that is accessible to StepZen. If you don't have a MySQL server you can use, one option is to deploy a ClearDb MySQL database to Heroku, as it is free for our purposes. 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.

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:

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

Now 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 into the query editor:

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

Click the little lightning bolt under the Query 1 tab to run the query, which will seed your database.

Query the database to verify that the seed data was entered:

SELECT * FROM authors

You should see a response with 3 rows.

Get Set Up with StepZen

You'll need a StepZen account. If you don't already have one, you can get access here.

You'll need to install the StepZen CLI, which allows you to easily upload, deploy and test your GraphQL API. It can be installed globally via npm.

npm install -g stepzen

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 Connecting to MySQL

Create a file called config.yaml in the root of your project. Be sure to add config.yaml to your .gitignore to keep it from being committed as it will contain your DSN, including your username and password to your database.

The contents of your config.yaml should look like this. The information that populates USERNAME, PASSWORD, HOSTNAME and DB_NAME should come from your DSN, for example from the DSN you copied from Heroku. Be sure to place it in the format shown below.

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

This file provides StepZen the information that it needs to connect to your database.

GraphQL Files

StepZen APIs are configured using GraphQL Schema Definition Language, which is basically GraphQL's standard language for defining a schema. Start by creating a book.graphql file in your root project folder.

Let's start by defining a basic Book type with properties and two queries on that type. The first query takes an id and returns a single instance of a Book and the second returns an array of book results that are filtered by their originalPublishingDate (this argument is required as indicated by the !).

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  author: Author
}
type Query {
  book(id: ID!): Book
  books(originalPublishingDate: Date!): [Book]
}

At this point, your Book type exists but isn't connected to any data. Let's connect it to the MySQL database.

Start by updating the book query. We'll use StepZen's @dbquery directive to tell StepZen that the query should come from a MySQL database and be populated from the books table. The configuration property tells StepZen to use the MySQL database configuration we named MySQL_config in our config.yaml

book(id: ID!): Book
  @dbquery(type: "mysql", table: "books", configuration: "MySQL_config")

That's all that is needed for this query. Since the column names match the properties in the Book type, StepZen can figure out the rest. Effectively it runs a query SELECT * FROM books WHERE id = {id}.

The next step is to create an Author type following the same flow as in Books. Create an author.graphql file in the root of your project with the following contents:

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"
    )
}

Connecting Types

You now have two types: Book and Author. You can query each but neither knows about the other. To fix that you'll need to use another StepZen custom directive called @materializer.

Open book.graphql and add an author property that returns the Author type you just created. Use the @materializer directive to tell StepZen how to connect the types. In this case, you'll call the query author. arguments indicates that you need to pass the authorID from Book as the id argument to the query.

author: Author
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "authorID"}]
  )

The finished book.graphql file should look like this:

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  @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]
}

@materializer can also accept field arguments. What do we mean by this? Let's learn by doing and create an argument on the book.graphql Query:

booksPublishedSince(since:Date!): [Book]
        @dbquery(type: "mysql",
            query: "SELECT * FROM books WHERE originalPublishingDate > ? ",
            configuration: "MySQL_config")

This defines a query that returns all the books published after the variable passed in.

Now, over in author.graphql, we add the value to the variable in a new @materializer directive:

  booksPublishedAfter(publishedAfter:Date!="1900-01-01") : [Book]
  @materializer(
    query: "booksPublishedSince"
    arguments: [
      {name: "since", argument: "publishedAfter"}
    ]
  )

In the arguments section, the name since is the name of the argument in the query referenced by @materializer. The argument, publishedAfter is a field argument annotated by @materializer. At runtime, the value of publishedAfter is used as the value for since.

Now queries like this:

query MyQuery {
  booksPublishedSince(since: "1920-12-12") {
    authorID
    id
    name
    originalPublishingDate
  }
}

return properly filtered results!

{
  "data": {
    "booksPublishedSince": [
      {
        "authorID": "1",
        "id": "1",
        "name": "Murder on the Orient Express",
        "originalPublishingDate": "1934-01-01"
      },
      {
        "authorID": "2",
        "id": "3",
        "name": "The Adventure of the Three Garridebs",
        "originalPublishingDate": "1924-10-25"
      }
    ]
  }
}

Connecting the Schema Files

Every StepZen schema requires an index.graphql file, which tells StepZen how to assemble your schema from the .graphql files. In this case, you need it to include two files: author.graphql and book.graphql. Create an index.graphql file in the root of your project with the following contents:

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

Deploying and Testing Your Schema

You can use the StepZen CLI to deploy and test your API. In your terminal, run this command to deploy to StepZen:

stepzen start

It will ask you to name your endpoint. You can define a name in the [FOLDER_NAME]/[ENDPOINT_NAME] format or use the default provided. StepZen will open up an Schema explorer that allows you to query your newly deployed API.

Try running the following query:

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

You should get a result that pulls data from the MySQL database, combining both books and authors.

{
  "data": {
    "book": {
      "name": "Murder on the Orient Express",
      "author": {
        "name": "Agatha Christie"
      }
    }
  }
}

This site uses cookies: By using this website, you consent to our use of cookies in accordance with our Website Terms of Use and Cookie Policy.