Introduction
SQLite3 is an awesome cross-platform relational database. Some of the major benefits of SQLite include its simplicity and easy management. Everything is stored in a single file and there is no authentication.
One of the big drawbacks with a SQLite database is that there is a global write-lock. Only one write operation can occur at any time. This can create a bottleneck for very write-intensive applications.
SQLite is used in production in many embedded and web applications. For example, you can use SQLite in combination with the Sinatra web application framework to persist data. If you are interested in learning how to use Sinatra, check out my Sinatra Tutorial.
Ruby is an incredibly productive and useful language. Combining Ruby with SQLite3 is a natural fit that opens many possibilities. Add in a simple web framework like Sinatra and you have an incredibly powerful but simple set of tools for building a web application.
You can read more on the official SQLite3 website and on the SQLite Wikipedia article.
If you need a database-agnostic library, something that you can use with SQLite and then easily configure to start using MySQL without rewriting all of your code, you want to use an ORM. ORM stands for Object-Relational-Mapping and the most popular one for Ruby is ActiveRecord.
To learn more about SQLite and how to use it in general, check out my SQLite3 Tutorial.
Install
If using Windows you will need to have Ruby with the DevKit to build the
native extensions. You can install it using the gem tool or if you
are in Linux, your distribution's package manager might have a package for it already.
Install using gem:
gem install sqlite3
If you are using a Linux distribution, it might already have a pre-built package for you to install. For example Ubuntu and Fedora already have packages.
Ubuntu install:
apt install ruby-sqlite3
Fedora install:
dnf install rubygem-sqlite3 rubygem-sqlite3-doc
Read documentation
If you need to refer to the official documentation, you can find it online
or access the local offline documentation with gem server.
Online documentation
The source code is available at https://github.com/sparklemotion/sqlite3-ruby and the official documentation is available at https://www.rubydoc.info/gems/sqlite3/frames.
Offline documentation
You can run the gem server locally to read
gem server
The gem server defaults to http://localhost:8808. The SQLite3 documentation URL will look something like http://localhost:8808/doc_root/sqlite3-1.4.1/.
Open or create a database
SQLite3 databases are single stand-alone files that commonly end with a .db extension.
You can also use a database name of ':memory:' to create an in-memory only database.
Keep in mind if you create a memory-only database it will be destroyed when
the program ends excution.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
Create a table
The safest way to ensure a table is created, is to use the CREATE TABLE IF NOT EXISTS statement.
This will create the table only if it is not already present.
This is a statement you can run on each startup of your application to
make sure the table you expect is available for use.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
db.execute "CREATE TABLE IF NOT EXISTS images(path TEXT, thumbs_up INT)"
Set results as hash
Set results to return as Hash object. This is slower but offers a huge convenience. Consider turning it off for high performance situations. Each row will have the column name as the hash key.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
db.results_as_hash = true
Executing statements
You can execute any SQL statements including deleting rows, dropping tables,
and creating new tables by calling the execute() function on the database object.
Inserting rows
One of the most common tasks with a database is to insert rows.
Use the execute() function on the database object to execute an insert
statement. Use questions marks (?) as variable placeholders, which
are passed as arguments after the query itself.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
db.execute "INSERT INTO images (path, thumbs_up) VALUES (?, ?)", 'image1.png', 0
Updating rows
After you have some rows inserted, you might need to update the rows.
You call execute() the same way but pass it the update statement
as opposed to the insert statement. You still use ? as placeholders
for variables and then pass the values after the SQL statement.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
# Assuming you have an images table created from the previous examples
db.execute "UPDATE images SET thumbs_up=? WHERE path=?", 1, 'image1.png'
Querying
You can use the query() function on the database object to easily create
a statement and bind variables to it. In this example we set the results_as_hash to true
that way we can easily get the values by name as opposed to their numeric position in the
query. The database object also has a get_first_value() function that will
return only the first row and discard the rest.
require 'sqlite3'
db = SQLite3::Database.open 'test.db'
db.results_as_hash = true
db.execute "CREATE TABLE IF NOT EXISTS images(path TEXT, thumbs_up INT)"
image_path = 'image1.png'
results = db.query "SELECT path, thumbs_up FROM images WHERE path=?", image_path
# Alternatively, to only get one row and discard the rest,
# replace `db.query()` with `db.get_first_value()`.
first_result = results.next
if first_result
puts first_result['thumb_up']
else
puts 'No results found.'
end
# Alternatively, you can go through each result like this:
# results.each { |row| puts row.join(',') }
Error handling
To properly handle any errors that occur during a SQLite operation,
you should wrap it in a begin/rescue/ensure block.
The rescue allows you to handle any error gracefully and ensure
gives you the ability to close the database or do any other cleanup
needed when handling the error.
require 'sqlite3'
begin
db = SQLite3::Database.open 'test.db'
# Attempt some action that may have an exception in SQLite
rescue SQLite3::Exception => e
# Handle the exception gracefully
ensure
# If the whole application is going to exit and you don't
# need the database at all any more, ensure db is closed.
# Otherwise database closing might be handled elsewhere.
db.close if db
end
Conclusion
After reading this, you should have a solid understanding of how to use SQLite3 in Ruby for common tasks like creating a database, creating tables, inserting data, updating rows, querying data, and catching exceptions.