Instant GraphQL APIs on MySQL

GraphQL and MySQL are two popular technologies that developers love!

In this guide, I am going to show how you can easily set up a GraphQL API layer for your MySQL using Space Cloud in a couple of minutes.

The keyword to note here is instant. Which means you won’t have to write a GraphQL backend yourself. Sounds like a magic right? Let me explain it to you in a minute.

Table of contents

• MySQL and GraphQL intro
• What is Space Cloud?
• What if I don’t use MySQL?
• Getting started with GraphQL and MySQL
• Setup
• Creating MySQL schema
• Running GraphQL queries on Space Cloud
• Conclusion

MySQL and GraphQL Intro

MySQL is one of the most popular SQL databases out there, next only to Oracle. The ease of setup and learning MySQL makes it a popular choice for most web projects. If you are an organization that needs a robust database management tool but are on a budget, then MySQL might be ideal for you.

GraphQL, on the other hand, is a query language for your APIs, gradually replacing REST. It allows you to describe your data needs precisely and provides you with the exact data you had asked for, solving age-old under fetching and over fetching problems. If you are new to GraphQL and want to learn more about it, check out its official site.

However, to avail these excellent benefits, you need to set up a GraphQL API on the backend by writing resolvers. Having to write a GraphQL server adds friction to its adoption. While many tools help you auto-generate GraphQL APIs for Postgres, there aren’t many that work with MySQL. That’s where Space Cloud comes into the picture by providing GraphQL APIs for your MySQL without writing a backend. So, let’s see what Space Cloud is all about.

What is Space Cloud?

To keep things simple,

Space Cloud is an open-source, serverless platform to develop, secure and scale your applications. It provides instant GraphQL and REST APIs on top of any database and microservice.

The coolest part about Space Cloud is that the database APIs are realtime. i.e. You can optionally _subscribe_ to changes in the database. This functionality comes in handy while adding realtime functionalities to your app.

Since Space Cloud also has a robust security module built into it, you can consume these APIs directly from the frontend. So in most use cases, just making an Angular or React app using the Space Cloud APIs should be the only code you write!

What’s more? You can deploy your existing HTTP workloads to Kubernetes/Docker using Space Cloud via a single command. Space Cloud will automatically scale these workloads including scale down to zero. It will also take care of end to end encryption, traffic splitting and much more! Check out all the features of Space Cloud

Already in love with Space Cloud? Show your support ❤ by giving us a star on Github.

What if I don’t use MySQL?

Space Cloud provides GraphQL APIs for MongoDB, Postgres, SQL Server and MySQL (and their compatible databases) out of the box. If you use any other database apart from MySQL, then these guides can help you get started:

• Getting started with Postgres
• Getting started with MongoDB

Cool, that’s enough of history and features. Let’s dive straight into it!

Getting started with GraphQL and MySQL

In this guide, we are going to building a simple blogging application on top of MySQL, which has authors and posts.

Each author can have multiple posts, whereas a post can only belong to a single author. Note that this is a one-to-many relation.

Note: Don’t worry if you get stuck somewhere. You can always ping me on our Discord channel.

Setup

We are using space-cli to run both Space Cloud and MySQL for us.

Step 1: Install the space-cli

space-cli is the cli tool for Space Cloud. You can download a version for your particular platform:

• Linux
• MacOS
• Windows

Unzip the compressed archive.

For Linux / Mac: unzip space-cli.zip && chmod +x space-cli

For Windows: Right-click on the archive and select extract here.

Copy the space-cli binary to your environment path variable for global usage.

For Linux / Mac: Copy the space-cli to /usr/local/bin. You may have to use sudo depending on the permissions of your /usr/local/bin.

For Windows: Add the path of the space-cli.exe to the environment variable PATH for making space-cli accessible globally.

To make sure if the space-cli binary is installed correctly, type the following command:

For Linux / Mac: space-cli --version

For Windows: space-cli.exe --version

This prints the space-cli version.

Step 2: Run Space Cloud & MySQL

We can set up all Space Cloud components (gateway and runner) via space-cli using a single command.

space-cli setup --dev

The setup command selects Docker as a target by default and runs all the containers required to setup Space Cloud. On successful installation it generates an output similar to this one:

