EXPRESS SWEET

EXPRESS SWEET is a powerful Express.js extension that streamlines your workflow and boosts productivity with a suite of utilities and enhancements.

• EXPRESS SWEET
  • EXPRESS SWEET Generator
  • API
    • Environment variable
    • Base configuration
    • Routing
      • Basic Routing
      • Nested Routing
      • Default Routing
      • Routing Methods
    • View
      • Configuration
      • Syntax
      • Layout
      • Array Helper
      • Comparison Helper
      • HTML Helper
      • Object Helper
      • String Helper
      • Date Helper
      • Number Helper
      • Math Helper
    • Model
      • Configuration
      • Accessing Model
      • Creating Model
      • Database class extends sequelize.Sequelize
        • Instance Methods
      • Model Class extends sequelize.Model
        • Class Properties
        • Class Methods
    • Authentication
      • Configuration
      • Login Procedure
      • Logout Procedure
  • Release Notes
  • Author
  • License

EXPRESS SWEET Generator

Use the application generator tool, express-sweet-generator, to quickly create an application skeleton.

1. Install and launch the application generator as a global npm package.

   1npm install -g express-sweet-generator

2. Display the command options with the -h option.

   1express-sweet -h
   2
   3Usage: express-sweet [options] [dir]
   4
   5Options:
   6
   7        --version      output the version number
   8    -o, --output <output>  add output <module> support (esm|cjs) (defaults to cjs)
   9    -p, --port <port>  application listening port (default: 3000)
   10    -f, --force        force on non-empty directory
   11    -h, --help         output usage information

3. For example, the following creates an Express app named myapp. The app will be created in a folder named myapp in the current working directory.

   1express-sweet -o esm myapp

4. Then install dependencies.

   1cd myapp/
   2npm install

5. The skeleton uses a DB. Please create a DB with the following SQL.

   1CREATE DATABASE IF NOT EXISTS `sample_db` DEFAULT CHARACTER SET utf8mb4;
   2
   3USE `sample_db`;
   4
   5CREATE TABLE `user` (
   6    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   7    `name` varchar(30) NOT NULL,
   8    `email` varchar(255) NOT NULL,
   9    `password` varchar(100) NOT NULL,
   10    `icon` varchar(768) NOT NULL DEFAULT MD5(RAND()),
   11    `created` datetime NOT NULL DEFAULT current_timestamp(),
   12    `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   13    PRIMARY KEY (`id`),
   14    UNIQUE KEY `ukUserEmail` (`email`),
   15    UNIQUE KEY `ukUserIcon`(`icon`)
   16) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   17
   18CREATE TABLE `profile` (
   19    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   20    `userId` int(10) unsigned NOT NULL,
   21    `address` varchar(255) NOT NULL,
   22    `tel` varchar(14) NOT NULL,
   23    `created` datetime NOT NULL DEFAULT current_timestamp(),
   24    `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   25    PRIMARY KEY (`id`),
   26    UNIQUE KEY `ukProfileUserId` (`userId`),
   27    CONSTRAINT `fkProfileUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
   28) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   29
   30CREATE TABLE `comment` (
   31    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   32    `userId` int(10) unsigned NOT NULL,
   33    `text` text NOT NULL,
   34    `created` datetime NOT NULL DEFAULT current_timestamp(),
   35    `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   36    PRIMARY KEY (`id`),
   37    CONSTRAINT `fkCommentUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
   38) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   39
   40CREATE TABLE `book` (
   41    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   42    `userId` int(10) unsigned NOT NULL,
   43    `title` text NOT NULL,
   44    `created` datetime NOT NULL DEFAULT current_timestamp(),
   45    `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   46    PRIMARY KEY (`id`),
   47    UNIQUE KEY `ukBookTitle` (`userId`, `title`(255)),
   48    CONSTRAINT `fkBookUser` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
   49) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   50
   51INSERT INTO `user` (`id`, `email`, `password`, `name`, `icon`) VALUES
   52    (1, 'robin@example.com', 'password', 'Robin', '/upload/1.png'),
   53    (2, 'taylor@example.com', 'password', 'Taylor', '/upload/2.png');
   54INSERT INTO `profile` (`userId`, `address`, `tel`) VALUES
   55    (1, '777 Brockton Avenue, Abington MA 2351', '202-555-0105'),
   56    (2, '30 Memorial Drive, Avon MA 2322', '');
   57INSERT INTO `comment` (`userId`, `text`) VALUES
   58    (1, 'From Robin #1'),
   59    (1, 'From Robin #2'),
   60    (2, 'From Taylor #1');
   61INSERT INTO `book` (`userId`, `title`) VALUES
   62    (1, 'Beautiful'),
   63    (1, 'Lose Yourself'),
   64    (2, 'When Im Gone');

6. Set the DB connection method in config/database.js. For details, please refer to here.

   1export default {
   2    development: {
   3        username: 'root',
   4        password: 'password',
   5        database: 'sample_db',
   6        host: 'localhost',
   7        dialect: 'mariadb'
   8    },
   9    test: {
   10        username: 'root',
   11        password: 'password',
   12        database: 'sample_db',
   13        host: 'localhost',
   14        dialect: 'mariadb'
   15    },
   16    production: {
   17        username: 'root',
   18        password: 'password',
   19        database: 'sample_db',
   20        host: 'localhost',
   21        dialect: 'mariadb'
   22    }
   23}

