No release in over 3 years
This module allows Ruby programs to interface with the SQLite3 database engine (http://www.sqlite.org). You must have the SQLite engine installed in order to build this module. Note that this module is NOT compatible with SQLite 2.x.
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
 Dependencies

Development

>= 2.8.0

Runtime

>= 1.3.3
 Project Readme

Ruby Interface for SQLite3

Overview

This library allows Ruby programs to use the SQLite3 database engine (http://www.sqlite.org).

Note that this module is only compatible with SQLite 3.6.16 or newer.

Test suite

Quick start

For help understanding the SQLite3 Ruby API, please read the FAQ and the full API documentation.

A few key classes whose APIs are often-used are:

  • SQLite3::Database (rdoc)
  • SQLite3::Statement (rdoc)
  • SQLite3::ResultSet (rdoc)

If you have any questions that you feel should be addressed in the FAQ, please send them to the mailing list or open a discussion thread.

require "sqlite3"

# Open a database
db = SQLite3::Database.new "test.db"

# Create a table
rows = db.execute <<-SQL
  create table numbers (
    name varchar(30),
    val int
  );
SQL

# Execute a few inserts
{
  "one" => 1,
  "two" => 2,
}.each do |pair|
  db.execute "insert into numbers values ( ?, ? )", pair
end

# Find a few rows
db.execute( "select * from numbers" ) do |row|
  p row
end
# => ["one", 1]
#    ["two", 2]

# Create another table with multiple columns
db.execute <<-SQL
  create table students (
    name varchar(50),
    email varchar(50),
    grade varchar(5),
    blog varchar(50)
  );
SQL

# Execute inserts with parameter markers
db.execute("INSERT INTO students (name, email, grade, blog)
            VALUES (?, ?, ?, ?)", ["Jane", "me@janedoe.com", "A", "http://blog.janedoe.com"])

db.execute( "select * from students" ) do |row|
  p row
end
# => ["Jane", "me@janedoe.com", "A", "http://blog.janedoe.com"]

Thread Safety

When SQLite3.threadsafe? returns true, then SQLite3 has been compiled to support running in a multithreaded environment. However, this doesn't mean that all classes in the SQLite3 gem can be considered "thread safe".

When SQLite3.threadsafe? returns true, it is safe to share only SQLite3::Database instances among threads without providing your own locking mechanism. For example, the following code is fine because only the database instance is shared among threads:

require 'sqlite3'

db = SQLite3::Database.new ":memory:"

latch = Queue.new

ts = 10.times.map {
  Thread.new {
    latch.pop
    db.execute "SELECT '#{Thread.current.inspect}'"
  }
}
10.times { latch << nil }

p ts.map(&:value)

Other instances can be shared among threads, but they require that you provide your own locking for thread safety. For example, SQLite3::Statement objects (prepared statements) are mutable, so applications must take care to add appropriate locks to avoid data race conditions when sharing these objects among threads.

Lets rewrite the above example but use a prepared statement and safely share the prepared statement among threads:

db = SQLite3::Database.new ":memory:"

# Prepare a statement
stmt = db.prepare "SELECT :inspect"
stmt_lock = Mutex.new

latch = Queue.new

ts = 10.times.map {
  Thread.new {
    latch.pop

    # Add a lock when using the prepared statement.
    # Binding values, and walking over results will mutate the statement, so
    # in order to prevent other threads from "seeing" this thread's data, we
    # must lock when using the statement object
    stmt_lock.synchronize do
      stmt.execute(Thread.current.inspect).to_a
    end
  }
}

10.times { latch << nil }

p ts.map(&:value)

stmt.close

It is generally recommended that if applications want to share a database among threads, they only share the database instance object. Other objects are fine to share, but may require manual locking for thread safety.

Support

Installation or database extensions

If you're having trouble with installation, please first read INSTALLATION.md.

General help requests

You can ask for help or support:

Bug reports

You can file the bug at the github issues page.

Contributing

See CONTRIBUTING.md.

License

This library is licensed under BSD-3-Clause, see LICENSE.

Dependencies

The source code of sqlite is distributed in the "ruby platform" gem. This code is public domain, see https://www.sqlite.org/copyright.html for details.