Hacker News Reader

Comment by weiznich | Hacker News Reader

I just want to point out that reading the value of this pragma is not the same as verifying that the schema hasn't change, as this does not guard you against manual modifications of either the value of the pragma (after all I can just do a `pragma set schema_version=42`) or the schema itself (I can also manually change the schema, without changing the value of the pragma).

So while this is a nice way to verify which of your migrations have been applied this doesn't give any guarantees around the schema at all. Essentially this is the same as what diesel does with reading the `__diesel_migration_version` table.

View on HN

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

As pointed out in the parent comment you do not need to update this pragma to create a mismatch between the expected schema and the actual schema. The point here is that this is not a sufficient safe way to ensure the schema is what you actually expect, but merely a check about which migrations are applied and which not. That's not different to what diesel does, beside the fact that you run the check on each transaction (which comes with the cost of an additional "query") instead of once at startup.

To actually check if the schema matches what you expect you need to query information about all relevant table and compare them with the expected state.

EDIT: I would also like to point to the [linked documentation above](https://www.sqlite.org/howtocorrupt.html#cfgerr) that explicitly states the following:

> Changing the PRAGMA schema_version while other database connections are open.

So you don't risk any corruption (from SQLite's point of view) if there is no other database connection open, which essentially means you just need to shutdown your application first.

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!

Again the assumption that no change in `schema_version` means that the observed schema stayed the same is not correct. One counter example is creating a temporary table. This doesn't increase the `schema_version` flag, but it allows you to shadow any existing table with whatever different structure you like. That's connection specific behavior, so you won't be able to observe that from other database connections. As soon as you allow users to execute arbitrary SQL (which you definitively want as you cannot reasonably provide a DSL for all supported SQL constructs) it's possible to break you assumption in that way.

Reply on news.ycombinator.com