new-sequelize-restful

new-sequelize-restful is a module that exports restful APIs for your Sequelize models quickly, inspired by sequelize-restful-extended but not a fork of that. In addition to sequelize-restful-extended:

It fully supports Sequelize's complex nested querys, including operators such as $gt, $lt, $like, $in and combinations such as $or, and handles pagination in a more standard way, i.e. by Range header in HTTP request and Content-Range header in HTTP response. This is very useful wen you are doing pagination in browser using angular-paginate-anything module.

It returns negative responses by sending '403 Forbidden', '500 Internal Server Error' and other standard HTTP response codes instead of sending '200 OK' with extra information saying that some errors have happened. This allows us to return positive responses by seding instance or array of instances directly without tedious extra information. This is extremely important in angular ngResource.

Install

npm install new-sequelize-restful

Usage

Configure express

1. Import new-sequelize-restful module
2. Route your path, e.g. '/api/staff', to new Restful(sequelize)).route()
3. By default, the route should has this format: "/whatever-string/model-name" or "/whatever-string/model-name/id". Examples: "/api/staff", "/api/staff/1"

Code example:

var express = require('express');
var Sequelize = require('sequelize');
var Restful = require('new-sequelize-restful');

var app = express();
var sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname');

app.use(require('body-parser').json({
	type: 'application/*',
}));
app.all(/\/api\//, (new Restful(sequelize)).route());
app.listen(80);

The Restful APIs

We take 'staff' model for example. Remember that:

1. It returns '200 OK' with instance or instances(depending on the request of a singel object or a collection) as content for positive(successfull) responses.
2. It returns '403 Forbidden', '500 Internal Server Error' and other standard HTTP response codes with error message as content for negative(some errors has happened) responses;

GET /api/staff

Return an array of all instances. Total number of rows can be read from Content-Range header in response.

# curl http://127.0.0.1:8090/api/staff

[{
	"id":1,
	"account":"account1",
	"password":"123456",
	"name":"tom",
	"role":1,
	"createAt":"2015-05-06T13:57:37.000Z",
	"lastLogin":"2015-05-06T13:57:37.000Z",
	"loginCnt":1
},{
	"id":2,
	"account":"account2",
	"password":"12wfde",
	"name":"marry",
	"role":1,
	"createAt":"2015-05-06T13:58:15.000Z",
	"lastLogin":"2015-05-06T13:58:15.000Z",
	"loginCnt":1
}]

You can add parameters to the requests. Complex Sequelize's nested query is supported.

1. All leading underlines '_'in keys are stripped, e.g. 'http://127.0.0.1:8090/api/staff?_name=tom' is equal to 'http://127.0.0.1:8090/api/staff?name=tom'. Because some client side codes do not allow JSON keys with leading '$' such as '$or' and '$and' while Sequelize actually has $or and $and. In this case, you can wrap these keys with leading '_', e.g. '_$or' and '_$and'.

2. All values will be parseJSONed if they are of type string and can be parseJSONed safely without error. For example: ('\' is just for shell escape)

curl 'http://127.0.0.1:8090/api/staff?_name=abc2&account=\{"$like":"%ab%"\}'

will result the where object in the Sequelize:

{ 
	account: { 
		'$like': '%ab%' 
	}, 
	name: 'abc2' 
}

You can also add pagination information to the query. The Pagination information is stored in Range header (includes offset, limit) when request and Content-Range header (includes offset, limit, total-count) when response. Go to 'angular-paginate-anything' for more information.

# curl -v --header "Range: 0-10" 'http://127.0.0.1:8090/api/staff?_name=abc2&account=\{"$like":"%ab%"\}'

Response:

HTTP/1.1 200 OK
X-Powered-By: Express
Accept-Ranges: items
Range-Unit: items
Content-Range: 0-0/1
Content-Type: application/json; charset=utf-8
Content-Length: 153
ETag: W/"99-28f12ee1"
set-cookie: connect.sid=s%3AA-SdrqRTV-avX_1O_KepGhoKlQFBVsEU.VDvv64%2BZuHN63Dnz8qTjYt9WDhWhD04z1VzpQcxwbW8; Path=/; HttpOnly
Date: Thu, 14 May 2015 05:09:11 GMT
Connection: keep-alive

[{
	"id":16,
	"account":"abc2",
	"password":"123",
	"name":"abc2",
	"role":0,
	"createAt":"2015-05-10T03:52:37.000Z",
	"lastLogin":"0000-00-00 00:00:00",
	"loginCnt":0
}]

Sorting by a specified column descending or ascending

$ curl 'http://127.0.0.1:8090/api/staff?$sort=-id'

[{
	"id":20,
	"account":"acct",
	"password":"123",
	"name":"jon",
	"role":1,
	"createAt":"2015-05-14T05:23:49.000Z",
	"lastLogin":"0000-00-00 00:00:00",
	"loginCnt":0
},{
	"id":19,
	"account":"ddd",
	"password":"123",
	"name":"noname",
	"role":0,
	"createAt":"2015-05-13T11:24:00.000Z",
	"lastLogin":"0000-00-00 00:00:00",
	"loginCnt":0
},{
	"id":18,
	"account":"vv",
	"password":"vv",
	"name":"vv",
	"role":0,
	"createAt":"2015-05-12T02:07:59.000Z",
	"lastLogin":"0000-00-00 00:00:00",
	"loginCnt":0
}]

POST /api/staff

Creates a new instance.

$ curl --header 'Content-Type: application/json' -d '{"account":"acct","password":"123","name":"jon","role":1}' -X POST http://www.scaleoa.com:8090/api/staff

{
	"id":20,
	"account":"acct",
	"password":"123",
	"name":"jon",
	"role":1
}

HEAD /api/staff

Returns a description of the model

$ curl -i -X HEAD http://127.0.0.1:8090/api/staff

{ 
	id: { type: 'INT(11)', allowNull: false, defaultValue: null },
	img: { type: 'MEDIUMTEXT', allowNull: true, defaultValue: null },
	account: { type: 'VARCHAR(64)', allowNull: false, defaultValue: null },
	password: { type: 'VARCHAR(64)', allowNull: false, defaultValue: null },
	name: { type: 'VARCHAR(64)', allowNull: false, defaultValue: null },
	role: { type: 'INT(11)', allowNull: false, defaultValue: null },
	createAt: { 
		type: 'TIMESTAMP',
		allowNull: false,
		defaultValue: 'CURRENT_TIMESTAMP' 
	},
	lastLogin: { 
		type: 'TIMESTAMP',
		allowNull: false,
		defaultValue: '0000-00-00 00:00:00' 
	},
	loginCnt: { type: 'INT(11)', allowNull: false, defaultValue: null }
}

GET /api/staff/1

Returns the instance with id 1

curl 'http://127.0.0.1:8090/api/staff/1'

{
	"id":1,
	"account":"account1",
	"password":"123456",
	"name":"tom",
	"role":1,
	"createAt":"2015-05-06T13:57:37.000Z",
	"lastLogin":"2015-05-06T13:57:37.000Z",
	"loginCnt":1
}

DELETE or DEL /api/staff/1

Destroys an instance with id 1

$ curl -X DELETE 'http://127.0.0.1:8090/api/staff/1'

{}

PUT /api/staff/1

Updates an instance with id 1

$ curl -X PUT --header 'Content-Type: application/json' -d '{"name":"new name"}' 'http://127.0.0.1:8090/api/staff/1'

{
	"id":1,
	"account":"account1",
	"password":"123456",
	"name":"new name",
	"role":1,
	"createAt":"2015-05-06T13:57:37.000Z",
	"lastLogin":"2015-05-06T13:57:37.000Z",
	"loginCnt":1
}

What's next ?

Restful APIs with associations from Sequelize models will be added at next update.