Use `diesel` without its DSL?

[biwecka]

Hi, after working on quite some Rust backends (with axum) using diesel (through deadpool_diesel) or sqlx I've experienced the following things:

• I quite like writing raw SQL (or use a query builder for dynamic queries) because for me it's easier to understand what exactly is going on (especially for complex queries with a lot of joins)
• With diesel I experienced build times to increase as the project grows (the largest project has a separate library crate that contains all database related code and this library alone takes ~90s to compile)

My first question is, if someone also noticed long build times with diesel and can give an insight on how the problem could possibly be solved.

Apart from that I had a pretty wild idea :
As sqlx pretty much fits my needs but it's lagging behind diesel in terms of performance (see diesel metrics and my own benchmark below) I thought about using diesel but without its DSL and therefore being able to remove all of the derive and proc macros (which maybe cause slow compile times). I'd then use diesel to execute raw queries or build dynamic queries with diesel's query builder.

What do you think about this idea? Does this make any sense or would you recommend using alternative crates? Would this approach maybe even make diesel queries to perform worse?

my benchmark results

The following benchmarks were performed using bombardier to create a lot of HTTP requests against a very minimal axum backend which used deadpool-diesel or sqlx to execute trivial select queries on the database. These backends were build in separate crates.
The -c flag sets the number of connections and the -n flag defines the number of requests to perform.

`deadpool-diesel`

-------------------------------------------------------------------------------
Deadpool-Diesel (pool size = 10):

--- 10 connections ---
./bombardier -c 10 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      6838.80     726.44   12961.47
  Latency        1.46ms   196.07us     5.79ms
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.19MB/s

--- 100 connections ---
./bombardier -c 100 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      7605.17     713.38   16579.55
  Latency       13.18ms     1.56ms    74.20ms
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.31MB/s

--- 1.000 connections ---
./bombardier -c 1000 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      7562.66     921.31   19251.22
  Latency      133.00ms    62.76ms      1.47s
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.30MB/s

--- 10.000 connections ---
./bombardier -c 10000 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      7497.86    2817.29   76722.83
  Latency         1.30s      0.90s      9.17s
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.27MB/s


-------------------------------------------------------------------------------

sqlx

-------------------------------------------------------------------------------
Sqlx (pool size = 10):

--- 10 connections ---
./bombardier -c 10 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      6056.48     488.28    7226.05
  Latency        1.65ms   224.14us     7.99ms
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.05MB/s

--- 100 connections ---
./bombardier -c 100 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      6127.51     496.42    7175.71
  Latency       16.37ms     2.57ms   107.63ms
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.06MB/s

--- 1.000 connections ---
./bombardier -c 1000 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      6139.28     829.31   22694.44
  Latency      163.64ms    54.06ms      1.47s
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.05MB/s

--- 10.000 connections ---
./bombardier -c 10000 -n 100000 http://localhost:3000
Statistics        Avg      Stdev        Max
  Reqs/sec      5745.57     709.37   10201.69
  Latency         1.68s      1.46s     14.48s
  HTTP codes:
    1xx - 0, 2xx - 100000, 3xx - 0, 4xx - 0, 5xx - 0
    others - 0
  Throughput:     1.00MB/s

-------------------------------------------------------------------------------

[ScratchCat458]

I think you're looking for the sql_query function.

[biwecka]

Exactly this is what I thought of. But do you think this is a sensible way of using diesel and will it help with compile times?
Or would you rather use a different crate if your goal is to write raw SQL anyways?

[ScratchCat458]

If your aim is write raw queries and don't want to use SQLX, trying using a crate for your specific database. rusqlite for SQLite and rust-postgres for Postgres. I'm not particularly familiar with the latter, but this probably the best option if you don't want to use an ORM.

[weiznich]

@biwecka

Exactly this is what I thought of. But do you think this is a sensible way of using diesel and will it help with compile times?

