Extralite 3.1.0 is Here!

I’ve just released of Extralite version 3.1.0. This new release implements automatic caching of parametric queries, and updates the bundled SQLite to version 3.53.4.

Extralite is a fast and innovative SQLite wrapper for Ruby with a rich set of features. It provides multiple ways of retrieving data from SQLite databases, makes it possible to use SQLite databases in multi-threaded and multi-fibered Ruby apps, and includes a comprehensive set of tools for managing SQLite databases.

Automatic Query Caching

Extralite now automatically caches the underyling sqlite3_stmt object for any parametric query. Previously, when you ran the same SQL using Database#query_xxx with different parameters, each time the query ran Extralite would create a new sqlite3_stmt, which causes sqlite to repeatedly parse the same SQL over and over again. Now, when calling one of the Database#query_xxx methods (or Database#execute), Extralite will automatically cache the underlying sqlite3_stmt object and reuse it on repeated calls, thus reducing allocations and minimizing parsing overhead, letting you squeeze even more performance out of your SQLite-based app.

Previously, I’ve written about the advantages of working with relational databases by issuing raw SQL queries instead of using an ORM. Extralite is all about letting you do that easily and at the same time maximize performance. Here’s a simple example of code that does just that:

class Posts
  def initialize(db)
    @db = db
  end

  def create(title:, body:)
    @db.query_splat <<~SQL, title, body
      insert into posts (title, body)
      values (?, ?)
      returning id
    SQL
  end

  def by_id(id)
    @db.query_single_row <<~SQL, id
      select id, title, body
      from posts
      where id = ?
    SQL
  end
end

With automatic query caching, you don’t need to explicitly create prepared queries (using Database#prepare). You can just issue the same parametric SQL, and Extralite will do the work of caching the underlying query for you.

Advanced SQLite Usage with Extalite

Extralite is designed to let you get the most out of your SQLite databases. It automatically sets your database up for using WAL and enforcing foreign key constraints (you can turn it off for legacy databases). It provides advanced features such as batch execution of parametric queries, savepoints, backups and changesets. And in addition, it is fast!

Extralite also lets you generate object graphs with indentity maps from query results using structured transforms, which lets you retrieve entities along with all their associations using a single query, without ever needing to use an ORM!

What’s Next for Extralite

My goal for Extralite is to make it into a comprehensive tool for building apps on top of SQLite databases without using an ORM. I want it to be fast and simple to use. Following up on automatic query caching, I’d like to actually remove the Extralite::Query class that encapsulates a prepared query, since in my view automatic query caching already solves most of the value of prepared queries. Please let me know if you think this abstraction is necessary for your use case, I might change my mind.