For the life of me I can't find a simple example of just running something like
"SELECT * FROM MyTable"
in Ruby. Everything I'm finding assumes an ORM or Rails. For now, I don't want ORM; I don't want Rails. I'm looking for something standalone that uses the pg gem and executes a simple query.
From the pg gem documentation (http://rubydoc.info/gems/pg/0.10.0/frames)
require 'pg'
conn = PGconn.open(:dbname => 'test')
res = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')
res.getvalue(0,0) # '1'
res[0]['b'] # '2'
res[0]['c'] # nil
My next question would be authentication with a DB that requires a password.
Looks like you can send a connection string like this:
PGconn.connect( "dbname=test password=mypass")
or use the constuctor with parameters:
PGconn.new(host, port, options, tty, dbname, login, password) or use a hash like :password => '...' see here for all available options.
Try this:
require 'pg'
conn = PGconn.connect("ip adddress", 5432, '', '', "db name", "user", "password")
res = conn.exec('select tablename, tableowner from pg_tables')
res.each do |row|
puts row['tablename'] + ' | ' + row['tableowner']
end
For the newer versions (e.g., 0.18.3 up to the current latest version 0.21.0) of gem pg , instead of using:
conn = PGconn.connect(*args)
(The PGconn, PGresult, and PGError constants are deprecated, and will be removed as of version 1.0.)
You should use conn = PG.connect(*args), for example:
require 'pg'
conn = PG.connect("IP-Address", 5432, '', '', "database-name", "username", "password")
res = conn.exec('select product_id, description, price from product')
res.each do |row|
puts row
end
Reference link: gem pg
I put together an example that will work if you have Docker, ruby, and the pg gem installed:
db=pgexample
docker rm -f $db &> /dev/null
docker run -itd --name $db -p 4242:5432 postgres:10
sleep 3 # wait for postgres to start
docker exec -u postgres $db psql -c "
CREATE TABLE things (
id serial PRIMARY KEY,
name varchar (50) NOT NULL
);
INSERT INTO things (name) VALUES ('foo');
INSERT INTO things (name) VALUES ('bar');
INSERT INTO things (name) VALUES ('baz');
"
docker exec -u postgres $db psql -c 'SELECT * FROM things;'
# id | name
# ----+------
# 1 | foo
# 2 | bar
# 3 | baz
# (3 rows)
ruby <<EOF
require 'pg'
conn = PG.connect(dbname: 'postgres', host: 'localhost', user: 'postgres', port: 4242)
q = ARGV.first || 'SELECT * FROM things;'
p conn.exec(q).to_a
EOF
# [{"id"=>"1", "name"=>"foo"}, {"id"=>"2", "name"=>"bar"}, {"id"=>"3", "name"=>"baz"}]
docker rm -f $db
In new versions you need to use PG::Connection instead of PGconn
It works like this:
require 'pg'
conn = PG::Connection.open(dbname: 'test')
res = conn.exec_params('SELECT $1 AS a, $2 AS b, $3 AS c', [1, 2, nil])
res = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')
See pg docs: https://deveiate.org/code/pg/PG/Connection.html
Assuming you're using the pg gem:
require 'pg'
pg_connection = PG::Connection.new(host: 'localhost', user: 'postgres', password: 'postgres')
# The examples below assume the following SQL schema:
# CREATE TABLE users (id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL, name varchar);
# INSERT INTO users (name) VALUES ('john'), ('bruce'), ('william'), ('sarah');
# Inserts new row and prints its ID
new_username = 'new'
pg_result = pg_connection.exec_params('INSERT INTO users (name) VALUES ($1) RETURNING id', [new_username])
puts "Inserted record's id: #{pg_result.getvalue(0, 0)}"
# Prints existing rows
pg_result = pg_connection.exec('SELECT * FROM users')
pg_result.each do |row|
puts "Fetched record details: id: #{row['id']}, name: #{row['name']}"
end
The code gives the following output:
Inserted record's id: 5
Fetched record details: id: 1, name: john
Fetched record details: id: 2, name: bruce
Fetched record details: id: 3, name: william
Fetched record details: id: 4, name: sarah
Fetched record details: id: 5, name: new
