I'm curious about the best practices for testing SQLAlchemy models and Alembic migration scripts. Specifically, what types of unit tests are recommended for validating SQLAlchemy model classes, particularly regarding constraints? Additionally, do you typically write tests for migration scripts generated by Alembic? If so, could you share some examples of what you've tested for both models and migrations?
2 Answers
We usually test upgrades on our models and migrations by doing the following: 1) spin up a Docker container with a PostgreSQL database during CI/CD, 2) use a test fixture to create the schema in a temporary database with SQLAlchemy, and 3) run a test applying Alembic migrations from a historical snapshot to ensure everything applies correctly and the autogenerate functionality detects no further changes. This helps catch issues, especially since some migrations were being written incorrectly in the past.
I haven't really unit tested those areas of my code since I trust that they work fine. But if you're looking to test, I suggest setting up a database container using Docker. You can then run SQL queries or integration tests easily from there.
Having those small snapshots of databases is a solid idea! It makes testing migrations much easier, especially when you need the database to be in a certain state.