How to plan schema changes in a SQLite database?

Question

I am currently developing an application that will store data in a SQLite database. A database will have much more read capabilities than writes (in fact, it will be populated with data once, and then there will be almost only reads). Therefore, reading performance is very important. The scheme that I am currently developing is likely to change in the future by adding additional columns and tables. I do not have much experience with databases in general. My question, particularly in SQLite, are there any pitfalls to consider when changing a schema? Are there any patterns or best practices for planning ahead for such cases?

Answer 1

Here are some suggestions:

• Do not use select * from ... because the value of * changes with schema changes; explicitly specify the columns in which your query is used.
• Save the version number of the circuit in the database and save the code in the application for conversion from the circuit version N to version N + 1; then all the code in the application works with the latest version of the circuit; this may mean that there are default values ​​to populate the added columns.
• You can avoid copying tables for schema updates using SQLite version 3.1.3 or higher, which supports ALTER TABLE ADD COLUMN...

Answer 2

Look at the data structure and the scheme of the scheme of stars. This may be redundant for your situation, but at least it will prevent accidental design.