Definition

To define mappings between a model and a table, use the define method. Sequelize will then automatically add the attributes createdAt and updatedAt to it. So you will be able to know when the database entry went into the db and when it was updated the last time. If you do not want timestamps on your models, only want some timestamps, or you are working with an existing database where the columns are named something else, jump straight on to configuration to see how to do that.

  1. var Project = sequelize.define('project', {
  2. title: Sequelize.STRING,
  3. description: Sequelize.TEXT
  4. })
  5. var Task = sequelize.define('task', {
  6. title: Sequelize.STRING,
  7. description: Sequelize.TEXT,
  8. deadline: Sequelize.DATE
  9. })

You can also set some options on each column:

  1. var Foo = sequelize.define('foo', {
  2. // instantiating will automatically set the flag to true if not set
  3. flag: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: true},
  4. // default values for dates => current time
  5. myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },
  6. // setting allowNull to false will add NOT NULL to the column, which means an error will be
  7. // thrown from the DB when the query is executed if the column is null. If you want to check that a value
  8. // is not null before querying the DB, look at the validations section below.
  9. title: { type: Sequelize.STRING, allowNull: false},
  10. // Creating two objects with the same value will throw an error. The unique property can be either a
  11. // boolean, or a string. If you provide the same string for multiple columns, they will form a
  12. // composite unique key.
  13. someUnique: {type: Sequelize.STRING, unique: true},
  14. uniqueOne: { type: Sequelize.STRING, unique: 'compositeIndex'},
  15. uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex'}
  16. // The unique property is simply a shorthand to create a unique index.
  17. someUnique: {type: Sequelize.STRING, unique: true}
  18. // It's exactly the same as creating the index in the model's options.
  19. {someUnique: {type: Sequelize.STRING}},
  20. {indexes: [{unique: true, fields: ['someUnique']}]}
  21. // Go on reading for further information about primary keys
  22. identifier: { type: Sequelize.STRING, primaryKey: true},
  23. // autoIncrement can be used to create auto_incrementing integer columns
  24. incrementMe: { type: Sequelize.INTEGER, autoIncrement: true },
  25. // Comments can be specified for each field for MySQL and PG
  26. hasComment: { type: Sequelize.INTEGER, comment: "I'm a comment!" },
  27. // You can specify a custom field name via the "field" attribute:
  28. fieldWithUnderscores: { type: Sequelize.STRING, field: "field_with_underscores" },
  29. // It is possible to create foreign keys:
  30. bar_id: {
  31. type: Sequelize.INTEGER,
  32. references: {
  33. // This is a reference to another model
  34. model: Bar,
  35. // This is the column name of the referenced model
  36. key: 'id',
  37. // This declares when to check the foreign key constraint. PostgreSQL only.
  38. deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
  39. }
  40. }
  41. })

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.

  1. Sequelize.STRING // VARCHAR(255)
  2. Sequelize.STRING(1234) // VARCHAR(1234)
  3. Sequelize.STRING.BINARY // VARCHAR BINARY
  4. Sequelize.TEXT // TEXT
  5. Sequelize.TEXT('tiny') // TINYTEXT
  6. Sequelize.INTEGER // INTEGER
  7. Sequelize.BIGINT // BIGINT
  8. Sequelize.BIGINT(11) // BIGINT(11)
  9. Sequelize.FLOAT // FLOAT
  10. Sequelize.FLOAT(11) // FLOAT(11)
  11. Sequelize.FLOAT(11, 12) // FLOAT(11,12)
  12. Sequelize.REAL // REAL PostgreSQL only.
  13. Sequelize.REAL(11) // REAL(11) PostgreSQL only.
  14. Sequelize.REAL(11, 12) // REAL(11,12) PostgreSQL only.
  15. Sequelize.DOUBLE // DOUBLE
  16. Sequelize.DOUBLE(11) // DOUBLE(11)
  17. Sequelize.DOUBLE(11, 12) // DOUBLE(11,12)
  18. Sequelize.DECIMAL // DECIMAL
  19. Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)
  20. Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
  21. Sequelize.DATE(6) // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
  22. Sequelize.DATEONLY // DATE without time.
  23. Sequelize.BOOLEAN // TINYINT(1)
  24. Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
  25. Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.
  26. Sequelize.ARRAY(Sequelize.ENUM) // Defines an array of enum. PostgreSQL only.
  27. Sequelize.JSON // JSON column. PostgreSQL only.
  28. Sequelize.JSONB // JSONB column. PostgreSQL only.
  29. Sequelize.BLOB // BLOB (bytea for PostgreSQL)
  30. Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
  31. 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)
  32. Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only.
  33. Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only.
  34. Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only.
  35. Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only.
  36. Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only.
  37. Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.
  38. Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only.
  39. Sequelize.GEOMETRY('POINT') // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
  40. Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geometry type and SRID. PostgreSQL (with PostGIS) or MySQL only.

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:

  1. require('pg').types.setTypeParser(1114, function(stringValue) {
  2. return new Date(stringValue + "+0000");
  3. // e.g., UTC offset. Use any offset that you would like.
  4. });

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:Be aware that this does not apply for PostgreSQL!

  1. Sequelize.INTEGER.UNSIGNED // INTEGER UNSIGNED
  2. Sequelize.INTEGER(11).UNSIGNED // INTEGER(11) UNSIGNED
  3. Sequelize.INTEGER(11).ZEROFILL // INTEGER(11) ZEROFILL
  4. Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
  5. 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:

  1. // for enums:
  2. sequelize.define('model', {
  3. states: {
  4. type: Sequelize.ENUM,
  5. values: ['active', 'pending', 'deleted']
  6. }
  7. })

