Want some programming help? [close]

Ruby SQLite Tutorial

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.

References

Advertisement