How to get Ruby working with SQLite on Windows

I know, I know – it’s Windows. Step 1, buy a Mac. I do much of my development on a Mac, and it truly is much easier to set things up. Unfortunately, I don’t always have a choice where my code is developed or deployed. I recently needed to get SQLite working with my Ruby development environment on Windows XP. It was a huge pain that involved tracking down a few different errors, so I thought I’d list out what I came across to help out anyone else out there that is in the unfortunate situation of having to work with XP.

I had already installed SQLite3 on my dev box. You can find the precompiled binaries for Windows here: http://www.sqlite.org/download.html. You should make sure that SQLite is installed and working properly. You can do this from your command prompt by simply creating a new database file and try creating a table.
sqlite3 test.db
CREATE TABLE test (col_one INT, col_two VARCHAR(20));
INSERT INTO test VALUES (1, 'some test string');
SELECT * FROM test;
If you can execute, these basic commands, then you know your SQLite installation is working properly.

Now, on to getting your Ruby code to work with SQLite3. So, naturally I thought I could simply install the ruby gem needed for working with SQLite. 
gem install sqlite3-ruby
But, instead of an installed gem, I received the following error message:
gem.bat install sqlite3-ruby
ERROR:  Error installing sqlite3-ruby:
        ERROR: Failed to build gem native extension.

C:/Ruby/bin/ruby.exe extconf.rb install sqlite3-ruby
checking for fdatasync() in rt.lib... no
checking for sqlite3.h... no

Gem files will remain installed in C:/Ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4 for inspection.
Results logged to C:/Ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4/ext/sqlite3_api/gem_make.out
Building native extensions.  This could take a while...


This is a misleading error. One would think the necessary solution would involve installing nmake on your Windows system. Well, you can certainly do that, but it will not help the problem. NMake is a useful tool though, if you want to get it, here’s the link to the Microsoft download site: http://download.microsoft.com/download/vc15/Patch/1.52/W95/EN-US/Nmake15.exe. If by chance you do try installing the gem after setting up nmake, you will likely see an error message like this one:

Building native extensions.  This could take a while...

ERROR:  Error installing sqlite3-ruby:
        ERROR: Failed to build gem native extension.

Gem files will remain installed in C:/Ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4 for inspection.
Results logged to C:/Ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4/ext/sqlite3_api/gem_make.out

 
As you can see, this time it was able to find nmake, but nmake threw a fatal error. 

So, finally, here’s your solution. You have to use the Windows gem for sqlite3 and not the standard gem. You need to download it from the rubyforge site and run gem install from the local file. Go here to get the gem: http://rubyforge.org/projects/sqlite-ruby/
Go to the ‘download’ link next to sqlite3-ruby
Find the latest *-mswin32 version (I got:  sqlite3-ruby-1.2.3-mswin32.gem) and download it.
In your cmd prompt, go to the directory where the downloaded gem is stored and type:
gem install sqlite3-ruby-1.2.3-mswin32.gem

This will install the gem on your system. You should now be able to access the SQLite 3 database from your Ruby code. If you found this information helpful, please let me know.