While preparing my travel laptop for rails work, I decided to give SQLite a try. I figured, “no problem”, rails was simple enough to setup for MySQL, certainly it would be just as easy for SQLite. I was almost right.

After setting up the database.yml file to use sqlite3 instead of mysql, I started seeing this problem:

SQLite3::SQLException: near “ADD”: syntax error: ALTER TABLE blah blah blah

After some googling, I found this post at another blog site. Basically it suggests ensuring you have the latest sqlite3 installed and the latest sqlite3-ruby gem. After some port magic and a quick gem update, I was totally up to date.

But I STILL had the same problem! Where did I go wrong? I reread Mark Cornick’s post and noticed this little note at the bottom

BTW, hopefully everyone knows this by now, but sqlite3-ruby won’t even link against your libsqlite3 unless SWIG is installed. Without SWIG, you get an incredibly strange sqlite3-ruby that stopped living and became a crazy mixed-up zombie

SWIG? I had never heard of SWIG before. Well, I found it here. SWIG basically generates code that connects various high level languages (such as Ruby) to C/C++ libraries. I’m assuming this is how ruby creates it’s bindings to sqlite3.

Once I had SWIG installed, I did a quick uninstall and reinstall of sqlite3-ruby and I was in business, migrations and all.

For more info on installing SQLite for use with rails, check out this how to wiki page off the main rails wiki.