1. Connect to the database (js/ts)
//
exports const config = {
database: {
database: 'management',
host: 'localhost',
port: 3306,
user: 'root',
password: '12345678'
}
}
//
const Sequelize = require('sequelize');
import { config } from './config';
const { database, host, port, user, password } = config;
const sequelize = new Sequelize(database, user, password, {
dialect: 'mysql',
host,
port,
logging: true, // logging: true, print sql to console timezone: '+08:00', //The unity of time, here is the East Eighth District, the default is 0 time zone ? define: { //The global definition will be passed through the connection instance // timestamps: false, // By default, Sequelize uses data types to automatically add createdAt and updatedAt fields to each model. These fields are automatically managed - these fields are automatically set whenever you create or update content using Sequelize. The createdAt field will contain the timestamp representing the creation time, and the updatedAt field will contain the timestamp of the latest update. //For models with timestamps: false parameter, this behavior can be disabled // createdAt: 'created_at', //Custom timestamp // updatedAt: 'updated_at',
// paranoid: true,
// deletedAt: 'deleted_at', //paranoid means that when the defendant wants to delete the record, it will not be physically deleted, but will add a special field containing the deleted request timestamp deletedAt. Pass the paranoid: true parameter to the model definition. Paranoid requires that timestamps must be enabled, that is, timestamps: true must be passed. // Convert the camel to an underscore //underscored: false,
pool: { // Use connection pool max: 5, // Maximum number of connections in the connection pool min: 0, // Minimum number of connections in the connection pool acquire: 30000,
idle: 10000 // If a thread has not been used within 10 seconds, then release the thread },
}
})
// Test database link sequelize
.authenticate()
.then(() => {
('Database connection is successful');
})
.catch((err: any) => {
// Print output when the database connection fails (err);
throw err;
});
export default sequelize; // Expose the connection object2. Database Model
1. Model definition
Call(modelName, attributes, options)
const User = ('User', {
// Define the model properties here id: {
type: ,
autoIncrement: true, //Authorize self-increase primaryKey: true
}
firstName: {
type: ,
allowNull: false
},
lastName: {
type:
// allowNull default to true }
}, {
// This is other model parameters sequelize, // We need to pass the connection instance // Local definition modelName: 'User' // We need to select the model name // Don't forget to enable timestamps! timestamps: true,
// Don't want createdAt createdAt: false,
// Want updatedAt but hope the name is updatedTimestamp updatedAt: 'updateTimestamp'
});
// `` will return to the model (User === ); // true
Timestamps
By default, Sequelize uses data typesAutomatically add to each modelcreatedAtandupdatedAtFields. These fields are automatically managed - whenever you create or update content using Sequelize, these fields are automatically set.createdAtThe field will contain a timestamp representing the creation time, andupdatedAtThe field will contain the latest updated timestamp.
For withtimestamps: falseParameter model, this behavior can be disabled. To enable createdAt, updatedAt musttimestamps: true：
('User', {
// ... (Properties) }, {
timestamps: false
});
It can also be enabled onlycreatedAt/updatedAtOne, and provide a custom name for these columns:
class Foo extends Model {}
({ /* Properties */ }, {
sequelize,
// Don't forget to enable timestamps! timestamps: true,
// Don't want createdAt createdAt: false,
// Want updatedAt but hope the name is updatedTimestamp updatedAt: 'updateTimestamp'
});
2. Generate the model
1. Global installationsequelize-auto， mysql2
npm install -g mysql2 npm install -g sequelize-auto
2. Run the following command
usesequelize-autoAutomatically generate corresponding models in accordance with the database.sequelize-autoOfficial document address:/sequelize/s…
//sequelize-auto -h "database address" -d "database name" -u "user name" -x "password" -p "port number" --dialect mysql sequelize-auto -o "./model" -d test -h 127.0.0.1 -u root -p 3306 -x 123456 -e mysql
Options:
--help Show help [boolean]
--version Show version number [boolean]
-h, --host IP/Hostname for the database. [string]
-d, --database Database name. [string]
-u, --user Username for database. [string]
-x, --pass Password for database. If specified without providing
a password, it will be requested interactively from
the terminal.
-p, --port Port number for database (not for sqlite). Ex:
MySQL/MariaDB: 3306, Postgres: 5432, MSSQL: 1433
[number]
-c, --config Path to JSON file for Sequelize-Auto options and
Sequelize's constructor "options" flag object as
defined here:
/master/class/lib/~#instance-constructor-constructor
[string]
-o, --output What directory to place the models. [string]
-e, --dialect The dialect/engine that you're using: postgres,
mysql, sqlite, mssql [string]
-a, --additional Path to JSON file containing model options (for all
tables). See the options: /master/class/lib/~#static-method- init
[string]
--indentation Number of spaces to indent [number]
-t, --tables Space-separated names of tables to import [array]
-T, --skipTables Space-separated names of tables to skip [array]
--caseModel, --cm Set case of model names: c|l|o|p|u
c = camelCase
l = lower_case
o = original (default)
p = PascalCase
u = UPPER_CASE
--caseProp, --cp Set case of property names: c|l|o|p|u
--caseFile, --cf Set case of file names: c|l|o|p|u|k
k = kebab-case
--noAlias Avoid creating alias `as` property in relations
[boolean]
--noInitModels Prevent writing the init-models file [boolean]
-n, --noWrite Prevent writing the models to disk [boolean]
-s, --schema Database schema from which to retrieve tables[string]
-v, --views Include database views in generated models [boolean]
-l, --lang Language for Model output: es5|es6|esm|ts
es5 = ES5 CJS modules (default)
es6 = ES6 CJS modules
esm = ES6 ESM modules
ts = TypeScript [string]
--useDefine Use `` instead of `init` for es6|esm|ts
--singularize, --sg Singularize model and file names from plural table
names [boolean]
3. Definition of corresponding database operators
const { Op } = sequelize;
[]: {a: 5} // And (a = 5) []: [{a: 5}, {a: 6}] // (a = 5 or a = 6) []: 6, // id > 6
[]: 6, // id >= 6
[]: 10, // id < 10
[]: 10, // id <= 10
[]: 20, // id != 20
[]: 3, // = 3
[]: true, // Not TRUE []: [6, 10], // Between 6 and 10 []: [11, 15], // Not between 11 and 15 []: [1, 2], // In [1, 2] []: [1, 2], // Not in [1, 2] []: '%hat', // Include '%hat' []: '%hat' // does not include '%hat' []: '%hat' // Includes '%hat' (case insensitive) (PG only) []: '%hat' // Not included '%hat' (PG only) []: '^[h|a|t]' // Match regular expressions/~ '^[h|a|t]' (MySQL/PG only) []: '^[h|a|t]' // Does not match regular expressions/!~ '^[h|a|t]' (MySQL/PG only) []: '^[h|a|t]' // ~* '^[h|a|t]' (PG only) []: '^[h|a|t]' // !~* '^[h|a|t]' (PG only) []: { []: ['cat', 'hat']} // Contains any array ['cat', 'hat'] - also applicable to iLike and notLike []: [1, 2] // && [1, 2] (PG array overlap operator) []: [1, 2] // @> [1, 2] (PG array contains operators) []: [1, 2] // <@ [1, 2] (PG array is included in operators) []: [2,3] // Any array [2, 3]::INTEGER (PG only) []: 'user.organization_id' // = 'user'.'organization_id', uses a database language-specific column identifier, this example uses $and: {a: 5} // AND (a = 5)
$or: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
$gt: 6, // > 6
$gte: 6, // >= 6
$lt: 10, // < 10
$lte: 10, // <= 10
$ne: 20, // != 20
$not: true, // IS NOT TRUE
$between: [6, 10], // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15
$in: [1, 2], // IN [1, 2]
$notIn: [1, 2], // NOT IN [1, 2]
$like: '%hat', // LIKE '%hat'
$notLike: '%hat' // NOT LIKE '%hat'
$iLike: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat' // NOT ILIKE '%hat' (PG only)
$like: { $any: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2] // && [1, 2] (PG array overlap operator)
$contains: [1, 2] // @> [1, 2] (PG array contains operator)
$contained: [1, 2] // <@ [1, 2] (PG array contained by operator)
$any: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
$col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
//Two uses of or operator order_status=0 or order_status=1 params['$or'] = [{ order_status: 0 }, { order_status: 1 }];
params['order_status'] = {
$or: [
{ $eq: 0 },
{ $eq: 1 }
]
}
4. Add, delete, modify and check (CRUD)
1. Add
create
Add a single record to the database.
const User = ('./model/user'); //Import the model const user = await ({
username: 'alice123',
isAdmin: true
});
bulkCreate
Bulk creation, by receiving array objects instead of single objects.
await ([
{ username: 'foo', pwd:"123"},
{ username: 'bar', pwd:"345"}
], { updateOnDuplicate: true, fields: ['username','pwd','address'] });
updateOnDuplicate: Whether to update if the line key already exists. If the unique index or primary key of existing records in the database table is already present, perform an update operation (true means update, false means no update. The default is update)
fields: The array to define fields (the rest of them will be ignored) is used to limit the columns actually inserted.
Tips: Explanation of the corresponding native SQL statement
In the MySQL database, if you follow the insert statement, the ON DUPLICATE KEY UPDATE clause.
- The row to be inserted produces duplicate values in the unique index or primary key of the existing record in the table, and the old row update occurs;
- If the inserted row data does not repeat the unique index or primary key of the record in the existing table, the new record insertion operation is performed.
findOrCreate
Unless a result that satisfies the query parameters, the methodfindOrCreateAn entry is created in the table. In both cases, it returns an instance (the found instance or the created instance) and a boolean value indicating whether the instance has been created or has existed.
usewhereParameters to find the entry, and usedefaultsParameters to define what must be created.defaultsIf the value of each column is not included, Sequelize will be usedwherevalue (if present).
const [user, created] = await ({
where: { username: '123' },
defaults: {
job: 'Technical Lead JavaScript'
}
});
(); // 'sdepold'
(); // This may or may not be 'Technical Lead JavaScript' (created); // Indicates whether this instance has just created a boolean value if (created) {
(); // This is definitely 'Technical Lead JavaScript' }
2. Query
Optional query parameters
const result = await ({
attributes: [
'goodsId',
[('SUM', ('order_count')), 'order_count'],
[('SUM', ('total_price_sum')), 'total_price_sum'],
[('SUM', ('goods_cost')), 'goods_cost'],
[('COUNT', ('goodsId')), 'goods_count']
],
where: params,
limit: 10,
offset:0,
raw: true,
group: [('goodsId')],
})
1. attributes
If you only need to query partial properties of the model, you can specify it in the query optionattributesImplementation, such as[colName1,colName2,colName3......]。
When you need to query all fields and use aggregate query for a certain field, you only need to pass in as an objectattributesAnd addincludeSub-properties are enough.
// It's more troublesome to specify the full query field ({
attributes: ['id', 'foo', 'bar', 'baz', 'quz', [('COUNT', ('hats')), 'hats_count']]
});
// This will be shorter and there will be no errors after you add/remove attributes ({
attributes: { include: [[('COUNT', ('hats')), 'hats_count']] }
});
When all queries can be passedexcludeSubproperties to exclude fields that do not require queries:
({
attributes: { exclude: ['baz'] }
});
Take an alias for the column:
existattributesAdd an item in the array[ oldColName , newColName ]or[ ('oldColName') , newColName ]
Aggregation query can be performed through methods:
As commonly usedSUM，COUNTwait.
2. where
In the modelfindOne/finAllorupdate/destroyDuring operation, you can specify awhereOption to specify filter criteria,whereIs an object containing attribute/value pairs, sequelize will produce filter conditions for query statements based on this object.
where:{ //Query username='admin' pwd ='123456' user_status=0 or user_status=1 username: 'admin',
pwd:"123456",
user_status:{
[]: [
{ $eq: 0 },
{ $eq: 1 }
]
}
}
Complex filtering conditions can be done in conjunction with character operations.
3. limit，offset
Inquiry, we can uselimitLimit the number of results returned and can be passedoffsetTo set the query offset (skip) amount, we can implement the function of pagination query through these two properties:
({ limit: 10 }) // Get 10 pieces of data (instance) ({ offset: 8 }) // Skip 8 pieces of data (instance) ({ offset: 5, limit: 5 }) // Skip 5 data and get the following 5 data (instance) //User scenario, paging request data const page = parseInt( || 1);
const limit = parseInt( || 10);
const currentPage = (page - 1) * limit;
({
limit,
offset: currentPage,
})
4. raw
{ raw: true }When passed as a parameter to finder, wrapping can be disabled on the result set of the query.
5. order
orderThe parameters use a series of items to make the sequelize method sort the query. These items themselves are[column, direction]array of form. The column will be escaped correctly and will be verified in the valid direction list (e.g.ASC, DESC, NULLS FIRSTwait).
({
order: [
//('date') Sometimes you can use() to specify the column to prevent the column name from being found during subqueries or associated queries. [('date'), 'DESC'],
['date', 'DESC'],
// Escape title and sort in descending order for valid direction list ['title', 'DESC'],
// Ascending order will be sorted by maximum age ('max', ('age')),
// Sort by maximum age in descending order [('max', ('age')), 'DESC'],
// The descending order will be sorted by otherfunction(`col1`, 12, 'lalala') [('otherfunction', ('col1'), 12, 'lalala'), 'DESC'],
// Sort by createdAt of the associated model using the model name as the associated name. [Task, 'createdAt', 'DESC'],
// Sort by createdAt of the associated model using the model name as the associated name. [Task, Project, 'createdAt', 'DESC'],
// The associated name will be used by the createdAt of the associated model. ['Task', 'createdAt', 'DESC'],
// Sort by createdAt of the nested association model using the associated name. ['Task', 'Project', 'createdAt', 'DESC'],
// The associated objects will be sorted by createdAt of the associated model. (Preferred method) [, 'createdAt', 'DESC'],
// Sort by createdAt of nested association models using association objects. (Preferred method) [, , 'createdAt', 'DESC'],
// Sorting simple association objects by createdAt of the association model. [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// Sort the createdAt simple association objects by the nested association model. [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
],
// Will be sorted in descending order of maximum age order: ('max(age) DESC'),
// If the direction is ignored, the default ascending order will be sorted in ascending order by maximum age order: ('max', ('age')),
// If the direction is omitted, the default ascending order will be arranged in ascending order of age order: ('age'),
// will be sorted randomly according to dialect (but not fn('RAND') or fn('RANDOM'))) order: ()
});
({
order: [
// Will return `name` ['name'],
// Will return `username` DESC ['username', 'DESC'],
// will return max(`age`) ('max', ('age')),
// will return max(`age`) DESC [('max', ('age')), 'DESC'],
// will return otherfunction(`col1`, 12, 'lalala') DESC [('otherfunction', ('col1'), 12, 'lalala'), 'DESC'],
// will return otherfunction(awesomefunction(`col`)) DESC, this nesting may be infinite! [('otherfunction', ('awesomefunction', ('col'))), 'DESC']
]
});
orderThe elements of the array can be as follows:
A string (it will be automatically referenced)
An array whose first element will be referenced and the second one will be appended verbatim
One hasrawObject of field:
-
rawContent will be added verbatim without reference - Everything else will be ignored if not set
raw, the query will fail
Call(This will generate a function call in SQL)
Call(This will reference the column name)
6. group
The syntax of grouping and sorting is the same, except that the grouping does not accept the direction as the last parameter of the array (it does not exist.ASC, DESC, NULLS FIRSTwait).
You can also pass strings directly togroup, the string will be included directly (normal) in the generated SQL.
({ group: 'name' });
({
group: [('date'),('id')] //You can specify multiple columns for grouping });
// Generate 'GROUP BY name'Tips: When grouping and sorting, you can use () to specify the column to prevent the column name from being found during subquery or associated query.
7. include
includeKeywords represent association query
Simple query
1. findOne
findOneThe method gets the first entry it finds (it can satisfy the optional query parameters provided).
const user = await ({ where: { username: 'admin' } });
The query will retrieve all entries from the table (unless subject towhereclause limitation).
const user = await ({ where: { username: 'admin' },raw:true });
，findAndCount
findAndCountAllThe method is combinedfindAllandcountThe convenient method is very useful when handling queries related to paging, where you want to retrieve thelimitandoffsetdata, but also need to know the total number of records matching the query.
When not providedgrouphour,findAndCountAllThe method returns an object with two properties:
-
count: an integer - the total number of records matching the query -
rows: an array object - the obtained record
When providedgrouphour,findAndCountAllThe method returns an object with two properties:
-
count- An array object - Contains total and preset properties in each group -
rows- an array object - the obtained record
const { count, rows } = await ({
where: {
username: {
[]: '%foo%'
}
},
offset: 10, //Query offset (skip) amount limit: 2 //Limit the number of results returned });
Related Query
let result = await ({
include: [
{
attributes: [[('title'), 'goods_name']],
association: (tbGoods, { targetKey: 'goodsId', foreignKey: 'goodsId' , as:'goods_name_info'}),
model: Goods,
required: false,
where: params2,
}
],
where: params,
limit,
offset: currentPage,
raw: true,
order: [
[('date'), 'DESC'],
]
})
Association type
(B, { /* Parameters */ }); // A has a B (B, { /* Parameters */ }); // A belongs to B (B, { /* Parameters */ }); // A has multiple B (B, { through: 'C', /* Parameters */ }); // A belongs to multiple Bs, through the link table Cassociation
asSpecify the alias for the connection
where
The where clause is also supported in the association.
required
false, indicating the left outer join (LEFT OUTER JOIN), all the left tables appear in the result set. If there is no corresponding record on the right table, the corresponding field isNULL。
true, indicates an internal connection (INNER JOIN), and records that meet the conditions will appear in the result set.
Subquery
Tips: BecauseAny content will be inserted without escaping
//Match the goods_name corresponding to goods_order according to goodsId let result = await goods_roi.findAll({
attributes: [
'goodsId',
[('SUM', ('order_count')), 'order_count'],
[('SUM', ('total_price_sum')), 'total_price_sum'],
[('SUM', ('goods_cost')), 'goods_cost'],
[
(`(
SELECT item_title
FROM goods_order AS goods_order
WHERE
goods_order.item_id = goods_roi.goodsId limit 1
)`),
'goods_name'
]
],
where: params,
raw: true,
group: [('goodsId')]
})
Practical Methodscount，max，min，sum，increment, decrement
countMethod only calculates the number of times an element appears in the database
const amount = await ({
where: {
id: {
[]: 25
}
}
});
(`This has ${amount} A record id Greater than 25`);
max, minandsum
Suppose we have three users, 10, 5 and 40 years old
await ('age'); // 40
await ('age', { where: { age: { []: 20 } } }); // 10
await ('age'); // 5
await ('age', { where: { age: { []: 5 } } }); // 10
await ('age'); // 55
await ('age', { where: { age: { []: 5 } } }); // 50
Increment increases by itself, decrement decreases by itself
await ({age: 5}, { where: { id: 1 } }) // Increase the age to 15 years old await ({age: -5}, { where: { id: 1 } }) // Reduce age to 5 years old3. Update update
Update query is also acceptedwhereparameter
*// Change all people without a last name to "Doe"* await ({ lastName: "Doe" }, {
where: {
lastName: null
}
});
4. Delete destroy
Destroy query is also acceptedwhereparameter
*// Delete all people named "Jane"* await ({
where: {
firstName: "Jane"
}
});
5. Hosted transaction
Sequelize supports two ways to use transactions:
- Managed Transactions: Sequelize will automatically roll back the transaction if any error is raised, otherwise the transaction will be committed. Also, if CLS (Continuous Local Storage) is enabled, all queries in the transaction callback will automatically receive the transaction object.
- Unmanaged transactions: commit and rollback transactions should be done manually by the user (by calling the appropriate Sequelize method).
Managed transactions automatically handle commit or rollback transactions. By passing the callback toTo start the managed transaction. This callback can beasync(usually).
In this case, the following will occur:
- Sequelize will automatically start the transaction and obtain the transaction object
t - Sequelize will then execute the callback you provide and pass it in it
t - If your callback throws an error, Sequelize will automatically roll back the transaction
- If your callback is successful, Sequelize will automatically submit the transaction
Only in this way,Only when the call is solved:
- Resolution to resolve your callback
- Or, if your callback throws an error, reject and throw an error
try {
const result = await (async (t) => {
const user = await ({
firstName: 'Abraham',
lastName: 'Lincoln'
}, { transaction: t }); //{transaction: t} must be passed as a parameter in the second object return user;
});
// If this line is executed, it means that the transaction has been successfully committed, and `result` is the result returned by the transaction // `result` is the result returned from the transaction callback (in this case `user`) } catch (error) {
// If this is executed, an error occurs. // This transaction has been automatically rolled back by Sequelize! }
Notice:()and()Not called directly.
Automatically pass transactions to all queries: in the example above, still by passing{ transaction: t }As the second parameter, the transaction is passed manually.
The above is the js version, and some of them do not apply to ts. ts should be used according to the specific official documentation.
The above is the detailed content of the Nodejs Sequelize manual to quickly get started with the application. For more information about the Nodejs Sequelize manual, please pay attention to my other related articles!