Isolated tests running in their own transactions

[OskarPersson]

Looking at the sqlx-postgres example as well as the testing example, it feels like it should be possible to have the same app and handlers accept either a sqlx Pool or Transaction object as the extension layer depending on context.

In my case I would like to use a pool as the extension layer and have it be extracted to a connection in the handler (just as in the sqlx-postgres example) when running the app. But when testing, I would like to be able to use the same exact app and handler, but send it a transaction object instead where multiple entries in the database might have been created before making the request such that the handler also works inside the same transaction object as the one in the test.

This way I can test against a real database but have each test isolated in their own transaction. I'm having problems achieving this, is this something that could be done?

[davidpdrsn]

I don't think thats possible since extractors (things that implement FromRequest) most be fully owned. That is, they cannot contain borrowed data. sqlx::Transaction borrows the connection so it cannot be accessed via an extractor. I'm not very familiar with sqlx so dunno if there is a workaround.

Also, in general I would strongly recommend against wrapping each test in a transaction. I've attempted to do something like that in the past but kept running into issues, like the one you're facing. Instead I would recommend giving each test its own totally separate database. This is how I do it at work and it works great. We basically have some test helpers that creates a database with a random name, runs the migrations, and automatically removes the database when the test ends (via a Drop implementation). It might seem kinda "heavy" to create separate databases for each test but our tests are still plenty fast.

[OskarPersson]

Hmm okay thanks. Is the Drop implemented on the pool object?

[davidpdrsn]

No. You make your own struct that contains the name of the database and implement Drop for that.

[OskarPersson]

I see. Well I'll see if I can figure it out. An example of this part somewhere would be greatly appreciated though. But I understand if that's something that can be hard to extract from the source code :)

[davidpdrsn]

Yeah a bit. I would also like to keep the examples focused on specific integrations with axum. This is kinda in a gray area. It would be almost identical to the current sqlx/tokio-postgres examples except have more setup code which isn't really related to axum.

[OskarPersson]

Yeah I didn't mean in the repo. Just in this conversation or as a github gist or something :)