How to Install & Configure Strapi with PostgreSQL

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:

npm run develop

So that my Strapi project will be started in watch mode.

That's all there is to it, our Strapi application is now using PostgreSQL instead of the --quickstart version that comes with SQLite.

Freddie Freddie 3 years ago 0
Login to Comment
No comments have been posted yet, be the first one to comment.
Enable & Write Cron Jobs for Strapi
Enable & Write Cron Jobs for Strapi
A cron job is a useful utility that can be found in any unix-like operating system. It can be used to schedule time-based commands without human intervention. With Strapi, we can use cron jobs to import data or run application tasks automatically. To write cron jobs in Strapi is super...
How to Create & Setup Dokku Applications & Databases
How to Create & Setup Dokku Applications & Databases
As I have mentioned many of times before, Dokku rocks! Dokku is a free and open-source project that helps you build and manage the lifecycle of your applications. Dokku does is it by leveraging the power of Docker and Heroku Buildpacks. It's perfect for solo developers or small teams ...
Fix PostgreSQL error: Stale postmaster.pid file on macOS
Fix PostgreSQL error: Stale postmaster.pid file on macOS
Sometimes when you mac shuts down unexpectedly while running a PostgreSQL database it can result in the following error: Stale postmaster.pid file. The data directory contains an old postmaster.pid file. FATAL: lock file “postmaster.pid” already exists Fix PostgreSQL error: Stale pos...