How to Migrate from SQLite to Postgres Database on Localhost?

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 4.3.2
  • Operating System: Windows
  • Database: Postgres 14.5
  • Node Version: v16.17.1
  • NPM Version:
  • Yarn Version:

My initial installation of Strapi v4.3.2 with a “quickstart” option on localhost, so the database is default to SQLite. I was asked to switch to Postgres database to be consistent with our company policy. What is the easiest to switch over from the existing SQLite database to a new Postgres database. I don’t care if I lose my data since we do not have too much of content right now.

Thank you.

2

I would maybe suggest to export sqlite to sql and then import into Postgres.

1 Like
3

There is no migration system in Strapi, so, either you use any plugin to export all your data in JSON format file or you export SQL.

4

You must change this file:
config/database.js

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 5432),
      database: env('DATABASE_NAME', 'my-project'),
      user: env('DATABASE_USERNAME', 'root'),
      password: env('DATABASE_PASSWORD', '123456'),
      ssl: env.bool('DATABASE_SSL', false),
    },
  },
});

After you run the project, Strapi automatically create schema base on models.

5

And if you want to transfer your data to your new postgres database, I created a plugin for that: strapi-plugin-import-export-entries

2 Likes
6

Thank you everyone for the feedback. I get it to work. These are the steps:

  1. Installed PostgreSQL database locally on my computer
  2. Modified the database.ts using Postgres db connection

I lost all of my content, but it was only the development’s content.

7

I am curious what table in the database (PostgreSQL) does Strapi saves its content to? I can see all of my components tables, but not clear where the content is saved in which table.

8

Did you open the Strapi database once?

9

Yes. I even query the tables for all components. I only saw keys, and I even joined the tables to get a complete list. I don’t know where the actual content of each collection type is saved to which tables.

10

I found the content I was looking for. My collection type contains nested components and I did not look deep enough. The problem is solved.

11

I’ve been able to load my Sqlite database to Postgres using pgloader in terminal.

pgloader --debug --verbose --type sqlite '/PATHTOSTRAPI/.tmp/data.db' pgsql://USER:PASSWORD@HOST/DBNAME

Warning, It wasn’t working for me when PATHTOFILE had spaces. I moved the file in a directory without spaces and it worked.

12

…But it’s not working as expected. Maybe the DB is not the same for SQLite and Postgresql. I ended up using npm run strapi export (on my SQLite setup) then npm run strapi import (on my Postgresql setup); see documentation here.