Get off of slonik fork

[lognaturel]

We are currently using a fork of slonik: https://github.com/alxndrsn/slonik/commit/b0299faa6405e474ea84d6c96dffecc98001a6e2

This is because of some stream-related issues.

At least one related patch has been accepted to upstream node-postgres: https://github.com/brianc/node-postgres/pull/2836

History of postgres clients used in Central:

• Started out with knex: chaining based query builder but wasn’t great to work with
  • Streaming-related bugs
• Swapped to slonik because it seemed closer to desired interface
  • Tests ran 30% slower than knex (not clear on production impact)
  • Experienced several streaming-related bugs
  • Single maintainer has come and gone in availability
• July 2022, started considering postgres.js
  • Lots of energy behind it
  • Uses a similar tag template literal interface as Slonik
  • issa discovered after finished Slonik work
  • Would have switched to it, but at the time, it didn’t support composing SQL fragments ("partials"), now introduced
  • Transactions weren't initially working, at least in tests
  • Initial PR: https://github.com/getodk/central-backend/pull/564
  • Lots of tiny differences from Slonik. For the most part, it’s an improvement. A lot of things that we did by hand before, porsager postgres has an answer.
  • porsager postgres has some weird quirks of its own though, e.g., no join utilities.
  • So far it’s a code reduction. No queries so far have needed to change. Only core infrastructure has changed so far.
  • Streaming issues - https://github.com/porsager/postgres/issues/455; would need to add support for multiple streams

A barrier to upgrading slonik has been several breaking changes since the version we were on. It seems there's likely a more deliberate approach to breaking changes now: https://github.com/gajus/slonik/issues/450#issuecomment-1409464447

There is now a bridge to use slonik on top of postgres.js instead of node-postgres: https://github.com/gajus/postgres-bridge

[matthew-white]

Great to have these notes centralized here! Here are a few from me:

• I encountered a variety of errors while trying out the initial PR for postgres.js. It could be that they all trace back to the issues with streaming, but that doesn't feel certain to me. Some of the errors were thrown in the postgres.js code, while others were thrown in our code (though could still stem from postgres.js).
• One thing that's changed in Slonik is how SSL is configured. That's mostly a good thing, because it could allow us to offer additional configuration options to our users. We know that some users have SSL configured. I wanted to make a note of this change because I think it'd be easy to miss among the other Slonik changes. One piece of awkwardness is that whatever SSL configuration we offer for Slonik will need to be available in Knex as well, since we still use Knex for migrations. Backend issue about SSL configuration: getodk/central-backend#433
• Bumping Slonik would allow us to adjust the batch size. See #948
• Bumping Slonik should resolve the MaxListenersExceededWarning that's mentioned here: #1228