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!

  • http://blog.mmediasys.com Luis Lavena

    Hello, Nice writeup.

    I would recommend a small change. Shuffle the locations where you recommend putting sqlite3 DLL.

    system folder has been deprecated on Since Windows 2000, that exist for legacy purposes and compatibility with early 16bits processes.

    You shouldn’t be copying this over system32, even if is just one DLL, Is not Windows 3.1.

    My personal recommendation is keep sqlite3.dll outside Windows directoy itself, so the better place to put it is Ruby bin folder.

    Also, with 1.2.5 of SQLite3/Ruby, you can take advantage of blocks for Database#new and Database#open, in similar fashion of File#new and File#open.

    Cheers!

    • http://www.skorks.com Alan Skorkin

      Cheers, thanks for the info I will update the post to reflect.

  • Kieran Hayes

    Don’t forget the excellent SQLite Manager Addon for Firefox.
    You can get it here:
    https://addons.mozilla.org/en-US/firefox/addon/5817

  • Pingback: Double Shot #509 « A Fresh Cup

  • songlee

    got this error
    The specified procedure could not be found. – Init_sqlite3

    Found out long time ago I installed merb which in turn install do_sqlite3 gem which cause the problem. Do gem uninstall to fix that.

    • http://www.skorks.com Alan Skorkin

      Thanks for that tip.
      Those kind of little dependencies can really catch you unawares, (especially if you’ve forgotten that you’ve installed something in the first place, or never knew). I can’t even tell you how many times similar stuff has happened to me :).

    • http://blog.mmediasys.com Luis Lavena

      You could get that error due two reasons:

      1) started from inside ruby\bin directory, and is using sqlite3.dll instead of the sqlite3.so (Ruby extension)

      Change the directory and try again.

      2) Incorrect installation of the sqlite3-ruby gem.

      Proceed with the uninstall/install cycle should fix it.

      HTH.

  • Pingback: SQLite3 for Windows « untitled

  • adla

    Hi
    i did all the steps but still reciving same error which is
    this application has failed to start because sqlite3.dll was not found. re-installing the application may fix this problem
    pleas need help i did uninstall then install did many thing still same error
    how can i fix this

    • http://www.skorks.com Alan Skorkin

      You need to make sure that your DLL is in the bin folder of your ruby installation, your bin folder should also been on the PATH. If you have that, everything should work. Also make sure you install the sqlite3-ruby gem not the sqlite-ruby gem.

  • Volkan

    Can you describe further how I can use SQLite by using the full path to it? I am building a portable app, and everything needs to be in the same folder.

  • Dave

    Thank you!- this helped me and I am just starting out.
    Thanks again

  • Pawan

    Hi,

    I dont have the requirement to use ruby on my machine.
    Will it suffice if I unzip the sqlite3 and sqlite3dll files in SYSTEM32 folder?
    Do I need to do anything else to activate the Sqlite db?

    Thanking you in advance.

    regards
    Pawan

  • http://techslam.net TechSlam

    Thank you Skorks. Your blog is my alltime favorite. thanks again.

  • Dennis Rose

    Skorks, thank you for your “what to download from where” regarding sqlite3. After two days being stalled my first app is running! Much appreciated. Now to track and read your other stuff. Keep it up.

  • Jarrod

    Thanks mate! Worked like a charm!

  • http://gainwithoutpain.wordpress.com/ Maria

    Thanks for the tips! That darn SQLite had me stalled too.

  • ben

    Thanks… really helpful.. to newbie in ruby

  • nico

    Thank this has helped me a lot.

  • James Stanley

    It’s not “called dl” dl stand for dyanmic library. dlopen and it’s ilk are standard library routines. Change the first sentence after the error message to something like “That’s becasue the dlopen rountine can’t find the dll in it’s search path” or something along those lines and you’re good.

  • S

    Thanks a lot for this article!

    It really has saved me from some unnecessary hastle =)

  • Bouallagui Moncef

    Thanks for the tips

    just one thing: instead of [gem install sqlite3-ruby] you should use [gem install sqlite3], the gem name has been updated from sqlite3-ruby to sqlite3.

  • Shwetank Gupta

    Thanks buddy, It really helps in my Sqlite installation