Class HandleEmptyInListsPlugin

A plugin that allows handling in () and not in () expressions.

These expressions are invalid SQL syntax for many databases, and result in runtime database errors.

The workarounds used by other libraries always involve modifying the query under the hood, which is not aligned with Kysely's philosophy of WYSIWYG. We recommend manually checking for empty arrays before passing them as arguments to in and not in expressions instead, but understand that this can be cumbersome. Hence we're going with an opt-in approach where you can choose if and how to handle these cases. We do not want to make this the default behavior, as it can lead to unexpected behavior. Use it at your own risk. Test it. Make sure it works as expected for you.

Using this plugin also allows you to throw an error (thus avoiding unnecessary requests to the database) or print a warning in these cases.

Examples

The following strategy replaces the in/not in expression with a noncontingent expression. A contradiction (falsy) 1 = 0 for in, and a tautology (truthy) 1 = 1 for not in), similarily to how Knex.js, PrismaORM, Laravel, SQLAlchemy handle this.

import Sqlite from 'better-sqlite3'
import {
  HandleEmptyInListsPlugin,
  Kysely,
  replaceWithNoncontingentExpression,
  SqliteDialect,
} from 'kysely'
import type { Database } from 'type-editor' // imaginary module

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Sqlite(':memory:'),
  }),
  plugins: [
    new HandleEmptyInListsPlugin({
      strategy: replaceWithNoncontingentExpression
    })
  ],
})

const results = await db
  .selectFrom('person')
  .where('id', 'in', [])
  .where('first_name', 'not in', [])
  .selectAll()
  .execute()
Copy

The generated SQL (SQLite):

select * from "person" where 1 = 0 and 1 = 1
Copy

The following strategy does the following:

When in, pushes a null value into the empty list resulting in in (null), similiarly to how TypeORM and Sequelize handle in (). in (null) is logically the equivalent of = null, which returns null, which is a falsy expression in most SQL databases. We recommend NOT using this strategy if you plan to use in in select, returning, or output clauses, as the return type differs from the SqlBool default type for comparisons.

When not in, casts the left operand as char and pushes a unique value into the empty list resulting in cast({{lhs}} as char) not in ({{VALUE}}). Casting is required to avoid database errors with non-string values.

import Sqlite from 'better-sqlite3'
import {
  HandleEmptyInListsPlugin,
  Kysely,
  pushValueIntoList,
  SqliteDialect
} from 'kysely'
import type { Database } from 'type-editor' // imaginary module

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Sqlite(':memory:'),
  }),
  plugins: [
    new HandleEmptyInListsPlugin({
      strategy: pushValueIntoList('__kysely_no_values_were_provided__') // choose a unique value for not in. has to be something with zero chance being in the data.
    })
  ],
})

const results = await db
  .selectFrom('person')
  .where('id', 'in', [])
  .where('first_name', 'not in', [])
  .selectAll()
  .execute()
Copy

The generated SQL (SQLite):

select * from "person" where "id" in (null) and cast("first_name" as char) not in ('__kysely_no_values_were_provided__')
Copy

The following custom strategy throws an error when an empty list is encountered to avoid unnecessary requests to the database:

import Sqlite from 'better-sqlite3'
import {
  HandleEmptyInListsPlugin,
  Kysely,
  SqliteDialect
} from 'kysely'
import type { Database } from 'type-editor' // imaginary module

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Sqlite(':memory:'),
  }),
  plugins: [
    new HandleEmptyInListsPlugin({
      strategy: () => {
        throw new Error('Empty in/not-in is not allowed')
      }
    })
  ],
})

const results = await db
  .selectFrom('person')
  .where('id', 'in', [])
  .selectAll()
  .execute() // throws an error with 'Empty in/not-in is not allowed' message!
Copy