A lightweight PostgreSQL ORM using JSONB. Provides an API for retreiving documents by arbitrary key, and performing queries on arbitrary keys and sub-keys.

pgstore.rb

require 'pg'
PGDB = PG.connect(host: '/tmp', dbname: 'mydb')
PGDB.type_map_for_results = PG::BasicTypeMapForResults.new(PGDB)

class Hash
  def symbolize_keys
    inject({}) { |m, kv| v = kv[1];
      m[kv[0].to_sym] = v.is_a?(Hash) ? v.symbolize_keys : v; m }
  end
end

# Implements an interface for accessing and manipulating JSONB documents in 
# PostgreSQL tables. The tables are expected to contain a single JSONB field
# named `attributes`.
class PGStore
  ATTRIBUTES = 'attributes'

  class << self
    def create_table(table)
      PGDB.query("create table if not exists #{table} (attributes jsonb)")
    end

    def query(table, where = nil, params = nil, &block)
      where = where_clause_from_hash(where) if where.is_a?(Hash)
      sql = "select attributes from #{table} #{where}"
      res = PGDB.query(sql, params)
      if block
        res.each {|r| block[r[ATTRIBUTES].symbolize_keys]}
      else
        res.map {|r| r[ATTRIBUTES].symbolize_keys}
      end
    end

    def count(table, where = nil, params = nil)
      where = where_clause_from_hash(where) if where.is_a?(Hash)
      sql = "select count(*) as count from #{table} #{where}"
      PGDB.query(sql, params).first[:count]
    end

    def get(table, where)
      where = where_clause_from_hash(where) if where.is_a?(Hash)
      sql = "select attributes from #{table} #{where} limit 1"
      row = PGDB.query(sql).first
      row && row[ATTRIBUTES].symbolize_keys
    end

    def set(table, where, attributes)
      where = where_clause_from_hash(where) if where.is_a?(Hash)
      sql = "update #{table} set attributes = $1 #{where}"
      res = PGDB.query(sql, [attributes.to_json])
      res.cmd_tuples == 1
    end

    def insert(table, attributes)
      sql = "insert into #{table} (attributes) values ($1)"
      res = PGDB.query(sql, [attributes.to_json])
      res.cmd_tuples == 1
    end

    def delete(table, where = nil, params = nil)
      where = where_clause_from_hash(where) if where.is_a?(Hash)
      res = PGDB.query("delete from #{table} #{where}", params)
      res.cmd_tuples
    end

    def where_clause_from_hash(hash)
      "where %s" % [
        hash.map {|k, v| "#{format_kv_cond(k, v)}"}.join(" and ")
      ]
    end

    CAST_OPERATOR = {
      Integer => '::int',
      Float => '::float',
      TrueClass => '::bool',
      FalseClass => '::bool'
    }

    COND_OPERATOR = {
      Regexp => '~',
      Array => 'in'
    }
    COND_OPERATOR.default = '='

    def format_kv_cond(k, v)
      if v.is_a?(Hash)
        return v.map do |hk, hv|
          format_kv_cond({k => hk}, hv)
        end.join(" and ")
      end

      cast_class = v.is_a?(Array) ? v.first.class : v.class

      cond_class = v.class

      "((attributes%s)%s %s %s)" % [
        attribute_ref(k),
        CAST_OPERATOR[cast_class],
        COND_OPERATOR[cond_class],
        literal(v)
      ]
    end

    def attribute_ref(k)
      if k.is_a?(Hash)
        hk, hv = k.keys.first, k.values.first
        "->%s%s" % [
          PGDB.escape_literal(hk.to_s),
          attribute_ref(hv)
        ]
      else
        "->>%s" % [PGDB.escape_literal(k.to_s)]
      end
    end

    def literal(v)
      case v
      when Array
        "(#{v.map {|o| literal(o)}.join(',')})"
      when String
        PGDB.escape_literal(v)
      when Symbol
        PGDB.escape_literal(v.to_s)
      when Time
        PGDB.escape_literal(v.iso8601)
      when nil
        "null"
      when Integer, Float, true, false
        v.to_s
      when Regexp
        PGDB.escape_literal(v.source)
      else
        raise "Can't literalize #{v.inspect}"
      end
    end
  end
end

PGStore.create_table(:my_table)
PGStore.delete(:my_table)

PGStore.insert(:my_table, key: 'first', a: 1, b: 2)
PGStore.insert(:my_table, key: 'second', a: 3, b: 4)
PGStore.insert(:my_table, key: 'third', a: 5, b: 6)

puts "* get first"
p PGStore.get(:my_table, key: 'first')

puts "* get those with b in (2, 4)"
PGStore.query(:my_table, b: [2, 4]) do |o|
  p o
end