INFO[0000] Setting up Space Cloud on docker. 
INFO[0000] Fetching latest Space Cloud Version 
INFO[0000] Starting container space-cloud-gateway… 
INFO[0000] Image spaceuptech/gateway:latest already exists. No need to pull it again 
INFO[0000] Starting container space-cloud-runner… 
INFO[0000] Image spaceuptech/runner:latest already exists. No need to pull it again
INFO[0001] Space Cloud (id: "local-admin") has been successfully setup! 👍 
INFO[0001] You can visit mission control at http://localhost:4122/mission-control 💻 
INFO[0001] Your login credentials: [username: "local-admin"; key: "KkYr6FvgYsvr"] 🤫

Verify Space Cloud installation

Verify the installation by running the following docker command:

docker ps

You should see an output similar to this:

CONTAINER ID        IMAGE                        COMMAND             CREATED              STATUS              PORTS                                            NAMES
507ce4042486        spaceuptech/runner:latest    "./app start"       About a minute ago   Up About a minute                                                    space-cloud-runner
33a5a7a9be3a        spaceuptech/gateway:latest   "./app run"         About a minute ago   Up About a minute   0.0.0.0:4122->4122/tcp, 0.0.0.0:4126->4126/tcp   space-cloud-gateway

Run MySQL

We will be running a fresh MySQL database in this guide. However, you can even add your existing databases to Space Cloud.

We can run a MySQL database inside a docker container via space-cli using the following command:

space-cli add database mysql --alias mysql

It might take some time if you did not have the mysql image cached locally.

Once we have started MySQL, we need to know its IP address. Luckily, the space-cli add database command also creates a domain name for our database, which is of the following format:

<alias-name>.db.svc.cluster.local

Since we had set the — alias to mysql in the add database command, the domain name for our MySQL translates to:

mysql.db.svc.cluster.local

Step 3: Configure Space Cloud

If you exec into docker container of Space Cloud’s gateway, you can see a config.yaml file generated by Space Cloud in the /app directory.

Space Cloud needs this config file to function. The config file is used to load various information like which databases to use, their connection strings and security rules.

Space Cloud has it’s own Mission Control (admin UI) to configure all of this quickly.

Open Mission Control:

Head over to http://localhost:4122/mission-control to open Mission Control.

Note: Replace localhost with the address of your Space Cloud if you are not running it locally.

Create a project:

Click on Create a Project button to open the following screen:

Give a name to your project.

Hit Create Project button to create the project.

Add database to your project

After creating the project, the next step is to add a database to your project:

Select MySQL as your database.

Replace localhost with the domain name we noted earlier to get the final connection string as:

root:my-secret-pw@tcp(mysql.db.svc.cluster.local:3306)/

Hit Add database button. If the connection was successful, you would get a notification for a successful connection. After that you would be directed to the overview page of your project:

Creating MySQL schema

Space Cloud provides you with a neat GraphQL SDL to describe the schema for your MySQL tables which looks something like this:

type author {
  id: ID! @primary
  name: String!
}

Space Cloud automatically creates a table named author in MySQL based on the schema. Thus, there is no need to deal with SQL statements to create tables.

The best part is that the schema SDL provided by Space Cloud is declarative in nature.

What this means is that Space Cloud always tries to accomplish the schema you have provided by only making those changes which are required. For example, let’s say you added one more field to your schema like this:

type author {
  id: ID! @primary
  name: String!
  description: String
}

Note: Space Cloud won’t create the entire table again since it’s already there. Instead, it just uses the ALTER TABLE statement to add a column. This ability in Space Cloud allows us to focus on our application rather than the database.

Creating author table

Head over to the Database section.

Click on Add a table button in the Overview tab to open the following form:

Give your table name as author.

Copy-paste the following schema and hit save:

type author {
  id: ID! @primary
  name: String!  
  posts: [post] @link(table: "post", from: "id", to: "author_id")
}

This schema creates a table author with id and name fields. The type ID is nothing but an auto-generated unique id whereas @primary directive tells Space Cloud to make the id field as a primary key of the table. Checkout data modelling in Space Cloud to learn more.

Note: The posts field is not a physical field in the author table. It’s just a virtual field that links to the post table to make GraphQL queries and mutations simpler from the frontend. We are going to cover it in more details below.

Creating post table

Once again click on the Add button in the Database section.

Give your table name as post.

Copy-paste the following schema and hit save:

type post {
  id: ID! @primary
  title: String!
  author_id: ID! @foreign(table: "author", field: "id")
}

