How to Use SQLite in Ruby on Rails

昵称34752 2007-07-05

展开全文

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.

For best results, use SQLite 3.3.7. Later versions (3.3.8) introduce an incompatibility, see here
(Not true as of Feb 6, 2007—see Rails 1.2.2 )

Step 1 – Install SQLite

Windows

To use SQLite for windows you need two files:

SQLite DLL
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.

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


sudo port install sqlite3
sudo port install sqlite # for sqlite 2

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.

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


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:

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 1.

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.

Windows


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:

$ 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.


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/...

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

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

“deadlock in timeoute.rb” error (usually through webrick)
“non-existent database or invalid SQL” error (usually through fcgi)

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

Uninstall the sqlite gem (sqlite-ruby or sqlite3-ruby).
Install swig through your operating system’s package manager or by downloading and compiling it.
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).

Q: It seems is that ActiveRecord’s 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)

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
# 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

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

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./ticket/6126/

Q: I’m getting this error: “libsqlite3.so: cannot open shared object file: No such file or directory”

On our Fedora server, we resolved that error by doing