Here’s a clean way to migrate an SQLite DB to MariaDB.

First, dump your database like so:

sqlite3 db.sqlite3 .dump                                        \
| sed 's/AUTOINCREMENT/AUTO_INCREMENT/g'                        \
| sed 's/INTEGER PRIMARY KEY/INT AUTO_INCREMENT PRIMARY KEY/g'  \
| sed '/^CREATE/s/"/`/g'                                        \
| sed '/^CREATE/s/DEFERRABLE INITIALLY DEFERRED//g'             \
| sed '/^PRAGMA/d'                                              \
| sed 's/BEGIN TRANSACTION/BEGIN; SET FOREIGN_KEY_CHECKS = 0/g' \
| sed 's/COMMIT/SET FOREIGN_KEY_CHECKS = 1; COMMIT/g'           \
> dump.sql

What happens here is that we

• rewrite AUTOINCREMENT to AUTO_INCREMENT,
• rewrite INTEGER PRIMARY KEY to INT AUTO_INCREMENT PRIMARY KEY,
• on lines that start with CREATE, replace " (quotes) with ``` (backticks),
• drop any lines that start with PRAGMA,
• drop any DEFERRABLE INITIALLY DEFERRED statements,
• rewrite BEGIN TRANSACTION to just BEGIN and disable foreign key checks during the import,
• re-enable foreign key checks just before the transaction is commited.

The output gets written to a file, that you can then just import using an equivalent of mariadb < dump.sql. There’s a catch though:

Foreign Keys

To cleanly import a file with foreign key constraints, the tables must be imported in an order such that all referenced tables are imported before one that declares a reference to them. SQlite just dumps tables in alphanumerical order, and I didn’t find a way to change that. There is a tool named lcdbdump that fixes most of the issues, but unfortunately it too doesn’t seem to fix referential integrity.

Django specialty: dumpdata

Since this is a Django project, I ended up running ./manage.py dumpdata to create a dump of my sqlite db, then switched the project to MariaDB, ran ./manage.py migrate to initialize the database and ran ./manage.py loaddata to load the data back in:

./manage.py dumpdata --indent=2 > project-data.json
# Change settings.py to point to MariaDB
./manage.py migrate
./manage.py flush
# Connect to MariaDB and run:
# delete from auth_permission;
# delete from django_content_type;
./manage.py loaddata project-data.json

That only works for Django projects though.