How to Connect to a MySQL Database

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

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

Prerequisites

First, you'll need a StepZen account. Once you have that account, follow the steps to Install and Set up StepZen.

Get Set Up

To follow along with this tutorial, 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, which is free and can be used for our purposes. Follow the steps below to get one set up:

  1. Download MySQL Workbench before you start. You must also have MySQL installed on your machine in order to use MySQL 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. Navigate to your dashboard, click New > Create New App. Type your app name and click Create App.
  4. Click the Resources tab, navigate to Add-ons, and search for ClearDB MySQL.
  5. Click on ClearDB MySQL, select the Ignite-Free plan, and Submit Order Form.
  6. 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.
  7. (Optional) Perform the steps in Seed your database from MySQL Workbench.

Seed your Database from MySQL Workbench

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

  1. Open MySQL Workbench.
  2. Click the home icon at the top left corner.
  3. Click the + button next to MySQL Connections, and 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 don't need a default schema).
  7. Click OK after you've confirmed the notification that your connection was successful.
  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 ensure 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);
  1. Click the little lightning bolt under the Query 1 tab to run the query. This will seed your database.
  2. Query the database to verify that the seed data was entered:
SELECT * FROM authors

You should see a response with three rows.

Connect to MySQL

Create a file called config.yaml in the root of your project. This file provides StepZen the information that it needs to connect to your database.

Ensure you add config.yaml to your .gitignore to keep it from being committed, since it will contain your DSN (including the username and password to your database).

The contents of your config.yaml should look like this:

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

Note: The information that populates USERNAME, PASSWORD, HOSTNAME and DB_NAME must come from your DSN. For example, from the DSN you copied from Heroku. Ensure you place it in the format shown above.

GraphQL Files

StepZen APIs are configured using GraphQL SDL, which is GraphQL's standard language for defining a schema.

Start by creating a book.graphql file in your root project folder. Define 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.
  • 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. Use StepZen's @dbquery directive to tell StepZen that:

  • The query must come from a MySQL database.
  • The database is populated from the books table.
  • The configuration property tells StepZen to use the MySQL database configuration you named MySQL_config in your config.yaml file:
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. It effectively runs the 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"
    )
}

Connect Types

You now have two types: Book and Author. You can query each, but neither type knows about the other. To fix that, 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:

  • Name 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 will 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 does this mean? Let's learn by doing and creating 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 is passed in.

In author.graphql, 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 the query looks like this:

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

The query will 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"
      }
    ]
  }
}

Connect 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 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
}

Deploy and Test 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 a Schema explorer that enables 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.