SQL Basics : SQLite3 Ruby Driver Basics

Objective


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)
         );
        SQL

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
users
sqlite> .schema users
CREATE TABLE users(
           id int,
           name varchar(30)
         );
sqlite> 

Insert Rows


Currently, there are no records in the users table.

sqlite> select count(*) from users;
0

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;
1
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)  
end

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
end

Run select.rb.

ruby select.rb
1
Bugs Bunny
2
Daffy Duck
3
Porky Pig

You can see that there are three records.

Summary


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


Create your own user feedback survey