It has never been easier for developers to run their infrastructure in the cloud. With just a few clicks, you can create and deploy all sorts of developers' products and services worldwide. This is true for the GraphQL-as-a-Service that StepZen offers and many other websites, APIs, and databases today. One of those databases that you can quickly deploy to the cloud is PlanetScale.

PlanetScale is a serverless database platform based on MySQL. It makes horizontally scaling your databases possible as it's built on Vitess. As PlanetScale is MySQL-based, you can use StepZen to introspect the database and create a GraphQL schema that you can deploy in seconds. Combining PlanetScale with StepZen gives you both a database and a GraphQL running in the cloud. These two scalable services prevent your team from worrying about the scalability of the products they build.

In this post, you'll learn how to set up PlanetScale and create an instant GraphQL API for it using StepZen.

Set up PlanetScale

Already using PlanetScale? You can create an instant GraphQL API for your database by running stepzen import mysql as you'll learn in Importing a MySQL database.

Setting up a database with PlanetScale can be done in just a few steps, as you can see in the official PlanetScale example for StepZen. You can use the PlanetScale CLI or their admin dashboard to set up a database. After setting up a new PlanetScale instance, you need to populate it with data before querying it with GraphQL.

PlanetScale allows you to import an existing MySQL database from a remote connection, but if you don't have a live database yet, you can use the mysql CLI from the command line to update a .sql file. In our official PlanetScale example, you can find a file named init.sql that can be used to populate this new database.

You should download the file init.sql to your machine or check out and clone the example repository. To populate the database with the mysql CLI, you should run the following command from your terminal or command prompt:

mysql -h [PLANETSCALE_HOST] -u [PLANETSCALE_USERNAME] -p[PLANETSCALE_PASSWORD] --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem < init.sql

You can get the values for PLANETSCALE_HOST, PLANETSCALE_USERNAME, and PLANETSCALE_PASSWORD by opening your database from the PlanetScale dashboard and clicking the "Connect"-button. You need to select "General" in the dropdown to get your database credentials.

After importing the .sql file that populates the database, the ER-diagram (Entity-Relationship Diagram) will look like the following:

You can query this data from PlanetScale using the console in the admin dashboard, the PlanetScale CLI, or any other connecting method to a MySQL database. We can also connect PlanetScale to StepZen, and get an instant GraphQL API based on its database schema.

Importing a MySQL database

Before running the command to import your PlanetScale database, make sure you have the StepZen CLI installed (npm i -g stepzen) and are logged into your StepZen account (stepzen login). You can find your credentials on the StepZen dashboard.

With StepZen, you can import a MySQL database using the CLI. StepZen will generate a GraphQL schema based on its tables and columns when importing this database. This schema will have queries for your queries and return types for the columns in these tables. To import a PlanetScale database, you need to run the command stepzen import mysql and provide your PlanetScale host, username, database name, and password to the prompts.

stepzen import mysql

? What would you like your endpoint to be called? api/with-planetscale

Downloading from StepZen...... done

? What is your host? <PLANETSCALE_HOST>
? What is your database name? <PLANETSCALE_DATABASE>
? What is the username? <PLANETSCALE_USERNAME>
? What is the password? [hidden] <PLANETSCALE_PASSWORD>

StepZen will automatically create a GraphQL schema for your PlanetScale database with a set of sample queries and mutations. The schema will be in the file mysql/index.graphql, and you can find the endpoint in stepzen.config.json. If you import more databases (or other data sources), the StepZen CLI will generate the schema in different directories. The file index.graphql brings all the schemas for your data sources together.

Also, by running stepzen import mysql a file called config.yaml is created that holds the credentials for PlanetScale. As PlanetScale only allows access over SSL/TLS, you need to append ?tls=true to the DSN configuration in this file:

configurationset:
  - configuration:
      name: mysql_config
      dsn: <PLANETSCALE_DSN>?tls=true

To explore the created GraphQL schema, you can open up the file mysql/index.graphql or start the GraphQL API as you'll learn in the next section.

Running a GraphQL API with StepZen

The GraphQL schema that StepZen generates for the PlanetScale database and the configuration details can be deployed instantly. A GraphQL API deployed with StepZen is performant, with a latency of under 100ms and a 99,99% availability. Similar to PlanetScale, StepZen also deploys serverless. To deploy your GraphQL schema, you need to run the following command:

stepzen start --dashboard=local

The default way to test your GraphQL endpoint is from the StepZen dashboard explorer. You can get a local GraphiQL IDE by running stepzen start with the --dashboard=local flag.

Your GraphQL API is successfully deployed to StepZen. You can query the production-ready endpoint at https://<YOUR_USERNAME>.stepzen.net/api/with-planetscale/__graphql or explore the GraphQL locally using GraphiQL on http://localhost:5001/api/with-planetscale. If you'd head over to the local GraphiQL interface, you can explore the GraphQL schema that was generated for your PlanetScale database.

When you, in example, use the query getCustomerList above, StepZen will only request the fields that are described in the query. That way, the loads on your database stay minimal, and the GraphQL API will be most performant. The query above will traverse to the following in SQL:

SELECT email, id FROM customer;

Next to exploring your GraphQL schema, you can run queries and mutations from this playground. A query has been generated for every database table, including its return type based on the columns in that table. But you can do more with StepZen, as you can also create relations between tables using custom directives.

Querying relations between tables

As MySQL databases are relational databases, you can make connections between different tables by using foreign keys. PlanetScale doesn't allow foreign key constraints, but we can still use keys to link tables. The ER diagram for the PlanetScale database has multiple relations between tables. With StepZen, you can build a GraphQL API by writing declarative code in GraphQL schemas. You can use the custom directive @materializer to combine data from different queries.

The table customer is linked to order through the key customerid in order. In the file mysql/index.graphql, a query has been generated to get all the orders, called getOrderList and has the return type Order. If you want to return the customer for every order, you need to add a new query called getCustomerById to the GraphQL schema:

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

And link this query to the GraphQL type Order on a new (relational) field called customer:

type Order {
  carrier: String!
  createdat: Date!
  customerid: Int!
  customer: [Customer]
    @materializer (query: "getCustomerById" arguments: [{ name: "id" field: "customerid"}])
  id: Int!
  shippingcost: Float
  trackingid: String!
}

When you request the field customer in the query getOrderList, the query getCustomerById will also be executed to get the fields you request from this relation. You can make any combination of queries with @materializer, even when there is no relational field described in your database.

What's next?

In this post, you've learned how to build a GraphQL API for PlanetScale using the StepZen CLI and write declarative code in a GraphQL schema. If you want to explore more, look at the complete example code in the StepZen examples repository on Github. Questions or want to share what you're building? Join us on Discord.