JSON formatting error when trying to POST or PUT using express4-tedious

Question

The sql-server-samples 'Todo' app I'm trying to get working is located here. It's supposed to turn a SQL Server database into a REST API with very few lines of code.

I am unable to POST or PUT. It gives an error

message: "JSON text is not properly formatted. Unexpected character 'o' is found at position 1.",
code: 'EREQUEST',
number: 13609,
state: 4,
class: 16,
serverName: 'SERVER',
procName: 'createTodo',
lineNumber: 4

No problem with GET, GET by id or DELETE. GET and DELETE work perfectly with SQL Server 2017 Developer, Express and Azure SQL Database.

No errors running the stored procedures themselves from within SQL Server or Azure. It's only when trying to POST or PUT from the command line that I get the error:

curl -X "POST" "http://localhost:3000/todo"
-i
-H 'Content-Type: application/json'
-d '{"title": "test_title", "description": "test_description", "completed": false, "dueDate": "2014-12-14T00:00:00.000Z"}'

Not seeing anything wrong with the JSON text itself. Not seeing any character 'o' at position 1. I've tried just about every variation there is with nvarchar, varchar, using an N' versus ' at the beginning of the JSON string, changed description to descrip thinking it might be a reserved word under SQL Server 2017, nothing seems to work.

The error message using Azure SQL Database is slightly different:

JSON text is not properly formatted. Unexpected character \'o\' is found at position 1.

app.js

var express = require('express');
var config = require('config');
var bodyParser = require('body-parser');
var tediousExpress = require('express4-tedious');

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

app.use(bodyParser.text()); 
app.use('/todo', require('./routes/todo'));

// catch 404 and forward to error handler
app.use(function (req, res, next) {
    var err = new Error('Not Found: '+ req.method + ":" + req.originalUrl);
    err.status = 404;
    next(err);
});
app.set('port', process.env.PORT || 3000);

var server = app.listen(app.get('port'), function() {
    console.log('Express server listening on port ' + server.address().port);
});

module.exports = app;

todo.js

var router = require('express').Router();
var TYPES = require('tedious').TYPES;

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

    req.sql("select * from todo for json path")
        .into(res, '[]');

});

/* GET single task. */
router.get('/:id', function (req, res) {

    req.sql("select * from todo where id = @id for json path, without_array_wrapper")
        .param('id', req.params.id, TYPES.Int)
        .into(res, '{}');

});

/* POST create task. */
router.post('/', function (req, res) {

    req.sql("exec createTodo @todo")
        .param('todo', req.body, TYPES.NVarChar)
        .exec(res);

});

/* PUT update task. */
router.put('/:id', function (req, res) {

    req.sql("exec updateTodo @id, @todo")
        .param('id', req.params.id, TYPES.Int)
        .param('todo', req.body, TYPES.NVarChar)
        .exec(res);

});

/* DELETE single task. */
router.delete('/:id', function (req, res) {

    req.sql("delete from todo where id = @id")
        .param('id', req.params.id, TYPES.Int)
        .exec(res);

});

module.exports = router;

default.json

{
    "connection":{
        "server"  : "<name>.database.windows.net",
        "userName": "username",
        "password": "password",
        "options": { "encrypt": "true", "database": "<azure-database-name>" }
    }
}

setup.sql - sample Todo table and stored procedures for POST and PUT

/*
CREATE DATABASE TodoDb;
USE TodoDb;
*/
DROP TABLE IF EXISTS Todo
DROP PROCEDURE IF EXISTS createTodo
DROP PROCEDURE IF EXISTS updateTodo
GO

CREATE TABLE Todo (
    id int IDENTITY PRIMARY KEY,
    title nvarchar(30) NOT NULL,
    description nvarchar(4000),
    completed bit,
    dueDate datetime2 default (dateadd(day, 3, getdate()))
)
GO

INSERT INTO Todo (title, description, completed, dueDate)
VALUES
('Install SQL Server 2016','Install RTM version of SQL Server 2016', 0, '2017-03-08'),
('Get new samples','Go to github and download new samples', 0, '2016-03-09'),
('Try new samples','Install new Management Studio to try samples', 0, '2016-03-12')

GO

create procedure dbo.createTodo(@todo nvarchar(max))
as begin
    insert into Todo
    select *
    from OPENJSON(@todo) 
            WITH (  title nvarchar(30), description nvarchar(4000),
                    completed bit, dueDate datetime2)
end
GO

create procedure updateTodo(@id int, @todo nvarchar(max))
as begin
    update Todo
    set title = json.title, description = json.description,
        completed = json.completed, dueDate = json.dueDate
    from OPENJSON( @todo )
            WITH(   title nvarchar(30), description nvarchar(4000),
                    completed bit, dueDate datetime2) AS json
    where id = @id
end
go

select * from todo for json path

Answer 1

I think there is a minor mistake. You are using body-parser.text() but sending data as JSON. Change curl request as below:

 curl -X "POST" "http://localhost:3000/todo" -i -H 'Content-Type: text/plain' -d '{"title": "test_title", "description": "test_description", "completed": false, "dueDate": "2014-12-14T00:00:00.000Z"}'

Also, you can use Postman to test API which is more convenient

Make sure the JSON string passed to the procedure should be accurate. You can verify input to the procedure by running below script:

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);