Extralite

A fast Ruby gem for working with SQLite3 databases

DOCS | BLOG POST

What is Extralite?

Extralite is a fast, extra-lightweight (about 600 lines of C-code) SQLite3 wrapper for Ruby. It provides a minimal set of methods for interacting with an SQLite3 database, as well as prepared statements.

Extralite comes in two flavors: the extralite gem which uses the system-installed sqlite3 library, and the extralite-bundle gem which bundles the latest version of SQLite (3.38.0), offering access to the latest features and enhancements.

Features

• A variety of methods for different data access patterns: rows as hashes, rows as arrays, single row, single column, single value.
• Prepared statements.
• Use system-installed sqlite3, or the bundled latest version of SQLite3.
• Super fast - up to 12.5x faster than the sqlite3 gem (see also comparison.)
• Improved concurrency for multithreaded apps: the Ruby GVL is released while preparing SQL statements and while iterating over results.
• Iterate over records with a block, or collect records into an array.
• Parameter binding.
• Automatically execute SQL strings containing multiple semicolon-separated queries (handy for creating/modifying schemas).
• Get last insert rowid.
• Get number of rows changed by last query.
• Load extensions (loading of extensions is autmatically enabled. You can find some useful extensions here: https://github.com/nalgeon/sqlean.)
• Includes a Sequel adapter.

Installation

To use Extralite in your Ruby app, add the following to your Gemfile:

gem 'extralite'

You can also run gem install extralite if you just want to check it out.

Installing the Extralite-SQLite3 bundle

If you don't have sqlite3 installed on your system, do not want to use the system-installed version of SQLite3, or would like to use the latest version of SQLite3, you can install the extralite-bundle gem, which integrates the SQLite3 source code.

Important note: The extralite-bundle will take a while to install (on my modest machine it takes about a minute), due to the size of the sqlite3 code.

Usage of the extralite-bundle gem is identical to the usage of the normal extralite gem.

Usage

require 'extralite'

# get sqlite3 version
Extralite.sqlite3_version #=> "3.35.2"

# open a database
db = Extralite::Database.new('/tmp/my.db')

# get query results as array of hashes
db.query('select 1 as foo') #=> [{ :foo => 1 }]
# or:
db.query_hash('select 1 as foo') #=> [{ :foo => 1 }]
# or iterate over results
db.query('select 1 as foo') { |r| p r }
# { :foo => 1 }

# get query results as array of arrays
db.query_ary('select 1, 2, 3') #=> [[1, 2, 3]]
# or iterate over results
db.query_ary('select 1, 2, 3') { |r| p r }
# [1, 2, 3]

# get a single row as a hash
db.query_single_row("select 1 as foo") #=> { :foo => 1 }

# get single column query results as array of values
db.query_single_column('select 42') #=> [42]
# or iterate over results
db.query_single_column('select 42') { |v| p v }
# 42

# get single value from first row of results
db.query_single_value("select 'foo'") #=> "foo"

# parameter binding (works for all query_xxx methods)
db.query_hash('select ? as foo, ? as bar', 1, 2) #=> [{ :foo => 1, :bar => 2 }]

# parameter binding of named parameters
db.query('select * from foo where bar = :bar', bar: 42)
db.query('select * from foo where bar = :bar', 'bar' => 42)
db.query('select * from foo where bar = :bar', ':bar' => 42)

# prepared statements
stmt = db.prepare('select ? as foo, ? as bar') #=> Extralite::PreparedStatement
stmt.query(1, 2) #=> [{ :foo => 1, :bar => 2 }]
# PreparedStatement offers the same data access methods as the Database class,
# but without the sql parameter.

# get last insert rowid
rowid = db.last_insert_rowid

# get number of rows changed in last query
number_of_rows_affected = db.changes

# get db filename
db.filename #=> "/tmp/my.db"

# load an extension
db.load_extension('/path/to/extension.so')

# close database
db.close
db.closed? #=> true

Usage with Sequel

Extralite includes an adapter for Sequel. To use the Extralite adapter, just use the extralite scheme instead of sqlite:

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

(Make sure you include extralite as a dependency in your Gemfile.)

Why not just use the sqlite3 gem?

The sqlite3-ruby gem is a popular, solid, well-maintained project, used by thousands of developers. I've been doing a lot of work with SQLite3 databases lately, and wanted to have a simpler API that gives me query results in a variety of ways. Thus extralite was born.

Extralite is quite a bit faster than sqlite3-ruby and is also thread-friendly. On the other hand, Extralite does not have support for defining custom functions, aggregates and collations. If you're using any of those features, you'll have to stick to sqlite3-ruby.

Here's a table summarizing the differences between the two gems:

Concurrency

Extralite releases the GVL while making blocking calls to the sqlite3 library, that is while preparing SQL statements and fetching rows. Releasing the GVL allows other threads to run while the sqlite3 library is busy compiling SQL into bytecode, or fetching the next row. This does not seem to hurt Extralite's performance:

Performance

A benchmark script is included, creating a table of various row counts, then fetching the entire table using either sqlite3 or extralite. This benchmark shows Extralite to be up to ~12 times faster than sqlite3 when fetching a large number of rows.

Rows as hashes

Benchmark source code

Rows as arrays

Benchmark source code

Prepared statements

Benchmark source code

As those benchmarks show, Extralite is capabale of reading up to 3M rows/second when fetching rows as arrays, and up to 2.6M rows/second when fetching rows as hashes.

License

The source code for Extralite is published under the MIT license. The source code for SQLite is in the public domain.

Contributing

Contributions in the form of issues, PRs or comments will be greatly appreciated!