Skip to content

Postgres 2 MongoDB database link

MongoDB FDW

Postgres foreign data wrapper for MongoDB database

This is part 4 of #fdw foreign data wrappers series of posts. Earlier we have introduced wrappers for mysql, oracle and sqlite databases. In this post we will connect to MongoDB database from within Postgres.

mongo_fdw

There are no binaries are available on official GitHub repository for this extension. You have to manually compile and install it. Compilation procedure is not straightforward. You will have to install a set of packages. Luckily, there is an autogen.sh script is present which simplifies a lot of things. Nevertheless you still have to install several additional utilities such as make, cmake, pkg-config.

When done with installation you will be able to setup it already within postgres database. Process is standard: execute CREATE EXTENSION command from within your postgres database. Afterwards create foreign server via CREATE SERVER command which specifies connection parameters to the MongoDB.

Docker image

Similarly to all other databases there is a postgres image available with mongo_fdw extension installed. You can pick it up on docker hub. I was unable to manage compilation under Postgres' alpine image and had to use full size image latest tag. Compiled library version resulted in +20 Mb to the 322 Mb of postgres uncompressed image size. Section below describes how to use the image.

Demo

In order to run commands below you should download or clone GitHub repo. It also contains readme file with project layout and files descriptions.

Demo infrastructure is spinned up via compose files. For our case we need two instances. One for MongoDB and one for Postgres. Inside mongo instance we will create a single table and populate it with 3 rows. From postgres instance we will select, insert and delete rows from this table. After that we will select from this table under mongo instance again to confirm that the data has been changed accordingly.

Note: If you use docker then just replace podman with docker in all commands below.

Let's start mongo and postgres instances. 

$ podman-compose -f postgres_mongo_compose.yml up -d

$ podman-compose -f postgres_mongo_compose.yml ps
CONTAINER ID  IMAGE                                        COMMAND   CREATED        STATUS            PORTS                                             NAMES
b87c08fce4c6  docker.io/chumaky/postgres_mongo_fdw:latest  postgres  4 minutes ago  Up 4 minutes ago  0.0.0.0:5432->5432/tcp, 0.0.0.0:27017->27017/tcp  postgres
5c504fc23b93  docker.io/library/mongo:latest               mongod    4 minutes ago  Up 4 minutes ago  0.0.0.0:5432->5432/tcp, 0.0.0.0:27017->27017/tcp  mongo

By default mongo starts without authorization. I.e. everyone could connect. We initialize mongo with root/root superuser role with admin authorization database to enable usual user/password authorization approach.

Within postgres database we automatically create mongo_fdw extension and map it to the corresponding mongo host. We also create foreign table users with the following structure. Initial _id attribute is required for MongoDB specific object id which is automatically assigned for every object. 

create foreign table users
( _id    name
, id     int
, name   text
) server mongo;

You could check postgres_mongo_compose.yml and sql/postgres_mongo_setup.sql files for details.

Now let's connect to the mongo instance. 

$ podman exec -it mongo mongo -u root -p root
MongoDB shell version v4.4.2
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("ba7b798c-ab5e-4de9-826c-69259700cf41") }
MongoDB server version: 4.4.2
Welcome to the MongoDB shell.
...
>

By default user connects to the test database. We can check that we currently use this database via the following command. 

> db
test
Where db is a special object which specifies current database user is connected to.

We already have users foreign table defined in postgres. In mongo instead of a table we should create a collection. Database and collection creation in mongo is implemented by "at first usage" approach. If referenced object doesn't exist it gets created automatically. Following command will automatically create in the current database new users collection and insert into it an object. 

> db.users.insertOne({id: 1, name: "John"})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5fee189c9b775b31e29362ea")
}
> db.users.find()
{ "_id" : ObjectId("5fee189c9b775b31e29362ea"), "id" : 1, "name" : "John" }

We can create many objects in one operation 

