Question

This is a Ruby programming tutorial for the PostgreSQL database. It covers the basics of PostgreSQL programming with the Ruby language.

PostgreSQL

PostgreSQL is a powerful, open source, object-relational database system. It is a multi-user database management system. It runs on multiple platforms, including Linux, FreeBSD, Solaris, Microsoft Windows, and Mac OS X. PostgreSQL is developed by the PostgreSQL Global Development Group.

PostgreSQL has sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.

Ruby

Ruby is a dynamic, reflective, general-purpose object-oriented programming language. The original author is a Japanese programmer Yukihiro Matsumoto. Ruby first appeared in 1995. Ruby supports various programming paradigms. This includes object orientation, reflection, imperative, and reflective programming.

Ruby pg

Ruby pg is a module that allows Ruby programs to interact with the PostgreSQL database engine. It supports the functions defined in the libpq C library.

Installation

We are going to install PostgreSQL database and additional necessary libraries.

$ sudo apt-get install postgresql

On a Debian-based system, we can install the PostgreSQL database from the packages using the above command.

$ sudo update-rc.d -f postgresql remove
 Removing any system startup links for /etc/init.d/postgresql ...
   /etc/rc0.d/K21postgresql
   /etc/rc1.d/K21postgresql
   /etc/rc2.d/S19postgresql
   /etc/rc3.d/S19postgresql
   /etc/rc4.d/S19postgresql
   /etc/rc5.d/S19postgresql
   /etc/rc6.d/K21postgresql

If we install the PostgreSQL database from packages, it is automatically added to the startup scripts of the operating system. If we are only learning to work with the database, it is unnecessary to start the database each time we boot the system. The above command removes any system startup links for the PostgreSQL database.

$ sudo apt-get install libpq-dev

To compile the Ruby pg module, we also need the development files of the C libpg library.

$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.

postgres=# \password postgres

We set a password for the postgres user.

$ sudo apt-get install ruby-dev

We install the Ruby development libraries, which are needed for compiling Ruby extention modules.

$ sudo gem install pg 

We install the Ruby pg module, which is the Ruby interface to the PostgreSQL database.

Starting and stopping PostgreSQL

In the next section, we are going to show how to start the PostgreSQL database, stop it, and query its status.

$ sudo service postgresql start
 * Starting PostgreSQL 9.3 database server   [ OK ]

On Debian-based Linux, we can start the server with the service postgresql start command.

$ sudo service postgresql status
9.3/main (port 5432): online

We use the service postgresql status command to check if PostgreSQL is running.

$ sudo service postgresql stop
 * Stopping PostgreSQL 9.3 database server   [ OK ]

We use the service postgresql stop command to stop PostgreSQL.

$ service postgresql status
9.3/main (port 5432): down

At this moment, the service postgresql status command reports that the PostgreSQL database is down.

Creating a user and a database

In the following steps, we create a new database user and database.

$ sudo -u postgres createuser janbodnar

We create a new role in the PostgreSQL system. We allow it to have ability to create new databases. A role is a user in a database world. Roles are separate from operating system users.

$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.

postgres=# ALTER USER janbodnar WITH password 'pswd37';
ALTER ROLE
postgres=# \q

With the psql command, we add a password for the new user.

PostgreSQL often uses trust or peer authentication policies on local connections. In case of the trust authentication policy, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). When making connections to the database, no password is required. (The restrictions made in the database and user columns still apply.) The trust authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate on a multiuser machine. In case of the peer authentication policy, the database username must match the operating system username.

$ sudo -u postgres createdb testdb --owner janbodnar

With the createdb command, we create a new database called testdb . Its owner is the new database user.

The libpq library

The libpq library is the C interface to PostgreSQL. It is a set of library functions that allow client programs to interact with PostgreSQL. It is also the underlying engine for several other PostgreSQL application interfaces, including those written for C++, Perl, PHP, Ruby, Python, and Tcl.

Ruby pg module is a wrapper around the libpg library.

lib_version.rb

#!/usr/bin/ruby

require 'pg'

puts 'Version of libpg: ' + PG.library_version.to_s

The program prints the version of the libpq library.

require 'pg'

We include the pg module.

puts 'Version of libpg: ' + PG.library_version.to_s

The library_version method returns the version of the libpq being used.

$ ./lib_version.rb 
Version of libpg: 90309

The version of the library is 9.3.9.

Server version

In the following example, we find out the version of the PostgreSQL database.

server_version.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  puts con.server_version

rescue PG::Error => e

  puts e.message 
  
ensure

  con.close if con
  
end

The example connects to the PostgreSQL database, executes a server_version method, prints the version, closes the connection, and cleans up.

...
# TYPE  DATABASE    USER      ADDRESS         METHOD

# "local" is for Unix domain socket connections only
local   all       all                   peer
...

