* Running your migrations in a build.rs file against a test database
* Running `diesel print-schema --locked-schema` afterwards to make sure that the provided `schema.rs` file matches the state of the database
* Use the `embed_migrations!` macro to embed and run the migrations on application startup
What rust-query does is just the "same" as these steps outlined above. Arguably they do all in one step, but that has the disadvantage that it forces rust-query to always have the full control over the database. Other than that there is no verification at any point happening that the schema actually matches what's declared there, they just apply migrations as well and reasonable assume that the database will match the declared state. This does not guard against cases where you for example manually modify the schema later or something like this.
For diesel we want to make this more robust at some point by providing an actual check function as part of the `schema.rs` files that allows to verify that the declared schema matches the actual database state. That one then could be called at different points, depending on the users requirements. If you are interested in such a feature I suggest reaching out to us in the diesel support channels.
1. Write initial db migrations
2. Start a test db, apply the migrations, generate schema.rs via the CLI tool
3. Write code based on schema.rs
4. Write new migrations
5. Test the changes on test db instance, re-generate schema.rs via the CLI tool
6. Update code based on the new schema.rs
When I first looked at Diesel I thought the safety was built into the code itself, but if it comes from out-of-code procedures using the CLI and a real database that makes a lot more sense. I don't think I got a good picture of how all these tools and libraries came together from the Diesel docs themselves. So, in this sense it seems very similar to SQLx.
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.
I guess from some perspective, writing raw SQL with tests that run against a real database is perfectly safe too. I think it's a spectrum of how database-first or code-first the database layer is.
> 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.
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.
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.
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.
If you are willing to risk corrupting the database, then yes you can trick rust-query
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.
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!