Tutorial: Create a GraphQL API for a MySQL database

Generate the GraphQL Schema

1. Create a workspace directory for your project; for example:

   mkdir mysql-tutorial

2. Navigate into your new workspace directory:

   cd mysql-tutorial

3. Run the following CLI command on your local machine:

   stepzen import mysql

   This command starts creating a GraphQL API that connects the data from your MySQL database, and prompts you for information as shown in the following steps.

4. Specify the preferred endpoint where you want your GraphQL deployed. The CLI prompts you with a default name for your endpoint (in this example, 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. The configuration includes the name of your endpoint (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 the following example:

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

5. 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]

API Connect for GraphQL performs the following tasks:

• Introspects your MySQL database and creates a GraphQL schema based on the tables and columns.

  To do so, API Connect for GraphQL 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 example:

configurationset:
  - configuration:
      name: mysql_config
      dsn: {{username}}:{{password}}@{{host}}:{{port}}/{{database name}}

• Creates a schema file (for example, mysql.graphql), with types and queries for all of the tables in your MySQL database.

  The schema uses the custom directive @dbquery to transform GraphQL operations to SQL. This file looks similar to the following (simplified) example:

type Customer {
  email: String!
  id: Int!
  name: String!
}

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

That's it! API Connect for GraphQL created a GraphQL schema for the MySQL database backend.

Deploy and run your endpoint

Run the CLI command stepzen start to deploy the generated GraphQL schema for your database to API Connect for GraphQL. A GraphQL API is immediately available on the endpoint that you configured in the preceding step (api/dozing-fly). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account.

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

   A GraphQL API is instantly available on the endpoint you configured in the preceding section (...api/dozing-fly). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account.

2. Now 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 are ready to 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 API Connect for GraphQL to extend your schema, as you will learn in the following sections.

Query by parameter

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

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.

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. Then, the id is 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 API Connect for GraphQL, 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"
    )
}

Work with mutations

If you want to write to the database, create a mutation type as in the following example:

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. API Connect for GraphQL performs the necessary database insert.