How to Introspect a MySQL Database

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

By introspecting your database, StepZen enables you to import custom schemas for your deployed MySQL databases.

The following sections describe how to introspect 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 this tutorial, you'll need:

  • A deployed MySQL database. You can create one by using these instructions to deploy a database on Railway.
  • Your DSN connection string to connect StepZen with your database.

Perform the steps in the following subsections to get set up:

Seed your database

Use the following SQL command to create a countries table in your database:

CREATE TABLE countries (
	id varchar(255),
	country_name varchar(255),
	isoCode varchar(255),
	GDPUSD int
);

Insert three countries into your newly created table:

INSERT INTO countries (
  id, country_name, isoCode, GDPUSD
)
VALUES (
	"Q889",
	"Afghanistan",
	"AFN",
	19.29
),
(
	"Q953",
	"Zambia",
	"ZMW",
	23.31
),
(
	"Q664",
	"New Zealand",
	"NZD",
	206.9
);

Verify that the seed command worked with a SELECT query:

SELECT * FROM countries;

You should get a response similar to the following:

+------+--------------+---------+--------+
| id   | country_name | isoCode | GDPUSD |
+------+--------------+---------+--------+
| Q889 | Afghanistan  | AFN     |     19 |
| Q953 | Zambia       | ZMW     |     23 |
| Q664 | New Zealand  | NZD     |    207 |
+------+--------------+---------+--------+
3 rows in set (0.04 sec)

Log in and Create a Project Folder

Follow the steps below to log in and create a project folder:

  1. Create a working directory for your project:
mkdir mysql-stepzen
  1. Navigate into the folder:
cd mysql-stepzen

Run stepzen import mysql

Run the following command from within the current directory:

stepzen import mysql

The StepZen CLI will then ask you what you'd like to name your endpoint. You can just accept the suggested endpoint name or supply one of your own in the format FOLDER_NAME/ENDPOINT_NAME.

? What would you like your endpoint to be called? api/dozing-sheep

The command will create a new folder and insert a stepzen.config.json file that contains the CLI configuration for your project, including the name of your endpoint.

Created /Users/path/to/project-folder/stepzen.config.json

Downloading from StepZen...... done

The JSON file will look similar to this:

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

Enter your Database Credentials

The command prompt will ask you four questions to connect to our 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]

Include both the host and port for the first question What is your host. For example, if you are using Railway, enter something like the following:

? What is your host? containers-us-west-11.railway.app:7199
? What is your database name? railway
? What is the username? root
? 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 where you've chosen to deploy.

In general, you will have something like this pattern:

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

Explore Your Import

After a successful import, your main folder will contain the following imported files:

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

Let's explore the various files in there.

index.graphql

index.graphql manages the schemas for StepZen. It tells StepZen how to assemble your schema by listing the .graphql files that must be included.

We only have one file so far (mysql.graphql) so the contents of index.graphql will look like this:

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

If you had more than one .graphql file, they would appear in the files: [] brackets as part of a comma-separated list of strings.

config.yaml

config.yaml contains the configuration information that StepZen needs to connect to your database, including the DSN information you provided to the questions from the CLI. It will look similar to the following:

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

Note: Ensure config.yaml is in .gitignore before pushing to any Git branches, as it contains sensitive information.

mysql.graphql

Inside mysql/mysql.graphql is schema code that defines the types and queries that StepZen will use to create your GraphQL API:

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 based upon the countries table. The query type getCountriesList returns all the information on the countries in that table. It effectively performs a SELECT * FROM countries SQL command using StepZen's custom @dbquery directive for connecting to a database.

To learn more about @dbquery and how to configure it, read our @dbquery documentation.

Use the StepZen Schema Explorer

Test your new API by running stepzen start from the command line. The StepZen Schema Explorer will open in your browser:

Run a test query

Copy/paste this GraphQL query into the query panel:

query MyQuery {
  getCountriesList {
    GDPUSD
    country_name
    id
    isoCode
  }
}

We'll get a response containing the data coming from our MySQL database:

{
  "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!

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.