Live updates to Meteor from Postgres
I’ve been playing around with Meteor recently for an internal LShift project in which I wanted the browser to have a read-only live view onto some timetracking data from TimeTracker as it changes. When a developer records time spent on a particular task, a row is inserted into a Postgres database. Simples.
One of Meteor’s selling points is its transparent client-server data
synchronisation through its powerful
Collections API, which is backed by
MongoDB on the server side. This poses a potential challenge for us: how can we leverage the reactive
Collections of Meteor yet feed it with Postgres data?
Well, I did manage to wrestle my fork of Bevan Hunt’s (a Meteor contributor!) excellent Meteor Angular Leaderboard demo into live page update as database insertions occur on Postgres. You could extend this to deal with database updates and deletions as well, an exercise left for the willing.
Don’t worry about AngularJS too much: we don’t really use it much in this post. I just like to have it kicking around in my project to deliver a reactive, keeps-you-on-the-edge client-side experience for other parts of the project which we won’t discuss.
http://localhost:3000/ and click around.
Also, set up your Postgres instance as you’d like it. Let’s say for this post,
there’s a database called
youramazingpostgresdb which lives at
Connecting to Postgres from Meteor
According to this StackOverflow question the ‘right’ way to do this would be to write a Postgres driver / connector for Meteor, but that sounds very much like overkill for my use case.
Instead, I intend on mirroring the Postgres database inserts with Meteor’s backing Mongo. Besides providing redundancy, doing this will let us leverage Meteor’s existing latency compensation mechanisms and client-side database cache. And it’ll be quicker to implement. What’s not to like?
Connecting to Postgres from Node
It wasn’t obvious how to download and use
npm packages together with Meteor
but luckily Gurjeet Singh’s post here shows us how. I use a Mac
so I don’t apply his Ubuntu PATH hack below.
server/server.coffee, we require
pg. While we’re at it, let’s specify the connection string as well.
Set up a notification channel on Postgres
Björn Gylling’s post here shows us what to do. Basically, we make use of the NOTIFY and LISTEN commands in Postgres. Be sure to use version 9.x, because even though version 8.x also has support for them, we rely on notification payloads later on which were only introduced in 9.0.
Log in to Postgres, and create a dummy table.
We create a notification function that spits out a JSON structure that represents the row just inserted…
… which we call whenever the table
foo does a row insert.
Register Meteor (Node, really) for table inserts
Okay, just as the post shows us, we use
pg.connect to register a watcher,
being mindful that we’ve got a
pg included for use with Meteor in a slightly
different way than normal. (as a side note, you might find js2coffee useful
Let’s mirror the Postgres table
foo with a Meteor Collection called
It works! … Sorta.
Start Meteor again if it’s not already running…
… and on
psql insert something into your table
… and you’ll see a notification like this on the server console.
But try to add a second row, and Meteor doesn’t get notified. I found out from
this post that the single-shot
pg.connect wasn’t what we were looking for.
Rather, we want a standalone client that’d passively
listen for notifications.
server.coffee. This time we also make the handler parse the payload JSON and insert the object into my Meteor Collection
Foos rather than just printing it out to console.
I restart the server, and BAM!
What on Earth’s this!
Turns out (thanks to this SO question) that the error has to do with Meteor’s concurrency model. The error was thrown because
Meteor is opinionated and likes everything to run on a single thread. My handler was executing
Foo.insert (a Meteor data API call) from
pg’s asynchronous event handler
client.on, and that was definitely from another thread.
Meteor wants your code (at least, its data access calls) to be run from what’s called a Fiber (I know, fellow citizens of Britain. I know, I feel your pain) so that it can schedule your Fiber for execution on that single thread. Fibers are themselves a bit like threads, except that they aren’t ever pre-empted: they run until each of them yield voluntarily. Fiber executions are then interleaved to form a braid of cooporative multitasking. All of this is managed by the Node Fibers package.
Our solution here is simple: just wrap the notification handling code and Fibers will take care of the rest.
Fibers will happily run my handling code whenever Meteor feels like yielding some control. This is usally very fast.
Now, whenever I insert a row into Postgres, the row insertion will
automatically be mirrored into the
Foos. Meteor works its magic, and sends the update to all browsers viewing the page, in almost real-time.