AdonisJS provides a lot of really nice stuff out of the box for rapid development of an application, but as with any framework occasionally there are bits missing that would be useful. A validator that works for compound-field unique indexes is one of those things.

Of course it's easy enough to create the compound-field unique indexes within the database schema files. KnexJS, the underlying query-builder library that Adonis uses, provides for this by simply passing an array of field names:

  table.unique(['field1', 'field2'])

Adonis' Lucid validators provide a unique validation rule so that you can ensure the value attempting to be added does not already exist within the specified table (optionally excluding a particular record by id), for example requiring that a user's email be unique:

  // Email must be provided, must be in the format of an email address
  // and must not already exist in the email field of the users table
  const rules = {
    email: 'required|email|unique:users,email',
  }

But there isn't a provision for validating these unique compound fields. Wouldn't it be nice if you could write a rule in a format similar to the unique validation rule, but for compound fields?

Maybe something like this?

  // Name must be provided, must be a string
  // and must not already exist in the name field of the foobar table
  // along with the same value for the friend_id field
  const rules = {
    name: 'required|string|uniqueCompound:foobar,name/friend_id',
  }

Thanks to the ability to extend the validator with a custom provider, you can.

I'll save you the suspense and just get to the provider code...
/providers/ValidateCompoundUnique/Provider.js:

'use strict'

const { ServiceProvider } = require('@adonisjs/fold')

class ValidateExists extends ServiceProvider {
  async _uniqueFn (data, field, message, args, get) {
    const Database = use('Database')
    const util = require('util')

    let ignoreId = null
    const fields = args[1].split('/')
    const table = args[0]
    if (args[2]) {
      ignoreId = args[2]
    }

    const rows = await Database.table(table).where((builder) => {
      for (const f of fields) {
        let value = get(data, f)
        builder.where(f, '=', value)
      }
      if (ignoreId) {
        builder.whereNot('id', '=', ignoreId)
      }
    }).count('* as total')

    if (rows[0].total) {
      throw message
    }
  }

  boot () {
    const Validator = use('Validator')

    Validator.extend('uniqueCompound', this._uniqueFn, 'Must be unique')
  }
}

module.exports = ValidateExists

This then gets added to the list of providers in your /start/app.js file:

const providers = [
  // ...
  
  path.join(__dirname, '..', 'providers', 'ValidateCompoundUnique/Provider'),
]

Once those are in place, the new uniqueCompound validation rule becomes available for use.

Format is:
uniqueCompound:[table],[field1]/[field2]/[field3]/[field4],[id]

Where:

  • [table] :: Name of the table to look at
  • [field1]/[field2]/... :: List of fields to run a combined uniqueness check against
  • [id] :: Optional id value to ignore (useful for some updates, for example)

The implementation feels a little bit hacky, and could probably be improved, but as is it gets the job done well enough for me.