Have you ever wanted to convert mysql database to sqlite? Or postgres to mysql? Or mysql to postgres?

Recently I've been migrating my django project from MySQL to SQLite. I tried to use dumpdata and loaddata commands.

• dumpdata can save your database to json file
• loaddata can populate your database with entities from the json file

But it is not so simple as it sounds:

• You must migrate your SQLite database first, because json file will not contain any schema altering instructions
• You must remove any conflicting content from contrib.auth application. For example, permissions or initialized superusers
• Even if you did all these steps you can have problems with indexes or duplicates

I decided to move on and try to convert my databases directly (without the django facilities). I checked stackoverflow and found many scripts for converting your MySQL dump to SQLite dump. For example mysql2sqlite.sh (don't use it!). But it failed too.

When I was already despaired I found an awesome project called sequel. It's ORM written in Ruby. Sequel has a command line interface and an option for copying one database to another even between different database types. Using -C option, you provide two connection strings:

sequel -C mysql://host1/database postgres://host2/database2

This copies the table structure, table data, indexes, and foreign keys from the MySQL database to the PostgreSQL database.

As I understand sequel performs 4 major steps for copying databases:

• It connects to the source database and extracts the metadata (table structure, table data, indexes, foreign keys)
• Then it builds an ORM models based on the extracted metadata
• Then it migrates a target database based on the ORM models
• Finally it populates the target database with the data from the source database

Sequel is awesome but for using it you have to install Ruby, gem package manager, sequel itself and all drivers you need for connecting to your databases. I found it inconvenient and have built a docker image called chibisov/sequel. It packed with sequel + drivers for MySQL, Postgres and SQLite.

With just one line of code I can migrate MySQL database to SQLite file:

$ docker run --name mysql2sqlite chibisov/sequel:4.31.0 sequel -C mysql2://user:password@host/database sqlite://db.sqlite

Be careful with the mysql connection string. You should use mysql2:// adapter because the default mysql:// adapter has problems with utf-8 encoding.

That's it! Now I can copy the database file to my host machine and remove the intermediate container:

$ docker cp mysql2sqlite:/db.sqlite ./
$ docker rm -v mysql2sqlite

You can watch how I convert databases in my terminal session: