DefinitionTo define mappings between a model and a table, use the 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 }) You can also set some options on each column: 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'} // 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 typesBelow 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.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.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) The BLOB data type allows you to insert data both as strings and as buffers. When you do a find or findAll on a model which has a BLOB column. that data will always be returned as a buffer. If you are working with the PostgreSQL TIMESTAMP WITHOUT TIME ZONE and you need to parse it to a different timezone, please use the pg library's own parser: require('pg').types.setTypeParser(1114, function(stringValue) { return new Date(stringValue + "+0000"); // e.g., UTC offset. Use any offset that you would like. }); In addition to the type mentioned above, integer, bigint, float and
double also support unsigned and zerofill properties, which can be
combined in any order: 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 The examples above only show integer, but the same can be done with bigint and float Usage in object notation: // for enums: sequelize.define('model', { states: { type: Sequelize.ENUM, values: ['active', 'pending', 'deleted'] } }) DeferrableWhen you specify a foreign key column it is optionally possible to declare the deferrable // 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 The last option is the default in PostgreSQL and won't allow you to dynamically change Getters & settersIt is possible to define 'object-property' getters and setter functions on your models, these can be used both for 'protecting' properties that map to database fields and for defining 'pseudo' properties. Getters and Setters can be defined in 2 ways (you can mix and match these 2 approaches):
N.B: If a getter or setter is defined in both places then the function found in the relevant property definition will always take precedence. Defining as part of a propertyvar 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 }) Defining as part of the model optionsBelow is an example of defining the getters and setters in the model options. The Note that the 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
/* a getter for 'title' property */ function() { return this.getDataValue('title'); }
/* a setter for 'title' property */ function(title) { return this.setDataValue('title', title.toString().toLowerCase()); } N.B: It is important to stick to using the ValidationsModel validations, allow you to specify format/content/inheritance validations for each attribute of the model. Validations are automatically run on The validations are implemented by validator. 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 (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 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 } } } } }) Note that where multiple arguments need to be passed to the built-in
validation functions, the arguments to be passed must be in an array.
But if a single array argument is to be passed, for instance an array of
acceptable strings for To use a custom error message instead of that provided by node-validator, use an object instead of the plain value or array of arguments, for example a validator which needs no argument can be given a custom message with isInt: { msg: "Must be an integer number of pennies" } or if arguments need to also be passed add an isIn: { args: [['en', 'zh']], msg: "Must be English or Chinese" } When using custom validator functions the error message will be whatever message the thrown See the node-validator projectfor more details on the built in validation methods. Hint: You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged. Validators and
|