3

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

Eric31
  • 111
  • 8
  • 2
    https://phauer.com/2017/dont-use-in-memory-databases-tests-h2/ –  Jun 21 '22 at 09:22
  • cast it to text. I have no idea how to do that in sqlalchemy. – Jasen Jun 21 '22 at 11:20
  • 1
    The overriding issue is testing against a database that is not the same as the one your application is running on. This is not a good idea as Sqlite is different in many ways from Postgres. – Adrian Klaver Jun 21 '22 at 15:15
  • 1
    Postgres is fully open source. Download the same version as your production and install on your test system. Testing on one RDBMS and expecting proper results on another is pure fantasy. If you do not test with the same RDBMS then ***you have not tested**. – Belayer Jun 21 '22 at 18:48
  • Hey everyone, thanks for your answers. In the end, I have decided to containerize a Postgres db and test on that. As many of you mention, my test env is not the same as my production env, and that is just not a fabulous idea. Thanks for your input! – Eric31 Jun 22 '22 at 13:13

1 Answers1

1

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.

Eric31
  • 111
  • 8