Well, That Didn’t Last Long. Bye, SQLite.

I recently started a new project in Rails 3.0 and decided to try SQLite as the development database. In the past I’ve always used MySQL for my Rails projects. I’m hosting the app on Heroku so it’s PostgreSQL in production, making this my first time using SQLite and my first time using different databases in development and production.

At first I was having fun with SQLite. It’s lightweight, doesn’t use much memory, runs fast on a local dev machine. A single database is stored in a single file. No server to configure, no nothing. Nice.

But I gave up on it after about five days because of an annoying limitation that bit me twice: if you add a NOT NULL column (:null => false in your migration) to an existing table, SQLite requires you to specify a default value—even if the table is empty.

It makes sense to require a default value on a NOT NULL column if there are already rows in the table. All relational databases require that. But if you don’t want to specify a default and the table is empty SQLite shouldn’t force you to.

I found discussions where people suggested a workaround of “Can’t you just drop the table and create a new one? As the table is empty, you won’t be losing any data.” Sure, but it’s far more error-prone to recreate the table because it’s not an incremental change. You have to carefully reconstruct the table and all indexes from all past migrations, which became unwieldy for me even in a 5-day-old project. It really is more error-prone: I tried it and screwed up a couple of times. That was it for me, so I decided it’s time to switch back to MySQL for local development.

The other experiment—using different databases in local development and production—is still going fine. We’ll see what snags I hit that make me change my mind about that too. I still wouldn’t do that on a big project but this is a little ol’ thang.

2 thoughts on “Well, That Didn’t Last Long. Bye, SQLite.”

    1. Andrew, did you switch to postgres for dev or use mysql or some other db? Just wondering how people’s experiences are using different DBs for dev and prod. Installing postgres locally would be just one more thing to do since I already have mysql.

Leave a Reply

Your email address will not be published. Required fields are marked *

Feel free to use <a>, <b>, <i>, <strong>, <em>, <strike>, <code>.

Code blocks:
[code language="ruby/javascript/html/css/sass/bash"]
[/code]