Ruby Database Access - DBI Tutorial

This chapter will show you how to access the database using Ruby. The Ruby DBI module provides a database-independent interface to Ruby scripts similar to the Perl DBI module.

DBI is the Database independent interface, which represents Ruby's database-independent interface. DBI provides an abstraction layer between Ruby code and the underlying database, allowing you to easily implement database switching. It defines a set of methods, variables, and specifications that provide a consistent database interface independent of the database.

DBI can interact 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 independent of any database available in the background. Whether you are using Oracle, MySQL, Informix, you can use DBI. The architecture diagram below clearly illustrates this.

Ruby DBI The general architecture uses two layers:

  • Database Interface (DBI) layer. This layer is independent of the database and provides a set of public access methods, regardless of the database server type.
  • Database Drive (DBD) layer. This layer is dependent on the database, and different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, Oracle, etc. use different drivers. Each driver is responsible for interpreting requests from the DBI layer and mapping those requests to requests for a given type of database server.


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

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

The Mac OS system needs to modify the ~/.bash_profile or ~/.profile file and add the following code:

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)

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:

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 the 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 script, the following results will be generated on the Linux machine.

  Server   version  :  5.0.45< /span>

If the connection is made with a data source, return the database handle (Database Handle) and save it to dbh for later use, otherwise dbh will be set to The nil value, e.err and e::errstr return the error code and error string, respectively.

Finally, before exiting this program, be sure to close the database connection and free up resources.

INSERT action

When you want to create a record in a database table, you need to use an INSERT operation.

Once the database connection is established, we can prepare to create a table or create an insert data table using the do method or the prepare and execute methods. record of.

Use the do statement

Statements that do not return rows can be processed by calling the do database. This method takes a statement string argument and returns the number of rows affected by the statement.


Similarly, you can execute a SQL INSERT statement to create a record inserted 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< /span> dbh.disconnect if dbh end

Use prepare and execute

You can use DBI's prepare and execute methods to execute SQL statements in Ruby code.

The steps to create a record are as follows:

  • Prepare an SQL statement with an INSERT statement. This will be done by using the prepare method.
  • Execute the SQL query and select all the results from the database. This will be done by using the execute method.
  • Release the statement handle. This will be done using the finish API.
  • If everything goes well, commit the action, otherwise you can rollback complete the transaction.

The following is the syntax for using these two methods:

dbh[ 'AutoCommit'] = false # Set auto-submit to false. begin dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'") dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'") dbh.commit rescue puts "transaction failed" dbh.rollback end dbh['AutoCommit'] = true

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