Sqlite to mysql migration

Discussions
1

After a couple days of research, I noticed that it’s a pretty common problem to start (intentionally of by accident) a new Strapi instance using SQLite and then make efforts to switch to MySQL.

I’ll describe here my journey on this subject:

I created a new fresh strapi instance and tried using the export - import method, but didn’t work

I also used sqlite3 .dump > data.sql to then create the MySQL data, but it also failed
I fixed many Syntax errors in the dumped file, but still failed due to datetime formats.

I also used sqlite3mysql with the better results, but still failed on the datetime fields .

I then used the following Python script

datefix.py

from datetime import datetime
def convert(value):
    return datetime.fromtimestamp(int(value)/1000).isoformat()

and used it in combination with :
cat datefix.py | sqlite-utils convert .data.db.backup {{table}} {{field}} -

in order to find all the datetime fields to apply the fix, I used the previously created tables in MySQL:

select distinct CONCAT("cat datefix.py | sqlite-utils convert .data.db.backup ", c.TABLE_NAME ," ", c.COLUMN_NAME, " -")     
from INFORMATION_SCHEMA.COLUMNS as c
where c.DATA_TYPE = 'datetime'
AND TABLE_SCHEMA='strapi'

This generated the scripts to run for fixing all the datetime formats.
Then I dropped the database and started again, using sqlite3mysql on the fixed sqlite database.

Now all the dates and tables look OK on mysql.

However, I still have the following problems:

1 - The created tables do not show in the admin panel, or through the API calls.

2 - When I restart the server, all the newly created tables are gone.

for the second point, I added the following to the config/database.js, but I still have the same problem

        ...
        forceMigration: false,
        runMigrations: false,

Is there a way to use move a running strapi from sqlite to mysql ?

Please help

2

Yes you can use