0.04
There's a lot of open issues
Extra-lightweight SQLite3 wrapper for Ruby with bundled SQLite3
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
 Dependencies

Development

= 5.15.0
= 5.51.0
= 0.17.1
= 0.9.27
 Project Readme

Extralite

A fast Ruby gem for working with SQLite3 databases

Ruby gem Tests MIT License

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.
  • 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 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)

# 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 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

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:

sqlite3-ruby Extralite
SQLite3 dependency depends on OS-installed libsqlite3 Use either system sqlite3 or bundled latest version of SQLite3
API design multiple classes single class
Query results row as hash, row as array, single row, single value row as hash, row as array, single column, single row, single value
Execute multiple statements separate API (#execute_batch) integrated
Prepared statements yes yes
custom functions in Ruby yes no
custom collations yes no
custom aggregate functions yes no
Multithread friendly no yes
Code size ~2650LoC ~600LoC
Performance 1x 1.5x to 12.5x (see below)

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

Row count sqlite3-ruby Extralite Advantage
10 75.3K rows/s 134.2K rows/s 1.78x
1K 286.8K rows/s 2106.4K rows/s 7.35x
100K 181.0K rows/s 2275.3K rows/s 12.53x

Rows as arrays

Benchmark source code

Row count sqlite3-ruby Extralite Advantage
10 64.3K rows/s 94.0K rows/s 1.46x
1K 498.9K rows/s 2478.2K rows/s 4.97x
100K 441.1K rows/s 3023.4K rows/s 6.85x

Prepared statements

Benchmark source code

Row count sqlite3-ruby Extralite Advantage
10 241.8K rows/s 888K rows/s 3.67x
1K 298.6K rows/s 2606K rows/s 8.73x
100K 201.6K rows/s 1934K rows/s 9.6x

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!