It's a sensible way of using diesel and it should help with compile times. The tradeoff there is that this basically opts out using the prepared statement cache.

The other approach that might help with compile times is using boxed queries everywhere, as that simplifies quite lot of the type constraints that the compiler needs to resolve to something simpler. The tradeoff there is this requires additional boxing, which at least for postgresql/mysql shouldn't be noticeable outside of hot inner loops.

The second variant essentially means that instead of

users::table.filter(users::name.eq("Ferris")).load::<User>(&mut conn)?;

you would write

users::table.into_boxed().filter(users::name.eq("Ferris")).load::<User>(&mut conn)?;

It's important to put the .into_boxed() call as first call to reduce the complexity of the query type.

[H2CO3]

SQL (ie., the actual querying/DML) is only one part of the problem, the other being the schema definition (i.e., the DDL). It can help a lot with correctness if at least your schema is statically and strongly typed, not just "arbitrary tuples of arbitrary dynamic types".

SQL's own type system (as implemented by most mainstream DBs) is insufficient for ensuring correctness in any but the most trivial situations. Thus, when practicing modern domain-driven design, it is almost obligatory to have a layer of abstraction between the low-level database and your application's model types.

On the flip side, the DSL of most ORMs doesn't – can't – fully replicate the functionality and all surface area of a particular implementation of SQL. Thus, "advanced" (read: highly vendor-specific) queries, or even some basic ones (such as recursive CTEs) remain accessible only via raw SQL. As ugly as SQL may be, it's unfortunately the best tool for the job more often than I'm comfortable admitting.

Thus, I'd recommend you don't just fall back to raw SQL unconditionally. Until a more advanced method of querying databases is available, ^[1] your best bet is to pick an ORM and use raw SQL when it's necessary or much simpler/more convenient than the equivalent ORM DSL would be.

---

1. I'm working on it! ↩︎

[biwecka]

@ScratchCat458 I thought about using database-specific crates too as their performance seems to be roughly on par with diesel but at the same time I'd prefer a solution that's compatible with at least Postgres and SQLite.

@weiznich I just looked up diesels prepared statement caching because I didn't know about it. As far as I understand it's a technique to speed up subsequent queries at runtime and therefore it has a positive effect on query performance. Therefore opting out of it probably makes query performance worse, correct?
Are raw queries in diesel not cached at all or only cached by SQL?

Your second suggestion seems to be relatively easy to try out and validate. Is it also possible to use this approach for insert statements like this:

diesel::insert_into(users::table.into_boxed())

I'd also be interested in why boxing helps in reducing the type constraint complexity. I haven't used Box<_> a lot because it causes heap allocation which usually discouraged (at least that's the impression I have when reading on forums). Is that why you said it might be a tradeoff only noticeable in hot inner loops?

@H2CO3 What do you mean by "arbitrary tuples of arbitrary dynamic types"? Are you referring to how raw data is returned from the database if it's not mapped to a struct?
If that's the case I can only say that I definitely don't want to use the raw tuples as they're returned from the database. diesel has QueryableByName and sqlx has the FromRow derive macro which conveniently map database results to structs.
The only problem with that is that the schema defined through these structs in code can differ from the actual schema in the database (which is defined by the sum of all migrations).
Is that the reason you say that I give up a statically and strongly typed schema when not using diesels DDL (which I assume is the generated schema.rs)?

What do you think about sqlxs compile time checking of queries against the database? Do you think it enforces types correctly or can it not do that reliably because type system of the database itself is insufficient?

I can understand your recommendation and it's the exact reason I used diesel in the large project I mentioned in the first post. But using the the ORM DSL for simple queries adds a lot of complexity and overhead (derive macros, compile time, ...) to the application (more than it benefit from it). Especially because I need to find a solution to ensure that the more complex queries written in raw SQL are typed correctly anyways (if not using sqlx's compile time checks probably through testing).

I'm curious about the more advanced method for querying databases you said you're working on Do you mind gibing some insights?

