Re: [sqlalchemy] Re: Testing and deprecation of nested transactions

[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

On Mon, 2 Aug 2021, 19:24 Dejan Čabrilo,  wrote:

> 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
>
> On Saturday, July 31, 2021 at 7:41:49 AM UTC+2 cfede...@gmail.com wrote:
>
>> 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
>>
>> On Saturday, 31 July 2021 at 00:24:36 UTC+2 Jonathan Vanasco wrote:
>>
>>> 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 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/fd9fc70a-94e3-4855-a22c-d803e75e07ecn%40googlegroups.com
> 
> .
>

-- 
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/CAN19dyfbb0vVNHVfahW8GVwajVbF%3DOag7BcW5XQWxUz1Jw94Pg%40mail.gmail.com.

[sqlalchemy] Re: Testing and deprecation of nested transactions

[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

On Saturday, July 31, 2021 at 7:41:49 AM UTC+2 cfede...@gmail.com wrote:

> 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
>
> On Saturday, 31 July 2021 at 00:24:36 UTC+2 Jonathan Vanasco wrote:
>
>> 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/fd9fc70a-94e3-4855-a22c-d803e75e07ecn%40googlegroups.com.

[sqlalchemy] Re: Testing and deprecation of nested transactions

[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!

On Saturday, July 31, 2021 at 12:24:36 AM UTC+2 Jonathan Vanasco wrote:

> 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/024daee3-effd-4bd9-a085-9b9625761962n%40googlegroups.com.

[sqlalchemy] Re: Testing and deprecation of nested transactions

[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

On Saturday, 31 July 2021 at 00:24:36 UTC+2 Jonathan Vanasco wrote:

> 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/db653c08-55ab-4392-8200-98db8897e3d1n%40googlegroups.com.

[sqlalchemy] Re: Testing and deprecation of nested transactions

['Jonathan Vanasco' via sqlalchemy]

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.