exists

Queries the database to ensure the value exists inside a given database table and column.

The validation rule is added by @adonisjs/lucid package. So make sure it is installed and configured , before using this rule.

import { schema, rules } from '@ioc:Adonis/Core/Validator'

{
  slug: schema.string({}, [
    rules.exists({ table: 'categories', column: 'slug' })
  ])
}

Case insensitivity

Many databases perform case sensitive queries. So either you can transform the value to lowerCase in JavaScript or make use of the caseInsensitive option to convert value to lowercase during the query.

{
  username: schema.string({}, [
    rules.exists({
      table: 'users',
      column: 'username',
      caseInsensitive: true,
    })
  ])
}

Following is an example of the query executed behind the scenes.

SELECT username FROM users WHERE LOWER(username) = LOWER(?)

Additional constraints

Additionally, you can also define where and whereNot constraints as an object of key-value pair. The key is the column name.

{
  slug: schema.string({}, [
    rules.exists({
      table: 'categories',
      column: 'slug',
      where: {
        tenant_id: 1,
        status: 'active',
      },
    })
  ])
}

SELECT slug FROM categories
  WHERE slug = ?
  AND tenant_id = ?
  AND status = ?

We perform a whereIn query if the value is an array. For example:

rules.exists({
  table: 'categories',
  column: 'slug',
  where: {
    group_id: [1, 2, 4],
  },
})

SELECT slug FROM categories
  WHERE slug = ?
  AND group_id IN (?, ?, ?)

Using refs

If you are caching your validation schema using the cacheKey and your where constraints relies on a runtime value, then you must make use of refs.

import { schema } from '@ioc:Adonis/Core/Validator'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class CreateUserValidator {
  constructor (protected ctx: HttpContextContract) {
  }

  public refs = schema.refs({
    tenantId: this.ctx.auth.user!.tenantId
  })

  public schema = schema.create({
    username: schema.string({}, [
      rules.exists({
        table: 'users',
        column: 'username',
        where: { tenant_id: this.refs.tenantId },
      })
    ])
  })

}