How to connect to and use Postgresql from Ruby

Ruby
1

Hello everyone.

I have just started to learn Ruby, quite nice language, but I’m stuck on
connecting to the database (Postgresql)

I have installed some ruby-pg and Ruby/DBI package for my Ubuntu system,
but then what?

I’m having trouble with finding the correct/best documentation on how to
connect and how to perform queries to the database.
Can someone point me in the right dirrection here?
Simple example code could perhaps also be of value to get me started.

Thank you all!

2

Rannug Pong wrote in post #1014012:

I have just started to learn Ruby, quite nice language, but I’m stuck on
connecting to the database (Postgresql)

I have installed some ruby-pg and Ruby/DBI package for my Ubuntu system,
but then what?

You did google for “ruby DBI”? There’s a good introductory article on
kitebird.

However, DBI is a pretty old layer and not well maintained. I think that
most people would either:

(1) use a higher-level abstraction layer such as ActiveRecord,
Datamapper or Sequel. All three of those have good documentation, and
will happily sit on top of a postgres database; or

(2) use the ruby-pg layer directly (look at its documentation for
examples).

Option (2) arguably gives the best performance, but that’s irrelevant
because the time for the database to perform a SQL query will far
outweigh the time to send the request. Option (1) gives you much easier
ways to interact with the database, and also makes it easier for your
code to be database-independent.

On an Ubuntu system, my personal inclination is to ignore the
Ubuntu-provided packages and instead install everything as gems. That
gives you much more control and the ability to install more recent
packages (or specific versions of packages).

HTH,

Brian.

3

On Jul 31, 2011, at 10:27 AM, Rannug Pong wrote:

Can someone point me in the right dirrection here?
Simple example code could perhaps also be of value to get me started.

Thank you all!

–
Posted via http://www.ruby-forum.com/.

  1. Install Postgres
  2. Did a search for ruby postgres and found this:

ruby-pg is now the official rubyforge project for the “postgres” ruby
gem. See the project here:

http://www.rubyforge.org/projects/ruby-pg

  1. Go to the page in question, note that the project has been moved
  2. Go to http://bitbucket.org/ged/ruby-pg/
  3. Read the install instructions, note the Ubuntu specific mention about
    the required libpq-dev package
  4. sudo gem install pg (or no sudo if you want to do a home install)
  5. Notice here’s an examples directory:

https://bitbucket.org/ged/ruby-pg/src/3d744d9776c3/sample/test1.rb

  1. Start the postgres server
  2. Switched to the postgres user (since postgres uses unix permissions
    by default)
  3. Ran the code and got errors, realized it’s because the sample code
    has not been updated to the latest version of the API
  4. Screw this we’re doing it live
  5. Fix the code and put it on a gist (that’s obviously not my password,
    and you might not need a password):
  1. Run the code again and get the following output:

datname datdba encoding datcollate datctype
…
…
template1 10 6 C C
…
template0 10 6 C C
…
postgres 10 6 C C
…

  1. Make note to self to fork that repository and push a fix
  2. ???
  3. Profit!

Regards,
Chris W.
Twitter: http://www.twitter.com/cwgem

4

On Jul 31, 2011, at 11:54 AM, Chad P. wrote:

–
Chad P. [ original content licensed OWL: http://owl.apotheon.org ]

Performance, as ruby-pg interfaces directly with libpq. There are also
chances that postgres offers specific functionality only accessible
through a libpq interface. This comes down to the question of what the
specific use case is for interfacing with postgres.

Regards,
Chris W.
Twitter: http://www.twitter.com/cwgem

5

I would personally recommend Sequel, even for performance. Although
ruby-pg is
more low level, it is very hard to fine tune every transaction. Sequel
by
default does a lot of caching in memory and comes with builtin database
connection pool. These can save great effort reimplementing by yourself
and also
boost you application performance. Not to mention Sequel also has got a
memchached plugin already just in case you want it. Using Postgres with
Sequel,
you still have access to bind variable and prepared statement
functionality
coming from ruby-pg, which can be fine tuned to further improve
performance.

Aside from performance, using high level library like Sequel makes your
application more portable and more readable.

Regards,

Roy Z.

6

On Mon, Aug 01, 2011 at 04:32:24AM +0900, Roy Z. wrote:

performance.

Aside from performance, using high level library like Sequel makes your
application more portable and more readable.

That was much more articulate than the answer I was thinking about, but
essentially similar in principle. Thanks.

7

On Mon, Aug 01, 2011 at 03:30:25AM +0900, Chris W. wrote:

  1. Install Postgres
    . . .
  2. Make note to self to fork that repository and push a fix
  3. ???
  4. Profit!

Why not just use Sequel or DataMapper?