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']}
}
});
const BookModel = require('../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[]) => 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.
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.
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.
Associate the model.
Define associations with other models such as hasOne, hasMany, belongsTo, belongsToMany.
This method is called automatically from within the express-sweet.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 sweet from 'express-sweet';
import ProfileModel from './ProfileModel';
export default class extends sweet.database.Model {
static association() {
// User has one profile.
this.hasOne(ProfileModel, {
foreignKey: 'userId', // profile.userId
sourceKey: 'id', // user.id
as: 'profile'
});
}
}
const Model = require('express-sweet').database.Model;
module.exports = class extends Model {
static association() {
// User has one profile.
const ProfileModel = require('./ProfileModel');
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:
options?: sequelize.TransactionOptions
Options provided when the transaction is created.
Return:
Promise<sequelize.Transaction> Returns a transaction object to identify the transaction being executed.
import BookModel from '../models/BookModel';
// SELECT count(*) AS `count` FROM `book` AS `book`;
await BookModel.count();
const BookModel = require('../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:
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).
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.
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.
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.
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.
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 sweet from 'express-sweet';
import BookModel from './BookModel';
export default class extends sweet.database.Model {
static association() {
// Users have many books, and books belong to many users.
this.hasMany(BookModel, {
foreignKey: 'userId', // book.userId
sourceKey: 'id', // user.id
as: 'books'
});
}
}
const Model = require('express-sweet').database.Model;
module.exports = class extends Model {
static association() {
// Users have many books, and books belong to many users.
const BookModel = require('./BookModel');
this.hasMany(BookModel, {
foreignKey: 'userId', // book.userId
sourceKey: 'id', // user.id
as: 'books'
});
}
}
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:
sql: string
SQL string.
options: object
Query options.
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});
