Instances

Building a non-persistent instance

In order to create instances of defined classes just do as follows. You might recognize the syntax if you coded Ruby in the past. Using the build-method will return an unsaved object, which you explicitly have to save.

  1. const project = Project.build({
  2. title: 'my awesome project',
  3. description: 'woot woot. this will make me a rich man'
  4. })
  5. const task = Task.build({
  6. title: 'specify the project idea',
  7. description: 'bla',
  8. deadline: new Date()
  9. })

Built instances will automatically get default values when they were defined:

  1. // first define the model
  2. const Task = sequelize.define('task', {
  3. title: Sequelize.STRING,
  4. rating: { type: Sequelize.STRING, defaultValue: 3 }
  5. })
  6. // now instantiate an object
  7. const task = Task.build({title: 'very important task'})
  8. task.title // ==> 'very important task'
  9. task.rating // ==> 3

To get it stored in the database, use the save-method and catch the events … if needed:

  1. project.save().then(() => {
  2. // my nice callback stuff
  3. })
  4. task.save().catch(error => {
  5. // mhhh, wth!
  6. })
  7. // you can also build, save and access the object with chaining:
  8. Task
  9. .build({ title: 'foo', description: 'bar', deadline: new Date() })
  10. .save()
  11. .then(anotherTask => {
  12. // you can now access the currently saved task with the variable anotherTask... nice!
  13. })
  14. .catch(error => {
  15. // Ooops, do some error-handling
  16. })

Creating persistent instances

While an instance created with .build() requires an explicit .save() call to be stored in the database, .create() omits that requirement altogether and automatically stores your instance's data once called.

  1. Task.create({ title: 'foo', description: 'bar', deadline: new Date() }).then(task => {
  2. // you can now access the newly created task via the variable task
  3. })

It is also possible to define which attributes can be set via the create method. This can be especially very handy if you create database entries based on a form which can be filled by a user. Using that would for example allow you to restrict the User model to set only a username and an address but not an admin flag:

  1. User.create({ username: 'barfooz', isAdmin: true }, { fields: [ 'username' ] }).then(user => {
  2. // let's assume the default of isAdmin is false:
  3. console.log(user.get({
  4. plain: true
  5. })) // => { username: 'barfooz', isAdmin: false }
  6. })

Updating / Saving / Persisting an instance

Now lets change some values and save changes to the database… There are two ways to do that:

  1. // way 1
  2. task.title = 'a very different title now'
  3. task.save().then(() => {})
  4. // way 2
  5. task.update({
  6. title: 'a very different title now'
  7. }).then(() => {})

It's also possible to define which attributes should be saved when calling save, by passing an array of column names. This is useful when you set attributes based on a previously defined object. E.g. if you get the values of an object via a form of a web app. Furthermore this is used internally for update. This is how it looks like:

  1. task.title = 'foooo'
  2. task.description = 'baaaaaar'
  3. task.save({fields: ['title']}).then(() => {
  4. // title will now be 'foooo' but description is the very same as before
  5. })
  6. // The equivalent call using update looks like this:
  7. task.update({ title: 'foooo', description: 'baaaaaar'}, {fields: ['title']}).then(() => {
  8. // title will now be 'foooo' but description is the very same as before
  9. })

When you call save without changing any attribute, this method will execute nothing;

Destroying / Deleting persistent instances

Once you created an object and got a reference to it, you can delete it from the database. The relevant method is destroy:

  1. Task.create({ title: 'a task' }).then(task => {
  2. // now you see me...
  3. return task.destroy();
  4. }).then(() => {
  5. // now i'm gone :)
  6. })

If the paranoid options is true, the object will not be deleted, instead the deletedAt column will be set to the current timestamp. To force the deletion, you can pass force: true to the destroy call:

  1. task.destroy({ force: true })

Working in bulk (creating, updating and destroying multiple rows at once)

In addition to updating a single instance, you can also create, update, and delete multiple instances at once. The functions you are looking for are called

  • Model.bulkCreate
  • Model.update
  • Model.destroy

Since you are working with multiple models, the callbacks will not return DAO instances. BulkCreate will return an array of model instances/DAOs, they will however, unlike create, not have the resulting values of autoIncrement attributes.update and destroy will return the number of affected rows.

First lets look at bulkCreate

  1. User.bulkCreate([
  2. { username: 'barfooz', isAdmin: true },
  3. { username: 'foo', isAdmin: true },
  4. { username: 'bar', isAdmin: false }
  5. ]).then(() => { // Notice: There are no arguments here, as of right now you'll have to...
  6. return User.findAll();
  7. }).then(users => {
  8. console.log(users) // ... in order to get the array of user objects
  9. })

To update several rows at once:

  1. Task.bulkCreate([
  2. {subject: 'programming', status: 'executing'},
  3. {subject: 'reading', status: 'executing'},
  4. {subject: 'programming', status: 'finished'}
  5. ]).then(() => {
  6. return Task.update(
  7. { status: 'inactive' }, /* set attributes' value */
  8. { where: { subject: 'programming' }} /* where criteria */
  9. );
  10. }).spread((affectedCount, affectedRows) => {
  11. // .update returns two values in an array, therefore we use .spread
  12. // Notice that affectedRows will only be defined in dialects which support returning: true
  13. // affectedCount will be 2
  14. return Task.findAll();
  15. }).then(tasks => {
  16. console.log(tasks) // the 'programming' tasks will both have a status of 'inactive'
  17. })

