Recipe 13.10. Talking Directly to a PostgreSQL Database

Problem

You want to send SQL queries and commands directly to a PostgreSQL database.

Solution

As with the MySQL recipe preceding this one, ask: do you really need to do this? The generic DBI library usually works just fine. As before, the main exception is when you need to make low-level SQL calls from within a Rails application.

There are two APIs for communicating with a PostgreSQL database, and both are available as gems. The postgres gem provides a Ruby binding to the C client library, and the postgres-pr gem provides a pure Ruby interface.

Here's a Postgres-specific version of the method with_db, defined in the chapter intro. It returns a PGconn object, which you can use to run queries or get server information. This code assumes you're accessing the database through TCP/IP on port 5432 of your local machine.

	require 'rubygems'
	require 'postgres'

	def with_db
	  db = PGconn.connect('localhost', 5432, '', '', 'cookbook',
	                      'cookbook_user', 'password')
	  begin
	    yield db
	  ensure
	    db.close
	  end
	end

The PGconn#exec method runs any SQL statement, whether it's a SELECT query or something else. When it runs a query, the return value is a result-set object (a PGresult); otherwise, it's nil. Here it is running some SQL commands:

	with_db do |db|
	  begin
	    db.exec('drop table secrets')
	  rescue PGError
	    # Unlike MySQL, Postgres does not have a "drop table unless exists"
	    # command. We can simulate it by issuing a "drop table" command and
	    # ignoring any error due to the table not existing in the first place.
	    # This is essentialy what MySQL's "drop table unless exists" does.
	  end

	  db.exec('create table secrets( id SERIAL PRIMARY KEY,
	                                 secret TEXT )')
	  db.exec(%{insert into secrets(secret) values
	            ('Oh, Postgres, you\\'re the only one who really understands me.')})
	end

Here's a query:

	with_db do |db|
	  res = db.query('select * from secrets')
	  res.each { |row| puts "#{row[0]}: #{row[1]}" }
	end
	# 1: Oh, Postgres, you're the only one who really understands me.

Discussion

Note the slight differences between the Postgres implementation of SQL and the MySQL implementation. The "drop table if exists" syntax is MySQL-specific. Postgres names the data types differently, and expects string values to be single-quoted.

Like the database connection itself, the result set you get from exec wants to be closed when you're done with it. As we did with query in the MySQL binding, we can alter exec to take an optional code block and do the cleanup for us:

	class PGconn
	  alias :exec_no_block :exec
	  def exec(sql)
	    res = exec_no_block(sql)
	    return res unless block_given?
	    begin
	      yield res
	    ensure
	      res.clear if res
	    end
	  end
	end

Now we can write more concise query code, and not have to worry about freeing the result set:

	with_db do |db|
	  db.exec('select * from secrets') do |res|
	    res.each { |row| puts "#{row[0]}: #{row[1]}" }
	  end
	end
	# 1: Oh, Postgres, you're the only one who really understands me.

The method PGresult#each yields you the rows of a result set as arrays, and PGresult#num_tuples gives you the number of rows matched by a query. The Postgres database binding has no equivalent of the MySQL binding's each_hash, but you can write one pretty easily:

	class PGresult
	  def each_hash
	    f = fields
	    each do |array|
	      hash = {}
	      fields.each_with_index do |field, i|
	        hash[field] = array[i]
	      end
	      yield hash
	    end
	  end
	end

Here it is in action:

	with_db do |db|
	  db.exec("select * from secrets") do |res|
	    puts "#{res.num_tuples} row(s) matched:"
	    res.each_hash do |hash|
	      hash.each { |k,v| puts " #{k} = #{v}" }
	    end
	  end
	end
	# 1 row(s) matched:
	#  id = 1
	# secret = Oh, Postgres, you're the only one who really understands me.

See Also

• The Postgres reference (http://www.postgresql.org/docs/manuals/ )

• The reference for the Ruby Postgres binding (http://ruby.scripting.ca/postgres/)

• If you can't get the native Postgres binding installed, try the postgres-pr gem; it implements a pure Ruby client to the Postgres server, with more or less the same interface as the native binding

• The PGconn.quote method helps you defend against SQL injection attacks; see Recipe 13.15, "Preventing SQL Injection Attacks," for more