Ruby: pg

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DATABASE_URL=postgres://app:${DB_PASSWORD}@old-postgres:5432/app
DATABASE_URL=postgres://app:${DB_PASSWORD}@machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Ruby 4.0
• pg 1.7.0

Run

bundle install
export DATABASE_URL=postgres://app:${DB_PASSWORD}@machsql-host:5432/app
bundle exec ruby main.rb

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.rb

# The pg gem (libpq) from Ruby.
require "pg"

conn = PG.connect(ENV.fetch("DATABASE_URL"))
begin
  conn.exec("CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)")
  id = conn.exec_params("INSERT INTO todo (title) VALUES ($1) RETURNING id", ["write the examples"]).getvalue(0, 0)
  open = conn.exec("SELECT count(*) FROM todo WHERE NOT done").getvalue(0, 0)
  puts "inserted todo #{id}, #{open} open"
ensure
  conn.close
end

Gemfile

source "https://rubygems.org"

gem "pg", "1.7.0"