Home » Ruby programming

Database access in Ruby

In this tutorial, we are going to learn about the database access in Ruby programming language, how to install and obtain Ruby DBI?
Submitted by Hrithik Chandra Prasad, on September 08, 2019

Ruby Database Access

If you want to connect your Ruby code with a database, you will have to take help from DBI. DBI is an abbreviation for Database Independent Interface and facilitates you with a conceptual layer between the Ruby code and primary database. It allows you an easy switching of Database implementations and also determines a set of variables, methods, and standard that advances a compatible database interface which is independent of the actual database being utilized.

How to install and obtain Ruby DBI?

For downloading the Ruby DBI, click on the link given below,

https://imgur.com/NFEuWe4/embed

After successful downloading, follow the steps given below before carrying out the process of Installation and also make sure the root privilege of your machine.

Step 1:

    $ tar zxf dbi-0.2.0.tar.gz

You are required to step into the distribution directory namely dbi-0.2.0 and setup.rb script will let you configure it. With the help of the following the command, the entire driver will be installed by default.

Step 2:

    $ ruby setup.rb config

Finally, build the driver and install the same by writing the following commands on the terminal.

Step 3:

    $ ruby setup.rb setup
    $ ruby setup.rb install

Here we are going to work with MySQL database. So, obviously before getting connected to a database, first, we have to create it. To create a database with the following specifications,

• The name of database is ExampleDB.
• There must exists a table named EMPLOYEE in ExampleDB.
• Table must be having fields namely FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
• The database should be accessible to Username 'user123' with the Password 'dbtest'.
• The proper installation of Ruby Module 'DBI' is must; otherwise no connection will be established.
• You should be aware of basics of MySQL.

Now, let us see how to connect a Ruby code with the Database with the help of following code,

require "dbi"
begin
   # The following statement is the statement 
   # to get connected with the Database
   datab = DBI.connect("DBI:Mysql:ExampleDB:localhost", "user123", "dbtest")
   # retrieving server version and displaying it.
   row = datab.select_one("SELECT VERSION()")
   puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
   puts "There is a problem with the connection. Error occured"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnecting from server
   datab.disconnect if datab
end

The above code will display the Server version. Firstly, we are including the module dbi in the code. If the connection is successfully established then the statement will return a database handle and it will get stored into the datab. Otherwise, an exception is thrown and datab will be assigned a nil value. The error code and statement will be printed in the terminal. At last, it is required to close the connection for releasing the resources held by the program.