As some of you know, I have been working around databases for about four decades (that does date me!). From the early days of System R and Ingres, through the commercial engines of DB2 and Oracle, the open sources of MySQL and Postgres, to the current generations of NoSQLs like MongoDB and Cassandra and scalable SQL like CockroachDB and Yugabyte, anyone who has predicted the demise of databases has proven to be wrong. SQL as the query language has persisted, evolved, and improved, but the basic

select * from foo where x = 1 group by y

is the language known to hundreds of thousands of developers. Why is that? Because databases just work, and how can you say that about too many things?

As a frontend developer, you want to see data in logical business constructs. Say a customer has one or more addresses; your React application would love to see data like this:

{
    "customer": {
        "name": "John Doe",
        "email": "john.doe@example.com",
        "addresses": [
            {
                "street": ...
            }
        ]
    }
}

The good thing is that there is already a query language that produces data like this—GraphQL. Awesome! However, the database that you (or someone on your team!) love stores data in mysterious ways.

database tables

So you have this impedance mismatch problem. You have a view of the data, expressible in GraphQL, that must match the implementation, which looks like a set of tables and join tables and all the other artifacts that you'd rather wish away.

Solving this impedance mismatch problem is easy to do with StepZen.

Get started

  1. Install the StepZen CLI: npm install -g stepzen.
  2. If you have not already done so, log in to StepZen: stepzen login using your Admin (stepzen.io) key, which is available from your My Account page.

Now, follow one of the following series of steps:

