Sequelize 模型
Definition
使用define方法，定义模型与表之间的映射。Sequelize将自动添加createAt与updateAt属性.因此你可以知道内容什么时候写入数据库以及更新的最后时间.如果你不想在模型中使用时间戳，你只想在已有的数据库中使用一些时间戳.你可以直接跳转到 configuration 查看如何设置。
var Project = sequelize.define('project', {
title: Sequelize.STRING,
description: Sequelize.TEXT
})
var Task = sequelize.define('task', {
title: Sequelize.STRING,
description: Sequelize.TEXT,
deadline: Sequelize.DATE
})
你也可以在没一列设置一些参数达到此目的。
var Foo = sequelize.define('foo', {
// instantiating will automatically set the flag to true if not set
flag: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: true},
// default values for dates => current time
myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },
// setting allowNull to false will add NOT NULL to the column, which means an error will be
// thrown from the DB when the query is executed if the column is null. If you want to check that a value
// is not null before querying the DB, look at the validations section below.
title: { type: Sequelize.STRING, allowNull: false},
// Creating two objects with the same value will throw an error. The unique property can be either a
// boolean, or a string. If you provide the same string for multiple columns, they will form a
// composite unique key.
someUnique: {type: Sequelize.STRING, unique: true},
uniqueOne: { type: Sequelize.STRING, unique: 'compositeIndex'},
uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex'}
// The unique property is simply a shorthand to create a unique index.
someUnique: {type: Sequelize.STRING, unique: true}
// It's exactly the same as creating the index in the model's options.
{someUnique: {type: Sequelize.STRING}},
{indexes: [{unique: true, fields: ['someUnique']}]}
// Go on reading for further information about primary keys
identifier: { type: Sequelize.STRING, primaryKey: true},
// autoIncrement can be used to create auto_incrementing integer columns
incrementMe: { type: Sequelize.INTEGER, autoIncrement: true },
// Comments can be specified for each field for MySQL and PG
hasComment: { type: Sequelize.INTEGER, comment: "I'm a comment!" },
// You can specify a custom field name via the "field" attribute:
fieldWithUnderscores: { type: Sequelize.STRING, field: "field_with_underscores" },
// It is possible to create foreign keys:
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
}
}
})
The comment option can also be used on a table, see model configuration
Data types
Below are some of the datatypes supported by sequelize. For a full and updated list, see DataTypes.
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 (with PostGIS) or MySQL only.
注释选项也可在一个表中使用，具体的可查看 模型配置
数据类型
下面是 Sequelize 支持的一些数据类型。完整新的支持列表，请查看 数据类型
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 (with PostGIS) or MySQL only.
BLOB 数据类型，你既可以插入字符串，也可以插入流。当你在有一个 BLOB 的列的模型上执行 find 或者 findAll 命令的时候，该字段数据将作为流被返回。
如果你使用 PostgreSQL TIMESTAMP WITHOUT TIME ZONE 你需要在不同的时区自己解析他，你可以使用 pg 库自己解析:
require('pg').types.setTypeParser(1114, function(stringValue) {
return new Date(stringValue + "+0000");
// e.g., UTC offset. Use any offset that you would like.
});
除了上面提到的类型，integer ，bigint ，float 和 double 还支持 unsigned 和zerofill 属性，可以按任意顺序进行组合：请注意，这并不适用于 PostgreSQL 的！
Sequelize.INTEGER.UNSIGNED // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL
以上仅实例 integer ,但对 bigint 与 float 同样适用.
使用对象表示法:
// for enums:
sequelize.define('model', {
states: {
type: Sequelize.ENUM,
values: ['active', 'pending', 'deleted']
}
})
Deferrable
当您指定可选可以声明在PostgreSQL可延迟类型的一个外键列。下列选项有效:
// Defer all foreign key constraint check to the end of a transaction
Sequelize.Deferrable.INITIALLY_DEFERRED
// Immediately check the foreign key constraints
Sequelize.Deferrable.INITIALLY_IMMEDIATE
// Don't defer the checks at all
Sequelize.Deferrable.NOT
最后一个选项是 PostgreSQL 中默认的，不会允许你在事物中动态更改规则。了解更多信息，请查看 事务章节 .
Getters & setters
它可以在模型上定义 'object-property' 的 getters 与 setters 方法，这些将可被用作映射数据库字段的 protecting 属性和 定义 pseudo 属性。
定义 Getters 与 Setters 有2种方法(你可以混合与匹配这2种方法):
- 作为单个属性的部分定义
- 作为模型参数的一部分
N.B:如果一个 getter 或 setter 在两个地方定义，然后函数在相应的属性定义将始终优先。
定义部分属性
var Employee = sequelize.define('employee', {
name: {
type : Sequelize.STRING,
allowNull: false,
get : function() {
var title = this.getDataValue('title');
// 'this' allows you to access attributes of the instance
return this.getDataValue('name') + ' (' + title + ')';
},
},
title: {
type : Sequelize.STRING,
allowNull: false,
set : function(val) {
this.setDataValue('title', val.toUpperCase());
}
}
});
Employee
.create({ name: 'John Doe', title: 'senior engineer' })
.then(function(employee) {
console.log(employee.get('name')); // John Doe (SENIOR ENGINEER)
console.log(employee.get('title')); // SENIOR ENGINEER
})
定义部分模型参数
下面是在模型选项中定义 setter 和 getter 方法的一个例子。
fullName 的getter方法，实例在你的模型中如何定义 pseudo 属性，该属性其实不是你数据库元数据的一部分。实际上，定义 pseudo 属性有两种方法:
- 使用模型的
getters方法 - 使用列的
VIRTUAL数据类型
VIRTUAL datatype 可以有验证，而对于虚拟属性的 getter 方法，则没有。
需要注意的是在 fullName 中引用的 this.firstname 和 this.lastname 方法将触发调用相应的 getter 方法。
如果你不希望使用 getDataValue() 方法来访问数据库的值。(见下文)
var Foo = sequelize.define('foo', {
firstname: Sequelize.STRING,
lastname: Sequelize.STRING
}, {
getterMethods : {
fullName : function() { return this.firstname + ' ' + this.lastname }
},
setterMethods : {
fullName : function(value) {
var names = value.split(' ');
this.setDataValue('firstname', names.slice(0, -1).join(' '));
this.setDataValue('lastname', names.slice(-1).join(' '));
},
}
});
Helper functions for use inside getter and setter definitions
- 经常使用
this.getDataValue检索底层属性值/* a getter for 'title' property */ function() { return this.getDataValue('title'); } - 经常使用
this.setDataValue设置底层属性值
N.B:坚持使用/* a setter for 'title' property */ function(title) { return this.setDataValue('title', title.toString().toLowerCase()); }setDataValue和getDataValue方法(反对直接访问底层数据属性值)-这样做可以保护你的自定义的getter和setter在底层模型所做的改变。
验证
模型验证，允许您为模型的每个属性指定格式/内容/继承等验证。
验证将在 create,update 和 save的时候自动执行。你也可以调用 validate() 手动验证一个实例
这些验证被 validator.js 实现
var ValidateMe = sequelize.define('foo', {
foo: {
type: Sequelize.STRING,
validate: {
is: ["^[a-z]+$",'i'], // will only allow letters
is: /^[a-z]+$/i, // same as the previous example using real RegExp
not: ["[a-z]",'i'], // will not allow letters
isEmail: true, // checks for email format ([email protected])
isUrl: true, // checks for url format (http://foo.com)
isIP: true, // checks for IPv4 (129.89.23.1) or IPv6 format
isIPv4: true, // checks for IPv4 (129.89.23.1)
isIPv6: true, // checks for IPv6 format
isAlpha: true, // will only allow letters
isAlphanumeric: true // will only allow alphanumeric characters, so "_abc" will fail
isNumeric: true // will only allow numbers
isInt: true, // checks for valid integers
isFloat: true, // checks for valid floating point numbers
isDecimal: true, // checks for any numbers
isLowercase: true, // checks for lowercase
isUppercase: true, // checks for uppercase
notNull: true, // won't allow null
isNull: true, // only allows null
notEmpty: true, // don't allow empty strings
equals: 'specific value', // only allow a specific value
contains: 'foo', // force specific substrings
notIn: [['foo', 'bar']], // check the value is not one of these
isIn: [['foo', 'bar']], // check the value is one of these
notContains: 'bar', // don't allow specific substrings
len: [2,10], // only allow values with length between 2 and 10
isUUID: 4, // only allow uuids
isDate: true, // only allow date strings
isAfter: "2011-11-05", // only allow date strings after a specific date
isBefore: "2011-11-05", // only allow date strings before a specific date
max: 23, // only allow values
min: 23, // only allow values >= 23
isArray: true, // only allow arrays
isCreditCard: true, // check for valid credit card numbers
// custom validations are also possible:
isEven: function(value) {
if(parseInt(value) % 2 != 0) {
throw new Error('Only even values are allowed!')
// we also are in the model's context here, so this.otherField
// would get the value of otherField if it existed
}
}
}
}
})
需要注意的是，内政的验证函数需要传递多个参数，要传递的参数必须是一个数组。 但是如果一个单独的数组参数需要传递，例如用户 isIn 接收的一个实例数组，这将解析为多个字符串数组参数，而不是一个数组参数。要解决此传递参数，例如 [['one','two']].
要使用自定义错误消息，而不是由validator.js提供，使用对象，而不是明文或参数数组，例如不需要任何参数可以给出一个自定义消息验证程序
isInt: {
msg: "Must be an integer number of pennies"
}
或者需要传递多个参数
isIn: {
args: [['en', 'zh']],
msg: "Must be English or Chinese"
}
当使用自定义的验证功能，该错误信息将是任何消息抛出Error对象持有。
查看 validator.js project 了解内置验证方法的更多细节
提示：您还可以定义日志部分自定义函数。只是传递一个函数。第一个参数将被记录。
Validators and allowNull
验证器也可以被定义用于检查特定字段的验证后的模型。使用此验证，例如，设置或者不设置 latitude 或者 longtitude 只要有一个是失败的，其他的一个也不设置。
模型对象上下文调用模型验证方法，如果抛出一个错误，则被认为是失败的，否则是对的。这与特定字段验证器是一样的。
收集到的任何错误消息都放在验证结果对象旁边的现场验证错误，在validate选项对象未通过验证的方法关键命名的键。即使只能是在任何一个时间每个模型验证方法只有一个错误消息，它是作为在数组的单个串错误，以最大化一致性的使用字段错误。
例如:
var Pub = Sequelize.define('pub', {
name: { type: Sequelize.STRING },
address: { type: Sequelize.STRING },
latitude: {
type: Sequelize.INTEGER,
allowNull: true,
defaultValue: null,
validate: { min: -90, max: 90 }
},
longitude: {
type: Sequelize.INTEGER,
allowNull: true,
defaultValue: null,
validate: { min: -180, max: 180 }
},
}, {
validate: {
bothCoordsOrNone: function() {
if ((this.latitude === null) !== (this.longitude === null)) {
throw new Error('Require either both latitude and longitude or neither')
}
}
}
})
在这个简单的对象错误验证的例子中，要么同时设置 latitude 与 longitude，要么都不设置。如果我们试图建立一个超出范围的纬度和经度，raging_bullock_arms.validate（） 可能返回
{
'latitude': ['Invalid number: latitude'],
'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
}