Have a Question?
logo Learn
logo Learn
Space Cloud Essentials chevron_right Space Cloud Basics

Adding a Database


Let’s explore some awesome powers of Space Cloud. In this guide, we will:

  • Add a local Postgres instance to our project
  • Create tables
  • Make trainers and catch Pokemons 😍 (Insert operation)
  • Retrieve all trainers along with their Pokemons 😎 (Join operation)

Note: You can use any other database Space Cloud supports for this guide. We are using Postgres since its simply awesome.

Adding Postgres to our project

Start a local Postgres instance

The first step would be starting a local Postgres instance. Since we already have docker installed, let’s go forward using that.

Run the following command in a terminal

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

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

Once you have started postgres, we need to inspect its IP address. We’ll need this to make sure Space Cloud can reach postgres.

docker inspect some-postgres | grep -i '"IPAddress"' | head -1

You’ll get a response similar to the following:

            "IPAddress": "172.17.0.4",

Note the value of the IPAddress field. In my case it’s 172.17.0.4.

Add Postgres to our Space Cloud project

Let’s head over to the Database tab of Storage section in Mission Control. It will prompt you to add a database. Click on the Add Database button to open the following form:

Add Database screen

Select POSTGRESQL as the database.

Make sure you have the right connection string. Replace localhost with the IP address we noted earlier. My final connection string looks like:

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

Hit the Add Database button to add the database. If everything goes will, you should see a screen like this:

DB Overview Screen

Create tables

Now let’s add some tables.

Click on Add table button to open this form:

Create a project screen

Name this table trainer.

Copy-paste the following schema and hit save:

type trainer {
  id: ID! @primary
  name: String!
  pokemons: [pokemon] @link(table: "pokemon", from: "id", to: "trainer_id")
}

Note: Don’t worry if this syntax is new to you. It is GraphQL SDL which Space Cloud uses to create tables for you. You can read more about it later from here.

Similarly, to create a pokemon table, click on Add Table button once again.

Name this table pokemon.

type pokemon {
  id: ID! @primary
  name: String!
  power: Integer!
  trainer_id: ID! @foreign(table: "trainer", field: "id")
}

Making trainers and catching pokemons (insert operation)

Let’s insert some trainers along with their pokemons.

Head over to the API Explorer tab in Mission Control:

API Explorer

Try running the following query in the GraphiQL section:

mutation {
  insert_trainer(
    docs: [
      { 
        id: "1",
        name: "Ash",
        pokemons: [
          { id: "1", name: "Pikachu", power: 300 },
          { id: "2", name: "Snorlax", power: 500 }
        ] 
      },
      { 
        id: "2",
        name: "Misty",
        pokemons: [
          { id: "3", name: "Psyduck", power: 250 },
          { id: "4", name: "Staryu", power: 350 }
        ] 
      },      
    ]
  ) @postgres {
    status
  }
}

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

Note: You are inserting in two tables simultaneously in this operation. The query is executed as a single transaction.

Retrieve trainers along with their Pokemons (join operation)

Try running the following query in GraphiQL:

query {
  trainer @postgres {
    id
    name
    pokemons {
      id
      name
      power
    }
  }
}

The response should look something like this:

{
  "trainer": [
    {
      "id": "1",
      "name": "Ash",
      "pokemons": [
        {
          "id": "1",
          "name": "Pikachu",
          "power": 300
        },
        {
          "id": "2",
          "name": "Snorlax",
          "power": 500
        }
      ]
    },
    {
      "id": "2",
      "name": "Misty",
      "pokemons": [
        {
          "id": "3",
          "name": "Psyduck",
          "power": 250
        },
        {
          "id": "4",
          "name": "Staryu",
          "power": 350
        }
      ]
    }
  ]
}

The above query performs a join and returns us the joint result. Similarly, you can perform several types of joins, including joining database results with the response of your services.

You can read more about the different types of database query you can perform from the docs.

Next Steps

Awesome! We have just started our Pokemon journey without writing a single line of backend code. The journey ahead is undoubtedly going to be super exciting!

Continue to the next guide to deploy a dockerized Restful Nodejs app using Mission Control.