Meteor support for PostgreSQL using OxideDB / FerretDB

[wreiske]

My employer is doing some work with Percona. During one of our calls, after mentioning that we use MongoDB heavily for our meteor projects, the Percona SME mentioned a cool project called FerretDB. FerretDB is "a stateless proxy, which converts MongoDB protocol queries to SQL, and uses PostgreSQL as a database engine. This will be compatible with MongoDB drivers, and should work as a drop-in replacement to MongoDB in many cases."

https://www.ferretdb.io/

I sent this project to the dev team over at Rocket.Chat, a major player in the Meteor / MongoDB world, and @geekgonecrazy sent me this: https://github.com/fcoury/oxide

Oxide is much like FerretDB, indeed, "the project was heavily inspired by FerretDB and is on its early days. The main difference is that there is no intention to support any database other than PostgreSQL (FerretDB is also supporting Tigris) and it's written in Rust, as opposed to Go."

I'd like to start a discussion about these different tools and potentially others like them. Would it make sense for Meteor to support these types of tools as well, or at least mention them in documentation? meteor create --blaze --db-engine=ferretdb or something similar could be created to start up a PostgreSQL with FerretDB or OxideDB.

Change streams and OPLOG might be interesting.

I'll report back here if the TODO list example app works out of the box or if there are any weird idiosyncrasies.

[wreiske]

OK - So I've messed with this a little bit. I created a repo to play around with it using docker-compose. https://github.com/wreiske/meteor-ferretdb

So far it works up until step07 of the blaze tutorial. As soon as the login code is added, it fails because FerretDB does not yet support the $addToSet modifier. FerretDB/FerretDB#825

Meteor-FerretDB.mp4

I'll do an oxide test when I get a free moment.

[StorytellerCZ]

Amazing! Having something like that would be amazing as you wouldn't have to worry about MongoDB calls in packages or do a major changes to Meteor!

[wreiske]

Found some time to work on an oxide test. No luck. The meteor user accounts package adds an index to emails.address and it looks like oxide isn't creating the index correctly in postgresql. A more simple example may work, but it looks like FerretDB may be more promising at this time.

