Installing And Using SQLite With Ruby On Windows

SQLite is a great little embeddable database engine that’s meant to be self-contained, easy to use and not require configuration. However when I tried to use it in my Ruby code I found that it wasn’t that straight forward getting everything to work, especially if you’re using Ruby on windows. The information on the old WWW was rather sparse. I worked it out eventually, so in the interests of being a helpful web denizen I thought I’d share.

What To Download And Where To Put It

First thing first, before we involve Ruby directly lets download set up everything we need. You need to download two things from the SQLite website (well strictly speaking you only actually NEED one, but we’ll download both since the other one is useful):

• sqlitedll-3_6_16.zip – you can think of this one as actually being the SQLite database that you will install on your machine
• sqlite-3_6_16.zip – this one is a command line utility that can be used to administer a SQLite database

Both of those files are located on the downloads page – http://www.sqlite.org/download.html, under the ‘Precompiled Binaries For Windows’ section.

Once you unzip both of those you will have several files, the important ones are:

• sqlite3.dll – this is the actual database
• sqlite3.exe – this is the command line utility

You need to take the DLL and put it somewhere, where Ruby would look for libraries when you execute a script, some possible locations would be, your windows system folder or the bin folder of your Ruby installation (Note: as reader Luis Lavena noted in the comments below, the ruby installation bin folder is by far the best place to put the DLL). On my machine it was:

C:\ruby1.8\bin
C:\WINDOWS\system
C:\WINDOWS\system32

You can also put the command line utility in any of those places as well, but you don’t have to as you can use that one from anywhere (if you do put it in another location make sure that location is in the PATH environment variable otherwise you will have to specify the full path to it every time you want to use it).

You’re now ready to install the SQLite Ruby interface gem. You will need to do the following:

gem install sqlite3-ruby

After it completes you will have a gem similar to the following in your gem repository, in my case:

C:\ruby1.8\lib\ruby\gems\1.8\gems\sqlite3-ruby-1.2.5-x86-mswin32

You’re now ready to work with SQLite in your Ruby application. Note that you need to install the ‘sqlite3-ruby’ gem and not the ‘sqlite-ruby’ one, as that is the old one.

If you did not install the DLL correctly you might get an error similar to the following when you try to run your ruby script:

C:/ruby1.8/lib/ruby/1.8/dl/import.rb:29:in `initialize': unknown error (RuntimeError)
        from C:/ruby1.8/lib/ruby/1.8/dl/import.rb:29:in `dlopen'
        from C:/ruby1.8/lib/ruby/1.8/dl/import.rb:29:in `dlload'
        from C:/ruby1.8/lib/ruby/1.8/dl/import.rb:27:in `each'
        from C:/ruby1.8/lib/ruby/1.8/dl/import.rb:27:in `dlload'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/driver/dl/api.rb:31
        from C:/ruby1.8/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
        from C:/ruby1.8/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/driver/dl/driver.rb:1
        from C:/ruby1.8/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
        from C:/ruby1.8/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/database.rb:619:in `load_driver'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/database.rb:617:in `each'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/database.rb:617:in `load_driver'
        from C:/ruby1.8/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.5-x86-mswin32/lib/sqlite3/database.rb:72:in `initialize'
        from D:/ruby-projects/search-engine/search-engine/lib/search-engine-main.rb:21:in `new'
        from D:/ruby-projects/search-engine/search-engine/lib/search-engine-main.rb:21

This is because the SQLite ruby interface is trying to use a deprecated driver (it’s called DL and it is buggy and doesn’t work) because it can’t find the native one, you need to make sure you put the DLL in the appropriate spot mentioned above. Also note that putting the DLL in the same directory as the script you’re executing does not work and produces a different error possibly because it has a dependency on MSVCRT.DLL.

How To Use SQLite With Ruby

We can now write some ruby code to test that everything is working, as well as learn to use SQLite a little bit.

require 'sqlite3'
database = SQLite3::Database.new( "new.database" )
 
database.execute( "create table sample_table (id INTEGER PRIMARY KEY, sample_text TEXT, sample_number NUMERIC);" )
 
database.execute( "insert into sample_table (sample_text,sample_number) values ('Sample Text1', 123)")
database.execute( "insert into sample_table (sample_text,sample_number) values ('Sample Text2', 456)")
 
rows = database.execute( "select * from sample_table" )
 
p rows

Running this code will create a new file called ‘new.database’ in the same folder where you ran the script. This is the sqlite database you’ve just created (sqlite stores data as files). To execute any SQL code using sqlite, you simply call the ‘execute’ method on your database instance and provide it some SQL code as a string. This is what we did to create a table and to insert two rows into it. Our little script produces the following output:

[["1", "Sample Text1", "123.0"], ["2", "Sample Text2", "456.0"]]

Which shows that we can get the two rows that we inserted back from the database.

If you attempt to re-run this script, it will simply pick up the database file since the file persists past the execution of the script. Incidentally the script would fail a second time since the table ‘sample_table’ already exists in the database.

You now have the basics down, from here the going is a little easier. You can look at the following resources:

SQLite/Ruby FAQ – all the different ways to use sqlite from a Ruby script

SQLite Documentation – self explanatory

Supported SQL Syntax – self explanatory

SQLite Datatypes – how datatypes work in SQLite 3

Enjoy!

Tagged as: database engine, ruby, ruby interface, sqlite, sqlite3, tutorial