Custom Schema during Database Migration from SQLite to MySQL using Taps

Migrating data from one DBMS to another one can be tricky business.

Here’s a nice way to migrate data from an SQLite3 file to a MySQL database using taps. Well, the nice way is actually well documented here: Taps for Easy Database Transfers.

So this post is actually about working around a simple problem: SQLite doesn’t have a scope for decimal columns, but MySQL does. When taps rebuilds the database schema in MySQL it simply assumes those decimal columns had scope 0, which makes them integer columns. The data migration will thus result in rounded values in all those columns!

And here’s how you can workaround this problem for Ruby On Rails projects. Replace the all-uppercase placeholders with your info.

# Start the taps server on the original SQLite DB
taps server sqlite://DATABASE_FILE 'TAP_USER' 'TAP_PW'

# Create the database as per database.yml, assuming proper privileges
rake db:create

# Manually load the schema
rake db:schema:load

# Pull the data, but skip the schema and don't copy the schema_migrations table
taps pull -e schema_migrations -s 'mysql://USER:PW@localhost/DATABASE' http://TAP_USER:TAP_PW@localhost:5000

Be aware that taps uses the default encoding for it’s MySQL connections, so you might have to set something like this in your ~/.my.cnf


Hope this helps:-)