Rocket.chat: Support for PostgreSQL as alternative to MongoDB

[engelgabriel]

Elephants don't fly, most of the time:
http://meteor-postgres.readthedocs.org
https://github.com/meteor/postgres-packages

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[dosire]

More context in https://about.gitlab.com/2015/08/22/gitlab-7-14-released/
To ship in 8.0 we need something to integrate around September 7 I think (@maxlazio knows more) so that will be really tight. Alternatively we can ship in 8.1. Please keep us posted.

[engelgabriel]

http://info.meteor.com/blog/an-early-look-at-sql-in-meteor

[engelgabriel]

Some approach to consider
http://www.postgresql.org/docs/9.4/static/datatype-json.html

[engelgabriel]

An early look at SQL in Meteor
https://forums.meteor.com/t/an-early-look-at-sql-in-meteor/8736

[engelgabriel]

More people wanting this to happen
https://github.com/RocketChat/Rocket.Chat/issues/639

[jclabaugh]

+1

Mongo can be a pain and can be very expensive to scale in the cloud. At least allowing to swap the data store to AWS DynamoDb (also noSQL as a scalable Db-as-a-service) would alleviate some of this for large deployments or in an enterprise setting. But PostgreSQL 9.3 would work too.

[engelgabriel]

We found a few temporary road blocks that the our team is having to deal with.

1. Meteor is yet to release the official support to SQL, so we are playing only with alpha implementations at this point. That means that some of the core packages, like the accounts, don't work 100% with SQL yet. We are patching what we can to make it work, and hopefully can contribute something back to the to meteor master branch. https://github.com/RocketChat/Rocket.Chat/compare/postgresql
2. The third party packages we use, also do not support SQL yet, for obvious reasons. We are creating an abstraction layer that overwrites some meteor APIs to make they work without having to fork them (at least most of them) using https://github.com/goodybag/mongo-sql
3. We are refactoring the all the source code to isolate all DB calls into a package. The idea is to turn it into a replaceable package with the DB of choice. This should be finished this week.
   https://github.com/RocketChat/Rocket.Chat/compare/models

Still early days to commit to a date.. but I'll update this issue more regularly on our progress.

[dosire]

@engelgabriel thanks for the update!

[liamdawson]

Any updates?

[sammcj]

Any update on this?

[engelgabriel]

We finished to isolate all DB calls into a package, and now it is helping with the multi tenant support. Once that's done, will jump back into this issue with MDG to get the work done.

[sammcj]

Hi @engelgabriel what is the ticket with the multi-tenant support? Is that #404?

[engelgabriel]

no @sammcj it is https://github.com/RocketChat/Rocket.Chat/issues/658

[sammcj]

Thanks @engelgabriel , great work :+1:

[artisin]

+1

[valessiobrito]

+1

[patrick-othmer]

+1

[engelgabriel]

We are starting right now:

https://github.com/RocketChat/meteor/tree/release-1.3-refactor-model-layer-v1

Stay tuned :wink:

[sammcj]

Fantastic!

All the best and thank you again.

On 4 Feb 2016, at 8:19 AM, Gabriel Engel [email protected] wrote:

We are starting right now: https://github.com/RocketChat/meteor

Stay tuned

—
Reply to this email directly or view it on GitHub.

[pavankumarkatakam]

Eagerly waiting for it ... :smiley:

[clorichel]

any chance to get a mongodb to postgresql update/migration/import-export guide when this task is done ?

or this idea is an impossible dream and we should wait for postgresql support before installing ?

[engelgabriel]

It is a possible dream.

[clorichel]

Great ! Would be glad to help on this if needed. Just ping me ;)

[jamieomatthews]

@engelgabriel is there any breakdown of work still needing to be done for this? For contributors to help out with?

[Sing-Li]

@jamieomatthews Thank you for the interest!

We are starting to work closely with MDG to 'unblock' this.

It involves two distinct parts:

