Has anyone successfully migrated data from sqlite to postgresql?

Questions and Answers
1

I’ve tried out the pgloader tool that allows me to move data from other databases to postgresql but this just left me with an empty sidebar on the strapi admin homepage.

2

I am not sure if that can help, but databases can have their own peculiarities, so to speak. I migrate them almost always via some SQL interface - typical, old fashioned SQL file with INSERT stuff. But, obviously, it can depend on database complexity and other factors.

3

Oh alright is this some sort of tooling?

4

Well, actually I use PhpStorm database functionality. At least, when during data modification before insert there is some mismatch it lets me know.

5

I’ve done it I think except for the dates of creation and update of items in my collection which wasn’t really important to me. I used the sequel cli tool.
Here’s a guide https://stackoverflow.com/questions/34680812/how-to-migrate-from-sqlite3-to-postgresql

6

I am also trying to use the pgloader tool to do a direct migration from sqlite to postgresql. The problem is that datetimes are stored in milliseconds since epoch in sqlite. The pgloader tool assumes you are inputting SECONDS epoch unix time. Thus, the whole pgloader migration fails.

If you can convert all the datetimes in the sqlitedb to seconds epoch time then pgloader tool should work (and possibly any other migration tool).

The created_at and update_at fields are important to me so I’m trying to do a custom transformation written in Lisp (pgloader is written in Lisp - see their docs) just to do the simple msec to sec unit conversion. It is a massive pain as I’ve never used Lisp before!

Here is my pgloader script so far, if someone wants to take it further. Just run pgloader <script-name> This will succesfully migrate the DB, but the datetimes will be way off due to the incorrect units:

load database
    from sqlite://data.db
    into postgresql://<user>:<secret>@localhost:5432/<my-db>

with include drop, create tables, create indexes, reset sequences

cast
    type datetime
        to timestamptz
    using (lambda (x)
            (unix-timestamp-to-timestamptz x ) 
    )
   
set work_mem to '16MB', maintenance_work_mem to '512 MB';

The other option is to see if the Strapi team can store datetimes in seconds instead of milliseconds as default. Considering sqlite db is the default starter option for strapi projects, I think it will bring great value to the community.

2 Likes
7

I’ve just got sqlite to postgresql migration working with pgloader. This is the updated pgloader script to use. It should convert epoch unix time from milliseconds to seconds on-the-fly.

load database
    from sqlite://data.db
    into postgresql://<user>:<secret>@localhost:5432/<my-dbname>

with include drop, create tables, create indexes, reset sequences

cast
    type datetime
        to timestamptz
    using (lambda (x)
            (unix-timestamp-to-timestamptz (if x (floor x 1000)) )
        )

set work_mem to '16MB', maintenance_work_mem to '512 MB';
2 Likes
8

You will also need to add quote identifiers to this list of options. pgloader defaults to Postgresql’s lower-case convention. Adding this option will override it by surrounding column names with double quotes.

Reference: Column case is ignored · Issue #649 · dimitri/pgloader · GitHub

10

I converted the timestamps after migrating the postgresql db:

  1. Create postgres database
postgres=# create database dbname;
  1. Migrate sqlite to postgres
$ sequel -C sqlite://data.db postgres://user:password@localhost/dbname
  1. Connect to your database and export all the date/time/timestamp columns in your database to a csv file:
Copy (select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('timestamp without time zone',
                        'timestamp with time zone',
                        'time with time zone',
                        'time without time zone',
                        'interval', 'date')
      and col.table_schema not in ('information_schema', 'pg_catalog')
order by col.table_schema,
         col.table_name,
         col.ordinal_position) To '/tmp/ts-columns.csv' With CSV DELIMITER ',' HEADER;
  1. Parse csv file and generate update statements
// install csv-parse
$ yarn add csv-parse

// gen-updates.js
const fs = require('fs')
const { parse } = require('csv-parse/sync')

const data = fs.readFileSync('/tmp/ts-columns.csv').toString()
const records = parse(data, { columns: true, skipEmptyLines: true })

const file = fs.createWriteStream('updates.sql')
for (const {table_name, column_name} of records) {
    const update = `update ${table_name} set ${column_name} = to_timestamp(extract(epoch from ${column_name})/1000) where ${column_name} is not null;\n`;
    file.write(update)
}
file.end()
  1. Execute updates on database
$ node gen-updates.js
$ psql -d dbname -U user -a -f updates.sql
2 Likes
11

I also use sequel to transfer data from sqlite to postgres, but after transfer, the id column in postgres had int8 type, and when we query data by const data = await strapi.db.query('').findMany(), the id field in output had string type. It is not correct. Did anyone get this problem?

12

[UPDATE] Here is a video that walks you through how to migrate your database from Sqlite to PostgresQL https://youtu.be/ROvjBdI8-MM?feature=shared

You can also use DTS to make this update locally.

  1. use strapi export to export and back up your data.
  2. configure postgreql based on the docs in your local environment
  3. use strapi import to import your backup to your postgresql db instance.

I am in the process creating a guide that explains this in more detail.

13

Thanks for the tutorial @Paul_Brats , but there are some disadvantages in this method:

  • Admin users and API tokens are not exported.
  • If you have lifecycles.js file to listen to record changes, you should disable it before importing. In my case, I listen to new record and save the changes to another table.
  • The record ID will not maintain: If your table has 4 records: [{id: 1}, {id: 2}, {id: 4}, {id: 5}], the new table after importing will be: [{id: 1}, {id: 2}, {id: 3}, {id: 4}].
    So be aware before import/export using strapi import

In my case, I have to use sequel to transfer database, and then write some functions more to convert some field (eg: createdAt) before use.

1 Like
14

Thank you for sharing. Really good points and gotchas to consider.