@weiznich As far as I know diesel usually maps rows to structs by "order" (that's why the fields of a Queryable struct need to be defined in the same order as the tables columns in schema.rs). Does using QueryableByName have a negative impact on query performance?

[weiznich]

@biwecka

I just looked up diesels prepared statement caching because I didn't know about it. As far as I understand it's a technique to speed up subsequent queries at runtime and therefore it has a positive effect on query performance. Therefore opting out of it probably makes query performance worse, correct?

Right, it degrades performance if you execute the same query (or a set of queries) often and if these queries are cacheable at all.

@biwecka

Are raw queries in diesel not cached at all or only cached by SQL?

They are not cached by diesel. I think SQL refers to the database side here? There might be caching on going there, but using existing prepared statements is always more efficient as that just skips any possible heuristics to see which query might match.

@biwecka

Your second suggestion seems to be relatively easy to try out and validate. Is it also possible to use this approach for insert statements like this:

diesel::insert_into(users::table.into_boxed())

That shouldn't be needed. As far as I'm aware most of the complexity comes from the select queries. (And there mostly from those queries with a lot of joins).

@biwecka

I'd also be interested in why boxing helps in reducing the type constraint complexity. I haven't used Box<_> a lot because it causes heap allocation which usually discouraged (at least that's the impression I have when reading on forums). Is that why you said it might be a tradeoff only noticeable in hot inner loops?

Boxing basically turns a query type like SelectStatement<SelectClause<(foo::column_a, foo::column_b)>, …> into BoxedSelectStatement<foo::table, Pg, SqlType = (…)>, which means the type stays more often the same. This allows rustc to reuse checks that were already done for this type, while for SelectStatement<…> rustc needs to do similar checks again and again, because most queries are slightly different, which results in slightly different types.
There are two ways this boxing can introduce overhead: The allocation itself takes time and it uses dynamic dispatch internally. Depending on how long your queries take this might be a problem or not. (It's mostly a problem for sqlite, as queries can be really fast there)

@biwecka

What do you think about sqlxs compile time checking of queries against the database? Do you think it enforces types correctly or can it not do that reliably because type system of the database itself is insufficient?

Just as a heads up: sqlx compile time checking has several in my opinion serve limitations, which result in false results. For example it cannot correctly detect whether columns are nullable or not. Additionally you can use it only with static queries. This also excludes "simple" queries that use an IN() operator with a variable number of binds.

[H2CO3]

@biwecka

The only problem with that is that the schema defined through these structs in code can differ from the actual schema in the database (which is defined by the sum of all migrations).

Yeah, that is one problem. (One half of the same problem, really – the other half being the mapping of the dynamically-typed rows to statically-typed objects, which can also fail/work incorrectly if the schema and your toe definitions do not match.)

@biwecka

What do you think about sqlxs compile time checking of queries against the database?

I'm not familiar enough with sqlx to be able tell with certainty, but AFAICT there's an obvious race condition between the compile-time checking of the DB and the queries issued at run-time. So I think it's a useful debugging device but I wouldn't count on it for ensuring correctness.

@biwecka

Do you think it enforces types correctly or can it not do that reliably because type system of the database itself is insufficient?

I don't think the weak typing of the underlying DB affects this issue. The race condition in itself is the problem. Since the whole point of sqlx's type verification procedure is to ensure consistency of the DB and the schema, I assume it does work and is sufficiently powerful to check eg. that tables have the correct columns and types.

@biwecka

I'm curious about the more advanced method for querying databases you said you're working on Do you mind gibing some insights?

It's an external DSL with type-checked migrations, and a query language resembling a modern functional language, with added features that are sorely missing from ORMs and RMDBS engines, such as sum types and recursive queries.

[biwecka]

@weiznich

They are not cached by diesel. I think SQL refers to the database side here?

With "cached by SQL" I was referring to the diesel documentation of the prepared statement caching which states

Queries will fall into one of three buckets

• Unsafe to cache
• Cached by SQL
• Cached by type

I thought that could mean that diesel might also cache prepared statements for raw SQL.

That shouldn't be needed. As far as I'm aware most of the complexity comes from the select queries. (And there mostly from those queries with a lot of joins).

Ok, I'll definitely give that a try One last question to boxing: Is this (.into_boxed()) what can be seen in the diesel metrics for example with diesel_async_boxed?

Thanks a log for the explanation of what happens when boxing is used and for the heads up regarding sqlx. Now that you mentioned it I remember reading about some issues regarding the detection of nullable columns. It's definitely lacking and in the long term probably not what I'd use because my guess is that compile time will suffer a lot from sqlxs query checks at compile time.

After this whole discussion it feels like diesel is still the best bet on interacting with databases in Rust. The only "complaints" I have is the compile time in larger projects and the pretty annoying usage of diesel through deadpool (with deadpool_diesel). As boxing could help with compile times and I saw that diesel_async is much easier to use than deadpool these might be the solution to my problem.
Maybe I'll also try out cornucopia and prisma-client-rust. Has anyone heard of these crates or used them?

@weiznich Would you say that diesel_async is the way to go for diesel in async contexts (e.g. web services)?

---

@H2CO3

Yeah, that is one problem. (One half of the same problem, really – the other half being the mapping of the dynamically-typed rows to statically-typed objects, which can also fail/work incorrectly if the schema and your toe definitions do not match.)

Unfortunately I don't quite understand that. Why are the rows dynamically-typed? Isn't Postgres for example statically typed and should thus return statically typed rows as a result? And what is a "toe definition"?

I'm not familiar enough with sqlx to be able tell with certainty, but AFAICT there's an obvious race condition between the compile-time checking of the DB and the queries issued at run-time.

Could you explain this further? How can the compile-time checking and the application at runtime have a race condition?

I'm sorry if my questions are pretty basic but I really want to understand your concerns about the type mapping situation.

[H2CO3]

@biwecka

Why are the rows dynamically-typed? Isn't Postgres for example statically typed and should thus return statically typed rows as a result?

RDBMSs are in theory statically-typed, but their type systems are very weak. In addition, to support returning generic rows/tuples across language boundaries, DB driver libraries pretty much have to be dynamically typed, because the SQL is just a string to the host application (it's not checked by it), and the serialized data has to come back somehow too, but that depends on the query dynamically.

@biwecka

And what is a "toe definition"?

A typo. I meant "row definitions".

@biwecka

How can the compile-time checking and the application at runtime have a race condition?

Because the compile-time checking happens first, and then the runtime of the application happens some time later. The DB could have changed schema in the meantime. I don't know whether sqlx provides any protection against this, but unless there is a specific, explicit mitigation, this error can very well happen.

[weiznich]

@biwecka

With "cached by SQL" I was referring to the diesel documentation of the prepared statement caching which states

In that case: "cached by SQL" just means that the hash of the sql string is used as query cache key. Queries constructed by sql_query are not cached this way as explained a bit below in the documentation by the following statement

SqlLiteral nodes

• We have no way of knowing whether the SQL was generated dynamically or not, so we must assume that it’s unbounded

@biwecka

One last question to boxing: Is this (.into_boxed()) what can be seen in the diesel metrics for example with diesel_async_boxed?

Yes that's behind the diesel_boxed and diesel_async_boxed entries in the benchmarks.

@biwecka

Maybe I'll also try out cornucopia and prisma-client-rust. Has anyone heard of these crates or used them?

At least cornucopia should suffer from the same type problems as sqlx, as the real problem there is that the databases just don't expose that information at all. I think cornucopia chose the other way around, so they will infer columns as nullable if they are in fact not nullable in more complex queries (anything that queries more than one table).

@biwecka

Would you say that diesel_async is the way to go for diesel in async contexts (e.g. web services)?

There are reasons for using one or the other. It really depends on your usecase. On the one hand diesel_async might seem to be easier to use, on the other hand you might suffer from cancellation issues due to how async rust currently works. Normal diesel doesn't have these issues, but might require a little bit additional work.

[biwecka]

Thanks for the clarification Now I understand the problems you brought up.

@H2CO3

DB driver libraries pretty much have to be dynamically typed [...]

Does this also affect pure Rust database drivers? Because they don't need to provide compatibility across language boundaries.

@H2CO3

The DB could have changed schema in the meantime.

This definitely is a problem and as far as I know it can only be solved or rather prevented by having migrations automatically applied before the application is deployed.

---

In conclusion these are issue I cannot really solve by choosing one database library over the other. Their underlying database drivers are likely having the same difficulties mapping rows to statically-typed objects and the library itself can also not force me to always apply the migrations on my development, staging and production databases.
Therefore all there's left for me to do is

• keeping the database schema up to date in every database I use (dev, staging, prod)
  • this is something I already ensure via CI/CD pipelines
• making sure the schema in the database and the structs in code are in sync
  • as this discussion pointed out diesel is probably the best library to ensure the database schema and the struct definitions are in sync and for special cases where raw SQL is needed I can simply opt-out of diesels DSL, use the query builder and use tests to ensure the query does not fail.

Furthermore diesel has been the most performant database library in my tests and it does not suffer from wrong type inference issues like sqlx with nullable columns.

[H2CO3]

@biwecka

Does this also affect pure Rust database drivers? Because they don't need to provide compatibility across language boundaries.

The language boundary is between the DB (SQL) and the host language. It doesn't matter what the host language is.

[biwecka]

@weiznich

At least cornucopia should suffer from the same type problems as sqlx [...]

I'll definitely check out cornucopia to see if they really have the same issue as sqlx. Thanks for the hint

@weiznich

[...] the databases just don't expose that information at all.

As far as I know sqlx uses Postgres' explain analyze ... to get information about the queries. How does diesel get more accurate schema information from the database to reliably infer for example the nullability of columns?

@weiznich

There are reasons for using one or the other. It really depends on your usecase. On the one hand diesel_async might seem to be easier to use, on the other hand you might suffer from cancellation issues due to how async rust currently works. Normal diesel doesn't have these issues, but might require a little bit additional work.

Can you give examples for use cases you were thinking of? What are "cancellation issues"?
Does using diesel + deadpool (via deadpool_diesel) solve these issues? Unfortunately I cannot use only diesel because in the context of a webserver I'd like to make use of multiple database connections.

[weiznich]

@biwecka

How does diesel get more accurate schema information from the database to reliably infer for example the nullability of columns?

Diesel only gets the table structure from the database. That's what in your schema.rs file. Everything else is essentially checked by implementing relevant parts of SQL in rusts type system. So for the left join case, we essentially just encoded that we need to tread columns coming from left joined tables as always being nullable, while for other cases, like inner joins, we don't add these restrictions.

@biwecka

Can you give examples for use cases you were thinking of

Essentially you really need to estimate how much traffic your service gets and how complicated your queries are:

• Less traffic and/or uncomplicated queries -> Likely OK to just not need to care about async at all. So just use whatever seems to work best
• Quite a bit traffic or long running queries -> You need to somehow care about how to run queries,
  • You have a high latency database server connection or you need to handle a LOT of traffic (more than crates.io) -> probably diesel_async might be worth
  • Otherwise again use whatever works best, so either diesel-async or diesel + deadpool_diesel.

@biwecka

What are "cancellation issues"?

When a future is dropped the runtime (tokio) stops polling it. That can result in issues, like dangling transactions. This cannot happen with normal diesel, as it just does not provide a way to interupt anything.

[biwecka]

@weiznich

Diesel only gets the table structure from the database.

Do I understand this correctly, that diesel "asks" the database about its schema and the database returns accurate information, but when sqlx checks the queries with explain analyze ... and the information given by the database is not accurate? Is the explain analyze ... query the problem of this inaccuracy? How does diesel retrieve the schema information from the database?

@weiznich

[...] or you need to handle a LOT of traffic (more than crates.io) [...]

I had a look at the source code of crates.io and found out, that they're using r2d2 as connection pool. As far as I know r2d2 is not async which means that whenever crates.io needs a connection from a connection pool this call blocks the current async task. Is this correct?
What deadpool_diesel provides to prevent this is that it automatically calls tokio::task::spawn_blocking when using its interact function to interact with the connection pool. So from my understanding I could also just choose deadpool_diesel to at least don't block the async task when requesting connections from the pool.

@weiznich

When a future is dropped the runtime (tokio) stops polling it.

I'm sorry if this question has an obvious answer But why would a future get dropped?

@weiznich

This cannot happen with normal diesel, as it just does not provide a way to interupt anything.

So this can also not happen when using diesel in combination with deadpool_diesel as it's basically like using plain diesel but wrapped into tokio::task::spawn_blocking?

[weiznich]

@biwecka

Do I understand this correctly, that diesel "asks" the database about its schema and the database returns accurate information, but when sqlx checks the queries with explain analyze ... and the information given by the database is not accurate? Is the explain analyze ... query the problem of this inaccuracy? How does diesel retrieve the schema information from the database?

For postgresql diesel queries the information_schema.. That provides information about the schema itself, but you only get information about tables/… from there. The sqlx approach requires that you get information about queries, which can be done by explain analyze or similar mechanisms, but all of them are lacking information. So the main difference here is which information are used for checking, diesel uses tables and implements checking based on that, while sqlx tries to use the database to check the whole query.

@biwecka

As far as I know r2d2 is not async which means that whenever crates.io needs a connection from a connection pool this call blocks the current async task. Is this correct?

Yes, at least if they don't use anything like spawn_blocking to prevent that. (The last time I've looked at their code, they had an abstraction for that).

@biwecka

So from my understanding I could also just choose deadpool_diesel to at least don't block the async task when requesting connections from the pool.

Yes, that sounds right.

@biwecka

I'm sorry if this question has an obvious answer But why would a future get dropped?

By using something like tokio::select!, where the other future completes earlier. That can be used for timeouts and such stuff. Or just by a http-client drop a connection.

@biwecka

So this can also not happen when using diesel in combination with deadpool_diesel as it's basically like using plain diesel but wrapped into tokio::task::spawn_blocking?

Yes this can also not happen if you use deadpool_diesel.

[biwecka]

@weiznich

For postgresql diesel queries the information_schema. [...] The sqlx approach requires that you get information about queries [...] but all of them are lacking information.

Thanks a lot for this clarification Now I finally understand where the difference in type-information comes from.

@weiznich

Yes, at least if they don't use anything like spawn_blocking to prevent that. (The last time I've looked at their code, they had an abstraction for that).

Oh, I may have overlooked that But as you also said I could simply use deadpool_diesel instead of building my own abstraction on top of r2d2.

@weiznich

Or just by a http-client drop a connection.

Wow, this sounds pretty scary. So for example when a client makes an HTTP request to my API, axum creates a new future for the route handler which calls the database two times (with diesel_async). If the client drops the connection after the first database call, the runtime stops polling and the future that handled the HTTP request cannot advance its state to the next .await (e.g. the second database call). Therefore the first query was executed and the second was not. Is that the correct understanding of the situation?

Can this problem be solved by using transactions? Or do the .awaits inside of the transaction closure then suffer from the same problem?
Does diesel_async not wrap plain diesel in tokio::task::spawn_blocking or something similar?
If it doesn't, how does diesel_async work under the hood (in comparison to diesel + deadpool_diesel)?

Edit:
I found out, that the cancellation of the request handling future/task is actually how axum handles the situation if the client closes the connection (see HTTP handlers: client disconnects and cancelled futures (Reddit)).
Is this behavior generally desired? I for example read about the "Slowloris" DDoS attack and that the cancellation of futures/tasks helps against this attack. So it definitely has its pros but as stated above it could also lead to unexpected behavior when talking to a database.

[biwecka]

To not have this topic ending with a bunch of questions I'm trying to answer my own questions after exploring the behavior of diesel, diesel_async and axum.

@biwecka

Therefore the first query was executed and the second was not. Is that the correct understanding of the situation?

Yes, this is how the web server behaves in this case. I also implemented a very simple axum route handler to demonstrate the cancellation problem:

async fn route_handler() -> String {
    println!("route handler entered");

    println!("1");
    tokio::time::sleep(Duration::from_secs(2)).await;
    println!("2");
    tokio::time::sleep(Duration::from_secs(2)).await;
    println!("3");
    tokio::time::sleep(Duration::from_secs(2)).await;
    println!("4");

    println!("route handler end");

    "Ok".into()
}

Accessing this route for example in you browser you'll see the loading spinner spinning quite some time because of the tokio::time::sleep(...).await calls. If you then stop the page loading somewhere between println!("1") and println!("4") you won't get to see any numbers printed to the console after that, because the browser closed the connection and thus the runtime stopped polling for the future (which is the route handler).

To prevent this behavior and finish the task you want to run, something like this is possible:

async fn route_handler() -> String {
    println!("route handler entered");

    let join_handle = tokio::spawn(async {
        println!("1");
        tokio::time::sleep(Duration::from_secs(2)).await;
        println!("2");
        tokio::time::sleep(Duration::from_secs(2)).await;
        println!("3");
        tokio::time::sleep(Duration::from_secs(2)).await;
        println!("4");
    });

    let _ = tokio::join!(join_handle);

    println!("route handler end");

    "Ok".into()
}

But keep in mind that this solution spawns an extra tokio task. So you might need to perform some tests and benchmarks before using this solution.

@biwecka

Can this problem be solved by using transactions? Or do the .awaits inside of the transaction closure then suffer from the same problem?

Yes, the problem of a connection dropping and thus executing one database call but not the next one (which potentially results in an inconsistent database state) can be solved by using transactions (and it probably should have been wrapped in a transaction before ).
When a transaction is "interrupted" by a closing HTTP connection the transaction was never finished/constructed and therefore never actually sent to the database. That's exactly what we want to happen in this case.

---

In addition to that I implemented and ran some benchmarks to compare the following cases:

• deadpool_diesel: normal diesel with deadpool as connection pool
• deadpool_diesel_boxed: like deadpool_diesel but with .into_boxed()
• deadpool_diesel_raw: like deadpool_diesel but with diesel::sql_query
• diesel_async_pool_deadpool: diesel_async with the deadpool feature
• diesel_async_boxed_pool_deadpool: like diesel_async_pool_deadpool but with .into_boxed()
• diesel_async_raw_pool_deadpool: like diesel_async_pool_deadpool but with diesel::sql_query
• diesel_async_pool_bb8: diesel_async with the bb8 feature
• diesel_async_pool_mobc: diesel_async with the mobc feature

All benchmarks were performed on Window's WSL2 with a fresh database and three database tables:

• 100 users
• 10 posts per user
• 10 comments per post

The backend used for the benchmarks was a very minimal axum web server with three endpoints for the different benchmarks. On every run every API endpoint was warmed up for 10 seconds and then the actual benchmark began. I used rewrk with the following options: rewrk -c 32 -d 60s -h <url>.

I measured the requests per second. Blue = best of 5 runs. Orange = median of the same 5 runs.
These are my results:

As you can see I only ran all benchmarks for three of the variations because these were the ones I was most interested in after seeing the results of trivial_query.
I hope this will help someone finding this in the future