Objection.js: Unique Validation

[shnhrrsn]

Perhaps I missed this in the docs, but I didn't see anything about validating uniqueness of a field.

Let's say I have a post model with a slug field, which is unique. How would I handle validation so a useful error message is thrown if a post is inserted with a slug that already exists? $beforeValidate doesn't allow a promise to be returned to do the look up, and inserting blindly results in a generic SQL error being thrown.

[koskimas]

You can do the lookup in $beforeUpdate and $beforeInsert methods.

knex (that takes care of the database communication) returns the errors thrown by the database clients. Knex has no unified error interface, and I don't want to maintain one. I think this is more a knex issue than objection.js.

I usually map the generic database error into a error result in a request handler middleware. For example postgres errors have some structure that you can use to detect them:

function isPostgresError(error) {
  if (!error) { return false; }
  // Just check the existence of a bunch of attributes. There doesn't seem to be an easier way.
  return _.all(['severity', 'code', 'detail', 'internalQuery', 'routine'], function(attr) {
    return _.has(error, attr);
  });
}

function parseUniqueViolationError(err) {
  // Parse the failed attribute name and value from the error message.
  var attrs = err.detail.match(/\((.+)\)=/);
  var values = err.detail.match(/=\((.+)\)/);

  if (attrs.length !== 0 && values.length !== 0) {
    attrs = attrs[1].split(', ');
    values = values[1].split(', ');

    var data = _.reduce(_.zipObject(attrs, values), function (result, value, attr) {
      while (attr.indexOf('"') !== -1) {
        attr = attr.replace('"', '');
      }
      result[attr] = 'Entity with "' + attr + '"="' + value + '" already exists';
      return result;
    }, {});

    return new ConflictError(data).toJSON();
  }

  return null;
}

Hacky, I know, but this is the best of the bad options.

[shnhrrsn]

Thanks, I ended up going with the $beforeInsert/$beforeUpdate approach since we're using different DBs in prod (maria) vs local (sqlite) -- didn't seem prudent to start detecting and parsing that way.

For anyone else who happens to stumble across this, this is what it looks like in practice:

$beforeInsert() {
    return super.$beforeInsert().then(() => {
        return this.constructor.query().select('id').where('slug', this.slug).first().then((row) => {
            if(typeof row === 'object' && row.id) {
                throw new BadRequestError('Slug already exists')
            }
        })
    })
}

[vietthang]

Hi @shnhrrsn, your solution works perfectly with insert, but in update, it will fail if the 'slug' you update is same as the 'slug' in database, which is a positive case.

Eg:

model.$query().patch({ slug: model.slug }) // update with same slug

It should update success but fails with your solution.

So I replace this code:

this.constructor.query().select('id').where('slug', this.slug)

to this

this.constructor.query().select('id').where('slug', this.slug).whereNot('id', this.$id())

But then the 'this' object in $beforeUpdate doesn't have id attribute. So I stuck here.

So, how do you handle the update case?

[shnhrrsn]

@vietthang Sorry the code I posted was incomplete, my actual code looks more like this:

$beforeInsert() {
    return this.$beforeSave(true)
}

$beforeUpdate() {
    return this.$beforeSave(false)
}

$beforeSave(inserting) {
    return this.constructor.query().select('id').where('slug', this.slug).first().then(row => {
        if(typeof row === 'object' && row.id) {
            if(inserting || Number.parseInt(row.id) !== Number.parseInt(this.id)) {
                throw new ValidationError({
                    slug: `Slug is already in use by #${row.id}`
                })
            }
        }
    })
}

That said, I haven't run into a scenario where the model I'm updating doesn't have an id. You _could_ add a step that checks if there's an id, and if there's not first look up the id for the slug:

$beforeUpdate() {
    if(this.id) {
        return this.$beforeSave(false)
    }

    return this.constructor.knexQuery().select('id').where('slug', this.slug).then(row => {
        this.id = row.id
        return this.$beforeSave(false)
    }
}

However I wouldn't advise doing this here — you should do it in your route logic. Take this scenario:

PATCH /posts/original-slug
{
   "slug": "new-slug",
   "title": "New Title"
}

PostModel.query().where('slug', 'original-slug').patch({
    slug: 'new-slug',
    title: 'New Title'
})

If you run that, the previous code is actually going to check if new-slug exists, not original-slug and that’s definitely not ideal. Instead, you could do this:

PostModel.query().where('slug', 'original-slug').first().then(post => {
    if(!post) {
        throw Error('Post not found')
    }

    return post.$query().patch({
        slug: 'new-slug',
        title: 'New Title'
    })
})

Now you’ll have a fully formed post object with id, and when you patch, everything should work as expected.

[vietthang]

First, sorry for the late response. As I understand from investigating the library source code, when you call:

post.$query().patch({
  slug: 'new-slug',
  title: 'New Title'
})

objection will create new model from this object

{
  slug: 'new-slug',
  title: 'New Title'
}

and this model will be used to construct a UpdateOperation and run $beforeUpdate in onBeforeInternal (UpdateOperation.js). The problem is, the model which newly construct only contains slug and title keys and not id key. So the this.id in $beforeUpdate/$beforeSave from your code is always undefined. It is impossible to validate unique key at Model level. I can do it at controller level, but so I have to repeat it in every saving function.

@koskimas @shnhrrsn: please provide some insight about this.

[koskimas]

@vietthang in 0.6 you will be able to access the old values from opt.old object. opt is the first argument passed to $beforeUpdate. You can already try this out by installing the newest rc version using npm install objection@next. There are some breaking changes in 0.6 though. Check out the changes in here.

[koskimas]

This issue explains how the hooks work. They are a bit complex because objection is more of a query builder than an ORM.

[vietthang]

@koskimas thank you for your information. I'm gonna try it soon.

[vietthang]

I tried your suggestion and it works. Just curious, as I understand, it won't work with patchAndFetchById right? Can you include the id to update in params as well? Or maybe some way more generic like the query?