Is it possible to write a webservice(REST API) with meteor on top of a SQL server database?

help
1

Hi there guys,ive been tasked by my boss to write a webservice to expose some routes into our database so that an external 3rd party app might access and modify some info regarding payments and accounts…I am used to writing the meteor way and i almost forgot everything about express since i tried to learn MEAN stack in one week.So i’d like to know if it is possible to write that REST API in meteor as a webservice to interact with SQL server instead of MongoDB…i am currently working on a project where i authenticate a user against a SQL server instance and if the input’s found i have to fetch all its related user data into a mongodb collection for realtime transaction…

Please guys any help or lead about the right way for me to pull this off would be greatly appreciated.

2

This is quite straightforward (more so if you are using Meteor 1.3+).

To create a REST endpoint in Meteor, look at using simple:rest, nimble:restivus or centiq:crud.

We use the npm sequelize package for server-side SQL database access. You can add that in Meteor 1.3 with meteor npm install sequelize --save and then

import Sequelize from 'sequelize';
const sql = new Sequelize('db', 'user', 'pw');

(Check the sequelize docs for more info).

If you are using Meteor 1.2, you will need to write a small wrapper package.

1 Like
3

thanks alot ill get back to you personaly when im done checking and implementing all that info

4

@robfallows .Now this is an unrelated question but can sequelize be used instead of TEDIOUS(i mean sequelize is like a multidatabase version of tedious which is only mssql )

5

I’ve no experience of tedious, but we use sequelize for MySQL and MSSQL, so … maybe yes?

6

@robfallows thank you for your time i will check everything out and build a simple Rest api to test things out

1 Like
7

@robfallows hi again sorry for the bother, just a little precision about the rest api.When you do so at your job, the workflow is:
1/ using sequelize to connect to mssql and retrieve the data
2/link then the data to mongodb collections
3/ make your endpoints available through method and publication like in simple-rest…
Is that it or you can skip step two and directly go to step 3 ?

thank you

8

Some of our data is copied to MongoDB (where we want our application to respond reactively), but most queries are SQL to endpoint only (depending on the functionality these may be any combination of REST, publication, method).

9

@robfallows Ok i finally get it …for this web service im mostly interested in SQL to endpoint…is it doable with simple:rest package?

The 3rd party that will be accessing the api will have his app built on the telephone network provider…so maybe ,i dont know yet but i think it will be SOAP instead of HTTP any ways i think the procedure must be the same.

10

Mostly, SOAP is sent over HTTP - but it’s not a requirement - any suitable transport layer can be used. It might be prudent to find out what the 3rd party expects, since we’re talking HTTP here.

11

@robfallows Exactly i just sent a mail to my manager asking about clarifying which transport protocol would be used or preferred by the 3rd party.Sincerely you have been a great help guiding me throughout all this stuff.My last question to you (at least for today :smiley: ) about the SQL to endpoint direct connection,is this possible with any of those meteor packages (simple:rest, restivus) ?

12

There is not any “magic” direct connection - but yes, it’s possible to write a REST endpoint in Meteor which exposes data/actions on a SQL backend database using one of those packages.

You just ask yourself “how would I do that with a Meteor method?” - because that’s all you really have to do.

13

@robfallows You mister deserve all my respect…You really are one of a kind.Most people would just leave one blurry answer and move on leaving me the asker with new questions and more clueless than at the beginning.You took time to guide me through all the steps and now i really have a clear idea of what ive got to do.
1-check between simple:rest and restivus which one i feel comfortable woth
2-read sequelize docs for the connection and requesting the datas
3-quickly review the REST rules in general
4-implement all of that asking myself how meteor method should return my sql server datas
5-produce a quick local demo without much security to test it all out

i am good to go.Thanks a bunch

1 Like
14

Thanks for kind words.

There is one more thing to note. The sequelize api works with promises - which is fine in Meteor - but your code will need to be written with this in mind.

15

@robfallows good to know.ill keep that in mind and get used to write them…

16

@zjjt, besides @robfallows always helpful responses (he has also helped me with another question I had) , I’d strongly suggest you opted for an HTTP (RESTfull) API rather than a SOAP one, if possible at all.

I work in an API management company (that supports both REST and SOAP API’s, so, no bias here) with hundreds of customers using APIS and definitely there has to be a very good reason to discard HTTP for SOAP.

17

@josegorchs,Definitely…i plan to demo it to my boss using http for REST but i dont know what that 3rd party’s is using as a technologie.i do hope they do their stuff with http…Thanks

18

@robfallows.Hi i hope you do fine…i still couldnt figure out how to use both technologies together(sorry)…can you help me with a sample code about how i could return sequelize queries inside my publications for use with simple:rest?

Meteor.publish("sqltable", function (index) { return sqlRecord(index);//this function should send a query via sequelize and return the row for that index }, { url: "sqltable/:0", httpMethod: "post" });

function sqlRecord(id){ return SQL.find(id).toJson()// i assume this is just an example }

thank you.

19

Publications are going to be trickier than methods, because publications return a cursor, not a value. If you have not written publications using the api before (added, changed, removed, ready) I strongly recommend sticking with methods. There are a lot of moving parts you will need to keep track of if you want a publication to behave as seamlessly as minimongo does. Also, there are no (Meteor) solutions for reactive (livedata) MSSQL queries.

Here’s a cut-down example using a client call with a server method:

On the client:

Meteor.call('dbqry', (err, res) => {
  if (err) throw err;
  console.log(res[0].count); // See notes below
});

On the server:

import { Meteor } from 'meteor/meteor';
import Sequelize from 'sequelize';

Meteor.startup(() => {
  const sequelize = new Sequelize('mssql:/user:password@host:1422/database');

  Meteor.methods({
    dbqry() {
      return sequelize.query("SELECT COUNT(*) AS [count] FROM [users]", { type: sequelize.QueryTypes.SELECT});
    }
  });
});

Notes:

  1. For this simple example, I’ve taken advantage of being able to return a Promise directly from a method. Normally, you would probably need to use a sequence of await operations inside an async function.
  2. The query returns an array of objects - in this case it’s an array of 1 element containing the count: [{count:1234}].
20

@robfallows…Now i have a clear picture of the whole process…thank you …am about to cry :joy: . I should then strictly stick to methods with mssql.while trying things out this weekend i used publications with simple:rest it worked but only for mongo…
One more thing i have a problem with authentification…how should i handle it?
mine should be custom and not rely on meteor.user i think.this 3rd party is a bank that will send messages through the api that payment has been done and create an account in our system.i think for that i need to authenticate the bank…

Thanks a Looooot (if only the O’s could really express my gratitude)