How to Connect A GraphQL API and A MySQL Database in Your GraphQL Layer
Tags: graphql, mysql, database, architecture
Published: 2021-09-16 19:16:47

_by Lucia Cerchie, originally published at [StepZen](https://stepzen.com/blog/connect-a-mysql-db-and-graphql-api-in-a-gql-layer)_

## Introduction

GraphQL layers delineate software and make it easier for backend engineers and frontend engineers to communicate and understand expectations. You can integrate data from any type of backend into a GraphQL layer, making this advantage accessible to any type of project. Today, we'll take a look at how to integrate a GraphQL API and MySQL database in your GraphQL layer. As a result, a developer can access data from two different backends in a single query!

Let's posit that we are working with a frontend developer who wants to create a page that displays a list of countries, their languages, and GDP (Gross Domestic Product) information. The information on languages comes from a GraphQL API, while the GDP information comes from a MySQL database. We are going to make things easy for the frontend developer by making both pieces of information available via a single query. You'll also see how to create the boilerplate code for frontend consumption.

Let's do this!

> Note: to follow along, you'll need [a StepZen account](stepzen.com/signup).
> This article also assumes basic familiarity with [GraphQL](https://graphql.org/) and the [GraphiQL IDE](https://github.com/graphql/graphiql).

## The GraphQL Backend

We'll be using [Trevor Blades's Countries API](https://github.com/trevorblades/countries).

It returns information on the world's countries, including things like capitals, currencies, and languages.

To get started, we will run

```bash
stepzen import graphql
```

Then you'll be prompted to enter the GraphQL API you'd like to import:

```bash
? What is the GraphQL endpoint?  https://countries.trevorblades.com/
? Do you want to add an Authorization header? No
? Do you want to add a prefix? No

Generating schemas...... done
Successfully imported 1 schemas from StepZen
```

Then, we'll accept the auto-suggestion for the endpoint name:

```bash
? What would you like your endpoint to be called? (api/bulging-cricket)
```

So, in my case, the GraphQL API endpoint is `api/bulging-cricket`. We'll decline the authorization header (the API requires no key) and the prefix for now (in general, though, prefixes are recommended for multiple backends):

```bash
? Do you want to add an Authorization header? No
? Do you want to add a prefix? No
```

Taking a look at our file structure, we see:

```
🐒➔ tree
    .
    ├── graphql
        ├── countries_trevorblades_com.graphql
    ├── index.graphql
    └── stepzen.config.json
```

Inside the `graphql` folder is the main schema, with the queries, types, and enums that StepZen has introspected from the API.

The `index.graphql` tells StepZen which file's schemas to deploy to the endpoint.

`stepzen.config.json` holds the endpoint information.

Now, if we run `stepzen start`, a GraphiQL editor will pop up in your `localhost:5000` browser window. And there's our GraphQL backend!

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

Now to get started with MySQL.

## The MySQL Backend

I've got a MySQL database [deployed on Railway](https://railway.app/). I've got one table in the database named `countries`, with columns named `id` (the primary key), `code`, `name`, and `GDP`. The values in the column `code` correspond exactly to the `code` values available in the countries API. There are similar values for `name` in the API as well, but no information on GDP.

If you'd like to seed your database similarly to mine, you can run:

```SQL
CREATE TABLE countries (
    id int,
    code string,
    name string,
    GDP int
);

INSERT INTO countries (
    id
    code
    name
    GDP
)
VALUES
(
    1,
    "AD",
    "Andorra",
    3
),
(
    2,
    "AE",
    "United Arab Emirates",
    421
),
(
    3,
    "AF",
    "Afghanistan",
    `19`
),
(
    4,
    "AG",
    "Antigua and Barbuda",
    2
),
(
    5,
    "AI",
    "Anguilla",
    219
);
```

The next thing to do is to run:

`stepzen import mysql`

You'll encounter a set of questions to prompt for your MySQL information:

```bash
? What is your host? containers-us-west-12.railway.app:5973
? What is your database name? railway
? What is the username? root
? What is the password? [hidden]
```

As you can see, I've filled mine out using values from my railway deployment. You can generally find these for deployed MySQL databases in your connection string, or dsn.

After StepZen has generated a schema for your database, you will see a folder structure like this added to your overall structure:

```
🐒➔ tree
    .
    ├── graphql
        ├── mysql.graphql
```

Just like with the GraphQL API import, inside the `graphql` folder is the main schema, with the queries, types, and enums that StepZen has introspected from the countries API.

Run `stepzen start`, and see the MySQL queries added to the left-hand pane. Now you'll be able to query the database:

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

## Our Goal

Remember our frontend developer from our introduction? To recap, they want to create a display page that show a list of countries, their languages, and GDPs.

The information on languages comes from the countries GraphQL API, while the GDP information comes from our MySQL database. To make things easier for them, we can make both pieces of information available in one query.

Let's do it!

## How we Use `@materializer` to Tie it all Together

In `mysql.graphql` we have a type `Countries` and a query that returns information on the countries.

```graphql
type Countries {
  GDP: Int
  code: ID
  name: String
}

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

In order to create our query, we will make a new type, employing `@materializer`, a [custom directive from StepZen](https://stepzen.com/docs/features/linking-types), to return data from our API in the new query.

```
type Countries_For_MySQL_And_GraphQL {
  GDP: Int
  code: ID
  name: String
  country: Country
    @materializer(
      query: "country"
      arguments: [{ name: "code", field: "code" }]
    )
}
```

The query that `@materializer` is now referencing comes from `graphql/countries_trevorblades_com.graphql`:

```graphql
  country(code: ID!): Country
    @graphql(
      endpoint: "https://countries.trevorblades.com/"
      prefix: { value: "", includeRootOperations: true }
    )
```

Since `country` takes in a parameter named `code`, we set the name in `@materializer`'s arguments equal to `code`, and set the value of this name to be equal to the code field in `Countries_For_MySQL_And_GraphQL`.

Now we're ready to write our query!

```graphql
  getGDPAndCountryByCodeMySQL(code: ID!): Countries_For_MySQL_And_GraphQL
    @dbquery(
      type: "mysql"
      query: "SELECT * FROM countries where ? = code"
      configuration: "mysql_config"
    )
```

`@dbquery` is [another custom StepZen directive](https://stepzen.com/docs/features/connect-a-database), which allows StepZen to connect to your database. It takes in `type` (in this case mysql), `query`, which specifies what query to make on the database, and `configuration`, which points to the `config.yaml` file which holds the secrets in your dsn string.

Let's take a look at this in the browser. If you don't have it running in the background already, run `stepzen start`

## Conclusion

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

As you can see, we are now able to access data (GDP and languages) from two different backends (GraphQL API and MySQL database) in a single query!

In addition to that, you can help your frontend developer teammate by pre-generating their boilerplate code for frontend consumption. Click the `Export` button on the top right, and code for consuming the query with Apollo Client is generated:

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

With StepZen, integrating multiple backends into one GraphQL data layer becomes easier with lots of little tools like these. Check them out in [our docs](https://stepzen.com/docs).