Deferrable

When you specify a foreign key column it is optionally possible to declare the deferrabletype in PostgreSQL. The following options are available:

  1. // Defer all foreign key constraint check to the end of a transaction
  2. Sequelize.Deferrable.INITIALLY_DEFERRED
  3. // Immediately check the foreign key constraints
  4. Sequelize.Deferrable.INITIALLY_IMMEDIATE
  5. // Don't defer the checks at all
  6. Sequelize.Deferrable.NOT

The last option is the default in PostgreSQL and won't allow you to dynamically changethe rule in a transaction. See the transaction section for further information.

Getters & setters

It 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):

  • as part of a single property definition
  • as part of a model options

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 property

  1. var Employee = sequelize.define('employee', {
  2. name: {
  3. type : Sequelize.STRING,
  4. allowNull: false,
  5. get : function() {
  6. var title = this.getDataValue('title');
  7. // 'this' allows you to access attributes of the instance
  8. return this.getDataValue('name') + ' (' + title + ')';
  9. },
  10. },
  11. title: {
  12. type : Sequelize.STRING,
  13. allowNull: false,
  14. set : function(val) {
  15. this.setDataValue('title', val.toUpperCase());
  16. }
  17. }
  18. });
  19. Employee
  20. .create({ name: 'John Doe', title: 'senior engineer' })
  21. .then(function(employee) {
  22. console.log(employee.get('name')); // John Doe (SENIOR ENGINEER)
  23. console.log(employee.get('title')); // SENIOR ENGINEER
  24. })

Defining as part of the model options

Below is an example of defining the getters and setters in the model options. The fullName getter, is an example of how you can define pseudo properties on your models - attributes which are not actually part of your database schema. In fact, pseudo properties can be defined in two ways: using model getters, or by using a column with the VIRTUAL datatype. Virtual datatypes can have validations, while getters for virtual attributes cannot.

Note that the this.firstname and this.lastname references in the fullName getter function will trigger a call to the respective getter functions. If you do not want that then use the getDataValue() method to access the raw value (see below).

  1. var Foo = sequelize.define('foo', {
  2. firstname: Sequelize.STRING,
  3. lastname: Sequelize.STRING
  4. }, {
  5. getterMethods : {
  6. fullName : function() { return this.firstname + ' ' + this.lastname }
  7. },
  8. setterMethods : {
  9. fullName : function(value) {
  10. var names = value.split(' ');
  11. this.setDataValue('firstname', names.slice(0, -1).join(' '));
  12. this.setDataValue('lastname', names.slice(-1).join(' '));
  13. },
  14. }
  15. });

