Swift PostgreSQL adapter

MRI adapter for PostgreSQL

Features

  • Lightweight & fast
  • Result typecasting
  • Prepared statements
  • Asynchronous support using PQsendQuery family of functions
  • Nested transactions

API

  Swift::DB::Postgres
    .new(options)
    #execute(sql, *bind)
    #prepare(sql)
    #begin(savepoint = nil)
    #commit(savepoint = nil)
    #rollback(savepoint = nil)
    #transaction(savepoint = nil, &block)
    #native_bind_format(&block)
    #native_bind_format=(value)
    #ping
    #close
    #closed?
    #escape(text)
    #query(sql, *bind)
    #fileno
    #result
    #write(table = nil, fields = nil, io_or_string)
    #read(table = nil, fields = nil, io = nil, &block)

  Swift::DB::Postgres::Statement
    .new(Swift::DB::Postgres, sql)
    #execute(*bind)
    #release

  Swift::DB::Postgres::Result
    #selected_rows
    #affected_rows
    #fields
    #types
    #each
    #insert_id

Bind parameters and hstore operators

Swift::DB::Postgres uses '?' as a bind parameter and replaces them with the '$' equivalents. This causes issues when you try to use the HStore '?' operator. You can permanently or temporarily disable the replacement strategy as below:


db.native_bind_format = true
db.execute("select * from users where tags ? $1", 'mayor')
db.native_bind_format = false

db.native_bind_format do
  db.execute("select * from users where tags ? $1", 'mayor')
end

Example

require 'swift/db/postgres'

db = Swift::DB::Postgres.new(db: 'swift_test')

db.execute('drop table if exists users')
db.execute('create table users (id serial, name text, age integer, created_at timestamp)')
db.execute('insert into users(name, age, created_at) values(?, ?, ?)', 'test', 30, Time.now.utc)

row = db.execute('select * from users').first
p row #=> {:id => 1, :name => 'test', :age => 30, :created_at=> #<Swift::DateTime>}

Asynchronous

Hint: You can use Adapter#fileno and EventMachine.watch if you need to use this with EventMachine.

require 'swift/db/postgres'

rows = []
pool = 3.times.map {Swift::DB::Postgres.new(db: 'swift_test')}

3.times do |n|
  Thread.new do
    pool[n].query("select pg_sleep(#{(3 - n) / 10.0}), #{n + 1} as query_id") do |row|
      rows << row[:query_id]
    end
  end
end

Thread.list.reject {|thread| Thread.current == thread}.each(&:join)
rows #=> [3, 2, 1]

Data I/O

The adapter supports data read and write via COPY command.

require 'swift/db/postgres'

db = Swift::DB::Postgres.new(db: 'swift_test')
db.execute('drop table if exists users')
db.execute('create table users (id serial, name text)')

db.write('users', %w{name}, "foo\nbar\nbaz\n")
db.write('users', %w{name}, StringIO.new("foo\nbar\nbaz\n"))
db.write('users', %w{name}, File.open("users.dat"))


db.read('users', %w{name}) do |data|
  p data
end

csv = File.open('users.csv', 'w')
db.execute('copy users to stdout with csv')
db.read(csv)

Performance

Don't read too much into it. Each library has its advantages and disadvantages.

  • insert 1000 rows and read them back 100 times with typecast enabled
  • pg uses the pg_typecast extension
$ ruby -v

ruby 1.9.3p125 (2012-02-16 revision 34643) [x86_64-linux]

$ ruby check.rb
                          user     system      total        real
do_postgres insert     0.190000   0.080000   0.270000 (  0.587877)
do_postgres select     1.440000   0.020000   1.460000 (  2.081172)

pg insert              0.100000   0.030000   0.130000 (  0.395280)
pg select              0.630000   0.220000   0.850000 (  1.284905)

swift insert           0.070000   0.040000   0.110000 (  0.348211)
swift select           0.640000   0.030000   0.670000 (  1.111561)

License

MIT