StepZen is now part of IBM. For the most recent product information and updates go to
https://www.ibm.com/products/stepzen

Getting Started with a MySQL Database

Use `stepzen import mysql` to create a GraphQL API from a MySQL database in minutes

There are two ways to create your GraphQL API with StepZen when you have a SQL database backend.

  • Use the command-line interface (CLI) command stepzen import [mysql | postgresql] to specify your database - StepZen introspects it and auto-generates a GraphQL schema for you.
  • Write your schema code in a .graphql GraphQL Schema Definition Language (SDL) file. Use the GraphQL directive @dbquery to connect the database, and with just a few lines of code, you have a working schema.) See How to Connect a MySQL Database.

This topic shows how to use stepzen import mysql.

Note: The source code for this page in in our Examples repository.

Create a GraphQL Schema

This section shows how to use a StepZen CLI to create a GraphQL API that connects data from a database backend.

Before you begin: Install and set up your StepZen account and the CLI.

  1. Run this command on your local machine:

    stepzen import mysql

    This command starts creating a GraphQL API that connects the data from your MySQL database.

  2. Specify the preferred endpoint you want your GraphQL deployed on. The CLI prompts you with a default name for your endpoint (api/dozing-fly), but you can change it:

    ? What would you like your endpoint to be called? (api/dozing-fly)

    The command creates a new directory that contains a stepzen.config.json file, which in turn contains your project's CLI configuration. This includes the name of your endpoint, in this case, api/dozing-fly:

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

Downloading from StepZen...... done

    The JSON file containing your endpoint looks similar to this:

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

  3. Specify the connection details for your database:

    ? What is your host?
? What is your database name?
? What is the username?
? What is the password? [input is hidden]

    If you don't have a MySQL database set up yet, you can use the following connection details to use a mocked read-only database:

    • host: db.introspection.stepzen.net
    • database name: introspection
    • username: testUserIntrospection
    • password: HurricaneStartingSample1934

What happens next?

StepZen does the following:

  • Introspects your MySQL database and create a GraphQL schema based on its tables and columns. To do so, StepZen automatically generates:

    • The types based on the table definitions.
    • Queries against the types.

  • Creates a configuration file called config.yaml with your connection details, which looks like the following:

configurationset:
  - configuration:
      name: mysql_config
      dsn: {{username}}:{{password}}@{{host}}:{{port}}/{{database name}}
  • Creates a schema file (e.g., mysql.graphql), with types and queries for all the tables in your MySQL database. It uses the custom directive @dbquery to transform GraphQL operations to SQL. This file looks similar to the following example (simplified):
type Customer {
  email: String!
  id: Int!
  name: String!
}

type Query {
  getCustomerList: [Customer]
    @dbquery(type: "mysql", table: "customer", configuration: "mysql_config")
}

That's it! StepZen has now created a GraphQL schema for a MySQL database backend.

Deploy and Run your Endpoint

Next, deploy the GraphQL endpoint you created.

  1. Execute stepzen start to deploy the generated GraphQL schema for your database to StepZen.

    A GraphQL API is instantly available in the cloud, on the endpoint you configured above (...api/dozing-fly). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account. The easiest way to do this is to use the StepZen dashboard explorer.

  2. Run the following query:

{
  getCustomerList {
    id
    name
  }
}

The response that pulls data from the MySQL database looks like this:

{
  "data": {
    "getCustomerList": [
      {
        "id": 1,
        "name": "Lucas Bill"
      }
      // More results
    ]
  }
}

Great! You can now extend the GraphQL schema.

Extend the Schema

So far, you've learned how to build and deploy a GraphQL API with a database backend. You can do much more with StepZen to extend your schema. In this section, you will learn how to:

Query by Parameter

The auto-generated getCustomerList query field resolves to a list of all customers. To retrieve a single customer, add an id: Int! argument to the query field and use it as a parameter in the SQL query inside the @dbquery directive.

type Query {
  getCustomerById(id: Int!): Customer
    @dbquery(
      type: "mysql"
      query: """
      select * from `customer` where `id` = ?
      """
      configuration: "mysql_config"
    )
}

You can also query by multiple parameters and include other parameters in addition to the primary key.

Here's an example for our mocked read-only MySQL database provided above in Create a GraphQL schema:

type Query {
  getCustomerByEmailAndName(email: String!, name: String!): [Customer]
    @dbquery(
      type: "mysql"
      query: """
      select * from `customer` where `email` = ? and `name` = ?
      """
      configuration: "mysql_config"
    )
}

Both the email and name arguments for the getCustomerByEmailAndName query are required, since @dbquery doesn't allow optional parameters.

Use @materializer to Query Across Tables

The queries so far only return data from individual tables. You can use the @materializer directive to query data across tables.

  1. Create a new query to return a list of orders for a specific customer:

    type Query {
  getOrderListByCustomerId(customerId: Int!): [Order]
    @dbquery(
      type: "mysql"
      query: """
      select * from `order` where `customerId` = ?
      """
      configuration: "mysql_config"
    )
}

    The orders retrieved by this query can be linked to the Customer type with the @materializer directive:

    type Customer {
  email: String!
  id: Int!
  name: String!
  orders: [Order]
    @materializer(
      query: "getOrderListByCustomerId"
      arguments: [{ name: "customerId", field: "id" }]
    )
}

How does it work?

The getOrderListByCustomerId query is called when you request the field orders in a query that returns data of type Customer (like getCustomerList). First, the data for the customer is retrieved including the field id. id is then used to retrieve the orders for this customer.

Paginate Responses

Pagination is a common requirement for APIs, but it's not always straightforward to implement. In StepZen, setting up pagination is similar to using parameters in a query:

type Query {
  getPaginatedCustomerList(first: Int!, after: Int!): [Customer]
    @dbquery(
      type: "mysql"
      query: "select * from customer limit ? offset ?"
      configuration: "mysql_config"
    )
}

Handle Mutations

We have only worked with queries up to this point. If you want to write to the database, create a mutation type:

type Mutation {
  insertCustomer(
    creditCard: String = ""
    label: String = ""
    street: String = ""
    city: String = ""
    postalCode: String = ""
    countryRegion: String = ""
    stateProvince: String = ""
    email: String!
    name: String!
  ): Customer
    @dbquery(
      type: "mysql"
      table: "customer"
      dml: INSERT
      configuration: "mysql_config"
    )
}

The mutation insertCustomer accepts parameters for all of the columns available on the customer table. StepZen performs the necessary database insert.

Learn More

Congratulations! You've created a GraphQL API based on a database backend that includes queries, parameters, and mutations.