一、基本的sequelize常见操作--增删改查
1、引入sequelize模块
var sequelize = require('sequelize');
2、连接数据库
var sequelize = new Sequelize('sample' , // 数据库名
'root' , // 用户名
'psw' , // 用户密码
{
'dialect' : 'mysql' , // 数据库使用mysql
'host' : 'localhost' , // 数据库服务器ip
'port' : 3306, // 数据库服务器端口
'define' : {
// 字段以下划线(_)来分割(默认是驼峰命名风格)
'underscored' : true ,
' charset ' : 'utf8',
' collate': 'utf8_general_ci',
' freezeTableName': true,
' timestamps': true, //为模型添加 createdAt 和 updatedAt 两个时间戳字段
},
' pool': {//连接池
'maxConnections': 20,
' minConnections': 0,
' maxIdleTime': 10000 // 连接最大空置时间(毫秒),超时后将释放连接
},
}
);
3、定义表
- var User = sequelize.define(
- 'user',
- {
- userId: {
- field: 'user_id',
- primaryKey: true,
- type: Sequelize.BIGINT,
- allowNull: false
- },
- userName: {
- field: 'user_name',
- type: Sequelize.STRING,
- allowNull: false
- },
- userIcon: {
- field: 'user_icon',
- type: Sequelize.STRING,
- allowNull: true
- },
- title: {
- field: 'title',
- type: Sequelize.STRING,
- allowNull: true
- },
- gender: {
- field: 'gender',
- type: Sequelize.ENUM('MALE','FEMALE'),
- allowNull: true
- },
- birth: {
- field: 'birth',
- type: Sequelize.STRING,
- allowNull: true
- },
- mail: {
- field: 'mail',
- type: Sequelize.STRING,
- allowNull: true
- },
- tel: {
- field: 'tel',
- type: Sequelize.STRING,
- allowNull: true
- },
- mobile: {
- field: 'mobile',
- type: Sequelize.STRING,
- allowNull: true
- },
- updateTime: {
- field: 'update_time',
- type: Sequelize.STRING,
- allowNull: true
- }
- },
- {
- tableName: 'user',
- timestamps: false,
- freezeTableName: true
- }
- );
4、往表里添加数据
- User.create({
- userId: 23,
- userName: '老杨',
- updateTime: '2016-01-22 18:37:22'
- });
5、修改表内数据
- var pram={'userName':'晓博'};
- user.update(
- pram,{
- 'where':{'userId':{eq:23}}
- }
- );//将userId等于23的userName改为'晓博'
6、删除表内数据
user.destroy({'where':{'id':{eq:23}}});//将表内userId等于23的元组删除
二、sequelize常见知识总结
定义model
import sequelize from 'sequelize'
var Foo = sequelize.define('foo', {
// 默认值 和 是否为空
flag: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: true},
// 默认时间为创建时间
myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },
// 设置allowNull为false将会在数据表中添加 NOT NULL列,如果查询时该列为null,数据库会抛出错误// 如果你想在查询前检查该值是否为null,看一下下面的验证(validations)部分
title: { type: Sequelize.STRING, allowNull: false},
// unique 可以是boolean, 或 string.如果多个列是相同string
// 就会变成 composite unique key.
uniqueOne: { type: Sequelize.STRING, unique: 'compositeIndex'},
uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex'}
// unique会创建索引
someUnique: {type: Sequelize.STRING, unique: true}
// 也可以这么创建索引
{someUnique: {type: Sequelize.STRING}},
{indexes: [{unique: true, fields: ['someUnique']}]}
// 主键
identifier: { type: Sequelize.STRING, primaryKey: true},
// 自动增量
incrementMe: { type: Sequelize.INTEGER, autoIncrement: true },
// 在MySQL and PG可以有comments
hasComment: { type: Sequelize.INTEGER, comment: "I'm a comment!" },
// 可以通过"field"属性设置特定的值
fieldWithUnderscores: { type: Sequelize.STRING, field: "field_with_underscores" },
// 创建外键
bar_id: {
type: Sequelize.INTEGER,
references: {
// This is a reference to another model
model: Bar,
// This is the column name of the referenced model
key: 'id',
// This declares when to check the foreign key constraint. PostgreSQL only.
deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
}
}
})
数据类型
Sequelize.STRING // VARCHAR(255)Sequelize.STRING(1234) // VARCHAR(1234)
Sequelize.STRING.BINARY // VARCHAR BINARY
Sequelize.TEXT // TEXT
Sequelize.TEXT('tiny') // TINYTEXT
Sequelize.INTEGER // INTEGER
Sequelize.BIGINT // BIGINT
Sequelize.BIGINT(11) // BIGINT(11)
Sequelize.FLOAT // FLOAT
Sequelize.FLOAT(11) // FLOAT(11)
Sequelize.FLOAT(11, 12) // FLOAT(11,12)
Sequelize.REAL // REAL PostgreSQL only.
Sequelize.REAL(11) // REAL(11) PostgreSQL only.
Sequelize.REAL(11, 12) // REAL(11,12) PostgreSQL only.
Sequelize.DOUBLE // DOUBLE
Sequelize.DOUBLE(11) // DOUBLE(11)
Sequelize.DOUBLE(11, 12) // DOUBLE(11,12)
Sequelize.DECIMAL // DECIMAL
Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)
Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6) // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY // DATE without time.
Sequelize.BOOLEAN // TINYINT(1)
Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.
Sequelize.JSON // JSON column. PostgreSQL only.
Sequelize.JSONB // JSONB column. PostgreSQL only.
Sequelize.BLOB // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)
Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only.
Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.
Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT') // Spatial column with geomerty type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geomerty type and SRID. PostgreSQL
范围类型
// 默认左开右闭'["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'
// inclusive lower bound, exclusive upper bound
Timeline.create({ range: [new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))] });
// control inclusion
const range = [new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))];
range.inclusive = false; // '()'
range.inclusive = [false, true]; // '(]'
range.inclusive = true; // '[]'
range.inclusive = [true, false]; // '[)'
// or as a single expression
const range = [
{ value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
{ value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'
// composite form
const range = [
{ value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
new Date(Date.UTC(2016, 1, 1)),
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'
Timeline.create({ range });
更多资料:https://itbilu.com/nodejs/npm/VkYIaRPz-.html#induction-promise 中文文档资料
https://www.liaoxuefeng.com/wiki/001434446689867b27157e896e74d51a89c25cc8b43bdb3000/001472286125147031e735933574ae099842afd31be80d1000