sequelize用法-model定义

定义模型

let Sequelize = require('sequelize');
let db = require("./init");
let MD5 = require('crypto').createHash('md5');
let InfoModel = require('./info');
let User = db.define('user', {
    id: {
        type: Sequelize.INTEGER, // 数据类型
        field: 'id', // 数据库中字段真是名称，默认就是属性名
        primaryKey: true, // 是否为主键
        unique: true, //是否可重复
        autoIncrement: true //没有这个时插入返回是id是null
    },
    username: {
        type: Sequelize.STRING,
        allowNull: false // 不允许为空
    },
    password: {
        type: Sequelize.STRING,
        //allowNull: false,
        defaultValue: '123456', // 默认值
        set(val) {
            /*
            这是set 当插入或者修改时去处理这个值。对应还get就是取这个值
            */
            return MD5.update(val).digest('hex');
        }
    },
    infoId: {
        type: Sequelize.INTEGER,
        field:'info_id',
        /*
        外键 model是对应的模型，key是外键链接的字段
        */
        references: {
            model: InfoModel,
            key: 'id'
        }
    },
    age: {
        type: Sequelize.INTEGER,
        /* 数据校验返回异常 customFunc自定义的校验*/
        validate: {
            max: {
                args: 100,
                msg: "age is larger"
            },
            min: {
                args: 1, // 不能为0 
                msg: 'age is small'
            },
            customFunc(val){
                if(val===50){
                    console.log('dddd');
                    //error中的string就相当于min中的msg
                    throw new Error('Only even values are allowed!')
                }
            }
        }
    },
    state: {
        type: Sequelize.ENUM,
        values: [0,1,2,3,4,5]
    }
}, {
        freezeTableName: true, // Model 对应的表名将与model名相同
        tableName: 'test_user',
        /*
        setterMethods,getterMethods这个是相当与在存取时都添加了changeName这个虚拟字段
        */
        setterMethods:{
            changeName(val){
                return this.setDataValue('username', val.slice(0, -1));
            }
        },
        getterMethods:{
            changeName(){
                return this.username+'changeName';
            }
        },
        // createdAt: 'createdAt', // 修改createAt在数据库中真是的字段名
        // updatedAt: 'updateAt',
        timestamps: true,
        underscored: true, // createdAt&updatedAt=>created_at&update_at
    });
module.exports = User;

说明

存在的数据类型有

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, SQLite and MySQL 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.

Validations

自带的校验有

      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 poem format (foo@bar.com)
      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 <= 23
      min: 23,                  // only allow values >= 23
      isCreditCard: true,       // check for valid credit card numbers

其实都是可以自定义返回类型的例如 max:20,可以写成 max:{ args:20,msg:’number is big’},在自定义方法中校验不合法时 throw new Error(‘Only even values are allowed!’)抛出msg
返回的错信息在这个model的读取操作的失败的回调Rejected中的参数中
结构如下

interface err{
{
    "name": "SequelizeValidationError",
    "errors": [
        {
            "message": "Only even values are allowed!", //错误信息
            "type": "Validation error",
            "path": "age", //字段名
            "value": 50, //实际的值
            "__raw": {}
        }
    ]
}