postgres    | 2022-08-24 20:42:36.260 UTC [1] LOG:  database system is ready to accept connections
setup       | CREATE SCHEMA
setup exited with code 0
oxide       | [2022-08-24T20:42:45Z INFO  oxide::server] OxideDB listening on 0.0.0.0:27017...
meteor_app  | Successfully connected to MongoDB
meteor_app  | Starting app...
postgres    | 2022-08-24 20:42:46.281 UTC [80] ERROR:  syntax error at or near "." at character 41
postgres    | 2022-08-24 20:42:46.281 UTC [80] STATEMENT:  CREATE UNIQUE INDEX IF NOT EXISTS emails.address_1 ON "meteor"."users" ((_jsonb->'emails.address'))
oxide       | thread '<unnamed>' panicked at 'called `Result::unwrap()` on an `Err` value: db error: ERROR: syntax error at or near "."
oxide       | 
oxide       | Caused by:
oxide       |     ERROR: syntax error at or near "."
oxide       | 
oxide       | Location:
oxide       |     /rustc/e092d0b6b43f2de967af0887873151bb1c0b18d3/library/core/src/convert/mod.rs:550:9', src/commands/create_indexes.rs:33:18
oxide       | note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
meteor_app  | 
meteor_app  | /opt/bundle/bundle/programs/server/node_modules/fibers/future.js:313
meteor_app  |                                           throw(ex);
meteor_app  |                                           ^
meteor_app  | errorClass [Error]: [An error occurred when creating an index for collection "users: connection 1 to 172.20.0.6:27017 closed]
meteor_app  |     at Collection.createIndex (packages/mongo/collection.js:770:15)
meteor_app  |     at setupUsersCollection (packages/accounts-base/accounts_server.js:1813:9)
meteor_app  |     at new AccountsServer (packages/accounts-base/accounts_server.js:74:5)
meteor_app  |     at packages/accounts-base/server_main.js:7:12
meteor_app  |     at module (packages/accounts-base/server_main.js:19:1)
meteor_app  |     at fileEvaluate (packages/modules-runtime.js:336:7)
meteor_app  |     at Module.require (packages/modules-runtime.js:238:14)
meteor_app  |     at require (packages/modules-runtime.js:258:21)
meteor_app  |     at /opt/bundle/bundle/programs/server/packages/accounts-base.js:2395:15
meteor_app  |     at /opt/bundle/bundle/programs/server/packages/accounts-base.js:2402:3
meteor_app  |     at /opt/bundle/bundle/programs/server/boot.js:401:38
meteor_app  |     at Array.forEach (<anonymous>)
meteor_app  |     at /opt/bundle/bundle/programs/server/boot.js:226:21
meteor_app  |     at /opt/bundle/bundle/programs/server/boot.js:464:7
meteor_app  |     at Function.run (/opt/bundle/bundle/programs/server/profile.js:280:14)
meteor_app  |     at /opt/bundle/bundle/programs/server/boot.js:463:13 {
meteor_app  |   isClientSafe: true,
meteor_app  |   error: 'An error occurred when creating an index for collection "users: connection 1 to 172.20.0.6:27017 closed',
meteor_app  |   reason: undefined,
meteor_app  |   details: undefined,
meteor_app  |   errorType: 'Meteor.Error'
meteor_app  | }
meteor_app exited with code 1

[fcoury]

I've got another one :) fcoury/oxide#62

Another one bites the dust: fcoury/oxide#63

Gonna try to run your repo now and let you know. Thanks again!

[fcoury]

OK I think we are now at the same place as FerretDB:

meteor_app  | Exception while invoking method 'login' MongoServerError: Err(InvalidUpdateError { reason: "Unknown modifier: $addToSet" })
meteor_app  |     at MessageStream.messageHandler (/opt/bundle/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/connection.js:462:30)
meteor_app  |     at MessageStream.emit (events.js:400:28)
meteor_app  |     at MessageStream.emit (domain.js:475:12)
meteor_app  |     at processIncomingData (/opt/bundle/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/message_stream.js:108:16)
meteor_app  |     at MessageStream._write (/opt/bundle/bundle/programs/server/npm/node_modules/meteor/npm-mongo/node_modules/mongodb/lib/cmap/message_stream.js:28:9)
meteor_app  |     at writeOrBuffer (internal/streams/writable.js:358:12)
meteor_app  |     at MessageStream.Writable.write (internal/streams/writable.js:303:10)
meteor_app  |     at Socket.ondata (internal/streams/readable.js:731:22)
meteor_app  |     at Socket.emit (events.js:400:28)
meteor_app  |     at Socket.emit (domain.js:475:12)
meteor_app  |     at addChunk (internal/streams/readable.js:293:12)
meteor_app  |     at readableAddChunk (internal/streams/readable.js:267:9)
meteor_app  |     at Socket.Readable.push (internal/streams/readable.js:206:10)
meteor_app  |     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

I'll start working on adding support for $addToSet.

[fcoury]

fcoury/oxide#64

[fcoury]

... a few PRs and a lot of fixes later, it works now with step 12(?) of the tutorial:

Meteor-Oxide.mp4

I will probably push a release out between today and tomorrow with all the fixes that made this now work.

Thanks again!

[StorytellerCZ]

Amazing work @fcoury ! I think we will soon be adding this to the documentation/guide.

[radekmie]

I like such initiatives, but by looking at the current state of both, it's far from being production ready. At the same time, there's nothing that Meteor actually has to do here - once it becomes a truly drop-in replacement for MongoDB, all you'll need to do will be to provide a MONGO_URL.

However, I admire the reactiveness of @fcoury - keep this up! As probably no one will share their codebase to debug any of these, you could run something like Rocket Chat to find even more bugs.

(And a sidenote for @fcoury - I read a few PRs in Oxide's repo, and it'd be great to build some more extensive test suite. Maybe you could even adopt the ones that MongoDB uses internally and test it in a more black-box way?)

[fcoury]

Thanks so much for your comments. Not on my computer right now but if you check the integration tests under the tests folder, they all use the native MongoDB crate, so you can consider all of those black box tests 😊

Regarding reusing an existing suite of tests, that is a great idea and for sure something I want to look into.

I am also trying to get to a place where it suits a subset that is "good enough" for some use cases. MongoDB has grown a lot over the years and you find all kinds of weird corner cases, one example that comes to mind is that the findAndModify method also allows for deletion, something I personally didn't expect. This is just the tip of the iceberg and something that came to mind as I was writing. But that's where I think having a complete suite can help. At least you can account for the whole and get a sense of effort and also make deliberate decisions on what to support.

Things I want to support initially include my own products but I would love to also include something from a community and/or product such as Meteor.

Thanks again for the kind words and I will have another pass at your comments when I am back at the computer.

[radekmie]

I would call findAndModify complex, but not hard. I think cases like ordering ({a:1,b:2} goes before or after {a:1,b:[]}, etc.) or distinct are similar, i.e., manageable with a finite amount of conditions. But then you enter the sea of coalescing aggregations and that's where the fun begins 😛

In any case, good luck!

[fcoury]

Yeah I meant it's complex as like, on the surface it seems straightforward until you start reading all the different behavior depending on each of the options.

And yes, the name of the game here is labor intensity and not so much hard problems. Some of them are definitely hard to implement, but most are just a lot of code writing and experimentarion with PostgreSQL json logic.

[Grubba27]

I've come past this discussion, and I've found it to be looking really good! I admire your efforts a lot @fcoury! We will love to have something like this working out of the box with Meteor.
Is there anything that we can do to help make this happen? There are a lot of example projects here that can be used and also we've been using fredmaiaarantes/simpletasks a lot for testing.
Also big thanks to @radekmie for providing questions,support and comments on this matter

[ptrfarkas]

Hi all, thank you so much for your interest in FerretDB, I am one of the maintainers of FerretDB.

One question to @Grubba27 and possibly others: how important is it for FerretDB to support OpLog tailing in order to be useful for Meteor-based apps? Is it possible to fall back to polling in case this feature is not supported? This is something we are wondering about, as we attempted to support BigBlueButton in the past.

We will be looking at the rest of the issues raised here, as we are approaching a level of compatibility which enables us to concentrate on specific features.

Also thank you for your work on checking compatibility @wreiske. Please do let me know if we can help with your specific use case when it comes to replacing MongoDB.

[radekmie]

how important is it for FerretDB to support OpLog tailing in order to be useful for Meteor-based apps?

It powers the entire "real-time-ness" of Meteor, so I'd say it's important for most of the users.

Is it possible to fall back to polling in case this feature is not supported?

Yes, it is, and some people would be perfectly fine with it. We're also looking into using Change Streams API to replace it, but that won't happen soon.

[Grubba27]

a friendly ping to @radekmie to further explain his thoughts on Change Streams and Oplog

[radekmie]

Right. I heard from @Grubba27, that you're now planning how to proceed with more full-blown Meteor integration. In my opinion, oplog will be the default for Meteor for at least a year more. I hope to get some, at least experimental, version of Change Streams replacement within the next half a year, but we'll see about that.

But there's a third option. Quite a few projects (including a few I worked or work on) use the cultofcoders:redis-oplog, which replaces the "realtime-ness" with Redis. On top of that, there's a oplogtoredis package, that makes it even better, as it's a separate service, "translating" oplog events into redis-oplog-compatible Redis operations.

If you could replicate something like oplogtoredis is doing with FerretDB, i.e., have some way of notyfing a Redis instance about the changes in a given format, then it'd be enough to have a fully realtime app.

---

Separately, I'd think about efforts here. I'm not that deep in MongoDB internals, but I know quite a bit. And based on my knowledge, it'd be hard to support Change Streams without supporting something "oplog-like". Sure, Change Streams are somehow documented (oplog is not), but there's no real technical specification - that may be a challenge.

That's why I think that looking into oplogtoredis may be interesting. Of course, that'd be basically Meteor-specific functionality, but hey, you asked about that here.

[radekmie]

@ptrfarkas Update from my end: as soon as FerretDB supports Change Streams, Meteor can stay fully reactive with cultofcoders:redis-oplog and my new tool changestream-to-redis.

[TechplexEngineer]

It looks like the Ferretdb project was recently featured on Hacker News. https://news.ycombinator.com/item?id=36590834
Has anyone tested Meteor with the latest releases?

[AlekSi]

Hi, FerretDB co-founder and CTO there. We tested FerretDB with many Meteor apps. In general, they work – except for OpLog, and that's a big exception for many production scenarios. We are going to implement OpLog in a few months. But if you can – please try your Meteor apps with FerretDB and create or vote for existing issues if something does not work.

[xet7]

Hi,
I just got news, that FerretDB 1.18 now has support of OpLog. So I will be trying replace MongoDB 6.x with FerretDB/PostgreSQL or FerretDB/SQLite.

https://github.com/FerretDB/FerretDB/releases/tag/v1.18.0

FerretDB/FerretDB#1752 (comment)

https://forums.meteor.com/t/ferretdb-1-18-now-has-oplog-support-trying-replace-mongodb-6-x-with-ferretdb-postgresql-or-ferretdb-sqlite/61092