I’ve been using SQLite with Ruby for a personal project, and the entire development + deployment experience is just wow. The combination of Ruby + SQLite is like Batman and Robin, minus the capes and with a ton of coding superpowers. This post gives a brief introduction to working with SQLite in Ruby.
Specifically, it covers:
- What is SQLite?
- How to install SQLite on your computer
- How to connect to SQLite in Ruby
- Create a database
- Create a table to store data
- Insert and Query data from the database tables
- Error Handling
By the end of the post, you will have a solid understanding of how to work with SQLite database in Ruby.
Let's get started.
What is SQLite?
SQLite is an awesome in-process relational database. It is self-contained, serverless, zero-configuration, and transactional. The big benefit of SQLite is its simplicity and ease of use. It's just a single file. That's it. You don't connect to a database server running in a separate process, you just access a database file in the same process.
What's more, just like Ruby, SQLite is dynamically typed database. So there're no types or restrictions on the data you can store in columns. You can store any type or size of data in a column. This makes it very flexible, just like Ruby. For more details, read this: Flexible typing is a feature of SQLite, not a bug.
If you want to learn more about the origins and history of SQLite, I highly recommend you check out this podcast with the creator of SQLite, Dr. Richard Hipp: The Untold Story of SQLite.
SQLite is everything I wanted in a database as a one-person development team, without any unnecessary complexities associated with the traditional client-server databases. It's an incredibly useful database for learning SQL as well as a very capable database for most applications.
Also, from the official SQLite website,
The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
I think that should put any concerns one might have about the performance and scalability capabilities of SQLite to rest.
How to Install SQLite?
If you are on a Mac, it's probably installed on your computer already. To check if you have SQLite or not, run the following command in a terminal window:
$ weby git:(sqlite) ✗ sqlite3
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
If you see the above output, SQLite is installed on your computer. Otherwise, you can check the official installation page on the SQLite website for your specific operating system.
To exit, type
ctrl + d.
How to Connect to SQLite from Ruby?
To connect to any database from a programming language, we need a driver written in that language, for that particular database. SQLite is no different.
To connect to and interact with a SQLite database, you will need the
sqlite3-ruby gem. Navigate to your project directory and run the following command (assuming you're using Bundler):
$ bundle add sqlite3
With the SQLite driver gem installed, the next step is to create a SQLite database, add a table, put some data, and query that data, all in Ruby.
Let's create a new
database.rb file in the
weby directory and write some Ruby code to play with SQLite.
Create or Open a Database
An SQLite database is just a file with the extension of
.db. If you want to take a backup, just copy + paste that file into a drive or cloud. That's it.
To create a database (or open if one exists already), we create a new instance of the
SQLite3::Database class, passing the name of the SQLite database file. The
SQLite3::Database class encapsulates a single connection to a SQLite database.
Don't forget to require the gem first.
db = SQLite3::Database.new("data.db")
Alternatively, you can use the
open method which works exactly as
new, if you don't pass a block.
db = SQLite3::Database.open("data.db")
If you pass a block, however, it returns the result of the block instead of the database instance. It will still close the database at the end, though.
SQLite3::Database.open("data.db") do |db|
# work with db
Create a Table
After you have the database instance returned by the
open method, you can execute any arbitrary SQL on it. For example, to create a new database table named
articles you might use the following SQL:
CREATE TABLE articles(
id INTEGER NOT NULL PRIMARY KEY,
To do the same thing in Ruby, just pass that SQL statement to the
db.execute method, as follows:
CREATE TABLE articles(
id INTEGER NOT NULL PRIMARY KEY,
Note: I am using the heredoc syntax to represent a multi-line, indented string. You can write the above statement as follows, though it's not as readable:
db.execute "CREATE TABLE articles(id INTEGER NOT NULL PRIMARY KEY, title TEXT, body TEXT)"
To ensure that a table is created only if it doesn't exist, you can use the
IF NOT EXISTS clause as follows:
db.execute "CREATE TABLE IF NOT EXISTS photos(path TEXT)"
Now SQLite will only create the
photos table if it doesn't exist in the database.
Insert and Query Data
As we saw, you can execute any arbitrary SQL against the database connection object. Inserting and querying data is no different. Just run the
SELECT statements as you would in a normal SQL query on the database.
records = [
title: "What is Rails?",
body: "Rails is a web application development framework written in the Ruby programming language."
title: "What is SQLite?",
body: "SQLite is an in-process, serverless, relational database."
records.each do |record|
db.execute "INSERT INTO articles (title, body) VALUES (?, ?)", record[:title], record[:body]
Note that the above
INSERT statement uses questions marks (
?) as variable placeholders, to parameterize the query, to protect against SQL injection attack. The values are passed as arguments after the query itself.
The following code reads the data we just inserted into SQLite database.
db.execute("SELECT * FROM articles") do |row|
# "What is Rails?"
# "What is SQLite?"
Note that the
row object above is an instance of
SQLite3::ResultSet::ArrayWithTypesAndFields class, which acts as an array. If you want the library to return a hash instead, set the
results_as_hash property on the database to
db = SQLite3::Database.new "data/blog.db"
db.results_as_hash = true
Now, the returned row will be an instance of
SQLite3::ResultSet::HashWithTypesAndFields class where the keys will be the names of the columns:
row.keys # ["id", "title", "body"]
row["title"] # "What is Rails?"
Use query() to bind parameters
Instead of passing and executing the SQL as a plain string to the
execute method, use the
query method which lets you create a statement, bind parameters to it, and then execute.
result = db.query( "select * from articles where title=?", ["What is Rails?"])
It returns an instance of
SQLite3::ResultSet class. Make sure you call
close on this result set instance, or there could be issues with locks on the table. A better strategy is to pass a block, instead. When the block terminates,
close will be invoked by default.
Catch and Handle Errors
Sometimes, errors might occur during the execution of an operation. To handle those errors, wrap the database access statements in
begin - rescue - ensure blocks, watching for
rescue blocks, you can handle any errors and using
ensure you can perform any cleanup as needed.
db = SQLite3::Database.new "data.db"
# do something that may result in an exception
rescue SQLite3::Exception => e
# gracefully handle the error
# close the database or perform other cleanup
There's much more you can do in SQLite, but for an introductory article, this should be enough to get you started. With my recent interest in SQLite, I am going to be writing a lot more on it, so stay tuned for more deep dives and cool adventures!
That's a wrap. I hope you found this article helpful and you learned something new.
As always, if you have any questions or feedback, didn't understand something, or found a mistake, please leave a comment below or send me an email. I reply to all emails I get from developers, and I look forward to hearing from you.
If you'd like to receive future articles directly in your email, please subscribe to my blog. If you're already a subscriber, thank you.