import express from 'express';
import {Sequelize} from 'sequelize';
import sequelizeCrud from 'express-sequelize-autocrud';
const app = express();
const sequelize = new Sequelize('your_db', 'your_user', 'your_password', {
host: 'localhost',
dialect: 'mysql',
});
// Define your Sequelize models here
// Generate routes using express-sequelize-autocrud
app.use(
'/api',
sequelizeCrud(sequelize, {
'/users': {
model: sequelize.model('users'),
operations: {
getList: {
// config goes here...
},
getOne: {
// config goes here...
},
create: {
// config goes here...
},
update: {
// config goes here...
},
delete: {
// config goes here...
},
},
},
'/tasks': {
model: sequelize.model('tasks'),
operations: {
// ... set your routes...
},
},
})
);
// Start your Express server
const port = process.env.PORT || 3000;
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
sequelizeCrud
function needs 2 parameters to build your routes:
- sequelize object.
- config json describes your models and routes.
const routes = sequelizeCrud(sequelize: Sequelize, config: sequelizeCrudConfig)
app.use('/abc', routes)
export type sequelizeCrudConfig = {
[basepath: string]: {
model: modelType;
operations: {
getList?: getListOptions;
getOne?: getOneOptions;
create?: createOptions;
update?: updateOptions;
delete?: deleteOptions;
custom?: customRoutesFunc;
};
};
};
-
basepath - Base path for all the sub-crud route
-
basepath.model - modelName(string) or sequelize model object
Operation URL getList GET <API_URL>/<BASE_PATH>
getOne GET <API_URL>/<BASE_PATH>/:resourceId
create POST <API_URL>/<BASE_PATH>
bulkCreate POST <API_URL>/<BASE_PATH>/bulk
update PUT <API_URL>/<BASE_PATH>/:resourceId
delete DELETE <API_URL>/<BASE_PATH>/:resourceId
In the example below getList URL will be
http://localhost:3000/crud/users
(in case the api runs @ localhost:300)const routes = sequelizeCrud(sequelize, { '/users': {model: 'users', operations: {getList: {}}}, }); app.use('/crud', routes);
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
pagination | BOOLEAN |
false | In case of true the library will run the findAndCountAll instead of findAll , add Content-Range header, and set limit and offset from query params. |
filterableFields | string[] or { include: string[] } or { exclude: string[] } |
[] | Which fields can be filtered (where query) from query params. |
sortableFields | string[] or { include: string[] } or { exclude: string[] } |
[] | Which fields can be sorted from query params. |
In addition you can add to the config ANY sequelize findAll or findAndCountAll configurations. (depends on pagination
value).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
In Case of pagination enabled, you can not use
group
andoffset
sequelize configs
query param | expected type | pagination mode | sequelize config |
---|---|---|---|
_start |
NUMBER | true | offset |
_end |
NUMBER | true | limit = (_end - _start ) |
_sort |
STRING | does not matter | order |
_order |
DESC | ASC default value: DESC |
does not matter | order |
...rest ( key=value ) |
key: STRING value: ANY |
does not matter | where |
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
byField | string | - | If set - will use findOne (default is findByPk ) that will return the getOne results filter by a specific field. |
In addition you can add to the config ANY sequelize findByPk configurations. (except where
).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
creatableFields | string[] or { include: string[] } or { exclude: string[] } |
{ exclude: ['id', 'createdAt', 'updatedAt'], } | Which fields can be added to request body. |
In addition you can add to the config ANY sequelize create configurations. (except transaction
).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
creatableFields | string[] or { include: string[] } or { exclude: string[] } |
{ exclude: ['id', 'createdAt', 'updatedAt'], } | Which fields can be added to request body. |
path | string |
/bulk | Path to define the POST request |
In addition you can add to the config ANY sequelize create configurations. (except transaction
).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
updatableFields | string[] or { include: string[] } or { exclude: string[] } |
{ exclude: ['id', 'createdAt', 'updatedAt'], } | Which fields can be added to request body. |
In addition you can add to the config ANY sequelize update configurations. (except transaction
and where
).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
All the parameters below are Optional.
Parameter | Type | Default value | Details |
---|---|---|---|
middleware | Express Middleware function:(req, res, next) => void |
- | Middleware function that will be triggered before the sequelize operation. Check middleware section |
In addition you can add to the config ANY sequelize destroy configurations. (except transaction
and where
).
Each config can be set hardcoded or with expressCrudFunction for getting the value dynamically based on user request.
You can trigger a custom middleware function before trigger the actual sequelize operation. Great for checking permissions before perform the request. Check out the example below and read more about express middleware:
const isAdmin = (req: Request, res: Response, next: NextFunction) => {
if (req.user && req.user.role === 'admin') {
next();
} else {
res.sendStatus(401);
}
};
const routes = sequelizeCrud(sequelize, {
'/users': {
model: sequelize.model('users'),
operations: {
getList: {},
getOne: {},
create: {middleware: isAdmin},
update: {middleware: isAdmin},
delete: {middleware: isAdmin},
},
},
});
Each crud / sequelize option can be set hard-coded on using custom middleware function that should return the final value. Check out the example below:
const routes = sequelizeCrud(sequelize, {
'/users': {
model: sequelize.model('users'),
operations: {
getList: {
pagination: true,
// hard-coded value
attributes: ['id', 'name', 'email'],
// dynamic value using expressCrudFunction
where: req =>
req.user && req.user.role === 'admin' ? {} : {active: true},
},
create: {
// hard-coded value
creatableFields: {exclude: ['id', 'credits']},
},
update: {
// dynamic value using expressCrudFunction
updatableFields: req =>
req.user && req.user.role === 'admin'
? {exclude: []}
: {exclude: ['id', 'credits']},
},
},
},
});
We've added an option to set custom routes to CRUD routes in case you want to extends the options the module provided.
exapmle:
const routes = sequelizeCrud(sequelize, {
'/tasks': {
model: sequelize.model('tasts'),
operations: {
getList: {filterableFields: {exclude: []}},
custom: router => {
// Add your custom routes here
router.get('/stats', (req, res) => {
res.send('Custom stats Route!');
});
},
},
},
});
In that example GET <BASE_URL>/tasks/stats
will return 'Custom stats Route!'
message.
⚠️ Warnnings
- All CRUD fetures does not support custom routes (middleware, crudFunctions etc.) you build your own routes from scratch!
- Use custom route with different paths so CRUD routes will not be overriden.
Create, Update and Delete operations are automatically use sequelize transaction. If you have post hooks it is highly recommended to add the transaction in your hooks as well. If you will do that sequelize will roll back automatically in case something will fail in the process. Check out the example below:
// models/users.ts
import {Sequelize, DataTypes} from 'sequelize';
import {modelName as tasks} from './tasks.model';
export const modelName = 'users';
const Users = (sequelize: Sequelize) => {
return sequelize.define(
modelName,
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false,
},
fullName: {
type: DataTypes.STRING,
allowNull: false,
validate: {
max: 20,
},
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: true,
},
},
},
{
hooks: {
afterCreate: async (attributes, options) => {
await sequelize.model(tasks).create(
{
description: 'First Task',
userId: attributes.getDataValue('id'),
},
// add the transaction for connect between create user and first task.
{transaction: options.transaction}
);
},
},
}
);
};
export default Users;
By default, Auto CRUD prints logs using js console library. You can seyour own logger if you want, check out the example bellow:
import express from 'express';
import sequelizeCrud from 'express-sequelize-autocrud';
import {createLogger, format, transports} from 'winston';
import {sequelize} from './db/models';
// Create a Winston logger
export const logger = createLogger({
level: process.env.LOG_LEVEL || 'info',
transports: [new transports.Console()],
format: format.combine(format.timestamp(), format.json()),
});
const app = express();
app.use(
'/api',
sequelizeCrud(
sequelize,
{
'/users': {
model: sequelize.model('users'),
operations: {
getList: {},
getOne: {},
create: {},
update: {},
delete: {},
},
},
}, // OPTIONAL: Adding custom logger
{
logging: {
info: msg => logger.info(msg), // Defualt: console.log
warn: msg => logger.warn(msg), // Defualt: console.warn
error: msg => logger.error(msg), // Defualt: console.error
},
}
)
);