migrations

[Nareshix]

SQLite has very little built-in schema alteration support, and so Django attempts to emulate it by:

Creating a new table with the new schema

Copying the data across

Dropping the old table

Renaming the new table to match the original name

migrations shuold be done atomically, hence in a transasction

SQLite only allows very limited changes to be done to an existing table. For anything else, you should:

rename the old table,
make a new table with the old name and with the correct column definitions,
copy the data over, and
drop the old table.
Do this within a single transaction, of course! Also, practice on another database before trying this with your production data.

https://www.sqlite.org/lang_altertable.html
https://docs.djangoproject.com/en/6.0/topics/migrations/

https://github.com/rust-db/refinery

[Nareshix]

For not production-ready cases, I meant using only 1 SQL file won't cut it for migration. I agree we can look at something like refinery to run SQL migration files, but I think sqlitex would be the perfect SQLite library for me if it can handle migration as well. My suggestion would be doing something like #[sqlitex("migration_folder/"), where it would load all SQL files alphabetically, then parse them one by one (for example, the folder has 01_table_create.sql, 02_table_update.sql, then sqlitex would parse 01_... first, then 02_... after).

[Nareshix]

look at how sqlx does it