In the pg_hba.conf , we have the peer default authentication method. In this method, the database user name must match the operating system user name. No password is required to make a connection.

con = PG.connect :dbname => 'testdb', :user => 'janbodnar'

With the connect method, we make a connection to the database. In the connection string, we provide the user name and the database name.

rescue PG::Error => e

  puts e.message 

We check for errors. This is important, since working with databases is error prone.

ensure

  con.close if con
  
end

In the end, we release the resources.

$ ./server_version.rb 
90309

Running the program, we get the database server version.

Authentication with a password

Next, we are going to authenticate to the database server with a password. In all other examples in this tutorial, we assume the peer or trust authentication mode. We change the authentication type for the local connection inside the pg_hba.conf file to md5 .

$ sudo service postgresql restart

To apply the changes, the database server must be restarted.

password_authentication.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar', 
    :password => 'pswd37'

  user = con.user
  db_name = con.db
  pswd = con.pass
  
  puts "User: #{user}"
  puts "Database name: #{db_name}"
  puts "Password: #{pswd}" 
  
rescue PG::Error => e

  puts e.message 
  
ensure

  con.close if con
  
end

In the example, we connect to the database with a password. We print the username, database name, and the password of the current database connection.

con = PG.connect :dbname => 'testdb', :user => 'janbodnar', 
  :password => 'pswd37'

In the connection string, we add the password option.

user = con.user

The user method returns the user name of the connection.

db_name = con.db

The db method returns the database name of the connection.

pswd = con.pass

The pass method returns the password of the connection.

$ ./password_authentication.rb 
User: janbodnar
Database name: testdb
Password: pswd37

The program prints the database user, the database name, and the password used.

Creating a database table

In this section, we create a database table and fill it with data.

create_table.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  con.exec "DROP TABLE IF EXISTS Cars"
  con.exec "CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
    Name VARCHAR(20), Price INT)"
  con.exec "INSERT INTO Cars VALUES(1,'Audi',52642)"
  con.exec "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
  con.exec "INSERT INTO Cars VALUES(3,'Skoda',9000)"
  con.exec "INSERT INTO Cars VALUES(4,'Volvo',29000)"
  con.exec "INSERT INTO Cars VALUES(5,'Bentley',350000)"
  con.exec "INSERT INTO Cars VALUES(6,'Citroen',21000)"
  con.exec "INSERT INTO Cars VALUES(7,'Hummer',41400)"
  con.exec "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
  
rescue PG::Error => e

  puts e.message 
  
ensure

  con.close if con
  
end

The created table is called Cars and it has three columns: the Id, the name of the car, and its price.

con.exec "DROP TABLE IF EXISTS Cars"

The exec method submits an SQL command to the server and waits for the result. Our SQL command drops a table if it already exists.

$ ./create_table.rb
$ psql testdb
psql (9.3.9)
Type "help" for help.

testdb=> SELECT * FROM Cars;
 id |  name  | price  
----+------------+--------
  1 | Audi     |  52642
  2 | Mercedes   |  57127
  3 | Skoda    |   9000
  4 | Volvo    |  29000
  5 | Bentley  | 350000
  6 | Citroen  |  21000
  7 | Hummer   |  41400
  8 | Volkswagen |  21600
(8 rows)

We execute the program and verify the created table with the psql tool.

Simple query

In this section, we execute a simple query command.

query_version.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'

  rs = con.exec 'SELECT VERSION()'
  puts rs.getvalue 0, 0
  
rescue PG::Error => e

  puts e.message 
  
ensure

  con.close if con
  
end

The example gets the version of the database server.

rs = con.exec 'SELECT VERSION()'

The SELECT VERSION() SQL statement retrieves the version of the database.

puts rs.getvalue 0, 0

The getvalue method returns a single field value of one row of the returned result set.

$ ./query_version.rb 
PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit

The program prints this output.

Retrieving multiple rows of data

The following example executes a query that returns multiple rows of data.

multiple_rows.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  rs = con.exec "SELECT * FROM Cars LIMIT 5"

  rs.each do |row|
    puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
  end
  
rescue PG::Error => e

  puts e.message 
  
ensure

  rs.clear if rs
  con.close if con
  
end

The program prints the data of the first five rows of the Cars table.

rs = con.exec "SELECT * FROM Cars LIMIT 5"

This SQL query returns five rows of data.

rs.each do |row|
  puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
end

With the each method, we go through the result set and print the fieds of a row.

$ ./multiple_rows.rb 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

This is the output of the multiple_rows.rb program.

Prepared statements

Prepared statements guard against SQL injections and increase performance. When using prepared statements, we use placeholders instead of directly writing the values into the statements.

prepared_statement.rb

#!/usr/bin/ruby

require 'pg'

