Accessing an MSSQL Database from Meteor

I recently wrote about my journey to Meteor, deciding for it, as the basis for the development of an in-house app. Well, I drew a somewhat rosy picture of Meteor. As always, when something shiny new hits the stage. But you know, if I would have learned only one thing the last 15 years, stuck waist-deep in the software-world, it would be: there is no perfect solution, no silver bullet, not even a best practice.

It started to get a little messy, when the idea, to replace the planned Xtend based API to our legacy project-portfolio-management-app, with a lean solution integrated into Meteor, struck me. Initially the Meteor app should make REST/JSON requests directed to the API, which is still in development and has to be extended to incorporate the requirements originating from the development of the Meteor app.

Before I can go on, I have to confess: I have the tendency to over-abstract. That said and provided with the fact, that I worked on the API for some weeks now, with no clear usage scenarios drawn out, you might imagine how the API code looks like. Actually the API is used in other integration projects, so it is not useless, per se. It just feels, from the distance of a few days Meteor-hacking and much more understanding of what I still need from the API, way to abstract and in the consequence to complicated.

Well, I should have learned that by now. There are three factors that let me always loose sight of the bigger picture: getting lost in nitty-gritty details of bending technology to my will, not knowing exactly what is truly needed and working basically alone.

Enough whining and back to the topic. I searched for pure JavaScript solutions to connect to the MSSQL database, which holds the data of the project-portfolio-management-app, and found Tedious. That looked promising, and a quick connection test to the database, using just Node and CoffeeScript, did show that it was indeed functional. Here is the test code:

A few month ago, when I decided to make the API and take the Xtend-(Java)-jTDS route therefor, I also found Tedious, if I remember right. But it wasn’t really working back then. It still has some flaws. The documentation says, that there should be a ‘done’ event when all rows of a request are fetched, but my callback was never called. As I learned just now, those ‘done’ events (there are more: ‘doneProc’, ‘doneInProc’) are somewhat low-level and not really defined through the way of invoking the request. You are supposed to use the constructors callback to execute something when the query finishes.

OK. I can query the database. But how do I put the Tedious NPM package into the Meteor environment. Here it comes:

  • Go to your Meteor projects folder.
  • “cd .meteor/local/build/server”
  • “npm install tedious” – you might have to prepend sudo, if you get errors. I had to use sudo on Ubuntu, on my Mac it worked without it.

I have found this steps in the answer to a StackOverflow question. To use the package you have to ‘require’ it. That works a bit differently in Meteor than usual. You have to use:

tedious = __meteor_bootstrap__.require(‘tedious’)

You can use ‘tedious’ now to create Connection and Request objects like in the example from above and query the database. But there is still one thing missing. If you try, for example, to put the received data into a Meteor collection you will get a nasty error, stating that you are not in the same fiber.

Node is inherently asynchronous, so are most of the packages. Meteor is different, there the server code runs in a single thread per request, it uses Fibers underneath to accomplish this behaviour. I usually like the async-style of Node more, but synchronous feels right in Meteor. So what to do? This guide has the answer. You have to use Futures. A Future is basically a tool that allows you to wait, until the result of an async call has returned. See the next gist for an example of using a Future, while a database is queried using Tedious. Putting the results into a Meteor collection:

 

Update: I made a new post, displaying the Npm-way you can take since Meteor 0.6.0.

Advertisements

14 thoughts on “Accessing an MSSQL Database from Meteor

  1. Ricky says:

    Very nice tutorial – I have been working on using this demo in my project, so thanks. I am new to the world of node.js and meteor.js – and I was wondering – obviously traditionally the way meteor works is it watches the collection – whenever that gets updated, the template gets updated. The problem with the solution above is that the Collection doesn’t get updated as the rows in the MSSQL database do, until you reload the server (or page if you do it in client – which, btw, __meteor_bootstrap__.require has been replaced by Npm.require in version 0.6). How can I propagate the changes to the collection in realtime? Sorry if it’s a stupid question – have only been looking at Meteor since yesterday.

    1. Dirk Porsche says:

      That’s not a stupid question.

      I made this solution to load some rather static data (types of projects) into my Meteor app. Changes are unlikely, and I’m in control of it. So if there were any changes necessary, I would restart the Meteor app, after incorporating those in the data source app.

      If I needed data updates, without reboot. Well, I think I would try to go the Meteor way and load the data from MSSQL into a Meteor collection on the server side.

      I would use something cron like (node-cron for example) to poll the MSSQL database regularly if some delay is not a big deal. In case you really need the very current data, you might call a server side method triggered when the information is accessed, that updates the “bridge collection”. Maybe when the displaying template gets rendered, is an appropriate event, but that could result in a deadlock problem … One had to experiment a bit.

      To use other types of datasources as live data in Meteor, I think you have to wait for the Meteor team to incorporate them. Others are planned. Or provide them yourself …

      I read about the replacement in 0.6.0 and will update things as soon as I will come across data loading, again. For now I’m working locally on other parts of the app. Thanks for the reminder, anyway.

      1. Ricky says:

        Thanks for the reply Dirk, much appreciated.

        I will probably end up going for the node-cron package you linked, as I suppose 30 second intervals are bearable, and I don’t have the time to invest looking into constructing an appropriate method, however may go back to it. I have also wondered (maybe another stupid question) about the security – obviously the connection details are available to anyone who views the source. I assume there is no realistic way to obscure it until meteor begins integration for the RDBMs, if they do.

        Thanks again for your time and the swift response, this article was a massive help to my project. Keep up the good work.

      2. Dirk Porsche says:

        This ones isn’t stupid, also. You can/should put the connection handling script files into the “server” directory. This way, the files will not be published to the client.

      3. rickyduck says:

        Perfect, thanks again for these guides, they’ve massively helped in what I thought was going to be a massive headache – in fact the app is almost ready. Thanks again Dirk, your blog is much commended!

    1. Dirk Porsche says:

      I guess Tedious will be the way to go, for a long time.

      You have to use some MS products (native client, …) for their solution, to work.

      No support for OSX and Linux. I don’t use MS products. I’m developing on OSX and Ubuntu and the target environment will be Ubuntu. So I will stick with Tedious, which is a pure JavaScript solution and works in any environment.

  2. Will says:

    Thanks for the tutorial, it helped a bunch.

    I was wondering how you would recommend having Iron Router wait for the data to be loaded? (For the purposes of my project putting everything into a Mongo collection would be impractical)

    1. Dirk Porsche says:

      Your welcome. Actually I’m not sure if this is still the way to do things. In the last years Meteor made some progress allowing different databases and publishing their DDP protocol. Maybe one would approach things differently now.

      I’ve basically left Meteor some months ago and never actually used any router (neither iron nor flow), I always made pure SPAs and avoided the overhead and indirection a router introduces, so I can’t give you advice on this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s