Learning Management Platform for Written Tutorial Series.

Ruby on Rails Database

Ruby on Rails Database

Introduction

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.

Use composer here

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

Tutorial pre-requisites

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.

  1. MySQL
  2. PostgreSQL
  3. SQLite

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 database.yml file.

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

bundle install

SQL Server rails database.yml

The corresponding /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

MySQL Rails

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

cd "C:\Sites"

Run the following command to create a new rails project

rails new movies_library –d mysql

HERE,

  • rails new movies_library –d mysql the 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

source 'https://rubygems.org'

HERE,

  • The above source uses HTTPS which is a secure protocol

Update it to the following

source 'http://rubygems.org'

HERE,

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

Open /config/database.yml

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 /config/database.yml

Open the command prompt or terminal and run the following command

cd "C:\Sites\movies_library"

Run the following rake command to create the database

rake db:create

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.

movies_library_development
movies_library_test

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.

rake db:drop

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

rake db:create

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

cd "C:\Sites\movies_library"

Run the following rails command to start the server

rails s

Load the following URL in your web browser

http://localhost:3000/

You should be able to see the welcome page. If you get any errors, use the comments section to ask for help.

Summary

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.

What’s next?

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 History

Tutorial version 1: Date Published 2015-10-09


...