knex/knex · error · Error

Alter table with to add constraints is not permitted in SQLi

Error message

Alter table with to add constraints is not permitted in SQLite

What it means

SQLite does not support adding CHECK constraints to an existing table via ALTER TABLE (the ALTER TABLE ADD CONSTRAINT syntax is not implemented by SQLite). knex's column compiler therefore overrides _pushAlterCheckQuery (the method that normally emits ALTER TABLE ... ADD CHECK for other dialects) to throw immediately on SQLite. This prevents emitting SQL that SQLite would reject.

Source

Thrown at lib/dialects/sqlite3/schema/sqlite-columncompiler.js:23

class ColumnCompiler_SQLite3 extends ColumnCompiler {
  constructor() {
    super(...arguments);
    this.modifiers = ['nullable', 'defaultTo'];
    this._addCheckModifiers();
  }

  // Types
  // -------

  enu(allowed) {
    return `text check (${this.formatter.wrap(
      this.args[0]
    )} in ('${allowed.join("', '")}'))`;
  }

  _pushAlterCheckQuery(checkPredicate, constraintName) {
    throw new Error(
      `Alter table with to add constraints is not permitted in SQLite`
    );
  }

  checkRegex(regexes, constraintName) {
    return this._check(
      `${this.formatter.wrap(
        this.getColumnName()
      )} REGEXP ${this.client._escapeBinding(regexes)}`,
      constraintName
    );
  }
}

ColumnCompiler_SQLite3.prototype.json = 'json';
ColumnCompiler_SQLite3.prototype.jsonb = 'json';
ColumnCompiler_SQLite3.prototype.double =
  ColumnCompiler_SQLite3.prototype.decimal =

View on GitHub (pinned to e25d54bcb7)

Solutions

Define CHECK constraints at table-creation time (inside createTable), which SQLite supports.
To add a CHECK to an existing SQLite table, rebuild it manually (knex.raw: create new table with check, copy data, drop, rename).
Skip the check-alter on SQLite via a dialect guard in your migration.

Example fix

// before
await knex.schema.alterTable('t', (b) => {
  b.string('email').alter().checkRegex(/^[^@]+@[^@]+$/);
});
// after
await knex.schema.alterTable('t', (b) => {
  b.string('email').alter();
});
// or rebuild manually with a CHECK on the new table

Defensive patterns

Strategy: validation

Validate before calling

function isSqlite(knex) { return /sqlite/i.test(knex.client.dialect || ''); }
if (!isSqlite(knex)) {
  await knex.schema.alterTable('t', (b) => b.string('c').alter().checkRegex(/x/));
} else {
  // define the CHECK at create time or rebuild the table manually
}

Type guard

function supportsAlterCheck(knex) { return !/sqlite/i.test(knex.client.dialect || ''); }

Try / catch

try {
  await knex.schema.alterTable('t', (b) => b.string('c').alter().check('c > 0'));
} catch (e) {
  if (/not permitted in SQLite/i.test(e.message)) {
    // skip the check on SQLite or rebuild the table manually
  } else throw e;
}

Prevention

Define CHECK constraints at createTable time on SQLite.
Branch alter-column-check code by dialect.
Maintain a manual table-rebuild helper for adding checks to existing SQLite tables.

When it happens

Trigger: Using a column modifier that adds a CHECK constraint to an existing column on SQLite, e.g. .alter().check() or .checkRegex() inside an alterTable block that modifies an existing column (alterColumnsPrefix path). Adding a CHECK to a column via the checks API on an already-existing table.

Common situations: Sharing migration code between SQLite (dev/test) and PostgreSQL/MySQL (prod) where adding checks to existing columns is allowed. Using the fluent checks API without checking dialect support.

Related errors

AI-assisted analysis of knex/knex@e25d54bcb7 (2026-08-03). Data as JSON: /data/errors/018be71007ce413b.json. Report an issue: GitHub.