simple-sql

The simple-sql gem defines a module Simple::SQL, which you can use to execute SQL statements on a Postgresql database. Care has been taken to provide the simplest interface we could come up with.

However, apart from providing a simple interface simple-sql also provides a huge performance boon over ActiveRecord when interacting with your database, by cutting out all the ActiveRecord layers between the pg gem and you ruby code. However, simple-sql is still using ActiveRecord to manage connections.

Note: Databases other than Postgresql are not supported, and there are no plans to do so.

The gem is available on rubygems as simple-sql.

Also make sure to add the pg gem to your Gemfile. simple-sql should be pretty flexible about the pg version: the earliest supported version is 0.21.

To use it add the following lines to your Gemfile and bundle up usual:

gem 'simple-sql' # + version
gem 'pg' # + version

Before you can send SQL commands to a database you need to connect first. simple-sql gives you the following options:

In a standalone applications you need to connect to a Postgresql server. simple-sql uses ActiveRecord's connection handling.

You can explicitly connect to a server by calling

db = ::Simple::SQL.connect! "postgres://user:password@server[:port]/database"

You can then use db.ask, db.all, etc. (See below)

Alternatively, you can have simple-sql figure out the details automatically:

db = ::Simple::SQL.connect!

In that case we try to find connection parameters in the following places:

• the DATABASE_URL environment value
• the config/database.yml file from the current directory, taking RAILS_ENV/RACK_ENV into account.

You don't need to call ::Simple::SQL.connect! inside a Rails application. simple-sql automatically uses the default connection when using Simple::SQL instead of an explicit database handle; i.e. typically you can use

Simple::SQL.ask "SELECT ..."

and simple-sql will be doing the right thing.

This is especially true in a Rails' applications controller action, because it allows you to mix Simple::SQL and ActiveRecord interactions with the database.

Simple::SQL.all runs a query, with optional arguments, and returns the result. Usage example:

# returns an array of arrays `[ <id>, <email> ]`.
Simple::SQL.all "SELECT id, email FROM users WHERE id = ANY($1)", [1,2,3]

If a block is passed to SQL.all, each row is yielded into the block:

Simple::SQL.all "SELECT id, email FROM users" do |id, email|
  # do something
end

Typically the SQL query returns an array of arrays (1 per row). If, however, the query is set up to only return a single column, this method returns an array of these values instead. This is especially useful with the block version:

Examples:

Simple::SQL.all("SELECT id FROM users")        # returns an array of id values, but

Simple::SQL.all "SELECT id FROM users" do |id|
  # do something with the users' ids.
end

This is identical to .all. It might offer beneficial optimisations, but this is theoretical at this point.

Simple::SQL.each "SELECT id, email FROM users" do |id, email|
  # do something
end

Simple::SQL.print "SELECT id, email FROM users" do |id, email|
  # do something
end

Simple::SQL.ask runs a query, with optional arguments, and returns the first result row or nil, if there was no result.

Simple::SQL.ask "SELECT id, email FROM users WHERE id = ANY($1) LIMIT 1", [1,2,3]

If the SQL query only returns a single column, this method returns the column value of the first row; otherwise it returns an array (or nil if there was no result).

Usually all attributes are converted to its corresponding ruby type.

Examples:

Simple::SQL.ask "SELECT id FROM users WHERE email=$1", "foo@local"         # returns a number (or `nil`) and
Simple::SQL.ask "SELECT id, email FROM users WHERE email=$?", "foo@local"  # returns an array `[ <id>, <email> ]` (or `nil`)

Note: whenever you run a query simple-sql takes care of sending query parameters over the wire properly. That means that you use placeholders $1, $2, etc. to use these inside your queries; the following is a correct example:

Simple::SQL.all "SELECT * FROM users WHERE email=$1", "foo@bar.local"

Usually arguments are converted correctly when sending over to the database. One notable exception is sending jsonb data - you must use JSON.encode on the argument:

Simple::SQL.ask "INSERT INTO table (column) VALUES($1)", JSON.encode(..)

It is probably worth pointing out that you cannotto use an array as the argument for the IN(?) SQL construct. Instead you want to use ANY, for example:

Simple::SQL.all "SELECT * FROM users WHERE id = ANY($1)", [1,2,3]

By default ask and all convert each result row into an Array. Sometimes you might want to use Hashes or similar objects instead. To do so, you use the into: keyword argument:

# returns a single Hash (or nil)
Simple::SQL.ask("SELECT id FROM users", into: Hash) 

If you want the returned record to be in a structure which is not a Hash, you can use the into: <klass> option. The following would return an array of up to two OpenStruct objects:

sql = "SELECT id, email FROM users WHERE id = ANY($1) LIMIT 1", 
Simple::SQL.all sql, [1,2,3], into: OpenStruct

This supports all target types that take a constructor accepting Hash arguments.

It also supports a :struct argument, in which case simple-sql creates uses a Struct-class. Struct classes are reused when possible, and are maintained by Simple::SQL. This is potentially the best performing option when you want to use dot-notation.

sql = "SELECT id, email FROM users WHERE id = ANY($1) LIMIT 1", 
Simple::SQL.all sql, [1,2,3], into: :struct

You can use all and ask with other queries as well. However, both only support running a single query. If you want to run multiple queries (i.e. a SQL script) you would probably look into Simple::SQL.exec instead.

Be aware that Simple::SQL.exec does not support placeholders: you cannot pass in arguments into Simple::SQL.exec.

simple-sql borrows transaction support from ActiveRecord.

A scope lets you build a condition over time, like this:

scope = db.scope "SELECT * FROM users"
scope = scope.where(email: ["foo@foobar.test", "bar@foobar.test"])
scope = scope.where("deleted_at is NULL")
users = scope.all

This also works with the default connection, via scope = Simple::SQL.scope ....

A scope supports the following methods to set up a scope:

These methods can then be used to evaluate the scope:

Inserting objects is much faster via simple-sql. You should be able to insert ~1000 or so records per second into a table with no trouble.

Simple::SQL.insert :users, first_name: "foo", last_name: "bar"

users = []
users.push first_name: "first", last_name: "user"
users.push first_name: "second", last_name: "user"

Simple::SQL.insert :users, users

The .insert method lets you set up conflict resolution, via

Simple::SQL.insert :users, users, on_conflict: :ignore

Simple::SQL.transaction do
  Simple::SQL.lock!(4711)

  # do something.
end

simple-sql builds a logger which logs all queries. The logger, by default, is created to write to STDERR; to get another logger use code like

Simple::SQL.logger = Rails.logger

Limited support for types: This gem does not use pg's support for encoding and decoding types, since that might probably interfere with how ActiveRecord is setting up the pg gem.

It therefore assumes ActiveRecord is used in the same project, which sets up pg to not decode data in any meaningful way, and provides some code to decode the data returned from the database. Only a handful of types is currently supported by the Decoder - it is fairly easy to add new types, though.

1. createdb simple-sql-test
2. bundle install
3. bin/rspec