Beware of SQLite for Agile Development

SQLite’s feature list is seemingly perfect for exploratory development and conceptual proofs. SQLite is lightweight with no configuration required. The database is stored in a local file that is easily portable* between systems. The data requirements for prototypes, blogs, and pet frog web apps^† are well within the capabilities of SQLite. The single-access model also works well with web apps where all of the logic lies in the application layer.

Migration Pitfalls

Initial development and prototyping involves quick changes to your database schema. This is the turn in the road where SQLite slips and falls down. The ALTER TABLE syntax can change the name of a table and add a column but cannot rename, drop, or redefine columns (to change the data type, for instance). This failure breaks Rails migrations.

These problems can be worked around: you can create a new table, INSERT a SELECT containing the migration logic on the old table, drop the old table, and then rename the new one. This procedure is overly haphazard but gets the job done.

If you are already hacking web apps then you probably have the ability to install and configure a real database. If you are on a single-user development machine that is otherwise secure you’ll be fine with the default trust permissions on database access from localhost. Once you’ve set up the database cluster once, creating a new database is as easy as createdb -U postgres NEWDATABASE.

While you are prototyping an app it is easy enough to blow away your database and start over. Equally easy is to find your under-development database suddenly filled with real data.

The logic of most web apps is simple and easy to reverse engineer by using the app, so the most important part is your data. Don’t store it somewhere that is going to become a source of pain later on. All the agility in the world won’t help you if you have your legs tied in a knot.