Convert a Ruby on Rails app from sqlite to MySQL?

Question

I made an app in Ruby on Rails and now I want to get it hosted. However, they require that I use MySQL and I set it up using sqLite3. Is there any way to convert it to use MySQL?

Do you need to migrate data from sqlite? Or do you just want to start using MySQL on a fresh database from here on out? — bensie, Nov 03 '09 at 21:45
The Rails 6+ way: https://stackoverflow.com/a/72572767/1136887 — James Hibbard, Jun 10 '22 at 10:28

score 119 · Accepted Answer · edited Dec 12 '22 at 22:43

119

Step 0

To be safe, I recommend experimenting a bit with this technique in a virtual machine. Save yourself a bunch of heartache and build a virtual machine, check out your code, and have a safe playground that you can throw away if tragedy strikes.

Step 1

Make a backup copy of your database.yml file.

(from your application root)

cp config/database.yml config.database.yml.sqlite3

Step 2

Make a backup copy of your data

For Rails 3, install the YAML DB gem: https://github.com/ludicast/yaml_db by running

gem install yaml_db

and then add to your Gemfile.

gem 'yaml_db'

For Rails 2.x install the YAML DB plugin:

script/plugin install git://github.com/adamwiggins/yaml_db.git

Run the dump task

rake db:dump

Step 3

Update your config/database.yml file. You will find entries like

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000
test:
  adapter: sqlite3
  database: db/test.sqlite3
  timeout: 5000
production:
  adapter: sqlite3
  database: db/production.sqlite3
  timeout: 5000

Change them to

development:
  adapter: mysql
  encoding: utf8
  reconnect: false
  database: **myapp_development**
  pool: 5
  username: **root**
  password: **supersecretpassword**
  **socket: /opt/local/var/run/mysql5/mysqld.sock**
test:
  adapter: mysql
  encoding: utf8
  reconnect: false
  database: **myapp_test**
  pool: 5
  username: **root**
  password: **supersecretpassword**
  socket: **/opt/local/var/run/mysql5/mysqld.sock**

production:
  adapter: mysql
  encoding: utf8
  reconnect: false
  database: **myapp_production**
  pool: 5
  username: **root**
  password: **supersecretpassword**
  socket: **/opt/local/var/run/mysql5/mysqld.sock**

Be sure to update the values surrounded by asterix as appropriate for your platform! The socket value is only good for Mac OSX using MacPorts. Most flavors of linux do not require this value.

Step 5

If you have some errors in the following step, you might have to install the mysql or mysql2 gem:

sudo gem install mysql

or

sudo gem install mysql2

Have rake create your database

rake db:create
rake db:schema:load

Step 6

Use YamlDb to reload your data into MySql

rake db:load

edited Dec 12 '22 at 22:43

thd

2,380
1
25
33

answered Nov 04 '09 at 02:19

marshally

3,541
2
23
26

5

You really should use db:schema:load instead of db:migrate for creating new databases. – ScottJ Nov 04 '09 at 19:40
4

actually, why do you need to use Virtual Machine? I thought you can commit your whole Rails project folder to Git or Mercurial or SVN, or even copy the whole folder to a different one, and all data including the sqlite DB data is there, able to be used as a Rails app again. – nonopolarity Sep 25 '10 at 05:08
I followed this and the process seemed to go smoothly but now I'm having a mysql2 syntax error. I've posted it here. http://stackoverflow.com/questions/9406016/mysql2error-you-have-an-error-in-your-sql-syntax – Marc Feb 23 '12 at 02:25
1

For those who didn't know like me: in rails 3.x, you can install the gem by adding "gem 'yaml_db'" to Gemfile and then do "bundle install" – didi_X8 Feb 19 '13 at 18:08
@動靜能量 - You generally aren't going to be committing your database to version control. And if you are - you would only have a development one in there anyways ( I would hope! ) After you run something in prod - Prod will have it's own database that wouldn't be in version control anyhow as you should be deploying it through some packaging system or a deploy key and it won't write back to Git. – TJ Biddle May 31 '13 at 18:44
I had (am) suffering a lot with different dev environments to production. I have SQlite for dev and MySQL for production and am learning the hard way the using find_by_sql with boolean values has unpredictable results – SG84 Feb 20 '14 at 14:52
update: http://stackoverflow.com/a/5411673/1847899 - https://rubygems.org/gems/mysql2 – Alexander Sidikov Pfeif May 22 '14 at 09:52
2

Stop using SQLite for development... http://streaming.nfm.id.au/stop-using-sqlite-for-rails-development-ndash/ – unom Sep 04 '14 at 11:39
1

Can you update this post please? Step2: for rails 3: command line is: gem install yaml_db, then add: gem 'yaml_db' in your Gemfile, then the rest of your comments – Michel Nov 30 '14 at 11:41

score 7 · Answer 2 · answered Nov 03 '09 at 21:12

7

As long as you have not written any SQL statements that run in sqlLite3 and not MySQL (which you won't have if all your database access is via ActiveRecord and ActiveRecord migrations) then all you need to do is change the database adapter in your database.yml config file.

answered Nov 03 '09 at 21:12

DanSingerman

36,066
13
81
92

2

Yes, you'll need to run rake db:migrate in the new environment (but that will be true of any new environment whether it is a different database engine or not.) – DanSingerman Nov 03 '09 at 21:47

score 6 · Answer 3 · edited Mar 07 '14 at 03:40

6

Check Taps. I've successfully converted a Mysql database to Postgres with it --it should support SQLite.

Edit: Including working link from cony's comment here.

edited Mar 07 '14 at 03:40

imbric

114
1
12

answered Nov 03 '09 at 21:59

pantulis

1,695
12
15

I had to do this recently, Taps worked like a charm, check out this blog entry for examples: http://adamblog.heroku.com/past/2009/2/11/taps_for_easy_database_transfers/ – Jack Chu Apr 24 '10 at 05:54
taps is an awesome utility, definitely worth to check it out – knoopx Jun 04 '10 at 09:52
link broken, http://adam.heroku.com/past/2009/2/11/taps_for_easy_database_transfers/ seems to work – conny Jul 30 '10 at 09:44

score 5 · Answer 4 · edited Dec 03 '12 at 18:45

5

myproject  user$ cd
user   $ rails new myproject -d mysql

Say 'no' for all question but for Overwrite .../myproject/config/*database.yml*? (enter "h" for help) [Ynaqdh] say 'yes'.

edited Dec 03 '12 at 18:45

McGarnagle

101,349
31
229
260

answered Dec 03 '12 at 18:24

Mikaele

85
1
4

Don't forget the gemfile – kwerle Oct 06 '20 at 22:36

score 5 · Answer 5 · answered Nov 03 '09 at 23:46

5

If there's no data to migrate, simply update database.yml and run 'rake db:schema:load' in the new environment. (NOT db:migrate which should only be used for incremental migrations!)

answered Nov 03 '09 at 23:46

ScottJ

1,080
12
20