So I am using Postgres for my production database. I use Alembic for migrations and SQLAlchemy to define models and query and so on. This setup works very well for the most part, but I have run into a problem recently.

One of my tables requires a config column, which will be a json blob of varying contents. As such, I have opted for the Postgres JSONB type.

The issue is that my local tests run with an sqlite in memory database. Sqlite apparently has cannot interpret this column type, raising the following exception up running the tests (and building the database):

sqlalchemy.exc.CompileError: (in table 'applications', column 'config'): Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x109e52ef0> can't render element of type JSONB

Has anyone got a suggestion about how to overcome this issue? I was thought about maybe trying to alter the migrations based on whether the program is running tests or in production, but I really don't know where to start in that regard.

Has anyone got any advice?

Thanks in advance, Eric

CodePudding user response：

For anyone who comes across a similar issue: as mentioned in the comments on the question, Sqlite presents many differences from Postgres. As such, my testing environment does not mirror my production environment well and differences like these force me to consider hacks, which is bad news.

The solution I have chosen is to containerize a Postgres db to be spun up when tests are run as described here. It's a slight hit in terms of speed, but it does allow me to reliably test db behaviour locally and in CI pipelines, so I think it is worth it.

Thanks to all the people who took the time to comment.