> db.users.insertMany([{id: 2, name: "Sarah"}, {id: 3, name: "Terminator"}])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5fee18e19b775b31e29362eb"),
                ObjectId("5fee18e19b775b31e29362ec")
        ]
}
> db.users.find()
{ "_id" : ObjectId("5fee189c9b775b31e29362ea"), "id" : 1, "name" : "John" }
{ "_id" : ObjectId("5fee18e19b775b31e29362eb"), "id" : 2, "name" : "Sarah" }
{ "_id" : ObjectId("5fee18e19b775b31e29362ec"), "id" : 3, "name" : "Terminator" }

Now let's connect to postgres instance. 

$ podman exec -it postgres psql postgres postgres
postgres=#

We can check that we already have mongo_fdw extension installed. We also have mongo foreign server and users foreign table defined. 

postgres=> \dx mongo_fdw
                       List of installed extensions
   Name    | Version |  Schema   |               Description
-----------+---------+-----------+-----------------------------------------
 mongo_fdw | 1.1     | mongo_fdw | foreign data wrapper for MongoDB access

postgres=> \des
         List of foreign servers
 Name  |  Owner   | Foreign-data wrapper
-------+----------+----------------------
 mongo | postgres | mongo_fdw

postgres=> \det
 List of foreign tables
 Schema | Table | Server
--------+-------+--------
 public | users | mongo

postgres=> \d users
                  Foreign table "public.users"
 Column |  Type   | Collation | Nullable | Default | FDW options
--------+---------+-----------+----------+---------+-------------
 _id    | name    |           |          |         |
 id     | integer |           |          |         |
 name   | text    |           |          |         |
Server: mongo

Let's check SELECT and UPDATE operations against users table. 

postgres=> select * from users;
           _id            | id |    name
--------------------------+----+------------
 5fee189c9b775b31e29362ea |  0 | John
 5fee18e19b775b31e29362eb |  0 | Sarah
 5fee18e19b775b31e29362ec |  0 | Terminator
(3 rows)
For some reason id attribute is displayed as 0 while in mongo it actually has a value. Probably there is a bug with INTEGER attributes mapping. Anyway, let's try to update them.

postgres=> update users set id = 1, name = 'John Connor' where name = 'John';
UPDATE 1
postgres=> update users set id = 2, name = 'Sarah Connor' where name = 'Sarah';
UPDATE 1
postgres=> select * from users;
           _id            | id |     name
--------------------------+----+--------------
 5fee189c9b775b31e29362ea |  1 | John Connor
 5fee18e19b775b31e29362eb |  2 | Sarah Connor
 5fee18e19b775b31e29362ec |  0 | Terminator
(3 rows)

We could not only select and change existing data but also INSERT and DELETE. 

postgres=> delete from users where name = 'Terminator';
DELETE 1
postgres=> insert into users (id, name) values (3, 'James Cameron');
INSERT 0 1
postgres=> select * from users;
           _id            | id |     name
--------------------------+----+---------------
 5fee189c9b775b31e29362ea |  1 | John Connor
 5fee18e19b775b31e29362eb |  2 | Sarah Connor
 5fee1c83abed8c006c124982 |  3 | James Cameron
(3 rows)

Now go back to mongo console and check that the data have been changed accordingly. 

> db.users.find()
{ "_id" : ObjectId("5fee189c9b775b31e29362ea"), "id" : 1, "name" : "John Connor" }
{ "_id" : ObjectId("5fee18e19b775b31e29362eb"), "id" : 2, "name" : "Sarah Connor" }
{ "_id" : ObjectId("5fee1c83abed8c006c124982"), "id" : 3, "name" : "James Cameron" }

As you can see we have full SQL and DML support over MongoDB right from the Postgres!

Summary

mongo_fdw extension allows SELECT and DML operations to be triggered from the Postgres and applied to the MongoDB database. For quickier setup there is a chumaky/postgres_mongo_fdw docker image is available on the docker hub. It contains mongo_fdw extension pre-installed.

Comments