Migrations

In SqliteMagic migrations are supported using migration scripts. For each database version there has to be a file named <db version>.sql in the assets folder containing all the SQL for migrating schema to defined version. When database connection is opened with a new version, all needed migration scripts are run inside a transaction.

Example:

../assets/4.sql

CREATE TABLE IF NOT EXISTS user_tmp (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, gender INTEGER, birth_date INTEGER)
INSERT INTO user_tmp (id, first_name, last_name) SELECT id, first_name, last_name FROM user
DROP TABLE user
ALTER TABLE user_tmp RENAME TO user
ALTER TABLE book ADD COLUMN sub_title TEXT

Note: for this example there also has to be files named "2.sql" and "3.sql" in the assets folder.

When writing migration scripts, it is very important to preserve the order of columns the same as in the table object. SqliteMagic relies on the order of columns when parsing objects and when the schema differs from the table objects, wrong values end up in the wrong columns. For inspecting the generated table schema see SqliteMagic_<table_name>_Handler#TABLE_SCHEMA.