Repository: knex/knex
Pull request #4584 (2021-07-23 13:19:31)

Add deferrable support for constraint
Address #4292 : Add Deferrable Support
- Foreign: ```deferrable([deferrable])``` can be chained off ```table.foreign()``` or ```column.references()```
- allow option object to be used for primary/unique:
```column.unique(indexName | options={[indexName: string], [deferrable: string]});```  
```table.unique(columns,indexName | options={[indexName: string], [deferrable: string]});``` 
```column.primary(constraintName | options={[constraintName : string], [deferrable: string]});```  
```table.primary(columns,constraintName | options={[constraintName : string], [deferrable: string]});``` 


zeotuan: @kibertoad this test seem to be flaky:
```Integration Tests
       postgresql | pg
         knex.migrate.latest in parallel
           is not able to run two migrations in parallel when transactions are disabled:

      AssertionError: expected false to equal true
      + expected - actual

      -false
      +true
      
      at /home/runner/work/knex/knex/test/integration/migrate/migration-integration-tests.js:789:33
      at runMicrotasks (<anonymous>)
      at processTicksAndRejections (node:internal/process/task_queues:96:5)```
kibertoad: Great job! Could you also create doc PR at https://github.com/knex/documentation?
zeotuan: > Great job! Could you also create doc PR at https://github.com/knex/documentation?

Documentation added. Can you check it out.

Patch:
diff --git a/lib/dialects/oracle/schema/oracle-tablecompiler.js b/lib/dialects/oracle/schema/oracle-tablecompiler.js
index a0759479..85538796 100644
--- a/lib/dialects/oracle/schema/oracle-tablecompiler.js
+++ b/lib/dialects/oracle/schema/oracle-tablecompiler.js
@@ -4,6 +4,7 @@ const utils = require('../utils');
 const TableCompiler = require('../../../schema/tablecompiler');
 const helpers = require('../../../util/helpers');
 const Trigger = require('./internal/trigger');
+const { isObject } = require('../../../util/is');
 
 // Table Compiler
 // ------
@@ -87,13 +88,18 @@ class TableCompiler_Oracle extends TableCompiler {
   }
 
   primary(columns, constraintName) {
+    let deferrable;
+    if (isObject(constraintName)) {
+      ({ constraintName, deferrable } = constraintName);
+    }
+    deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
     constraintName = constraintName
       ? this.formatter.wrap(constraintName)
       : this.formatter.wrap(`${this.tableNameRaw}_pkey`);
     this.pushQuery(
       `alter table ${this.tableName()} add constraint ${constraintName} primary key (${this.formatter.columnize(
         columns
-      )})`
+      )})${deferrable}`
     );
   }
 
@@ -126,6 +132,11 @@ class TableCompiler_Oracle extends TableCompiler {
   }
 
   unique(columns, indexName) {
+    let deferrable;
+    if (isObject(indexName)) {
+      ({ indexName, deferrable } = indexName);
+    }
+    deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
     indexName = indexName
       ? this.formatter.wrap(indexName)
       : this._indexCommand('unique', this.tableNameRaw, columns);
@@ -133,7 +144,8 @@ class TableCompiler_Oracle extends TableCompiler {
       `alter table ${this.tableName()} add constraint ${indexName}` +
         ' unique (' +
         this.formatter.columnize(columns) +
-        ')'
+        ')' +
+        deferrable
     );
   }
 
diff --git a/lib/dialects/postgres/schema/pg-tablecompiler.js b/lib/dialects/postgres/schema/pg-tablecompiler.js
index d1f54b7b..bf8b3fe9 100644
--- a/lib/dialects/postgres/schema/pg-tablecompiler.js
+++ b/lib/dialects/postgres/schema/pg-tablecompiler.js
@@ -5,6 +5,7 @@
 
 const has = require('lodash/has');
 const TableCompiler = require('../../../schema/tablecompiler');
+const { isObject } = require('../../../util/is');
 
 class TableCompiler_PG extends TableCompiler {
   constructor(client, tableBuilder) {
@@ -112,17 +113,27 @@ class TableCompiler_PG extends TableCompiler {
   // -------
 
   primary(columns, constraintName) {
+    let deferrable;
+    if (isObject(constraintName)) {
+      ({ constraintName, deferrable } = constraintName);
+    }
+    deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
     constraintName = constraintName
       ? this.formatter.wrap(constraintName)
       : this.formatter.wrap(`${this.tableNameRaw}_pkey`);
     this.pushQuery(
       `alter table ${this.tableName()} add constraint ${constraintName} primary key (${this.formatter.columnize(
         columns
-      )})`
+      )})${deferrable}`
     );
   }
 
   unique(columns, indexName) {
+    let deferrable;
+    if (isObject(indexName)) {
+      ({ indexName, deferrable } = indexName);
+    }
+    deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
     indexName = indexName
       ? this.formatter.wrap(indexName)
       : this._indexCommand('unique', this.tableNameRaw, columns);
@@ -130,7 +141,8 @@ class TableCompiler_PG extends TableCompiler {
       `alter table ${this.tableName()} add constraint ${indexName}` +
         ' unique (' +
         this.formatter.columnize(columns) +
-        ')'
+        ')' +
+        deferrable
     );
   }
 
