Transaction rollback for testing

[aranvir]

Hi, I'm using SQLAlchemyInitPlugin in my app but struggle to implement a "rollback recipe" like this one for SQLAlchemy.

Is there an example somewhere on how to do this or does someone have some tips?

From what I understand: to implement the recipe a connection must be started before the specific app test runs so that the next database session injected can be bound to that connection. After validating the return statement the transaction can be rolled back to the save point. However, since the plugin is quite complex/nested to an outsider I find it difficult to identify the spot where I'd have to insert an override. And I'm not sure if it's so difficult because I'm overlooking something or because it's just not possible.

[krylosov-aa]

Hi! Here's an example of how this could be implemented. This library allows you to manage sessions and transactions without using DI. You might want to consider using it in conjunction with litestar.
https://github.com/krylosov-aa/context-async-sqlalchemy/blob/main/examples/fastapi_example/tests/transactional/conftest.py

Now you can write your own middleware, following the example of the ready-made one for FastApi. Or you can wait a little while until I finish it and publish it: krylosov-aa/context-async-sqlalchemy#7

If you still want to stick with di, litestar should definitely provide the ability to replace dependencies. Fastapi has similar dependencies, and I replaced the dependency in conftest in fixture with one that always rolls back.

[aranvir]

Thanks for the pointer, this looks interesting!

I had tried to come up with my own solution but failed and just manually rolled back stuff in my tests via deletes.

[krylosov-aa]

If you're interested, here are a couple of tips to help you figure it out faster.

The key idea is that the middleware does not initialize a new context if it was initialized in tests:
https://github.com/krylosov-aa/context-async-sqlalchemy/blob/main/context_async_sqlalchemy/fastapi_utils/middleware.py#L30

The tests you asked about are called transactional:
https://github.com/krylosov-aa/context-async-sqlalchemy/blob/main/examples/fastapi_example/tests/transactional/__init__.py

I also have a non-transactional test in which the database is explicitly cleared. These tests are also very useful in many cases where transactions are manually managed in the code:
https://github.com/krylosov-aa/context-async-sqlalchemy/blob/main/examples/fastapi_example/tests/non_transactional/__init__.py

Overall, I highly recommend checking out the examples to see the library's capabilities. In short, it offers the convenience of DI, but doesn't force you to propagate sessions through the entire stack from top to bottom. It also allows for early transaction and session closure. It also supports multiple sessions for concurrent requests:
https://github.com/krylosov-aa/context-async-sqlalchemy/blob/main/examples/fastapi_example/routes/__init__.py

[krylosov-aa]

I took a closer look at the example you posted above and realized that the current solution, while allowing for tests to be written using transaction rollback, covers fewer scenarios than it theoretically could. I've created a task to integrate session substitution with save points to improve this.

krylosov-aa/context-async-sqlalchemy#8

P.S.
Turns out it was easy. Just did it.