Testing and deprecation of nested transactions

[Dejan Čabrilo]

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

[Jonathan Vanasco]

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.

[Federico Caselli]

Hi,

Have you tried using this pattern from the documentation? I think you can also use that while using only connections

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html?highlight=after_transaction_end#joining-a-session-into-an-external-transaction-such-as-for-test-suites

[Dejan Čabrilo]

Thanks for the suggestion! Sqlite3 won't work for my use-case because I depend on Postgresql and its extensions a lot.

I ended up using this fixture:

@pytest.fixture(scope='function')
     alembic.config.main(argv=["upgrade", "head"])
     yield
     alembic.config.main(argv=["downgrade", "base"])

But with the function scope (which I want so no test depends on another one) I am afraid it will get very slow over time :(

Thank you!

[Dejan Čabrilo]

Hi,

From what I see, that's only pertinent to the ORM, right? I don't use ORM, SqlAlchemy core only, so I think that wouldn't work, right?

Thanks!

Dejan

[Federico]

If you only need to support postgresql you may also create a db to be used as template, then each test gest it's own db generated from the original template db.

I think this should be the fastest way of restoring the db state after a test 

--
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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/iQnCq-1FX00/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fd9fc70a-94e3-4855-a22c-d803e75e07ecn%40googlegroups.com.