Alembic for maintaining the same schema SQLite and Postgres DB #1009
-
|
I am not sure whether what I am doing is the right approach but I use SQLite to test while developing as the tests run much much faster compared to running it on a Postgres DB. My prod is in Postgres and before pushing I run all of my tests on docker image that resembles my prod. Therefore I would like to use Alembic to write revisions that will work both for Postgres and SQLite I know that each of the dialects have their nuances (e.g with_batch Sqlite / enum handling in Postgres) but I was wondering if Alembic is a right tool to write revisions for both of the DBs. There are already some workarounds I need to do in order to have agnostic upgrades for SQLite and Postgres. What's your opinion about it? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
|
the "develop on sqlite, deploy to Postgresql" use case is very common. it's not the only way of working, and as you noted has some limitations, but if you are fine working around the limitations it's definitely a very typical way to work. it's also common to have test suites that run the majority of tests on sqlite, but then have a subset of "functional" tests that only take effect when a PostgreSQL database URL is also made available; tests like these include migration tests and tests that may be making use of PostgreSQL-specific features. that way you can iterate the majority of features testing only on sqlite but still have postgresql in coverage. |
Beta Was this translation helpful? Give feedback.
-
|
Awesome! Thanks Mike! |
Beta Was this translation helpful? Give feedback.
-
|
To second what Mike said, we've just switched to Alembic, but we have the same scenario, and that has worked for us for more than a decade with thousands of tests (our previous tool was SQLAlchemy Migrate, but same idea). In fact, we run almost all our tests on both databases (manually - depending on whatever is in the config; CI - only postgres). There are a few postgres-only tests - we just decorate those with a pytest marker. And yes, we sometimes have to write small workarounds to make the code compatible with both databases, but, in my experience, it's not a big deal. So, 👍 |
Beta Was this translation helpful? Give feedback.
the "develop on sqlite, deploy to Postgresql" use case is very common. it's not the only way of working, and as you noted has some limitations, but if you are fine working around the limitations it's definitely a very typical way to work.
it's also common to have test suites that run the majority of tests on sqlite, but then have a subset of "functional" tests that only take effect when a PostgreSQL database URL is also made available; tests like these include migration tests and tests that may be making use of PostgreSQL-specific features. that way you can iterate the majority of features testing only on sqlite but still have postgresql in coverage.