7. The DB to be accessed can be defined for each environment. Specify the environment in the .env file

   1NODE_ENV=development

8. Run the application with the following command.

   1npm start

9. Then, load http://localhost:3000/ in your browser to access the app.
   The generated app has the following directory structure:

   1.
   2├── .env
   3├── app.js
   4├── ecosystem.config.js
   5├── nginx.sample.conf
   6├── package.json
   7├── bin
   8│   └── www
   9├── client
   10│   ├── package.json
   11│   ├── webpack.config.js
   12│   └── src
   13├── config
   14│   ├── authentication.js
   15│   ├── config.js
   16│   ├── database.js
   17│   └── view.js
   18├── errors
   19│   └── UserNotFound.js
   20├── models
   21│   ├── BookModel.js
   22│   ├── CommentModel.js
   23│   ├── ProfileModel.js
   24│   └── UserModel.js
   25├── public
   26│   ├── build
   27│   └── upload
   28├── routes
   29│   ├── login.js
   30│   ├── users.js
   31│   └── api
   32│       └── users.js
   33├── shared
   34│   ├── CustomValidation.js
   35│   └── empty.js
   36└── views
   37    ├── edit-personal.hbs
   38    ├── error.hbs
   39    ├── login.hbs
   40    ├── personal.hbs
   41    ├── users.hbs
   42    ├── layout
   43    │   └── default.hbs
   44    └── partials
   45        └── .gitkeep

API

Environment variable

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.

1if (process.env.NODE_ENV === 'development')
2  ;

Base configuration

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".

  1import {Router} from 'express';
  2
  3const router = Router();
  4router.get('/', (req, res) => {
  5    res.send('Hello World');
  6});
  7export 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.

  1rewrite_base_url: baseUrl => {
  2    return `${baseUrl}/admin`;
  3}

• 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).

  1is_ajax: req => {
  2    // If the request URL begins with /api, it is assumed to be Ajax.
  3    return /^\/api/.test(req.path);
  4    // return !!req.xhr;
  5}

• 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();)

  1hook_handle_error: (error, req, res, next) => {
  2    if (error.status === 404)
  3        // If the URL cannot be found, a 404 error screen (views/error/404.hbs) is displayed.
  4        res.render('error/404');
  5    else
  6        // For other errors, unknown error screen (views/error/500.hbs) is displayed.
  7        res.render('error/500');
  8},

Routing

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.

• app is an instance of express.
• METHOD is an HTTP request method, in lowercase.
• PATH is a path on the server.
• HANDLER is the function executed when the route is matched.

1import express from 'express';
2
3const app = express();
4app.METHOD(PATH, HANDLER)

Basic Routing

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.

1import {Router} from 'express';
2
3const router = Router();
4router.get('/', (req, res) => {
5    res.send('Hello World');
6});
7export default router;

Nested Routing

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.

1import {Router} from 'express';
2
3const router = Router();
4router.get('/', (req, res) => {
5    res.send('Hello World');
6});
7export default router;

Default Routing

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:

1default_router: '/blog'

Where blog is the name of the router module you want used.
Next, create the routes/blog.js module.

1import {Router} from 'express';
2
3const router = Router();
4router.get('/', (req, res) => {
5    res.send('Hello World');
6});
7export default router;

Now when you request the root URL (/), you will see "Hello World".

Routing Methods

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.

• checkout
• copy
• delete
• get
• head
• lock
• merge
• mkactivity
• mkcol
• move
• m-search
• notify
• options
• patch
• post
• purge
• put
• report
• search
• subscribe
• trace
• unlock
• unsubscribe

View

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.

Configuration

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: string
  The directory path where the view will be placed.
  The default is the views/ directory directly under the application root.
• partials_dir: string
  The directory path where the reusable templates are located.
  The default is the views/partials/ directory directly under the application root.
  Reusable templates are automatically loaded when you launch the EXPRESS SWEET application and can be called from other templates.
• layouts_dir: string
  The directory path where the reusable base template is located.
  The default is the views/layout/ directory directly under the application root.
• default_layout: string
  The path to the layout file used by default.
  The default is the views/layout/default.* File directly under the application root.
  The extension of the default layout file is replaced with the extension specified by extension.
• extension: string
  Extension for templates and partials files.
  The default is .hbs.
• beforeRender: (req: express.Request, res: express.Response) => Promise<void>|void
  Hook function just before the view is rendered.
  For example, you can set your own local variables that can be used within the view.

  1beforeRender: (req, res) => {
  2    res.locals.extra = 'Extra';
  3}

Syntax

To mark where layout should insert page.

1{{{body}}}

To declare a block placeholder in layout.

1{{{block "script"}}}

To define block content in a page.

