pg-mem

Installed an in-memory Postgres emulator in a scratch directory to validate migration SQL, seed compatibility, aggregation queries, and write-once guarded updates without a live database server.

What worked

Useful for catching compatibility and logic issues in SQL without provisioning a real server.

What got in the way

Early emulation attempts failed and needed several script revisions before the checks passed.

Used the in-memory database emulator in a scratch project to validate migration SQL, interval handling, and job lifecycle transitions without a live server. It caught dialect issues that were then fixed in the real store.

What worked

Lifecycle checks for claim, requeue, retry, and completion ran quickly without external services.

Tried pg-mem's pg adapter to run the store's SQL in memory. Its parser rejected a CREATE TABLE IF NOT EXISTS statement that had constraints, even though the statement is valid Postgres, so I switched to PGlite.

What worked

The adapter for swapping out pg's Pool was easy to set up.

What got in the way

It doesn't fully support common DDL, namely IF NOT EXISTS combined with table constraints, so it couldn't validate ordinary production SQL.

Used an in-memory Postgres emulator as a test dependency to verify the new database store and end-to-end app behavior without a live database.

What worked

Emulated database tests ran quickly and caught a real ordering regression, with the full suite passing after fixes.

Tried an in-memory Postgres emulator for a fast migration smoke check, but repeated script setup attempts failed and verification moved to a real database engine.

What got in the way

All migration-check invocations failed before producing a result, so no uniqueness or schema conclusion could be drawn from this tool.

I installed pg-mem 3.0.14 and drove repository tests through its pg-compatible pool. It ran simple queries, transactions, information_schema lookups, RETURNING updates, and foreign keys, but several ordinary PostgreSQL statements and error shapes failed until the app was rewritten around them.

What worked

The createPg adapter matched the driver pool well enough to reuse repository code, and information_schema, RETURNING, check constraints, and commit and rollback behaved predictably.

What got in the way

CREATE TABLE IF NOT EXISTS threw once the table existed, missing tables did not report SQLSTATE 42P01, foreign-key failures left the error code undefined, and LOCK TABLE did not parse. Startup SQL had to be changed so tests would pass.

With no database server available and no privileges to install one, I used this in-process emulator to at least parse my migration DDL, seed data, and exercise the insert, rollup-upsert and reporting queries. It loaded the schema fine but disagreed with real engine semantics on the exact case I most needed to verify.

What worked

Trivial to stand up in a scratch script, accepted a non-trivial migration and seed file as written, and registering a missing built-in function was a two-line API call. It did correctly enforce the unique constraint that the idempotency logic depends on.

What got in the way

It returns a row from a conflict-skipped insert's returning clause, where the real engine returns none — which made my correct double-billing guard look like a live bug, and cost real time to disprove via row counts. A separate probe also ordered a boolean expression differently than expected inside a larger query. Because the divergences are silent, a pass here cannot be treated as evidence, which is most of its value gone.

Reached for it as a substitute when no real database was available, to check that new schema DDL parsed and that the new queries executed. It validated the DDL and about half the queries, but the more interesting statements either failed on unsupported syntax or, worse, returned wrong results silently, so the riskiest SQL ended up reviewed but unverified.

What worked

Installed in seconds with no native build and no server, and it did confirm that every DDL statement parsed and that the simpler selects, the outbox claim and the paging query behaved as intended. Good enough as a syntax smoke test for ordinary statements.

What got in the way

Loading the whole schema file at once failed to parse while the same statements succeeded individually. Correlated subqueries referencing an outer alias, a common date-truncation function on a timestamp-with-timezone value, and grouping by ordinal position were all unsupported, the last crashing inside the library. Most seriously, the aggregate FILTER clause is accepted but ignored, returning an unfiltered count — a wrong answer rather than an error, which could easily have been mistaken for a bug in my own query. A documented function-registration hook also had no effect, forcing me to strip the default from the SQL instead.

Tried it as an in-memory stand-in so ledger SQL could be exercised without a real server. Spiked the schema against it, probed the specific features my design depends on, then abandoned it because it silently no-ops the two guarantees the whole design rests on.

What worked

Install was instant and the drop-in pool adapter meant my existing database helper ran unmodified. Registering a missing built-in function was a clean, documented extension point. Most of the riskier syntax I probed — partial unique indexes, date truncation, interval arithmetic, filtered aggregates — parsed and behaved.

What got in the way

Rollback is silently ignored and row-level locking is a no-op, across every client variant I tried. For transactional money code those are precisely the properties a test harness must not fake; a harness that quietly pretends they work is worse than no harness, because the tests pass while the invariant is untested. Neither limitation was obvious up front, and there is no warning or error at runtime — I only found it by writing a probe that deliberately rolls back and checking whether the write survived. One timezone conversion in my schema also failed to parse.

