Hytak

What I think is that you are confusing the schema_version and user_version pragmas.

user_version is what you would use to keep track of which migrations have run and it can be safely updated (or not updated) without corrupting the database.

schema_version is the thing that sqlite updates automatically on every single change to the schema. sqlite uses schema_version to make sure that prepared statements don't accidentally run on a different schema than what they are prepared for. schema_version is also the thing that rust-query uses to make sure that the schema hasn't changed since the application started.

You can see now that you can not trick rust-query without also tricking sqlite (and risking corruption if there was a prepared statement).

If you shutdown the application before you change the schema_version, then rust-query will just see that the schema is different when the application is started. Because rust-query always reads the full schema and compares it to the expected schema on application start!

manually updating the schema_version can lead to database corruption https://www.sqlite.org/pragma.html#pragma_schema_version

If you are willing to risk corrupting the database, then yes you can trick rust-query

Author here: I just wanted to point out that rust-query does in fact read the schema from the database and compares it to the expected schema. There is no checksumming or comparison of migrations. It is checked efficiently that the schema hasn't changed at the start of every transaction by just checking the `schema_version` sqlite pragma.

https://news.ycombinator.com/item?id=42283030

sqlite has `STRICT` tables that are statically typed

You (and many other commenters) are right that rust-query currently requires downtime to do migrations. For many applications this is fine, but it would still be nice to support zero-downtime migrations

Your argument that the application should be the authority on the interface it expects from the database makes a lot of sense. I will consider changing the schema check to be more flexible as part of support for zero-downtime migrations.

Sending row IDs to your frontend has two potential problems:

- Row IDs can be reused when the row is deleted. So when the frontend sends a message to the backend to modify that data, it might accidentally modify a different row that was created after the original was deleted.

- You may be leaking the total number of rows (this allows detecting when new rows are created etc. which can be problematic).

If you have nothing else to uniquely identify a row, then you can always create a random unique identifier and put a unique constraint on it.

Hi, migrations are 1 select statement + `n` insert statement for `n` rows right now.

This might be improved to insert in batches in the future without changing the API.

rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty).

Furthermore, at the start of every transaction, rust-query will check that the `schema_version` (sqlite pragma) did not change. (source: I am the author)