diff --git a/lib/schema/tablebuilder.js b/lib/schema/tablebuilder.js
index cd119a58..a861f3ad 100644
--- a/lib/schema/tablebuilder.js
+++ b/lib/schema/tablebuilder.js
@@ -116,6 +116,20 @@ class TableBuilder {
         foreignData.onDelete = statement;
         return returnObj;
       },
+      deferrable: (type) => {
+        const unSupported = [
+          'mysql',
+          'mssql',
+          'redshift',
+          'mysql2',
+          'oracledb',
+        ];
+        if (unSupported.indexOf(this.client.dialect) !== -1) {
+          throw new Error(`${this.client.dialect} does not support deferrable`);
+        }
+        foreignData.deferrable = type;
+        return returnObj;
+      },
       _columnBuilder(builder) {
         extend(builder, returnObj);
         returnObj = builder;
diff --git a/lib/schema/tablecompiler.js b/lib/schema/tablecompiler.js
index 613f5969..d7825cb7 100644
--- a/lib/schema/tablecompiler.js
+++ b/lib/schema/tablecompiler.js
@@ -97,6 +97,11 @@ class TableCompiler {
         ? (this.lowerCase ? ' on delete ' : ' ON DELETE ') +
           foreignData.onDelete
         : '';
+      const deferrable = foreignData.deferrable
+        ? this.lowerCase
+          ? ` deferrable initially ${foreignData.deferrable.toLowerCase()} `
+          : ` DEFERRABLE INITIALLY ${foreignData.deferrable.toUpperCase()} `
+        : '';
       if (this.lowerCase) {
         this.pushQuery(
           (!this.forCreate ? `alter table ${this.tableName()} add ` : '') +
@@ -110,6 +115,7 @@ class TableCompiler {
             ' (' +
             references +
             ')' +
+            deferrable +
             onUpdate +
             onDelete
         );
@@ -126,6 +132,7 @@ class TableCompiler {
             ' (' +
             references +
             ')' +
+            deferrable +
             onUpdate +
             onDelete
         );


Test patch:
diff --git a/test/integration2/transaction/set-deferrable-constraint.spec.js b/test/integration2/transaction/set-deferrable-constraint.spec.js
new file mode 100644
index 00000000..8410e16f
--- /dev/null
+++ b/test/integration2/transaction/set-deferrable-constraint.spec.js
@@ -0,0 +1,116 @@
+const { getAllDbs, getKnexForDb } = require('../util/knex-instance-provider');
+const { isMssql, isOracle, isPostgreSQL } = require('../../util/db-helpers');
+
+describe('Transaction', () => {
+  describe('setDeferrableConstraint', () => {
+    getAllDbs().forEach((db) => {
+      describe(db, () => {
+        let knex;
+        const tableName = 'deferrableTestTable';
+        const tableName1 = 'deferrableTestTable1';
+        before(function () {
+          knex = getKnexForDb(db);
+          if (!isPostgreSQL(knex) && !isOracle(knex)) {
+            return this.skip();
+          }
+          if (isMssql(knex)) {
+            // Enable the snapshot isolation level required by certain transaction tests.
+            return knex.raw(
+              `ALTER DATABASE :db: SET ALLOW_SNAPSHOT_ISOLATION ON`,
+              { db: knex.context.client.config.connection.database }
+            );
+          }
+        });
+
+        after(() => {
+          return knex.destroy();
+        });
+
+        beforeEach(async () => {
+          await knex.schema.createTable(tableName, (table) => {
+            table.integer('id').primary().notNull();
+          });
+          await knex.schema.createTable(tableName1, (table) => {
+            table.integer('id').primary().notNull();
+          });
+        });
+
+        afterEach(async () => {
+          await knex.schema.dropTable(tableName);
+          await knex.schema.dropTable(tableName1);
+        });
+
+        it('deferrable initially immediate unique constraint all row are checked at end of update', async () => {
+          await knex.schema.table(tableName, (table) => {
+            table.integer('value').unique({ deferrable: 'immediate' });
+          });
+          await knex.schema.table(tableName1, (table) => {
+            table.integer('value').unique({ deferrable: 'immediate' });
+          });
+          const trx = await knex.transaction({
+            isolationLevel: 'read committed',
+          });
+          await trx(tableName).select();
+          await trx(tableName).insert({ id: 1, value: 1 });
+          await trx(tableName).insert({ id: 2, value: 2 });
+          //This usually fail but deferrable initially immediate allow check to be performed at the end of update isntead
+          await trx(tableName).update({
+            value: knex.raw('?? + 1', ['value']),
+          });
+          await trx.commit();
+        });
+
+        it('deferred unique constraint are only checked when transaction is committed', async () => {
+          await knex.schema.table(tableName, (table) => {
+            table.integer('value').unique({ deferrable: 'deferred' });
+          });
+          const trx = await knex.transaction({
+            isolationLevel: 'read committed',
+          });
+          await trx(tableName).insert({ id: 1, value: 1 });
+          await trx(tableName).insert({ id: 2, value: 2 });
+          //This usually fail but deferrable initially deferred allow constraint to be checked at the commit instead
+          await trx(tableName).insert({ id: 3, value: 1 });
+          await trx(tableName).insert({ id: 4, value: 2 });
+          await trx(tableName).delete().where({ id: 3 });
+          await trx(tableName).delete().where({ id: 4 });
+          await trx.commit();
+        });
+
+        it('deferred foreign constraint are only checked when transaction is committed', async () => {
+          await knex.schema.table(tableName, (table) => {
+            table.integer('value');
+            table
+              .foreign('value')
+              .deferrable('deferred')
+              .references(`${tableName1}.id`)
+              .withKeyName('fk1');
+          });
+          await knex.schema.table(tableName1, (table) => {
+            table.integer('value');
+            table
+              .foreign('value')
+              .deferrable('deferred')
+              .references(`${tableName}.id`)
+              .withKeyName('fk');
+          });
+          const trx = await knex.transaction({
+            isolationLevel: 'read committed',
+          });
+          await trx(tableName).select();
+          await trx(tableName).insert({ id: 1, value: 1 });
+          await trx(tableName).insert({ id: 2, value: 2 });
+          await trx(tableName1).insert({ id: 1, value: 1 });
+          await trx(tableName1).insert({ id: 2, value: 2 });
+          await trx.commit();
+          await knex.schema.table(tableName, (table) => {
+            table.dropForeign('value', 'fk1');
+          });
+          await knex.schema.table(tableName1, (table) => {
+            table.dropForeign('value', 'fk');
+          });
+        });
+      });
+    });
+  });
+});
diff --git a/test/unit/schema-builder/mssql.js b/test/unit/schema-builder/mssql.js
index 1238123d..185b6782 100644
--- a/test/unit/schema-builder/mssql.js
+++ b/test/unit/schema-builder/mssql.js
@@ -486,6 +486,22 @@ describe('MSSQL SchemaBuilder', function () {
     );
   });
 
+  it('adds foreign key with deferred throw error ', function () {
+    const addDeferredConstraint = () => {
+      client
+        .schemaBuilder()
+        .createTable('person', function (table) {
+          table
+            .integer('user_id')
+            .notNull()
+            .references('users.id')
+            .deferrable('immediate');
+        })
+        .toSQL();
+    };
+    expect(addDeferredConstraint).to.throw('mssql does not support deferrable');
+  });
+
   it('adds foreign key with onUpdate and onDelete', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/mysql.js b/test/unit/schema-builder/mysql.js
index 07d9807a..d42cee10 100644
--- a/test/unit/schema-builder/mysql.js
+++ b/test/unit/schema-builder/mysql.js
@@ -385,6 +385,24 @@ module.exports = function (dialect) {
       );
     });
 
+    it('adds foreign key with deferred throw error ', function () {
+      const addDeferredConstraint = () => {
+        client
+          .schemaBuilder()
+          .createTable('person', function (table) {
+            table
+              .integer('user_id')
+              .notNull()
+              .references('users.id')
+              .deferrable('immediate');
+          })
+          .toSQL();
+      };
+      expect(addDeferredConstraint).to.throw(
+        'mysql does not support deferrable'
+      );
+    });
+
     it('adds foreign key with onUpdate and onDelete', function () {
       tableSql = client
         .schemaBuilder()
diff --git a/test/unit/schema-builder/oracle.js b/test/unit/schema-builder/oracle.js
index fd83d75c..e11379f6 100644
--- a/test/unit/schema-builder/oracle.js
+++ b/test/unit/schema-builder/oracle.js
@@ -375,6 +375,54 @@ describe('Oracle SchemaBuilder', function () {
     );
   });
 
+  it('adds foreign key with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .notNull()
+          .references('users.id')
+          .deferrable('immediate');
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "person_user_id_foreign" foreign key ("user_id") references "users" ("id") deferrable initially immediate '
+    );
+  });
+
+  it('adds unique constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .unique({ indexName: 'user_id_index', deferrable: 'immediate' });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_index" unique ("user_id") deferrable initially immediate'
+    );
+  });
+
+  it('adds primary constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table.integer('user_id').primary({
+          constraintName: 'user_id_primary',
+          deferrable: 'immediate',
+        });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_primary" primary key ("user_id") deferrable initially immediate'
+    );
+  });
+
   it('test adding incrementing id', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/oracledb.js b/test/unit/schema-builder/oracledb.js
index 7353ada2..b027486d 100644
--- a/test/unit/schema-builder/oracledb.js
+++ b/test/unit/schema-builder/oracledb.js
@@ -358,6 +358,54 @@ describe('OracleDb SchemaBuilder', function () {
     );
   });
 
