refactor(pg): usage of PostgreSQL in the codebase

[Trantorian1]

Motivation

The current impl has us connect to a local PostgreSQL server as part of a connection Pool. This is abstracted behind the PostgresAdaptater class. While this is fine for simple queries, there currently isn't any good way to make more complex requests to the db (transactions in particular). It seems like a lot of this could be simplified using more of the core builtins provided by node-postgress.

Request

The following could be streamlined:

• database connection: node-postgress will already load pg env variables automatically to set up a connection: we should not need to set those up programatically.
• transactions and cursors: there currently isn't a way to retrieve a postgres client from the PostgresAdapter.
• graceful shutdown: Are we sure we are closing the sql pool connection properly on shutdown?
• instances: we probably want to replace PostgresAdapter with a singleton instance of Pool as it does not make sense to create multiple pooled connections to the db.

Scope

• Remove PostgresAdapter in favor of more direct usage of node-postgres. Error handling would still be central to database.ts but we would have no abstraction around different query type and would just be passing raw SQL and arguments. This has the upside that we would no longer be limited to a very specific set of elementary queries.
• Update existing code to use raw SQL queries instead of PostgesAdapter. This should make individual queries clearer.
• Integrate metrics Feat/prometheus #115 as part of database queries.
• Allow SQL transaction by making pooled clients available inside of SNAK.

Are you willing to help with this issue?

Yes!