Pongo - Mongo but on Postgres and with strong consistency benefits

Pongo - Mongo but on Postgres and with strong consistency benefits 

Flexibility or Consistency? Why not have both? Wouldn’t it be great to have MongoDB flexible schema and PostgreSQL consistency?

MongoDB is a decent database, but it gives headaches with its eventual consistency handling. I wrote about it a few times in past:

• Dealing with Eventual Consistency and Idempotency in MongoDB projections
• Long-polling, how to make our async API synchronous
• How to test event-driven projections

Don’t get me wrong, eventual consistency is fine. We need to learn to live with that, still… Undeniably, having strong consistency guarantees, transactions, read your own writing is great.

On Friday, I decided to spend my working day on the small proof of concept that I called Pongo.

What’s Pongo?

It’s a MongoDB-compliant wrapper on top of Postgres.

You can setup it like that:

import { pongoClient } from "@event-driven-io/pongo";

const connectionString =
  "postgresql://dbuser:secretpassword@database.server.com:5432/yourdb";

const pongoClient = pongoClient(postgresConnectionString);
const pongoDb = pongoClient.db();

const users = pongoDb.collection  

It will start internally with a PostgreSQL connection pool connected to your selected database.
Having that, you can then perform operations like:
const anita = { name: "Anita", age: 25 };

// Inserting
await pongoCollection.insertOne(roger);
await pongoCollection.insertOne(cruella);

const { insertedId } = await pongoCollection.insertOne(alice);
const anitaId = insertedId;

// Finding by Id
const anitaFromDb = await pongoCollection.findOne({ _id: anitaId });

// Updating
await users.updateOne({ _id: anitaId }, { $set: { age: 31 } });

// Deleting
await pongoCollection.deleteOne({ _id: cruella._id });

// Finding by Id
const anitaFromDb = await pongoCollection.findOne({ _id: anitaId });

// Finding more
const users = await pongoCollection.find({ age: { $lt: 40 } });
Internally, it’ll set up the collection as the PostgreSQL table with the key-value structure:

CREATE TABLE IF NOT EXISTS "YourCollectionName" (
    _id UUID PRIMARY KEY, 
    data JSONB
)
Essentially, it treats PostgreSQL as a key/value database. Sounds familiar? Yet, it’s a similar concept to Marten or, more correctly, to AWS DocumentDB (see here or there, they seem to be using Mongo syntactic sugar on top of AuroraDB with Postgres).
I explained in general strategy for migrating relational data to document-based that contrary to common belief, document data is structured but less rigidly, as in the relational approach. JSON has structure, but it is not enforced for each document. We can easily extend the schema for our documents, even for specific ones, by adding new fields. We should also not fail if the field we expect to exist, but doesn’t.
Handling semi-structured data in a relational database can be tricky, but PostgreSQL’s JSONB data type offers a practical solution. Unlike the plain text storage of the traditional JSON type, JSONB stores JSON data in a binary format. This simple change brings significant advantages in terms of performance and storage efficiency.
The binary format of JSONB means that data is pre-parsed, allowing faster read and write operations than text-based JSON. You don’t have to re-parse the data every time you query it, which saves processing time and improves overall performance. Additionally, JSONB supports advanced indexing options like GIN and GiST indexes, making searches within JSONB documents much quicker and more efficient.
Moreover, JSONB retains the flexibility of storing semi-structured data while allowing you to use PostgreSQL’s robust querying capabilities. You can perform complex queries, joins, and transactions with JSONB data, just as you can with regular relational data.