How To Layer a GraphQL API on a MySQL Database With a Single Command

Originally posted on StepZen.com

Adding a GraphQL layer to your architecture has many advantages. It allows you to decouple the front and backends, creating a clearer line between the responsibilities of frontend and backend developers.

And if you make that layer a StepZen GraphQL API endpoint, you can hand off time-consuming considerations like lifecycle maintenance and security to the StepZen.

In addition, you'll be able to spin up your endpoint in a matter of minutes. I'll teach you how.

Getting Started

Signing Up for StepZen

You'll need to create a StepZen account first, in order to obtain your API and admin keys (available via the "My Account" button on the top right once you log in).

Next, you'll install the StepZen CLI. This will allow you to deploy your endpoint from the command line.

Your Database

Use these instructions to deploy a database on Railway. Alternatively, you could use the dsn from a database deployed on a different service to find the details the StepZen CLI will need.

Using The StepZen CLI

The first thing you'll need to do from the command line is run

stepzen login

When prompted for your credentials, enter the ones from your account page:

What is your account name?: {ACCOUNT}
What is your admin key?: {ADMINKEY}

Now, make yourself a folder for your project and cd into it like:

mkdir mysql-stepzen
cd mysql-stepzen

From where you are now, run:

stepzen import mysql

The StepZen CLI will then ask you what you'd like to name your endpoint:

? What would you like your endpoint to be called? api/dozing-sheep
Created /Users/luciacerchie/project-folder/stepzen.config.json
Downloading from StepZen...... done

In this case I am accepting the suggested endpoint. You'll notice that the command will also insert a stepzen.config.json file, which will hold the name of your endpoint.

Now, it will ask you 4 questions to connect to your MySQL database.

? What is your host? host_address_here
? What is your database name? database_name_here
? What is the username? username_here
? What is the password? [hidden]

Finding these inputs in your dsn string can be a little tricky, as the dsn strings might have slightly different formats depending on how you've chosen to deploy, but in general you will have something like this pattern:

mysql://{{USERNAME}}:{{PASSWORD}}@{{HOST}}:{{port}}/{{DATABASE_NAME}}

Exploring Your Import

If you open your main folder upon import success, you'll notice some imported files.

.
├── _mysql
│   └── mysql.graphql
├── config.yaml
├── index.graphql
└── stepzen.config.json

First, at the bottom of the working directory, you'll have stepzen.config.json, which I've mentioned before. It will look something like:

{
  "endpoint": "api/dozing-sheep"
}

This gives StepZen the information it needs to configure your endpoint.

Next up, we have index.graphql, which will look like:

schema @sdl(files: ["mysql/mysql.graphql"]) {
  query: Query
}

index.graphql martials the schemas for StepZen. If you had more than one, it would appear in the files: [] brackets as part of a comma-separated list of strings.

Your config.yaml will look like:

configurationset:
  - configuration:
      name: mysql_config
      dsn: {{USERNAME}}:{{PASSWORD}}@{{HOST}}:{{port}}/{{DATABASE_NAME}}

This provides StepZen with the details it needs to make the connection to your database.

NOTE: Make sure config.yaml is in your .gitignore before pushing to any git branches.

Lastly, inside mysql/mysql.graphql, you will find a schema like:

type Countries {
  GDPUSD: Int
  country_name: String!
  id: String
  isoCode: String
}

type Query {
  getCountriesList: [Countries]
    @dbquery(type: "mysql", table: "countries", configuration: "mysql_config")
}

StepZen has introspected your database and inferred the type Countries from your countries table, and the query type getCountriesList returns all the information on the countries in that table, in the mode of a SELECT * FROM... SQL command.

Using the StepZen Schema Explorer

From your terminal, run stepzen start. The Schema Explorer will open up at localhost:5000/api/foldername in your browser:

If you copy/paste this query into the central panel:

query MyQuery {
  getCountriesList {
    GDPUSD
    country_name
    id
    isoCode
  }
}

You'll get a response like:

{
  "data": {
    "getCountriesList": [
      {
        "GDPUSD": 19,
        "country_name": "Afghanistan",
        "id": "Q889",
        "isoCode": "AFN"
      },
      {
        "GDPUSD": 23,
        "country_name": "Zambia",
        "id": "Q953",
        "isoCode": "ZMW"
      },
      {
        "GDPUSD": 207,
        "country_name": "New Zealand",
        "id": "Q664",
        "isoCode": "NZD"
      },
      {
        "GDPUSD": 15,
        "country_name": "Mozambique",
        "id": "Q1029",
        "isoCode": "MZN"
      }

The information from your database is now available on your GraphQL API endpoint!

Where to Go From Here

If you're curious about how to consume data on the frontend, see our blog post on how to use plain javascript to get the job done.

Or, if you want to know how to connect other types of backends, like REST APIs, see our docs.

Our docs also provide a great deep dive on connecting MySQL and StepZen.

If those don't address your questions, please hit us up on Discord. We'd love to chat!