如何使用Knex为已有PostgreSQL表添加约束？

问题描述 #

尝试用Knex迁移给PostgreSQL的已有users表添加Check约束，编写的迁移代码如下：

exports.up = function(knex) {
  return knex.schema.alterTable('users', table => {
    table.check(
      '?? <> \'deleted\' AND ?? IS NOT NULL',
      ['status', 'email'],
      'users_email_is_not_null'
    );
};

运行后收到报错：

error: alter table "users" add constraint users_email_is_not_null check($1$2 <> \'deleted\' AND $3$4 IS NOT NULL) - syntax error at or near "$2"

但在创建表时添加相同约束是有效的，需确认Knex是否支持在alterTable中添加Check约束及解决报错的方法。

原因分析 #

Knex的table.check()方法在alterTable场景下，对占位符??的解析逻辑存在问题，导致生成的SQL出现语法错误；而创建表时Knex的语法解析逻辑与修改表时不同，因此能正常执行。

解决方案 #

Knex本身支持在alterTable中添加Check约束，可通过以下两种方式解决报错：

方式1：使用原始SQL语句执行 #

绕过Knex的check方法，直接用knex.raw()执行ALTER TABLE语句，精准控制SQL语法：

exports.up = function(knex) {
  return knex.raw(`
    ALTER TABLE users
    ADD CONSTRAINT users_email_is_not_null
    CHECK (status <> 'deleted' AND email IS NOT NULL);
  `);
};

exports.down = function(knex) {
  return knex.raw(`
    ALTER TABLE users
    DROP CONSTRAINT users_email_is_not_null;
  `);
};

方式2：使用checkConstraint方法（适用于新版本Knex） #

部分新版本Knex提供了checkConstraint方法，在alterTable中可正确解析约束逻辑：

exports.up = function(knex) {
  return knex.schema.alterTable('users', table => {
    table.checkConstraint('users_email_is_not_null', 'status <> \'deleted\' AND email IS NOT NULL');
  });
};

exports.down = function(knex) {
  return knex.schema.alterTable('users', table => {
    table.dropCheckConstraint('users_email_is_not_null');
  });
};

内容的提问来源于stack exchange，提问作者MaterialZ