Repository: knex/knex (js)

Issue:
Feature request: partial index inside unique method
# Environment

Knex version: 0.95.14
Database + version: postgres 12.5
OS: mac


# Feature discussion / request

I saw that there is this feature request created [#2525](https://github.com/knex/knex/issues/2525) but was closed. I have also checked that this PR [#2401](https://github.com/knex/knex/pull/2401) add predicate options only for `.index` method. As postgres supports the unique partial index, is it possible to add the predicate inside the `.unique` method too?




Pull request:
feat: support partial unique indexes
Adds MSSQL, PostgreSQL, and SQLite support for partial unique indexes, heavily based on #4768.

The Postgres driver defaults to creating a unique constraint instead of an index, so Postgres support for `useConstraint` was added, defaulting to `true` without predicate and `false` with predicate. The alternative would be to always default to `useConstraint: true`, but then `predicate` would error by default unless `useConstraint: false` is explicitly specified. Dunno which is better.

Docs: knex/documentation#449

Fixes #4870

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 914649fc..63358372 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,21 @@
 # Master (Unreleased)
 
+# 2.3.0 - 31 August, 2022
+
+### New features:
+
+- PostgreSQL: Explicit jsonb support for custom pg clients #5201
+- SQLite: Support returning with sqlite3 and better-sqlite3 #5285
+- MSSQL: Implement mapBinding mssql dialect option #5292
+
+### Typings:
+
+- Update types for TS 4.8 #5279
+- Fix typo #5267
+- Fix WhereJsonObject withCompositeTableType #5306
+- Fix AnalyticFunction type #5304
+- Infer specific column value type in aggregations #5297
+
 # 2.2.0 - 19 July, 2022
 
 ### New features:
diff --git a/lib/dialects/mssql/index.js b/lib/dialects/mssql/index.js
index 29f28a44..27654fd8 100644
--- a/lib/dialects/mssql/index.js
+++ b/lib/dialects/mssql/index.js
@@ -333,14 +333,24 @@ class Client_MSSQL extends Client {
   _typeForBinding(binding) {
     const Driver = this._driver();
 
+    if (
+      this.connectionSettings.options &&
+      this.connectionSettings.options.mapBinding
+    ) {
+      const result = this.connectionSettings.options.mapBinding(binding);
+      if (result) {
+        return [result.value, result.type];
+      }
+    }
+
     switch (typeof binding) {
       case 'string':
-        return Driver.TYPES.NVarChar;
+        return [binding, Driver.TYPES.NVarChar];
       case 'boolean':
-        return Driver.TYPES.Bit;
+        return [binding, Driver.TYPES.Bit];
       case 'number': {
         if (binding % 1 !== 0) {
-          return Driver.TYPES.Float;
+          return [binding, Driver.TYPES.Float];
         }
 
         if (binding < SQL_INT4.MIN || binding > SQL_INT4.MAX) {
@@ -350,25 +360,21 @@ class Client_MSSQL extends Client {
             );
           }
 
-          return Driver.TYPES.BigInt;
+          return [binding, Driver.TYPES.BigInt];
         }
 
-        return Driver.TYPES.Int;
+        return [binding, Driver.TYPES.Int];
       }
       default: {
-        // if (binding === null || typeof binding === 'undefined') {
-        //   return tedious.TYPES.Null;
-        // }
-
         if (binding instanceof Date) {
-          return Driver.TYPES.DateTime;
+          return [binding, Driver.TYPES.DateTime];
         }
 
         if (binding instanceof Buffer) {
-          return Driver.TYPES.VarBinary;
+          return [binding, Driver.TYPES.VarBinary];
         }
 
-        return Driver.TYPES.NVarChar;
+        return [binding, Driver.TYPES.NVarChar];
       }
     }
   }
@@ -401,8 +407,8 @@ class Client_MSSQL extends Client {
   }
 
   // sets a request input parameter. Detects bigints and decimals and sets type appropriately.
-  _setReqInput(req, i, binding) {
-    const tediousType = this._typeForBinding(binding);
+  _setReqInput(req, i, inputBinding) {
+    const [binding, tediousType] = this._typeForBinding(inputBinding);
     const bindingName = 'p'.concat(i);
     let options;
 
diff --git a/lib/dialects/mssql/schema/mssql-tablecompiler.js b/lib/dialects/mssql/schema/mssql-tablecompiler.js
index a4d09594..4802bd71 100644
--- a/lib/dialects/mssql/schema/mssql-tablecompiler.js
+++ b/lib/dialects/mssql/schema/mssql-tablecompiler.js
@@ -280,20 +280,24 @@ class TableCompiler_MSSQL extends TableCompiler {
    * Create a unique index.
    *
    * @param {string | string[]} columns
-   * @param {string | {indexName: undefined | string, deferrable?: 'not deferrable'|'deferred'|'immediate', useConstraint?: true|false }} indexName
+   * @param {string | {indexName: undefined | string, deferrable?: 'not deferrable'|'deferred'|'immediate', useConstraint?: true|false, predicate?: QueryBuilder }} indexName
    */
   unique(columns, indexName) {
     /** @type {string | undefined} */
     let deferrable;
     let useConstraint = false;
+    let predicate;
     if (isObject(indexName)) {
-      ({ indexName, deferrable, useConstraint } = indexName);
+      ({ indexName, deferrable, useConstraint, predicate } = indexName);
     }
     if (deferrable && deferrable !== 'not deferrable') {
       this.client.logger.warn(
         `mssql: unique index [${indexName}] will not be deferrable ${deferrable} because mssql does not support deferred constraints.`
       );
     }
+    if (useConstraint && predicate) {
+      throw new Error('mssql cannot create constraint with predicate');
+    }
     indexName = indexName
       ? this.formatter.wrap(indexName)
       : this._indexCommand('unique', this.tableNameRaw, columns);
@@ -302,10 +306,6 @@ class TableCompiler_MSSQL extends TableCompiler {
       columns = [columns];
     }
 
-    const whereAllTheColumnsAreNotNull = columns
-      .map((column) => this.formatter.columnize(column) + ' IS NOT NULL')
-      .join(' AND ');
-
     if (useConstraint) {
       // mssql supports unique indexes and unique constraints.
       // unique indexes cannot be used with foreign key relationships hence unique constraints are used instead.
@@ -315,12 +315,18 @@ class TableCompiler_MSSQL extends TableCompiler {
         )})`
       );
     } else {
-      // make unique constraint that allows null https://stackoverflow.com/a/767702/360060
+      // default to making unique index that allows null https://stackoverflow.com/a/767702/360060
       // to be more or less compatible with other DBs (if any of the columns is NULL then "duplicates" are allowed)
+      const predicateQuery = predicate
+        ? ' ' + this.client.queryCompiler(predicate).where()
+        : ' WHERE ' +
+          columns
+            .map((column) => this.formatter.columnize(column) + ' IS NOT NULL')
+            .join(' AND ');
       this.pushQuery(
         `CREATE UNIQUE INDEX ${indexName} ON ${this.tableName()} (${this.formatter.columnize(
           columns
-        )}) WHERE ${whereAllTheColumnsAreNotNull}`
+        )})${predicateQuery}`
       );
     }
   }
diff --git a/lib/dialects/postgres/schema/pg-columncompiler.js b/lib/dialects/postgres/schema/pg-columncompiler.js
index a7cd3e32..443f734b 100644
--- a/lib/dialects/postgres/schema/pg-columncompiler.js
+++ b/lib/dialects/postgres/schema/pg-columncompiler.js
@@ -145,6 +145,7 @@ function jsonColumn(client, jsonb) {
   if (
     !client.version ||
     client.config.client === 'cockroachdb' ||
+    client.config.jsonbSupport === true ||
     parseFloat(client.version) >= 9.2
   ) {
     return jsonb ? 'jsonb' : 'json';
diff --git a/lib/dialects/postgres/schema/pg-tablecompiler.js b/lib/dialects/postgres/schema/pg-tablecompiler.js
index d2eeb980..dd029cf1 100644
--- a/lib/dialects/postgres/schema/pg-tablecompiler.js
+++ b/lib/dialects/postgres/schema/pg-tablecompiler.js
@@ -189,20 +189,44 @@ class TableCompiler_PG extends TableCompiler {
 
   unique(columns, indexName) {
     let deferrable;
+    let useConstraint = true;
+    let predicate;
     if (isObject(indexName)) {
-      ({ indexName, deferrable } = indexName);
+      ({ indexName, deferrable, useConstraint, predicate } = indexName);
+      if (useConstraint === undefined) {
+        useConstraint = !!deferrable || !predicate;
+      }
+    }
+    if (!useConstraint && deferrable && deferrable !== 'not deferrable') {
+      throw new Error('postgres cannot create deferrable index');
+    }
+    if (useConstraint && predicate) {
+      throw new Error('postgres cannot create constraint with predicate');
     }
     deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
     indexName = indexName
       ? this.formatter.wrap(indexName)
       : this._indexCommand('unique', this.tableNameRaw, columns);
-    this.pushQuery(
-      `alter table ${this.tableName()} add constraint ${indexName}` +
-        ' unique (' +
-        this.formatter.columnize(columns) +
-        ')' +
-        deferrable
-    );
+
+    if (useConstraint) {
+      this.pushQuery(
+        `alter table ${this.tableName()} add constraint ${indexName}` +
+          ' unique (' +
+          this.formatter.columnize(columns) +
+          ')' +
+          deferrable
+      );
+    } else {
+      const predicateQuery = predicate
+        ? ' ' + this.client.queryCompiler(predicate).where()
+        : '';
+
+      this.pushQuery(
+        `create unique index ${indexName} on ${this.tableName()} (${this.formatter.columnize(
+          columns
+        )})${predicateQuery}`
+      );
+    }
   }
 
   index(columns, indexName, options) {
diff --git a/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js b/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
index 930693f1..5e1a9b2b 100644
--- a/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
+++ b/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
@@ -51,7 +51,7 @@ class TableCompiler_SQLite3 extends TableCompiler {
         const type = col.getColumnType();
 
         const defaultTo = col.modified['defaultTo']
-          ? formatDefault(col.modified['defaultTo'][0], type, this.client)
+          ? formatDefault(col.modified['defaultTo'][0], col.type, this.client)
           : null;
 
         const notNull =
@@ -132,8 +132,9 @@ class TableCompiler_SQLite3 extends TableCompiler {
   // Compile a unique key command.
   unique(columns, indexName) {
     let deferrable;
+    let predicate;
     if (isObject(indexName)) {
-      ({ indexName, deferrable } = indexName);
+      ({ indexName, deferrable, predicate } = indexName);
     }
     if (deferrable && deferrable !== 'not deferrable') {
       this.client.logger.warn(
@@ -144,8 +145,13 @@ class TableCompiler_SQLite3 extends TableCompiler {
       ? this.formatter.wrap(indexName)
       : this._indexCommand('unique', this.tableNameRaw, columns);
     columns = this.formatter.columnize(columns);
+
+    const predicateQuery = predicate
+      ? ' ' + this.client.queryCompiler(predicate).where()
+      : '';
+
     this.pushQuery(
-      `create unique index ${indexName} on ${this.tableName()} (${columns})`
+      `create unique index ${indexName} on ${this.tableName()} (${columns})${predicateQuery}`
     );
   }
 
diff --git a/lib/formatter/wrappingFormatter.js b/lib/formatter/wrappingFormatter.js
index dc4fda11..c9fe7ecf 100644
--- a/lib/formatter/wrappingFormatter.js
+++ b/lib/formatter/wrappingFormatter.js
@@ -225,8 +225,7 @@ function rawOrFn(value, method, builder, client, bindingHolder) {
       compileCallback(value, method, client, bindingHolder),
       undefined,
       builder,
-      client,
-      bindingHolder
+      client
     );
   }
   return unwrapRaw(value, undefined, builder, client, bindingHolder) || '';
diff --git a/package.json b/package.json
index 2ae9bb11..74206563 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "knex",
-  "version": "2.2.0",
+  "version": "2.3.0",
   "description": "A batteries-included SQL query & schema builder for PostgresSQL, MySQL, CockroachDB, MSSQL and SQLite3",
   "main": "knex",
   "types": "types/index.d.ts",
@@ -105,8 +105,8 @@
   },
   "devDependencies": {
     "@tsconfig/recommended": "^1.0.1",
-    "@types/node": "^18.0.4",
-    "better-sqlite3": "^7.5.1",
+    "@types/node": "^18.7.14",
+    "better-sqlite3": "^7.6.2",
     "chai": "^4.3.6",
     "chai-as-promised": "^7.1.1",
     "chai-subset-in-order": "^3.1.0",
@@ -123,25 +123,25 @@
     "JSONStream": "^1.3.5",
     "lint-staged": "^13.0.0",
     "mocha": "^10.0.0",
-    "mock-fs": "^5.1.2",
+    "mock-fs": "^5.1.4",
     "mysql": "^2.18.1",
     "mysql2": "^2.3.3",
     "nyc": "^15.1.0",
-    "oracledb": "^5.3.0",
-    "pg": "^8.7.3",
-    "pg-query-stream": "^4.2.1",
+    "oracledb": "^5.4.0",
+    "pg": "^8.8.0",
+    "pg-query-stream": "^4.2.4",
     "prettier": "2.6.2",
     "rimraf": "^3.0.2",
     "sinon": "^14.0.0",
     "sinon-chai": "^3.7.0",
     "source-map-support": "^0.5.21",
-    "sqlite3": "^5.0.4",
+    "sqlite3": "^5.0.11",
     "tap-spec": "^5.0.0",
-    "tape": "^5.5.3",
+    "tape": "^5.6.0",
     "tedious": "^14.4.0",
     "toxiproxy-node-client": "^2.0.6",
-    "ts-node": "^10.7.0",
-    "tsd": "^0.22.0",
+    "ts-node": "^10.9.1",
+    "tsd": "^0.23.0",
     "typescript": "4.8.2"
   },
   "buildDependencies": [
diff --git a/types/index.d.ts b/types/index.d.ts
index 69dc3c8d..b246bbe5 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -2513,6 +2513,7 @@ export declare namespace Knex {
         storageEngineIndexType?: string;
         deferrable?: deferrableType;
         useConstraint?: boolean;
+        predicate?: QueryBuilder;
       }>
     ): TableBuilder;
     /** @deprecated */
@@ -2699,6 +2700,7 @@ export declare namespace Knex {
     debug?: boolean;
     client?: string | typeof Client;
     dialect?: string;
+    jsonbSupport?: boolean;
     version?: string;
     connection?: string | StaticConnectionConfig | ConnectionConfigProvider;
     pool?: PoolConfig;
@@ -2887,6 +2889,7 @@ export declare namespace Knex {
       multiSubnetFailover?: boolean;
       packetSize?: number;
       trustServerCertificate?: boolean;
+      mapBinding?: (value: any) => ({ value: any, type: any } | undefined);
     }>;
     pool?: Readonly<{
       min?: number;


Test patch:
diff --git a/test/integration2/dialects/mssql.spec.js b/test/integration2/dialects/mssql.spec.js
index 25c488d4..1dfe3e25 100644
--- a/test/integration2/dialects/mssql.spec.js
+++ b/test/integration2/dialects/mssql.spec.js
@@ -1,4 +1,5 @@
 const { expect } = require('chai');
+const { TYPES } = require('tedious');
 const { getAllDbs, getKnexForDb } = require('../util/knex-instance-provider');
 
 async function fetchDefaultConstraint(knex, table, column) {
@@ -31,6 +32,8 @@ describe('MSSQL dialect', () => {
         beforeEach(async () => {
           await knex.schema.createTable('test', function () {
             this.increments('id').primary();
+            this.specificType('varchar', 'varchar(100)');
+            this.string('nvarchar');
           });
         });
 
@@ -362,6 +365,31 @@ describe('MSSQL dialect', () => {
             return result ? result.comment : undefined;
           }
         });
+
+        describe('supports mapBinding config', async () => {
+          it('can remap types', async () => {
+            const query = knex('test')
+              .where('varchar', { value: 'testing', type: TYPES.VarChar })
+              .select('id');
+            const { bindings } = query.toSQL().toNative();
+            expect(bindings[0].type, TYPES.VarChar);
+            expect(bindings[0].value, 'testing');
+
+            // verify the query runs successfully
+            await query;
+          });
+          it('undefined mapBinding result falls back to default implementation', async () => {
+            const query = knex('test')
+              .where('nvarchar', 'testing')
+              .select('id');
+
+            const { bindings } = query.toSQL().toNative();
+            expect(bindings[0], 'testing');
+
+            // verify the query runs successfully
+            await query;
+          });
+        });
       });
     });
 });
diff --git a/test/integration2/schema/alter.spec.js b/test/integration2/schema/alter.spec.js
index cbfbbaae..2fff3444 100644
--- a/test/integration2/schema/alter.spec.js
+++ b/test/integration2/schema/alter.spec.js
@@ -34,6 +34,7 @@ describe('Schema', () => {
               table.dateTime('column_datetime');
 
               table.integer('column_defaultTo').defaultTo(0);
+              table.boolean('column_boolean_defaultTo').defaultTo(false);
               table.string('column_notNullable').notNullable();
               table
                 .dateTime('column_defaultToAndNotNullable')
@@ -96,7 +97,7 @@ describe('Schema', () => {
               expect(item_one.column_string).to.be.a('number');
               expect(item_one.column_datetime).to.be.a('number');
               expect(tableAfter).to.equal(
-                "CREATE TABLE \"alter_table\" (`column_integer` varchar(255), `column_string` integer, `column_datetime` date, `column_defaultTo` integer DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)"
+                "CREATE TABLE \"alter_table\" (`column_integer` varchar(255), `column_string` integer, `column_datetime` date, `column_defaultTo` integer DEFAULT '0', `column_boolean_defaultTo` boolean DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)"
               );
             });
 
@@ -128,13 +129,14 @@ describe('Schema', () => {
                 "insert into `alter_table` (`column_notNullable`) values ('text') - SQLITE_CONSTRAINT: NOT NULL constraint failed: alter_table.column_string"
               );
               expect(tableAfter).to.equal(
-                "CREATE TABLE \"alter_table\" (`column_integer` integer DEFAULT '0', `column_string` varchar(255) NOT NULL, `column_datetime` datetime NOT NULL DEFAULT '0', `column_defaultTo` integer DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)"
+                "CREATE TABLE \"alter_table\" (`column_integer` integer DEFAULT '0', `column_string` varchar(255) NOT NULL, `column_datetime` datetime NOT NULL DEFAULT '0', `column_defaultTo` integer DEFAULT '0', `column_boolean_defaultTo` boolean DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)"
               );
             });
 
             it('removes not specified default and not null constraints', async () => {
               await knex.schema.alterTable('alter_table', (table) => {
                 table.integer('column_defaultTo').alter();
+                table.boolean('column_boolean_defaultTo').alter();
                 table.string('column_notNullable').alter();
                 table.dateTime('column_defaultToAndNotNullable').alter();
               });
@@ -145,10 +147,11 @@ describe('Schema', () => {
               const tableAfter = (await knex.raw(QUERY_TABLE))[0].sql;
 
               expect(item_two.column_defaultTo).to.be.null;
+              expect(item_two.column_boolean_defaultTo).to.be.null;
               expect(item_two.column_notNullable).to.be.null;
               expect(item_two.column_defaultToAndNotNullable).to.be.null;
               expect(tableAfter).to.equal(
-                'CREATE TABLE "alter_table" (`column_integer` integer, `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer, `column_notNullable` varchar(255), `column_defaultToAndNotNullable` datetime, `column_nullable` boolean NULL)'
+                'CREATE TABLE "alter_table" (`column_integer` integer, `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer, `column_boolean_defaultTo` boolean, `column_notNullable` varchar(255), `column_defaultToAndNotNullable` datetime, `column_nullable` boolean NULL)'
               );
             });
 
@@ -166,7 +169,28 @@ describe('Schema', () => {
                 "insert into `alter_table` (`column_notNullable`) values ('text') - SQLITE_CONSTRAINT: NOT NULL constraint failed: alter_table.column_nullable"
               );
               expect(tableAfter).to.equal(
-                "CREATE TABLE \"alter_table\" (`column_integer` integer, `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NOT NULL)"
+                "CREATE TABLE \"alter_table\" (`column_integer` integer, `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer DEFAULT '0', `column_boolean_defaultTo` boolean DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NOT NULL)"
+              );
+            });
+
+            it('properly changes the default value of a boolean column', async () => {
+              await knex.schema.alterTable('alter_table', (table) => {
+                table
+                  .boolean('column_boolean_defaultTo')
+                  .defaultTo(true)
+                  .alter();
+              });
+
+              await knex('alter_table').insert({
+                column_notNullable: 'text',
+              });
+
+              const item_two = (await knex('alter_table'))[1];
+              const tableAfter = (await knex.raw(QUERY_TABLE))[0].sql;
+
+              expect(item_two.column_boolean_defaultTo).to.equal(1);
+              expect(tableAfter).to.equal(
+                "CREATE TABLE \"alter_table\" (`column_integer` integer, `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer DEFAULT '0', `column_boolean_defaultTo` boolean DEFAULT '1', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)"
               );
             });
 
@@ -178,7 +202,7 @@ describe('Schema', () => {
               const queries = await builder.generateDdlCommands();
 
               expect(queries.sql).to.deep.equal([
-                "CREATE TABLE `_knex_temp_alter111` (`column_integer` varchar(255), `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)",
+                "CREATE TABLE `_knex_temp_alter111` (`column_integer` varchar(255), `column_string` varchar(255), `column_datetime` datetime, `column_defaultTo` integer DEFAULT '0', `column_boolean_defaultTo` boolean DEFAULT '0', `column_notNullable` varchar(255) NOT NULL, `column_defaultToAndNotNullable` datetime NOT NULL DEFAULT '0', `column_nullable` boolean NULL)",
                 'INSERT INTO "_knex_temp_alter111" SELECT * FROM "alter_table";',
                 'DROP TABLE "alter_table"',
                 'ALTER TABLE "_knex_temp_alter111" RENAME TO "alter_table"',
diff --git a/test/integration2/schema/misc.spec.js b/test/integration2/schema/misc.spec.js
index 20db0132..befed615 100644
--- a/test/integration2/schema/misc.spec.js
+++ b/test/integration2/schema/misc.spec.js
@@ -1659,6 +1659,43 @@ describe('Schema (misc)', () => {
             expect(results).to.not.be.empty;
           });
         });
+
+        describe('supports partial unique indexes - postgres, sqlite, and mssql', function () {
+          it('allows creating a unique index with predicate', async function () {
+            if (!(isPostgreSQL(knex) || isMssql(knex) || isSQLite(knex))) {
+              return this.skip();
+            }
+
+            await knex.schema.table('test_table_one', function (t) {
+              t.unique('email', {
+                indexName: 'email_idx',
+                predicate: knex.whereNotNull('email'),
+              });
+            });
+          });
+
+          it('actually stores the predicate in the Postgres server', async function () {
+            if (!isPostgreSQL(knex)) {
+              return this.skip();
+            }
+            await knex.schema.table('test_table_one', function (t) {
+              t.unique('email', {
+                indexName: 'email_idx_2',
+                predicate: knex.whereNotNull('email'),
+              });
+            });
+            const results = await knex
+              .from('pg_class')
+              .innerJoin('pg_index', 'pg_index.indexrelid', 'pg_class.oid')
+              .where({
+                relname: 'email_idx_2',
+                indisvalid: true,
+                indisunique: true,
+              })
+              .whereNotNull('indpred');
+            expect(results).to.not.be.empty;
+          });
+        });
       });
 
       describe('hasTable', () => {
diff --git a/test/integration2/util/knex-instance-provider.js b/test/integration2/util/knex-instance-provider.js
index f5a68e5c..c287bd3b 100644
--- a/test/integration2/util/knex-instance-provider.js
+++ b/test/integration2/util/knex-instance-provider.js
@@ -173,6 +173,13 @@ const testConfigs = {
       server: 'localhost',
       port: 21433,
       database: 'knex_test',
+      options: {
+        mapBinding(value) {
+          if (value && value.type) {
+            return { value: value.value, type: value.type };
+          }
+        },
+      },
     },
     pool: pool,
     migrations,
diff --git a/test/unit/schema-builder/mssql.js b/test/unit/schema-builder/mssql.js
index d33200f9..eeef5a09 100644
--- a/test/unit/schema-builder/mssql.js
+++ b/test/unit/schema-builder/mssql.js
@@ -641,6 +641,53 @@ describe('MSSQL SchemaBuilder', function () {
     );
   });
 
+  it('test adding unique index with a predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'CREATE UNIQUE INDEX [baz] ON [users] ([foo], [bar]) where email = "foo@bar"'
+    );
+  });
+
+  it('test adding unique index with a where not null predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereNotNull('email'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'CREATE UNIQUE INDEX [baz] ON [users] ([foo], [bar]) where [email] is not null'
+    );
+  });
+
+  it('throws when adding unique constraint with predicate', function () {
+    expect(() => {
+      client
+        .schemaBuilder()
+        .table('users', function (table) {
+          table.unique(['foo', 'bar'], {
+            indexName: 'baz',
+            useConstraint: true,
+            predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
+          });
+        })
+        .toSQL();
+    }).to.throw('mssql cannot create constraint with predicate');
+  });
+
   it('test adding foreign key', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/postgres.js b/test/unit/schema-builder/postgres.js
index a77f68c7..fb9065d5 100644
--- a/test/unit/schema-builder/postgres.js
+++ b/test/unit/schema-builder/postgres.js
@@ -93,6 +93,41 @@ describe('PostgreSQL Config', function () {
         );
       });
     });
+
+    describe('check custom pg client', function () {
+      it('jsonb as text', function () {
+        knexInstance = knex({
+          ...config,
+          version: 'custom-pg',
+          jsonbSupport: false,
+        });
+        tableSql = knexInstance.schema
+          .table('public', function (t) {
+            t.jsonb('test_name');
+          })
+          .toSQL();
+        equal(1, tableSql.length);
+        expect(tableSql[0].sql).to.equal(
+          'alter table "public" add column "test_name" text'
+        );
+      });
+      it('jsonb', function () {
+        knexInstance = knex({
+          ...config,
+          version: 'custom-pg',
+          jsonbSupport: true,
+        });
+        tableSql = knexInstance.schema
+          .table('public', function (t) {
+            t.jsonb('test_name');
+          })
+          .toSQL();
+        equal(1, tableSql.length);
+        expect(tableSql[0].sql).to.equal(
+          'alter table "public" add column "test_name" jsonb'
+        );
+      });
+    });
   });
 });
 
