README
feathers-sequelize
A Feathers database adapter for Sequelize, an ORM for Node.js. It supports PostgreSQL, MySQL, MariaDB, SQLite and MSSQL and features transaction support, relations, read replication and more.
- feathers-sequelize
Very Important: Before using this adapter you have to be familiar with both, the Feathers Basics and general use of Sequelize. For associations and relations see the associations section. This adapter may not cover all use cases but they can still be implemented using Sequelize models directly in a Custom Feathers service.
npm install --save feathers-sequelize
And one of the following:
npm install --save pg pg-hstore
npm install --save mysql2 // For both mysql and mariadb dialects
npm install --save sqlite3
npm install --save tedious // MSSQL
Important:
feathers-sequelize
implements the Feathers Common database adapter API and querying syntax. For more information about models and general Sequelize usage, follow up in the Sequelize documentation.
API
service(options)
Returns a new service instance initialized with the given options.
const Model = require('./models/mymodel');
const service = require('feathers-sequelize');
app.use('/messages', service({ Model }));
app.use('/messages', service({ Model, id, events, paginate }));
Options:
Model
(required) - The Sequelize model definitionid
(optional, default: primary key of the model) - The name of the id field property. Will use the first property withprimaryKey: true
by default.raw
(optional, default:true
) - Runs queries faster by returning plain objects instead of Sequelize models.Sequelize
(optional, default:Model.sequelize.Sequelize
) - The Sequelize instanceevents
(optional) - A list of custom service events sent by this servicepaginate
(optional) - A pagination object containing adefault
andmax
page sizemulti
(optional) - Allowcreate
with arrays andupdate
andremove
withid
null
to change multiple items. Can betrue
for all methods or an array of allowed methods (e.g.[ 'remove', 'create' ]
)operators
(optional) - A mapping from query syntax property names to to Sequelize secure operatorswhitelist
(optional) - A list of additional query parameters to allow (e..g[ '$regex', '$geoNear' ]
). Default is the supportedoperators
params.sequelize
When making a service method call, params
can contain an sequelize
property which allows to pass additional Sequelize options. This can e.g. be used to retrieve associations. Normally this wil be set in a before hook:
app.service('messages').hooks({
before: {
find(context) {
// Get the Sequelize instance. In the generated application via:
const sequelize = context.app.get('sequelizeClient');
const { User } = sequelize.models;
context.params.sequelize = {
include: [ User ]
}
return context;
}
}
});
Other options that params.sequelize
allows you to pass can be found in Sequelize querying docs.
Beware that when setting a top-level where
property (usually for querying based on a column on an associated model), the where
in params.sequelize
will overwrite your query
.
operators
Sequelize deprecated string based operators a while ago for security reasons. Starting at version 4.0.0 feathers-sequelize
converts queries securely, so you can still use string based operators listed below. If you want to support additional Sequelize operators, the operators
service option can contain a mapping from query parameter name to Sequelize operator. By default supported are:
'$eq',
'$ne',
'$gte',
'$gt',
'$lte',
'$lt',
'$in',
'$nin',
'$like',
'$notLike',
'$iLike',
'$notILike',
'$or',
'$and'
// Find all messages in room 2 or 5
app.service('users').find({
query: {
name: {
$like: 'Dav%'
}
}
});
GET /users?name[$like]=Dav%
Caveats
raw
queries
Sequelize By default, all feathers-sequelize
operations will return raw
data (using raw: true
when querying the database). This results in faster execution and allows feathers-sequelize to interoperate with feathers-common hooks and other 3rd party integrations. However, this will bypass some of the "goodness" you get when using Sequelize as an ORM:
- custom getters/setters will be bypassed
- model-level validations are bypassed
- associated data loads a bit differently
- ...and several other issues that one might not expect
Don't worry! The solution is easy. Please read the guides about working with model instances.
Working with MSSQL
When using MSSQL as the database, a default sort order always has to be applied, otherwise the adapter will throw an Invalid usage of the option NEXT in the FETCH statement.
error. This can be done in your model with:
model.beforeFind(model => model.order.push(['id', 'ASC']))
Or in a hook like this:
module.exports = function (options = {}) {
return async context => {
const { query = {} } = context.params;
// Sort by id field ascending (or any other property you want)
// See https://docs.feathersjs.com/api/databases/querying.html#sort
const $sort = { id: 1 };
context.params.query = {
$sort: {
},
...query
}
return context;
}
}
Example
Here is an example of a Feathers server with a messages
SQLite Sequelize Model:
$ npm install @feathersjs/feathers @feathersjs/errors @feathersjs/express @feathersjs/socketio sequelize feathers-sequelize sqlite3
In app.js
:
const path = require('path');
const feathers = require('@feathersjs/feathers');
const express = require('@feathersjs/express');
const socketio = require('@feathersjs/socketio');
const Sequelize = require('sequelize');
const service = require('feathers-sequelize');
const sequelize = new Sequelize('sequelize', '', '', {
dialect: 'sqlite',
storage: path.join(__dirname, 'db.sqlite'),
logging: false
});
const Message = sequelize.define('message', {
text: {
type: Sequelize.STRING,
allowNull: false
}
}, {
freezeTableName: true
});
// Create an Express compatible Feathers application instance.
const app = express(feathers());
// Turn on JSON parser for REST services
app.use(express.json());
// Turn on URL-encoded parser for REST services
app.use(express.urlencoded({ extended: true }));
// Enable REST services
app.configure(express.rest());
// Enable Socket.io services
app.configure(socketio());
// Create an in-memory Feathers service with a default page size of 2 items
// and a maximum size of 4
app.use('/messages', service({
Model: Message,
paginate: {
default: 2,
max: 4
}
}));
app.use(express.errorHandler());
Message.sync({ force: true }).then(() => {
// Create a dummy Message
app.service('messages').create({
text: 'Message created on server'
}).then(message => console.log('Created message', message));
});
// Start the server
const port = 3030;
app.listen(port, () => {
console.log(`Feathers server listening on port ${port}`);
});
Run the example with node app
and go to localhost:3030/messages.
Associations
Embrace the ORM
The documentation on Sequelize associations and relations is essential to implementing associations with this adapter and one of the steepest parts of the Sequelize learning curve. If you have never used an ORM, let it do a lot of the heavy lifting for you!
params.sequelize.include
Setting Once you understand how the include
option works with Sequelize, you will want to set that option from a before hook in Feathers. Feathers will pass the value of context.params.sequelize
as the options parameter for all Sequelize method calls. This is what your hook might look like:
// GET /my-service?name=John&include=1
function (context) {
const { include, ...query } = context.params.query;
if (include) {
const AssociatedModel = context.app.services.fooservice.Model;
context.params.sequelize = {
include: [{ model: AssociatedModel }]
};
// Update the query to not include `include`
context.params.query = query;
}
return context;
}
Underneath the hood, feathers will call your models find method sort of like this:
// YourModel is a sequelize model
const options = Object.assign({ where: { name: 'John' }}, context.params.sequelize);
YourModel.findAndCount(options);
For more information, follow up up in the Sequelize documentation for associations and this issue.
Querying
Additionally to the common querying mechanism this adapter also supports all Sequelize query operators.
Note: This adapter supports an additional
$returning
parameter for patch and remove queries. By settingparams.$returning = false
it will disable feathers and sequelize from returning what was changed, so mass updates can be done without overwhelming node and/or clients.
Querying a nested column
To query based on a column in an associated model, you can use Sequelize's nested column syntax in a query. The nested column syntax is considered an operator by Feathers, and so each such usage has to be whitelisted.
Example:
// Find a user with post.id == 120
app.service('users').find({
query: {
'$user.post.id