CODEWITHSUNDEEP
ruby-on-rails
Austin Richardson
Austin Richardson

Reputation: 8427

Convert a Ruby on Rails app from sqlite to MySQL?

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?

Upvotes: 59

Views: 46498

Answers (6)

Shiva
Shiva

Reputation: 12514

Good news! From Rails 6, they've added a command to just do this. Its very convenient.

$ rails db:system:change -h        
Usage:
  bin/rails db:system:change

Options:
  [--to=TO]  # The database system to switch to.

Change `config/database.yml` and your database gem to the target database

Example

$ rails db:system:change --to=mysql

Upvotes: 1

marshally
marshally

Reputation: 3567

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

Upvotes: 119

pantulis
pantulis

Reputation: 1705

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.

Upvotes: 6

Mikaele
Mikaele

Reputation: 85

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'.

Upvotes: 5

ScottJ
ScottJ

Reputation: 1092

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!)

Upvotes: 5

DanSingerman
DanSingerman

Reputation: 36502

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.

Upvotes: 7

Related Questions