@@ -880,6 +915,22 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding unique index', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique('foo', {
+          indexName: 'bar',
+          useConstraint: false,
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index "bar" on "users" ("foo")'
+    );
+  });
+
   it('adding index without value', function () {
     tableSql = client
       .schemaBuilder()
@@ -1013,6 +1064,68 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
+  it('adding unique index with a predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index "baz" on "users" ("foo", "bar") where email = "foo@bar"'
+    );
+  });
+
+  it('adding unique index with a where not null predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereNotNull('email'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index "baz" on "users" ("foo", "bar") where "email" is not null'
+    );
+  });
+
+  it('throws when adding unique constraint with a predicate', function () {
+    expect(() => {
+      client
+        .schemaBuilder()
+        .table('users', function (table) {
+          table.unique(['foo', 'bar'], {
+            indexName: 'baz',
+            useConstraint: true,
+            predicate: client.queryBuilder().whereNotNull('email'),
+          });
+        })
+        .toSQL();
+    }).to.throw('postgres cannot create constraint with predicate');
+  });
+
+  it('throws when adding unique index with deferrable set', function () {
+    expect(() => {
+      client
+        .schemaBuilder()
+        .table('users', function (table) {
+          table.unique(['foo', 'bar'], {
+            indexName: 'baz',
+            useConstraint: false,
+            deferrable: 'immediate',
+          });
+        })
+        .toSQL();
+    }).to.throw('postgres cannot create deferrable index');
+  });
+
   it('adding incrementing id', function () {
     tableSql = client
       .schemaBuilder()
diff --git a/test/unit/schema-builder/sqlite3.js b/test/unit/schema-builder/sqlite3.js
index 164b52e4..fa5742cb 100644
--- a/test/unit/schema-builder/sqlite3.js
+++ b/test/unit/schema-builder/sqlite3.js
@@ -547,6 +547,38 @@ describe('SQLite SchemaBuilder', function () {
     );
   });
 
+  it('adding unique index with a predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index `baz` on `users` (`foo`, `bar`) where email = "foo@bar"'
+    );
+  });
+
+  it('adding unique index with a where not null predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.unique(['foo', 'bar'], {
+          indexName: 'baz',
+          predicate: client.queryBuilder().whereNotNull('email'),
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index `baz` on `users` (`foo`, `bar`) where `email` is not null'
+    );
+  });
+
   it('adding incrementing id', function () {
     tableSql = client
       .schemaBuilder()