1{{#contentFor "script"}}
2    CONTENT HERE
3{{/contentFor}}

Layout

There are three ways to use a layout, listed in precedence order.

1. Declarative within a page. Use handlebars comment.

   1{{!< LAYOUT}}

   Layout file resolution.

   1If path starts with '.'
   2    LAYOUT is relative to template
   3Else If `layoutsDir` is set
   4    LAYOUT is relative to `layoutsDir`
   5Else
   6    LAYOUT from path.resolve(dirname(template), LAYOUT)

2. 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.

   1res.render('veggies', {
   2    title: 'My favorite veggies',
   3    veggies: veggies,
   4    layout: 'layout/veggie'
   5});

   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.

   1res.render('veggies', {
   2    title: 'My favorite veggies',
   3    veggies: veggies,
   4    layout: null // render without using a layout template
   5});

   Layout file resolution.

   1If path starts with '.'
   2    layout is relative to template
   3Else If `layoutsDir` is set
   4    layout is relative to `layoutsDir`
   5Else
   6    layout from path.resolve(viewsDir, layout)

3. 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.

Array Helper

• findObjectInArray()
  Finds an object in an array based on the specified field name and value.

  Parameters:

  • array: Array<{[key: string]: any}>
    The array of objects to search.
  • fieldName: string
    The name of the field to search.
  • fieldValue: any
    fieldValue The value to search for.

  Return:
  object|null The first object in the array that matches the criteria, or null if no match is found.

  1{{!-- 
  2  items is an array of objects: [{id: 123, name: 'Item A'}, {id: 456, name: 'Item B'}]
  3  This code will output: "Item A" 
  4--}}
  5{{#each items}}
  6  {{#if (eq id 123)}}
  7    {{lookup (findObjectInArray ../items 'id' id) 'name'}}
  8  {{/if}}
  9{{/each}}

Comparison Helper

• eq()
  Determine whether or not two values are equal (===).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the value and type are the same, false if they are different.

  1{{eq val1 val2}}
  2{{#if (eqw val1 val2)}}...{{/if}}

• eqw()
  Determine whether or not two values are equal (==) i.e. weak checking.

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the values are the same, false if they are different.

  1{{eqw val1 val2}}
  2{{#if (eqw val1 val2)}}...{{/if}}

• neq()
  Determine whether or not two values are not equal (!==).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the value and type are different, false if they are the same.

  1{{neq val1 val2}}
  2{{#if (neq val1 val2)}}...{{/if}}

• neqw()
  Determine whether or not two values are not equal (!=) weak checking.

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the values are different, false if they are the same.

  1{{neqw val1 val2}}
  2{{#if (neqw val1 val2)}}...{{/if}}

• lt()
  Check for less than condition (a < b).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the first value is less than the second value, false otherwise.

  1{{lt val1 val2}}
  2{{#if (lt val1 val2)}}...{{/if}}

• lte()
  Check for less than or equals condition (a <= b).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the first value is less than or equal to the second value, false otherwise.

  1{{lte val1 val2}}
  2{{#if (lte val1 val2)}}...{{/if}}

• gt()
  Check for greater than condition (a > b).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the first value exceeds the second value, false otherwise.

  1{{gt val1 val2}}
  2{{#if (gt val1 val2)}}...{{/if}}

• gte()
  Check for greater than or equals condition (a >= b).

  Parameters:

  • val1: any
    First value to be compared with second.
  • val2: any
    Second value to be compared with first.

  Return:
  boolean Returns true if the first value is greater than or equal to the second value, false otherwise.

  1{{gte val1 val2}}
  2{{#if (gte val1 val2)}}...{{/if}}

• not()
  Logical NOT of any expression.

  Parameters:

  • val: any
    Any expression.

  Return:
  boolean Returns the logical negation of the value.

  1{{not val}}
  2{{#if (not (eq val1 val2))}}...{{/if}}

• ifx()
  Helper to imitate the ternary ?: conditional operator.

  Parameters:

  • condition: boolean
    Satisfying condition for getting first value. Either true of false.
  • val1: any
    First value to be displayed as result.
  • val2: any
    Second value to be displayed as result. Defaults to an empty string.

  Return:
  any Returns the result of the ternary operator.

  1{{ifx true val1 val2}}
  2{{ifx false val1 val2}}
  3{{ifx (eq val1 val2) val3 val4}}
  4{{ifx (not (eq val1 val2)) val3 val4}}
  5{{ifx true val}}
  6{{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:

  • value: any
    Character strings, arrays, objects, etc. to be checked.

  Return:
  boolean Returns true if the value is empty, false otherwise.

  1{{empty val}}
  2{{#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:

  • val: any
    Character strings, arrays, objects, etc. to be checked.

  Return:
  boolean Returns true if the value is not empty, false otherwise.

  1{{notEmpty val}}
  2{{#if (notEmpty val)}}...{{/if}}

• count()
  Determine the length of an array.

  Parameters:

  • items: any[]
    Array whose elements to be counted.

  Return:
  number|false Returns the length of the array if the value is an array, false if the value is not an array.

  1{{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:

  • ...params: any[]
    Any number of boolean parameters.

  Return:
  boolean Returns the result of the logical product.

  1{{and val1 val2}}
  2{{#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:

  • ...params: any[]
    Any number of boolean parameters.

  Return:
  boolean Returns the result of the OR.

  1{{or val1 val2}}
  2{{#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:

  • ...params: any[]
    Any number of parameters.

  Return:
  any Returns the first non-false element of the parameter.

  1{{coalesce val1 val2}}

• includes()
  Returns boolean if the array contains the element strictly or non-strictly.

  Parameters:

  • items: any[]
    The array.
  • val: any
    The value to be checked for existence in the array.
  • strict: boolean
    false for non-strict checking. true by default.

  Return:
  boolean Returns true if the array contains the specified value, false otherwise.

  1{{includes [1, 2, 3] 2}}
  2{{includes [1, 2, 3] 2 true}}
  3{{#if (includes [1, 2, 3] 2)}}...{{/if}}
  4{{ifx (includes [1, 2, 3] 2) true false}}

• regexMatch()
  Returns true if the given str matches the given regex.

  Parameters:

  • val: string
    The string against which to match the regular expression.
  • pattern: string
    The text of the regular expression.
  • flags?: string
    Regular expression flags, such as global and case-insensitive searches. The default is none (undefined).

  Return:
  boolean true if there is a match between the regular expression and the string str. Otherwise, false.

  1{{regexMatch 'Hello, world!' 'Hello'}}
  2{{regexMatch 'Hello, world!' 'Hello' 'i'}}
  3{{#if (regexMatch 'Hello, world!' 'Hello')}}...{{/if}}

HTML Helper

• cacheBusting()
  Returns the Assets path containing the file update time parameter.

  Parameters:

  • filePath: string
    Paths of Assets files such as CSS and JS in public directories.
  • baseUrl: string
    Application Origin URL. The default is none (undefined).

  Return:
  string Returns the Assets file path with the update date and time parameters.

  1{{!-- results in: example.com/assets/style.css?1620526340463 --}}
  2{{cacheBusting '/assets/style.css' '//example.com'}}

• stripTags()
  Removes HTML tags from a string, optionally allowing specific tags.

  Parameters:

  • str: string
    The string to remove HTML tags from.
  • allowedTags: string|string[]
    An array of allowed HTML tags. Default is an empty array.
  • replacement: string
    The string to replace HTML tags with. Default is blank.

  Return:
  string The string with HTML tags removed.

  1{{!-- results in: lorem ipsum dolor sit amet --}}
  2{{{stripTags '<a href="https://example.com">lorem ipsum <strong>dolor</strong> <em>sit</em> amet</a>'}}}
  3
  4{{!-- results in: lorem ipsum <strong>dolor</strong> sit amet --}}
  5{{{stripTags '<a href="https://example.com">lorem ipsum <strong>dolor</strong> <em>sit</em> amet</a>' '<strong>' ''}}}
  6
  7{{!-- results in: 🍩lorem ipsum 🍩dolor🍩 🍩sit🍩 amet🍩 --}}
  8{{{stripTags '<a href="https://example.com">lorem ipsum <strong>dolor</strong> <em>sit</em> amet</a>' [] '🍩'}}}

Object Helper

• jsonStringify()
  Stringify an object using JSON.stringify.

  Parameters:

  • val: any
    The value to convert to a JSON string.
  • indent: number
    The number of space characters to use as whitespace.

  Return:
  string A JSON string representing the given value, or undefined.

  1{{jsonStringify val}}

• jsonParse()
  Parses the given string using JSON.parse.

  Parameters:

  • val: any
    Object to stringify.

  Return:
  any JavaScript value or object described by a string.

  1{{jsonParse val}}

String Helper

• replace()
  Returns a new string with some or all matches of a pattern replaced by a replacement.

  Parameters:

  • val: string
    String.
  • find: string
    The string to be replaced.
  • replace: string
    The string to replace.

  Return:
  string Character string after replacement.

  1{{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:

  • val: string
    String.
  • separator: string
    A character that delimits the substrings in this string. Default is a comma.

  Return:
  string[] An Array of strings, split at each point where the separator occurs in the given string. The default is a comma.

  1{{split "a,b,c" ","}}
  2{{#each (split list ',')}}<div>{{this}}</div>{{/each}}

• formatBytes()
  Convert bytes to just the right units(KB, MB, GB, TB, PB, EB, ZB, YB).

  Parameters:

  • bytes: number
    Bytes.
  • decimals?: number
    Number of decimal places to display. Default is 0.

  Return:
  string Returns a value with units.

  1{{!-- results in: 1 KB --}}
  2{{formatBytes 1024}}
  3
  4{{!-- results in: 1.21 KB --}}
  5{{formatBytes 1234 2}}
  6
  7{{!-- results in: 1.205 KB --}}
  8{{formatBytes 1234 3}}
  9
  10{{!-- results in: 0 Bytes --}}
  11{{formatBytes 0}}

Date Helper

• formatDate()
  Use moment to format the date.

  Parameters:

  • format: string
    A format string based on moment.
  • date: string
    Date string to format.
  • locale: string|string[]
    Language or language-country locale string (or array of strings) available in https://github.com/moment/moment/tree/develop/locale.

  Return:
  string Returns formatted date.

  1{{!-- results in: 2021/10/24 --}}
  2{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z"}}
  3
  4{{!-- results in: 2021/10/24 --}}
  5{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z" 'jp'}}
  6
  7{{!-- results in: 2021/10/24 --}}
  8{{formatDate 'YYYY/MM/DD' "2021-10-24T02:13:06.610Z" 'es'}}

Number Helper

• number2locale()
  Returns the language-sensitive representation of a number as a string.

  Parameters:

  • val: number|string
    Target number or numeric string.
  • locales: string|undefined
    A string with a BCP 47 language tag, or an array of such strings. Corresponds to the 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.

  1{{!-- results in: 123,456.789 --}}
  2{{number2locale 123456.789}}
  3
  4{{!-- German uses comma as decimal separator and period for thousands. --}}
  5{{!-- results in: 123.456,789 --}}
  6{{number2locale 123456.789 'de-DE'}}

Math Helper

• add()
  Calculates the sum of two numbers.

  Parameters:

  • val1: number|string
    The first number.
  • val2: number|string
    The second number.

  Return:
  number

  1{{add 1 2}}

• sub()
  Calculates the difference of the given values.

  Parameters:

  • val1: number|string
    The first number.
  • val2: number|string
    The second number.

  Return:
  number

  1{{sub 5 2}}

• multiply()
  Calculate the multiplication of the given values.

  Parameters:

  • val1: number|string
    The first number.
  • val2: number|string
    The second number.

  Return:
  number

  1{{multiply 5 2}}

• divide()
  Compute the division of the given values.

  Parameters:

  • val1: number|string
    The first number.
  • val2: number|string
    The second number.

  Return:
  number

  1{{divide 10 2}}

• ceil()
  Round up the value.

  Parameters:

  • val: number|string
    Number to be rounded to nearest greater integer.

  Return:
  number

  1{{ceil 5.6}}

• floor()
  Rounds down a number.

  Parameters:

  • val: number|string
    Number to be rounded to nearest lower integer.

  Return:
  number

  1{{floor 5.6}}

• abs()
  Returns an absolute value.

  Parameters:

  • val: number|string
    Number to perform absolute value operation on.

  Return:
  number

  1{{abs -5.6}}

Model

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.

• Automatic database connection.
• Basic CRUD methods.
• And more.

This class provides a solid base from which to build your own models, allowing you to rapidly build out your application’s model layer.

Configuration

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: string
  The username which is used to authenticate against the database.
• password: string|null
  The password which is used to authenticate against the database.
  The default is no password (null).
• database: string
  The name of the database.
• host: string
  The host of the relational database.
• port: number|null
  The port of the relational database.
  The default is automatic selection (null).
• dialect: string
  The dialect of the database you are connecting to.
  One of mariadb, mysql, postgres, sqlite and mssql.
• logging: boolean|(...message: any[]) => void
  Set true to output the executed query etc. to the log.
  The default is no log output (false).
• timezone: string
  Time zone dedicated to writing to the database.
  For example, for the Japanese time zone, do the following.

  1timezone: '+09:00'

Accessing Model

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.

1import BookModel from '../models/BookModel';
2
3// INSERT INTO book (title) VALUES ('Beautiful')
4await BookModel.create({title: 'Beautiful'});
5
6// SELECT * FROM book
7await BookModel.findAll();
8
9// UPDATE book SET title = 'Beautiful' WHERE id= 1
10await BookModel.update({title: 'Beautiful'}, {where: {id: 1}});
11
12// DELETE FROM book WHERE id= 1
13await BookModel.destroy({where: {id: 1}});

Creating Model

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.

1import * as expressExtension from 'express-sweet';
2
3export default class extends expressExtension.database.Model {
4  static get table() {
5    return 'user';
6  }
7
8  static get attributes() {
9    return {
10      id: {
11        type: this.DataTypes.INTEGER,
12        primaryKey: true,
13        autoIncrement: true
14      },
15      name: this.DataTypes.STRING,
16      email: this.DataTypes.STRING,
17      password: this.DataTypes.STRING,
18      icon: this.DataTypes.STRING,
19      created: this.DataTypes.DATE,
20      modified: this.DataTypes.DATE
21    };
22  }
23}

Database class extends sequelize.Sequelize

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.

Instance Methods

• 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.

  1import * as expressExtension from 'express-sweet';
  2
  3await expressExtension.database.Database.isConnect();

Model Class extends sequelize.Model

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.

Class Properties

• protected static table: string
  The name of the table that the model accesses. This member must be defined in a subclass.

  1import * as expressExtension from 'express-sweet';
  2
  3export default class extends expressExtension.database.Model {
  4    static get table() {
  5        return 'user';
  6    }
  7}

• protected static attributes: sequelize.ModelAttributes
  List of columns in the table accessed by this model. This member must be defined in a subclass.

  1import * as expressExtension from 'express-sweet';
  2
  3export default class extends expressExtension.database.Model {
  4    static get attributes() {
  5        return {
  6            id: {
  7                type: this.DataTypes.INTEGER,
  8                primaryKey: true,
  9                autoIncrement: true
  10            },
  11            name: this.DataTypes.STRING,
  12            email: this.DataTypes.STRING,
  13            password: this.DataTypes.STRING,
  14            icon: this.DataTypes.STRING,
  15            created: this.DataTypes.DATE,
  16            modified: this.DataTypes.DATE
  17        };
  18    }
  19}

• public static readonly DataTypes: {[key: string]: any}
  A convenience class holding commonly used data types.
  This is an alias for sequelize.DataTypes.
  See here for details.

  1{id: this.DataTypes.INTEGER}

• public static readonly Op: {[key: string]: any}
  Operator symbols to be used when querying data.
  This is an alias for sequelize.Op.
  See here for details.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT * FROM book WHERE title = 'Beautiful' AND genre = 'Nonfiction';
  4BookModel.findOne({
  5    where: {
  6        [BookModel.Op.and]: [
  7            {title: 'Beautiful'},
  8            {genre: 'Nonfiction'}
  9        ]
  10    }
  11});
  12
  13// SELECT * FROM book WHERE title = 'Beautiful' OR title = 'Lose Yourself';
  14BookModel.findAll({
  15    where: {
  16        [BookModel.Op.or]: [
  17            {title: 'Beautiful'},
  18            {title: 'Lose Yourself'}
  19        ]
  20    }
  21});
  22
  23// DELETE FROM user WHERE name = 'Beautiful' OR name = 'Lose Yourself';
  24BookModel.destroy({
  25    where: {
  26        title: {[BookModel.Op.or]: ['Beautiful', 'Lose Yourself']}
  27    }
  28});

• public static readonly fn: (fn: string, ...args: unknown[]) => any
  Creates an object representing a database function.
  This can be used in search queries, both in where and order parts, and as default values in column definitions.
  If you want to refer to columns in your function, you should use sequelize.col, so that the columns are properly interpreted as columns and not a strings.
  See here for details.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT upper(`title`) AS `title` FROM `book` AS `book`;
  4const books = await BookModel.findAll({
  5    attributes: [[BookModel.fn('upper', BookModel.col('title')), 'title']],
  6    raw: true
  7});

• public static readonly col: (col: string) => any
  Creates an object which represents a column in the DB, this allows referencing another column in your query.
  This is often useful in conjunction with sequelize.fn, since raw string arguments to fn will be escaped.
  See here for details.
• public static readonly literal: (val: string) => any
  Creates an object representing a literal, i.e. something that will not be escaped.
  See here for details.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT `id`, `title`, (SELECT COUNT(*) FROM comment WHERE comment.bookId = book.id) AS `count` FROM `book` AS `book`;
  4const books = await BookModel.findAll({
  5    attributes: [
  6        'id',
  7        'title',
  8        [BookModel.literal(`(SELECT COUNT(*) FROM comment WHERE comment.bookId = book.id)`), 'count']
  9    ],
  10    raw: true
  11});

• public static readonly where: (attr: sequelize.AttributeType, comparator: string, logic: sequelize.LogicType) => sequelize.Utils.Where
  A way of specifying attr = condition.
  The attr can either be an object taken from Model.rawAttributes (for example Model.rawAttributes.id or Model.rawAttributes.name).
  The attribute should be defined in your model definition.
  The attribute can also be an object from one of the sequelize utility functions (sequelize.fn, sequelize.col etc.)
  For string attributes, use the regular {where: {attr: something}} syntax.
  If you don't want your string to be escaped, use sequelize.literal. See here for details.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT `title` FROM `book` AS `book` WHERE CHAR_LENGTH(`title`) <= 10;
  4const books = await BookModel.findAll({
  5    attributes: ['title'],
  6    where: BookModel.where(
  7        BookModel.fn('CHAR_LENGTH', BookModel.col('title')),
  8        {[BookModel.Op.lte]: 10}
  9    ),
  10    raw: true
  11});

• public static readonly QueryTypes: {[key: string]: string}
  An enum of query types used by sequelize.query.
  See here for details.
• public static readonly Transaction: (typeof sequelize.Transaction)
  Reference to sequelize.Transaction.
  This includes properties such as isolation level enums used with the transaction option.

  1const BookModel = require('../models/BookModel');
  2BookModel.Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED // "READ UNCOMMITTED"
  3BookModel.Transaction.ISOLATION_LEVELS.READ_COMMITTED // "READ COMMITTED"
  4BookModel.Transaction.ISOLATION_LEVELS.REPEATABLE_READ  // "REPEATABLE READ"
  5BookModel.Transaction.ISOLATION_LEVELS.SERIALIZABLE // "SERIALIZABLE"

Class Methods

• 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.

  1import * as expressExtension from 'express-sweet';
  2import ProfileModel from './ProfileModel';
  3
  4export default class extends expressExtension.database.Model {
  5    static association() {
  6        // User has one profile.
  7        this.hasOne(ProfileModel, {
  8            foreignKey: 'userId', // profile.userId
  9            sourceKey: 'id', // user.id
  10            as: 'profile'
  11        });
  12    }
  13}

• 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:

  • options?: sequelize.TransactionOptions
    Options provided when the transaction is created.

  Return:
  Promise<sequelize.Transaction> Returns a transaction object to identify the transaction being executed.

  Simple transaction usage example.

  1import BookModel from '../models/BookModel';
  2
  3let transaction;
  4try {
  5    transaction = await BookModel.begin();
  6    const book = await BookModel.create({title: 'Beautiful'}, {transaction});
  7    await transaction.commit();
  8} catch {
  9    if (transaction)
  10        await transaction.rollback();
  11}

  You can also use transaction options.

  1import BookModel from '../models/BookModel';
  2
  3let transaction;
  4try {
  5    transaction = await BookModel.begin({
  6        isolationLevel: BookModel.Transaction.ISOLATION_LEVELS.REPEATABLE_READ,
  7        type: BookModel.Transaction.TYPES.DEFERRED,
  8    });
  9    const book = await BookModel.findOne({where: {id: 1}}, {transaction});
  10    book.title = 'Beautiful';
  11    await book.save({transaction});
  12    await transaction.commit();
  13
  14    // Load updated data.
  15    await book.reload();
  16} catch {
  17    if (transaction)
  18        await transaction.rollback();
  19}

• public static create()
  Builds a new model instance and calls save on it.
  See here for details.

  Parameters:

  • values: object
    Hash of data values to create new record with.
  • options: object
    Build and query options.

  Return:
  Promise<Model> Returns a model that contains the data for the added record.

  1import BookModel from '../models/BookModel';
  2
  3// INSERT INTO `book` (`id`,`title`) VALUES (DEFAULT,'Beautiful');
  4await 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:

  • options: object
    save options.

  Return:
  Promise<Model> Returns a model that contains data for manipulated records such as add and update.

  1import BookModel from '../models/BookModel';
  2
  3// INSERT INTO `book` (`id`,`title`) VALUES (DEFAULT,'Beautiful');
  4const book = BookModel.build({title: 'Beautiful'});
  5await book.save();
  6
  7// UPDATE `book` SET `title`='Lose Yourself' WHERE `id` = 1;
  8book.title = 'Lose Yourself';
  9await 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:

  • options: object
    A hash of options to describe the scope of the search.

  Return:
  Promise<Model|null> Returns a Model containing the first data found in the database.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT `id`, `title`, `created`, `modified` FROM `book` AS `book` LIMIT 1;
  4await BookModel.findOne();

• public static findAll()
  Search for multiple instances.
  See here for details.

  Parameters:

  • options: object
    A hash of options to describe the scope of the search.

  Return:
  Promise<Array<Model>> Returns a model containing the data found in the database.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT `id`, `title`, `created`, `modified` FROM `book` AS `book` WHERE `book`.`title` LIKE 'Beautiful%';
  4await BookModel.findAll({
  5    where: {
  6        title: {
  7            [BookModel.Op.like]: 'Beautiful%'
  8        }
  9    }
  10});

• public static count()
  Count the number of records matching the provided where clause.
  See here for details.

  Parameters:

  • options: object
    A hash of options to describe the scope of the search.

  Return:
  Promise<number> Returns the count of records that match the condition.

  1import BookModel from '../models/BookModel';
  2
  3// SELECT count(*) AS `count` FROM `book` AS `book`;
  4await BookModel.count();

• public static update()
  Update multiple instances that match the where options.
  See here for details.

  Parameters:

  • values: object
    hash of values to update.
  • options: object
    update options.

  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).

  1import BookModel from '../models/BookModel';
  2
  3// UPDATE `book` SET `title`='Lose Yourself' WHERE `id` = 1;
  4await 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:

  • values: object
    hash of values to upsert.
  • options: object
    upsert options.

  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.

  1import BookModel from '../models/BookModel';
  2
  3// INSERT INTO `book` (`title`) VALUES (?) ON DUPLICATE KEY UPDATE `title`=VALUES(`title`);
  4await 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:

  • options: object
    destroy options.

  Return:
  Promise<number> The number of destroyed rows.

  1import BookModel from '../models/BookModel';
  2
  3// DELETE FROM `user` WHERE `id` = 1;
  4await 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:

  • target: Model
    The target model.
  • options: object
    hasOne association options.

  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.

  1import * as expressExtension from 'express-sweet';
  2import ProfileModel from  './ProfileModel';
  3
  4export default class extends expressExtension.database.Model {
  5    static association() {
  6        // User has one profile.
  7        this.hasOne(ProfileModel, {
  8            foreignKey: 'userId', // profile.userId
  9            sourceKey: 'id', // user.id
  10            as: 'profile'
  11        });
  12    }
  13}

  This is an example of record search.

  1import UserModel from '../models/UserModel';
  2
  3// SELECT
  4//     `user`.`id`,
  5//     `user`.`name`,
  6//     `profile`.`id` AS `profile.id`,
  7//     `profile`.`userId` AS `profile.userId`,
  8//     `profile`.`address` AS `profile.address`,
  9//     `profile`.`tel` AS `profile.tel`
  10// FROM
  11//     `user` AS `user`
  12//     LEFT OUTER JOIN `profile` AS `profile` ON `user`.`id` = `profile`.`userId`;
  13// 
  14// results in: [
  15//     {
  16//         "id": 1,
  17//         "name": "Robin",
  18//         "profile": {
  19//             "userId": 1,
  20//             "address": "777 Brockton Avenue, Abington MA 2351",
  21//             "tel": "202-555-0105"
  22//         }
  23//     }
  24// ]
  25await UserModel.findAll({
  26    attributes: ['id', 'name'],
  27    include: [
  28        {
  29            association: 'profile',
  30            attributes: ['userId', 'address', 'tel']
  31        },
  32    ]
  33});

• 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:

  • target: Model
    The target model.
  • options: object
    belongsTo association options.

  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.

  1import * as expressExtension from 'express-sweet';
  2import UserModel from './UserModel';
  3
  4export default class extends expressExtension.database.Model {
  5    static association() {
  6        // Profile belongs to one user.
  7        this.belongsTo(UserModel, {
  8            foreignKey: 'userId',  // profile.userId,
  9            targetKey: 'id',  // user.id
  10            as: 'user'
  11        });
  12    }
  13}

  This is an example of record search.

  1import ProfileModel from  '../models/ProfileModel';
  2
  3// SELECT
  4//     `profile`.`id`,
  5//     `profile`.`userId`,
  6//     `profile`.`address`,
  7//     `profile`.`tel`,
  8//     `user`.`id` AS `user.id`,
  9//     `user`.`name` AS `user.name`
  10// FROM
  11//     `profile` AS `profile`
  12//     INNER JOIN `user` AS `user` ON `profile`.`userId` = `user`.`id`;
  13// 
  14// results in: [
  15//     {
  16//         "userId": 1,
  17//         "address": "777 Brockton Avenue, Abington MA 2351",
  18//         "tel": "202-555-0105",
  19//         "user": {
  20//             "id": 1,
  21//             "name": "Robin"
  22//         }
  23//     }
  24// ]
  25await ProfileModel.findAll({
  26    attributes: ['userId', 'address', 'tel'],
  27    include: {
  28        association: 'user',
  29        required: true,
  30        attributes: ['id', 'name']
  31    }
  32});

• 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:

  • target: Model
    The target model.
  • options: object
    hasMany association options.

  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.

  1import * as expressExtension from 'express-sweet';
  2import CommentModel from './CommentModel';
  3
  4export default class extends expressExtension.database.Model {
  5    static association() {
  6        // User has many comments.
  7        this.hasMany(CommentModel, {
  8            foreignKey: 'userId',  // comment.userId
  9            sourceKey: 'id',  // user.id
  10            as: 'comments'
  11        });
  12    }
  13}

  This is an example of record search.

  1import UserModel from  '../models/UserModel';
  2
  3// SELECT
  4//     `user`.`id`,
  5//     `user`.`name`,
  6//     `comments`.`id` AS `comments.id`,
  7//     `comments`.`userId` AS `comments.userId`,
  8//     `comments`.`text` AS `comments.text`
  9// FROM
  10//     `user` AS `user`
  11//     LEFT OUTER JOIN `comment` AS `comments` ON `user`.`id` = `comments`.`userId`;
  12// 
  13// results in: [
  14//     {
  15//         "id": 1,
  16//         "name": "Robin",
  17//         "comments": [
  18//             {
  19//                 "userId": 1,
  20//                 "text": "From Robin #1"
  21//             },
  22//             {
  23//                 "userId": 1,
  24//                 "text": "From Robin #2"
  25//             }
  26//         ]
  27//     }
  28// ]
  29await UserModel.findAll({
  30    attributes: ['id', 'name'],
  31    include: {
  32        association: 'comments',
  33        attributes: ['userId', 'text']
  34    }
  35});

  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:

  1[
  2    {
  3        "id": 1,
  4        "name": "Robin",
  5        {
  6            "userId": 1,
  7            "text": "From Robin #1"
  8        }
  9    },
  10    {
  11        "id": 1,
  12        "name": "Robin",
  13        {
  14            "userId": 1,
  15            "text": "From Robin #2"
  16        }
  17    }
  18]

  With raw: false (default):

  1[
  2    {
  3        "id": 1,
  4        "name": "Robin",
  5        "comments": [
  6            {
  7                "userId": 1,
  8                "text": "From Robin #1"
  9            },
  10            {
  11                "userId": 1,
  12                "text": "From Robin #2"
  13            }
  14        ]
  15    }
  16]

• public static belongsToMany()
  Create an N:M association with a join table.
  Defining through is required.
  See here for details.

  Parameters:

  • target: Model
    The target model.
  • options: object
    belongsToMany association options.

  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.

  1import * as expressExtension from 'express-sweet';
  2import BookModel from './BookModel';
  3
  4export default class extends expressExtension.database.Model {
  5    static association() {
  6        // Users have many books, and books belong to many users.
  7        this.belongsToMany(BookModel, {
  8            foreignKey: 'userId',  // book.userId
  9            sourceKey: 'id',  // user.id
  10            as: 'books'
  11        });
  12    }
  13}

  This is an example of record search.

  1import UserModel from  '../models/UserModel';
  2
  3// SELECT
  4//     `user`.`id`,
  5//     `user`.`name`,
  6//     `books`.`id` AS `books.id`,
  7//     `books`.`userId` AS `books.userId`,
  8//     `books`.`title` AS `books.title`
  9// FROM
  10//     `user` AS `user`
  11//     LEFT OUTER JOIN `book` AS `books` ON `user`.`id` = `books`.`userId`;
  12// 
  13// results in: [
  14//     {
  15//         "id": 1,
  16//         "name": "Robin",
  17//         "books": [
  18//             {
  19//                 "userId": 1,
  20//                 "title": "Beautiful"
  21//             },
  22//             {
  23//                 "userId": 1,
  24//                 "title": "Lose Yourself"
  25//             }
  26//         ]
  27//     }
  28// ]
  29await UserModel.findAll({
  30    attributes: ['id', 'name'],
  31    include: {
  32        association: 'books',
  33        attributes: ['userId', 'title']
  34    }
  35});

• public static query()
  Raw Queries.
  As there are often use cases in which it is just easier t