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 allows you to import custom schemas for your deployed MySQL databases.

Getting Set Up

To follow along with these instructions you'll need to have access to a deployed MySQL database. You can create one by using these instructions to deploy a database on Railway. You'll need your DSN connection string to connect StepZen with your database.

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 back like so:

+------+--------------+---------+--------+
| 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)

Login and create project folder

You'll need to have the StepZen CLI installed and configured. If you do not, follow the documentation here.

Let's make a working directory for our project and cd into the folder:

mkdir mysql-stepzen
cd mysql-stepzen

Run stepzen import mysql

From within the current directory, 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

We can just accept the suggested endpoint name or supply one of our own in the format FOLDER_NAME/ENDPOINT_NAME. The command will also insert a stepzen.config.json file which contains some CLI configuration for your project, including the name of your endpoint. It will look something like this:

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

Enter your database credentials

Now, it will ask you 4 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]

We need to include both the host and port for the first question "What is your host." For example, if you are using Railway you will 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 our DSN string can be a little tricky, as the DSN strings might have slightly different formats depending on where 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 we open your main folder upon import success, we can see the imported files.

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

Let's explore the various files in there.

index.graphql

The index.graphql which martials the schemas for StepZen. Basically, it tells StepZen how to assemble your schema by listing the .graphql files that should be included. In our case, we only have one file so far, mysql.graphql. The contents of our index.graphql will look like this:

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

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

config.yaml

Our config.yaml contains the configuration information that StepZen needs to connect to our database, including the DSN information we provided to the questions from the CLI. It will look something like :

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

NOTE: Make sure config.yaml is in your .gitignore before pushing to any git branches as it contains sensitive information.

mysql.graphql

Lastly, inside mysql/mysql.graphql, we will find a schema code that defines the types and queries that StepZen will use to create our 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 our countries table. The query type getCountriesList returns all the information on the countries in that table – effectively performing a 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.

Using the StepZen Schema Explorer

Let's test our new API by running stepzen start from the command line. The StepZen Schema Explorer will open up 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.