Helper functions for use inside getter and setter definitions

  • retrieving an underlying property value - always use this.getDataValue()
  1. /* a getter for 'title' property */
  2. function() {
  3. return this.getDataValue('title');
  4. }
  • setting an underlying property value - always use this.setDataValue()
  1. /* a setter for 'title' property */
  2. function(title) {
  3. return this.setDataValue('title', title.toString().toLowerCase());
  4. }

N.B: It is important to stick to using the setDataValue() and getDataValue() functions (as opposed to accessing the underlying "data values" property directly) - doing so protects your custom getters and setters from changes in the underlying model implementations.

Validations

Model validations, allow you to specify format/content/inheritance validations for each attribute of the model.

Validations are automatically run on create, update and save. You can also call validate() to manually validate an instance.

The validations are implemented by validator.js.

  1. var ValidateMe = sequelize.define('foo', {
  2. foo: {
  3. type: Sequelize.STRING,
  4. validate: {
  5. is: ["^[a-z]+$",'i'], // will only allow letters
  6. is: /^[a-z]+$/i, // same as the previous example using real RegExp
  7. not: ["[a-z]",'i'], // will not allow letters
  8. isEmail: true, // checks for email format (foo@bar.com)
  9. isUrl: true, // checks for url format (http://foo.com)
  10. isIP: true, // checks for IPv4 (129.89.23.1) or IPv6 format
  11. isIPv4: true, // checks for IPv4 (129.89.23.1)
  12. isIPv6: true, // checks for IPv6 format
  13. isAlpha: true, // will only allow letters
  14. isAlphanumeric: true, // will only allow alphanumeric characters, so "_abc" will fail
  15. isNumeric: true, // will only allow numbers
  16. isInt: true, // checks for valid integers
  17. isFloat: true, // checks for valid floating point numbers
  18. isDecimal: true, // checks for any numbers
  19. isLowercase: true, // checks for lowercase
  20. isUppercase: true, // checks for uppercase
  21. notNull: true, // won't allow null
  22. isNull: true, // only allows null
  23. notEmpty: true, // don't allow empty strings
  24. equals: 'specific value', // only allow a specific value
  25. contains: 'foo', // force specific substrings
  26. notIn: [['foo', 'bar']], // check the value is not one of these
  27. isIn: [['foo', 'bar']], // check the value is one of these
  28. notContains: 'bar', // don't allow specific substrings
  29. len: [2,10], // only allow values with length between 2 and 10
  30. isUUID: 4, // only allow uuids
  31. isDate: true, // only allow date strings
  32. isAfter: "2011-11-05", // only allow date strings after a specific date
  33. isBefore: "2011-11-05", // only allow date strings before a specific date
  34. max: 23, // only allow values
  35. min: 23, // only allow values >= 23
  36. isArray: true, // only allow arrays
  37. isCreditCard: true, // check for valid credit card numbers
  38. // custom validations are also possible:
  39. isEven: function(value) {
  40. if(parseInt(value) % 2 != 0) {
  41. throw new Error('Only even values are allowed!')
  42. // we also are in the model's context here, so this.otherField
  43. // would get the value of otherField if it existed
  44. }
  45. }
  46. }
  47. }
  48. });

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 isIn, this will be interpreted as multiple string arguments instead of one array argument. To work around this pass a single-length array of arguments, such as [['one', 'two']] as shown above.

To use a custom error message instead of that provided by validator.js, 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

  1. isInt: {
  2. msg: "Must be an integer number of pennies"
  3. }

or if arguments need to also be passed add anargsproperty:

  1. isIn: {
  2. args: [['en', 'zh']],
  3. msg: "Must be English or Chinese"
  4. }

When using custom validator functions the error message will be whatever message the thrownErrorobject holds.

See the validator.js project for 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 allowNull

If a particular field of a model is set to allow null (with allowNull: true) and that value has been set to null , its validators do not run. This means you can, for instance, have a string field which validates its length to be at least 5 characters, but which also allowsnull.

Model validations

Validations can also be defined to check the model after the field-specific validators. Using this you could, for example, ensure either neither of latitude and longitude are set or both, and fail if one but not the other is set.

