Unique email validation before create new record

[raj-optisol]

Am trying to validate Email already exists validation in Bookshelf.js when creating new record.

I found one solution here github but its not working, even i tried with Promise

User = bookshelf.Model.extend({
  tableName: 'users',
  initialize: function() {
    this.on('saving', this._assertEmailUnique);
  },
  _assertEmailUnique: function(model, attributes, options) {
    if (this.hasChanged('email')) {
      return this
        .query('where', 'email', this.get('email'))
        .fetch(_.pick(options, 'transacting'))
        .then(function (existing) {
          if (!existing) throw new Error('duplicate email');
        });
    }
  }
});

For Model validation currently am using Joi, looks like Joi also not supporting for custom validation for this. Am using Postgres Database. There is any other way to do it.. Please help...

[vellotis]

Hi @raj-optisol
I think the example you found has a bug.

User = bookshelf.Model.extend({
  tableName: 'users',
  initialize: function() {
    this.on('saving', this._assertEmailUnique);
  },
  _assertEmailUnique: function(model, attributes, options) {
    if (this.hasChanged('email')) {
      return this
        .query('where', 'email', this.get('email'))
        .fetch(_.pick(options, 'transacting'))
        .then(function (existing) {

          // Currently it throw an error if 'email' is existing
          // existing = new bookshelf.Model()
          // console.log(!existing) // true
          if (!existing) throw new Error('duplicate email');

        });
    }
  }
});

So it should be...

if (existing) throw new Error('duplicate email');

[raj-optisol]

After removing this _.pick(options, 'transacting') from fetch() only query started executing.. But its not gave me desired output.

User = bookshelf.Model.extend({
  tableName: 'users',
  initialize: function() {
    this.on('saving', this._assertEmailUnique);
  },
  _assertEmailUnique: function(model, attributes, options) {
    if (this.hasChanged('email')) {
      return this
        .query('where', 'email', this.get('email'))
        .fetch()
        .then(function (existing) {

          // Currently it throw an error if 'email' is existing
          // existing = new bookshelf.Model()
          // console.log(!existing) // true
          if (existing) throw new Error('duplicate email');

        });
    }
  }
});

and also changed query like this .query('where', 'email', '=', this.get('email'))

I checked with existing email, its not finding anything in the DB.. returning like its new always..

[raj-optisol]

Fixed: Querying on this will constrain your query to the current record, you need to query from scratch, using plain User

User = bookshelf.Model.extend({
  tableName: 'users',
  initialize: function() {
    this.on('saving', this._assertEmailUnique);
  },
  _assertEmailUnique: function(model, attributes, options) {
    if (this.hasChanged('email')) {
      return User
        .query('where', 'email', this.get('email'))
        .fetch()
        .then(function(existing) {
          if (existing) {
            throw new Error('Duplicated email: User id #' + existing.id);
          }
        });
    }
  }
});

[rhys-vdw]

Just put a unique constraint on the column...

On Wed, Jul 6, 2016, 8:51 PM raj-optisol notifications@github.com wrote:

Closed #1313 #1313.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#1313 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AAyLWRUk6vWS6dE_Ma73suSk3WczZw8Lks5qS4ivgaJpZM4JDqK6
.

[raj-adroit]

@rhys-vdw If i add unique constraint on the column, it won't insert and throw error, How can i catch and show/parse that error ?

[vellotis]

@raj-optisol There is now way at the moment how to distinguish errors thrown by knex. knex/knex#522

[aj0strow]

Here's what I'm using. Start with a unique constraint.

// migration file

knex.table("table_name", t => {
  t.unique("unique_column_name")
  t.unique("compound_index", "part_two")
})

Then count duplicates on each create or save, respecting transactions, and filtering out the current model from the count.

function assertUnique(/* columns */) {
  const columns = _.toArray(arguments)
  if (columns.length === 0) {
    throw new Error("please pass unique columns")
  }
  return function(model, attributes, options) {
    const hasChanged = _.some(columns, column => {
      return model.hasChanged(column)
    })
    if (model.isNew() || hasChanged) {
      return this.constructor.query(q => {
        columns.forEach(column => {
          q.where(column, '=', model.get(column))
        })
        if (!model.isNew()) {
          q.where(model.idAttribute, '<>', model.id)
        }
      })
      .count({ transacting: options.transacting })
      .then(n => {
        if (n > 0) {
          return bluebird.reject({
            name: "DuplicateError",
            message: columns.join(", ")
          })
        }
      })
    }
  }
}

Then run the assertion for any model with unique constraints.

// users
  this.on("saving", assertUnique("email"))

// posts
  this.on("saving", assertUnique("md5_hash"))
  this.on("saving", assertUnique("user_id", "title"))

It should work for most use cases.

[martinmurciego]

#1047