Generating a Rails project configured to use MySQL

Published on and tagged with mysql  ruby on rails

A little trap I blundered into each time I generated a Rails project with

$ rails example // Rails 2.x
$ rails new example // Rails 3.x

is that by default a generated project is configured to use a SQLite database. And so the configuration file for the databases looks as shown below (I omit the configurations for the “test” and “production” environments, as they are almost identical):

# example/config/database.yml
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

This leads, of course, to an error as soon as you try to access the database while there is no such driver installed ;-)

To avoid this small problem, you have to specify the database type when using the “rails” command:

$ rails -d mysql example // Rails 2.x
$ rails new example -d mysql // Rails 3.x

This command then generates the following database.yml:

# example/config/database.yml
  adapter: mysql  # Rails 3.x uses the mysql2 adapter
  encoding: utf8
  database: example_development
  pool: 5
  username: root
  socket: /var/run/mysqld/mysqld.sock

Happy Rails baking!

Update 2009-03-23: An alternative approach in Rails 2.x is to define the variable RAILS_DEFAULT_DATABASE with:


Update 2011-02-20: Adapted for Rails3

16 comments baked

  • Andreas

    When I started learning rails I used SQLite for the development. Works just fine. But there are certain pitfalls with SQLite on your development machine when you’re going to use MySQL in production:
    If you use boolean values they are handled differently in SQLite and MySQL (That’s not a big problem because rails handles that for you – unless you use some complex queries as i did :( ). And certain functions are different: For exmple, it’s: RAND() in MySQL but RANDOM() in SQLite.

    Just saying this because I spent a lot of time figuring out why my tests aren’t working as expected (development, production: MySQL, test: SQLite)

    Well, anyway: I like how easy it is to set up a new project using SQLite.

  • cakebaker

    @Andreas: Thanks for your comment!

    Yes, such little incompatibilities between the databases can be annoying. Hence I prefer to use the same database type in all environments, as it eliminates a possible source of problems.

    And yes, setting up a new project is quite smooth, even with MySQL (as soon as you learned about the “-d” option *g*).

  • rbn

    just wandering…
    does anybody know how do you config rails to make mysql and not sqlite the default adapter?

  • cakebaker

    @rbn: You can define the variable RAILS_DEFAULT_DATABASE and set it to mysql, see the hint I added to the article.

    Hope that helps!

  • rbn

    fantastic… :)) thank you very much for your kind and quick response!

  • cakebaker

    @rbn: You are welcome!

  • Rails with MySQL « Gagan's Blog

    […] I got it from:¬† […]

  • steve gray

    I wish everyone wrote clear, concise and direct posts like this!

  • cakebaker

    @steve: You are welcome!

  • Aaron

    I’m using Windows Vista with Rails 3 and MySQL2, and the command was a little different in my case.

    It is:

    “rails new appname -d mysql”

    Hope this helps, and thanks for the great post!

  • cakebaker

    @Aaron: Thanks for your comment!

    I adapted the article for Rails3.

  • sunswei

    Thank you very much for updating.

  • cakebaker

    @sunswei: You are welcome :)

  • Varunkumar

    i configured rails with mysql, but its not working.

    adapter: mysql2
    encoding: utf8
    reconnect: false
    database: test [am able to connect to this DB]
    pool: 5
    username: root
    password: root
    host: localhost
    Am getting the error as

  • cakebaker

    @Varunkumar: Hm, it’s difficult to say what the cause of this error is. Maybe the accepted answer to Trouble getting Rails 3 to work with MySQL is helpful for you?

  • raneves


© daniel hofstetter. Licensed under a Creative Commons License