Notice the @foreign directive. It creates a foreign key on the author table to maintain the integrity of the relation. Read more about modelling relations in Space Cloud.

Great! We have created all the tables required for this guide. Its time to play around with MySQL using the auto-generated GraphQL APIs.

Running GraphQL queries on Space Cloud

Mission Control has an embedded GraphiQL IDE which lets us fire queries without having to build a frontend.

Head over to the Explorer section.

Let’s start by creating some authors and posts, which translates to inserting a few records in your MySQL.

Inserting data

Inserting data into our MySQL is as simple as firing a GraphQL query for us. So let’s try inserting a few authors along with their posts. Below is a GraphQL query that helps us do that. Try running this GraphQL query in the GraphiQL editor:

mutation {
 insert_author(
   docs: [
    { 
      id: "1", 
      name: "Dan",
      posts: [
        { id: "1", title: "Introducing Hooks" },
        { id: "2", title: "React vs Vue" }
      ]
    },
    { 
      id: "2", 
      name: "Ryan",
      posts: [
        { id: "3", title: "Context API" },
        { id: "4", title: "React + Redux" }
      ]
    }
  ]
 ) @mysql {
   status
   error
 }
}

On successful insert, you should be able to see the status as 200 which means you have successfully inserted the documents in your MySQL.

The docs argument is nothing but the array of records that you want to insert in your table.

If you remember, we mentioned a @link directive on the posts field in the author table along with the argument — table: “post”. Space Cloud uses this information to insert the posts array correctly into the post table. It also inserts the post.author_id field automatically based on the foreign key that we mentioned.

Note: Even if we leave the id field blank, Space Cloud auto-generates a unique random string as id for us.

If you have noticed, we have used a @mysql directive in the above query. Replace it with whatever alias name you have kept while adding the database in Mission Control.

Querying data with filters

Having inserted authors and their posts, let’s try to query posts of a particular author. It’s nothing but a filtering operation.

Try running the following query in the GraphiQL editor:

query {
 post (
   where: {author_id: "1"}
 ) @mysql {
   id
   title
 }
}

As you can see, we only got the posts that belonged to author_id 1. Check out this guide for a complete list of filtering options.

Updating data

Updates require two information: a where clause to filter the documents to be updated and at least one update operator.

Let’s say you want to update the name of a particular author in your MySQL. Here’s a GraphQL query to do it:

mutation {
 update_author (
   where: { id: "2"},
   set: {name: "Ryan Florence"}
 ) @mysql {
   status
   error
 }
}

You can also perform various operations like incrementing, decrementing, multiplying. Check out this guide for a complete list of update operations.

Performing joins

Now comes the fun part, i.e. fetching relational data from your MySQL as a nested JSON on frontend.

Let’s say we want to show a list of authors along with the title of their posts. You can easily query such relational data from MySQL via a simple GraphQL query of Space Cloud. Try running the following GraphQL query:

query {
 author @mysql {
   id
   name
   posts {
     title
   }
 }
}

The response should look something like this:

{
  "author": [
    {
      "id": "1",
      "name": "Dan",
      "posts": [
        {
          "title": "Introducing Hooks"
        },
        {
          "title": "React vs Vue"
        }
      ]
    },
    {
      "id": "2",
      "name": "Ryan Florence",
      "posts": [
        {
          "title": "Context API"
        },
        {
          "title": "React + Redux"
        }
      ]
    }
  ]
}

The query that we used above performs a join operation on the backend between author and post table with the condition — author.id == post.author_id. This condition is derived from the arguments (table, from, to) of the @link directive, which we mentioned earlier. You can read more about @link directive from here.

Although Space Cloud supports most of the database operations out of the box, it also provides an escape hatch for advanced use cases in the form of prepared queries.

Conclusion

Setting up an GraphQL layer on top of MySQL is not that difficult. Just point Space Cloud to a MySQL instance are you are good to consume GraphQL in your applications.

As an advantage, GraphQL opens up tons of new possibilities like performing joins and making the already popular MySQL even simpler to use. We are very excited about it and are going to double down on making our GraphQL support more robust.

The declarative schema for MySQL allows us to focus more on our applications rather than the database.

The future, however, seems to be amazing. With the ability to do cross-database joins with MySQL and other NoSQL databases mean we can build enterprise-grade apps that leverage the best of both worlds.

Show your support❤️ by giving it a star on Github.