How to Install & Configure Strapi with PostgreSQL

PostgreSQL PostgreSQL
Strapi Strapi

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.

Common Errors & Questions

I get this strange error when installing Strapi and PostgreSQL and trying to run "npm run develop", why? 

npm ERR! code ENOENT
npm ERR! syscall open
npm ERR! path /Users/user/package.json
npm ERR! errno -2
npm ERR! enoent ENOENT: no such file or directory, open '/Users/user/package.json'
npm ERR! enoent This is related to npm not being able to find a file.
npm ERR! enoent

This error is not actually related to Strapi, nor PostgreSQL. It's npm related, or more related to your actions. You have to make sure that you are in your main Strapi project folder before interacting with npm, since that where it is install and that's where you package.json file is located, which your project relies on.

I hope you've found this tutorial helpful. If you run into any errors or have any questions, just let me know in the comments below. I always try to update my tutorials to reflect any issues that have plagued other users when following along.

Did You Enjoy the Tutorial?

Consider sending some love to Freddie the creator.

Comments (0)

You need to be logged in to comment..

Topics Mentioned in this Tutorial

PostgreSQL
4
0
7
PostgreSQL, also known as Postgres, is a relational database management system (RDBMS) that is free and open-source. The software is widely popular for storing relation data for web-base applications. PostgreSQL emphasise extensibility and technical standards compliance. Which make the database used for a wide variety of web services with many concurrent users. PostgreSQL can be deployed on a single machine or spread out on clusters among different data centers....
Strapi
2
0
0
Strapi is a headless CMS built with JavaScript and NodeJS that makes it really quick and easy to create a content rich REST or GraphQL API....