• accounts subsystem
• everything else that is Rocket.Chat specific (already managed under a 'model' layer)

The part that MDG is assisting us with is the 'accounts' subsystem (the users NoSQL collection, and everything associated with login/auth). This is ongoing work and there will be a community that is built around it - and it will be extended to include SQL data sources other than Postgres, as well as other non-SQL data sources. More information to come (very soon) on this.

The second part, however, is quite well understood, and we are ready to immediately take on contributions. @engelgabriel and/or @rodrigok can provide more detailed information on how you can help.

Thanks again.

[engelgabriel]

Hi @jamieomatthews , we already isolated most DB calls into a model object, that we need split into Mongo/Postgres/ETC interfaces. We are also simplifying the schema to make it easier to use RDBMS.

One good starting point to help, is to find a good migration package for postgres that we could use?

[jamieomatthews]

@engelgabriel sure, I'd be happy to try to help.

As for migration package - do you mean a tool to migrate from mongo to postgres? Or a migration package in general for future postgres upgrades?

[urquilla]

+1

[engelgabriel]

I mean for future PostgreSQL schemas upgrades

[jamieomatthews]

How about https://github.com/db-migrate/node-db-migrate ?

[Sing-Li]

For the Meteor platform specific portion of this project (as described in my earlier comment), work will continue with MDG at this public repository:

Meteor's accounts repository

[Sing-Li]

@jamieomatthews fyi - Rocket.Chat's model layer code is currently here:

https://github.com/RocketChat/Rocket.Chat/tree/develop/packages/rocketchat-lib/server/models

[kramer65]

Just a question. How much money would need to be raised to get this going?

[engelgabriel]

@kramer65 This has been big investment for us. We have already spent over 15k USD on development, and probably need another 15k USD to finish.

[mccambridge]

