Ruby on Rails
HowtoUseSQLite

SQLite is a lightweight SQL-compliant database. The download is about 244kb (command-line client + DLL).

SQLite implements most of the SQL92 and needs no configuration. SQLite is a client-only solution that does not require a separate server process.

If using a Rails version before 1.2.2, use SQLite 3.3.7 for better results, as there is an incompatibility

Step 1 – Install SQLite

Windows

To use SQLite for windows you need two files:

  1. SQLite DLL
  2. SQLite command-line client for creating tables

These can be downloaded as precompiled binaries at SQLite’s homepage

Once you’ve unzipped your downloads, copy the three files to your Ruby bin directory (typically C:\ruby\bin).

Add sqlite(3).exe and sqlite(3).dll to your path.

Watch this screencast for a step-by-step demonstration. Don’t forget to restart your server after you complete the installation.

Unix

Most package managers put an sqlite or sqlite3 binary in your path.
If you built from source, try ‘/usr/local/bin/sqlite(3)’.

If you have not built from source install the sqlite3-dev package as well. It might be called libsqlite3-dev on your system.

If you need to store UTF-8 data in your database make sure to supply —enable-utf8 to the ./configure script"

Mac OS X (up till version 10.3.9)

with DarwinPorts:
<pre> sudo port install sqlite3 sudo port install sqlite # for sqlite 2 </pre>

Mac OS X 10.4 (aka Tiger) and beyond

sqlite3 is part of Mac OS X 10.4. Its path is ‘/usr/bin/sqlite3’. You do not need to install it (but see below).

Note: this version of SQLite doesn’t support the “drop table if exists foo;” syntax, but this is only a minor problem.

Step 2 – Get the sqlite ruby gem

Use gem to install sqlite for Ruby.

Windows

<pre>C:\work\rb\test>gem install sqlite-ruby

C:\work\rb\test>"c:\ruby\bin\ruby.exe" “c:\ruby\bin\gem” install sqlite
Attempting local installation of ‘sqlite’
Local gem file not found: sqlite*.gem
Attempting remote installation of ‘sqlite’
Select which gem to install for your platform (i386-mswin32)
1. sqlite-ruby 2.2.3 (ruby)
2. sqlite-ruby 2.2.3 (mswin32)
3. sqlite-ruby 2.2.2 (ruby)

>

Select option 2.

When using SQLite 3:
<pre>C:\work\rb\test>gem install sqlite3-ruby

C:\work\rb\test>"c:\ruby\bin\ruby.exe" “c:\ruby\bin\gem” install sqlite3
Attempting local installation of ‘sqlite3’
Local gem file not found: sqlite3*.gem
Attempting remote installation of ‘sqlite3’
Select which gem to install for your platform (i386-mswin32)
1. sqlite3-ruby 1.1.0 (mswin32)
2. sqlite3-ruby 1.1.0 (ruby)
3. sqlite3-ruby 1.0.1 (ruby)

>

Select option 1.

UNIX and Mac OS X

_Note: SWIG used to be required for the sqlite3-ruby gem, however as of version 1.20.0 it is no longer required:
_

Install the gem as follows:

sudo gem install sqlite3-ruby
Unless you get this (because you typed sqlite3 instead of sqlite3-ruby):

  Attempting local installation of 'sqlite3'
  Local gem file not found: sqlite3*.gem
  Attempting remote installation of 'sqlite3'
  ERROR:  While executing gem ... (Gem::GemNotFoundException)
    Could not find sqlite3 (> 0) in the repository

You will see the following:

Select which gem to install for your platform (powerpc-darwin8.0)
 1. sqlite3-ruby 1.2.1 (mswin32)
 2. sqlite3-ruby 1.2.1 (ruby)
 3. sqlite3-ruby 1.2.0 (mswin32)
 4. sqlite3-ruby 1.2.0 (ruby)
 5. Skip this gem
 6. Cancel installation

Choose the highest-numbered “(ruby)” version, in this case option 2.

Also if this fails make sure you have the Ruby dev package as ‘mkmf’ is needed from it.

Creating a database

