Can Meteor and MySQL play nicely together?

One of our longstanding goals as a company has been to improve the way our team works together. Past initiatives have always involved increasing transparency, communication, and something we call the “maximization of mental space” (probably the most valuable resource any of us has, and the one thing that will get chipped away at relentlessly if you don’t remain vigilant).

And so...as usually happens when we have goals to achieve, we dreamed up an app to help make them happen. The app would be called “Teams”, and because it should of course be real-time, we determined that app should naturally be developed in Meteor.

We didn’t want to build yet "another chat app" since we have tons of choices to choose from there already. Rather, we wanted an app that showed each team member what everyone is working on *right now* as well as who else is working on the same thing, budget burn, sprint information, and invoice information -- all at a glance.

---

Project Ricochet is a full-service digital agency specializing in Meteor, Apollo, and GraphQL.

Is there something we can help you or your team out with?

---

All that sounds good in theory, but how do we bundle the various services we use together into one app? Simply having an API and deciding to use Meteor isn’t enough. We have tons of numbers to crunch from these various systems: Jira, Toggl Time Tracker, Pushpin Planner, our estimation tool, our company knowledge base, and even a MySQL database that we use for our workflow and invoice automation — which itself is already ingesting Toggl timer data. No small feat, for sure.

Meteor and MySQL? Seriously?

Before you say that using Meteor with MySQL feels dirty, I’d like to point out in fact that MySQL integration is on the Meteor Roadmap and was a strong contender as the default database when the Meteor team first devised their diabolical plans. Mongo was largely chosen for quicker adoption by the Javascript community. Meteor and MySQL can actually play nicely together — and even save time too, if implemented correctly in the right scenario.

In my opinion, there are two main reasons for using MySQL in a Meteor project:

• First, if you need to use or display data from a MySQL database that is already part of another system you're tied to — and this simply must be the case for budgetary or business reasons.
• Second, if you have a lot of data that you want to aggregate and run computations on. I’ve consistently found that when I have a big set of data to bend, twist, group, aggregate, sum, count, multiply (you get the idea), MySQL is really quite wonderful.
  
  In similar situations, I’ve found that doing the same set of operations in Mongo would be more difficult and require calculations to be done in loops via js code. It’s also hard to follow, usually results in more than a few annoying bugs, and most certainly doesn’t offer the flexibility and power of a simple MySQL command. If this is something we’re behind the curve on, I’d love to hear from you.
  
  In the case of our Teams app, both scenarios actually applied &m-dash so MySQL was a no brainer. The use of MySQL to drive Meteor collections really highlights how collections and subscriptions are largely used in Meteor. We often have large datasets on the server side, but the subscriptions often function as a way to drive the UI and what the user should see at that moment. From that perspective, much of the Mongo collections in our Meteor app only contain the completed and aggregated data that we want to see on the front-end.

---

Can Meteor scale?

Learn more about Meteor performance with our white paper on the "Top 10 Meteor Performance Problems" and how to address them.

Download White Paper

---

Caveat Emptor: what about my reactivity?

Of course, the downside to all this is that Meteor doesn’t yet monitor the MySQL data with any sort of reactivity — although this *is* in the roadmap.

And in fact, in our particular use case, having the myriad data changing in real time might actually be overwhelming to the user (since there is so much and it changes so quickly). Furthermore, our MySQL data is largely a secondary repository of data for our app, as we have to poll both Jira and Toggl for data directly via their APIs, thus making MySQL reactivity even less important.

If you need true real-time data from MySQL with reactivity, you might want to think twice about using it for now until this is added in by the Meteor team.

You do have options though. To account for the lack of reactivity, you could do two things:

• You could poll the mysql data as needed using the cron-meteor package and update a Mongo collection with the relevant data as needed — which in turn drives the UI. This isn’t true “real-time”, but in very few cases is true real-time actually needed.
• While polling, in the case that the data either hasn’t yet been ingested by MySQL from the remote service *or* the polling hasn’t yet pulled it into the app, you could provide a button on the UI to allow the user to poll the service directly via an async call.
  
  This is actually only relevant in one particular scenario in our app, which illustrates that true “real-time” data doesn’t usually have to be all that real-time. A few seconds or minutes delay is oftentimes just fine and Meteor’s reactivity / real-time quality is oftentimes more of a convenience to the developer than anything else. In other words, people use Meteor with all its reactive glory not because to-the-second data is actually all that important, but rather because they like that they don’t need much code to update things as the underlying information changes.

Running MySQL in your app

Running MySQL in your app is actually quite easy. We used the the meteor-mysql package (https://github.com/patrickocoffeyo/meteor-mysql), which can be installed in your Meteor app by running:

$ meteor add patrickocoffeyo:mysql

Once you have the module installed, you can simply start using MySQL as you might in any other app:

    {
        var future_mysql = new Future();
        
        mysql.query(
            'SELECT ' + key + ' FROM entry WHERE entry_jira_issue_key LIKE ? AND ' + key + ' IS NOT NULL LIMIT 1',
            [
                jira_issue_key
            ],
            function(err, rows)
            {
                if(err)
                {
                    console.log('MySQL Error:', err);
                    future_mysql.return(false);
                }
                else
                {
                    future_mysql.return(rows);
                }
            }
        );
        
        var result = future_mysql.wait();
        
        if(result && result.length)
        {
            return result[0][key];
        }
    }

Summing things up:

In conclusion, MySQL integration with Meteor apps can be appropriate and no, it’s not a dirty shameful thing you must do in the shadows of the interwebs. We pride ourselves on our ability to match the right technology and solution to a particular problem, regardless of what is considered “the standard”. When you confront the reality of pressures and requirements in true enterprise software development, you have to come up with crafty solutions to tricky problems. Sometimes a hybrid of technologies is the perfect fit.

---

Curious about how much it might cost to get help from an agency that specializes in Meteor & Apollo?

We're happy to provide a free estimate!