How can I migrate my data from my rails app from mySql to SQLite

Question

How can I migrate my data from my rails app from mySql to SQLite?

Is this possible?

Answer 1

If you need to do it on the fly, via some sort of export method, you can just open a couple DB connections, one to your mysql db and one to your sqlite db, map the fields appropriately, and insert to your sqlite instance.

If you don't have to do it on the fly, there is this page.

Answer 2

If you just want to move your data and schema from one to the other, this shouldn't be too hard, although if you are using one of the dark corners of mySQL, things might get hairier. Basically, you should dump your mySql database to some sort of file (there are a ton of ways to do this, for example from the command line or from something like phpmyAdmin) and then, if memory serves (it's been a while since I last did this stuff), do something like this:

sqlite3 <database name> ".import <export file name>"

Additionally, there is a script to automate this on the this page of the sqlite website, so you might see if it actually works.

Answer 3

Great question!

In the past I had to migrate Gb of databases from SQlite to MySQL to PostgreSQl and viceversa. I tried tons of utilities, scripts and tools but the only way I found to successfully migrate the database was to export the data into a database-free schema, then import the data.

This plugin saved my life thousands of times: yaml_db.