Lesson Weekend

Now that we know the basics of SQL, we can use the pg gem to connect a Postgres server to our Ruby programs. We can install the gem with the following command:

$ gem install pg

Before we use the pg gem, we need to create a database. Let's open psql and create a database called address_book with a table called contacts that has a column for a primary key and a varchar column called name.

Now we can connect to the database via our interactive Ruby terminal (irb) and execute SQL commands:

irb(main):001:0> require 'pg'
irb(main):002:0> address_book_db = PG.connect({:dbname => 'address_book'})
irb(main):003:0> address_book_db.exec("INSERT INTO contacts (name) VALUES ('ruby');")

Note: If you are on a Windows machine, attempting to connect to your database like this can throw an error, in the form fe_sendauth: no password supplied (PG::ConnectionBad). In this case, you would just need to add the password to your connection hash, like so:

irb(main):001:0> require 'pg'
irb(main):002:0> address_book_db = PG.connect({:dbname => 'address_book', :password => 'epicodus'})
irb(main):003:0> address_book_db.exec("INSERT INTO contacts (name) VALUES ('ruby');")

In an upcoming lesson, we'll learn how to protect passwords in our Sinatra app with a Ruby gem. For now, enter your password manually in IRB.

To access the database, we pass the SQL command as an argument to exec method, which takes our PG database object as a receiver.

Now let's run a query:

irb(main):004:0> results = address_book_db.exec("SELECT * FROM contacts;")
 => #<PG::Result:0x007faa83151f78>

What is this PG::Result object?

> results.each { |result| p result }
> results.each { |result| p result.class }

Note that we're using inline syntax instead of using do and end in the line above. If our code is a single line, we can use { to represent do and } to represent the end. This is yet another way Ruby is flexible.

Our results are a collection of hashes. The keys are the column names and the values are the column values.

Since the exec method just executes a SQL statement, we could have actually created the table with it. However, creating and modifying tables are one-off tasks. We create the database schema once and then the application can just assume it exists. Since we'll be using the pg gem in our application code, which will assume that the database and schema already exist, we'll continue to create and modify tables (and databases themselves) in psql.

Here are a few things to be aware of when working with the pg gem:

  • All of the values returned from PG::Results objects will be strings. It is up to you to convert them into the proper Ruby class.
  • Booleans are represented by 't' and 'f'.
  • The SQL equivalent of Ruby's nil is NULL. If you are trying to perform an insert into the database and need to leave a value out, you should pass in NULL, not nil.

Connect to a database:

$ some_db = PG.connect({:dbname => 'some_db'})

Run SQL:

$ some_db.exec("INSERT INTO contacts (name) VALUES ('michael');")

The results are a collection of hashes:

results = address_book_db.exec("SELECT * FROM contacts;")
results.each { |result| p result }
results.each { |result| p result.class } #each result is a hash

Lesson 4 of 29
Last updated August 7, 2022