In this guide, I am going to show how you can easily set up a GraphQL API layer for your Postgres 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

Postgres and GraphQL Intro

Postgres is arguably the most advanced SQL database out there. The open-source community and the advanced set of features have made Postgres the fastest growing database. By extending its support for JSON type, Postgres seems to be a viable option for many who would otherwise have turned to document stores.

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, they don’t work with other databases. That’s where Space Cloud comes into the picture by providing GraphQL APIs for all your databases, including Postgres 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 web server which provides instant GraphQL and REST APIs on the database of your choice.

The coolest part about Space Cloud is that all the APIs are realtime. You can optionally subscribe to changes in the database. This functionality comes in handy while making real-time apps.

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!

To show your support❤️, ️️ you can give it a star on Github.

What if I don’t use Postgres?

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

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

Getting started with GraphQL and Postgres

In this guide, we are going to building a simple blogging application on top of Postgres, 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 Docker Compose to run both Space Cloud and Postgres for us.

Step 1: Get the docker-compose file

The spaceuptech/space-cloud/install-manifests repo contains all installation manifests required to deploy Space Cloud anywhere. Get the docker-compose file from there:

wget https://raw.githubusercontent.com/spaceuptech/space-cloud/master/install-manifests/quick-start/docker-compose/postgres/docker-compose.yaml

You should be able to see a docker-compose.yaml file with the following contents:

version: '3.6'
services:
  postgres:
    image: postgres
    restart: always
  space-cloud:
    image: spaceuptech/space-cloud
    ports:
    - "4122:4122"
    - "4126:4126"
    depends_on:
    - "postgres"
    restart: always
    environment:
      ## The DEV environment lets you use Mission Control (Admin UI) without login
      ## Change the dev mode to false if you want a login to your Mission Control UI
      DEV: "true"
      ## Uncomment next lines to change the login credentials of Mission Control UI
      # ADMIN_USER: "admin"
      # ADMIN_PASS: "123"
      # ADMIN_SECRET: "some-secret" # This is the JWT secret used for login authentication in Mission Control

Step 2: Run Space Cloud & Postgres

docker-compose up -d

Check if the containers are running:

docker ps

Step 3: Configure Space Cloud

If you exec into docker container of Space Cloud, you can see a config.yaml file generated by Space Cloud in the home 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.

Select PostgreSQL as your database and hit Next to create the project.

Configure database module:

Head over to the Database section:

Click Edit Connection button to open the following form:

Copy-paste the following connection string in it:

postgres://postgres:mysecretpassword@postgres:5432/postgres?sslmode=disable

If the connection was successful, you should be able to see something like these:

Creating Postgres schema

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

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

Space Cloud automatically creates a table named author in Postgres 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

Click on Add a table button in the Database section 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 Postgres 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 Postgres.

Inserting data

Inserting data into our Postgres 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" }
      ]
    }
  ]
 ) @postgres {
   status
 }
}

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

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 @postgres directive in the above query. For MySQL or MongoDB, change it to @mysql or @mongo respectively. And that’s it!

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"}
 ) @postgres {
   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 Postgres. Here’s a GraphQL query to do it:

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

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 Postgres 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 Postgres via a simple GraphQL query of Space Cloud. Try running the following GraphQL query:

query {
 author @postgres {
   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 by the arguments (table, from, to) of the @link directive, which we mentioned earlier. You can read more about @link directive from here.

Conclusion

Setting up an GraphQL layer on top of Postgres is not that difficult. Just point Space Cloud to a Postgres 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 Postgres 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 Postgres 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 Postgres 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.