Extralite

For complex queries involving JOINs, use Extralite::Transform to map flat result rows into nested, structured Ruby objects. This allows you to use an identity map to eliminate duplicate entities (e.g., when a post has multiple tags) and represent one-to-many or many-to-many relationships naturally.

transform = Extralite::Transform do
  {
    id:       integer.identity, # uses identity map to prevent duplicates
    content:  text,
    tags:     [{ # nested relationship
      id:     integer.identity,
      name:   text
    }]
  }
end

db.query(transform, sql)

A single Extralite::Database instance can be used in multiple threads simultaneously if and only if:

1. No explicit transactions are used.
2. Each thread issues queries via Database#query_xxx or uses its own Query instance.
3. The gvl_release_threshold is not set to 0 (ensuring the GVL is released periodically).

Extralite provides different methods to retrieve data in various formats depending on your needs. All #query_xxx methods return an array of rows (for multiple rows), while #query_single_xxx methods return a single row/value.

Available Modes

• :hash (Default): Returns each row as a hash mapping column names (symbols) to values. Use #query or #query_single.
• :array: Returns each row as an array. Use #query_array or #query_single_array.
• :splat: Returns values without a container (unwrapped). Use #query_splat or #query_single_splat.

# Hash mode (default)
db.query('select 1') #=> [{ "1" => 1 }]

# Array mode
db.query_array('select 1') #=> [[1]]

# Splat mode
db.query_splat('select 1') #=> [1]

# Single row variants
db.query_single('select 1') #=> { "1" => 1 }
db.query_single_array('select 1') #=> [1]
db.query_single_splat('select 1') #=> 1

In multi-fibered applications (using gems like Async or Polyphony), use the progress handler to allow the fiber scheduler to switch between fibers while a query is running.

• For general fiber schedulers (like Async): call sleep(0) in the progress handler.
• For Polyphony: call snooze. If the app is also multi-threaded, also call Thread.pass.

# General fiber scheduler solution
db.on_progress { sleep(0) }

# Polyphony solution
db.on_progress do
  snooze
  Thread.pass
end

The :splat mode is highly efficient for retrieving single columns or when iterating with a block. It avoids the overhead of allocating arrays or hashes for every row, reducing Ruby GC pressure.

Unwrapping single columns

Instead of mapping an array of arrays, use query_splat to get a flat array of values:

# Using array mode (requires manual unwrapping)
ids = db.query_array('select id from tasks where active = 1').map { |r| r.first }

# Using splat mode (direct)
ids = db.query_splat('select id from tasks where active = 1')

Iterating with splatted arguments

When providing a block to #query_splat, the column values are passed as individual arguments to the block:

db.query_splat('select a, b, c from foo') do |a, b, c|
  do_this_with(a, b)
  do_that_with(c)
end

If you do not have the sqlite3 library installed on your system, want to avoid using the system-installed version, or want access to the latest SQLite features (e.g., version 3.53.3), install the extralite-bundle gem. This gem bundles the SQLite source code directly.

Usage is identical to the standard extralite gem; simply use require 'extralite' to load it.

Prepared queries (prepared statements) maximize performance for repeated queries and allow for parameterized SQL to prevent injection. Use #prepare to create a query object, then #bind to provide parameters.

# Create a parameterized query
query = db.prepare('select * from foo where x = ?')

# Bind and execute
query.bind(1).to_a

# Or bind during preparation
query = db.prepare('select * from foo where x = ?', 1)

You can install Extralite using Bundler or by installing the gem manually. Extralite supports Ruby 3.0 and newer.

Using Bundler

Add this to your Gemfile:

gem 'extralite'

Manual Installation

$ gem install extralite

Extralite provides a Sequel adapter. To use it, ensure extralite is in your Gemfile and use the extralite:// URI scheme when connecting.

require 'sequel'
require 'extralite'

DB = Sequel.connect('extralite://blog.db')
articles = DB[:articles]
p articles.to_a