Postgres locally and in CI.
Tags: jest, postgres, node, gitlab
Published: 2023-12-18 20:40:55

It all started with a little nodejs library called [pg-mem](https://github.com/oguimbal/pg-mem).  It promised an in-memory postgres database that would be a great lightweight option for testing in CI. It was advertised as experimental but figured it would be worth it just for its sheer convenience.

Getting it up and running was easy but I did run into two small issues, certainly not deal breakers.

1. Some queries return a slightly incorrect result (primary key of joined data a big [mangled](https://github.com/oguimbal/pg-mem/issues/339) when using `GROUP BY` clauses).

2. The entire suite of functions available in Postgres are not implemented, so depending on your queries you may have to implement them on your own (ie: `jsonb_build_object(...)`).

A quick pop onto their Github page revealed at least a few pages of unresolved [issues](https://github.com/oguimbal/pg-mem/issues).  In any case, tests were running smoothly both locally and in CI so I was happy.

Sadly, as queries became more complex, `pg-mem` became less able to cope and over time it became clear that we had... Outgrown each other.  To be clear, this isn't a knock on `pg-mem`, not even a bit.  In fact, the opposite, it's a great library I even attempted to fix the bugs I encountered. Finding enough time to work for free is just the reality of open source development. I have no doubt that with more resources it could truly realize its potential.  The allure of an in-memory Postgres database for testing remains strong but I needed proper query execution and so along came `Docker`.

`Docker` is an easy technology to work with.  It's well-documented, ubiquitous, and there are libraries for it in almost every programming language you can imagine.  The only real stumbling block was coming to the realization that a volume needs to be mounted at the absolute path on a Windows system.  Once ChatGPT helped me with that, we had one running database in a Docker container.  I refactored the tests that relied on weird `pg-mem` results and we were successfully back to square one. Integrating the nodejs library [dockerode](https://github.com/apocas/dockerode) to start and stop the container using Jest's global setup/teardown functions further automated the solution.

One weird issue worth mentioning is that sometimes when Jest would exit it would leave a nasty error in my terminal and the Docker container wouldn't spin down.  After some research, the issue turned out to be a result of not releasing all the client connections to the database pool.  Ensuring `client.release()` occurs during the `finally` block of a try in `client.query(...)` resolved the issue completely.

At this point I was fairly impressed by just how well this worked, thinking little about how CI would dash all my hopes and dreams.  After naively thinking `dockerode` would just work in Gitlab CI, I had to go completely back to the drawing board. After doing some research, Postgres turned out to be pretty simple to setup within the [gitlab-ci.yaml](https://docs.gitlab.com/ee/ci/services/postgres.html) file.  Unfortunately, it didn't support seeding the database with a schema in any capacity.  Apparently, I'm not the only person to [bemoan](https://gitlab.com/gitlab-org/gitlab-runner/-/issues/3210) this lack of functionality.  My local solution mounted my schema file at `dockerinit.d`.  After some thought I decided to refactor the logic in the `globalSetup` Jest function to simply start the container, using the `pg` library directly to insert the schema in the `beforeAll` method of my test suite.  Importantly, the code to start the container would only run if the `CI` env var injected by Gitlab was not present.

After separating container logic from SQL inserts, we finally had our golden calf.  Tests ran both locally and in CI.  It wasn't particularly easy to accomplish and documentation on this entire effort was oddly lacking.  Hopefully, this post will make it easier for others to accomplish similar outcomes on the future.

Cheers 🍻!