How to Install & Configure Strapi with PostgreSQL

If you have used Strapi before, you know how fast and easy it is to install with the "--quickstart" flag that includes SQLite as a default database.

However, if you want to use a database like MongoDB or PostgreSQL the install and configuration requires a little bit more attention. In this tutorial we are going to install Strapi and configure it to use PostgreSQL as a database.

To install Strapi with PostgreSQL there are some extra steps involved when compared to installing Strapi with the --quickstart flag. I will cover them below.

Requirements

To follow along in this tutorial there are a few requirements.

Node.js >= 10.x installed on your machine
NPM >= 6.x installed on your machine
PostgreSQL installed on your machine

This tutorial will not cover the installation of the following tools.

Navigate to the folder where you want Strapi installed

cd directoryname

Install Strapi without the --quickstart flag

Strapi can be installed using NPM or Yarn, choose your favorite.

#NPM

npx create-strapi-app projectname

#Yarn

yarn create strapi-app projectname

In the meantime we can create out PostgreSQL database

Start by activating Postgres in your terminal by runnning:

psql postgres

You will know that your Postgres instance is activated if the terminal prepends "postgres=#"

Start by creating your database

CREATE DATABASE dbname;

Now it's time to create a user role.

CREATE ROLE db_user WITH LOGIN PASSWORD 'password' CREATEDB;

When that's done we can go ahead an grant that user priviliges to our newly created database.

GRANT ALL PRIVILEGES ON DATABASE dbname TO db_user;

If successful your terminal should spit back "GRANT".

That's all we need to continue our installation and setup of Strapi with PostgreSQL.

Configure Strapi to be used with PostgreSQL

As we don't used the --quickstart flag, our terminal will ask us a few questions moving forward.

Choose: Custom (manuall settings)

? Choose your installation type
  Quickstart (recommended)
❯ Custom (manual settings)

Next it ask us which data base we want to use, in this case we will select postgres.

? Choose your installation type Custom (manual settings)
? Choose your default database client
  sqlite
❯ postgres
  mysql
  mongo

The third question will ask us what the name of our database is going to be. Here you type in the name of the PostgreSQL database we created earlier.

? Choose your installation type Custom (manual settings) ? Choose your default database client postgres ? Database name: (backend)

Now it is time to select the host adress. Your host adress is most certainly your localhost which is 127.0.0.1, otherwise select your host address.

? Choose your installation type Custom (manual settings)
? Choose your default database client postgres
? Database name: dbname
? Host: (127.0.0.1)

Strapi also needs to know the port of PostgreSQL, the standard port that is used by postgres is 5432 and will most certainly be that in your case as well. Otherwise specify your own custom port number.

? Choose your installation type Custom (manual settings)
? Choose your default database client postgres
? Database name: dbname
? Host: 127.0.0.1
? Port: (5432)

Now it is time add the database user role that we created earlier "db_user"

? Choose your installation type Custom (manual settings)
? Choose your default database client postgres
? Database name: dbname
? Host: 127.0.0.1
? Port: 5432
? Username:

And the password:

? Choose your installation type Custom (manual settings)
? Choose your default database client postgres
? Database name: dbname
? Host: 127.0.0.1
? Port: 5432
? Username: db_user
? Password:

After that done, Strapi will ask us if we want to enable SSL connection. This is for the use of https://. In this case I will Choose N (No) as this is being developed locally for test purposes.

? Choose your installation type Custom (manual settings)
? Choose your default database client postgres
? Database name: dbname
? Host: 127.0.0.1
? Port: 5432
? Username: db_user
? Password: ********
? Enable SSL connection: (y/N)

When the installation is done (it can take a minute or two) you will be promted with the following suggestions.

Make sure you have navigate to your current projectname folder and run the command that suits you.

Your application was created at /Users/u/development/project/projectname.

Available commands in your project:

  npm run develop
  Start Strapi in watch mode.

  npm run start
  Start Strapi without watch mode.

  npm run build
  Build Strapi admin panel.

  npm run strapi
  Display all available commands.

You can start by doing:

  cd /Users/u/development/project/projectname
  npm run develop

In my case I am going to chose: