Meteor, SQL & Other Databases

Since its inception, the Meteor platform has been built around using MongoDB as its core database which is great for us at Compose because we know MongoDB. But there's more to the world than just MongoDB and the Meteor developers and community know that - for example, the most voted feature in Meteor's Trello Roadmap is SQL support. There's been work on a whole range of different components to widen the Meteor frameworks compatibility and capability and in this article we're going to look at some of those projects.

This is an article in the Compose View series where we look at trends in databases and development.

Before we do that, it's worth remembering how Meteor's database layers are organised. There's the Meteor server itself which connects to an instance of MongoDB. This is the backbone of the Meteor architecture and it's where the heavy lifting is done. It's also where Meteor uses oplog-tailing to track changes in the MongoDB database. There's another database implementation in the web browser running a Meteor application, minimongo, a JavaScript-based, in-memory database which is used by the Meteor client code as a proxy to the server and it's database. It's the two-layer approach which helps Meteor run so smoothly but it does mean there's two database layers to replace to bring in another database architecture. But it also gives a number of opportunities to inject SQL into the mix.

AlaSQL

If we just want the familiarity of SQL querying, then AlaSQL could be what you are looking for. AlaSQL is a pure JavaScript database with an SQL front end to localStorage, IndexedDB and even Excel. One of its more recent enhancements is support for Meteor collections. This lets you turn...

return Robots.find({}, { sort: { introduced: 1 }} );

into

return alasql('SELECT * FROM ? ORDER BY introduced',[Robots]);

which doesn't look like a huge jump, until you realise that this works in both the browser and the server and opens up a way to do JOIN, GROUP BY, UNION, DISTINCT and others. AlaSQL is very much in development, but already comes with a useful Wiki of documentation. AlaSQL isn't just for Meteor; Meteor is just one of the things it supports along with CSV import, JSON data manipulation and a whole host of things useful to a data wrangler.

Meteor-PostgreSQL

While AlaSQL lets you harness Meteor and SQL without giving up MongoDB, the desire to completely replace the database is high in some. Take the Meteor-PostgreSQL project from Space Elephant which replaces the backend database with PostgreSQL, using node-postgres and miniSQL and AlaSQL combined for the frontend database. The developers used PostgreSQL's NOTIFY function as a substitute for Meteor's use of MongoDB's oplog. Instead of creating loosed collections in your Meteor app like...

robots = new Mongo.Collection('robots');

you can create an SQL collection instead...

robots = new SQL.Collection('robots', 'postgres://u:p@server.compose.io/compose');

which also points at the database server. Creating the table for the data is then done using createTable ...

robots.createTable({ name: ['$string'], model: ['$string'], introduced: ['$string'] }).save();

There's a video demonstrating the changes that need to be made to a Meteor application, specifically the "To-do" example. It manages it without adding anything to the line count. Other documentation shows how you can publish inner joined updates, with limit, to clients. Joins can be done server or client side, with obvious tradeoffs and benefits. The wiki has a full list of supported methods along with their SQL translation so you can start to judge how complete the implementation is. It's an intriguing project, very much in its early days, but it shows promise.

It's also not the only project taking on PostgreSQL; focussing on getting the reactive updating working, Ben Green has produced Meteor-pg. Ben had previously worked on a proof-of-concept Meteor integration with MySQL – the PostgreSQL work is the more recent work. Meteor-pg only handles the reactive element, as can be seen in the ported leaderboard example where modifications to the data take place with the server creating a node-postgres connection and executing the update command. It's still interesting work, but meteor-postgreSQL seems to be closer to Meteor philosophy and more advanced.

Meteor-RethinkDB

SQL is not the only way to plug in to new database technology. Slava Kim and others are working on a full Meteor integration with RethinkDB under the name meteor-rethinkdb. On the server, they use RethinkDB's ReQL query language to access the RethinkDB server while on the client side, they've create Reqlite, a JavaScript implementation of ReQL to run in the browser. We've talked about RethinkDB's Changefeeds and Meteor-RethinkDB makes use of them to get its updates.

That does limit the complexity of the available queries for updates but it's a limitation many would put up with for an integrated, pushed update stream. The package is, again, in the very early stages of development; for example it requires build scripts to be run to assemble the Reqlite and drivers as there's no packaging. Slava is actively recruiting developers to help with Reqlite as that, currently, needs the most work. If you want to know more, check out Slava's video where he introduced the project.

The Compose View

These are just some of the projects that are letting developers plug in alternative database technology into Meteor. AlaSQL is a generally useful JavaScript library which now allows SQL queries on Meteor collections, making it a handy complement to any project, especially Meteor ones. Meteor-postgresql is an impressive run at doing a full-stack database replacement for Meteor and Meteor-RethinkDB is the beginnings of what could be a beautiful integration. We're heading towards a time when Meteor will be able to speak to many databases, and we'll be here at Compose ready to provide them to you. It's an exciting time for meteorites.