Working with Rails – Setting up and connecting to a database

Elliot Smith

November 2007

In this article, authors Elliot Smith and Rob Nichols explain the setup of a new Rails application and how to integrate it with other data sources. Specifically, this article focuses on turning the abstract data structure for Intranet into a Rails application. This requires a variety of concepts and tools, namely:

  • The structure of a Rails application.
  • Initializing an application using the rails command.
  • Associating Rails with a database.
  • The built-in utility scripts included with each application.
  • Using migrations to maintain a database.
  • Building models and validating them.
  • Using the Rails console to manually test models.
  • Automated testing of models using Test::Unit.
  • Hosting a project in a Subversion repository.
  • Importing data into the application using scripts.

In this article, we'll focus on the first 3 concepts.

The World According to Rails

To understand how Rails applications work, it helps to get under its skin: find out what motivated its development, and the philosophy behind it.

The first thing to grasp is that Rails is often referred to as opinionated software (see It encapsulates an approach to web application development centered on good practice, emphasizing automation of common tasks and minimization of effort. Rails helps developers make good choices, and even removes the need to make choices where they are just distractions.

How is this possible? It boils down to a couple of things:

  1. Use of a default design for applications-
    By making it easy to build applications using the Model-View-Controller (MVC) architecture, Rails encourages separation of an application's database layer, its control logic, and the user interface. Rails' implementation of the MVC pattern is the key to understanding the framework as a whole.
  2. Use of conventions instead of explicit configuration-
    By encouraging use of a standard directory layout and file naming conventions, Rails reduces the need to configure relationships between the elements of the MVC pattern. Code generators are used to great effect in Rails, making it easy to follow the conventions.

We'll see each of these features in more detail in the next two sections.

Model-View-Controller Architecture

The original aim of the MVC pattern was to provide architecture to bridge the gap between human and computer models of data. Over time, MVC has evolved into an architecture which decouples components of an application, so that one component (e.g. the control logic) can be changed with minimal impact on the other components (e.g. the interface).

Explaining MVC makes more sense in the context of "traditional" web applications. When using languages such as PHP or ASP, it is tempting to mix application logic with database-access code and HTML generation. (Ruby, itself, can also be used in this way to write CGI scripts.) To highlight how a traditional web application works, here's a pseudo-code example:

    # define a file to save email addresses into
    email_addresses_file = 'emails.txt'
    # get the email_address variable from the querystring
    email_address = querystring['email_address']
    # CONTROLLER: switch action of the script based on whether
    # email address has been supplied
    if '' == email_address
        # VIEW: generate HTML form to accept user input which
        # posts back to this script
        content = "<form method='post' action='" + self + "'>
        <p>Email address: <input type='text' name='email_address'/></p>
        <p><input type='submit' value='Save'/></p>
        # VIEW: generate HTML to confirm data submission
        content = "<p>Your email address is " + email_address + "</p>"
        # MODEL: persist data
        if not file_exists(email_addresses_file)
        end if
        write_to_file(email_addresses_file, email_address)
    end if
    print "<html><head><title>Email manager</title></head>
    <body>" + content + "</body></html>"

The highlighted comments indicate how the code can be mapped to elements of the MVC architecture:

  • Model components handle an application's state. Typically, the model does this by putting data into some kind of a long-term storage (e.g. database, filesystem). Models also encapsulate business logic, such as data validation rules. Rails uses ActiveRecord as its model layer, enabling data handling in a variety of relational database back-ends.
    In the example script, the model role is performed by the section of code which saves the email address into a text file.

  • View components generate the user interface (e.g. HTML, XML). Rails uses ActionView (part of the ActionPack library) to manage generation of views.
    The example script has sections of code to create an appropriate view, generating either an HTML form for the user to enter their email address, or a confirmation message acknowledging their input.

  • The Controller orchestrates between the user and the model, retrieving data from the user's request and manipulating the model in response (e.g. creating objects, populating them with data, saving them to a database). In the case of Rails, ActionController (another part of the ActionPack library) is used to implement controllers. These controllers handle all requests from the user, talk to the model, and generate appropriate views.
    In the example script, the code which retrieves the submitted email address, is performing the controller role. A conditional statement is used to generate an appropriate response, dependent on whether an email address was supplied or not.

In a traditional web application, the three broad classes of behavior described above are frequently mixed together. In a Rails application, these behaviors are separated out, so that a single layer of the application (the model, view, or controller) can be altered with minimal impact on the other layers. This gives a Rails application the right mix of modularity, fl exibility, and power.

Next, we'll see another piece of what makes Rails so powerful: the idea of using conventions to create associations between models, views, and controllers. Once you can see how this works, the Rails implementation of MVC makes more sense: we'll return to that topic in the section Rails and MVC.

Convention over Configuration

In the previous section, we met the MVC framework, used to define the general design of every Rails application. The MVC framework naturally breaks an application into three groups of components (models, views, and controllers). In the "olden" days, a web application framework would typically define relationships between these components using a configuration file (e.g. an XML file in the Struts framework). Writing this configuration file was often a laborious and error-prone task, and could take the same amount of time as writing the application code itself.

The Rails developers recognized that, most of the time, the relationships between the parts of an MVC application are obvious, repetitive, and shouldn't require configuration. There tends to be a common set of actions associated with each controller ("show a list of model instances"; "show a single model instance"; "create, update, or delete a model instance"); and developers will tend to give them similar names (list, show, delete, update, create). This realization prompted the Rails developers to create a set of conventions around how common application components are implemented: standards for class names and locations, controllers and actions, fi le names, and directory structure.

Rail uses these conventions to minimize the need for configuration, automatically generating much of it when the application is bootstrapped. As well as simplifying configuration, the conventions also remove the need for a developer to make certain decisions. In classical web applications, a developer would often have to decide where to put files and directories, and then have to define relationships between application elements (e.g. which views are used by which controller). By contrast, every Rails application has a familiar directory structure, automatically generated by tools; each file added to the project usually adheres to a naming standard; classes follow a similar naming convention; and there are conventions for naming and locating supporting files, like Javascripts and images. By making choices for the developer, Rails can save time (and sometimes arguments), leading to its much touted productivity gains.

If you need to, you can step outside the Rails conventions. Mostly, though, there is no need to, and you can greatly reduce development time by embracing the conventions instead.

Rails and MVC

At its core, the architecture of Rails is standard MVC; however, unlike older forms of MVC for the web, Rails minimizes the effort needed to maintain the MVC pattern. This is because the conventions inherent in Rails, as described in the previous section, reduce the need for configuration. The diagram, below, gives a graphical representation of how Rails implements MVC, and also summarizes how conventions are used to define the workflow of an application. Our fledgling Intranet application is used as an example; specifically, the page which displays a list of people:

Working with Rails – Setting up and connecting to a database

Fleshing out the steps in the diagram, here's what happens when a client requests a list of people:

  1. The client asks for the URL:/people/list.
  2. Rails' routing code parses the URL into a request for a particular controller, and a particular method on that controller. In this case, Rails uses a typical route to fragment the path into a controller name (people) and the name of a method on that controller (list). In this case, the following sequence is executed:

    1. An instance of the PeopleController class is created. Rails knows to generate an instance of this class, as it uses the first part of the path (= people), capitalised (= People), and with the string 'Controller' appended (= PeopleController), to determine the correct controller class to use. This returns a PeopleController object (which will be referred to as "the controller" from now on). This is followed by:
    2. A call to the list method (a.k.a. an action) of the controller. Again, the path is used to determine, which method to call: in this case, the second fragment of the path is list; hence Rails calls the list method.
    The routing facilities in Rails are covered in more detail in the section on routing in Chapter 7 of the book
  3. The list action of the controller uses the find method of the Person model class to query the database. Each model class provides a find method, which enables querying of the table associated with the model.
  4. The Person class is, by convention, associated with a table called people. The Person class, therefore, generates the SQL required to retrieve a set of records from the people table in the back-end database.
    Note how the table name is the pluralized, lowercase equivalent of the model class name: Person associates with people; Address associates with addresses; Company associates with companies; etc. These relationships don't have to be specified: they are configured solely through consistent names. (It is also possible to turn pluralization off, if you want to buck these conventions.)
  5. The set of records is converted into an array of Person instances and returned to the controller.
  6. The controller uses a view template to create the output for the client. This output will typically be based on an HTML outline, filled out with data from the model. In our example, the template might produce an HTML table from the array of Person instances, one row per person, showing the name of the person and a link to their full details.
    The name of the template to render for a given action is derived by convention (again), and is based on the name of the action: in this case, the action is called list, so Rails uses a template called list.rhtml. If there is no appropriate .rhtml file, Rails will look for a .rxml (Builder XML template) fileinstead.
  7. The controller returns the generated HTML to the client.

As you can see, the Rails conventions enable some powerful connections between aspects of the model, view, and controller components, with no need for configuration.

The Rails "power tools" are the keys to leveraging its conventions, namely:

  • The rails command. This creates the "skeleton" for an application, including the directory structure, public fi les (like error pages and Javascripts), stubs for automated testing, plus several utility scripts. The created directories and fi les follow the conventions described previously.
  • The Rails generators. These are included with the utility scripts, and added to very new Rails application created using the rails command (see above). They are used to add new components to the application, such as new models or controllers, again following the naming conventions.

We'll see how to use these tools in the following sections, as we start building the Intranet application.

Setting Up a New Rails Application

Every Rails application looks basically the same. Each has the same directories, and even files with the same names. The reason for this is that Rails provides a command (rails) for creating a stock set of directories and stub files as a starting point for any new application, to be fleshed out as development progresses. These files and directories are arranged in such a way that the different parts of Rails (in fact, it is a framework of frameworks) can work together effectively. When starting a new application, running the rails command is the first step.

If you you already have an empty Intranet directory inside your Eclipse workspace, "connected" to the code repository, then rather than create a new directory for your application, you can use this existing one for your application as shown below:

  $ cd workspace
  $ rails Intranet

(Note that you may need to replace workspace with the directory you are using for your Eclipse workspace.)

More generally, use this command from a prompt to start a new Rails application in situations where you haven't created a project directory yet:

  $ rails /path/to/application

When you run the rails command, you should see something like this

  $ rails Intranet
  create app/controllers
  create app/helpers
  create app/models
  create app/views/layouts
  create config/environments
  create components
  create log/server.log
  create log/production.log
  create log/development.log
  create log/test.log

(This has been truncated for brevity: the rails command generates a lot of stuff.) The absolute path to the top-level directory containing the application is referred to inside Rails as the RAILS_ROOT directory. In our case (as we're developing with Rory), this directory is:


Rather than going into too much detail, below is a summary of what is contained in a fresh RAILS_ROOT directory. Directories which are particularly important are marked with an asterisk, as we'll be spending most of our time inside them:

  1. *app contains the core MVC classes of the application (see the earlier section Rails and MVC), separated into four subdirectories:

    1. controllers contains controller definitions, which handle the control flow of the application.
    2. *models hold model definitions, which act as the layer between the controllers and the database.
    3. views contains templates for generating interface elements, such as XHTML or XML output.
    4. helpers are companions to views. They are intended to move heavy lifting out of view templates and into "helper" methods, which (generally) generate HTML elements.
  2. Components is largely deprecated, and remains for historical reasons. Originally, components were intended to encapsulate reusable chunks of code, which could be shared across applications. They were superseded by plugins (see the section on plugins in Chapter 8 of the book You can safely delete this directory.
  3. *config holds the application's configuration. Most of the time, you only need to worry about setting up the connection to the database (which we'll do in the section Setting Up a Database Connection, later in this article).
  4. *db holds all database-related files and scripts. This includes dumps of the database and schema definitions. We'll be working in this directory throughout the article.
  5. doc holds an auto-generated API documentation for your application.
  6. lib holds library files, which are not necessarily Rails-specific, e.g. generic Ruby libraries. Any Ruby file you drop in here (anything ending in *.rb) is automatically loaded during your application's startup. It is most often used as the location for new Rake task definitions, which are added to the tasks sub-directory (see the bullet point below on the Rakefile).
  7. log holds log files for your application.
  8. public contains static files, such as images and Javascripts.
  9. *script contains helper scripts for your application, such as the interactive console and code generators.
  10. test contains test cases for your code. Each time you auto-generate a new model or controller, tests are added here.
  11. tmp contains temporary files, namely cached output, session data, and server sockets.
  12. vendor houses the plugins directory.
  13. The README file in the root of your application directory contains generic information for people who may be installing your application. The README_FOR_APP file in the doc directory is probably a better place to add your information, as this becomes part of any auto-generated documentation.
  14. Rake is an important tool in your arsenal, enabling you to automate repetitive development tasks. The Rakefile defines the set of tasks available in your project. By default, these include things like running your test suite, maintaining your database, and clearing temporary data. You can add your own Rake tasks under lib/tasks.

If you switch to Eclipse to browse your new Rails application, you might wonder where the files have gone. This is because Eclipse doesn't monitor changes to the filesystem. When we added our application files outside Eclipse (by running the rails command), Eclipse didn't know anything about it.

The solution is to refresh Eclipse's view of the filesystem. Right-click on the name of the project and select the Refresh option from the context menu. You should now be able to see your files.

Now we have a skeletal application, we are finally ready for some magic.

Using Mongrel to Serve Your Application

I expect you're dying to see your fi rst Rails application up and running? It turns out this is no work at all. Connect into your application's directory using the command line, and run the following command:

  $ ruby script/server

Or, on Windows:

  $ ruby scriptserver

We'll be using the Linux-style syntax throughout the book when running the scripts, with forward slashes as path delimiters.

This command runs one of the Rails built-in scripts, located in the script directory, which starts a Mongrel server whose single purpose is running your Rails application.

If the server starts correctly, you should see:

  => Booting Mongrel (use 'script/server webrick' to force WEBrick)
  => Rails application starting on
  => Call with -d to detach
  => Ctrl-C to shutdown server
  ** Starting Mongrel listening at
  ** Starting Rails with development environment...
  ** Rails loaded.
  ** Loading any Rails specific GemPlugins
  ** Signals ready. TERM => stop. USR2 => restart. INT => stop (no
  ** Rails signals registered. HUP => reload (without restart). It might
  not work well.
  ** Mongrel available at
  ** Use CTRL-C to stop.

Note that the application is served on port 3000 by default and is accessible at the URL http://localhost:3000/. If you open this URL in a browser, you should see something like this:

Working with Rails – Setting up and connecting to a database

Click on the About your application's environment link to see some information about the versions of Ruby and Rails you are running with. On Linux, you can dispense with the ruby command and just use this command to run the server:

  $ script/server

The only requirement for this to work is that the script should be able to find your Ruby binary. This is defined in the shebang line (the first line of the file). Usually, the default line works correctly:

  #!/usr/bin/env ruby

However, if your Ruby installation isn't in your PATH environment variable, you may have to modify the ruby part of this line to point at its location. For example, if your Ruby installation is in /opt/ruby1.8.4 you would use the shebang:

  #!/usr/bin/env /opt/ruby1.8.4/bin/ruby

Our application isn't too exciting so far, we can't interact with it very much, and it has little data to serve. In the next sections, we're going to build it up, taking a data-centric approach.

As of Rails 1.2, if Mongrel is installed on your machine, it is used as the default web server for your application. However, if you don't have Mongrel installed, Mongrel isn't on your path, or you are using an lder version of Rails, WEBrick is used instead. The only difference is that WEBrick is significantly slower, and not suitable for running Rails applications in a production environment. Other than that, you can happily develop an application using either.

Connecting Rails to a Database

Throughout decades of client-server computing, the architecture of database driven applications has been designed by database administrators working in the "enterprise". This architecture places the responsibility for managing data squarely in the database itself. Applications are considered "dumb", not to be trusted with vital tasks like data validation and formatting, or managing relationships between different tables. Using this traditional architecture, we might manage our Intranet application's data structures like this:

  • Stored procedures could be used to manage complex operations on the database. For example, inserting a new person, company, and associated addresses might be done using a single call to a stored procedure. This would insert new records into the appropriate tables, manage foreign key relationships between the new records, and perform validation of data.    
  • Cascade operations could be used to manage dependencies between records in related tables. For example, if the id field of a record in the companies table changed, any references to that in the people table would be updated.    
  • Views could be used to retrieve, sort, and format data from one or more tables using a single query. In our case, a view might be used to retrieve the names, phone numbers, and website addresses from the companies table, ordered by company name.

By contrast, in the Rails view of the world, the application is king, and the database is its servant. This is due to Rails being the product of web developers: in the web development environment, databases are typically unsophisticated buckets for holding "stuff" (because they are often designed by people who aren't full-time database administrators).

However, beyond this, performing database operations through model classes simplifies database access considerably: making a change to an application can be done in the high-level application code, rather than by modifying low-level SQL code in the database layer. Therefore, operations, which would be delegated to the database in the traditional client-server architecture (such as the ones in the bullet points above) are instead handled by ActiveRecord in a Rails application.

ActiveRecord is a so-called object-relational mapping (ORM) layer (also known as a relational persistence layer), similar in scope to Hibernate (Java) or ADODB (PHP). Instead of dealing with the database through SQL statements, an ORM layer presents records in a database table as a series of objects; it also provides class-level methods for creating database connections, querying tables, and so on. The fi elds in a record are not read or written directly, but accessed through instance methods on an object. This reduces the complexity and tedium of working with a relational database, as the onus for creating database connections, correctly formatting SQL statements, escaping quote marks in strings, etc. is moved into the ORM layer, away from the developer.

Bruce Tate and Curt Hibbs call ActiveRecord an Object Relational Wrapping layer, rather than a Mapping layer: their view is that as there is no explicit configuration of the relationship between the model classes and the database tables, it doesn't count as a "mapping". They have a point, but the exact semantics aren't too important. Here, I'm treating "mapping" in the sense that objects can be mapped onto records in the database tables.

An ORM layer is one way to implement the model part of the MVC architecture, and this is the role ActiveRecord fulfils in a Rails application: it acts as the intermediary between the controller and the database, retrieving, validating, updating, and deleting records on behalf of the controller.

Before we can see how this works, we need a database to use as the back-end for our application. Setting this up is covered in the following sections.

Creating a Database and System Account

We're going to use MySQL as the database back-end for the Intranet application. If you followed the installation instructions, you should have MySQL available on your machine.

These instructions cover how to setup a database and user in MySQL. However, if you have an alternative database back-end (Oracle, SQL Server, PostgreSQL, etc.), create a database and a user with whichever tools that are appropriate for your database instead.

The first step is to initialize the MySQL database. You could use one of the MySQL GUI clients to do this, but we'll use the command line as this works the same regardless of your platform. Use this command to login to the database server on your local machine:

  $ mysql -u root -p

Note that -u root specifies we are logging in as the root user: you need to use an account with sufficient privileges to create a new database. -p indicates that we intend to use a password to login. If your login is successful, you should see:

  Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.24a
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Once you've logged in successfully, create a database called intranet_development with:

  mysql> CREATE DATABASE intranet_development;
Query OK, 1 row affected (0.05sec)

This is the database we'll be using during the development of our application. The next step is to create a user account for our Rails application. T his is a good practice: instead of using a real person's account (or even worse, the root account) to connect our Rails application to MySQL, we will use a dedicated system account. This gives us a handful of security advantages:

  • The system account can be given minimal permissions: just the ones the application needs to work with the database. If our application never needs to create temporary tables, we don't need to give the system account the CREATE TEMPORARY TABLES permission, for example. MySQL allows us to refine permissions further, down to the individual table and row level if required.    
  • We can restrict the system account's access to the Rails application's database. If the account's were compromised, the cracker's vandalism would be limited to just that database.    
  • We can restrict the load that the system account can put on the database service, such as the number of queries, updates, and connections the system account can make per hour. If the Rails application were hit by a denial of service attack, these settings could help limit the damage that the application can cause.    
  • The system account can be limited to accessing the database from a single IP domain name, IP address, or IP range. Any attempt to access the database from a machine outside the range automatically fails, further tightening our setup.

Again, you can use either a GUI tool or the command line to create the system account. Here's how to do it on the command line (once you are logged in to MySQL):

  mysql> GRANT ALL ON intranet_development.* TO intranet@localhost
  IDENTIFIED BY 'police73';
  Query OK, 0 rows affected (0.01 sec)
  Query OK, 0 rows affected (0.00 sec)

This simultaneously creates the intranet user account and gives that user all permissions (known as privileges in MySQL) on the intranet database. The IDENTIFIED BY clause specifies the account password ('police73'). @localhost specifies the host name from which this user can access the database. If the database server is on a separate physical box from the application server, the application server's IP address or domain name should be used here. FLUSH PRIVILEGES applies the permissions to the server.

A note on permissions

In the example above, the system account is given all permissions on the database. You may not want to give your application this level of access to the database, once it is in production. MySQL makes it trivial to restrict permissions on individual tables or columns within tables and/or by SQL operation (e.g. the production system account could just be given SELECT permissions if the application just reads from the database tables).


Setting Up a Database Connection

The database connection settings for a Rails application are stored in a single  file called database.yml, inside the config directory (which is in turn inside the RAILS_ROOT). The freshly-minted database.yml file (produced by the rails command) can seem a bit confusing, mainly because it contains a lot of examples and hints. However, after removing comments, it boils down to this:

        adapter: mysql
        database: Intranet_development
        username: root
        host: localhost
        adapter: mysql
        database: Intranet_test
        username: root
        host: localhost
        adapter: mysql
        database: Intranet_production
        username: root
        host: localhost

This is a YAML (rhymes with "camel") file, which configures the database settings for each of the three Rails' environments: development, test, and production—which is explained in the next section).

YAML, ( "YAML ain't a Markup Language"), is officially described as a "data serialization language" (see, Its purpose is comparable to that of XML: both YAML and XML are used to define configuration settings, log file formats, or other kinds of structured data. However, YAML has the following benefits over XML:

  • Concise
  • Easy to parse
  • Easy to implement
  • Human-readable

The YAML syntax used in the database.yml file defines a YAML mapping from keys to values. (If you've ever worked with a configuration file, the key-value concept should be familiar.) When the file is read by Rails, it is converted into a Ruby hash (c.f. a dictionary or associative array in other languages). The words which are flush with the left of the file ('development', 'test', 'production') are the keys of this top-level hash; in Ruby syntax, the hash looks like this:

        'development' => {...},
        'test' => {...},
        'production' => {...}

Underneath each of these top-level keys is a series of name-value pairs; each series is also converted into a hash. The parent key has this hash as a value. In Ruby syntax, the resulting "hash of hashes" looks like this:

        'development' => {
            'adapter' => 'mysql',
            'database' => 'Intranet_development',
            'username' => 'root',
            'password' => nil,
            'host' => 'localhost'
        'test' => {...},
        'production' => {...}

(The 'test' and 'production' sections are truncated for brevity.)

Each sub-hash specifies a database configuration for one of the Rails environments,as described in the next section.

Configuring the Rails Environments

Rails can run an application in different modes; each mode is known as an environment. Each environment is kept isolated from the others, uses a different database, and handles logging and error checking differently. This makes it possible to develop your code without breaking the live application, run tests without wiping records from the live database, and run your application more efficiently in production (amongst other things).

The three environments have different purposes and characteristics:

  1. Development
    This is the environment you use when building your application. Logging and error reporting are at their most verbose, making it simpler to track down bugs. This environment uses its own database which is kept isolated from the production database.    
  2. Test
    This environment is only normally used when running automated tests. The database for this environment is regenerated from scratch each time tests are run, by cloning the structure (not the data) of the development environment.    
  3. Production
    Rails does a lot of work behind the scenes creating classes and their methods on the fly. In the production environment, this work is done once when the application starts and cached to save work later (by contrast, in development, it is done on each request). Debugging and logging are also set to a minimum in this environment.

You can configure database settings by editing the section of the database.yml file named after the environment. For now, we'll just edit the settings for the development environment:

        adapter: mysql
        database: 'intranet_development'
        username: intranet
        password: police73
        socket: /var/run/mysqld/mysqld.sock

You must use spaces, not tabs, in a YAML file: attempting to use tabs in a YAML file will break it completely. For reference, so that you can recognize it in case it happens to you, here's a YAML error caused by an errant tab:

/usr/lib/ruby/1.8/yaml.rb:133:in 'load': syntax error on
line 13, col 11:
' adapter: mysql' (ArgumentError)

There is no need to configure the test and production databases at this point: we can leave the default settings as they are until we need these environments.

Each line of the configuration is described in more detail in the table below:

Configuration option

Example setting




The type of database to which the application is connected.



The name of the database to use in this environment. The Rails convention is to use the suffix _development for the development database, and _test for the test database. (Note: quote marks are used around the database name as it contains an underscore: MySQL is sometimes a bit funny about underscores...)



The username for the system account.



The password for the system account.



If the application needs to connect to the database server over a network, this is the hostname or IP address of the database server. Where the Rails application and the database server are running on the same machine, localhost can be used.


/var/run/mysqld/mysqd. sock

This is an interesting setting, and one which is often overlooked. It is mainly used on *nix machines, where the database connection is made using a Unix-style socket, rather than a network (TCP) connection. In some cases, it may not be possible to create a network connection at all (TCP can be switched off on MySQL on *nix); in this case, specifying a socket location is the only option. The example setting shown here points at the default socket location on Ubuntu. See the section Troubleshooting a MySQL Connection for more details about sockets


Although we are using MySQL as our database server, ActiveRecord supports a range of other back-ends. You can specify which by changing the adapter configuration option to one of these values:

  • db2
  • firebird
  • frontbase
  • oci (Oracle)
  • openbase
  • postgresql
  • sqlite
  • sqlserver
  • sybase

Each adapter has its own set of configuration options: see the Rails documentation for adapter-specific details.

Some adapters have specific requirements, such as the installation of additional Ruby or native libraries. This may restrict your choice of server operating system for running your Rails application. For example, the sqlserver adapter requires the use of ODBC, which may in turn require you to install a variety of ODBC software on your application server (see Make sure you are aware of all the pre-requisite software you need for some of the Rails database adapters.

Testing the Database Connection

It is useful to check that the database connection is working before going any further. There's an easy way to do this. Start up a Rails console session using another of the built-in scripts Rails adds to your application called the console. Do this using the command line, by connecting into RAILS_ROOT and running:

  ruby script/console

You should see:

  Loading development envir  >>

If you do, Rails has loaded your application's components correctly. The command prompt can now be used to enter commands, which directly manipulate your Rails application, without having to use a browser: very useful for general testing and tinkering. (We'll come back to the console throughout this article.) For now, enter this command to test your database connection:

  >> ActiveRecord::Base.connection

If the database connection is working correctly, you should see:

  => #<ActiveRecord::ConnectionAdapters::MysqlAdapter:0xb74dbe34 ...>

This has been truncated, as you get a lot of detail back from the console. But providing an ActiveRecord::ConnectionAdapters::MySQLAdapter instance has been successfully created, you can be happy that the database connection is working.

If the database connection fails, you might see this instead:

  Mysql::Error: Can't connect to local MySQL server through socket '/var/
  run/mysql/mysqld.sock' (2)

...which means the socket location is wrong.


Mysql::Error: Access denied for user 'intranet'@'localhost' (usingpassword:

...which means the system account does not have access to the server and/or the database. The next section covers what to do if one of these errors occurs.

Troubleshooting a MySQL Connection

If you discover that your MySQL connection isn't working (see the previous section),check the following:

  1. Test whether you can login to MySQL using the mysql command line client and the system account, as specified in your database.yml file. If you can login, it's likely that your database.yml file contains a typo in the username and/or password.    
  2. If you can login with the mysql command line client using the system account, try the command:
    USE intranet_development;

    If you get an error at this point, it means the system account doesn't have the correct level of access to the database. Check the permissions granted to the system account.

  3. Check that you are using the correct MySQL socket location. If you are unsure where your socket is located, login using the MySQL command line client and run this query:
    | mysql> SHOW VARIABLES LIKE 'socket';
    You should get back something like this:
    | Variable_name | Value |
    | socket | /var/run/mysqld/mysqld.sock |

    Now, make sure that the socket setting in database.yml matches the value of the socket variable reported by MySQL.

  4. If you are connecting using a host option, ensure that you can connect from the application server to the database server using the mysql command line client. If you can connect successfully, but your Rails application can't, it may be that the host setting is wrong in database.yml.

Hopefully, you should be able to fix pretty much any MySQL connection error by following these steps.


In this article, we covered the various aspects of working with Rails. We looked at the Model-View-Controller Architechture, setting up a new Rails application, using Mongrel, and we also looked at how to connect Rails to a database and the related troubleshooting.

You've been reading an excerpt of:

Ruby on Rails Enterprise Application Development: Plan, Program, Extend

Explore Title