+  it('adds foreign key with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .notNull()
+          .references('users.id')
+          .deferrable('immediate');
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "person_user_id_foreign" foreign key ("user_id") references "users" ("id") deferrable initially immediate '
+    );
+  });
+
+  it('adds unique constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .unique({ indexName: 'user_id_index', deferrable: 'immediate' });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_index" unique ("user_id") deferrable initially immediate'
+    );
+  });
+
+  it('adds primary constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table.integer('user_id').primary({
+          constraintName: 'user_id_primary',
+          deferrable: 'immediate',
+        });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_primary" primary key ("user_id") deferrable initially immediate'
+    );
+  });
+
   it('test adding incrementing id', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/postgres.js b/test/unit/schema-builder/postgres.js
index 1e9040ab..45d8d8ef 100644
--- a/test/unit/schema-builder/postgres.js
+++ b/test/unit/schema-builder/postgres.js
@@ -357,6 +357,54 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adds foreign key with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .notNull()
+          .references('users.id')
+          .deferrable('immediate');
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "person_user_id_foreign" foreign key ("user_id") references "users" ("id") deferrable initially immediate '
+    );
+  });
+
+  it('adds unique constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table
+          .integer('user_id')
+          .unique({ indexName: 'user_id_index', deferrable: 'immediate' });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_index" unique ("user_id") deferrable initially immediate'
+    );
+  });
+
+  it('adds primary constraint with deferrable initially immediate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .createTable('person', function (table) {
+        table.integer('user_id').primary({
+          constraintName: 'user_id_primary',
+          deferrable: 'immediate',
+        });
+      })
+      .toSQL();
+    equal(2, tableSql.length);
+    expect(tableSql[1].sql).to.equal(
+      'alter table "person" add constraint "user_id_primary" primary key ("user_id") deferrable initially immediate'
+    );
+  });
+
   it('rename table', function () {
     tableSql = client.schemaBuilder().renameTable('users', 'foo').toSQL();
     equal(1, tableSql.length);