Open-source🍃MongoDB API to 🚀YugabyteDB with 🥭MangoDB proxy
Tags: postgres, yugabytedb, mongodb, ferretdb
Published: 2021-11-03 13:59:00



_Please note that MangoDB is now called [FerretDB](https://www.ferretdb.io/2021/12/01/mangodb-has-a-new-name-and-the-momentum-is-stronger-than-ever-meet-ferretdb/)_ and has moved to:
- https://www.ferretdb.io
- https://github.com/FerretDB

There is no doubt that one great thing about MongoDB is the API. Many developers loves it. I'm a big fan of SQL, but we need to listen to all users, and they have use-cases for it. Another thing we expect from NoSQL is the scalability.

In this example we have both, open-source, ACID and resilient, with
 - MangoDB proxy between MongoDB and PostgreSQL protocols
 - YugabyteDB with its PostgreSQL compatible API on top of the fully consistent distributed storage

This MangoDB project is new, and when looking for it you will see Google still trying to tell you that you made a typo. So this post is a first quick test to validate how it works. Things will probably change with contributions to https://github.com/MangoDB-io/MangoDB

## MangoDB example

I'll take de demo application from:
```
git clone https://github.com/MangoDB-io/example
cd example
```
and install it on my laptop because I became recently a big fan of Docker on Windows.

The `docker-compose.yml` starts a PostgreSQL database and I'll replace that with a YugabyteDB one. This is easy.

## postgres service

First, I remove the `postgres` service. And add the `yb-master` and `yb-tserver` ones from [YugabyteDB docker-compose.yml](https://docs.yugabyte.com/latest/deploy/docker/docker-compose/#create-a-docker-compose-yaml-file)

I didn't change any parameters, and I keep the defaults:
- host name is yb-tserver
(in a distributed database you can connect to any server)
- port is 5433
(this is our default, rather than the 54322 default for PostgreSQL)
- user is yugabyte
(and password is the same)
- database is yugabyte
(you can create a dedicated one of course)

## setup service

I change this in the setup service command, which starts just to create the schema for the application (which is a "todo" list in this example):
```
psql -h yb-tserver -p 5433 -U yugabyte -d yugabyte -c 'CREATE SCHEMA IF NOT EXISTS todo'
```

and the docker-compose dependecy is set to `yb-tserver` instead of postgres

## mangodb service

The application also needs the connection string. We use the PostgreSQL driver as it is the same protocol, and change the host, port and database name only:

```
    depends_on:
      - 'postgres'
    entrypoint: ["sh", "-c", "psql -h postgres -U postgres -d mangodb -c 'CREATE SCHEMA IF NOT EXISTS todo'"]
```


## Start the application

Here is my final `docker-compose.yml`:
```yaml
version: "3"

volumes:
  yb-master-data-1:
  yb-tserver-data-1:

services:
  client:
    build: ./app/client
    hostname: 'todo_client'
    container_name: 'todo_client'
    stdin_open: true
  api:
    build: ./app/api
    hostname: 'todo_api'
    container_name: 'todo_api'
  nginx:
    image: nginx
    hostname: 'nginx'
    container_name: 'nginx'
    ports:
      - 8888:8888
    volumes:
      - ./nginx.conf:/etc/nginx/conf.d/default.conf
  yb-master:
      image: yugabytedb/yugabyte:latest
      container_name: yb-master-n1
      volumes:
      - yb-master-data-1:/mnt/master
      command: [ "/home/yugabyte/bin/yb-master",
                "--fs_data_dirs=/mnt/master",
                "--master_addresses=yb-master-n1:7100",
                "--rpc_bind_addresses=yb-master-n1:7100",
                "--replication_factor=1"]
      ports:
      - "7000:7000"
      environment:
        SERVICE_7000_NAME: yb-master
  yb-tserver:
      image: yugabytedb/yugabyte:latest
      container_name: yb-tserver-n1
      volumes:
      - yb-tserver-data-1:/mnt/tserver
      command: [ "/home/yugabyte/bin/yb-tserver",
                "--fs_data_dirs=/mnt/tserver",
                "--start_pgsql_proxy",
                "--rpc_bind_addresses=yb-tserver-n1:9100",
                "--tserver_master_addrs=yb-master-n1:7100"]
      ports:
      - "9042:9042"
      - "5433:5433"
      - "9000:9000"
      environment:
        SERVICE_5433_NAME: ysql
        SERVICE_9042_NAME: ycql
        SERVICE_6379_NAME: yedis
        SERVICE_9000_NAME: yb-tserver
      depends_on:
      - yb-master
  mangodb:
    image: ghcr.io/mangodb-io/mangodb:latest
    hostname: 'mangodb'
    container_name: 'mangodb'
    command:
      [
        '-listen-addr=:27017',
        '-postgresql-url=postgres://yugabyte@yb-tserver:5433/yugabyte',
      ]
    ports:
      - 27017:27017
  setup:
    image: postgres:14.0
    hostname: 'setup'
    container_name: 'setup'
    restart: 'on-failure'
    depends_on:
      - 'yb-tserver'
    entrypoint: ["sh", "-c", "psql -h yb-tserver -p 5433 -U yugabyte -d yugabyte -c 'CREATE SCHEMA IF NOT EXISTS todo'"]

```

I pull the images, create the containers and run the services:

```
docker-compose up
```
Here is the start from command line:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8a4z4i44evw6o7213sp0.png)
Visible in Docker Desktop:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lonl28ubqio4c1hqkx7y.png)

