crystal-pg

A Postgres driver for Crystal

usage

shards

Add this to your shard.yml and run crystal deps

dependencies:
  pg:
    github: will/crystal-pg

connecting

require "pg"
DB = PG.connect("postgres://...")

typed querying

The preferred way to send queries is to send a tuple of the types you expect back along with the query. #rows will then be an array of tuples with each element properly casted. You can also use parameterized queries for injection-safe server-side interpolation.

result = DB.exec({Int32, String}, "select id, email from users")
result.fields  #=> [PG::Result::Field, PG::Result::Field]
result.rows    #=> [{1, "will@example.com"}], …]
result.to_hash #=> [{"field1" => value, …}, …]

result = DB.exec({String}, "select $1::text || ' ' || $2::text", ["hello", "world"])
result.rows #=> [{"hello world"}]

Out of the box, crystal-pg supports 1-32 types. If you need more, you can reopen PG::Result and use the generate_gather_rows macro. If your field can return nil, you should use Int32|Nil for example, which is a union of the type and Nil.

untyped querying

If you do not know the types beforehand you can omit them. However you will get back an array of arrays of PGValue. Since it is a union type of amost every type, you will probably have to manually cast later on in your program.

result = DB.exec("select * from table")
result.fields  #=> [PG::Result::Field, …]
result.rows    #=> [[value, …], …]
result.to_hash #=> [{"field1" => value, …}, …]

result = DB.exec("select $1::text || ' ' || $2::text", ["hello", "world"])
result.rows #=> [["hello world"]]

Listen/Notify

There are two ways to listen for notifications. For docs on NOTIFY, please read https://www.postgresql.org/docs/current/static/sql-notify.html.

1. Any connection can be given a callback to run on notifications. However they are only received when other traffic is going on.
2. A special listen-only connection can be established for instant notification processing with PG.connect_listen.

# see full example in examples/listen_notify.cr
PG.connect_listen("postgres:///", "a", "b") do |n| # connect and  listen on "a" and "b"
  puts "    got: #{n.payload} on #{n.channel}"     # print notifications as they come in
end

Arrays

Crystal-pg supports several popular array types. If you only need a 1 dimensional array, you can cast down to the appropriate Crystal type:

DB.exec({Array(Int32?)},
  "select ARRAY[1, null, 3]"
).rows.first # => {[1, nil, 3]}

DB.exec({Array(String)},
  "select '{hello, world}'::text[]"
).rows.first # => {["hello", "world"]}

Requirements

Crystal-pg is tested on Postgres versions 9.1 through 9.4 and developed on 9.5 (travis does not currently have 9.5 support). Since it uses protocal version 3, older versions probably also work but are not guaranteed.

Linking requires that the pg_config binary is in your $PATH and returns correct results for pg_config --includedir and pg_config --libdir.

Supported Datatypes

• text
• boolean
• int8, int4, int2
• float4, float8
• timestamptz, date, timestamp (but no one should use ts when tstz exists!)
• json and jsonb
• uuid
• bytea
• numeric/decimal (1)
• varchar
• regtype
• geo types: point, box, path, lseg, polygon, circle, line
• array types: int8, int4, int2, float8, float4, bool, text

1: A note on numeric: In postgres this type has arbitrary percision. In this driver, it is represented as a PG::Numeric which retians all precision, but if you need to do any math on it, you will probably need to cast it to a float first. If you need true abitrary percision, you can optionally require pg_ext/big_rational which adds #to_big_r, but requires that you have LibGMP installed.

Connection Pooling

If you would like a connection pool, check out ysbaddaden/pool