Fun fact - you don't even need to maintain a special unicorn "_migrations" table or any other external state to keep track of things with SQLite migration. You can simply utilize the user_version pragma:
We have a DatabaseVersion constant in the classes that own each type of SQLite database, and all they have to do at ctor time is query the database for current version and run a for loop over the difference to execute the required migration scripts.
Our migrators are one-way (we don't define a matching 'down.sql'). We would push new code to back something out of a SQL schema and then increment our counter just like if we added something new. Decrementing/skipping is disallowed since this would cause information loss on the migration path. Having a monotonic version number per type of database makes it super easy to keep everything on rails for us.
For database access, we also use raw SQL via Dapper.
Fun fact - you don't even need to maintain a special unicorn "_migrations" table or any other external state to keep track of things with SQLite migration. You can simply utilize the user_version pragma:
https://sqlite.org/pragma.html#pragma_user_version
We have a DatabaseVersion constant in the classes that own each type of SQLite database, and all they have to do at ctor time is query the database for current version and run a for loop over the difference to execute the required migration scripts.
Our migrators are one-way (we don't define a matching 'down.sql'). We would push new code to back something out of a SQL schema and then increment our counter just like if we added something new. Decrementing/skipping is disallowed since this would cause information loss on the migration path. Having a monotonic version number per type of database makes it super easy to keep everything on rails for us.
For database access, we also use raw SQL via Dapper.