One way to create isolate integration tests is to tear down and recreate database tables for every single test. This ensures every test starts with a clean and known state. However, achieving this with MySQL is an expensive (slow) operation and would add too much execution time between tests.
Tracelons uses SQLAlchemy as an ORM. Since the MySQL database is abtracted behind this layer, we could switch test database providers to SQLite. SQLite can be run in-memory, allowing fast teardowns of the test database. Coupled with fast schema creation, this enables very fast database operations during test runs.
However, there are some drawbacks to using SQLite as a replacement for MySQL during testing:
- Some tooling in the tracelons repo is built specifically for MySQL and would require changes to work with SQLite.
- SQLite does not have the same features, and does not enforce the same rules as MySQL. Therefore, it differs from production and could possibly allow tests to pass that would otherwise fail.
- Dropping and creating tables per test would not work in a parallel execution environment.