Recommendations for working with PostgreSQL

ruby-talk
1

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

Thanks in advance.

···

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
awj @reddit: "The terms never and always are never always true."

2

Chad Perrin wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

Thanks in advance.

og www.nitroproject.org

$ cat testog.rb
require 'og'

class Comment
    property :title, String
    property :body, String
    property :author, String
    property :create_time, Time
end

og_psql = {
  :destroy => false,
  :store => :psql,
  :user => 'rthompso',
  :address => '127.0.0.1',
  :password => 'rthompso',
  :name => 'test'
}

db =Og.setup(og_psql)

t1= Time.now
# save the object in the database
db.store.start
1.upto(10000) { |i|
  c = Comment.new
  c.title = 'Hello'
  c.body = 'World'
  c.create_time = Time.now
  c.author = 'tml'
  c.save
}
db.store.commit
puts Time.now - t1

-----------------this is on cygwin windows ----------------------

   $ ruby -v ruby 1.8.6 (2007-03-13 patchlevel 0) [i386-cygwin]

···

------------------------------------------------------
cyberhome: /home/rthompso>
$ export RUBYOPT=rubygems
cyberhome: /home/rthompso>
$ ruby testog.rb
  INFO: Og uses the Psql store.
NOTICE: CREATE TABLE will create implicit sequence "ogcomment_oid_seq" for serial column "ogcomment.oid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ogcomment_pkey" for table "ogcomment"
  INFO: Created table 'ogcomment'.
10.697

cyberhome: /home/rthompso>
$ psql -U rthompso -h 127.0.0.1 -c "select count(*) from ogcomment" test
  count
-------
  10000
(1 row)

3

If you're looking for an ORM, there is of course ActiveRecord, and
someone else already offered up Og.

As far as the driver goes, the ruby-postgres gem seems to be the way to
go.

Ben

···

On Wed, Oct 17, 2007, Chad Perrin wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

4

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

First you need to install the postgres driver:

  sudo gem install postgres

As far as ORM's go, may I suggest Sequel:

  require 'sequel/postgres'

  DB = Sequel('postgres://rthompso:rthompso@127.0.0.1/test')

  DB.create_table :items do # Create a new table
    column :name, :text
    column :price, :float
  end

  items = DB[:items] # Create a dataset

  # Populate the table
  items << {:name => 'abc', :price => rand * 100}
  items << {:name => 'def', :price => rand * 100}
  items << {:name => 'ghi', :price => rand * 100}

  puts "Count of items: #{items.count}"

  puts "All items:"
  items.reverse_order(:price).print

  puts "The average price is: #{items.avg(:price)}"

  puts "All items with price under 50"
  items.filter {:price < 50}.print

You can find more information here:

  Google Code Archive - Long-term storage for Google Code Project Hosting.

Or on sequel-talk:

  http://groups.google.com/group/sequel-talk

best
Sharon

5

Reid Thompson wrote:

Chad Perrin wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

Thanks in advance.

og www.nitroproject.org

$ cat testog.rb
require 'og'

class Comment
   property :title, String
   property :body, String
   property :author, String
   property :create_time, Time
end

og_psql = {
:destroy => false,
:store => :psql,
:user => 'rthompso',
:address => '127.0.0.1',
:password => 'rthompso',
:name => 'test'
}

db =Og.setup(og_psql)

t1= Time.now
# save the object in the database
db.store.start
1.upto(10000) { |i|
c = Comment.new
c.title = 'Hello'
c.body = 'World'
c.create_time = Time.now
c.author = 'tml'
c.save
}
db.store.commit
puts Time.now - t1

-----------------this is on cygwin windows ----------------------

  $ ruby -v ruby 1.8.6 (2007-03-13 patchlevel 0) [i386-cygwin]

------------------------------------------------------
cyberhome: /home/rthompso>
$ export RUBYOPT=rubygems
cyberhome: /home/rthompso>
$ ruby testog.rb
INFO: Og uses the Psql store.
NOTICE: CREATE TABLE will create implicit sequence "ogcomment_oid_seq" for serial column "ogcomment.oid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ogcomment_pkey" for table "ogcomment"
INFO: Created table 'ogcomment'.
10.697

cyberhome: /home/rthompso>
$ psql -U rthompso -h 127.0.0.1 -c "select count(*) from ogcomment" test
count
-------
10000
(1 row)

I just updated og...
updated to reflect a few changes...
I'm sure there are others that can give better examples..

require 'og'

class Comment
    attr_accessor :title, String
    attr_accessor :body, String
    attr_accessor :author, String
    attr_accessor :create_time, Time
end

og_psql = {
  :destroy => false,
  :store => :postgresql,
  :user => 'rthompso',
  :address => '127.0.0.1',
  :password => 'rthompso',
  :name => 'test'
}

db =Og.setup(og_psql)