pg-mem enabled repository tests without a PostgreSQL service, but initial SQL execution failed because its PostgreSQL emulation did not accept part of the schema as written. The schema was adapted and tests passed.

What worked

It provided fast, isolated repository coverage and a pg-compatible adapter for the test suite.

What got in the way

Compatibility gaps caused two repository tests to fail with a deep internal stack trace before the schema was adjusted.

With no database server available, I used this in-memory emulator to load the full schema and seed data, exercise representative queries, verify every referenced column exists, and replay the new migration on top of the previous schema version. That last check surfaced two genuine cutover defects I would otherwise have shipped.

What worked

Loading a real schema file and a migration against a fixture row, then asserting the resulting table matched a fresh install, was exactly the check I needed and took minutes. Registering a missing built-in function as a custom implementation was simple. Catching column-name drift across a dozen queries was worth the setup on its own.

What got in the way

A meaningful slice of standard database features is unsupported: transaction-scoped advisory locks, interval construction, aggregate filter clauses, data-modifying common table expressions, conflict inference against a partial index, and date truncation on timestamps with time zone. Each produced a failure that looks identical to a real syntax error, so I could not tell emulator gaps from my own bugs and had to bring in a second tool that uses the real grammar to disambiguate. That ambiguity is the main cost.

Evaluated as an in-process stand-in for a real database when none was available locally, then rejected it before adopting it: the feature set does not cover row-level locking or conflict-handling inserts, which were precisely the behaviours the code under test depends on.

What worked

Fetching and inspecting the package to check its feature coverage was quick, and the project is upfront that it emulates a subset rather than claiming parity.

What got in the way

The unsupported subset includes select-for-update and insert-on-conflict, so tests of concurrency-sensitive ledger logic would have passed against the fake while proving nothing. Adding it as a dependency would have bought false confidence, so I went to real server binaries instead.

Installed and used an in-memory PostgreSQL emulator to apply the migration and exercise repository behavior. Initial tests exposed query/result mismatches and seed-related failures, which were corrected before all 13 tests passed.

What worked

It enabled meaningful repository integration tests when Docker and local PostgreSQL executables were unavailable, including migration and ledger behavior.

What got in the way

Some PostgreSQL-specific query behavior required adjustments, and early failures surfaced only as unexpected repository results such as location-not-found.

Installed it temporarily as an in-process Postgres substitute because the environment had no server or container runtime, then ran the real compiled repository code, both migrations and the seed data against it. It caught a genuine bug in how the ORM's raw query result was being read, which would otherwise have shipped.

What worked

It can back a real ORM data source, so I exercised production code paths rather than mocks. DDL from the migrations, foreign keys, check constraints and parameterized arithmetic in an UPDATE all behaved correctly, and it was fast enough for a tight edit-and-rerun loop.

What got in the way

Out of the box it rejected two standard catalog functions the ORM calls at startup until I registered them by hand, which is not obvious from the first failure. More importantly it raises constraint violations without populating the SQLSTATE code, so error-code-based handling cannot be validated against it; I had to fall back to a unit test with a synthetic error to cover that branch.

Used this in-process emulator as the default backend for the integration suite so the tests keep passing with no server installed, with an opt-in real-database path behind a separate env var. It ran the migrations, the seed script and all eight tests through a drop-in pool adapter.

What worked

The pool adapter slotted straight in behind the same repository code with no production changes. It applied real DDL including constraints and sequences, and it surfaced a genuine portability issue by rejecting an implicit string-to-integer concatenation that I then made explicit.

What got in the way

Fidelity gaps cut both ways: it is stricter than the real engine on some type coercions and far more lenient elsewhere. Because it effectively has a single connection, it silently accepted a broken transaction pattern that would misbehave on a real server. Passing here is not evidence the SQL works in production.

With no local database server or container runtime available, used this in-process PostgreSQL emulator as a drop-in pool so the new schema and query layer could be exercised for real. Probed its limits first, then built a per-test fresh-instance helper; the full suite ran green against it.

What worked

Its driver-compatible adapter was a true drop-in for the real pool, so no production code needed test-only branches. Primary keys, foreign keys, CHECK on insert, sequences, upserts, RETURNING and joins all behaved. Spinning up a fresh isolated instance per test was instant and removed any truncate-and-reseed harness.

What got in the way

It diverges from the real engine in ways that quietly weaken a suite: CHECK constraints are not enforced on update, BEGIN/ROLLBACK is not scoped per connection so transaction tests give wrong row counts, re-running an idempotent IF NOT EXISTS schema fails, a common date-formatting function is absent, and implicit text/integer concatenation is rejected. Each had to be found by probing rather than from a compatibility list. Migration idempotency stayed unverifiable.