Model validator methods are called with the model object's context and are deemed to fail if they throw an error, otherwise pass. This is just the same as with custom field-specific validators.

Any error messages collected are put in the validation result object alongside the field validation errors, with keys named after the failed validation method's key in the validate option object. Even though there can only be one error message for each model validation method at any one time, it is presented as a single string error in an array, to maximize consistency with the field errors.

An example:

  1. var Pub = Sequelize.define('pub', {
  2. name: { type: Sequelize.STRING },
  3. address: { type: Sequelize.STRING },
  4. latitude: {
  5. type: Sequelize.INTEGER,
  6. allowNull: true,
  7. defaultValue: null,
  8. validate: { min: -90, max: 90 }
  9. },
  10. longitude: {
  11. type: Sequelize.INTEGER,
  12. allowNull: true,
  13. defaultValue: null,
  14. validate: { min: -180, max: 180 }
  15. },
  16. }, {
  17. validate: {
  18. bothCoordsOrNone: function() {
  19. if ((this.latitude === null) !== (this.longitude === null)) {
  20. throw new Error('Require either both latitude and longitude or neither')
  21. }
  22. }
  23. }
  24. })

In this simple case an object fails validation if either latitude or longitude is given, but not both. If we try to build one with an out-of-range latitude and no longitude, raging_bullock_arms.validate() might return

  1. {
  2. 'latitude': ['Invalid number: latitude'],
  3. 'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
  4. }

Configuration

You can also influence the way Sequelize handles your column names:

  1. var Bar = sequelize.define('bar', { /* bla */ }, {
  2. // don't add the timestamp attributes (updatedAt, createdAt)
  3. timestamps: false,
  4. // don't delete database entries but set the newly added attribute deletedAt
  5. // to the current date (when deletion was done). paranoid will only work if
  6. // timestamps are enabled
  7. paranoid: true,
  8. // don't use camelcase for automatically added attributes but underscore style
  9. // so updatedAt will be updated_at
  10. underscored: true,
  11. // disable the modification of table names; By default, sequelize will automatically
  12. // transform all passed model names (first parameter of define) into plural.
  13. // if you don't want that, set the following
  14. freezeTableName: true,
  15. // define the table's name
  16. tableName: 'my_very_custom_table_name'
  17. })

If you want sequelize to handle timestamps, but only want some of them, or want your timestamps to be called something else, you can override each column individually:

  1. var Foo = sequelize.define('foo', { /* bla */ }, {
  2. // don't forget to enable timestamps!
  3. timestamps: true,
  4. // I don't want createdAt
  5. createdAt: false,
  6. // I want updatedAt to actually be called updateTimestamp
  7. updatedAt: 'updateTimestamp'
  8. // And deletedAt to be called destroyTime (remember to enable paranoid for this to work)
  9. deletedAt: 'destroyTime',
  10. paranoid: true
  11. })

You can also change the database engine, e.g. to MyISAM. InnoDB is the default.

  1. var Person = sequelize.define('person', { /* attributes */ }, {
  2. engine: 'MYISAM'
  3. })
  4. // or globally
  5. var sequelize = new Sequelize(db, user, pw, {
  6. define: { engine: 'MYISAM' }
  7. })

Finally you can specify a comment for the table in MySQL and PG

  1. var Person = sequelize.define('person', { /* attributes */ }, {
  2. comment: "I'm a table comment!"
  3. })

Import

You can also store your model definitions in a single file using the import method. The returned object is exactly the same as defined in the imported file's function. Since v1:5.0 of Sequelize the import is cached, so you won't run into troubles when calling the import of a file twice or more often.

  1. // in your server file - e.g. app.js
  2. var Project = sequelize.import(__dirname + "/path/to/models/project")
  3. // The model definition is done in /path/to/models/project.js
  4. // As you might notice, the DataTypes are the very same as explained above
  5. module.exports = function(sequelize, DataTypes) {
  6. return sequelize.define("project", {
  7. name: DataTypes.STRING,
  8. description: DataTypes.TEXT
  9. })
  10. }