t1= Time.now
# save the object in the database
db.store.start
1.upto(10000) { |i|
  c = Comment.new
  c.title = 'Hello'
  c.body = 'World'
  c.create_time = Time.now
  c.author = 'tml'
  c.save
}
db.store.commit
puts Time.now - t1

6

> I have need to work with PostgreSQL via Ruby. What gems/modules do you
> recommend? Are there any quickstart-type howtos online to which you
> might direct me?

If you're looking for an ORM, there is of course ActiveRecord, and
someone else already offered up Og.

I'm not necessarily looking for ORM, though now that I think about it I
can't help but wonder if every single programmatic interface between Ruby
and a relational database might qualify in some manner as ORM (since
"everything is an object" in Ruby). In the Perl world, I'd tend to use
DBI for all things database-y, and was wondering what the preferred
approach would be in the Ruby world especially in the case of PostgreSQL.

As far as the driver goes, the ruby-postgres gem seems to be the way to
go.

Thanks for confirming that.

···

On Wed, Oct 17, 2007 at 11:21:15AM +0900, Ben Bleything wrote:

On Wed, Oct 17, 2007, Chad Perrin wrote:

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
W. Somerset Maugham: "The ability to quote is a serviceable substitute for
wit."

7

Thanks. I'll look into it.

···

On Wed, Oct 17, 2007 at 01:44:05PM +0900, Sharon Rosner wrote:

> I have need to work with PostgreSQL via Ruby. What gems/modules do you
> recommend? Are there any quickstart-type howtos online to which you
> might direct me?

First you need to install the postgres driver:

  sudo gem install postgres

As far as ORM's go, may I suggest Sequel:

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
Brian K. Reid: "In computer science, we stand on each other's feet."

8

Thanks. I'll look into it.

···

On Wed, Oct 17, 2007 at 08:22:27AM +0900, Reid Thompson wrote:

require 'og'

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
Baltasar Gracian: "A wise man gets more from his enemies than a fool from
his friends."

9

From: Chad Perrin [mailto:perrin@apotheon.com]
Sent: Wednesday, October 17, 2007 8:37 AM
To: ruby-talk ML
Subject: Re: recommendations for working with PostgreSQL

[...]

I'm not necessarily looking for ORM, though now that I think
about it I
can't help but wonder if every single programmatic interface
between Ruby
and a relational database might qualify in some manner as ORM (since
"everything is an object" in Ruby).

[...]

I'd argue against that - an ORM would map the entity described by the
database to an equivalent object in Ruby (say, an entry in an addressbook).
DBI like interfaces do map to objects in Ruby, but those objects represent
merely the database row with no knowledge about the entity that row
describes.

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
W. Somerset Maugham: "The ability to quote is a serviceable
substitute for
wit."

Felix

···

-----Original Message-----

10

> > I have need to work with PostgreSQL via Ruby. What gems/modules do you
> > recommend? Are there any quickstart-type howtos online to which you
> > might direct me?
>
> If you're looking for an ORM, there is of course ActiveRecord, and
> someone else already offered up Og.

I'm not necessarily looking for ORM, though now that I think about it I
can't help but wonder if every single programmatic interface between Ruby
and a relational database might qualify in some manner as ORM (since
"everything is an object" in Ruby). In the Perl world, I'd tend to use

But then, just because the language you're using means everything is
an object, does not mean to say that when you connect to a database
and start pulling stuff out of it that what you get is ORM. ORM is an
interface your application would define for itself, mapping to the
underlying tables/views/whatever in the database.

Ruby makes this easier in that you could just (at a crude level) map
an object you define to have properties, behaviour and scope of the
underlying tables, etc., in the database. Sometimes there is merit to
that -- and indeed, coming into this are things like patterns (the
factory pattern in particular is useful in ORM scenarios.)

DBI for all things database-y, and was wondering what the preferred
approach would be in the Ruby world especially in the case of PostgreSQL.

