Is this the right way to switch local db from sqlite to postgres?

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 3.2.4
  • Operating System: macOS High Sierra 10.13.6
  • Database: SQLite 5.0.0, PostgreSQL 8.4.1
  • Node Version: 12.18.4
  • NPM Version: >=6.0.0
  • Yarn Version: 1.22.4

I have a blog with posts and I’m trying to write raw db queries in my post.js controller to calculate consecutive day streaks. My production is deployed to Heroku in postgres while my local db is sqlite, and since the SQL queries are written differently for sqlite vs postgres, I wanted to change my local db to postgres so that I can write the same SQL query and test it in local correctly. So I decided to switch my local db from sqlite over to postgres, but there’s some steps missing in the docs about doing this, so wanted to double check if I missed out anything here (noob alert here, sorry for being cautious but just wanted to be sure).

First I created a postgres db in my strapi app’s root folder - followed the steps in this tutorial:

psql postgres
CREATE DATABASE dbname;
CREATE ROLE db_user WITH LOGIN PASSWORD 'password' CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE dbname TO db_user;

Next, I followed the strapi docs under Configuration and pasted the code to config/database.js

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST', 'localhost'),
        port: env.int('DATABASE_PORT', 5432),
        database: env('DATABASE_NAME', 'db_name'),
        username: env('DATABASE_USERNAME', 'db_user'),
        password: env('DATABASE_PASSWORD', 'password'),
        schema: 'public',
      },
      options: {},
    },
  },
});

Then I restarted the server and the app ran fine. I lost my placeholder data from sqlite but no biggie - I recreated them again easily.

So my question again is, did I miss out anything? Any other config that I should update, or import etc before I merge these changes to my production app in Heroku?

I’m unfamiliar with backend dev, and database configurations are kinda stressful for me, so would be super thankful for anyone familiar to advise. Thanks!!

1 Like
2

You should be fine in terms of the local migration, have you followed our docs to create a ./config/env/production/database.js file for Heroku?

1 Like
3

Thanks for confirming! Yes the production database.js is done already. I just wanted to make sure things are all set before I push to the Heroku master.

4

Yup so long as Heroku is loading the production node environment you should have no issues

1 Like
5

@jasonleow, I am going the exact same process as you. How did you created your Postgres database within your Strapi project? I created my Postgres DB via pgAdmin UI and I cannot dictate the path where I want my database to be created.

I managed to get my existing Strapi project to connect to the new PostgreSQL database, but now I have a .tmp/database.db (SQLite) in project as well.

6

I created this 2 years ago - my method might be outdated for your Strapi version. I recall Strapi docs having pretty good walk-through for it… did it not work for you?

7

I got it to work.

Thank you

8

Issue Resolved