Setting up SQLite3 for Ruby on Rails Development

UPDATE: Thanks to an astute reader, it seems to have full SQLite3 support on Windows you need to add the SQLite3.dll and the SQLite3.def files also in your path.  I put these in my ruby/bin folder, which is where they will be in the Instant Rails release coming up.

The release of Ruby on Rails 2.0.2 made some fairly significant changes in the default database for Rails applications.  The default, and standard, database has always been MySql but the Rails team felt it easier for development to use SQLite3 and make it the new default.

Setting up SQLite3 is pretty straight forward both on Windows and the Mac OSX, both Tiger and Leopard come with SQLite3 already installed so that makes the Mac one up on Windows.  My Tiger installation had SQLite3 v3.13 installed on it so I wanted to make sure I had the latest at the time of this writing and put 3.5.4 on it.

Installing on Windows

The installation on Windows was really straight forward.  The main thing is to download the pre-compiled version of SQLite3 3.5.4 from the SQLite web site, unzip it and copy the single sqlite3.exe to my ruby/bin directory.

Now that SQLite3 is installed, just install the gem:

gem install sqlite3-ruby

And that’s it.

Installing/Updating on the Apple Mac OSX

Upgrading my Rails installation was a breeze on my MacBook Pro, after upgrading to RubyGems 1.0 the Rails 2.0.2 install went through without a hitch.  The only real concern I had about upgrading to Rails 2.0.2 on the Mac was the fact the default database for 2.0.2 is SQLite3 and I had an old version.  I feel a bit indifferent about installing over versions of software installed by Apple in the event it is used by something and I break it.  I am pretty new to the Mac and fixing an issue like this can sometimes be intimidating.

I found an older version of SQLite3, here:

/usr/local/bin/sqlite3

There are several ways to update software like this on the Mac and using MacPorts seems to be a popular way to do it, but I chose to install from source.   I have a Source directory in my home folder on my Mac and I simply start everything from there.  So, to install I did the following:

$ curl -O http://www.sqlite.org/sqlite-3.5.4.tar.gz $ tar xzf sqlite-3.5.4.tar.gz $ cd sqlite-3.5.4 $ ./configure --prefix=/usr/local $ make $ sudo make install

After working with Windows systems for most of my professional career I chuckle at how cryptic the commands are in Unix-like environments.  Maybe this is to keep the barrier to entry high.

Using the “which sqlite3″ command should reveal the same location as the original SQLite3 installation.  If you actually type in “sqlite3″ you enter the command mode of SQLite3 and should see the new 3.5.4 version.

The last step to is to install the Ruby driver for SQLite3, accomplished with:

$ sudo gem install sqlite3-ruby

This installed without any problems and was good to go with using SQLite3 on my Mac Rails 2.0.2 installation.  It’s funny though, the Windows installation was very easy and the Mac install took a bit of work that was not particularly obvious.  I thought the Mac was supposed to be so much easier than the PC?  Isn’t this what the Apple ads try to get us to believe?  I guess Apple just has better marketing.

