Rapid API development tutorial with Docker + PostgreSQL + PostGraphile

The combination of Docker, PostgreSQL and PostGraphile is your ultimate weapon to get your application database and a GraphQL API running… in few minutes.

6 min readJul 24, 2018

This project is available on my Github repository docker-postgresql-postgraphile.

A couple of month ago I saw the following video of Caleb Meredith giving an impressive demo of PostGraphile (formerly known as PostGraphQL). It basically shows how he built a simple PostgreSQL database and a GraphQL API in 7 minutes… Impressive!

So last week-end I finally decided to experiment with it as I would like to use it for a personal project. But I did not want to use Postgraphile by itself, I wanted to do it with Docker so that my project could be easily ported and ran everywhere. While doing so, I took care of documenting step by step what I was doing so that I could reuse it for myself as a tutorial.

I’m sharing it below with a big disclaimer… I had never used Docker, PostgreSQL nor PostGraphile before, so I figured out this could be useful to other noobs like me.

Note the following was done on a virtual machine running Linux Ubuntu 18.04 LTS. A the end of this tutorial, you will have a GraphQL API exposing data from a PostgreSQL database, both running locally on your machine in separate Docker containers.

Install Docker

Add the Docker repository to your Linux repository. Execute the following commands in a terminal window.

$ sudo apt-get update
$ sudo apt-get install apt-transport-https ca-certificates curl software-properties-common
$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
$ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

Install Docker Community Edition.

$ sudo apt-get update
$ sudo apt-get install docker-ce

Add your user to the docker group to setup permissions. Make sure to restart your machine after executing this command.

$ sudo usermod -a -G docker <username>

Test your Docker installation. Executing the following command will automatically download the hello-world Docker image if it does not exist and run it.

$ docker run hello-world

Remove the hello-world image once you're done.

$ docker image ls
$ docker rmi -f hello-world

Run PostgreSQL Docker image

The following command downloads a PostgreSQL docker image based on Alpine. Alpine is a minimal Linux distribution widely used for containers.

$ docker pull postgres:alpine

Run a Docker container using the postgres:alpine image. In order to preserve the database and data created in the container, the image must be run with the following parameters:

$ docker run --name psql -v /home/user/project/postgresql/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=1234 -p 5432:5432 -d postgres:alpine

Parameters description:

name: Name of the container. Value is psql in the example above but you can pick a name of your choice.
v: Volume, mapping between the project folder on your host machine and the PostgreSQL folder in your container, in format host_folder:container_folder. All the files generated in the container_folder will be copied in the host_folder so that you can preserve and retrieve your data when stopping/restarting the container. Replace the host folder by the destination of your choice. If it does not exist it will be automatically created.
e: Environment variables for the container. POSTGRES_PASSWORD sets the password of PostgreSQL superuser. Value is 1234 in the example above but you should pick a stronger password of your choice.
p: Port, mapping between the port of your host machine and the port of your container, in format host_port:container_port.
d: Run the container as a deamon.
postgres:alpine: Name of the Docker image to use to run the container.

Create PostgreSQL database

The database will contain two tables:

parent_table
child_table

There is a relationship between parent_table and child_table as one parent record can have one or several children records. It is a "one-to-many" relationship (one parent, many children). The parent_table_id column in the child_table will be used as a foreign key of the parent_table.

Install psql client

Install the psql client to connect to the container running PostgreSQL. This will allow us to execute SQL statements on the container.

$ sudo apt-get install postgresql-client

Create database and tables

The following command opens a connection to the PostgreSQL container. When prompted for the password, type the value you used for the POSTGRES_PASSWORD parameter.

psql -h 0.0.0.0 -p 5432 -U postgres

Parameters description:

h: Host, defaulted to 0.0.0.0 since the container runs on your local machine.
p: Port, the port opened in the previous step is 5432 which is the default port for PostgreSQL.
U: User, the database superuser is postgres by default.

Execute the following SQL statements to create the database and tables:

CREATE DATABASE your_database;
\connect your_database;CREATE SCHEMA your_schema;CREATE TABLE your_schema.parent_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);COMMENT ON TABLE your_schema.parent_table IS
'Provide a description for your parent table.';CREATE TABLE your_schema.child_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    parent_table_id INTEGER NOT NULL REFERENCES your_schema.parent_table(id)
);COMMENT ON TABLE your_schema.child_table IS
'Provide a description for your child table.';

Insert data

Execute the following SQL statements to populate the tables with some dummy data.

INSERT INTO your_schema.parent_table (name, description) VALUES
('Parent name 1', 'Parent description 1'),
('Parent name 2', 'Parent description 2'),
('Parent name 3', 'Parent description 3');INSERT INTO your_schema.child_table (name, description, parent_table_id) VALUES
('Child name 1', 'Child description 1', 1),
('Child name 2', 'Child description 2', 2),
('Child name 3', 'Child description 3', 3);

Verify data was correctly inserted with the following SQL statement.

SELECT A.id, A.name, A.description, B.id, B.name, B.description
FROM your_schema.parent_table A
INNER JOIN your_schema.child_table B ON A.id=B.parent_table_id;

It should output the following data set.

id |     name      |     description      | id |     name     |     description     
----+---------------+----------------------+----+--------------+---------------------
  1 | Parent name 1 | Parent description 1 |  1 | Child name 1 | Child description 1
  2 | Parent name 2 | Parent description 2 |  2 | Child name 2 | Child description 2
  3 | Parent name 3 | Parent description 3 |  3 | Child name 3 | Child description 3
(3 rows)

Exit psql by typing the command \q.

Run PostGraphile Docker image

The following command downloads a PostGraphile docker image.

$ docker pull graphile/postgraphile

In order to connect the PostGraphile container to the PostgreSQL container, the graphile/postgraphile image must be run with the following parameters:

$ docker run --name pgql -p 5000:5000 -d graphile/postgraphile --connection postgres://postgres:1234@host:5432/your_database --schema your_schema --watch

Parameters description:

name: Name of the container. Value is pgql in the example above but you can pick a name of your choice.
p: Port, mapping between the port of your host machine and the port of your container, in format host_port:container_port. In the command above the ports are both set to 5000.
d: Run the container as a deamon.
graphile/postgraphile: Name of the Docker image to use to run the container.
connection: Connection string to the PostgreSQL container and database, in format postgres://db_user:password@host:port/your_database. In the command above the values are set as below:
db_user: PostgreSQL superuser is postgres.
password: The value you used for the POSTGRES_PASSWORD parameter when running the postgres container.
host: IP address of your local machine where the postgres container is running. You can find it by typing ifconfig in a terminal window.
port: Port opened on the postgres container. The port opened in the previous step is 5432 which is the default port for PostgreSQL.
your_database: Name of the PostgreSQL database.
schema: Name of the schema in the PostgeSQL database.
watch: The watch flag automatically detects changes done to the database and reload the GraphiQL interface accordingly.

Test GraphQL API

Navigate to http://0.0.0.0:5000/graphiql to visualize the API GraphiQL documentation. From there you can execute queries and mutations such as the examples below. The API endpoint itself is http://0.0.0.0:5000/graphql.

Query example

query {
  allParentTables {
    nodes {
      id
      name
      description
      childTablesByParentTableId {
        nodes {
          id
          name
          description
        }
      }
    }
  }
}

Mutation example

mutation {
  createParentTable(
    input: {
      parentTable: {
        name: "Parent name 4"
        description: "Parent description 4"
      }
    }
  ) {
    parentTable {
      id
      name
      description
    }
  }
}