How to convert databases with one line of code
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.
dumpdatacan save your database to json file
loaddatacan 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.authapplication. 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:[email protected]/database sqlite://db.sqlite
Be careful with the mysql connection string. You should use
mysql2:// adapter because the default
mysql:// adapter has problems with
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:
Subscribe to blog.chib.me
Get the latest posts delivered right to your inbox