How to Connect A GraphQL API and A MySQL Database in Your GraphQL Layer

by Lucia Cerchie, originally published at StepZen

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.
This article also assumes basic familiarity with GraphQL and the GraphiQL IDE.

The GraphQL Backend

We'll be using Trevor Blades's Countries API.

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

To get started, we will run

stepzen import graphql

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

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

? 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):

? 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!

Now to get started with MySQL.

The MySQL Backend

I've got a MySQL database deployed on Railway. 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:

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:

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

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.

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, 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:

  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!

  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, 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

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:

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.