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.

3 Responses 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)

  2. natesymer says:

    I’d like to point out that you program like a C developer. It’s not a bad thing, but this is Ruby. You don’t use camel case and you don’t define a method called main only to call it a few lines later.

    Also, why do you need to wrap everything? Wouldn’t it be faster to not wrap it?

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