And delete them:

  1. Task.bulkCreate([
  2. {subject: 'programming', status: 'executing'},
  3. {subject: 'reading', status: 'executing'},
  4. {subject: 'programming', status: 'finished'}
  5. ]).then(() => {
  6. return Task.destroy({
  7. where: {
  8. subject: 'programming'
  9. },
  10. truncate: true /* this will ignore where and truncate the table instead */
  11. });
  12. }).then(affectedRows => {
  13. // affectedRows will be 2
  14. return Task.findAll();
  15. }).then(tasks => {
  16. console.log(tasks) // no programming, just reading :(
  17. })

If you are accepting values directly from the user, it might be beneficial to limit the columns that you want to actually insert.bulkCreate()accepts an options object as the second parameter. The object can have a fields parameter, (an array) to let it know which fields you want to build explicitly

  1. User.bulkCreate([
  2. { username: 'foo' },
  3. { username: 'bar', admin: true}
  4. ], { fields: ['username'] }).then(() => {
  5. // nope bar, you can't be admin!
  6. })

bulkCreate was originally made to be a mainstream/fast way of inserting records, however, sometimes you want the luxury of being able to insert multiple rows at once without sacrificing model validations even when you explicitly tell Sequelize which columns to sift through. You can do by adding a validate: true property to the options object.

  1. const Tasks = sequelize.define('task', {
  2. name: {
  3. type: Sequelize.STRING,
  4. validate: {
  5. notNull: { args: true, msg: 'name cannot be null' }
  6. }
  7. },
  8. code: {
  9. type: Sequelize.STRING,
  10. validate: {
  11. len: [3, 10]
  12. }
  13. }
  14. })
  15. Tasks.bulkCreate([
  16. {name: 'foo', code: '123'},
  17. {code: '1234'},
  18. {name: 'bar', code: '1'}
  19. ], { validate: true }).catch(errors => {
  20. /* console.log(errors) would look like:
  21. [
  22. { record:
  23. ...
  24. name: 'SequelizeBulkRecordError',
  25. message: 'Validation error',
  26. errors:
  27. { name: 'SequelizeValidationError',
  28. message: 'Validation error',
  29. errors: [Object] } },
  30. { record:
  31. ...
  32. name: 'SequelizeBulkRecordError',
  33. message: 'Validation error',
  34. errors:
  35. { name: 'SequelizeValidationError',
  36. message: 'Validation error',
  37. errors: [Object] } }
  38. ]
  39. */
  40. })

Values of an instance

If you log an instance you will notice, that there is a lot of additional stuff. In order to hide such stuff and reduce it to the very interesting information, you can use theget-attribute. Calling it with the option plain = true will only return the values of an instance.

  1. Person.create({
  2. name: 'Rambow',
  3. firstname: 'John'
  4. }).then(john => {
  5. console.log(john.get({
  6. plain: true
  7. }))
  8. })
  9. // result:
  10. // { name: 'Rambow',
  11. // firstname: 'John',
  12. // id: 1,
  13. // createdAt: Tue, 01 May 2012 19:12:16 GMT,
  14. // updatedAt: Tue, 01 May 2012 19:12:16 GMT
  15. // }

Hint:You can also transform an instance into JSON by using JSON.stringify(instance). This will basically return the very same as values.

Reloading instances

If you need to get your instance in sync, you can use the methodreload. It will fetch the current data from the database and overwrite the attributes of the model on which the method has been called on.

  1. Person.findOne({ where: { name: 'john' } }).then(person => {
  2. person.name = 'jane'
  3. console.log(person.name) // 'jane'
  4. person.reload().then(() => {
  5. console.log(person.name) // 'john'
  6. })
  7. })

Incrementing

In order to increment values of an instance without running into concurrency issues, you may use increment.

First of all you can define a field and the value you want to add to it.

  1. User.findById(1).then(user => {
  2. return user.increment('my-integer-field', {by: 2})
  3. }).then(user => {
  4. // Postgres will return the updated user by default (unless disabled by setting { returning: false })
  5. // In other dialects, you'll want to call user.reload() to get the updated instance...
  6. })

Second, you can define multiple fields and the value you want to add to them.

  1. User.findById(1).then(user => {
  2. return user.increment([ 'my-integer-field', 'my-very-other-field' ], {by: 2})
  3. }).then(/* ... */)

Third, you can define an object containing fields and its increment values.

  1. User.findById(1).then(user => {
  2. return user.increment({
  3. 'my-integer-field': 2,
  4. 'my-very-other-field': 3
  5. })
  6. }).then(/* ... */)

Decrementing

In order to decrement values of an instance without running into concurrency issues, you may use decrement.

First of all you can define a field and the value you want to add to it.

  1. User.findById(1).then(user => {
  2. return user.decrement('my-integer-field', {by: 2})
  3. }).then(user => {
  4. // Postgres will return the updated user by default (unless disabled by setting { returning: false })
  5. // In other dialects, you'll want to call user.reload() to get the updated instance...
  6. })

Second, you can define multiple fields and the value you want to add to them.

  1. User.findById(1).then(user => {
  2. return user.decrement([ 'my-integer-field', 'my-very-other-field' ], {by: 2})
  3. }).then(/* ... */)

Third, you can define an object containing fields and its decrement values.

  1. User.findById(1).then(user => {
  2. return user.decrement({
  3. 'my-integer-field': 2,
  4. 'my-very-other-field': 3
  5. })
  6. }).then(/* ... */)