How to plan for schema changes in an SQLite database?

Question

I am currently developing an application that will store data in an SQLite database. The database will have much more read- than write-access (in fact, it will be filled with data once, and then almost only reading will happen). The read-performance is therefore much mre important. The schema I am currently developing is very likely to change in the future, with additional columns and tables being added. I do not have very much experience with databases in general. My question is, specifically in SQLite, are there any pitfalls to be considered when changing a schema? Are there any patterns or best practices to plan ahead for such cases?

Answer 1

Here are some suggestions:

1. Don't use select * from ... because the meaning of * changes with schema changes; explicitly name the columns your query uses
2. Keep the schema version number in the database and keep code in the application to convert from schema version N to version N+1; then all the code in the application works with the latest schema version; this may mean having default values to fill added columns
3. You can avoid copying tables for schema updates with SQLite version 3.1.3 or better which supports ALTER TABLE ADD COLUMN...