Just for clarification (I'm a little confused): 1. How far along is the R.C side of this issue, and 2. How far along is Meteor towards implementing Postgres/RDBMS? Thanks!

[Sing-Li]

I can provide some info on item 2.

Meteor will start to support Postgresql (and RDBMS in general) with the apollo project. The team is in place. Find out about the project here:

https://www.youtube.com/watch?v=fLjtFHgSrSI

And here:

https://github.com/apollostack/apollo

You can track the progress of apollo in the Meteor forums (https://forums.meteor.com/), and through release announcements.

[sammcj]

Has any progress been made on this?
It's looking more and more like Rocket Chat is going to be stuck with Mongo...

[engelgabriel]

@sammcj we have limited resources, so when we've learned about Meteor's project called Apollo, we decided to wait for it to mature so we don't have to re-invent the wheel.

[subesokun]

Seems there has been some progress recently! The Meteor integration for Apollo has been released now in version v0.1.1. Not sure if that's already mature enough to start playing around with it and support for Blaze seems to be not ready yet.

Here's a small work-in-progress migration guide I've found while looking around apollo.md. Basically you'd need to replace every Collection call against a corresponding GraphQL query to make it database agnostic. Sounds like a major rework :sweat_smile: ... but GraphQL is the way to go so I'm looking forward to the day when Rocket.Chat gets shipped with Apollo :)

http://dev.apollodata.com/core/meteor.html
https://github.com/apollostack/blaze-apollo

[teon]

We use mongodb in our infrastructure only for Rocket.Chat purpose. Of course we deployed mongo in a cluster and from what I can tell you after 1 year of using:

• Mongo does heavy i/o on our servers
• Mongo does havy network traffic for replication

Also - we had 2 mongo huge crashes in that year.

From the other hand we have at least 20 heavy projects on Postgres on our infrastrucutre (also clustered) and non of the above problems.

I would highly recommend to support Postgres!

[sammcj]

Mongo is not a production ready database, please consider moving to PostgreSQL so that others who value data consistency may use the project.

[urquilla]

Same for me, almost anything else besides Mongo would make adoption much easier here, PostgreSQL would be a huge win.

[davistan]

Hi, just a quick suggestion, parse-server is currently implementing postgres as an adapter to mongo - have a look at their code and maybe it may help.

Not sure whether to bring this up to meteor/postgres-packages as the repo looks quiet.

[telmich]

Hello,

is there any status update on supporting postgresql?

Mongodb seems to begin to crash more regularly with more data and as described before, it is not really a production ready DB.

I think you all are aware of it that postgresql can also be used as a NoSQL database with Json and JSONB fields - so in theory there should not be much work involved in supporting postgres right away.

[gbartolini]

Another one suggesting to support PostgreSQL, the most advanced and reliable open source database.
I also have a question: do you think this can be ready soon? We are about to try some communication solutions and at the moment Mattermost is in a better position - as it supports PostgreSQL. Thanks.

[Skryabind]

We are choosing a company messenger now. Rocket.Chat is more preferable, but we don't want to use MongoDB. So please tell when do you plan to add PostgresSQL support?

[Doctor-Who]

Same here, I gonna kick Office 365 for internal communication and use RocketChat because it's the best features of what we could expected.
But security fail and unresolvable crash of MongoDB just make me choose Mattermost in the future :(
I hope this project will be achieved and allow me to reconsiderate my choice.

[telmich]

I think what would be helpful for all users, if the developers of Rocketchat could comment on a possible timeline for this feature.

[lkraider]

MeteorPostgres seems dead, anyone knows the state/roadmap of ApolloStack (https://github.com/apollostack) and if Rocket.Chat will migrate there?

For reference, the ParsePlatform guys were able to develop a database adapter interface that talks to Mongo and PostgreSQL, perhaps Rocket.Chat could build on top of the same abstraction:
https://github.com/drew-gross/parse-server/blob/master/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

[engelgabriel]

Yes, we have plans to migrate/add the ApolloStack into Rocket.Chat, so we'd moving form DDP to GraphQL in the future. The current main roadblock is still the Account package. We have done considerable amount of work to port it to PostgreSQL, but it is incomplete. We went a step back, and decided to finish writing all our end-to-end testing before we can safely continue this path, as the chances for breaking stuff is high.

[yogzblr]

Any further news on this? Can we expect this to make it to some release in the near future?

[Sing-Li]

Yes. The GraphQL / ApolloStack work has started. No exact dates or release to give at this point, but it is concurrently ongoing with main development. We will post update as soon as we reach some tangible milestones.

[telmich]

That's great to hear @Sing-Li ! We are experiencing weekly crashes of rocketchat with mongodb currently, so postgres would be much, much appreciated

[thiesschneider]

yay!

[telmich]

Btw, most (if not all) of our rocketchat crashes are caused by mongodb problems

[jfaraklit]

+1, Postgres is the way to go I believe. We are on AWS and mongo has been a pain lately.

[mnlbox]

+1

[telmich]

Fyi - we migrated from rocket.chat to mattermost, because of this issue and the inability of nodejs to properly handle IPv6.

[thiesschneider]

Hey there... Any news on this?

[telmich]

Fyi @thiesschneider We migrated to mattermost, because of mongodb and ipv6 issues in the nodejs stack of rocketchat.

[thiesschneider]

Fyi @telmich: This is the rocketchat repo and I am interested in the status of the postgres integration...

[kohenkatz]

@thiesschneider This comment by @Sing-Li looks like it's still the most recent status. Follow development along that line in the graphql branch, which does seem to be actively worked on, albeit slowly.

[TwizzyDizzy]

As discussed: please add a label "feature-needs-review".

/cc @SeanPackham

Cheers
Thomas

[Atticus-]

Looks like that graphql branch was merged in late January (https://github.com/RocketChat/Rocket.Chat/pull/8158).

According to the PR, they've even got accounts on GraphQL using accounts-js, but it looks like the resolvers still use MongoDB? Does this put us any closer to moving off MongoDB entirely? It'd be nice to get an update on this issue since that PR was merged.

[TwizzyDizzy]

@rocket-cat label add "Feature: Request"

[marceloschmidt]

We're keeping this issue open as a discussion but it's not in our plans to add this in 2018.

[seand88]

+1 for postgres

[Valinwolf]

I was about to install RocketChat to replace Discord... then I realized it used MongoDB... then I saw this... oh well... off to looking for another possible alternative.

[ice1e0]

@marceloschmidt This ticket is still in the roadmap under In progress. The roadmap should be updated when this is not in plan.

Personally, I think that an alternative database would be a great advantage for the product. I don't mind what database system. I think many people refuse to use Rocket.Chat just because the database is MongoDB which has a bad reputation when it comes to scalability which is required in big communities or enterprises.

See also: Which companies have moved away from MongoDB and why? What did they move to?

[sandys]

is rocket.chat now using graphql everywhere ?

Because there are nodejs projects which layer a graphql server on top of postgres (https://www.graphile.org/postgraphile/) ... so rocket-chat devs dont have to fight postgresql.

They can simply use the graphql goodness and the end user can choose to run graphql or mongodb.

[piotrkochan]

@sandys there is one graphql-related PR https://github.com/RocketChat/Rocket.Chat/pull/11860

I assume there is more functionality which is not covered in Rocket's GraphQL

[scher200]

(The nextcloud integration)[https://rocket.chat/2018/09/17/RC-Nextcloud-partnership-post/] look very nice. With this awesome job done the two could use the same database: Postgress :+1:
If the Postgraphile can handel the graphql, and @engelgabriel told us there is a start. Please tell us what is left?

[sandys]

hi guys,
not sure if you have looked at this, but it is becoming increasingly imperative to build an alternative to mongodb.

https://www.processmechanics.com/2018/10/18/the-server-side-public-license-is-flawed/

this is based on a recent news around mongodb license change - https://www.mongodb.com/press/mongodb-issues-new-server-side-public-license-for-mongodb-community-server

“Service Source Code” means the Corresponding Source for the Program or the modified version, and the Corresponding Source for all programs that you use to make the Program or modified version available as a service, including, without limitation, management software, user interfaces, application program interfaces, automation software, monitoring software, backup software, storage software and hosting software, all such that a user could run an instance of the service using the Service Source Code you make available. (emphasis added)

This amazingly broad. For example, this would include deployment scripts and software (e.g., Ansible, Salt, and my scripts) - but I don't own the copyrights to that software, and I cannot release it "under the terms of [the SSPL]."

from a legal perspective, we are reading this to mean that RocketChat Cloud as well as Server could be in potential conflict with the broader mongodb license.

We would love to get clarity from the core team if there is a goal to move away from Mongodb to Postgresql. Given where Postgresql has reached with its last PG11 release, and the Mongodb license issues.. we strongly believe that this is something that could soon turn into a blocker.

[opairdrop]

I see no need to re-analyze the idiocy of the mentality presented by Mongo in the above links. The delusion on Mongo's part to think that they should simultaneously exist as a required component in an open source framework and be owed money as if they were proprietary software speaks for itself.

The only thing worth restating here is that a significant barrier to Rocket.Chat adoption is the cost of hosting Mongo. By design Mongo is more expensive to host (for little to zero added benefit), and with the above licensing games they are declaring their intent to play, the cost to host Mongo will be even more expensive, assuming people won't be able to get it from third party providers.

I appreciate that a large change in a mature software such as Rocket.Chat is difficult, but at some point you guys are going to have to wrangle with the fact that Mongo will be a constant weight around this project's neck, hindering its adoption.

[seand88]

Mongo was hot.
Now its not.
Let's regress.
And run it on postgres.

Seriously though this is a problem made by the decision to use the meteor framework.
RocketChat would have to rewrite the entire software stack to start using this on postgres

[opairdrop]

I agree that meteor has the same decision, regarding which Rocket.Chat is just a prominent example. But this isn't something that can be punted to someone else in terms of responsibility.

If Mongo persists with the above licensing change then every framework that relies on them will suffer for it.

[sandys]

im actually hopeful about this because of a comment made in the rocketchat forums.
https://forums.rocket.chat/t/legal-implications-of-mongodb-licensing-change/2332/4?u=sandys

Support for postgresql however is quite orthogonal to this license change - more of a technical topic - and is likely to arrive with our maturing support for GraphQL / Apollo.

if the rocketchat team can be convinced that Postgres support is not an orthogonal thing and solves multiple problems in one shot... then they would pay more attention.

[piotrkochan]

@mathiasblc this is not a bug

[ChrissW-R1]

@mathiasblc this is not a bug

You're right. It is not a bug. But e.g. in our environment this tool is the only one which uses it's own database. So we couldn't migrated it in our backup routines or load balancers. So for installations with more than 600 users it makes sense to integrate it in the existing infrastructure.

[menturion]

+1 for Postgres.

[mnlbox]

Guys you can use nice realtime graphql on postgres with https://hasura.io and zero setup with just 15 mb docker image. It's an awesome tools and have a good mix with react.
Why you still use Meteor and Mongo?
It's 2019.

[hypery2k]

any progress on this?

[ghost]

distros dropping Mongo from their repos like hot potatoes...

https://wiki.archlinux.org/index.php/MongoDB

MongoDB has been removed from the official repositories due to its re-licensing issues

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8-beta/html/8.0_beta_release_notes/new-features#web_servers_databases_dynamic_languages_2

Note that the NoSQL MongoDB database server is not included in RHEL 8.0 Beta because it uses the Server Side Public License (SSPL).

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=916107

MongoDB should not be part of (at least) Buster for the following
reasons:

• MongoDB 3.6 and 4.0 will be supported longer, but upstream's switch
  to SSPLv1 complicates matters. As discussed in #915537, we will not
  be distributing any SSPL-licensed software

---

It would seem prudent that alternatives are provided or else Rocket.Chat may end up in a ditch together with MongoDB

[Rauserch]

any Update yet?

[rodrigok]

I'm closing this since it's not in our roadmap.

Support for multiple databases is not feasible right now, due to performance issues and different features each database has. Migrate existant clients to a new database would be problematic.

This item may rise again in the future, but right now it's not possible.

[pierreozoux]

Thanks for closing this. Even though we are not all necessarily happy, we have to understand that you have to run a product, and this means making choices.

As RocketChat is based on meteor, people don't get the complexity to add this support now.

I think adding mongo support would be pretty equivalent to refactoring everything.

Thanks again and keep up the good work 👍

[apjapj]

Any news on PostgreSQL integration? The ticket was closed almost a year earlier and its very hard to justify using MongoDB with an opensource project nowadays. Thanks in advance

[piotrkochan]

@apjapj I don't believe that rocket chat will support postgresql ever

[seand88]

postgres has had a json/jsonb datatype for a while. it should be fairly easy to just change a document store to a postgres table jsonb store. each table would be a "collection" in mongo with one jsonb field per table.
If there is a decent repository layer this shouldnt be too much work.

Mongo has way less hosting options and is much more expensive to host.
Going this route you could virtually use any database like mysql etc.. as they have json fields as well.

[kohenkatz]

@apjapj @seand88 If you want a similar platform that supports PostgreSQL, look at Mattermost. As noted in https://github.com/RocketChat/Rocket.Chat/issues/533#issuecomment-549556265, there's no way this is going to happen.

[pierreozoux]

it should be fairly easy to just change a document store to a postgres table jsonb store. each table would be a "collection" in mongo with one jsonb field per table.
If there is a decent repository layer this shouldnt be too much work.

Feel free to try, I'd love to see that you are right :) I think it is not that trivial :)