An Example of using PostgreSQL with Ruby

This is a fairly complete example, creating a table in the database called “Test”, and assuming a user has been added to Postgres as well with permissions to create, insert, query, and drop tables.

Replace [username] and [password] with the username and password for your database.

require 'pg'

class PostgresDirect
  # Create the connection instance.
  def connect
    @conn = PG.connect(
        :dbname => 'Test',
        :user => '[username]',
        :password => '[password]')
  end

  # Create our test table (assumes it doesn't already exist)
  def createUserTable
    @conn.exec("CREATE TABLE users (id serial NOT NULL, name character varying(255), CONSTRAINT users_pkey PRIMARY KEY (id)) WITH (OIDS=FALSE);");
  end

  # When we're done, we're going to drop our test table.
  def dropUserTable
    @conn.exec("DROP TABLE users")
  end

  # Prepared statements prevent SQL injection attacks.  However, for the connection, the prepared statements
  # live and apparently cannot be removed, at least not very easily.  There is apparently a significant
  # performance improvement using prepared statements.
  def prepareInsertUserStatement
    @conn.prepare("insert_user", "insert into users (id, name) values ($1, $2)")
  end

  # Add a user with the prepared statement.
  def addUser(id, username)
    @conn.exec_prepared("insert_user", [id, username])
  end

  # Get our data back
  def queryUserTable
    @conn.exec( "SELECT * FROM users" ) do |result|
      result.each do |row|
        yield row if block_given?
      end
    end
  end

  # Disconnect the back-end connection.
  def disconnect
    @conn.close
  end
end

def main
  p = PostgresDirect.new()
  p.connect
  begin
    p.createUserTable
    p.prepareInsertUserStatement
    p.addUser(1, "Marc")
    p.addUser(2, "Sharon")
    p.queryUserTable {|row| printf("%d %s\n", row['id'], row['name'])}
  rescue Exception => e
    puts e.message
    puts e.backtrace.inspect
  ensure
    p.dropUserTable
    p.disconnect
  end
end

main
About these ads
This entry was posted in Ruby on Rails. Bookmark the permalink.

One Response to An Example of using PostgreSQL with Ruby

  1. SutoCom says:

    Reblogged this on Sutoprise Avenue, A SutoCom Source and commented:
    Add your thoughts here… (optional)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s