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?
2
-
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– bensie2009-11-03 21:45:07 +00:00Commented Nov 3, 2009 at 21:45
-
The Rails 6+ way: stackoverflow.com/a/72572767/1136887James Hibbard– James Hibbard2022-06-10 10:28:05 +00:00Commented Jun 10, 2022 at 10:28
Add a comment
|
6 Answers
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
9 Comments
ScottJ
You really should use db:schema:load instead of db:migrate for creating new databases.
nonopolarity
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.
didi_X8
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"
unom
Stop using SQLite for development... streaming.nfm.id.au/…
Michel
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
|
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.
1 Comment
DanSingerman
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.)
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.
3 Comments
Jack Chu
I had to do this recently, Taps worked like a charm, check out this blog entry for examples: adamblog.heroku.com/past/2009/2/11/…
knoopx
taps is an awesome utility, definitely worth to check it out
conny
link broken, adam.heroku.com/past/2009/2/11/taps_for_easy_database_transfers seems to work
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!)
Comments
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'.
1 Comment
kwerle
Don't forget the gemfile
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
