PostgreSQL tables like Mongo collections

[ArturFormella]

Is it possible to expose regular PostgreSQL tables as collections? Even read only.

[gortiz]

Hi @ArturFormella,

What you suggest is quite interesting and ToroDB Server 0.40 supports it partially. There is a command that allow to execute any readonly sql sentence. The result set returned by postgres is then translated to a MongoDB Cursor. There are a couple of issues with that approach:

• ToroDB Server 0.40 is an older version that uses the deprecated data storage algorithm. ToroDB 0.50 does not support that command yet (but we would like to add it on future releases, it shouldn't be difficult).
• You can only execute two operations with cursors in ToroDB: fetch and close. You cannot, for example, use it on an aggregation pipeline.

On the other hand, this is not exactly as "regular PostgreSQL tables as collections" because you have to query them using SQL queries. You cannot access non-torodb tables with the MongoDB syntax. We would like to do that, but it is quite difficult (specially if you want to build hierarchical json objects with several tables).

There is also a unorthodox and hackish way to do what you want. You can "hack" your tables to look like ToroDB tables and add to the ToroDB metatables, then ToroDB should be able to execute read only queries against them. If you don't want to modify your tables, it should work with views.

[ArturFormella]

Hello!
Thank you for your answer, @gortiz.

I was thinking of Torodb as gateway to Postgres database with all advantages of MongoDB query language.
Even because Mongo query language is more suitable for Javascript than SQL.

I will try a hackish way first :)

Is this document current?
https://github.com/torodb/torodb/wiki/How-ToroDB-stores-json-documents-internally

[gortiz]

Is this document current?
https://github.com/torodb/torodb/wiki/How-ToroDB-stores-json-documents-internally

Nope, it is not! We have to update it or remove it until we can do it because it can be confusing. The new storage engine used by ToroDB Server follows the ToroDB Stampede conventions (you can found them here).

Since some month ago we have been focused on ToroDB Stampede (the solution that replicates from a MongoDB replica set but does not offer a MongoDB interface to talk with the data) and ToroDB Server has fallen behind. Tomorrow or early next week we will have a more useful version of ToroDB Server on the devel branch. I recommend you to wait until then to start to start your hacking season.

[gortiz]

Hi @ArturFormella ,

I have just accept a PR on the devel branch that fixed a couple of bugs on ToroDB Server. Feel free to test it if you want. I am looking forward to hear from your experience.