The YugabyteDB console is available on: http://localhost:7000

# The example application:

The application is accessible on http://localhost:8888/ and we can add items to the To-Do list:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vp4rbm40t49e149y4zva.png)
This calls the [db.collection.insertOne()](https://github.com/MangoDB-io/example/blob/707a8d84c4a285706456bc7d565f18096f13d517/app/api/routes/tasksRoutes.js#L19) MongoDB function:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/un3l2d1hrr0o1nmo8hzx.png)

# Check the database

This MongoDB call is translated to SQL by the MangoDB proxy. The collection is a table:
```
$ psql postgres://yugabyte:yugabyte@localhost:5433/yugabyte
psql (12.7, server 11.2-YB-2.9.0.0-b0)
Type "help" for help.

yugabyte=# \dn

  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 todo   | yugabyte
(2 rows)

yugabyte=# set schema 'todo';
SET

yugabyte=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 todo   | tasks | table | yugabyte
(1 row)

yugabyte=# \d+ todo.tasks
                                   Table "todo.tasks"
 Column | Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-------+-----------+----------+---------+----------+--------------+-------------
 _jsonb | jsonb |           |          |         | extended |              |

yugabyte=# select * from todo.tasks;
                                                                    _jsonb
-----------------------------------------------------------------------------------------------------------------------------------------------
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182627a17462641b80439d4"}, "completed": false, "description": "Play 😎"}
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182627017462641b80439d3"}, "completed": false, "description": "Start MangoDB"}
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "6182626817462641b80439d2"}, "completed": false, "description": "Start YugabyteDB"}
(3 rows)

yugabyte=#

```
The storage is very simple: one table with one JSONB column.

# SQL Statements

I'll track the statements used with the pg_stat_statements extension which is enabled by default in YugabyteDB. Just resetting in my lab:
```
yugabyte=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)
```

In the application I refresh, mark "Play" as completed, Insert a new task, delete it, and refresh multiple times.

```
yugabyte=# select calls,query from pg_stat_statements;
 calls |                                                        query
-------+----------------------------------------------------------------------------------------------------------------------
     1 | select query from pg_stat_statements
     1 | INSERT INTO "todo"."tasks" (_jsonb) VALUES ($1)
     1 | DELETE FROM "todo"."tasks" WHERE _jsonb->$1 = $2
     1 | SELECT _jsonb FROM "todo"."tasks" WHERE _jsonb->$1 = $2
     1 | UPDATE "todo"."tasks" SET _jsonb = $1 WHERE _jsonb->'_id' = $2
     7 | SELECT _jsonb FROM "todo"."tasks"
     1 | select pg_stat_statements_reset()
    11 | SELECT COUNT(*) > 0 FROM information_schema.columns WHERE column_name = $1 AND table_schema = $2 AND table_name = $3
    11 | SELECT COUNT(*) > 0 FROM information_schema.tables WHERE table_schema = $1 AND table_name = $2
     1 | select * from pg_stat_statements
(10 rows)
```

There are many things to optimize here. Reading the information_schema many time is not the most efficient. We need an index on the ID (which is in the JSON document). And updates are re-writing the whole document. I'll think about this and probably contribute to this open-source project. Probably the ID should be in another column, that we can properly index and shard, rather than scanning the whole table or adding an additional index.

# JSONB indexing

As YugabyteDB plugs the distributed storage to a full PostgreSQL query layer, we can even index this Here is the table with just one JSONB (it was created by with `CREATE TABLE "todo"."tasks" (_jsonb jsonb);`):

```
yugabyte=# select * from todo.tasks;
                                                              _jsonb
----------------------------------------------------------------------------------------------------------------------------------
 {"$k": ["description", "completed", "_id"], "_id": {"$o": "618282aea9a2a141efa3c401"}, "completed": false, "description": "Franck"}
(1 row)
```

If I select one key, it has to scan the whole table:
```
yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on tasks  (cost=0.00..105.00 rows=1000 width=32) (actual time=0.815..0.817 rows=1 loops=1)
   Filter: ((_jsonb ->> '_id'::text) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
 Planning Time: 0.048 ms
 Execution Time: 0.874 ms
(4 rows)
```

But I can create a unique index on it:
```
yugabyte=# create unique index task_pk ON todo.tasks
           ((_jsonb ->> '_id'::text) hash);
CREATE INDEX
```
Do not forget the double parenthesis (this is the PostgreSQL syntax): 
- one for the list of columns to index, 
- and one because it is not directly a column but a value derived from the JSON document.

The HASH modifier is optional here because hash sharding is the default on the first column. And this is what we want on this generated identifier. But if you have range scans, you could change it to ASC or DESC.

Now I have a fast access to the document:
```
yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using task_pk on tasks  (cost=0.00..4.12 rows=1 width=32) (actual time=13.617..13.622 rows=1 loops=1)
   Index Cond: ((_jsonb ->> '_id'::text) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
 Planning Time: 11.593 ms
 Execution Time: 13.706 ms
(4 rows)
```

This means that a query with the key will go to the right tablet (the tables and indexes are automatically sharded in YugabyteDB) and to the right row. We are ready to scale out and keep the low latency.

# Index Only Indexes

The previous execution plan may require two RPC on a scale-out database: one to the index and one to the table. Because, for better agility, all indexes are global in YugabyteDB. And, of course, with no compromise on strong consistency. But an Index Only Scan would be better. It is easy to acheive (I explained in [How a Distributed SQL Database Boosts Secondary Index Queries with Index Only Scan](https://blog.yugabyte.com/how-a-distributed-sql-database-boosts-secondary-index-queries-with-index-only-scan/)):
```
yugabyte=# create unique index task_pk ON todo.tasks
           ((_jsonb ->> '_id'::text) hash) include (_jsonb);
CREATE INDEX
```

And here is the fastest access you can have to a document on a SQL distributed database, still with the full agility of a JSON document:
```
yugabyte=# explain analyze select * from todo.tasks where _jsonb->>'_id' = '{"$o": "618282aea9a2a141efa3c401"}';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using task_pk on tasks  (cost=0.00..4.12 rows=1 width=32) (actual time=2.559..2.561 rows=1 loops=1)
   Index Cond: (((_jsonb ->> '_id'::text)) = '{"$o": "618282aea9a2a141efa3c401"}'::text)
   Heap Fetches: 0
 Planning Time: 5.229 ms
 Execution Time: 2.607 ms
(5 rows)
```

Now you have all in the index and don't need the table at all. In PostgreSQL you have no choice as you need to maintain the heap table. But YugabyteDB stores tables in LSM trees where rows are organized for fast access on the primary key. When storing documents into a SQL table, it is better to have the identifier in its own column, an integer or uuid, to really have a `(key uuid, value jsonb)` schema. I'll suggest that to the MangoDB project, as well as some other optimizations for PostgreSQL or YugabyteDB. But the essence is there: a simple MongoDB API to distributed SQL database.