Database abstraction/adapters for ruby

Question

What are the database abstractions/adapters you are using in Ruby? I am mainly interested in data oriented features, not in those with object mapping (like active record or data mapper).

I am currently using Sequel. Are there any other options?

I am mostly interested in:

• simple, clean and non-ambiguous API
• data selection (obviously), filtering and aggregation
• raw value selection without field mapping: SELECT col1, col2, col3 => [val1, val2, val3] not hash of { :col1 => val1 ...}
• be able to pass list of columns/values to be selected: select(array_of_columns) (not: dataset.select(:col1, :col2, :col3) which requires that columns are known)
• API takes into account table schemas 'some_schema.some_table' in a consistent (and working) way; also reflection for this (get schema from table)
• database reflection: get list of table columns, their database storage types and perhaps adaptor's abstracted types
• table creation, deletion
• be able to work with other tables (insert, update) in a loop enumerating selection from another table without requiring to fetch all records from table being enumerated

Purpose is to manipulate data with unknown structure at the time of writing code, which is the opposite to object mapping where structure or most of the structure is usually well known. I do not need the object mapping overhead.

What are the options, including back-ends for object-mapping libraries?

Answer 1

I'm the lead developer of Sequel, so this response is obviously biased, but I'm not aware of any ruby database library that does everything you want.

It appears that some of your desires are perceived limitations in Sequel, some of which can be addressed:

• raw value selection without field mapping: SELECT col1, col2, col3 => [val1, val2, val3] not hash of { :col1 => val1 ...}

Try:

DB[:table].filter([:col1, :col2, :col3].zip([1, 2, 3]))
# SELECT * FROM table WHERE ((col1 = 1) AND (col2 = 2) AND (col3 = 3))

It's trivial to add a new dataset method that provides a nicer API for the above:

DB[:table].bfilter([:col1, :col2, :col3], [1, 2, 3])

• be able to pass list of columns/values to be selected: select(array_of_columns) (not: dataset.select(:col1, :col2, :col3) which requires that columns are known)

Try:

array_of_columns = [:col1, :col2, :col3]
DB[:table].select(*array_of_columns)
# SELECT col1, col2, col3 FROM table

• API takes into account table schemas 'some_schema.some_table' in a consistent (and working) way; also reflection for this (get schema from table)

Sequel deals with table schemas:

DB[:schema__table]
DB[:table.qualify(:schema)]
# SELECT * FROM schema.table

Any place where this doesn't work is generally considered a bug. I'm not sure what you mean by reflection. The same table name can be used in multiple schemas, so which schema a table is in is generally an ambiguous question.

• database reflection: get list of table columns, their database storage types and perhaps adaptor's abstracted types

Columns as array of symbols:

DB[:table].columns
# => [:col1, :col2, :col3]

Schema information:

DB.schema(:table)
# [[:col1=>{:type=>:integer, :db_type=>'int(11)', :default=>nil, ...}], ...]

:type is the ruby type symbol, :db_type is the database type string.

• be able to work with other tables (insert, update) in a loop enumerating selection from another table without requiring to fetch all records from table being enumerated

I assume you are asking for something like this:

DB[:table].each do |row|
  DB[:other_table].insert(:blah=>row[:blah])
end

Which doesn't work correctly on some adapters in Sequel due to the connection pool using the same database connection for the insert on :other_table while it is still being used for the :select on table. You can work around this by using the sharding support:

DB = Sequel.connect(..., :servers=>{:read_only=>{}})
DB[:table].each do |row|
  DB[:other_table].insert(:blah=>row[:blah])
end

In this case, DB will use the :read_only shard for the select on :table and the :default shard for the insert on :other_table. You can also specify shards explicitly:

DB[:table].server(:read_only).each do |row|
  DB[:other_table].server(:default).insert(:blah=>row[:blah])
end

The points I didn't address I assume you know that Sequel already handles. And it's certainly possible that Sequel doesn't meet all of your needs, though in that case I doubt any other ruby database library will.