weiznich

It doesn't even work for all dynamic where clauses, but only for such that can lead to a fixed number of combinations. This approach doesn't work for dynamic API's that let users combination column + filters as required, which is rather common in complex web applications. (Think of the GitHub issue search box for example)

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.

As this claim comes up quite often I have a set of examples I typically ask these people to provide. So far I've got so response, maybe you can point out how you would implement one or more of the following queries using `sqlx::query!`?

* A batch insert query for mysql

* A conditional where clause for postgresql that allows the user to dynamically specify the column + filter operation + values over e.g. a rest end point. Each column can appear zero, one or multiple times with different or even the operations.

* A IN expression with a list of values provided by a rust `Vec<_>` (so dynamically sized) for sqlite.

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.

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.

> 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.

> As for async, my primary concern for that was to make sure you minimise blocking code calls in your futures. Even if there weren't many performance gains to be made from Diesel itself being async but having database calls marked as Futures could theoretically help the runtime schedule and manage other threads.

That doesn't require an async database library at all. It merely requires some abstraction like [`deadpool-diesel`](https://docs.rs/deadpool-diesel/latest/deadpool_diesel/index...) that ensures that the caller always uses `tokio::spawn_blocking` or similar to run database queries. And yes this scales rather well as the number of database connections in your application is rather low (usually a few ten) and therefore much smaller than the number of threads in the tokio blocking thread-pool.

That depends on the workflow. The query checking is based on the information in your `schema.rs` file. That file is usually generated via a CLI tool provided as part of diesel in your local development workflow. It's generally assumed to match the migrations in the repository, but you also can use the CLI tool to verify that this is actually the case. To get the full way around: The migrations then describe the database state that's used to define your `schema.rs` file, which in turn are embedded into the binary via the `embed_migration!` macro. If you now have the requirement to make sure that the database matches the provided schema file you can ensure that at build time by:

* 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.

Please note that up until today an async rust database library does not give you any measurable performance advantage compared to a sync database library. In addition to that it won't even matter for most applications as they don't reach the required scale to even hit that bottleneck. As a matter of facts crates.io just run well on sync diesel up until maybe a month ago. They have now switched to diesel-async, for certain specific not-performance related reasons. The lead developer there told me specifically that from a performance point of view the service would have been fine with sync diesel for quite a while without problems, and that's with somewhat exponential growth of requests.

Other than that the async rust ecosystem is still in a place that makes it literally impossible to provide strong guarantees around handling transactions and make sure that they ended, which is a main reason why diesel-async is not considered stable from my side yet. This problem exists in all other async rust database libraries as well, as it's an language level problem. They just do not document this correctly.

Please note that this comparison is outdated since at least 2 years, given that diesel-async exists for more than 2 years now and this page completely forgets to mention it.

> Also sqlx.

The guarantees provides by sqlx are less strong than what's provided by diesel due to the fact that sqlx needs to know the complete query statically at compile time. This excludes dynamic constructs like `IN` expressions or dynamic where clauses from the set of checked queries. Diesel also verifies that these queries are correct at compile time.

You get the same safety guarantees with diesel by using the [`embed_migration!`](https://docs.diesel.rs/2.2.x/diesel_migrations/macro.embed_m...) macro and running your applications on startup. Diesel decouples that as there are situations where you might want to work with a slightly different database as defined, for example if you don't control the database.

Please reach out on the diesel discussion forum[1] about the lacking dev experience. I'm happy to discuss these issues and potential solutions there.

[1] https://github.com/diesel-rs/diesel/discussions

Diesel maintainer here. So first of all we are aware of the sometimes bad error message and we are working on solutions. There is already an unstable feature flag that improves some error messages and the next diesel release will include additional tools to improve the error messages in additional cases. In addition we try to change the language to give us more tools to control certain error messages emitted by the compiler, because mostly the error is large, but has a well known underlying issue that can be described much shorter.

> I once (foolishly) tried to write a generic repository implementation for database entities which could either have sqlite or postgres as a backend. I thought this would be very convenient for testing, being able to just keep all of the code the same safe for using a sqlite backed repository instead of a Postgres one.

It's generally not advice to test against different databases than you run in production. This is one of the reasons why these kind of things are not simple in diesel. Prefer running the tests against the "real" database system and use `Connection::begin_test_transaction()` to prevent polluting the database with test data.

More generally speaking: Writing generic code involving diesel is likely an advanced topic, so don't expect it to work quickly. I usually advice people to not write this generic code, at least not if they just want to use diesel in their applications.

Just using a transaction that will be rolled back after each test case is exactly what diesel suggests. There is a separate function for this on diesels connection trait[1]

[1]: https://docs.diesel.rs/2.0.x/diesel/connection/trait.Connect...

At least for diesel that's not true anymore. I've build a async connection implementation that will published alongside the next major release. See https://github.com/weiznich/diesel_async for details