Sign Up for Free

RunKit +

Try any Node.js package right in your browser

This is a playground to test code. It runs a full Node.js environment and already has all of npm’s 400,000 packages pre-installed, including express4-tedious with all npm packages installed. Try it out:

var express4Tedious = require("express4-tedious")

This service is provided by RunKit and is not affiliated with npm, Inc or the package authors.

express4-tedious v0.3.0

Express4 middleware that enables you to create REST API using Tedious and SQL Server 2016+.

Express4 Tedious Middleware

Express4 middleware that enables you to create REST API using SQL Server and Tedious.

Contents

Setup
Initializing data access components
Create REST API

Setup

To install Tedious Express4 package, run the following command:

npm install express4-tedious --save 

Initialize Middleware

In order to initialize data access middleware, you need to add sql method to your request when you initialize express app and routes:

var express = require('express');
var tediousExpress = require('express4-tedious');

var app = express();
app.use(function (req, res, next) {
    req.sql = tediousExpress(req, {connection object});
    next();
});

This call should be used before any other route definition. Connection object should look like:

{
    "server"  : "<<server name or ip>>",
    "userName": "<<user name>>",
    "password": "<<password>>",
    "options": { "encrypt": true, "database": "<<database name>>" }
}

Use "encrypt": true if database is hosted in Azure SQL.

Create REST API

Once you setup the middleware, you can easily create REST API using T-SQL queries:

/* GET product listing. */
router.get('/', function (req, res) {

    req.sql("select * from Product for json path")
        .into(res);

});

In the sql method you can specify T-SQL query that should be executed. Method into will stream results of the query into response object. [] will be sent to the client if results are not returned by query. You can provide second patamater that represents a custom string that should be returned if there is no response from database.

You can also create REST API that uses parameters:

/* GET product by id. */
router.get('/:id', function (req, res) {
    
    req.sql("select * from Product where id = @id for json path, without_array_wrapper")
        .param('id', req.params.id, TYPES.Int)
        .into(res, '{}');

});

You can create REST APIs that are invoked using POST, PUT, and DELETE methods, and execute SQL query using exec method (without results returned to the client):

/* PUT update product. */
router.put('/:id', function (req, res) {
    
    req.sql("exec updateProduct @id, @product")
        .param('id', req.params.id, TYPES.Int)
        .param('product', req.body, TYPES.NVarChar)
        .exec(res);

});

Note: you need to provide res object to the exec method, because this method will return status code to the client.

Handling errors

This middleware returns error 500 if any error happens with descirption of the error as plain text.

You can customize the function that handles the error and provide your own error handler:

/* PUT update product. */
router.put('/:id', function (req, res) {
    
    req.sql("exec updateProduct @id, @product")
        fail(function(ex, res) { 
            res.statusCode = 500;   
            res.write(ex.message);
            res.end();
        } )
        .param('id', req.params.id, TYPES.Int)
        .param('product', req.body, TYPES.NVarChar)
        .exec(res);

});

Error handler is a function that gets exception and response output as parameters.

Metadata

RunKit is a free, in-browser JavaScript dev environment for prototyping Node.js code, with every npm package installed. Sign up to share your code.
Sign Up for Free