The user will have an SQLite database on their file system. Now I update my application code to add another column, or a whole relationship, or relax a constraint. Do I create a migration script that the app executes on startup? Do I add some code that tries to figure it out depending on the database library I use?
Do I add some code that tries to figure it out depending on the database library I use?
Probably not e.g. if dropping a constraint the only way it could automatically determine the constraint to be dropped is by comparing the old and new schema by opening both databases and executing statements to check.
Do I create a migration script that the app executes on startup?
Probably yes.
You may also wish to utilise the user_version, a 4 byte integer in which you could store a version number. As the value is stored in the header (first 100 bytes of the file), this value can be extracted and compared against a static compiled expected version number prior to going to the relative expense of opening the database and thus if the version stored in the header is the same as the expected version the migration can be skipped.