How To Layer a GraphQL API on a MySQL Database With a Single Command
Tags: graphql, todayilearned, mysql, database
Published: 2021-07-26 16:08:39


_Originally posted on [StepZen.com](https://stepzen.com/blog/how-to-layer-a-graphql-api-on-a-mysql-database-in-a-single-command)_

Adding a GraphQL layer to your architecture has many advantages. It allows you to [decouple](https://stepzen.com/blog/decoupling-frontend-backend) 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](https://stepzen.com/blog/why-us) 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](https://stepzen.com/request-invite) 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](https://stepzen.com/docs/quick-start). This will allow you to deploy your endpoint from the command line.

### Your Database

Use these instructions to deploy a [database on Railway](https://gist.github.com/ajcwebdev/8599295373c092f30fce9968eaf48635). 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

```bash
stepzen login
```

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

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

```bash
mkdir mysql-stepzen
cd mysql-stepzen
```

From where you are now, run:

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

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

```json
{
  "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:

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

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

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

![graphiql browser screenshot](https://codimd.s3.shivering-isles.com/demo/uploads/upload_248ed338c0018b71de7c5b1f693ff644.png)

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

```graphql
query MyQuery {
  getCountriesList {
    GDPUSD
    country_name
    id
    isoCode
  }
}
```

You'll get a response like:

```json
{
  "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](https://stepzen.com/blog/consume-graphql-in-javascript).

Or, if you want to know how to connect other types of backends, like REST APIs, [see our docs](https://stepzen.com/docs/connecting-backends).

Our docs also provide a great deep dive on [connecting MySQL and StepZen](https://stepzen.com/docs/connecting-backends/how-to-connect-a-mysql-database).

If those don't address your questions, please [hit us up on Discord](https://discord.com/channels/768229795544170506/768229795544170509). We'd love to chat!