I typically do local developer testing with sqlite3, and the switch the database to postgresql for build/deploy/ci testing in the cloud.
For complex tests, I typically use a fresh database "image". e.g. a sqlite file or pgdump output that is tracked in git. This is not the solution you're looking for, but i've found it very useful. I spent a long time working on a testing setup like you are trying to accomplish, but abandoned it when we built out an integrated test suite and data had to persist across multiple database connections. On Friday, July 30, 2021 at 4:19:35 AM UTC-4 dcab...@gmail.com wrote: > Hello everyone, > > I am working on a new project using SqlAlchemy Core 1.4 with Postgresql > and wanted to implement the following pattern for my tests: > > - Before each test I would start a transaction (in a > @pytest.fixture(autorun=True)) > - Each test may create its own transactions > - At the end of each test, I would rollback the transaction > > The purpose is to keep the database "clean" between tests and not have to > manually delete all inserted data. > > However, it seems that SqlAlchemy 1.4 is deprecating nested transactions > and that they will be removed in 2.0. > > Is there an alternative approach or best practice that I can use for > isolating tests in transactions? > > I had an alternative idea, like: > > - Before each test create the first savepoint (let's call current > savepoint N) > - Catch any commit in the code and instead create a savepoint N+1 > - Catch any rollback and rollback to N-1 > > Obviously, that seems like a lot of work and I'm not even sure if I can > intercept begins, commits and rollbacks that easily. > > Alternatively, I could run upgrade and downgrade migrations on every test, > but that would slow the test suite down a lot. > > Any advice and thoughts would be appreciated. > > Thanks! > Dejan > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e4e452ef-351d-4f92-a87c-1ab52ebc70ffn%40googlegroups.com.