最近在公司接触到了sequelize(Nodejs的ORM框架),研究了一下官方文档,做了以下整理
Models Definition
let DeviceInterface = Sequelize.define('DeviceInterface', { name: { type: Sequelize.STRING, allowNull: false }, createBy: { type: Sequelize.INTEGER, defaultValue: '-1' }, image: { type: Sequelize.STRING }, desc: { type: Sequelize.STRING } }, { tableName: 'DeviceInterface' });
Data types
Sequelize.STRING // VARCHAR(255)Sequelize.STRING(1234) // VARCHAR(1234)Sequelize.STRING.BINARY // VARCHAR BINARYSequelize.TEXT // TEXTSequelize.TEXT('tiny') // TINYTEXTSequelize.INTEGER // INTEGERSequelize.BIGINT // BIGINTSequelize.BIGINT(11) // BIGINT(11)Sequelize.FLOAT // FLOATSequelize.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 // DOUBLESequelize.DOUBLE(11) // DOUBLE(11)Sequelize.DOUBLE(11, 12) // DOUBLE(11,12)Sequelize.DECIMAL // DECIMALSequelize.DECIMAL(10, 2) // DECIMAL(10,2)Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgresSequelize.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 geometry type. PostgreSQL (with PostGIS) or MySQL only.Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geometry type and SRID. PostgreSQL (with PostGIS) or MySQL only.
其他定义方法
var User = sequelize.define( 'user', { 'emp_id': { 'type': Sequelize.CHAR(10), // 字段类型 'allowNull': false, // 是否允许为NULL 'unique': true // 字段是否UNIQUE }, 'nick': { 'type': Sequelize.CHAR(10), 'allowNull': false }, 'department': { 'type': Sequelize.STRING(64), 'allowNull': true } }, { // 自定义表名 'freezeTableName': true, 'tableName': 'xyz_users', // 是否需要增加createdAt、updatedAt、deletedAt字段 'timestamps': true, // 不需要createdAt字段 'createdAt': false, // 将updatedAt字段改个名 'updatedAt': 'utime' // 将deletedAt字段改名 // 同时需要设置paranoid为true(此种模式下,删除数据时不会进行物理删除,而是设置deletedAt为当前时间 'deletedAt': 'dtime', 'paranoid': true });