Question

This part of the SQLite Ruby tutorial will show you how to connect to a database and do a few simple things with the database.

Before we start

The SQLite comes with the sqlite3 command line utility. It can be used to issue SQL commands against a database. Now we are going to use the sqlite3 command line tool to create a new database.

$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

We provide a parameter to the sqlite3 tool. The test.db is a database name. It is a single file on our disk. If it is present, it is opened. If not, it is created.

sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. There are currently no tables. The .exit command terminates the interactive session of the sqlite3 command line tool. The ls Unix command shows the contents of the current working directory. We can see the test.db file. All data will be stored in this single file.

The sqlite-ruby interface is used to interact with SQLite database with the Ruby language.

$ sudo apt-get install libsqlite3-ruby

The above command installs the module on Debian based Linux systems.

The first step is to create a Database object. The Database class encapsulates a single connection to an SQLite database. The database object is closed with the close method.

SQLite3::Database.new dbname
SQLite3::Database.open dbname

The new method creates a new Database object that opens the given dbname file. If the file does not exist, it will be created if possible. By default, the new database will return result rows as arrays. The open method opens the database contained in the given file.

SQLite3::Database.new ":memory:"

It is possible to create an in-memory database if we provide a special string :memory: for the file name.

Sources

The sqlite-ruby.rubyforge.org website was consulted when creating this tutorial.

Version

In the first code example, we will get the version of the SQLite database.

#!/usr/bin/ruby

require 'sqlite3'

begin
  
  db = SQLite3::Database.new ":memory:"
  puts db.get_first_value 'SELECT SQLITE_VERSION()'
  
rescue SQLite3::Exception => e 
  
  puts "Exception occurred"
  puts e
  
ensure
  db.close if db
end

In the above Ruby script we create a new in-memory database. We execute an SQL statement which returns the version of the SQLite database.

require 'sqlite3'

We use sqlite3 Ruby module to connect to the SQLite database.

db = SQLite3::Database.new ":memory:"

We create a new database object. The Database class encapsulates a single connection to an SQLite database. The database is created in memory. So it is not permanent.

puts db.get_first_value 'SELECT SQLITE_VERSION()'

We call the get_first_value method of the db object. It executes the SQL statement and obtains the first value of the first row of a result set.

rescue SQLite3::Exception => e 
  
  puts "Exception occurred"
  puts e

We check for errors. This is important, since working with databases is error prone.

ensure
  db.close if db
end

In the end, we release the resources.

$ ./version.rb
3.7.7

The output might look like the above.

Inserting data

We will create a Cars table and insert several rows to it.

#!/usr/bin/ruby

require 'sqlite3'

begin
  
  db = SQLite3::Database.open "test.db"
  db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
    Name TEXT, Price INT)"
  db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
  db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
  db.execute "INSERT INTO Cars VALUES(3,'Skoda',9000)"
  db.execute "INSERT INTO Cars VALUES(4,'Volvo',29000)"
  db.execute "INSERT INTO Cars VALUES(5,'Bentley',350000)"
  db.execute "INSERT INTO Cars VALUES(6,'Citroen',21000)"
  db.execute "INSERT INTO Cars VALUES(7,'Hummer',41400)"
  db.execute "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
  
rescue SQLite3::Exception => e 
  
  puts "Exception occurred"
  puts e
  
ensure
  db.close if db
end

The above script creates a Cars table and inserts 8 rows into the table.

db = SQLite3::Database.open "test.db"

We connect to the test.db database.

db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
  Name TEXT, Price INT)"

The execute method executes the given SQL statement. A new Cars table is created if it does not already exist.

db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"

These two lines insert two cars into the table. Note that by default, we are in the autocommit mode, where all changes to the table are immediately effective.

sqlite> .mode column  
sqlite> .headers on

We verify the written data with the sqlite3 tool. First we modify the way the data is displayed in the console. We use the column mode and turn on the headers.

sqlite> SELECT * FROM Cars;
Id      Name    Price   
----------  ----------  ----------
1       Audi    52642   
2       Mercedes  57127   
3       Skoda     9000    
4       Volvo     29000   
5       Bentley   350000  
6       Citroen   21000   
7       Hummer    41400   
8       Volkswagen  21600 

This is the data that we have written to the Cars table.

The last inserted row id

Sometimes we need to determine the id of the last inserted row. We use the last_insert_row_id method to find it.

#!/usr/bin/ruby

require 'sqlite3'

begin
  
  db = SQLite3::Database.new ":memory:"
  
  db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
  db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
  db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
  db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
  db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
  db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
  
  id = db.last_insert_row_id
  puts "The last id of the inserted row is #{id}"
  
rescue SQLite3::Exception => e 
  
  puts "Exception occurred"
  puts e
  
ensure
  db.close if db
end

We create a Friends table in memory. The Id is automatically incremented.

db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"

In SQLite, INTEGER PRIMARY KEY column is auto incremented. There is also an AUTOINCREMENT keyword. When used in INTEGER PRIMARY KEY AUTOINCREMENT a slightly different algorithm for Id creation is used.

db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"

These five SQL statements insert five rows into the Friends table.

id = db.last_insert_row_id

Using the last_insert_row_id method, we get the last inserted row Id .

$ ./last_rowid.rb 
The last id of the inserted row is 5

We see the output of the script.

Fetching data

In the last example of this chapter we fetch some data. More about data fetching will be discussed in the Queries chapter.

#!/usr/bin/ruby

require 'sqlite3'

begin
  
  db = SQLite3::Database.open "test.db"
  
  stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
  rs = stm.execute 
  
  rs.each do |row|
    puts row.join "\s"
  end
       
rescue SQLite3::Exception => e 
  
  puts "Exception occurred"
  puts e
  
ensure
  stm.close if stm
  db.close if db
end

In the example we fetch 5 rows from the Cars table.

stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
rs = stm.execute 

We prepare an SQL statement for execution with the prepare method. The method returns a statement object. Then the SQL statement is executed using the execute method. It returns a result set. The ResutlSet object is a simple cursor over the data that the query returns.

rs.each do |row|
  puts row.join "\s"
end

With the each method we traverse the data in the result set. In each cycle, it returns a row. The row is an array of fields. These fields are joined with a empty space to form a line.

$ ./fetch.rb 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

This is the output of the fetch.rb script.

In this chapter of the SQLite Ruby tutorial, we have shown how to establish a database connection to the SQLite database. We have explained scripts which do some basic work with a database.