Ruby Database Access - DBI Tutorial

This part will tell you the best way to get to the database utilizing Ruby. The Ruby DBI module gives a database-autonomous interface to Ruby contents like the Perl DBI module.

DBI is the Database autonomous interface, which speaks to Ruby's database-free interface. DBI gives a deliberation layer between Ruby code and the basic database, enabling you to effortlessly actualize database exchanging. It characterizes a lot of techniques, factors, and details that give a steady database interface free of the database.

DBI can interface with the following:

  • ADO (ActiveX Data Objects)
  • DB2
  • Frontbase
  • mSQL
  • MySQL
  • ODBC
  • Oracle
  • OCI8 (Oracle)
  • PostgreSQL
  • Proxy/Server
  • SQLite
  • SQLRelay

DBI Application Architecture

DBI is free of any database accessible out of sight. Regardless of whether you are utilizing Oracle, MySQL, Informix, you can utilize DBI. The engineering graph beneath obviously delineates this.

Ruby DBI Architecture

Ruby DBI The general design utilizes two layers:

  • Database Interface (DBI) layer. This layer is free of the database and gives a lot of community strategies, paying little respect to the database server type.
  • Database Drive (DBD) layer. This layer is subject to the database, and diverse drivers give access to various database motors. MySQL, PostgreSQL, InterBase, Oracle, and so on utilize diverse drivers. Every driver is in charge of translating demands from the DBI layer and mapping those solicitations to demands for a given sort of database server.


If you need to compose a Ruby content to get to a MySQL database, you have to introduce the Ruby MySQL module first.

Install Mysql Development Kit

# Ubuntu sudo apt-get install mysql-client sudo apt-get install libmysqlclient15-dev # Centos yum install mysql-devel


If you want to write a Ruby script to access a MySQL database, you need to install the Ruby MySQL module first.

Install Mysql Development Kit

MYSQL=/usr/local/mysql/bin export PATH=$PATH:$MYSQL export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

Or use a soft connection:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Install DBI with RubyGems (recommended)

RubyGems was created in November 2003 and became part of the Ruby standard library from Ruby 1.9. More details can be found at: Ruby RubyGems

Install dbi and dbd-mysql with gem:

sudo gem install dbi sudo gem install mysql sudo gem install dbd-mysql

Use source installation (Ruby version less than 1.9 using this method)

This module is a DBD and is available from http://tmtm.org/downloads/mysql/ruby / Download it.

Download the latest package, extract it to the directory, and execute the following command to install:

ruby extconf .rb or ruby extconf.rb --with-mysql-dir=/usr/local/mysql or ruby extconf.rb --with-mysql- config

Then compile:


Get and install Ruby/DBI

You can download and install the Ruby DBI module from the link below:


Before you start the installation, make sure you have root privileges. Now install the following steps to install:

Step 1

git clone https:// github.com/erikh/ Ruby-dbi. git

Or directly zip the package and unzip it.

Step 2

Go to the directory ruby-dbi-master and configure it in the directory using the setup.rb script. The most common configuration command is the config parameter without any arguments. This command is configured by default to install all drivers.

ruby setup .rb config

More specifically, you can use the --with option to list the specific parts you want to use. For example, if you only want to configure the main DBI module and the MySQL DBD layer driver, enter the following command:

ruby setup .rb config --with=dbi,dbd_mysql

Step 3

The final step is to create a drive and install it using the following command:

ruby setup.rb setup ruby setup.rb install

Database Connection

Assuming we are using a MySQL database, make sure to:

before connecting to the database:

  • You have created a database TESTDB.
  • You have created the table EMPLOYEE in TESTDB.
  • This table has fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • Set the user ID "testuser" and the password "test123" to access TESTDB
  • The Ruby module DBI has been properly installed on your machine.
  • You have seen the MySQL tutorial and understood the basics of MySQL.

The following is an example of connecting the MySQL database "TESTDB":



#!/usr/bin/ruby -w require "dbi" begin # Connect to the MySQL server dbh= DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") # Get the server version string and display row = dbh.select_one("SELECT VERSION()") puts "Server version: " + row[0 ] rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure # Disconnect from the server< /span> dbh.disconnect if dbh end

When running this content, the accompanying outcomes will be produced on the Linux machine.

Server version: 5.0.45

If the association is made with an information source, the database handle (Database Handle) is returned and spared to dbh for resulting use, generally dbh will be set to The nil esteem, e.err and e::errstr return the mistake code and blunder string, separately.

Finally, before leaving this program, make certain to close the database association and free up assets.

INSERT action

When you need to make a record in a database table, you have to utilize an INSERT task.

Once the database association is set up, we can plan to make a table or make an addition information table utilizing the do strategy or the prepare and execute techniques. record of.

Use the do statement

Statements that don't return columns can be handled by calling the do database. This strategy takes an announcement string contention and returns the quantity of lines influenced by the announcement.


Similarly, you can execute a SQL INSERT proclamation to make a record embedded into the EMPLOYEE table.


#!/usr/bin/ruby -w require "dbi" begin # Connect to the MySQL server dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)" ) puts "Record has been created" dbh.commit rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure #Disconnect from the server... dbh.disconnect if dbh end

Use prepare and execute

You can utilize DBI's prepare and execute techniques to execute SQL explanations in Ruby code.

The ventures to make a record are as follows:

  • Prepare a SQL articulation with an INSERT proclamation. This will be finished by utilizing the prepare technique.
  • Execute the SQL inquiry and select every one of the outcomes from the database. This will be finished by utilizing the execute strategy.
  • Release the announcement handle. This will be finished utilizing the finish API.
  • If everything goes well, commit the activity, else you can rollback complete the exchange.

The following is the linguistic structure for utilizing these two methods:


sth = Dbh.prepare( statement) sth.execute ... zero or more SQL operations .. . sth.finish

These two strategies can be utilized to pass bind values ​​to SQL proclamations. Now and again the esteem entered may not be given ahead of time, in which case the bound esteem will be utilized. Utilize the question mark (?) rather than the real esteem, and the real esteem is gone through the execute() API.

The following precedent makes two records in the EMPLOYEE table:

welookups is optimized for learning.© welookups. 2018 - 2019 All Right Reserved and you agree to have read and accepted our term and condition.