Ruby on Rails Database
In this tutorial, we will show you how to work with databases in ruby on rails. Ruby on rails supports many database engines but we will focus on MySQL in this tutorial. We will also show you how to connect to SQL Server in rails.
The best way to learn is by practice. Towards that end, we will be working with a Ruby on Rails database example tutorial project. We will create a movies database. The complete tutorial project series will look similar to the one shown below.
Topics to be covered
We will cover the following topics in this tutorial
- Tutorial pre-requisites
- Databases supported by rails
- Rails SQL Server Adapter
- MySQL Rails
- MySQL Rails Database Configuration
- Ruby on Rails Create Database
- Rails Database Reset
- Rails Drop Database
This tutorial assumes you have;
- HTML basics – this is an added advantage. You can still write rails code without knowing HTML but it’s highly recommended.
- Installed ruby on rails version 4 and above
- WEBrick webserver or any other webserver that supports ruby on rails
- MySQL already installed, up and running
- You have an IDE i.e. Aptana Studio or text editor such as sublime text.
- Command line prompt or terminal
- Access to the internet
Databases supported by rails
From a theoretical point of view, any database can be supported by rails as long as an adapter that facilitates communication between rails and the database exists. Ruby on rails supports the following databases out of the box.
Connecting to other databases such as SQL server requires you to install the required adapter.
SQL Server Rails
This activerecord-sqlserver-adapter example shows you how to install the adapter for SQL Server and set the
Add the following line to gemfile to install activerecord-sqlserver-adapter.
gem 'tiny_tds' gem 'activerecord-sqlserver-adapter'
Run the following command to install the gem
SQL Server rails database.yml
/config/database.yml file should be as follows
development: adapter: sqlserver host: 127.0.0.1 # used when dataserver is blank port: 1433 database: database_name username: sa password: billgates
Note: you are supposed to create the database in SQL Server
By default, a new ruby on rails project is set to work with SQLite. Our tutorial series will use MySQL so we will need to take care of that when creating a new project. It is possible to do that after creating the project but it’s better to do that when creating a new project.
Creating a new MySQL rails project
I am using windows and I have a Sites directory in drive C where all of my ruby on rails projects are created. You can store the files anywhere you want to. However, I will be making reference to
C:\Sites throughout these tutorial series.
Open the command prompt
Run the following command to browse to the sites directory
Run the following command to create a new rails project
rails new movies_library –d mysql
rails new movies_library –d mysqlthe command creates a new rails project movies_library. –d mysql tells rails to configure the new project to work with MySQL database
The rails command runs bundle install command after creating a new project. The following error is very common especially when working with windows.
Unable to pull data from 'https://rubygems.org/': SSL_connect returned=1 errno=0 state=SSLv3
The quick fix to the above problem
Open gemfile file in the project root
Locate the following line
- The above source uses HTTPS which is a secure protocol
Update it to the following
- The above source uses HTTP without security. Note: for security reasons, you should only use HTTP on a computer which does not contain extra super hyper sensitive data.
MySQL rails database configuration
The database configuration file is located in
/config/database.yml file. Rails supports three environments namely development, production and test. Each of these environment is supposed to have its own database.
Set the username and password as shown in the code below. Use a valid username and password based on your MySQL database configurations.
default: &default adapter: mysql2 encoding: utf8 pool: 5 username: root password: melody host: localhost
Rails has set the database name to movieslibrarydevelopment by default. It has also set the database names for production and test but we are only interested in the development database for now.
Ruby on Rails Create Database
The database name that rails set for us in the configuration file does not yet exist. We can either create the database using any MySQL client tool or we can use rake to create the database. We will use rake to create the database. Rake will use the database connection parameters set in
Open the command prompt or terminal and run the following command
Run the following rake command to create the database
If the command doesn’t not return any error of feedback, then it was executed successfully. The above database will create the development and test databases.
Open MySQL and browse through the list of database. You should be able to see the following databases.
Rails drop database
Just like we used rake to create the database, we can also use rake to delete the database if we no longer need it.
The following command is used to delete the database.
If you check the databases in MySQL, our two database for development and testing will be deleted. We need the databases for our ruby on rails database example project so we will create them again.
Run the following command to create the database again
Testing the database connection
We will now run the web server and access our application from the web browser. If everything goes well, we will be able to see the welcome page. If any error occurs well connecting to the database, rails will give us the error message
Open the command prompt / terminal
Run the following command to browse to the web root of our project
Run the following rails command to start the server
Load the following URL in your web browser
You should be able to see the welcome page. If you get any errors, use the comments section to ask for help.
In this tutorial, we learnt how to create a new rails project that uses MySQL database, set the database connection parameters and use rake commands to create the database and drop them when we no longer need them.
In the next tutorial, we will introduce you to rake and rake db commands.
If you found this tutorial useful, support us by using the social media buttons to like and share the tutorial. If you didn’t find it useful, please use the comments section below to let us know how we can do better next time.
Each week, a new Ruby on Rails tutorial is added to our collection. Subscribe to our newsletter, like our Facebook fan page or follow us on Twitter to get free updates when the collection is updated.
Tutorial version 1: Date Published 2015-10-09