PostgreSQL

---

Table of contents

• What is PostgreSQL?
• Why do we use PostgreSQL?
• Why are there no alternatives for during development?
• Setting up PostgreSQL
  • Prerequisites
  • Starting your Postgres instance
  • Cleaning the database and restarting it

What is PostgreSQL?

PostgreSQL is a free and open-source relational database management system (DBMS). It is one of the most popular DBMS's available.

Why do we use PostgreSQL?

In the past Skyra was using the DBMS RethinkDB, however when we started noticing that RethinkDB was no longer able to keep up with the growth of Skyra and the load on the database that she caused we started to look for other solutions. PostgreSQL was our first choice because of its popularity, support and ease of use.

Why are there no alternatives for during development?

Skyra uses PostgreSQL as database both in production and for development. This is primarily because we use TypeORM, an ORM API for TypeScript that makes interacting with the database really easy. Due to the way that TypeORM functions it is not possible to have entities that work for both a lightweight database (such as SQLite) as well as a full database to be used in the production environment.

Setting up PostgreSQL

Prerequisites

You will need to install Docker if you want to easily create and manage the PostgreSQL instance. You should also be sure to install docker-compose to make the process easiest.

Lastly we recommend you install PowerShell Core as we provide a PowerShell script to call various docker-compose commands. This is optional however, if you know your way around docker-compose or have an extension such as Docker for VSCode.

Starting your Postgres instance

The following commands can be used in succession to do the initial launch of the PostgreSQL instance and setting up the database schema.

First create a Docker volume:

docker volume create --name=postgres-data

Then start a Postgres container:

# When using Powershell
yarn dockerps start postgres

# When not using Powershell
docker-compose -p skyra -f ./.docker/docker-compose.yml up -d postgres

Next make sure the TypeScript has been compiled:

yarn build

And lastly use TypeORM to sync the schema. Note that TypeORM CLI will not exit automatically but once it says Schema syncronization finished successfully. you can exit the process with Control + C:

yarn typeorm schema:sync

Cleaning the database and restarting it

To restart a fully clean instance of the database use the following command:

yarn typeorm:schema:resync

This will go through the following steps:

1. Stop and remove the Docker container called pgsql
2. Remove the postgres-data Docker volume
3. Create a new Docker volume called postgres-data
4. Start a new Postgres Docker container called pgsql
5. Sleep for 3 seconds to let the Docker container start
6. Run yarn typeorm schema:sync to set up the schema.