SQLite stores databases in files. To create a new database, run the sqlite command with a filename for your database, and start adding tables.

Note that it may not be necessary to create the database explicitly. With sqlite3 on Windows at least, setting up the configuration and running rake db:migrate creates the db. Please confirm for other setups.

You don’t have to create the database on Mac OS X either. Just edit your database.yml and run rake db:migrate. Verified for Mac OS X 10.4 and Rails 1.2.3.

Same on Linux (Ubuntu). It only creates the database your are configured to be running though. So if you’re set up for development, you don’t get the files for testing or production.

Windows

<pre>C:\work\rb\test>sqlite db\test.db SQLite version 2.8.16 Enter ".help" for instructions sqlite> create table articles ...> (id integer primary key, ...> title varchar(255), ...> text varchar(1024) ...> ); sqlite> .quit (or create table articles (id integer primary key, title varchar(255), text varchar(1024) );

C:\work\rb\test>dir db
Volume in drive C has no label.
Directory of C:\work\rb\test\db
14.04.2005 14:14 4 096 test.db
1 File(s) 4 096 bytes
C:\work\rb\test>

Unix and Mac OS X

In a UNIX system, and provided that you have a file consisting of the SQL creation statements for this database (for example from an application you are deploying), you can shortcut the creation operation:
<pre> $ mkdir ~/databases $ sqlite3 ~/databases/rails-app.db < /path/to/schema.sql

SQLite version 3.1.2
Enter “.help” for instructions
sqlite> .schema

# Prints the schema

sqlite> .quit

Configure database.yml to use sqlite

SQLite does not use authentication and needs only a pointer to the database file.
The adapter parameter tells Ruby to use SQLite for a database.

<pre>development: adapter: sqlite dbfile: db/dev.db

test:
adapter: sqlite
dbfile: db/test.db

production:
adapter: sqlite
dbfile: db/prod.db

Please note: If you are using SQLite3 use “sqlite3” instead of “sqlite” for the value of the adapter parameter.

And you are done!

Possible Gotchas

Q: I get errors when trying to install the gem…

sudo gem install sqlite
Attempting local installation of ‘sqlite’
Local gem file not found: sqlite*.gem
Attempting remote installation of ‘sqlite’
Building native extensions. This could take a while…
ERROR: While executing gem … (RuntimeError)
ERROR: Failed to build gem native extension.
Gem files will remain installed in /usr/lib/ruby/gems/1.8/gems/sqlite-2.0.1 for inspection.
ruby extconf.rb install sqlite
checking for main() in -lsqlite… no
checking for sqlite.h… no

Any clues? I did install swig as well as sqlite via DarwinPorts already, which puts stuff in /opt/…

%{color:green} I had the same problem. Use
gem install sqlite-ruby -- --with-sqlite-dir=/opt/local — Kanwei %

A: There are several possible reasons; if you don’t have Xcode installed the ruby.h header file won’t be found. If you have Xcode 2.2 on Tiger 10.4.3 you’ll find the ruby.h header file has moved from /usr/lib/ruby/1.8/powerpc-darwin8.0/ruby.h to /usr/lib/ruby/1.8/universal-darwin8.0/ruby.h. As a result it isn’t being found by gem. Some people have found symlinks fix the problem, but many readers of comp.lang.ruby advocate installing ruby from source as the install of ruby that comes with Tiger is deemed to be slightly broken. — GrahamAshton

In Linux (SUSE 10), install ruby-devel (to get ruby.h) if not installed — Alberto

%{color:green}B: I had two versions of Ruby installed via DarwinPorts. Unfortunately rb-rubygems was linked to the older (1.8.2) one. My solution was to uninstall ruby (1.8.2 and 1.8.4) and rb-rubygems and reinstall them after swig. — Michael %

I got the exact same error, but it turned out that Ubuntu Breezy doesn’t install gcc by default, a quick

sudo apt-get install gcc
fixed that. Probaly not relevant to the above though since DarwinPorts is pretty useless without gcc

I have also received the same error (Ubuntu Breezy), but it was because the sqlite3.h was missing. You need libsqlite3-dev:

sudo apt-get install libsqlite3-dev

(This fixed my problem on Debian, too — Phillip)

Q: I am experiencing strange database errors on Linux/Unix

A: The sqlite gem is defaulting to a pure ruby version that doesn’t always work. The solution is:

  1. Uninstall the sqlite gem (sqlite-ruby or sqlite3-ruby).
  2. Install swig:through your operating system’s package manager or by downloading and compiling it.
  3. Re-install the sqlite gem and now it should actually compile the ‘native’ extension using the actual sqlite binary dll.

A: On Mac OS X 10.4 Tiger, try uninstalling the gem (sudo gem uninstall sqlite3), then use DarwinPorts to install SWIG (sudo port install swig), then re-install the SQLite3 gem (sudo gem install sqlite3).

Q: Windows version gives an error message like “Application failed to start because sqlite.dll was not found. Re-installing the application may fix this problem.”

A: Make sure the .dll and .exe are in your path. If you installed sqlite3 then use for example ‘sqlite3 db\test.db’ to create a database. In your database.yml you must use ‘adapter: sqlite3’.

Q: SQLite::Exceptions::\DatabaseException file is encrypted or is not a database.

A: It seems that sqlite databases created with version 2 do not work with sqlite version 3 and vice versa.

Q: SQLite returns “0.0” for values from database views.

A: Looks like Rails thinks the field is a float? You can put numbers in the fields, but strings show up as 0.0. Try adding the field specifier (ex. TEXT) in the database definition.

Q: Using SQLite in Rails terminates the webrick server by throwing segfaults such as “deadlock 0xb781a95c: run:-/usr/lib/ruby/1.8/drb/drb.rb:932: [BUG] Segmentation fault”. Any hints?

A: This is likely to be a problem with sqlite-ruby. Maybe you didn’t install it properly. See the question above about strange database errors. On Mac OS X 10.4 Tiger, try uninstalling the gem (sudo gem uninstall sqlite3), then use DarwinPorts or Fink to install SWIG (sudo port install swig), making sure SWIG is in your PATH, then re-install the SQLite3 gem (sudo gem install sqlite3).

%{color:red}Q: It seems is that ActiveRecord save method doesn’t set the id of the inserted row after saving the record to a SQLite database. Is this an issue with SQLite or with ActiveRecord

A: Make sure you have swig installed before installing sqlite3-ruby! See the question immediately above this one for instructions. I’ve had the exact problem and installing swig then re-install sqlite3-ruby did fix it. One note: I believe sqlite3-ruby is the preferred module (sudo gem install sqlite3-ruby).

Q: I am using Migrations and am having the same issue. ActiveRecord doesn’t set the id after a save. Checking the database schema, I have: “id” INTEGER PRIMARY KEY NOT NULL. Any suggestions?

Q: Same here – I’m using migrations to create a table and the id is not set after an insert. Migrations is creating the id column as INTEGER PRIMARY KEY NOT NULL. Is there a workaround?

A: The “id” field should be INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL – the database will then set it automagically. This is probably what ActiveRecord is expecting. See also SQLite create table and SQLite autoincrement

Q: WEBrick is complaining about “\ArgumentError: No database file specified. Missing argument: dbfile”

A: sqlite3 uses the parameter “dbfile:” in place of “database:”, which is easy to visually gloss over when editing a database.yml that is preconfigured for MySQL .

Q: Does ActiveRecord::Migration support SQLite?

A: Migrations appear to work fine with SQLite in Rails 0.14.0 and later. SQLite migrations are not supported in prior versions of Rails.

Q: I am a newbie so excuse this question. Do I have to use the gem version of Ruby-Sqlite, or will a non-gem installed ruby-sqlite work too? (I am on *_nix*)

A:

Q: I keep getting this error: ./sqlite3.dll: 127: The specified procedure could not be found. – Init_sqlite3 (LoadError)

A:

On Ubuntu

Here’s what worked for me:
Ubuntu 6.06LTS:

sudo apt-get install libsqlite3-0 sqlite3 swig libsqlite3-ruby

Pre Ubuntu 6.06LTS:


sudo apt-get install libsqlite3-0 libsqlite3-dev sqlite3 swig
sudo gem install sqlite3-ruby

  1. Select the highest Ruby version (ususally 1.)

If you don’t install the libsqlite3-dev package, then you won’t have the header files you need to compile the native extension when you install the Rubygem.

    Hope that helps.

    Debian sid

    To install Ruby on Rails in Debian sid do:

    apt-get install rails sqlite3 sqlite3-ruby
    

    {color:blue} Note: you’ll probably need to add libsqlite3 and libsqlite3-dev to that — Phillip

    Debian etch


    apt-get install sqlite3 libsqlite3-0 libsqlite3-ruby libsqlite3-dev libsqlite3-ruby1.8

    You need to use sqlite3 as adapter in config/database.yml file.
    ::
    category:HowTo

    If you get other weird errors, perhaps you are using MySQL syntax (instead of “standard” SQL) for creating the tables. The example in this page worked for me on Rails. – Max

    Example of weird error:

    SQLite3::SQLException: SQL logic error or missing database: INSERT INTO
    

    An alternative possible solution to this kind of error is to check the ownership of your database file, it appears that at least sometimes (i.e. rails 1.1 sqlite 3.2.8) that the writing process (i.e. apache/webbrick) must own the database file (i.e. write permission is not sufficient) I suppose this has something to do with locking – occ

    Q: Please excuse my newbie question ! My yaml.rb returns argument error for “adapter: sqlite3”. I have both sqlite3 and sqlite installed. And the database.yml is configured as: development:adapter: sqlite3 dbfile: db/temp.db

    A: I had similar problems with yaml – it turned out to be a tab in the file, make sure you only have spaces and no tabs

    Q: I’m getting deadlocks and application error exceptions when using SQLite. How can I fix this?
    A: Look at this Trac ticket:
    http://dev.rubyonrails.org/ticket/6126/

    Apparently it wasn’t enough just to have this package installed (it had already been installed):


    sqlite.i386 3.3.3-1.2

    Q: On Mac OS X (10.4/Tiger), I’m getting an error during migrations:

    SQLite3::SQLException: near “ADD”: syntax error: ALTER TABLE a_table_name ADD “a_field_name” a_type

    WTF?

    A: The sqlite3 install shipped with OS X 10.4 (3.1.3) leads to migration errors when used out-of-the-box with sqlite3-ruby-1.2.1, Rails 1.2.4, and ActiveRecord 1.15.4 (these are my exact version numbers, I suspect either Rails or sqlite3-ruby as the incompatible gems). SWIG may solve the problem; what I did was uninstall sqlite3-ruby, install sqlite3 3.3.17 from souce (with the defaults, it installed to /usr/local so other OS X stuff using it — Safari, for instance - would continue to work), and reinstall the sqlite3-ruby gem. Now migrations work. (In my desperation, I also un and re-installed all my gems, but I’m confident that rebuilding sqlite3-ruby against sqlite3 3.3.17 is what fixed it.)

    I had the above problem with Rails 2.0.2 and sqlite3 3.1.2, but the same procedure (compiling sqlite3 3.5.4 from source, removing the sqlite3-ruby gem, and reinstalling it) worked for me. I didn’t uninstall and reinstall all my gems, just sqlite3-ruby. —Ben Kimball

    Q: Running my Rails application via Webrick works fine, but when I use CGI/Apache, I get a dreadful “SQLite3::SQLException: SQL logic error or missing database” error. What gives?

    A: The user Apache is running as needs write permissions not only to the database file itself but also to the directory the file resides in.

    Q: Has anyone figured out a work-around for the sql size limit in Sqlite3?

    I can’t store more than 1mb in my sqlite db. The reason is that there is a 1mb limit to the length of the sql that sqlite accepts. Using sqlite from c, you would bind the larger pieces of data (i.e. “data = ?”). That reduces the size of the sql allowing you to store up to 1gb of data.

    Does anyone know how to work around this w/o changing ActiveRecord::Base?

    Q: How do I install sqlite3-ruby on linux (CentOS) as a non-root user?

    A: