[weiznich]

> So is this the expected workflow?

It's impossible to write what is the expected workflow, because that heavily depends on your requirements. Overall there is certain functionality that exists in diesel and that can be combined in different ways to build different kind of workflows.

For example: If you have a database that is controlled by someone else you won't want to use any migration functionality at all, you would want to use only `diesel print-schema` there to generate the `schema.rs` file for you. Similarly different workflows consisting of any of the existing parts are possible.

> So, in this sense it seems very similar to SQLx.

There is an important difference here: Diesel provides a operate CLI to generate rust code for you, instead of connecting to a database from the "compiler" (or reading files). That's really important as you don't have any non-deterministic proc-macros, which are really not that great with the rust compiler (and officially something that's at least in some grey zone in terms of support).

> My take is that this is a "database-first" approach vs rust-query's "code-first" approach to safety. I think the benefits of a code-first design are that you don't need any additional CLI tooling, manual procedures outside of code, to test migrations, or a running database at any point in development.

This brings me back to the non-standard workflow point raised before. You also can have a code first approach with diesel. The cli tool supports generating the migrations for you from the given `schema.rs` file and a up and running database. So you basically would write the `schema.rs` file in that case and the tool generates SQL to move the database to that `schema.rs` state.

> > that has the disadvantage that it forces rust-query to always have the full control over the database > > I think this goes for any database layer, doesn't it? If you write migrations for Diesel and then someone goes and makes manual changes to the production database you're similarly out of luck.

My point here is more: rust-query really forces you to have control over the database. Diesel is totally fine with not being able to control migrations or whatever. For running code you only need to provide a schema.rs file, which might be generated by running migrations or which might be hand written or which might be generated from an existing stable database.

> The rust-query author also mentions checksumming, but I think at the end of the day people always have the ability to go in and hollow out assumptions - but IMO the code-first approach which discourages any manual database interaction draws a clear line.

That's likely only about migrations, not about the actual database state. It can help to make sure that migrations are really the same as used to setup the database, but it won't help with cases where you change the database manually. I don't think it's even meaningful to check on each database interaction that the schema hasn't change so far, as that would be quite expensive.

[Hytak]

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

[weiznich]

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.

[Hytak]

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

[weiznich]

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.

[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!

[weiznich]

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.