if ARGV.length != 1 then
  puts "Usage: prepared_statement.rb rowId"
  exit
end

rowId = ARGV[0]

begin
  
  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
  rs = con.exec_prepared 'stm1', [rowId]
    
  puts rs.values 
  
rescue PG::Error => e

  puts e.message 
  
ensure

  rs.clear if rs
  con.close if con
  
end

The program takes a row Id as its argument. It fetches the data of the specified row and prints it. Since the program takes a value from a user, which cannot be trusted, it is necessary to use a prepared statement.

rowId = ARGV[0]

The command line argument is stored in the rowId variable.

con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"

The prepare method prepares an SQL statement with the given name to be executed later. Our SQL statement returns one row of the Cars table. The $1 is a placeholder, which is later filled with an actual value.

rs = con.exec_prepared 'stm1', [rowId]

The exec_prepared method executes a prepared named statement specified by the statement name. The second parameter is an array of bind parameters for the SQL query.

puts rs.values 

The values method prints the field values of the row.

$ ./prepared_statement.rb 4
4
Volvo
29000

This is the output of the example.

The following example shows another way to create prepared statements.

prepared_statement2.rb

#!/usr/bin/ruby

require 'pg'

begin
  
  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"
  rs = con.exec_params(stm, [1, 2, 3])
    
  puts rs.values 
  
rescue PG::Error => e

  puts e.message 
  
ensure

  rs.clear if rs
  con.close if con
  
end

The example uses exec_params to create and execute a prepared statement.

stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"

In the statement, we append data types of the expected parameters to the placeholders.

rs = con.exec_params(stm, [1, 2, 3])

The exec_params method sends an SQL query request to the database using placeholders for parameters.

$ ./prepared_statement2.rb 
1
2
3

This is the output of the example.

Metadata

Metadata is information about the data in the database. The following belongs to metadata: information about the tables and columns in which we store data, the number of rows affected by an SQL statement, or the number of rows and columns returned in a result set.

Column headers

In the first example, we print column headers.

column_headers.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
  puts 'There are %d columns ' % rs.nfields
  puts 'The column names are:'
  puts rs.fields
  
rescue PG::Error => e

  puts e.message 
  
ensure

  rs.clear if rs
  con.close if con
  
end

The example prints the number of available columns and their names to the console.

rs = con.exec 'SELECT * FROM Cars WHERE Id=0'

In the SQL statement, we select all columns of a row.

puts "There are %d columns " % rs.nfields

The nfields method returns the number of columns in the row of the query result.

puts rs.fields

The fields method returns an array of strings representing the names of the fields in the result.

$ ./column_headers.rb 
There are 3 columns 
The column names are:
id
name
price

This is the output of the example.

Listing tables

The PostgreSQL's information schema consists of a set of views that contain information about the objects defined in the current database. The tables view contains all tables and views defined in the current database.

list_tables.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  rs = con.exec "SELECT table_name FROM information_schema.tables 
    WHERE table_schema = 'public'"
    
  rs.each do |row|
    puts row['table_name']
  end
  
rescue PG::Error => e

  puts e.message 
  
ensure

  rs.clear if rs
  con.close if con
  
end

The example prints all the tables in the testdb database.

rs = con.exec "SELECT table_name FROM information_schema.tables 
  WHERE table_schema = 'public'"

This SQL statement selects all tables from the current database.

rs.each do |row|
  puts row['table_name']
end

The tables are printed to the console.

$ ./list_tables.rb 
authors
books
cars

The list_tables.rb program prints available tables in the testdb database.

Transactions

A transaction is an atomic unit of database operations against the data in one or more databases. SQL statements in a transaction can be either all committed to the database or all rolled back. SQL statements are put into transactions for data safety and integrity.

PostgreSQL operates in the autocommit mode. Every SQL statement is executed within a transaction: each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.

An explicit transaction is started with the BEGIN command and ended with the COMMIT or ROLLBACK command.

transaction.rb

#!/usr/bin/ruby

require 'pg'

begin

  con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
  
  con.transaction do |con|
    
    con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
    con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
  
  end
  
rescue PG::Error => e

  puts e.message 
  
ensure

  con.close if con
  
end

In the example, we update the price of a car and insert a new car. The two operations are included in a single transaction. This means that either both operations are executed or none.

con.transaction do |con|
  
  con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
  con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"

end

The transaction method runs the code inside the block in a single transaction. It executes a BEGIN at the start of the block, and a COMMIT at the end of the block, or ROLLBACK if any exception occurs.

This was PostgreSQL Ruby tutorial. You may be also interested in SQLite Ruby tutorial , MySQL Ruby tutorial , PostgreSQL Python tutorial , MongoDB Ruby tutorial . or PostgreSQL PHP tutorial on ZetCode.