Technorati Tags: ,,,
  • http://www.nerdbucket.com Nerdmaster

    Quick note – if you haven’t installed the DLL for windows, you need to do that as well. The sqlite3.exe file alone wasn’t enough for me – I had to download the “sqlitedll-3_5_4.zip” file and unzip the .dll and .def files into C:\Windows\System32. Obviously any directory in your path will do.

  • http://www.nerdbucket.com/ Nerdmaster

    Quick note – if you haven’t installed the DLL for windows, you need to do that as well. The sqlite3.exe file alone wasn’t enough for me – I had to download the “sqlitedll-3_5_4.zip” file and unzip the .dll and .def files into C:WindowsSystem32. Obviously any directory in your path will do.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Nerdmaster – strange. I unzipped the sqlite3.exe and run it just fine without any additional dll’s.

    What application were you doing that required the .dll and .def files?

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Nerdmaster – strange. I unzipped the sqlite3.exe and run it just fine without any additional dll’s.
    What application were you doing that required the .dll and .def files?

  • bosmeeuw

    Hello Rob, please don’t bash “Unix-like systems” because you only know about Windows. For starters, compiling a tar-ball this way really is obvious when you have even the smallest amount of experience with the *nix CLI. What’s more, almost all *nix distros have decent package management built in. This would allow you to install sqlite (and automically keep it updated) with a simple command like “apt-get install sqlite3″ without any manual searching/downloading required.

  • bosmeeuw

    Hello Rob, please don’t bash “Unix-like systems” because you only know about Windows. For starters, compiling a tar-ball this way really is obvious when you have even the smallest amount of experience with the *nix CLI. What’s more, almost all *nix distros have decent package management built in. This would allow you to install sqlite (and automically keep it updated) with a simple command like “apt-get install sqlite3″ without any manual searching/downloading required.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Bosmeeuw – I am not bashing any Unix-like systems. I love working on my Mac and the Linux systems I maintain. I was only pointing out the steps I went through on both and then looked back and realized there was quite a few more steps in the Mac procedures.

    So if what I wrote seemed to bash, not meant to be. It was more like a tongue in check joke on how Mac folks always claim their process is so simple.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Bosmeeuw – I am not bashing any Unix-like systems. I love working on my Mac and the Linux systems I maintain. I was only pointing out the steps I went through on both and then looked back and realized there was quite a few more steps in the Mac procedures.
    So if what I wrote seemed to bash, not meant to be. It was more like a tongue in check joke on how Mac folks always claim their process is so simple.

  • Stoly

    Thanks for the helpful article. I chuckled at your ‘chuckle’ line myself, although the number of steps are identical in Linux as in OSX.

    @Bosmeeuw: OSX isn’t *nix, it’s UNIX. Subtle but important difference. There is no package management system for OSX. It’s POSIX, i.e. make; make install. `apt-get` is Debian-based, and while it’s *nix, it’s not UNIX. Of course, real Linux users use Slackware anyway, as you know. :-)

  • Stoly

    Thanks for the helpful article. I chuckled at your ‘chuckle’ line myself, although the number of steps are identical in Linux as in OSX.
    @Bosmeeuw: OSX isn’t *nix, it’s UNIX. Subtle but important difference. There is no package management system for OSX. It’s POSIX, i.e. make; make install. `apt-get` is Debian-based, and while it’s *nix, it’s not UNIX. Of course, real Linux users use Slackware anyway, as you know. :-)

  • January 1, 1970

    There are several ways to update software like this on the Mac and using MacPorts seems to be a popular way to do it, but I chose to install from source.

    For future reference, MacPorts builds things from source using a recipe you can control, and merely creates a way to track and manage installed software.

  • January 1, 1970

    There are several ways to update software like this on the Mac and using MacPorts seems to be a popular way to do it, but I chose to install from source.
    For future reference, MacPorts builds things from source using a recipe you can control, and merely creates a way to track and manage installed software.

  • J Garcia

    For all the Debian users that can’t live without apt-get, it is available for Mac OS X: http://www.finkproject.org/

    @Bosmeeuw: OSX isn’t *nix, it’s UNIX. Subtle but important difference. There is no package management system for OSX. It’s POSIX, i.e. make; make install. `apt-get` is Debian-based, and while it’s *nix, it’s not UNIX. Of course, real Linux users use Slackware anyway, as you know.

  • J Garcia

    For all the Debian users that can’t live without apt-get, it is available for Mac OS X: http://www.finkproject.org/
    @Bosmeeuw: OSX isn’t *nix, it’s UNIX. Subtle but important difference. There is no package management system for OSX. It’s POSIX, i.e. make; make install. `apt-get` is Debian-based, and while it’s *nix, it’s not UNIX. Of course, real Linux users use Slackware anyway, as you know.

  • bosmeeuw

    I’d say UNIX is covered under *nix – spot the wildcard!

    Rob: don’t worry, I’m not defending Mac users here :) But if you do a lot of server administration, you really should look into package management systems. Over time it can become hard to manage your system if you roll everything from source. Package management keeps track of dependencies, updates, etc.

  • bosmeeuw

    I’d say UNIX is covered under *nix – spot the wildcard!
    Rob: don’t worry, I’m not defending Mac users here :) But if you do a lot of server administration, you really should look into package management systems. Over time it can become hard to manage your system if you roll everything from source. Package management keeps track of dependencies, updates, etc.

  • yak-man

    Rob – sqlite3 runs fine; it’s when I try to run rake that the *.dll issue comes up… would be nice to include this with the gem (when getting it for win machines).

    Anyway, manually emptying the sqlitedll*.zip into the ruby/bin area does the trick…

    ah, manual configurations are _so_ much fun! *smirk*

    yak

  • yak-man

    Rob – sqlite3 runs fine; it’s when I try to run rake that the *.dll issue comes up… would be nice to include this with the gem (when getting it for win machines).
    Anyway, manually emptying the sqlitedll*.zip into the ruby/bin area does the trick…
    ah, manual configurations are _so_ much fun! *smirk*
    yak

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Yak-man – thanks for the info, I wasn’t sure when the .dll was needed. I hadn’t seen an issues with experimenting with it. I am glad for the info as I put SQLite3 into InstantRails.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Yak-man – thanks for the info, I wasn’t sure when the .dll was needed. I hadn’t seen an issues with experimenting with it. I am glad for the info as I put SQLite3 into InstantRails.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Bosmeeuw and others. What is the “best” way to manage packages on the Mac? I have used MacPorts but didn’t know if that was the standard Mac package management system. I have used some others on Linux but what do most people use? I know it varies based on Linux distribution but I am curious.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Bosmeeuw and others. What is the “best” way to manage packages on the Mac? I have used MacPorts but didn’t know if that was the standard Mac package management system. I have used some others on Linux but what do most people use? I know it varies based on Linux distribution but I am curious.

  • http://galaxyelf.com/blog/2008/01/19/rails-202-uses-sqlite3-by-default/ Handy Patriawan’s Blog » Blog Archive » Rails 2.0.2 Uses SQLite3 by Default?

    […] I used Google to search for the change and found Rob’s post. […]

  • http://galaxyelf.com/blog/2008/01/19/rails-202-uses-sqlite3-by-default/ Handy Patriawan’s Blog &

    […] I used Google to search for the change and found Rob’s post. […]

  • http://www.flexlive.net/?p=80 FlexLive.net » Flex + Ruby on Rails Installation

    […] Ruby on Rails. Make sure you also configure SQLite3 for […]

  • http://www.flexlive.net/?p=80 FlexLive.net » Flex + Ru

    […] Ruby on Rails. Make sure you also configure SQLite3 for […]

  • Mikey

    Thanks for this, it really helped me. Although this is probably a bit old, but as with Nerdmaster, I also needed to put those dll files in the System32 dir, for the sqlite3 to properly work. Otherwise the rails application doesn’t work, for instance the default //localhost:3000/ page’s “About you application’s environment” link doesn’t work properly, and it comes up with an UNKNOWN ERROR and a full stack trace.

    Thought I would report this, for other newbies experimenting with RoR! A simple but effective guide, thanks!

  • Mikey

    Thanks for this, it really helped me. Although this is probably a bit old, but as with Nerdmaster, I also needed to put those dll files in the System32 dir, for the sqlite3 to properly work. Otherwise the rails application doesn’t work, for instance the default //localhost:3000/ page’s “About you application’s environment” link doesn’t work properly, and it comes up with an UNKNOWN ERROR and a full stack trace.
    Thought I would report this, for other newbies experimenting with RoR! A simple but effective guide, thanks!

  • Mikey

    Ah! I missed out Yak-man’s comment… So there you go it is important to have those dlls!

  • Mikey

    Ah! I missed out Yak-man’s comment… So there you go it is important to have those dlls!

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Mikey Thanks for updating your experience, all great to know. Yes, I had updated my original post saying the files were needed. At first I wasn’t sure but they are definitely needed.

  • http://rbazinet.wordpress.com/ Rob Bazinet

    @Mikey Thanks for updating your experience, all great to know. Yes, I had updated my original post saying the files were needed. At first I wasn’t sure but they are definitely needed.

  • http://scottmotte.com/archives/77 Scott Motte » Blog Archive » Getting sqlite3 to work on Tiger

    RE: Setting up SQLite3 for Ruby on Rails Development

    Pingback from Scott Motte » Blog Archive » Getting sqlite3 to work on Tiger

  • http://scottmotte.com/archives/77 Scott Motte » Blog Archiv

    RE: Setting up SQLite3 for Ruby on Rails Development

    Pingback from Scott Motte » Blog Archive » Getting sqlite3 to work on Tiger

  • Archana

    Thanks so much for this article. In order to make my ruby program work with sqlite, even I had to download sqlitedll-3_6_1.zip separately from sqlite.org site and put the extracted sqlite3.def & sqlite3.dll files in the path. This worked even though I had installed the sqlite-ruby gem already.

  • Archana

    Thanks so much for this article. In order to make my ruby program work with sqlite, even I had to download sqlitedll-3_6_1.zip separately from sqlite.org site and put the extracted sqlite3.def & sqlite3.dll files in the path. This worked even though I had installed the sqlite-ruby gem already.

  • Jordan Schroeder

    Not so simple for me! I got the exe, dll and def files, put in the /bin dir. But, when I run:
    gem install sqlite3-ruby
    I get:
    Building native extensions. This could take a while…
    ERROR: Error installing sqlite3-ruby:
    ERROR: Failed to build gem native extension.
    C:/RubyOnRails/bin/ruby.exe extconf.rb install sqlite3-ruby
    checking for fdatasync() in rt.lib… no
    checking for sqlite3.h… no
    nmake
    ‘nmake’ is not recognized as an internal or external command,
    operable program or batch file.
    Help?

  • Jordan Schroeder

    Not so simple for me! I got the exe, dll and def files, put in the /bin dir. But, when I run:

    gem install sqlite3-ruby

    I get:

    Building native extensions. This could take a while…

    ERROR: Error installing sqlite3-ruby:

    ERROR: Failed to build gem native extension.

    C:/RubyOnRails/bin/ruby.exe extconf.rb install sqlite3-ruby

    checking for fdatasync() in rt.lib… no

    checking for sqlite3.h… no

    nmake

    ‘nmake’ is not recognized as an internal or external command,

    operable program or batch file.

    Help?

  • http://www.awmckinley.net/ Adam McKinley

    Hey Jordan!
    I had the same problem. It looks like (though I’m not sure) there is currently not a "win32" build available for the latest version (1.2.4) from the Ruby Gems repository.
    Try this (worked for me):
    gem install sqlite3-ruby -v 1.2.3

  • http://www.awmckinley.net/ Adam McKinley

    Hey Jordan!

    I had the same problem. It looks like (though I’m not sure) there is currently not a "win32" build available for the latest version (1.2.4) from the Ruby Gems repository.

    Try this (worked for me):

    gem install sqlite3-ruby -v 1.2.3

  • jeet

    Hi guys i have exe, dll everything….i tried 1.2.3 version also as mentioned above…but no luck…i am getting Failed to build gem native extention

  • jeet

    Hi guys i have exe, dll everything….i tried 1.2.3 version also as mentioned above…but no luck…i am getting Failed to build gem native extention

  • http://www.prettybit.com/ Ivan

    Adam, thanks! your solution worked for me on Vista.
    I’m using Ruby 1.9.1

  • http://www.prettybit.com Ivan

    Adam, thanks! your solution worked for me on Vista.

    I’m using Ruby 1.9.1

  • Gagan

    i am using ruby 1.8.6
    rails 2.3.2
    i have a problem installing sqlite 3 for windows
    i copied the three files sqlite3.def ,sqlite3.dll and sqlite3.exe that i have downloaded from the sqlite official website, to my /ruby/bin folder
    then i tried /ruby/bin>gem install sqlite3-ruby
    but the same problem occur saying
    "no such file to load sqlite3"
    please help me to find the solution

  • Gagan

    i am using ruby 1.8.6

    rails 2.3.2

    i have a problem installing sqlite 3 for windows

    i copied the three files sqlite3.def ,sqlite3.dll and sqlite3.exe that i have downloaded from the sqlite official website, to my /ruby/bin folder

    then i tried /ruby/bin>gem install sqlite3-ruby

    but the same problem occur saying

    "no such file to load sqlite3"

    please help me to find the solution

  • Andrew Wilson

    Got this working on Windows Vista 64 – was originally getting "Internal Server Error" and "Unknown Error" as described above.
    The fix was to set the "Compatibility Mode" on sqlite3.exe (right click on file, click "Properites", click Compatibility tab) to "Windows XP SP 2".
    I suspect there was some error with the 32-bit DLLs on a 64-bit OS, as I didn’t have to do this on Vista 32.

  • Andrew Wilson

    Got this working on Windows Vista 64 – was originally getting "Internal Server Error" and "Unknown Error" as described above.

    The fix was to set the "Compatibility Mode" on sqlite3.exe (right click on file, click "Properites", click Compatibility tab) to "Windows XP SP 2".

    I suspect there was some error with the 32-bit DLLs on a 64-bit OS, as I didn’t have to do this on Vista 32.

  • http://www.casinoroulette4free.com/ free roulette casino online

    Fantastic tutorial. I love it when supersmart people are able to dumb stuff down for us smart people. Thanks for stepping me through this. Ruby on Rails is fun – but for us tweakers out there – getting things set up seems like 1000 steps. Your tutorial was crisp, clean, easy to read, logical, and … it worked. Thank you very much.

  • http://www.casinoroulette4free.com free roulette casino online

    Fantastic tutorial. I love it when supersmart people are able to dumb stuff down for us smart people. Thanks for stepping me through this. Ruby on Rails is fun – but for us tweakers out there – getting things set up seems like 1000 steps. Your tutorial was crisp, clean, easy to read, logical, and … it worked. Thank you very much.