How to Migrate from SQLite to Postgres Database on Localhost?

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.

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

1 Like

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.

You must change this file:

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: env('DATABASE_HOST', ''),
      port:'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.

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


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.

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.

Did you open the Strapi database once?

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.

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.

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.

…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.