Working with SQLite in Ruby

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:

1. What is SQLite?
2. How to install SQLite on your computer
3. How to connect to SQLite in Ruby
4. Create a database
5. Create a table to store data
6. Insert and Query data from the database tables
7. 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,

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.
sqlite>

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.

require "sqlite3"

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.

require "sqlite3"

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.

require 'sqlite3'

SQLite3::Database.open("data.db") do |db|
  # work with db
end

Create a Table

After you have the database instance returned by the new or 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,
  title TEXT,
  body TEXT
)

To do the same thing in Ruby, just pass that SQL statement to the db.execute method, as follows:

db.execute <<~SQL
  CREATE TABLE articles(
    id INTEGER NOT NULL PRIMARY KEY,
    title TEXT,
    body TEXT
  )
SQL

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 INSERT and 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]
end

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|
  p row[1]
end

# "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 true.

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?"])

result.close

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 SQLite3::Exception errors.

Inside the rescue blocks, you can handle any errors and using ensure you can perform any cleanup as needed.

require "sqlite3"

begin
    db = SQLite3::Database.new "data.db"

    # do something that may result in an exception
rescue SQLite3::Exception => e 
    # gracefully handle the error
ensure
    # close the database or perform other cleanup
end

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.