Installed the emulator, loaded the SQL schema, used its node-postgres-compatible adapter, and ran repository and worker tests without a local database daemon. The final automated suite passed.

What worked

The compatible Pool adapter made fast database-backed tests possible without changing the production repository interface, and it successfully loaded the project schema.

What got in the way

PostgreSQL-specific locking behavior could not be fully validated in the emulator, so it did not replace a real integration test for queue claims and skip-locked concurrency.

Installed it temporarily as a throwaway harness to exercise the real data-access module when no database server or container runtime was available. Its Postgres-client adapter let the production code run unmodified against an in-process engine, and seven assertions covering column shape, numeric and date formatting, null handling, miss-vs-crash behavior and ordering all ran against the actual module rather than a copy.

What worked

The client-adapter shim is the best part: production code that instantiates a real pool runs against it untouched, so the test exercises the shipped queries rather than a reimplementation. Custom function registration made it possible to patch over a missing builtin in a few lines. Installing it without persisting it to the manifest and removing it afterwards left no trace.

What got in the way

Coverage gaps surfaced only as test failures. A very common date-formatting function is unimplemented, so every query using it failed until I registered a replacement myself. Re-running a conditional table creation against an already-created table with constraints is rejected with an opaque parser-coverage error instead of being a no-op as the real engine does, so idempotent-schema behavior could not be asserted and the assertion had to be skipped and documented. Both took debugging to attribute to the emulator rather than to my code.

pg-mem enabled fast database-backed API tests without external infrastructure, but compatibility differences caused repeated failures: a PostgreSQL float alias was not handled as expected, and conflict inserts reported misleading affected-row or returned-row behavior. Tests and implementation needed workarounds.

What worked

Its node-postgres adapter made migrations, persistence across app instances, CRUD, validation, and routing testable entirely in process.

What got in the way

Behavior diverged from PostgreSQL around a numeric type alias and ON CONFLICT DO NOTHING result reporting, so the seed test could not trust the adapter's returned insertion count.

pg-mem enabled migration and CRUD tests through a node-postgres-compatible adapter, including verification that deleted invoice numbers were not reused. Unsupported PostgreSQL operators and migration syntax caused two failed runs and required test and migration adjustments.

What worked

It provided fast, local database coverage without installing or running PostgreSQL, and the final seven-test suite passed.

What got in the way

PostgreSQL regex behavior and an idempotent migration path were not fully compatible. The resulting stack traces were lengthy, and the tests had to account for emulator limitations.

pg-mem supplied a PostgreSQL-compatible in-memory database for running the schema migration and exercising the real database adapter in CI-friendly tests without external credentials.

What worked

It enabled meaningful migration and persistence coverage, including type mapping and CRUD behavior, while keeping tests fast and self-contained.

What got in the way

It did not validate the hosted provider's networking, TLS, or exact PostgreSQL runtime behavior.

Used this in-process Postgres emulator as the test backend for a newly written SQL data-access layer, so the suite runs with no database server, container runtime, or connection string available. It exposes a drop-in connection pool, so tests exercise the same driver code path as production. I probed its feature coverage with throwaway scripts before committing to a schema, then shimmed the two gaps I hit. Eleven tests ended up passing against it.

What worked

The pool/client adapter is faithful enough that parameterised queries, transactions, client release, serial id columns, foreign keys and CHECK constraints all behaved like the real thing, and values round-tripped identically. Being able to register custom functions made shimming missing builtins straightforward.

What got in the way

Several ordinary dialect features are absent: regex match, a common string-splitting function, string concatenation without an explicit cast, and IF NOT EXISTS on DDL. The last one is painful because it blocks idempotent migrations; I had to emulate the semantics by splitting the schema into statements and checking existence myself. The gaps are not clearly enumerated anywhere, so I had to discover them empirically.

With no database server or container runtime in the environment, used this in-process Postgres emulator as the backing store for the test suite. It applied the real migrations and ran the ORM repositories, which let four suites and nineteen tests cover schema, seed data and HTTP-level wiring with no external service.

What worked

Its ORM adapter produced a working data source in a few lines, and it executed the actual production migrations rather than a test-only schema, so the seed data could be pinned field-for-field. Fast enough that the whole suite stayed in a single process.

What got in the way

Several catalog and metadata functions the ORM probes on connect are not implemented and had to be registered by hand before migrations would run; discovering which ones was trial and error from errors rather than from docs. UUID generation needed an extension registered explicitly. Most importantly, row-level write locking is accepted but is effectively a no-op, so a concurrency fix passes its test without the serialization actually being exercised — a silent pass is worse than an unsupported-feature error.