express-sweet
Advanced tools
EXPRESS SWEET is a powerful Express.js extension that streamlines your workflow and boosts productivity with a suite of utilities and enhancements.
EXPRESS SWEET is a powerful Express.js extension that streamlines your workflow and boosts productivity with a suite of utilities and enhancements.
Use the application generator tool, express-sweet-generator, to quickly create an application skeleton.
npm install -g express-sweet-generator
express-sweet -h
Usage: express-sweet [options] [dir]
Options:
--version output the version number
-o, --output <output> add output <module> support (esm|cjs) (defaults to cjs)
-p, --port <port> application listening port (default: 3000)
-f, --force force on non-empty directory
-h, --help output usage information
myapp. The app will be created in a folder named myapp in the current working directory.
express-sweet -o esm myapp
cd myapp/
npm install
CREATE DATABASE IF NOT EXISTS `sample_db` DEFAULT CHARACTER SET utf8mb4;
USE `sample_db`;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(100) NOT NULL,
`icon` varchar(768) NOT NULL DEFAULT MD5(RAND()),
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `ukUserEmail` (`email`),
UNIQUE KEY `ukUserIcon`(`icon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `profile` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(10) unsigned NOT NULL,
`address` varchar(255) NOT NULL,
`tel` varchar(14) NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `ukProfileUserId` (`userId`),
CONSTRAINT `fkProfileUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `comment` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(10) unsigned NOT NULL,
`text` text NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
CONSTRAINT `fkCommentUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `book` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(10) unsigned NOT NULL,
`title` text NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `ukBookTitle` (`userId`, `title`(255)),
CONSTRAINT `fkBookUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`id`, `email`, `password`, `name`, `icon`) VALUES
(1, 'robin@example.com', 'password', 'Robin', '/upload/1.png'),
(2, 'taylor@example.com', 'password', 'Taylor', '/upload/2.png');
INSERT INTO `profile` (`userId`, `address`, `tel`) VALUES
(1, '777 Brockton Avenue, Abington MA 2351', '202-555-0105'),
(2, '30 Memorial Drive, Avon MA 2322', '');
INSERT INTO `comment` (`userId`, `text`) VALUES
(1, 'From Robin #1'),
(1, 'From Robin #2'),
(2, 'From Taylor #1');
INSERT INTO `book` (`userId`, `title`) VALUES
(1, 'Beautiful'),
(1, 'Lose Yourself'),
(2, 'When Im Gone');
config/database.js. For details, please refer to here.
export default {
development: {
username: 'root',
password: 'password',
database: 'sample_db',
host: 'localhost',
dialect: 'mariadb'
},
test: {
username: 'root',
password: 'password',
database: 'sample_db',
host: 'localhost',
dialect: 'mariadb'
},
production: {
username: 'root',
password: 'password',
database: 'sample_db',
host: 'localhost',
dialect: 'mariadb'
}
}
.env file
NODE_ENV=development
npm start
http://localhost:3000/ in your browser to access the app..
├── .env
├── app.js
├── ecosystem.config.js
├── nginx.sample.conf
├── package.json
├── bin
│ └── www
├── client
│ ├── package.json
│ ├── webpack.config.js
│ └── src
├── config
│ ├── authentication.js
│ ├── config.js
│ ├── database.js
│ └── view.js
├── errors
│ └── UserNotFound.js
├── models
│ ├── BookModel.js
│ ├── CommentModel.js
│ ├── ProfileModel.js
│ └── UserModel.js
├── public
│ ├── build
│ └── upload
├── routes
│ ├── login.js
│ ├── users.js
│ └── api
│ └── users.js
├── shared
│ ├── CustomValidation.js
│ └── empty.js
└── views
├── edit-personal.hbs
├── error.hbs
├── login.hbs
├── personal.hbs
├── users.hbs
├── layout
│ └── default.hbs
└── partials
└── .gitkeep
If you set the environment variable file in env_path of config/config.js, the value of the environment variable file will be set automatically in process.env.
The NODE_ENV environment variable is required. If omitted, development is used.
You can access the environment variables yourself and perform your own checks and logic, as in the following example.
if (process.env.NODE_ENV === 'development')
;
The basic configuration of EXPRESS SWEET is defined in the config/config.js file.
Click here to download a sample ESM configuration and here to download a sample CJS configuration.
env_path: string
The path to the environment configuration file (.env).
When you start the EXPRESS SWEET application, the contents of the environment configuration file are automatically read and saved in process.env.
The default is .env.
cors_enabled: boolean
Set to true to allow requests from another domain to the application.
The default is false.
max_body_size: string|number
Controls the maximum request body size.
If this is a number, then the value specifies the number of bytes.
if it is a string, the value is passed to the bytes library for parsing.
The default is 100kb.
router_dir: string
The directory path where the routes module is located.
The default is the routes/ directory directly under the application root.
default_router: string
EXPRESS SWEET can be told to load a default router when a URI is not present, as will be the case when root URL (/) is requested.
For example, to specify a default route, set default_router as follows.
Where blog is the name of the router module you want used.
Next, create the routes/blog.js module.
In the following example, requesting the root URL (/) will result in "Hello World".
import {Router} from 'express';
const router = Router();
router.get('/', (req, res) => {
res.send('Hello World');
});
export default router;
rewrite_base_url: (baseUrl: string) => string
This is a hook that rewrites the base URL.
If you want to rewrite the app.locals.baseUrl property and the view's baseUrl variable, use this hook to return a new base URL.
The default value is the referrer's origin (eg https://example.com).
In this example, https://example.com/admin is used as the base URL.
rewrite_base_url: baseUrl => {
return `${baseUrl}/admin`;
}
is_ajax: (req: express.Request) => boolean
How to determine if it is an ajax request.
The default is that if there is an XMLHttpRequest in the request header (req.xhr) returns true.
For example, if there is no XMLHttpRequest in req(express.Request) and the Ajax endpoint starts with /api, a custom Ajax decision can be made like return /^\/api\//.test(req.path).
is_ajax: req => {
// If the request URL begins with /api, it is assumed to be Ajax.
return /^\/api/.test(req.path);
// return !!req.xhr;
}
hook_handle_error: (error: any, req: express.Request, res: express.Response, next: express.NextFunction) => void
Hooks the default behavior on request errors.
If unset, simply returns an error HTTP status. (res.status(error.status||500).end();)
hook_handle_error: (error, req, res, next) => {
if (error.status === 404)
// If the URL cannot be found, a 404 error screen (views/error/404.hbs) is displayed.
res.render('error/404');
else
// For other errors, unknown error screen (views/error/500.hbs) is displayed.
res.render('error/500');
},
Routing refers to determining how an application responds to a client request to a particular endpoint, which is a URI (or path) and a specific HTTP request method (GET, POST, and so on).
Each route can have one or more handler functions, which are executed when the route is matched.
Route definition takes the following structure.
import express from 'express';
const app = express();
app.METHOD(PATH, HANDLER)
All routes are defined in your route files, which are located in the routes/ directory.
These files are automatically mapped by EXPRESS SWEET to the route files specified in the URI and the route handlers defined in the route files.
The following examples illustrate defining simple routes.
routes/user.js responds to GET /user requests.
import {Router} from 'express';
const router = Router();
router.get('/', (req, res) => {
res.send('Hello World');
});
export default router;
The router supports nested files.
If you create a nested folder structure files will be automatically routed in the same way still.
routes/api/users.js responds to GET /api/users requests.
import {Router} from 'express';
const router = Router();
router.get('/', (req, res) => {
res.send('Hello World');
});
export default router;
EXPRESS SWEET can be told to load a default router when a URI is not present, as will be the case when only your site root URL (/) is requested.
To specify a default router, open your config/config.js file and set this variable:
default_router: '/blog'
Where blog is the name of the router module you want used.
Next, create the routes/blog.js module.
import {Router} from 'express';
const router = Router();
router.get('/', (req, res) => {
res.send('Hello World');
});
export default router;
Now when you request the root URL (/), you will see "Hello World".
Express supports the following routing methods corresponding to the HTTP methods of the same names. For more details about routing, see the Exprees’s Routing Guide.
EXPRESS SWEET uses Handlebars as its view template engine.
This section describes the basic usage of the view on EXPRESS SWEET.
See the here for more information on how to use Handlebars.
The view (template engine) configuration is defined in the config/view.js file.
Click here to download a sample ESM configuration and here to download a sample CJS configuration.
views_dir: stringviews/ directory directly under the application root.partials_dir: stringviews/partials/ directory directly under the application root.layouts_dir: stringviews/layout/ directory directly under the application root.default_layout: stringviews/layout/default.* File directly under the application root.extension.extension: string.hbs.beforeRender: (req: express.Request, res: express.Response) => Promise<void>|voidbeforeRender: (req, res) => {
res.locals.extra = 'Extra';
}
To mark where layout should insert page.
{{{body}}}
To declare a block placeholder in layout.
{{{block "script"}}}
To define block content in a page.
{{#contentFor "script"}}
CONTENT HERE
{{/contentFor}}
There are three ways to use a layout, listed in precedence order.
Declarative within a page. Use handlebars comment.
{{!< LAYOUT}}
Layout file resolution.
If path starts with '.'
LAYOUT is relative to template
Else If `layoutsDir` is set
LAYOUT is relative to `layoutsDir`
Else
LAYOUT from path.resolve(dirname(template), LAYOUT)
As an option to render.
Do not use this option in conjunction with passing user submitted data to res.
render e.g. res.render('index', req.query).
This allows users to read arbitrary files from your filesystem.
res.render('veggies', {
title: 'My favorite veggies',
veggies: veggies,
layout: 'layout/veggie'
});
This option also allows for layout suppression (both the default layout and when specified declaratively in a page) by passing in a falsey Javascript value as the value of the layout property.
res.render('veggies', {
title: 'My favorite veggies',
veggies: veggies,
layout: null // render without using a layout template
});
Layout file resolution.
If path starts with '.'
layout is relative to template
Else If `layoutsDir` is set
layout is relative to `layoutsDir`
Else
layout from path.resolve(viewsDir, layout)
Lastly, use defaultLayout if specified in hbs configuration options.
Layouts can be nested: just include a declarative layout tag within any layout template to have its content included in the declared "parent" layout.
Be aware that too much nesting can impact performances, and stay away from infinite loops.
findObjectInArray()
Finds an object in an array based on the specified field name and value.
Parameters:
Return:
object|null The first object in the array that matches the criteria, or null if no match is found.
{{!--
items is an array of objects: [{id: 123, name: 'Item A'}, {id: 456, name: 'Item B'}]
This code will output: "Item A"
--}}
{{#each items}}
{{#if (eq id 123)}}
{{lookup (findObjectInArray ../items 'id' id) 'name'}}
{{/if}}
{{/each}}
eq()
Determine whether or not two values are equal (===).
Parameters:
Return:
boolean Returns true if the value and type are the same, false if they are different.
{{eq val1 val2}}
{{#if (eqw val1 val2)}}...{{/if}}
eqw()
Determine whether or not two values are equal (==) i.e. weak checking.
Parameters:
Return:
boolean Returns true if the values are the same, false if they are different.
{{eqw val1 val2}}
{{#if (eqw val1 val2)}}...{{/if}}
neq()
Determine whether or not two values are not equal (!==).
Parameters:
Return:
boolean Returns true if the value and type are different, false if they are the same.
{{neq val1 val2}}
{{#if (neq val1 val2)}}...{{/if}}
neqw()
Determine whether or not two values are not equal (!=) weak checking.
Parameters:
Return:
boolean Returns true if the values are different, false if they are the same.
{{neqw val1 val2}}
{{#if (neqw val1 val2)}}...{{/if}}
lt()
Check for less than condition (a < b).
Parameters:
Return:
boolean Returns true if the first value is less than the second value, false otherwise.
{{lt val1 val2}}
{{#if (lt val1 val2)}}...{{/if}}
lte()
Check for less than or equals condition (a <= b).
Parameters:
Return:
boolean Returns true if the first value is less than or equal to the second value, false otherwise.
{{lte val1 val2}}
{{#if (lte val1 val2)}}...{{/if}}
gt()
Check for greater than condition (a > b).
Parameters:
Return:
boolean Returns true if the first value exceeds the second value, false otherwise.
{{gt val1 val2}}
{{#if (gt val1 val2)}}...{{/if}}
gte()
Check for greater than or equals condition (a >= b).
Parameters:
Return:
boolean Returns true if the first value is greater than or equal to the second value, false otherwise.
{{gte val1 val2}}
{{#if (gte val1 val2)}}...{{/if}}
not()
Logical NOT of any expression.
Parameters:
Return:
boolean Returns the logical negation of the value.
{{not val}}
{{#if (not (eq val1 val2))}}...{{/if}}
ifx()
Helper to imitate the ternary ?: conditional operator.
Parameters:
Return:
any Returns the result of the ternary operator.
{{ifx true val1 val2}}
{{ifx false val1 val2}}
{{ifx (eq val1 val2) val3 val4}}
{{ifx (not (eq val1 val2)) val3 val4}}
{{ifx true val}}
{{ifx false val}}
empty()
Check if it is empty.
If the value is an array, returns true if there are no elements.
If the value is a string, the leading and trailing spaces are trimmed and then checked.
Parameters:
Return:
boolean Returns true if the value is empty, false otherwise.
{{empty val}}
{{#if (empty val)}}...{{/if}}
notEmpty()
Check that it is not empty.
If the value is an array, returns true if there is an element.
If the value is a string, the leading and trailing spaces are trimmed and then checked.
Parameters:
Return:
boolean Returns true if the value is not empty, false otherwise.
{{notEmpty val}}
{{#if (notEmpty val)}}...{{/if}}
count()
Determine the length of an array.
Parameters:
Return:
number|false Returns the length of the array if the value is an array, false if the value is not an array.
{{count val}}
and()
Returns the boolean AND of two or more parameters passed i.e it is true iff all the parameters are true.
Parameters:
Return:
boolean Returns the result of the logical product.
{{and val1 val2}}
{{#if (and val1 val2)}}...{{/if}}
or()
Returns the boolean OR of two or more parameters passed i.e it is true if any of the parameters is true.
Parameters:
Return:
boolean Returns the result of the OR.
{{or val1 val2}}
{{#if (or val1 val2)}}...{{/if}}
coalesce()
Returns the first non-falsy value from the parameter list.
Works quite similar to the SQL's COALESCE() function, but unlike this checks for the first non-false parameter.
Parameters:
Return:
any Returns the first non-false element of the parameter.
{{coalesce val1 val2}}
includes()
Returns boolean if the array contains the element strictly or non-strictly.
Parameters:
false for non-strict checking. true by default.Return:
boolean Returns true if the array contains the specified value, false otherwise.
{{includes [1, 2, 3] 2}}
{{includes [1, 2, 3] 2 true}}
{{#if (includes [1, 2, 3] 2)}}...{{/if}}
{{ifx (includes [1, 2, 3] 2) true false}}
regexMatch()
Returns true if the given str matches the given regex.
Parameters:
Return:
boolean true if there is a match between the regular expression and the string str. Otherwise, false.
{{regexMatch 'Hello, world!' 'Hello'}}
{{regexMatch 'Hello, world!' 'Hello' 'i'}}
{{#if (regexMatch 'Hello, world!' 'Hello')}}...{{/if}}
cacheBusting()
Returns the Assets path containing the file update time parameter.
Parameters:
undefined).Return:
string Returns the Assets file path with the update date and time parameters.
{{!-- results in: example.com/assets/style.css?1620526340463 --}}
{{cacheBusting '/assets/style.css' '//example.com'}}
jsonStringify()
Stringify an object using JSON.stringify.
Parameters:
Return:
string A JSON string representing the given value, or undefined.
{{jsonStringify val}}
jsonParse()
Parses the given string using JSON.parse.
Parameters:
Return:
any JavaScript value or object described by a string.
{{jsonParse val}}
replace()
Returns a new string with some or all matches of a pattern replaced by a replacement.
Parameters:
Return:
string Character string after replacement.
{{replace 'The quick brown fox jumps over the lazy dog. If the dog reacted, was it really lazy?' 'dog' 'monkey'}}
split()
Split string by the given character.
Parameters:
Return:
string[] An Array of strings, split at each point where the separator occurs in the given string. The default is a comma.
{{split "a,b,c" ","}}
{{#each (split list ',')}}<div>{{this}}</div>{{/each}}
formatBytes()
Convert bytes to just the right units(KB, MB, GB, TB, PB, EB, ZB, YB).
Parameters:
Return:
string Returns a value with units.
{{!-- results in: 1 KB --}}
{{formatBytes 1024}}
{{!-- results in: 1.21 KB --}}
{{formatBytes 1234 2}}
{{!-- results in: 1.205 KB --}}
{{formatBytes 1234 3}}
{{!-- results in: 0 Bytes --}}
{{formatBytes 0}}
formatDate()
Use moment to format the date.
Parameters:
moment.Return:
string Returns formatted date.
{{!-- results in: 2021/10/24 --}}
{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z"}}
{{!-- results in: 2021/10/24 --}}
{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z" 'jp'}}
{{!-- results in: 2021/10/24 --}}
{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z" 'es'}}
number2locale()
Returns the language-sensitive representation of a number as a string.
Parameters:
locales parameter of the Intl.NumberFormat() constructor.
In implementations without Intl.NumberFormat support, this parameter is ignored and the host's locale is usually used.Return:
string A string with a language-sensitive representation of the given number.
{{!-- results in: 123,456.789 --}}
{{number2locale 123456.789}}
{{!-- German uses comma as decimal separator and period for thousands. --}}
{{!-- results in: 123.456,789 --}}
{{number2locale 123456.789 'de-DE'}}
add()
Calculates the sum of two numbers.
Parameters:
Return:
number
{{add 1 2}}
sub()
Calculates the difference of the given values.
Parameters:
Return:
number
{{sub 5 2}}
multiply()
Calculate the multiplication of the given values.
Parameters:
Return:
number
{{multiply 5 2}}
divide()
Compute the division of the given values.
Parameters:
Return:
number
{{divide 10 2}}
ceil()
Round up the value.
Parameters:
Return:
number
{{ceil 5.6}}
floor()
Rounds down a number.
Parameters:
Return:
number
{{floor 5.6}}
abs()
Returns an absolute value.
Parameters:
Return:
number
{{abs -5.6}}
Models provide a way to interact with a specific table in your database.
EXPRESS SWEET provides a Sequelize based model class that offers some great features, including.
This class provides a solid base from which to build your own models, allowing you to rapidly build out your application’s model layer.
The database configuration is defined in the config/database.js file.
Click here to download a sample ESM configuration and here to download a sample CJS configuration.
username: stringpassword: string|nullnull).database: stringhost: stringport: number|nullnull).dialect: stringmariadb, mysql, postgres, sqlite and mssql.logging: boolean|(...message: any[]) => voidfalse).timezone: stringtimezone: '+09:00'
Place the model in the models/ directory of the root directory.
When you load the model, you have immediate access to the model's functions for working with the database.
import BookModel from '../models/BookModel';
// INSERT INTO book (title) VALUES ('Beautiful')
await BookModel.create({title: 'Beautiful'});
// SELECT * FROM book
await BookModel.findAll();
// UPDATE book SET title = 'Beautiful' WHERE id= 1
await BookModel.update({title: 'Beautiful'}, {where: {id: 1}});
// DELETE FROM book WHERE id= 1
await BookModel.destroy({where: {id: 1}});
To take advantage of EXPRESS SWEET’s model, you would simply create a new model class that extends database/Model.
This class provides convenient access to the database connection, the Query Builder, and a number of additional convenience methods.
For more information, see reference.
import * as expressExtension from 'express-sweet';
export default class extends expressExtension.database.Model {
static get table() {
return 'user';
}
static get attributes() {
return {
id: {
type: this.DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: this.DataTypes.STRING,
email: this.DataTypes.STRING,
password: this.DataTypes.STRING,
icon: this.DataTypes.STRING,
created: this.DataTypes.DATE,
modified: this.DataTypes.DATE
};
}
}
This is the class that makes the database connection.
See here for other available methods.
The database connection is automatic when the model is loaded.
The configuration related to the database connection is defined in the config/database.js file.
For more information on database configuration, see Database configuration.
public constructor()
Instantiate sequelize with name of database, username and password.
public isConnect()
Test the connection by trying to authenticate. It runs SELECT 1+1 AS result query.
Return:
Promise<boolean> Returns true if it can connect to the database, false if it cannot.
import * as expressExtension from 'express-sweet';
await expressExtension.database.Database.isConnect();
This is a class that abstracts the tables in the database.
See here for more information on the methods and properties available in your model.
protected static table: stringimport * as expressExtension from 'express-sweet';
export default class extends expressExtension.database.Model {
static get table() {
return 'user';
}
}
protected static attributes: sequelize.ModelAttributesimport * as expressExtension from 'express-sweet';
export default class extends expressExtension.database.Model {
static get attributes() {
return {
id: {
type: this.DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: this.DataTypes.STRING,
email: this.DataTypes.STRING,
password: this.DataTypes.STRING,
icon: this.DataTypes.STRING,
created: this.DataTypes.DATE,
modified: this.DataTypes.DATE
};
}
}
public static readonly DataTypes: {[key: string]: any}sequelize.DataTypes.{id: this.DataTypes.INTEGER}
public static readonly Op: {[key: string]: any}sequelize.Op.import BookModel from '../models/BookModel';
// SELECT * FROM book WHERE title = 'Beautiful' AND genre = 'Nonfiction';
BookModel.findOne({
where: {
[BookModel.Op.and]: [
{title: 'Beautiful'},
{genre: 'Nonfiction'}
]
}
});
// SELECT * FROM book WHERE title = 'Beautiful' OR title = 'Lose Yourself';
BookModel.findAll({
where: {
[BookModel.Op.or]: [
{title: 'Beautiful'},
{title: 'Lose Yourself'}
]
}
});
// DELETE FROM user WHERE name = 'Beautiful' OR name = 'Lose Yourself';
BookModel.destroy({
where: {
title: {[BookModel.Op.or]: ['Beautiful', 'Lose Yourself']}
}
});
public static readonly fn: (fn: string, ...args: unknown[]) => anyimport BookModel from '../models/BookModel';
// SELECT upper(`title`) AS `title` FROM `book` AS `book`;
const books = await BookModel.findAll({
attributes: [[BookModel.fn('upper', BookModel.col('title')), 'title']],
raw: true
});
public static readonly col: (col: string) => anypublic static readonly literal: (val: string) => anyimport BookModel from '../models/BookModel';
// SELECT `id`, `title`, (SELECT COUNT(*) FROM comment WHERE comment.bookId = book.id) AS `count` FROM `book` AS `book`;
const books = await BookModel.findAll({
attributes: [
'id',
'title',
[BookModel.literal(`(SELECT COUNT(*) FROM comment WHERE comment.bookId = book.id)`), 'count']
],
raw: true
});
public static readonly where: (attr: sequelize.AttributeType, comparator: string, logic: sequelize.LogicType) => sequelize.Utils.Whereimport BookModel from '../models/BookModel';
// SELECT `title` FROM `book` AS `book` WHERE CHAR_LENGTH(`title`) <= 10;
const books = await BookModel.findAll({
attributes: ['title'],
where: BookModel.where(
BookModel.fn('CHAR_LENGTH', BookModel.col('title')),
{[BookModel.Op.lte]: 10}
),
raw: true
});
public static readonly QueryTypes: {[key: string]: string}sequelize.query.public static readonly Transaction: (typeof sequelize.Transaction)const BookModel = require('../models/BookModel');
BookModel.Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED // "READ UNCOMMITTED"
BookModel.Transaction.ISOLATION_LEVELS.READ_COMMITTED // "READ COMMITTED"
BookModel.Transaction.ISOLATION_LEVELS.REPEATABLE_READ // "REPEATABLE READ"
BookModel.Transaction.ISOLATION_LEVELS.SERIALIZABLE // "SERIALIZABLE"
public static association()
Associate the model.
Define associations with other models such as hasOne, hasMany, belongsTo, belongsToMany.
This method is called automatically from within the mount method, so you don't have to run it yourself.
See the here for more information.
If you omit the alias (as) option, the associated name will be hasOne, singular for belongsTo, and plural for hasMany.
import * as expressExtension from 'express-sweet';
import ProfileModel from './ProfileModel';
export default class extends expressExtension.database.Model {
static association() {
// User has one profile.
this.hasOne(ProfileModel, {
foreignKey: 'userId', // profile.userId
sourceKey: 'id', // user.id
as: 'profile'
});
}
}
public static begin()
Starts a transaction and returns a transaction object to identify the running transaction.
This is an alias for the sequelize.Sequelize.transaction() method.
See here for details.
Parameters:
Return:
Promise<sequelize.Transaction> Returns a transaction object to identify the transaction being executed.
Simple transaction usage example.
import BookModel from '../models/BookModel';
let transaction;
try {
transaction = await BookModel.begin();
const book = await BookModel.create({title: 'Beautiful'}, {transaction});
await transaction.commit();
} catch {
if (transaction)
await transaction.rollback();
}
You can also use transaction options.
import BookModel from '../models/BookModel';
let transaction;
try {
transaction = await BookModel.begin({
isolationLevel: BookModel.Transaction.ISOLATION_LEVELS.REPEATABLE_READ,
type: BookModel.Transaction.TYPES.DEFERRED,
});
const book = await BookModel.findOne({where: {id: 1}}, {transaction});
book.title = 'Beautiful';
await book.save({transaction});
await transaction.commit();
// Load updated data.
await book.reload();
} catch {
if (transaction)
await transaction.rollback();
}
public static create()
Builds a new model instance and calls save on it.
See here for details.
Parameters:
Return:
Promise<Model> Returns a model that contains the data for the added record.
import BookModel from '../models/BookModel';
// INSERT INTO `book` (`id`,`title`) VALUES (DEFAULT,'Beautiful');
await BookModel.create({title: 'Beautiful'});
public static save()
Validates this instance, and if the validation passes, persists it to the database.
See here for details.
Parameters:
Return:
Promise<Model> Returns a model that contains data for manipulated records such as add and update.
import BookModel from '../models/BookModel';
// INSERT INTO `book` (`id`,`title`) VALUES (DEFAULT,'Beautiful');
const book = BookModel.build({title: 'Beautiful'});
await book.save();
// UPDATE `book` SET `title`='Lose Yourself' WHERE `id` = 1;
book.title = 'Lose Yourself';
await book.save();
public static findOne()
Search for a single instance.
Returns the first instance found, or null if none can be found.
See here for details.
Parameters:
Return:
Promise<Model|null> Returns a Model containing the first data found in the database.
import BookModel from '../models/BookModel';
// SELECT `id`, `title`, `created`, `modified` FROM `book` AS `book` LIMIT 1;
await BookModel.findOne();
public static findAll()
Search for multiple instances.
See here for details.
Parameters:
Return:
Promise<Array<Model>> Returns a model containing the data found in the database.
import BookModel from '../models/BookModel';
// SELECT `id`, `title`, `created`, `modified` FROM `book` AS `book` WHERE `book`.`title` LIKE 'Beautiful%';
await BookModel.findAll({
where: {
title: {
[BookModel.Op.like]: 'Beautiful%'
}
}
});
public static count()
Count the number of records matching the provided where clause.
See here for details.
Parameters:
Return:
Promise<number> Returns the count of records that match the condition.
import BookModel from '../models/BookModel';
// SELECT count(*) AS `count` FROM `book` AS `book`;
await BookModel.count();
public static update()
Update multiple instances that match the where options.
See here for details.
Parameters:
Return:
Promise<Array<number, number>> The first element is always the number of affected rows, while the second element is the actual affected rows (only supported in postgres with options.returning true).
import BookModel from '../models/BookModel';
// UPDATE `book` SET `title`='Lose Yourself' WHERE `id` = 1;
await BookModel.update({title: 'Lose Yourself'}, {where: {id: 1}});
public static upsert()
Insert or update a single row.
An update will be executed if a row which matches the supplied values on either the primary key or a unique key is found.
Note that the unique index must be defined in your sequelize model and not just in the table.
Otherwise you may experience a unique constraint violation, because sequelize fails to identify the row that should be updated.
See here for details.
Parameters:
Return:
Promise<Model, boolean|null> returns record and whether row was created or updated as boolean. For Postgres/SQLite dialects boolean value is always null.
import BookModel from '../models/BookModel';
// INSERT INTO `book` (`title`) VALUES (?) ON DUPLICATE KEY UPDATE `title`=VALUES(`title`);
await BookModel.upsert({title: 'Lose Yourself'});
public static destroy()
Delete multiple instances, or set their deletedAt timestamp to the current time if paranoid is enabled.
See here for details.
Parameters:
Return:
Promise<number> The number of destroyed rows.
import BookModel from '../models/BookModel';
// DELETE FROM `user` WHERE `id` = 1;
await BookModel.destroy({where: {id :1}});
public static hasOne()
Creates an association between this (the source) and the provided target.
The foreign key is added on the target.
See here for details.
Parameters:
Return:
sequelize.HasOne One-to-one association.
For one-to-one association we will use two tables as an example they are User and Profile table.
User table has one Profile table and Profile table belongs to the User table.
Here's the relation diagram for it.
HasOne put the association key in the target model.
Here User can exist without a Profile, but the vice versa is not possible.
This means, we will insert userId field to Profile model’s table.
This is a user model that defines an association in which the user has one profile.
import * as expressExtension from 'express-sweet';
import ProfileModel from './ProfileModel';
export default class extends expressExtension.database.Model {
static association() {
// User has one profile.
this.hasOne(ProfileModel, {
foreignKey: 'userId', // profile.userId
sourceKey: 'id', // user.id
as: 'profile'
});
}
}
This is an example of record search.
import UserModel from '../models/UserModel';
// SELECT
// `user`.`id`,
// `user`.`name`,
// `profile`.`id` AS `profile.id`,
// `profile`.`userId` AS `profile.userId`,
// `profile`.`address` AS `profile.address`,
// `profile`.`tel` AS `profile.tel`
// FROM
// `user` AS `user`
// LEFT OUTER JOIN `profile` AS `profile` ON `user`.`id` = `profile`.`userId`;
//
// results in: [
// {
// "id": 1,
// "name": "Robin",
// "profile": {
// "userId": 1,
// "address": "777 Brockton Avenue, Abington MA 2351",
// "tel": "202-555-0105"
// }
// }
// ]
await UserModel.findAll({
attributes: ['id', 'name'],
include: [{
association: 'profile',
attributes: ['userId', 'address', 'tel']
}]
});
public static belongsTo()
Creates an association between this (the source) and the provided target.
The foreign key is added on the source.
See here for details.
Parameters:
Return:
sequelize.BelongsTo One-to-one association.
For one-to-one association we will use two tables as an example they are User and Profile table.
User table has one Profile table and Profile table belongs to the User table.
Here's the relation diagram for it.
BelongsTo put the associations key in the source model.
Here User can exist without a Profile, but the vice versa is not possible.
This means, we will insert userId field to Profile model’s table.
This is a profile model that defines an association whose profile belongs to one user.
import * as expressExtension from 'express-sweet';
import UserModel from './UserModel';
export default class extends expressExtension.database.Model {
static association() {
// Profile belongs to one user.
this.belongsTo(UserModel, {
foreignKey: 'userId', // profile.userId,
targetKey: 'id', // user.id
as: 'user'
});
}
}
This is an example of record search.
import ProfileModel from '../models/ProfileModel';
// SELECT
// `profile`.`id`,
// `profile`.`userId`,
// `profile`.`address`,
// `profile`.`tel`,
// `user`.`id` AS `user.id`,
// `user`.`name` AS `user.name`
// FROM
// `profile` AS `profile`
// INNER JOIN `user` AS `user` ON `profile`.`userId` = `user`.`id`;
//
// results in: [
// {
// "userId": 1,
// "address": "777 Brockton Avenue, Abington MA 2351",
// "tel": "202-555-0105",
// "user": {
// "id": 1,
// "name": "Robin"
// }
// }
// ]
await ProfileModel.findAll({
attributes: ['userId', 'address', 'tel'],
include: {
association: 'user',
required: true,
attributes: ['id', 'name']
}
});
public static hasMany()
Creates a 1:m association between this (the source) and the provided target.
The foreign key is added on the target.
See here for details.
Parameters:
Return:
sequelize.HasMany One-to-many association.
For one-to-many association we will use two tables as an example they are User and Comment table.
User table has many Comment table and Comment table belongs to the User table.
Here's the relation diagram for it.
The HasMany put the association key in the target model.
Here user and comments share a one to many relationship.
Each user can make multiple comments while each comment is associated with only a single user.
This is a user model that defines an association in which the user has many comments.
import * as expressExtension from 'express-sweet';
import CommentModel from './CommentModel';
export default class extends expressExtension.database.Model {
static association() {
// User has many comments.
this.hasMany(CommentModel, {
foreignKey: 'userId', // comment.userId
sourceKey: 'id', // user.id
as: 'comments'
});
}
}
This is an example of record search.
import UserModel from '../models/UserModel';
// SELECT
// `user`.`id`,
// `user`.`name`,
// `comments`.`id` AS `comments.id`,
// `comments`.`userId` AS `comments.userId`,
// `comments`.`text` AS `comments.text`
// FROM
// `user` AS `user`
// LEFT OUTER JOIN `comment` AS `comments` ON `user`.`id` = `comments`.`userId`;
//
// results in: [
// {
// "id": 1,
// "name": "Robin",
// "comments": [
// {
// "userId": 1,
// "text": "From Robin #1"
// },
// {
// "userId": 1,
// "text": "From Robin #2"
// }
// ]
// }
// ]
await UserModel.findAll({
attributes: ['id', 'name'],
include: {
association: 'comments',
attributes: ['userId', 'text']
}
});
Using hasMany with findAll
When using hasMany association with findAll in Sequelize, you need to be aware of the raw option's behavior.
If you want to retrieve child rows as an array within the parent row, you should disable the raw option.
Enabling the raw option will result in the parent row being duplicated for each child row. This behavior is also discussed in this GitHub issue.
Example:
With raw: true:
[
{
"id": 1,
"name": "Robin",
{
"userId": 1,
"text": "From Robin #1"
}
},
{
"id": 1,
"name": "Robin",
{
"userId": 1,
"text": "From Robin #2"
}
}
]
With raw: false (default):
[
{
"id": 1,
"name": "Robin",
"comments": [
{
"userId": 1,
"text": "From Robin #1"
},
{
"userId": 1,
"text": "From Robin #2"
}
]
}
]
public static belongsToMany()
Create an N:M association with a join table.
Defining through is required.
See here for details.
Parameters:
Return:
sequelize.BelongsToMany Many-to-many association with a join table.
For many-to-many association we will use two tables as an example they are User and Book table.
User is marking down Books that he has read. Each user can mark as many books as they want, creating a many to many association between User and Books.
Books can belong to many users.
Here's the relation diagram for it.
The BlongsToMany put the association key in the target model.
Here user and book share a many to many relationship.
Each user can make multiple books, and each book can be associated with multiple users.
This is a user model that defines an association where users and books have a many-to-many relationship.
import * as expressExtension from 'express-sweet';
import BookModel from './BookModel';
export default class extends expressExtension.database.Model {
static association() {
// Users have many books, and books belong to many users.
this.belongsToMany(BookModel, {
foreignKey: 'userId', // book.userId
sourceKey: 'id', // user.id
as: 'books'
});
}
}
This is an example of record search.
import UserModel from '../models/UserModel';
// SELECT
// `user`.`id`,
// `user`.`name`,
// `books`.`id` AS `books.id`,
// `books`.`userId` AS `books.userId`,
// `books`.`title` AS `books.title`
// FROM
// `user` AS `user`
// LEFT OUTER JOIN `book` AS `books` ON `user`.`id` = `books`.`userId`;
//
// results in: [
// {
// "id": 1,
// "name": "Robin",
// "books": [
// {
// "userId": 1,
// "title": "Beautiful"
// },
// {
// "userId": 1,
// "title": "Lose Yourself"
// }
// ]
// }
// ]
await UserModel.findAll({
attributes: ['id', 'name'],
include: {
association: 'books',
attributes: ['userId', 'title']
}
});
public static query()
Raw Queries.
As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the Model.query method.
This is an alias for the sequelize.Sequelize.query() method.
See here for details.
Parameters:
Return:
Promise<any> By default, the function will return two arguments: an array of results, and a metadata object, containing number of affected rows etc. If you are running a type of query where you don't need the metadata, for example a SELECT query, you can pass in a query type to make sequelize format the results.
// By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc).
// Note that since this is a raw query, the metadata are dialect specific.
const [results, metadata] = await BookModel.query("UPDATE book SET title = 'Lose Yourself' WHERE id = 1");
// In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:
// We didn't need to destructure the result here - the results were returned directly
await BookModel.query("SELECT * FROM book", {type: BookModel.QueryTypes.SELECT});
EXPRESS SWEET has built-in passport authentication middleware for user authentication by username and password. You can immediately start user authentication in your application using the authentication configuration file and the authentication service module.
For more information about passport, click here.
NOTE: If an unauthenticated user makes a request to a URL that allows access only if authenticated, the user will be redirected to the page specified by failure_redirect.
If that access is asynchronous, a 401 error is returned.
The user authentication configuration is defined in the config/authentication.js file.
Click here to download a sample ESM configuration and here to download a sample CJS configuration.
enabled: boolean
Set to true to enable user authentication using Passport middleware.
User authentication is enabled (true) by default.
session_store: 'memory'|'redis'
The session store instance, defaults to a new MemoryStore(memory) instance.
cookie_name?: string|undefined
The name of the session ID cookie to set in the response (and read from in the request).
The default value is connect.sid.
cookie_secure?: boolean|undefined
Specifies the boolean value for the Secure Set-Cookie attribute.
The default is true, which sets the Secure attribute on the cookie.
cookie_httpOnly?: boolean|undefined
Specifies the boolean value for the HttpOnly Set-Cookie attribute.
Defaults to true, which sets the HttpOnly attribute on the cookie.
redis_host?: string|undefined
If the session is stored in redis, this field is required and should be set to the hostname of the Redis server.
For example, to connect to redis on localhost on port 6379, set redis://localhost:6379.
To connect to a different host or port, use a connection string in the format redis[s]://[[username][:password]@][host][:port][/db-number].
For example, redis://alice:foobared@awesome.redis.server:6380.
username: string
The login username field name used for authentication.
This should be set to the same value as the user field name in the POST body sent to the server and the user example name in the login user table.
password: string
The login password field name used for authentication.
This should be set to the same value as the password field name in the POST body sent to the server and the password column name in the login user table.
success_redirect: string
The URL to redirect to after successful authentication.
The default is the root URL (/).
failure_redirect: string|((req: express.Request, res: express.Response) => string)
Specify the URL to redirect after logging out, or the URL to redirect when the logoff user requests a URL that only the logged-in user can access.
This usually specifies the URL of the login page.
The default is /login.
// Set the URL to redirect to in case of login failure as a string.
failure_redirect: '/login',
// Dynamically set the url to redirect to on login failure.
failure_redirect: (req, res) => {
// If the role stored in the cookie is admin, redirect to the admin login screen.
return req.cookies.role === 'admin' ? '/adminlogin' : 'login';
},
authenticate_user: (username: string, password: string, req: express.Request): Promise<object|null>
This hook is called when authenticating a user.
Please find the user information that owns the credentials based on the user name and password you received and return it.
If the user who owns the credentials cannot be found, return null.
NOTE: Note that the user information must include an ID value that can identify the user.
The following example uses the user model to find the user who owns the credentials based on the username and password.
authenticate_user: async (username, password, req) => {
const UserModel = require('../models/UserModel');
return UserModel.findOne({
where: {
email: username,
password
},
raw: true
});
}
subscribe_user: (id: number): Promise<object>
This hook is called when user authentication is successful.
Please search and return the authenticated user information to be set in the session based on the user ID of the parameter.
The returned data will be set in the req.user property and the view's session variable.
The following example uses the user model to return the user information that owns the credentials based on the authenticated user's id.
subscribe_user: async (id) => {
const UserModel = require('../models/UserModel');
return UserModel.findOne({
where: {
id
},
raw: true
});
}
allow_unauthenticated: (string|RegExp)[]
By default, it requires an authenticated user for all requests.
You can use the allow_unauthenticated option to disable this behavior on certain requests.
For example, if you don’t want to authenticate all requests that contain api in the URL, set allow_unauthenticated as follows.
allow_unauthenticated: ['/api']
You can also use regular expressions.
allow_unauthenticated: [/^\/api/]
expiration: number
Specifies the time, in milliseconds, before the session expires.
The default is 24 hours (86400000 milliseconds).
To authenticate the request, call services/Authentication.authenticate() method.
This method returns true if the authentication succeeds and false if it fails.
If authentication succeeds, the next handler will be invoked and the req.user property will be set to the authenticated user.
The post-authentication logic depends on whether the login request is received asynchronously or synchronously, so an example of both logics is shown here.
Create a user table to authenticate.
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(100) NOT NULL,
`icon` varchar(768) NOT NULL DEFAULT MD5(RAND()),
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `ukUserEmail` (`email`),
UNIQUE KEY `ukUserIcon`(`icon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Set the email address and password columns of the user table to be used for authentication in the config file config/authentication.js.
/**
* Authentication user ID field name, defaults to `username`.
* @type {string}
*/
username: 'email',
/**
* Authentication password field name, defaults to `password`.
* @type {string}
*/
password: 'password'
For asynchronous requests.
Returns the authentication result and executes subsequent processing on the front end.
When the form submits, it sends a login request (/api/users/login), and the routes/api/user.js router receives the request, authenticates it based on the username and password, and returns the result.
After that, the front end checks the authentication result returned from the router and redirects to / if the authentication result is successful (true).
If you redirect to /, the page will automatically switch to the URL specified in success_redirect of config/authentication.js.
import {Router} from 'express';
import * as expressExtension from 'express-sweet';
const router = Router();
router.post('/login', async (req, res, next) => {
const isAuthenticated = await expressExtension.services.Authentication.authenticate(req, res, next);
res.json(isAuthenticated);
});
export default router;
Below is an example of HTML.
<form id="form">
<label>Email</label><input type="email" name="email" required autofocus>
<label class="form-label">Password</label><input type="password" name="password" required>
<button type="submit">Login</button>
</form>
<script>
const form = document.querySelector('#form');
form.addEventListener('submit', async event => {
event.preventDefault();
const res = await fetch('/api/users/login', {method: 'POST', body: new FormData(form)});
const isAuthenticated = await res.json();
if (!isAuthenticated)
return void alert('The user name or password is incorrect.');
location.href = '/';
});
</script>
For sync request.
If the authentication is successful, redirect to the page with the URL specified by success_redirect in config/authentication.js.
If it fails, redirect to the page with the URL specified by failure_redirect in config/authentication.js.
import {Router} from 'express';
import * as expressExtension from 'express-sweet';
const router = Router();
router.post('/login', async (req, res, next) => {
const isAuthenticated = await expressExtension.services.Authentication.authenticate(req, res, next);
if (isAuthenticated)
await expressExtension.services.Authentication.successRedirect(res);
else
await expressExtension.services.Authentication.failureRedirect(req, res);
});
export default router;
Below is an example of HTML.
<form method="post" action="/api/users/login">
<label>Email</label><input type="email" name="email" required autofocus>
<label class="form-label">Password</label><input type="password" name="password" required>
<button type="submit">Login</button>
</form>
If you want to log out the user, call the services/Authentication.logout() method.
Invoking logout() will remove the req.user property and clear the login session (if any).
import {Router} from 'express';
import * as expressExtension from 'express-sweet';
const router = Router();
router.get('/logout', (req, res) => {
expressExtension.services.Authentication.logout(req);
res.redirect('/');
});
export default router;
Below is an example of HTML.
<a href="/api/users/logout">Logout</a>
All changes can be found here.
Takuya Motoshima
[2.0.4] - 2025/1/14
<!-- ## [2.0.4] - Unreleased -->exceptions to errors.hasMany association with the findAll method.findObjectInArray Handlebars helper: This helper is added to the view, allowing you to find an object in an array based on a specified field name and value.
{{!--
items is an array of objects: [{id: 123, name: 'Item A'}, {id: 456, name: 'Item B'}]
This code will output: "Item A"
--}}
{{#each items}}
{{#if (eq id 123)}}
{{lookup (findObjectInArray ../items 'id' id) 'name'}}
{{/if}}
{{/each}}
FAQs
EXPRESS SWEET is a powerful Express.js extension that streamlines your workflow and boosts productivity with a suite of utilities and enhancements.
The npm package express-sweet receives a total of 64 weekly downloads. As such, express-sweet popularity was classified as not popular.
We found that express-sweet demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Company News
Socket is joining TC54 to help develop standards for software supply chain security, contributing to the evolution of SBOMs, CycloneDX, and Package URL specifications.
Security News
PyPI now allows maintainers to archive projects, improving security and helping users make informed decisions about their dependencies.
Research
Security News
Malicious npm package postcss-optimizer delivers BeaverTail malware, targeting developer systems; similarities to past campaigns suggest a North Korean connection.