Database validation rules

Inspired by the Adonis Lucid/Vine.js rules, @nhtio/validation adds two database-powered rules you can use on any schema: uniqueInDb and existsInDb.

Providing a database connection

Use .knex(connection) or .db(connection) on any schema to attach a database connection. The connection can be any of the supported types listed in KnexConnectionConfigurations:

• a Knex instance (recommended)
• an AdonisJS QueryClientContract
• a plain Knex config object

When applied to an object schema, children can inherit the connection from their parents. You can also override the connection at any child node.

import { validator as joi } from '@nhtio/validation'
import knex from 'knex'

const db = knex({ client: 'sqlite3', connection: { filename: ':memory:' }, useNullAsDefault: true })

// Attach at child
const childLevel = joi.object({
    email: joi.string().email().knex(db).uniqueInDb('users', 'email'),
})

// Attach at parent (children inherit)
const parentLevel = joi
    .object({
        email: joi.string().email().uniqueInDb('users', 'email'),
    })
    .knex(db)

// Override at child (wins over parent)
const db2 = knex({ client: 'sqlite3', connection: { filename: ':memory:' }, useNullAsDefault: true })
const override = joi
    .object({
        email: joi.string().email().knex(db2).uniqueInDb('users', 'email'),
    })
    .knex(db)

For more on these helpers, see AnySchema#knex and AnySchema#db.

Rules and examples

uniqueInDb(table, column, options?)

Ensures the value does not already exist in the given table/column.

Basic usage (fail on duplicate, pass on new):

// setup
await db('users').insert({ email: 'foo@bar.com' })

const schema = joi.object({
    email: joi.string().email().knex(db).uniqueInDb('users', 'email'),
})

await expect(schema.validateAsync({ email: 'foo@bar.com' })).rejects.toThrow(
    'The "email" has already been taken'
)

const ok = await schema.validateAsync({ email: 'unique@example.com' })
// ok.email === 'unique@example.com'

Case-insensitive checks:

await db('users').insert({ email: 'Test@Example.com' })

const schema = joi.object({
    email: joi.string().email().knex(db).uniqueInDb('users', 'email', { caseInsensitive: true }),
})

await expect(schema.validateAsync({ email: 'test@example.com' })).rejects.toThrow(
    'The "email" has already been taken'
)

Custom filter (e.g., multi-tenant uniqueness):

await db('users').insert([
    { username: 'john', tenant_id: 1 },
    { username: 'john', tenant_id: 2 },
])

const schema = joi.object({
    username: joi.string().knex(db).uniqueInDb('users', 'username', {
        filter: async (query) => {
            query.where('tenant_id', 1)
        },
    }),
})

await expect(schema.validateAsync({ username: 'john' })).rejects.toThrow(
    'The "username" has already been taken'
)

Using references for table or column:

const byTable = joi.object({
    tableName: joi.string().valid('users'),
    email: joi.string().email().knex(db).uniqueInDb(joi.ref('tableName'), 'email'),
})

const byColumn = joi.object({
    field: joi.string().valid('username'),
    value: joi.string().knex(db).uniqueInDb('users', joi.ref('field')),
})

Null handling: many databases allow multiple NULLs in a unique column. Nulls are not checked and will pass.

const schema = joi.object({
    parent_id: joi.number().allow(null).knex(db).uniqueInDb('users', 'parent_id'),
})

const ok = await schema.validateAsync({ parent_id: null })
// ok.parent_id === null

existsInDb(table, column, options?)

Ensures the value already exists in the given table/column.

Basic usage (fail when not found, pass when found):

await db('users').insert({ country_id: 1 })

const schema = joi.object({
    country_id: joi.number().knex(db).existsInDb('users', 'country_id'),
})

await expect(schema.validateAsync({ country_id: 999 })).rejects.toThrow(
    'The selected "country_id" is invalid'
)

const ok = await schema.validateAsync({ country_id: 1 })
// ok.country_id === 1

Case-insensitive string lookups:

await db('users').insert({ username: 'TestUser' })

const schema = joi.object({
    username: joi.string().knex(db).existsInDb('users', 'username', { caseInsensitive: true }),
})

const ok = await schema.validateAsync({ username: 'testuser' })
// ok.username === 'testuser'

Custom filter:

await db('users').insert([
    { id: 1, tenant_id: 1, country_id: 10 },
    { id: 2, tenant_id: 2, country_id: 10 },
])

const schema = joi.object({
    country_id: joi.number().knex(db).existsInDb('users', 'country_id', {
        filter: async (query) => {
            query.where('tenant_id', 1)
        },
    }),
})

const ok = await schema.validateAsync({ country_id: 10 })
// ok.country_id === 10

Using references for table or column:

const byTable = joi.object({
    tableName: joi.string().valid('users'),
    country_id: joi.number().knex(db).existsInDb(joi.ref('tableName'), 'country_id'),
})

const byColumn = joi.object({
    field: joi.string().valid('points'),
    value: joi.number().knex(db).existsInDb('users', joi.ref('field')),
})

Inheriting connections from parent schemas

You can attach a connection at the object level and let children inherit it:

await db('users').insert({ email: 'existing@example.com', username: 'existinguser' })

const schema = joi
    .object({
        email: joi.string().email().uniqueInDb('users', 'email'),
        username: joi.string().uniqueInDb('users', 'username'),
    })
    .knex(db)

await expect(schema.validateAsync({ email: 'existing@example.com' })).rejects.toThrow(
    'The "email" has already been taken'
)
await expect(schema.validateAsync({ username: 'existinguser' })).rejects.toThrow(
    'The "username" has already been taken'
)

Inheritance also works across multiple nested objects and with the .db(...) alias:

const address = joi.object({
    country_id: joi.number().existsInDb('users', 'country_id'),
})

const user = joi
    .object({
        email: joi.string().email().uniqueInDb('users', 'email'),
        address,
    })
    .db(db)

await expect(
    user.validateAsync({ email: 'another@example.com', address: { country_id: 999 } })
).rejects.toThrow('The selected "address.country_id" is invalid')

To override a parent connection at a specific child, call .knex() (or .db()) again on that child schema.

Using multiple validators together

await db('users').insert({ id: 1, username: 'existing', email: 'existing@test.com', country_id: 1 })

const schema = joi.object({
    username: joi.string().knex(db).uniqueInDb('users', 'username'),
    email: joi.string().email().knex(db).uniqueInDb('users', 'email'),
    country_id: joi.number().knex(db).existsInDb('users', 'country_id'),
})

const ok = await schema.validateAsync({ username: 'newuser', email: 'new@test.com', country_id: 1 })
// ok.username === 'newuser', ok.email === 'new@test.com', ok.country_id === 1

await expect(
    schema.validateAsync({ username: 'existing', email: 'different@test.com', country_id: 1 })
).rejects.toThrow('The "username" has already been taken')

await expect(
    schema.validateAsync({ username: 'another', email: 'another@test.com', country_id: 999 })
).rejects.toThrow('The selected "country_id" is invalid')

Error handling and caveats

• If no database connection can be resolved at runtime (neither on the field nor any of its parent schemas), the rules return an error like: Database connection not provided for "<label>".
• External rules only run in async mode. Use validateAsync().
• caseInsensitive applies to string comparisons only.
• Null/undefined values are skipped by both rules (they pass through unchanged).

API links

• AnySchema#knex
• AnySchema#db
• AnySchema#uniqueInDb
• AnySchema#existsInDb