SQL Basics : SQLite3 Ruby Driver Basics


Learn how to create table, insert records and retrieve records from a Ruby program that connects to mysqlite database.

SQLite3 Ruby Driver

The sqlite3-ruby gem is a SQLite3 database driver for Ruby programs to interact with the SQLite3 database. You must have the SQLite3 database installed to use this gem. Install the gem by running:

gem install sqlite3

This installed sqlite3 gem version 1.3.9 on my machine.

Create Database

Create client.rb with the following code.

require 'sqlite3'

db = SQLite3::Database.new('test.db')

rows = db.execute <<-SQL 
          create table users(
           id int,
           name varchar(30)

puts rows
puts rows.inspect

Run the client.rb.

ruby client.rb.

The output shows an empty array: []. Let's open the test.db database and check the users table.

$ sqlite3 test.db
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite> .schema users
           id int,
           name varchar(30)

Insert Rows

Currently, there are no records in the users table.

sqlite> select count(*) from users;

Let's insert a record. Create insert.rb.

require 'sqlite3'

db = SQLite3::Database.open('test.db')

db.execute("INSERT INTO users(id, name) VALUES(?, ?)", [1, 'Bugs Bunny'])

Run the insert.ruby.

ruby insert.rb

Now we see the record we just inserted.

sqlite> select count(*) from users;
sqlite> select * from users;
1|Bugs Bunny

You can also use a hash to insert multiple records. Create insert2.rb.

require 'sqlite3'

db = SQLite3::Database.open('test.db')

records = {2 => 'Daffy Duck', 3 => 'Porky Pig'}           

records.each do |record|
  db.execute("INSERT INTO users(id, name) VALUES(?, ?)", record)  

Now, you will see three records.

sqlite> select * from users;
1|Bugs Bunny
2|Daffy Duck
3|Porky Pig

Select Rows

Let's select the records from the users table. Create select.rb.

require 'sqlite3'

db = SQLite3::Database.open('test.db')

db.execute("select * from users") do |result|
  puts result

Run select.rb.

ruby select.rb
Bugs Bunny
Daffy Duck
Porky Pig

You can see that there are three records.


In this article we saw examples on using sqlite3-ruby in a database client written in Ruby. You learned how to create tables, insert records and retrieve results from a table in a Ruby program.

Related Articles

Software Compatibility Best Practices

I spoke to some of the most talented and experienced software developers. I have created a guide that is filled with valuable insights and actionable ideas to boost developer productivity.

You will gain a better understanding of what's working well for other developers and how they address the software compatibility problems.

Get the Guide Now