Back at the end of 2015 there was a burst of work being done by various parties on adding live SQL databases to Meteor. MDG announced they were working on an official package, which seems to have stopped everyone else from tackling the project. Searching here and on the broader web turns up a bunch of attempts, but none seem to be production ready and haven’t been touched for 2 or 3 years.
I’m trying to use Meteor to integrate with my client’s existing postgres DB. I’m considering brute forcing the solution by polling postgres for changes and reflecting changes from the server to the client (I was going to reflect the changes into a Mongo DB, but the client’s IT department refuses to install Mongo). Is there any hope of a Meteor native SQL solution that is production ready or do I need to move to a non-Meteor solution?
You can look at redis-oplog, the db driver can be abstracted to any db basically. In theory it’s possible, however we don’t have it in our roadmap currently because we don’t need it.
That’s a great solution for MySQL. However, @rcurrier is asking for Postgres. I know @numtel followed up the original MySQL live implementation with one for Postgres, which required triggers setting up to achieve the same results. I would imagine that will still work, even though it’s more of a hassle to set up than MySQL.
Thanks, @vlasky and @robfallows, those are exactly the answers I was hoping for. Glad to see there is a MySQL integration package being used in production. I’m going to give @numtel’s PG package a try.
Postgres supports something called logical decoding that allows for decoding the write-ahead log. There is already a couple of PG plugins (wal2json, decoding_json) that outputs json data for DB changes. We already have redis-oplog that knows how send data into Meteor to provide reactivity. I looks like it should be possible to combine the two into something like redis-pg. Anyone have any thoughts on this approach or why it won’t work? I’m thinking about tackling it myself unless someone else is looking for a project.
There is also Debezium, which uses Kafka rather than redis. The upside is there are DB plugins for both PG and MySQL. The downside is that it introduces another app into the stack (Kafka) and requires Docker and is written in Java. It also appears that it won’t run on Windows for those of us developing on that particular platform.
Going beyond just SQL there are also Kafka and Confluent connectors for dozens of different database sources including AWS Lambda, DynamoDB, rethinkDB, Splunk, Twitter, and even FTP. These have the same downsides as Debezium.
People still cite SQL support, or the lack of well-integrated SQL support, as a prime reason not to use Meteor. I think Meteor would be well served by a section in the Guide that documents best practices for using Meteor with Postgres, with MySQL, with Redis, with Apollo and other popular databases that already work well with Meteor. I’ve never used Meteor with anything other than Mongo because I’m afraid to, because it feels like the support is skin deep and that I might end up needing to support large parts of the database integration myself. If that’s not the case, I’d love to read as much.
I think you need to be wary of terms which infer that MDG should in some way “support” any of the millions of npm packages which you may decide to use in a Meteor application.
The fact is that Meteor lets you use any npm package, which includes Postgres, MySQL, Redis, Apollo, etc. There’s no reason to expect that you should get anything other than the documented behaviour for that package - Meteor is just a NodeJS application, after all. In your own words, they “already work well with Meteor” and there’s no reason to suppose that you’d need to support large parts of the database integration yourself.
The OP here was specifically asking about live SQL integration, which goes above and beyond “documented behaviour”. In my opinion, it’s unlikely that MDG will add any livequery functionality to other databases. Experiments were done some years ago (with Redis), but where would it end? There’s always another database and MDG’s resources are limited.
However, if you really need livequery, there are options. If you focus on in-band queries, then you could, as @diaconutheodor has suggested, leverage redis-oplog to provide livequery functionality to any database - and there may, in the future, be some level of integration for Apollo.
Having said all that, I think your proposal of a “databases” section in the Guide is a great idea - perhaps you’d consider a PR for that?
I think your proposal of a “databases” section in the Guide is a great idea - perhaps you’d consider a PR for that?
Like I wrote, I’ve only ever used Mongo, so I’m not the one to write that section
I think most people who’ve done any development in Meteor realize that you can import an NPM module, and therefore one could import a Postgres or Redis or other database NPM module and use it like any other freestanding import; but that doesn’t provide any integration with Meteor. One of the principles of Meteor is reactivity, and using a database that doesn’t provide reactivity isn’t really using Meteor as it’s meant to be used. But so many of these other databases do work reactively! Redis and MySQL, to name two, and if it can be done in MySQL then it can probably be done in most sophisticated relational databases, certainly Postgres. I don’t see why MDG shouldn’t “support” these at least as much as it supports UI layers like Angular and Vue, that is, by documenting in the Guide how to use third-party packages to enable full integration with Meteor.
As the OP of this thread, getting reactivity into Meteor requires the DB to provide some means of knowing (other than polling) when data has changed. Mongo provides the OpLog, Cassandra provides a data-tap, MySQL provides something similar to OpLog, Oracle provides Golden Gate (at a substantial add-on price), I have no idea if SQLite provides change stream, and Postgres didn’t provide anything up until recently (but triggers could be used). I know nothing about any of the myriad of the other SQL and NoSQL DBs floating around. Those with data change streams should be able to use a modified version of the Redis-OpLog module.
But getting reactivity is only a piece of the problem. Mongo is buried deep into Meteor's structure: its use of collections (that don't map well onto a SQL DB) plus its use of miniMongo on the client to speed up DB access. Using a NPM to do this is apparently possible since the MySQL package seems to allow collections to work, but Meteor isn't set up to allow the DB to be replaced. There was talk at one point about abstracting out the DB interfaces, but I don't believe any work was done and I don't know if MDG would even accept a change that large if it could even be done. I'll let Rob or one of the other devs respond to that if they want to. But I don't expect Meteor will ever work as well with other DBs as it works with Mongo.
I got lucky and someone much higher up the food chain than me managed to get IT to allow us to use Mongo (thus proving that upper management is, at least on occasion, good for something), so my search is at an end. If I get some spare time in the near future, I'll try to edit my notes and posts from this thread into something coherent and upload to github or some wiki (but not the meteor github wiki) so others can update it as time goes on.
Apollo brings great reactivity into Meteor using MySQL. It uses pubsub. When you run a mutation on your database, the mutation function on the server issues a call to pubsub. Pubsub pushes a notification to all clients who are subscribed to those changes, based on their subscription variables – i.e. a client viewing a blog post only sees notifications for mutations affecting that particular post, not for all blog posts on the site. It works very well.
.