Ruby pg (Postgres) library notes

pg.txt

# gem 'pg'
# https://github.com/ged/ruby-pg
# http://deveiate.org/code/pg
#
# I always find it a nightmare to look up how it works because
# it's not intuitive at all. So here are some notes.

# CONNECTION
# ==========
# No connection URL support, so..
uri = URI.parse(ENV['DATABASE_URL'])
DB = PG.connect(uri.hostname, uri.port, nil, nil, uri.path[1..-1], uri.user, uri.password)

DB.reset     => disconnects and reconnects

# QUERIES AND RESULTS
# ===================
# More at https://deveiate.org/code/pg/PG/Connection.html

DB.send_query('UPDATE x SET y = 1 WHERE z = 2')              # async
DB.send_query_params('UPDATE x SET y = 1 WHERE z = $1', [2]) # async
res = DB.get_result

res = DB.exec('SELECT * FROM x WHERE id = 1')                # sync with response
res = DB.exec_params('SELECT * FROM x WHERE id = $1', [1])   # sync with response

# PG::Result
#   res.ntuples         => number of rows returned
#   res.nfields         => number of columns returned
#   res.values          => array of row arrays
#   res.each { |row|  } => `row` is a hash of { column => value, ... }
#   res[0]              => hash of the first row
#   res[n]              => hash of the n-1th row (IndexError raised if out of range)
#   res.first           => hash of the first row (or nil if no rows - no IndexError raised)

# Exceptions that may come back from exec:
#   PG::UndefinedColumn
#   PG::SyntaxError