The import method can also accept a callback as an argument.

  1. sequelize.import('project', function(sequelize, DataTypes) {
  2. return sequelize.define("project", {
  3. name: DataTypes.STRING,
  4. description: DataTypes.TEXT
  5. })
  6. })

Database synchronization

When starting a new project you won't have a database structure and using Sequelize you won't need to. Just specify your model structures and let the library do the rest. Currently supported is the creation and deletion of tables:

  1. // Create the tables:
  2. Project.sync()
  3. Task.sync()
  4. // Force the creation!
  5. Project.sync({force: true}) // this will drop the table first and re-create it afterwards
  6. // drop the tables:
  7. Project.drop()
  8. Task.drop()
  9. // event handling:
  10. Project.[sync|drop]().then(function() {
  11. // ok ... everything is nice!
  12. }).catch(function(error) {
  13. // oooh, did you enter wrong database credentials?
  14. })

Because synchronizing and dropping all of your tables might be a lot of lines to write, you can also let Sequelize do the work for you:

  1. // Sync all models that aren't already in the database
  2. sequelize.sync()
  3. // Force sync all models
  4. sequelize.sync({force: true})
  5. // Drop all tables
  6. sequelize.drop()
  7. // emit handling:
  8. sequelize.[sync|drop]().then(function() {
  9. // woot woot
  10. }).catch(function(error) {
  11. // whooops
  12. })

Because .sync({ force: true }) is destructive operation, you can use match option as an additional safety check.match option tells sequelize to match a regex against the database name before syncing - a safety check for caseswhere force: true is used in tests but not live code.

  1. // This will run .sync() only if database name ends with '_test'
  2. sequelize.sync({ force: true, match: /_test$/ });

Expansion of models

Sequelize allows you to pass custom methods to a model and its instances. Just do the following:

  1. var Foo = sequelize.define('foo', { /* attributes */}, {
  2. classMethods: {
  3. method1: function(){ return 'smth' }
  4. },
  5. instanceMethods: {
  6. method2: function() { return 'foo' }
  7. }
  8. })
  9. // Example:
  10. Foo.method1()
  11. Foo.build().method2()

Of course you can also access the instance's data and generate virtual getters:

  1. var User = sequelize.define('user', { firstname: Sequelize.STRING, lastname: Sequelize.STRING }, {
  2. instanceMethods: {
  3. getFullname: function() {
  4. return [this.firstname, this.lastname].join(' ')
  5. }
  6. }
  7. })
  8. // Example:
  9. User.build({ firstname: 'foo', lastname: 'bar' }).getFullname() // 'foo bar'

You can also set custom methods to all of your models during the instantiation:

  1. var sequelize = new Sequelize('database', 'username', 'password', {
  2. // Other options during the initialization could be here
  3. define: {
  4. classMethods: {
  5. method1: function() {},
  6. method2: function() {}
  7. },
  8. instanceMethods: {
  9. method3: function() {}
  10. }
  11. }
  12. })
  13. // Example:
  14. var Foo = sequelize.define('foo', { /* attributes */});
  15. Foo.method1()
  16. Foo.method2()
  17. Foo.build().method3()

Indexes

Sequelize supports adding indexes to the model definition which will be created during Model.sync() or sequelize.sync.

  1. sequelize.define('user', {}, {
  2. indexes: [
  3. // Create a unique index on email
  4. {
  5. unique: true,
  6. fields: ['email']
  7. },
  8. // Creates a gin index on data with the jsonb_path_ops operator
  9. {
  10. fields: ['data'],
  11. using: 'gin',
  12. operator: 'jsonb_path_ops'
  13. },
  14. // By default index name will be [table]_[fields]
  15. // Creates a multi column partial index
  16. {
  17. name: 'public_by_author',
  18. fields: ['author', 'status'],
  19. where: {
  20. status: 'public'
  21. }
  22. },
  23. // A BTREE index with a ordered field
  24. {
  25. name: 'title_index',
  26. method: 'BTREE',
  27. fields: ['author', {attribute: 'title', collate: 'en_US', order: 'DESC', length: 5}]
  28. }
  29. ]
  30. })