Test cases should use Postgres and not sqlite:///:memory:

[brassy-endomorph]

Is your feature request related to a problem? Please describe.

There is drift between the test and prod environment, and with something as complex as Postgres and remote state, we don't want that drift. Especially if we ever use any PG specific features

Describe the solution you'd like

In conftest.py we should have a fixture that gives us a fresh PG database with which to test on with scope="function". We can use PG template DBs as a fixture with scope="session" to only have to create the tables once and that use that in the fixture the provides the DB to the test cases.

Additional context

This may be a blocker for #528 and if it's not, we'd have to likely add a bunch of Python that would only ever be needed for the test cases and not the real app code, and that's some serious code smell IMO.

This is very likely a blocker for meaningfully implementing #555