Extralite - a Super Fast Ruby Gem for Working with SQLite3 Databases
- Source code: https://github.com/digital-fabric/extralite
- Documentation: http://www.rubydoc.info/gems/extralite
What is Extralite?
Extralite is a super fast, extra-lightweight (about 1300 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.42.0), offering access to the
latest features and enhancements.
Features
- Super fast - up to 11x faster than the sqlite3 gem (see also comparison.)
- A variety of methods for different data access patterns: rows as hashes, rows as arrays, single row, single column, single value.
- Prepared statements.
- Parameter binding.
- Use system-installed sqlite3, or the bundled latest version of SQLite3.
- 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.
- Automatically execute SQL strings containing multiple semicolon-separated queries (handy for creating/modifying schemas).
- Execute the same query with multiple parameter lists (useful for inserting records).
- 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
gem 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, using require 'extralite'
to load the gem.
Synopsis
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)
# insert multiple rows
db.execute_multi('insert into foo values (?)', ['bar', 'baz'])
db.execute_multi('insert into foo values (?, ?)', [[1, 2], [3, 4]])
# 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 column names for the given sql
db.columns('select a, b, c from foo') => [:a, :b, :c]
# get db filename
db.filename #=> "/tmp/my.db"
# get list of tables
db.tables #=> ['foo', 'bar']
# get and set pragmas
db.pragma(:journal_mode) #=> 'delete'
db.pragma(journal_mode: 'wal')
db.pragma(:journal_mode) #=> 'wal'
# load an extension
db.load_extension('/path/to/extension.so')
# close database
db.close
db.closed? #=> true
More Features
Interrupting Long-running Queries
When running long-running queries, you can use Database#interrupt
to interrupt
the query:
timeout_thread = Thread.new do
sleep 10
db.interrupt
end
result = begin
db.query(super_slow_sql)
rescue Extralite::InterruptError
nil
ensure
timeout_thread.kill
timeout_thread.join
end
Creating Backups
You can use Database#backup
to create backup copies of a database. The
#backup
method takes either a filename or a database instance:
# with a filename
db.backup('backup.db')
# with an instance
target = Extralite::Database.new('backup.db')
db.backup(target)
For big databases, you can also track the backup progress by providing a block that takes two arguments - the number of remaining pages, and the total number pages:
db.backup('backup.db') do |remaining, total|
puts "backup progress: #{(remaining.to_f/total * 100).round}%"
end
Retrieving Status Information
Extralite provides methods for retrieving status information about the sqlite
runtime, database-specific status and prepared statement-specific status,
Extralite.runtime_status
, Database#status
and PreparedStatement#status
respectively. You can also reset the high water mark for the specific status
code by providing true as the reset argument. The status codes mirror those
defined by the SQLite API. Some examples:
# The Extralite.runtime_status returns a tuple consisting of the current value
# and the high water mark value.
current, high_watermark = Extralite.runtime_status(Extralite::SQLITE_STATUS_MEMORY_USED)
# To reset the high water mark, pass true as a second argument:
current, high_watermark = Extralite.runtime_status(Extralite::SQLITE_STATUS_MEMORY_USED, true)
# Similarly, you can interrogate a database's status (pass true as a second
# argument in order to reset the high watermark):
current, high_watermark = db.status(Extralite::SQLITE_DBSTATUS_CACHE_USED)
# The PreparedStatement#status method returns a single value (pass true as a
# second argument in order to reset the high watermark):
value = stmt.status(Extralite::SQLITE_STMTSTATUS_RUN)
Working with Database Limits
The Database#limit
can be used to get and set various database limits, as
discussed in the SQLite docs:
# get limit
value = db.limit(Extralite::SQLITE_LIMIT_ATTACHED)
# set limit
db.limit(Extralite::SQLITE_LIMIT_ATTACHED, new_value)
Setting the Busy Timeout
When accessing a database concurrently it can be handy to set a busy timeout, in
order to not have to deal with rescuing Extralite::BusyError
exceptions. The
timeout is given in seconds:
db.busy_timeout = 5
Tracing SQL Statements
To trace all SQL statements executed on the database, pass a block to
Database#trace
. To disable tracing, call Database#trace
without a block:
# enable tracing
db.trace { |sql| puts sql: sql }
# disable tracing
db.trace
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
.)
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 hurt Extralite's performance, as you can see:
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 ~11 times faster than sqlite3
when fetching a
large number of rows.
Rows as Hashes
Row count | sqlite3 1.6.0 | Extralite 1.21 | Advantage |
---|---|---|---|
10 | 63.7K rows/s | 94.0K rows/s | 1.48x |
1K | 299.2K rows/s | 1.983M rows/s | 6.63x |
100K | 185.4K rows/s | 2.033M rows/s | 10.97x |
Rows as Arrays
Row count | sqlite3 1.6.0 | Extralite 1.21 | Advantage |
---|---|---|---|
10 | 71.2K rows/s | 92.1K rows/s | 1.29x |
1K | 502.1K rows/s | 2.065M rows/s | 4.11x |
100K | 455.7K rows/s | 2.511M rows/s | 5.51x |
Prepared Statements
Row count | sqlite3 1.6.0 | Extralite 1.21 | Advantage |
---|---|---|---|
10 | 232.2K rows/s | 741.6K rows/s | 3.19x |
1K | 299.8K rows/s | 2386.0M rows/s | 7.96x |
100K | 183.1K rows/s | 1.893M rows/s | 10.34x |
As those benchmarks show, Extralite is capabale of reading up to 2.5M rows/second when fetching rows as arrays, and up to 2M 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!