If you want a React App populated with data from your database

  1. npx create-stepzen-app introspection; cd introspection
  2. npm start

    This fires up your React app on localhost:3000. The location and weather are displayed for ip: "8.8.8.8" (a Google server).

  3. cd stepzen

    You will see the artifacts that support the above React app query, and if you are familiar with StepZen, you will see the GraphQL code that uses ip-api and openweathermap to fetch the data for the above query.

  4. Now let us add data from MySQL. We provide a prebuilt database.

    stepzen import mysql

    It asks you a series of questions. Use the following:

    - hostname: 35.224.227.100
    - database name: introspection
    - username: testUserIntrospection
    - password: HurricaneStartingSample1934

    That's it. This one command creates the artifacts from the mysql you point it to.

    (It is not required, but you may edit the index.graphql in your stepzen folder to edit out the files ip-api/ipapi.graphql and openweathermap/openweathermap.graphql. We won't be using them for this scenario.)

  5. stepzen start

    A GraphiQL browser launches at localhost:5000 in which you can query your database. Try this query:

    {
        getCustomer(id: 1) {
            email
            name
            orderList {
            carrier
            shippingCost
            trackingId
        }
    }
    }

    Feel free to try other queries using the GraphQL Builder tab in GraphiQL.

  6. Go to the Code Exporter tab in the GraphiQL browser (under the Beta tab). You can see the React component code for the above query. Copy it.

  7. Navigate to Helloworld.js in your project directory's src. Paste the code from the previous step.
  8. Reload the app (on localhost:3000)

    The data of the query above shows up in a nicely formatted HTML table. Cool, right?

    Congrats! In a few easy steps, you have set up a React app, set up a GraphQL endpoint from a MySQL database, and connected the app to that endpoint.

If you want to set up a GraphQL endpoint

  1. Create your project folders

    mkdir introspection; cd introspection; mkdir stepzen; cd stepzen

  2. Next, let's add data from MySQL. We have provided a prebuilt MySQL database.

    stepzen import mysql

    Yopu are prompted to provide a set of information. Use the following:

    • hostname: 35.224.227.100
    • database name: introspection
    • username: testUserIntrospection
    • password: HurricaneStartingSample1934

    That's it. This one command creates the artifacts for the mysql database.

  3. stepzen start

    The CLI asks you for your API endpoint. Accept the default or choose whatever two part name you like. A GraphiQL browser is launched on localhost:5000 where you can run whatever query you want. A good query to try is:

    {
        getCustomer(id: 1) {
            email
            name
            orderList {
            carrier
            shippingCost
            trackingId
        }
    }

    Feel free to try other queries using the GraphQL Builder tab in GraphiQL (it might be under the More tab).

Congrats! With a couple of simple steps, you have a running GraphQL endpoint against a MySQL database. Cool, right?

What did we do here?

  1. In one step, we deployed a React app that connected to a GraphQL backend running on StepZen, which had ip-api and openweathermap APIs available as connected GraphQL endpoint.
  2. In one more step, we connected to a MySQL database. This connection:
    • Automatically generated the types based on the table definitions.
    • Did not generate any types for tables that were just link tables.
    • Automatically stitched types together.
    • Automatically generated queries against the types.
  3. In one more step, modified your React app to display the data from your favorite query.

Modifying the auto-generated code

You might want to change the code in some ways. Instead of starting from stepzen import mysql, you can start from scratch, and create your own queries and types in a schema file. But for now, this section describes how to modify the generated code.

Add more queries

What if you wanted to be able to find customers using their emails? Simple. Add this block of code in the type Query section of mysql/mysql.graphql.

getCustomerByEmail (email: String!): Customer
    @dbquery (type: "mysql", query: "select * from customer where email = ?", configuration: "mysql_config")

Save the file. Your endpoint is automatically re-deployed. Now you can use the Explorer tab to build a query like:

query MyQuery {
    getCustomerByEmail(email: "john.doe@example.com") {
        name
    }
}

What did we just do? @dbquery is a custom directive. It takes one of two parameters: either table (in which case it makes some default assumptions of how to access data), or query (in which case, you specify how to access the data). In the latter, the ? does parameter substitution in the order in which they appear in the query, and here, since there is only one, the parameter email gets passed down into the SQL statement.

Look through the generated code for other examples of such queries.

Change the name of a query or type

Sometimes the autogenerated name of the query or type is not what you want.

  • Want to change the name of a query? Easy. For example, to change getAddressUsingCustomerAddress to customerAddress, change getAddressUsingCustomerAddress to customerAddress everywhere it appears. Use your favorite IDE to replace all occurrences.

  • Want to change the name of a type? For example, type Product to type Item.

    1. Change type Product to type Item.
    2. Change every query or mutation that returns either Product or [Product] to return Item or [Item].

Save your changes, and your new names appear.

Change the name of fields

Sometimes, the auto-generated names of fields are not to your liking. Changing field names is also easy. For example, to change the field title in type Product to name:

  1. Change the field in the definition type Product.
  2. Everywhere there is a query that returns either Product or [Product], remove the parameter table: "product" and add a new parameter query with a select *, title as item...:
    getProductList: [Product]
    @dbquery(
      type: "mysql"
      query: "select *, title as item from product"
      configuration: "mysql_config"
    )
    getProduct(id: Int!): Product
    @dbquery(
      type: "mysql"
      query: "select *, title as item from product where id = ?"
      configuration: "mysql_config"
    )

Basically, we have to make the MySQL query return the title (stored internally in MySQL) as item (needed for the GrahphQL definitions). That's it.

Adding more to our schema

Now that you've started with a MySQL database, there is no need to stop there. Let us connect the orders in our GraphQL endpoint with real, live, delivery information. This is really simple.

  1. Open a terminal, navigate to the stepzen directory of introspection, and issue stepzen import UPS

    It asks whether you want to use your keys to access UPS or StepZen's. Select ours.

  2. If you have stepzen start running from the previous step in the other terminal, you have to do nothing, otherwise issue stepzen start.

  3. Go to localhost:5000, and issue the following query in the GraphiQL browser:

    {
        getCustomer(id: 1) {
            email
            name
            orderList {
            carrier
            shippingCost
            trackingId
            delivery {
                status
                statusDate
            }
        }
    }

Voila! You have connected the data from MySQL to real, live data from UPS.

Where to go from here

If you look in your stepzen directory, you see that in mysql/mysql.graphql, there is a set of mutations available for you. Mutations won't work in the (read-only) database we provided you but you can explore mutations to create Customer, Order, etc. if you configure your own database. If you would like the sample ddl and data load files we used for this demo please drop us a note on our Discord channel.

Note: If your database is complex, or it is slow to respond, your stepzen import mysql command might time out. We are making it work for more and more complex schemas, but for now, you can always manually construct your types and the SQL calls that will support them (see examples above). And do let us know if support for more complex schemas is critical for your project.

As always, we encourage and welcome your feedback or questions on Discord.