Easy Mongo from your Postgres

MongoDB got an early start as a developer friendly database. Mongo was trivial to get started with and has some good tooling for scaling. Since you didn't have to create a schema, you were off and running in no time. Don't get us wrong, we do firmly believe a well designed schema is important. If you're not managing your schema it's managing you. Because of these capabilities, MongoDB has been used by teams for:

• the typical, primary application database
• supporting database for large, sharded JSON datasets
• rolling log using MongoDB’s capped collections
• rapidly updating JSON documents using MongoDB’s atomic updates

In the open-source database world we’re seeing a lot of customers working with both Mongo and Postgres. We’re excited to announce today that for all new Crunchy Bridge deployments, you will have access to the MongoDB FDW extension. Said another way, it is a foreign data wrapper that will represent data in MongoDB via Postgres.

You can run the following query in MongoDB:

db.purchases.find({ _id: ObjectId('6408fab340abb74a9d616083') })

And it will return the object in MongoDB. By connecting your Postgres to Mongo via the FDW, you can then run the following in your Postgres:

SELECT * FROM mongo_purchases WHERE _id = '6408fab340abb74a9d616083';

It doesn't have to be Mongo vs. Postgres. The two can be used in conjunction, in fact a number of our customers are adopting both. If you are using both, you shouldn't have to go through complicated pipelines and ETL to allow them to talk to each other, they can be friends. Let's dig in at connecting Crunchy Bridge, our fully managed Postgres-as-a-service and MongoDB Atlas, a fully managed MongoDB-as-a-service. One multi-cloud DBaaS meets another.

Getting Started

To get started, do the following:

1. Signup & create a Crunchy Bridge database, we have low cost plans available.

2. Connect to your MongoDB Atlas database, where we will need to get a few variables for configurations. First, we’ll need the replica set name, and we can get that by running as a user with MongoDB Atlas Admin privileges:

   rs.status().set // = `replica-set` value used later in tutorial
   

   Then, run the following to get a host and port that we will use later. It will be in the format <mongo-host>:<mongo-port>, which we’ll use below:

   rs.status().members.forEach(function (m) {
   	print(m.name)
   })
   

   Choose any of the hosts listed — it does not matter. The MongoDB driver built into MongoFDW will automatically connect to the primary during writes.

3. Connect to your Crunchy Bridge database as the postgres user, and run the following:

   CREATE EXTENSION mongo_fdw;
   
   -- create the mongodb server
   CREATE SERVER atlas_server
   	FOREIGN DATA WRAPPER mongo_fdw
   	OPTIONS (
   		address '<mongo-host>', -- from step #2
   		port '<mongo-port>', -- from step #2
   		ssl 'true',
   		replica_set '<replica-set>', -- from step #2
   		authentication_database 'admin',
   		weak_cert_validation 'true'
   	);
   
   -- create user
   CREATE USER MAPPING FOR postgres
   	SERVER atlas_server
   	OPTIONS (username '<your-mongodb-username>', password '<your-mongodb-password>');
   
   -- connect tables
   CREATE FOREIGN TABLE mongo_superheroes
   	(
   		_id name,
   		name text,
   		data json, -- jsonb not allowed
   	  created_at timestamp
   	)
   	SERVER atlas_server
   	OPTIONS (database 'superhero-database', collection 'superheroes');
   

If you run into errors, double check the host, port, replica set, username, and password are correct. If those are correct, check that your MongoDB Atlas firewall permits connecting from the Postgres host.

Working with data

Once you have configured MongoFDW, you’ll be able to work with the MongoDB data. Inserts are as native as you can imagine:

INSERT INTO mongo_superheroes (name, data, created_at) VALUES ('batman', '{"superpowers": ["moral compass", "innovative"]}', now());

Then, we can query data from that table:

SELECT * FROM mongo_superheroes;

Which returns something like the following.

           _id            |  name  |                         data                          |       created_at
--------------------------+--------+-------------------------------------------------------+-------------------------
 640a40334d7edb1a9921b2f2 | batman | { "superpowers" : [ "moral compass", "innovative" ] } | 2023-03-30 20:23:14.974

Now, you can log into your MongoDB, and run the following from that superhero-database:

db.superheroes.find()

which returns:

[
  {
    _id: ObjectId("640a40334d7edb1a9921b2f2"),
    name: 'batman',
    data: { superpowers: [ 'moral compass', 'innovative' ] },
    created_at: ISODate("2023-03-30T20:23:14.974Z")
  }
]

Go forth and conquer

So there you have it - a developer friendly way to merge MongoDB and Postgres into a truly hybrid data structure. We can see it being used for ETL processes that sync data from a MongoDB application to a Postgres warehouse for analysis. We can also see where you might want to write data to MongoDB directly from Postgres. We are looking forward to what you all end up doing with this new foreign data wrapper.