2013-03-14

Co.Labs

How To Use MySQL With Meteor

I've been using Meteor for the last few months and for the most part I really like it. There's a big advantage to running the same language on the client and the server, and Meteor takes care of a lot of the housekeeping that you need to do on other platforms like bundling and compressing your Javascript and CSS. But there's one problem: the DB.



The problem with Meteor, for me, isn’t really with Meteor at all. It's with MongoDB, which is bundled with Meteor. I do like the fact that I don't need to change the schema when I make changes to the DB or the fact that I can insert arbitrary data and even create real objects without resorting to ORM. But all of this doesn't really compensate for the fact that MongoDB is so limited compared to modern SQL databases. There are quite a few things I found problematic--such as the absence of AUTOINCREMENT (and yes, I know about using uuids) and not having joins.

Since Meteor is open source I figured I'll give a shot at trying to make it work with SQL and it ended up being a lot simpler than I expected. This is initial implementation of only supports MySQL. It should be relatively easy to port to other databases. Here's a demo and the project on Github. And here’s what you get with this initial version of Meteor SQL, which you can think of as the No-NoSQL solution.

Features

  • Full server side support of select, insert, update and delete on a table
  • All changes get propagated to all subscribed clients as with MongoDB
  • Changes to the DB from other apps are detected immediately (100ms, configurable), and propagated to the client
  • Light weight implementation
  • Changes are handled by triggers, no diffs to existing queries needed
  • Polling is done on a single indexed table resulting in very little overhead.
  • Includes https://github.com/hiddentao/squel for cleaner query construction
  • Partial support for general select statements. They work correctly, but are not reactive on the client.

Limitations

  • Client side the collection still use mongo syntax for find()
  • All tables need to have a unique id
  • Insert, Update and Delete operations on the client don't update the data locally. Instead they run on the server and then the server refreshes the client's data. This could result in slower refresh times, but guarantees that the client always sees data that has been committed to the DB. It also means that unlike minmongo, the full range of SQL options are available to the client.

    Installation

  • Standard mysql set up
  • Install mysql
  • create database meteor;
  • grant all on meteor.* to meteor@'localhost' IDENTIFIED BY 'xxxxxxxxx'; #Change the password to something else
  • Now install the mysql client for node.js
  • run meteor in the app's directory so that it builds the hierarchy in the .meteor directory
  • cd .meteor/local/build/server/
  • sudo npm install mysql
  • Change the database config params in server/DBconfig.js to match the password you entered above as well as anything else needed

Implementation Approach

One of the most important and elegant features in Meteor is the automatic updating of clients when a row is updated, deleted, or changed on the server. Magically, all the browser's data is updated. My understanding is that the standard implementation does a diff whenever there's a change to a table so that it can figure out which rows are changes. With SQL we can do it much more elegantly.

  • We create a table meteor_DBchanges that keeps track of changes to the DB.
  • We then add 3 triggers to each table to keep track of insert, updated, and delete on the table.
  • The triggers insert a row into meteor_DBchanges on each of these operations.
  • We poll the above table every 100ms to see if there's been any new changes since the last time. The query is simple: select * from meteor_DBchanges where cid > $lastchangeid. The column cid is a unique key with an index so it'll run fast, and whenever we do get rows that need changing, we just save the highest one in lastchangeid.
  • When there is a change, we just publish it using Meteor's standard Meteor.publish methods added(), changed() and deleted().
  • On the client operations, insert, update, delete use Meteor.call, they make a remote procedure call to the server. This is not as elegant as the current Mongo solution which updates the rows locally, and then the changes get propagated. On the other hand, it does guarantee that what you see in your browser is what the server has committed. I suspect that most developers won't care either way.

Things This Project Needs

  • The first item on the roadmap is making the select statements reactive. Currently you can have select statements, but they're a one-shot deal. Changes to the database are not going to be reflected on the client. At first I was thinking of keeping track which tables each select uses, and updating the selects based on changes to the tables. The problem is that since select statements are so flexible, there's really no easy way to know which rows have changed. I came up with a simple solution that should work for most cases. Just use SQL views instead of select statements and then treat the views the same as tables, and we're done. Views have more limitations than select statements, but for most use cases, they'll probably do.
  • The second is supporting prepared statements. This is mostly a question of the database driver. For the initial version I chose the most popular mysql node.js driver. It has the major advantage of being 100 percent Javascript. I suspect that it doesn't support prepared statements because of that, but the question needs researching.
  • The ability to handle transactions. It's possible right now, but it should be pretty easy to provide a clean API for it.
  • Finally, client side SQL. This is the hardest and probably the most interesting issue. Life would be really easy if we could just support the browsers that support SQLite embedded in them. Unfortunately, we don't have that luxury, and it doesn't look like there's any existing client side SQL implementation. Like Meteor's, this implementation will probably be quite limited. Initially it'll probably only support single table selects. It's not clear that joins are really needed client side, when they can run much more effectively server side.

All in all this has been a fun useful project and it had the rare property of going even faster than I expected. Too often in software development it feels like things are harder than they should be that I cherish the times when the sailing is smooth.

Dror Matalon can be found on Github here. He blogs at Matal.com.

[Image: Flickr user Darren Kirby]