Having written numerous libraries (well, OK, two) in both perl and
ruby, I can honestly say that whilst it's somewhat "easier" in perl
(you're not forced to go down the abstracted route of ORM design) you
lose a lot of functionality -- the scope of what you can write in Perl
diminishes since the interface subroutines rarely map in an ORM way
[1]. Look into it heavily with Ruby -- it will make life easier.

-- Thomas Adam

[1] Anyone who things writing OO Perl is a good thing for this; think
again. OO perl is just broken.

···

On 17/10/2007, Chad Perrin <perrin@apotheon.com> wrote:

On Wed, Oct 17, 2007 at 11:21:15AM +0900, Ben Bleything wrote:
> On Wed, Oct 17, 2007, Chad Perrin wrote:

11

>
> I'm not necessarily looking for ORM, though now that I think about it I
> can't help but wonder if every single programmatic interface between Ruby
> and a relational database might qualify in some manner as ORM (since
> "everything is an object" in Ruby). In the Perl world, I'd tend to use

But then, just because the language you're using means everything is
an object, does not mean to say that when you connect to a database
and start pulling stuff out of it that what you get is ORM. ORM is an
interface your application would define for itself, mapping to the
underlying tables/views/whatever in the database.

Ruby makes this easier in that you could just (at a crude level) map
an object you define to have properties, behaviour and scope of the
underlying tables, etc., in the database. Sometimes there is merit to
that -- and indeed, coming into this are things like patterns (the
factory pattern in particular is useful in ORM scenarios.)

Thus, my "in some manner" qualification. The comment about all
programmatic DB interfaces in Ruby being ORMs was really a throw-away,
not exactly meant to be taken in strictly literal fashion. Sorry if that
wasn't clear.

> DBI for all things database-y, and was wondering what the preferred
> approach would be in the Ruby world especially in the case of PostgreSQL.

Having written numerous libraries (well, OK, two) in both perl and
ruby, I can honestly say that whilst it's somewhat "easier" in perl
(you're not forced to go down the abstracted route of ORM design) you
lose a lot of functionality -- the scope of what you can write in Perl
diminishes since the interface subroutines rarely map in an ORM way
[1]. Look into it heavily with Ruby -- it will make life easier.

Thanks for the advice. I shall (look into it heavily).

[1] Anyone who things writing OO Perl is a good thing for this; think
again. OO perl is just broken.

I certainly don't disagree much. It's not any more broken, really, than
OOP in languages like C++, but they're pretty broken in that regard too.
Just my opinion, of course. In general, I love Perl -- but in terms of
its OO constructs, I do not love it.

···

On Thu, Oct 18, 2007 at 02:37:39AM +0900, Thomas Adam wrote:

On 17/10/2007, Chad Perrin <perrin@apotheon.com> wrote:

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
Patrick J. LoPresti: "Emacs has been replaced by a shell script which 1)
Generates a syslog message at level LOG_EMERG; 2) reduces the user's disk
quota by 100K; and 3) RUNS ED!!!!!!"

12

I'm having a difficult time finding anything about Og itself. I find a
lot of stuff in Google that seems to be related to it, but only leads me
to Nitro with little or no mention of Og. What am I missing?

It should be noted that I'm not writing a web app at this time, so if Og
is somehow specific to Nitro, it's probably not what I need.

···

On Thu, Oct 18, 2007 at 12:39:33AM +0900, Chad Perrin wrote:

On Wed, Oct 17, 2007 at 08:22:27AM +0900, Reid Thompson wrote:
>
> require 'og'

Thanks. I'll look into it.

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
McCloctnick the Lucid: "The first rule of magic is simple. Don't waste your
time waving your hands and hopping when a rock or a club will do."

13

> > require 'og'

Thanks. I'll look into it.

I'm having a difficult time finding anything about Og itself. I find a
lot of stuff in Google that seems to be related to it, but only leads me
to Nitro with little or no mention of Og. What am I missing?

It should be noted that I'm not writing a web app at this time, so if Og
is somehow specific to Nitro, it's probably not what I need.

Og isn't tied to Nitro. Although I do use Og with Nitro on some
projects, I also use Og independently on others.

I believe `gem install og` should be sufficient.

In my own exprience Og is a nice enough piece of software that it's worth looking into despite the scarcity of documentation.

The tutorial.txt provided with the Og installation covers the
basics. (Although out-of-date now on a couple points.) There are
also a couple more detailed tutorials on: http://oxywtf.de/

Note: If you're running an older version of Postgres, e.g.
7.4.x, the og-0.41.0 gem is broken for 7.4 versions of Postgres,
but can be patched easily:

···

From: "Chad Perrin" <perrin@apotheon.com>

On Thu, Oct 18, 2007 at 12:39:33AM +0900, Chad Perrin wrote:

On Wed, Oct 17, 2007 at 08:22:27AM +0900, Reid Thompson wrote:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--- og-0.41.0/lib/og/store/sql.rb.orig 2006-12-27 04:59:23.000000000 -0800
+++ og-0.41.0/lib/og/store/sql.rb 2007-03-19 00:15:42.000000000 -0700
@@ -350,7 +350,7 @@
     if table_type = @options[:table_type]
       sql << ") TYPE = #{table_type};"
     else
- sql << ")"
+ sql << ") WITHOUT OIDS;" # %%BWK HACK for postgres 7.4 (added WITHOUT OIDS)
     end

     # Create indices.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Alternately you could use darcs to pull the latest sources, which
contain the real patch, which isn't as hacky as my one-liner above.

Note: The Nitro/Og team is on the verge of releasing a 0.50 gem,
Real Soon Now.

Regards,

Bill

14

Thanks. That'll probably help me in my decision about what to use.

···

On Thu, Oct 18, 2007 at 04:39:31AM +0900, Bill Kelly wrote:

The tutorial.txt provided with the Og installation covers the
basics. (Although out-of-date now on a couple points.) There are
also a couple more detailed tutorials on: http://oxywtf.de/

--
CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ]
awj @reddit: "The terms never and always are never always true."