testing patterns with sqlalchemy 2.0

[Chris Withers]

Hi All,

Are there any libraries (or anything in sqlalchemy itself!) that cover
the pattern of running unit tests in against a database such that each
test gets its own sterile environment in which to run? Postgres, if it
helps. I've done some stuff with running in a subtransaction and rolling
back at the end of the test before, but wanted to see if anything had
become common enough to end up in a popular library yet...

What's the recommended way of getting a blank db to test against? Run
alembic migrations in a session-level fixture? Create an empty schema
from the models using create_all? Something else?

cheers,

Chris

[Jonathan Vanasco]

> Create an empty schema from the models using create_all? 

This is what I usually do with smaller projects.  On some large legacy projects, I use a database dump that is loaded into Postgres - as they often rely on a lot of records that need to be in the database and generating them via SqlAlchemy is so much slower and would be a pain to develop.

When using unittest, some tests will use a fresh DB per test-run, others per-class, and others per-test.  Sometimes the tests dictate that, other times I control that with env vars.  That gives us the flexibility to work on a small section and do efficient test runs during development.

[Chris Withers]

On 01/09/2022 20:00, Jonathan Vanasco wrote:
>
> > Create an empty schema from the models using create_all?
>
> This is what I usually do with smaller projects.

When taking this approach, how do you ensure the accumulated schema
migrations end up with a database that matches the one that create_all
gives you?

> When using unittest, some tests will use a fresh DB per test-run, others
> per-class, and others per-test.

Yeah, pytest fixture scoping gives a great way to set these up.

> Sometimes the tests dictate that, other
> times I control that with env vars.  That gives us the flexibility to
> work on a small section and do efficient test runs during development.

Creating a database for every unit test feels like something that would
be slow enough to be annoying. How are you creating databases such that
it's fast enough for this not to be the case?

Chris

[Elmer de Looff]

On Fri, Sep 2, 2022, 08:47 Chris Withers <ch...@withers.org> wrote:

On 01/09/2022 20:00, Jonathan Vanasco wrote:
>
>  > Create an empty schema from the models using create_all?
>
> This is what I usually do with smaller projects. 

When taking this approach, how do you ensure the accumulated schema
migrations end up with a database that matches the one that create_all
gives you?

You might consider adding a step to your CI pipeline where you auto-generate a new revision and ensure it is empty. Some of the projects at work use this as a safety valve. 

Flask's Alembic integration makes this particularly nice, where the migration command has an observable different exit code when no migrations are necessary. You might want to look at how to detect for this case and create a small check script, or even an explicit test in the suite.

> When using unittest, some tests will use a fresh DB per test-run, others
> per-class, and others per-test.

Yeah, pytest fixture scoping gives a great way to set these up.

> Sometimes the tests dictate that, other
> times I control that with env vars.  That gives us the flexibility to
> work on a small section and do efficient test runs during development.

Creating a database for every unit test feels like something that would
be slow enough to be annoying. How are you creating databases such that
it's fast enough for this not to be the case?

Chris

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d779d7ad-1eff-e4e8-1f9c-9979b80a9146%40withers.org.

[Jonathan Vanasco]

> When taking this approach, how do you ensure the accumulated schema
migrations end up with a database that matches the one that create_all
gives you?

Sadly, I don't.  I've actually been working on a test to ensure every table and column in the model exists in the database!

> Creating a database for every unit test feels like something that would

be slow enough to be annoying. How are you creating databases such that
it's fast enough for this not to be the case?

It depends on the project, context and test.  That's why we use env vars to control it.

Using a sql dump into postgres is pretty fast, but sqlite is not.
I have some tests that note which tables are required, and just build those tables.
Tests are designed to work on a populated existing database as well, so they don't need to be empty.

It's definitely not a one-size fits all concept for me.  I have some projects with under 20 tables, but some legacy applications with hundreds of tables.