Repository: knex/knex
Pull request #5509 (2023-03-07 05:28:59)

[Sqlite3] Fix can not get table sql because of case sensitive
## Example

Here is an example how to reproduce the issue.
https://github.com/zydmayday/learn-knex/blob/main/knex/knex.test.ts#L34-L44

## Details

Use sqlite3 as database for testing, and has some migration scripts for setting up tables.

If we have one script create a table called "some_table(https://github.com/zydmayday/learn-knex/blob/main/knex/migrations/1_create_table.ts)",
and another script alter table but using table name "Some_Table(https://github.com/zydmayday/learn-knex/blob/main/knex/migrations/2_add_columns.ts)".
Then after running jest, we will have the following error logs.

```
TypeError: Cannot read properties of undefined (reading 'sql')

      at client.transaction.connection (node_modules/knex/lib/dialects/sqlite3/schema/ddl.js:56:13)
```

## How to fix

Because the related SQL script is case sensitive, change it to run in a case insensitive way.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/57606686/badge)](https://coveralls.io/builds/57606686)

Coverage: 92.392%. Remained the same when pulling **dd8f2a96d5a87efaa942c4bcef2d79bfd6a1edd9 on zydmayday:master** into **0d27bcb60b9bfd57fabf896715350089e0b1050c on knex:master**.

zydmayday: @OlivierCavadenti Thanks for the label, i added the tests.
kibertoad: If SQLite is case-sensitive, why should knex hide that?
does it behave differently between DDL operations and querying? would same code work case-insensitively for querying?
zydmayday: > If SQLite is case-sensitive, why should knex hide that? does it behave differently between DDL operations and querying? would same code work case-insensitively for querying?

Hi @kibertoad 
Thanks for replying.
Actually it is not because sqlite3 is case sensitive.
It is because the query in function `getTableSql` is case sensitive.

When we try to get some tables DDL, we don't care if we pass the UPPERCASE or lowercase of table name.
In fact, sqlite3 is not case sensitive as I searched.
kibertoad: got it, thanks!

Patch:
diff --git a/.github/workflows/codeql-analysis.yml b/.github/workflows/codeql-analysis.yml
index 0cbb8f0f..d32a422b 100644
--- a/.github/workflows/codeql-analysis.yml
+++ b/.github/workflows/codeql-analysis.yml
@@ -8,8 +8,15 @@ on:
   schedule:
     - cron: '36 2 * * 0'
 
+permissions:
+  contents: read
+
 jobs:
   analyze:
+    permissions:
+      actions: read  # for github/codeql-action/init to get workflow details
+      contents: read  # for actions/checkout to fetch code
+      security-events: write  # for github/codeql-action/analyze to upload SARIF results
     name: Analyze
     runs-on: ubuntu-latest
 
diff --git a/.github/workflows/coverage.yml b/.github/workflows/coverage.yml
index e84ae706..f5ff4003 100644
--- a/.github/workflows/coverage.yml
+++ b/.github/workflows/coverage.yml
@@ -4,8 +4,14 @@ name: Coverage
 
 on: ["push", "pull_request"]
 
+permissions:
+  contents: read
+
 jobs:
   build:
+    permissions:
+      checks: write  # for coverallsapp/github-action to create new checks
+      contents: read  # for actions/checkout to fetch code
     runs-on: ubuntu-latest
 
     name: Coverage
@@ -22,7 +28,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.5.1
+        uses: actions/setup-node@v3.6.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/.github/workflows/linting.yml b/.github/workflows/linting.yml
index 46a0e3df..2e451413 100644
--- a/.github/workflows/linting.yml
+++ b/.github/workflows/linting.yml
@@ -10,6 +10,9 @@ on:
     branches:
       - master
 
+permissions:
+  contents: read
+
 jobs:
   build:
     runs-on: ubuntu-latest
@@ -26,7 +29,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.5.1
+        uses: actions/setup-node@v3.6.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 0678b796..8f57f888 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,18 +1,18 @@
 # Master (Unreleased)
 
-# 2.4.2 - 22 January, 2022
+# 2.4.2 - 22 January, 2023
 
 ### Bug fixes
 
 - CLI: Fix incorrent EOL causing errors on Linux #5455
 
-# 2.4.1 - 18 January, 2022
+# 2.4.1 - 18 January, 2023
 
 ### Bug fixes
 
 - PostgreSQL: Fix Malformed array literal 2.4.0 Regression #5439
 
-# 2.4.0 - 06 January, 2022
+# 2.4.0 - 06 January, 2023
 
 ### New features:
 
diff --git a/lib/dialects/better-sqlite3/index.js b/lib/dialects/better-sqlite3/index.js
index 548bf737..18cf1576 100644
--- a/lib/dialects/better-sqlite3/index.js
+++ b/lib/dialects/better-sqlite3/index.js
@@ -9,7 +9,11 @@ class Client_BetterSQLite3 extends Client_SQLite3 {
 
   // Get a raw connection from the database, returning a promise with the connection object.
   async acquireRawConnection() {
-    return new this.driver(this.connectionSettings.filename);
+    const options = this.connectionSettings.options || {};
+
+    return new this.driver(this.connectionSettings.filename, {
+      nativeBinding: options.nativeBinding,
+    });
   }
 
   // Used to explicitly close a connection, called internally by the pool when
diff --git a/lib/dialects/oracledb/schema/oracledb-columncompiler.js b/lib/dialects/oracledb/schema/oracledb-columncompiler.js
index 117a74f4..df4bb058 100644
--- a/lib/dialects/oracledb/schema/oracledb-columncompiler.js
+++ b/lib/dialects/oracledb/schema/oracledb-columncompiler.js
@@ -4,7 +4,7 @@ const { isObject } = require('../../../util/is');
 class ColumnCompiler_Oracledb extends ColumnCompiler_Oracle {
   constructor() {
     super(...arguments);
-    this.modifiers = ['defaultTo', 'nullable', 'comment'];
+    this.modifiers = ['defaultTo', 'nullable', 'comment', 'checkJson'];
     this._addCheckModifiers();
   }
 
@@ -38,14 +38,20 @@ class ColumnCompiler_Oracledb extends ColumnCompiler_Oracle {
   }
 
   json() {
-    return `varchar2(4000) check (${this.formatter.columnize(
-      this.getColumnName()
-    )} is json)`;
+    // implicitly add the check for json
+    this.columnBuilder._modifiers.checkJson = [
+      this.formatter.columnize(this.getColumnName()),
+    ];
+    return 'varchar2(4000)';
   }
 
   jsonb() {
     return this.json();
   }
+
+  checkJson(column) {
+    return `check (${column} is json)`;
+  }
 }
 
 ColumnCompiler_Oracledb.prototype.time = 'timestamp with local time zone';
diff --git a/lib/dialects/postgres/execution/pg-transaction.js b/lib/dialects/postgres/execution/pg-transaction.js
index 43561c44..553ada1e 100644
--- a/lib/dialects/postgres/execution/pg-transaction.js
+++ b/lib/dialects/postgres/execution/pg-transaction.js
@@ -2,10 +2,17 @@ const Transaction = require('../../../execution/transaction');
 
 class Transaction_PG extends Transaction {
   begin(conn) {
-    if (this.isolationLevel) {
-      return this.query(conn, `BEGIN ISOLATION LEVEL ${this.isolationLevel};`);
+    const trxMode = [
+      this.isolationLevel ? `ISOLATION LEVEL ${this.isolationLevel}` : '',
+      this.readOnly ? 'READ ONLY' : '',
+    ]
+      .join(' ')
+      .trim();
+
+    if (trxMode.length === 0) {
+      return this.query(conn, 'BEGIN;');
     }
-    return this.query(conn, 'BEGIN;');
+    return this.query(conn, `BEGIN TRANSACTION ${trxMode};`);
   }
 }
 
diff --git a/lib/dialects/redshift/transaction.js b/lib/dialects/redshift/transaction.js
index 8849bc38..10e6ef91 100644
--- a/lib/dialects/redshift/transaction.js
+++ b/lib/dialects/redshift/transaction.js
@@ -2,10 +2,17 @@ const Transaction = require('../../execution/transaction');
 
 module.exports = class Redshift_Transaction extends Transaction {
   begin(conn) {
-    if (this.isolationLevel) {
-      return this.query(conn, `BEGIN ISOLATION LEVEL ${this.isolationLevel};`);
+    const trxMode = [
+      this.isolationLevel ? `ISOLATION LEVEL ${this.isolationLevel}` : '',
+      this.readOnly ? 'READ ONLY' : '',
+    ]
+      .join(' ')
+      .trim();
+
+    if (trxMode.length === 0) {
+      return this.query(conn, 'BEGIN;');
     }
-    return this.query(conn, 'BEGIN;');
+    return this.query(conn, `BEGIN ${trxMode};`);
   }
 
   savepoint(conn) {
diff --git a/lib/dialects/sqlite3/execution/sqlite-transaction.js b/lib/dialects/sqlite3/execution/sqlite-transaction.js
index 5477bb1a..99a3e789 100644
--- a/lib/dialects/sqlite3/execution/sqlite-transaction.js
+++ b/lib/dialects/sqlite3/execution/sqlite-transaction.js
@@ -11,6 +11,13 @@ class Transaction_Sqlite extends Transaction {
         'sqlite3 only supports serializable transactions, ignoring the isolation level param'
       );
     }
+    // SQLite infers read vs write transactions from the statement operation
+    // https://www.sqlite.org/lang_transaction.html#read_transactions_versus_write_transactions
+    if (this.readOnly) {
+      this.client.logger.warn(
+        'sqlite3 implicitly handles read vs write transactions'
+      );
+    }
     return this.query(conn, 'BEGIN;');
   }
 }
diff --git a/lib/dialects/sqlite3/schema/internal/sqlite-ddl-operations.js b/lib/dialects/sqlite3/schema/internal/sqlite-ddl-operations.js
index 4a66f1ae..73371cf0 100644
--- a/lib/dialects/sqlite3/schema/internal/sqlite-ddl-operations.js
+++ b/lib/dialects/sqlite3/schema/internal/sqlite-ddl-operations.js
@@ -15,7 +15,7 @@ function renameTable(tableName, alteredName) {
 }
 
 function getTableSql(tableName) {
-  return `SELECT type, sql FROM sqlite_master WHERE (type='table' OR (type='index' AND sql IS NOT NULL)) AND tbl_name='${tableName}'`;
+  return `SELECT type, sql FROM sqlite_master WHERE (type='table' OR (type='index' AND sql IS NOT NULL)) AND lower(tbl_name)='${tableName.toLowerCase()}'`;
 }
 
 function isForeignCheckEnabled() {
diff --git a/lib/execution/transaction.js b/lib/execution/transaction.js
index 4e2c7cf3..40532cfc 100644
--- a/lib/execution/transaction.js
+++ b/lib/execution/transaction.js
@@ -55,6 +55,7 @@ class Transaction extends EventEmitter {
     this._completed = false;
     this._debug = client.config && client.config.debug;
 
+    this.readOnly = config.readOnly;
     if (config.isolationLevel) {
       this.setIsolationLevel(config.isolationLevel);
     }
@@ -98,13 +99,20 @@ class Transaction extends EventEmitter {
   }
 
   begin(conn) {
-    const runIsolationLevelQuery = this.isolationLevel
-      ? this.query(
-          conn,
-          `SET TRANSACTION ISOLATION LEVEL ${this.isolationLevel};`
-        )
-      : Promise.resolve();
-    return runIsolationLevelQuery.then(() => this.query(conn, 'BEGIN;'));
+    const trxMode = [
+      this.isolationLevel ? `ISOLATION LEVEL ${this.isolationLevel}` : '',
+      this.readOnly ? 'READ ONLY' : '',
+    ]
+      .join(' ')
+      .trim();
+
+    if (trxMode.length === 0) {
+      return this.query(conn, 'BEGIN;');
+    }
+
+    return this.query(conn, `SET TRANSACTION ${trxMode};`).then(() =>
+      this.query(conn, 'BEGIN;')
+    );
   }
 
   savepoint(conn) {
diff --git a/lib/query/method-constants.js b/lib/query/method-constants.js
index dc28cb6b..afafe07b 100644
--- a/lib/query/method-constants.js
+++ b/lib/query/method-constants.js
@@ -66,6 +66,7 @@ module.exports = [
   'union',
   'unionAll',
   'intersect',
+  'except',
   'having',
   'havingRaw',
   'orHaving',
diff --git a/lib/query/querybuilder.js b/lib/query/querybuilder.js
index 096e35ee..547a6040 100644
--- a/lib/query/querybuilder.js
+++ b/lib/query/querybuilder.js
@@ -817,30 +817,12 @@ class Builder extends EventEmitter {
     return this._union('union all', args);
   }
 
-  // Adds an intersect statement to the query
-  intersect(callbacks, wrap) {
-    if (arguments.length === 1 || (arguments.length === 2 && isBoolean(wrap))) {
-      if (!Array.isArray(callbacks)) {
-        callbacks = [callbacks];
-      }
-      for (let i = 0, l = callbacks.length; i < l; i++) {
-        this._statements.push({
-          grouping: 'union',
-          clause: 'intersect',
-          value: callbacks[i],
-          wrap: wrap || false,
-        });
-      }
-    } else {
-      callbacks = toArray(arguments).slice(0, arguments.length - 1);
-      wrap = arguments[arguments.length - 1];
-      if (!isBoolean(wrap)) {
-        callbacks.push(wrap);
-        wrap = false;
-      }
-      this.intersect(callbacks, wrap);
-    }
-    return this;
+  intersect(...args) {
+    return this._union('intersect', args);
+  }
+
+  except(...args) {
+    return this._union('except', args);
   }
 
   // Adds a `having` clause to the query.
diff --git a/package.json b/package.json
index 4509fe35..31998521 100644
--- a/package.json
+++ b/package.json
@@ -62,7 +62,7 @@
   },
   "dependencies": {
     "colorette": "2.0.19",
-    "commander": "^9.1.0",
+    "commander": "^10.0.0",
     "debug": "4.3.4",
     "escalade": "^3.1.1",
     "esm": "^3.2.25",
@@ -127,12 +127,12 @@
     "mocha": "^10.0.0",
     "mock-fs": "^5.1.4",
     "mysql": "^2.18.1",
-    "mysql2": "^2.3.3",
+    "mysql2": "^3.2.0",
     "nyc": "^15.1.0",
     "oracledb": "^5.4.0",
     "pg": "^8.8.0",
     "pg-query-stream": "^4.2.4",
-    "prettier": "2.8.3",
+    "prettier": "2.8.7",
     "rimraf": "^3.0.2",
     "sinon": "^15.0.1",
     "sinon-chai": "^3.7.0",
@@ -143,8 +143,8 @@
     "tedious": "^14.4.0",
     "toxiproxy-node-client": "^2.0.6",
     "ts-node": "^10.9.1",
-    "tsd": "^0.25.0",
-    "typescript": "4.8.3"
+    "tsd": "^0.28.1",
+    "typescript": "5.0.2"
   },
   "buildDependencies": [
     "rimraf"
@@ -251,7 +251,8 @@
     ],
     "exclude": [
       "lib/dialects/oracle",
-      "lib/dialects/oracledb"
+      "lib/dialects/oracledb",
+      "test/**/*.spec.js"
     ]
   },
   "tsd": {
diff --git a/types/index.d.ts b/types/index.d.ts
index 70f93ee8..119f9aa1 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -708,12 +708,11 @@ export declare namespace Knex {
     // Partition by
     partitionBy: PartitionBy<TRecord, TResult>;
 
-    // Intersect
-    intersect: Intersect<TRecord, TResult>;
-
-    // Union
+    // Unions
     union: Union<TRecord, TResult>;
     unionAll: Union<TRecord, TResult>;
+    intersect: Intersect<TRecord, TResult>;
+    except: Except<TRecord, TResult>;
 
     // Having
     having: Having<TRecord, TResult>;
@@ -729,6 +728,8 @@ export declare namespace Knex {
     havingNotIn: HavingRange<TRecord, TResult>;
     andHavingNotIn: HavingRange<TRecord, TResult>;
     orHavingNotIn: HavingRange<TRecord, TResult>;
+    havingNull: HavingNull<TRecord, TResult>;
+    havingNotNull: HavingNull<TRecord, TResult>;
 
     // Clear
     clearSelect(): QueryBuilder<
@@ -788,6 +789,11 @@ export declare namespace Knex {
       columnName: string,
       amount?: number
     ): QueryBuilder<TRecord, number>;
+    increment(
+      columns: {
+        [column in keyof TRecord]: number
+      }
+    ): QueryBuilder<TRecord, number>;
 
     decrement(
       columnName: keyof TRecord,
@@ -1976,6 +1982,9 @@ export declare namespace Knex {
     ): QueryBuilder<TRecord, TResult>;
   }
 
+  interface Except<TRecord extends {} = any, TResult = unknown[]>
+    extends Intersect<TRecord, TResult> {}
+
   interface Union<TRecord extends {} = any, TResult = unknown[]>
     extends Intersect<TRecord, TResult> {}
 
@@ -2007,6 +2016,11 @@ export declare namespace Knex {
     >;
   }
 
+  interface HavingNull<TRecord extends {} = any, TResult = unknown[]> {
+    (columnName: keyof TRecord): QueryBuilder<TRecord, TResult>;
+    (columnName: string): QueryBuilder<TRecord, TResult>;
+  }
+
   // commons
 
   interface ColumnNameQueryBuilder<
@@ -2290,6 +2304,7 @@ export declare namespace Knex {
     userParams?: Record<string, any>;
     doNotRejectOnRollback?: boolean;
     connection?: any;
+    readOnly?: boolean;
   }
 
   interface Transaction<TRecord extends {} = any, TResult = any[]>
@@ -2336,11 +2351,11 @@ export declare namespace Knex {
     dropViewIfExists(viewName: string): SchemaBuilder;
     dropMaterializedView(viewName: string): SchemaBuilder;
     dropMaterializedViewIfExists(viewName: string): SchemaBuilder;
-    renameView(oldViewName: string, newViewName: string): Promise<void>;
+    renameView(oldViewName: string, newViewName: string): SchemaBuilder;
     view(
       viewName: string,
       callback: (viewBuilder: AlterViewBuilder) => any
-    ): Promise<void>;
+    ): SchemaBuilder;
     alterView(
       viewName: string,
       callback: (tableBuilder: AlterViewBuilder) => any
@@ -2370,7 +2385,7 @@ export declare namespace Knex {
     table(
       tableName: string,
       callback: (tableBuilder: AlterTableBuilder) => any
-    ): Promise<void>;
+    ): SchemaBuilder;
     dropTableIfExists(tableName: string): SchemaBuilder;
 
     // Schema
@@ -2507,7 +2522,7 @@ export declare namespace Knex {
     setNullable(column: string): TableBuilder;
     dropNullable(column: string): TableBuilder;
     unique(
-      columnNames: readonly (string | Raw)[],
+      columnNames: string | readonly (string | Raw)[],
       options?: Readonly<{
         indexName?: string;
         storageEngineIndexType?: string;
@@ -2518,7 +2533,7 @@ export declare namespace Knex {
     ): TableBuilder;
     /** @deprecated */
     unique(
-      columnNames: readonly (string | Raw)[],
+      columnNames: string | readonly (string | Raw)[],
       indexName?: string
     ): TableBuilder;
     foreign(column: string, foreignKeyName?: string): ForeignConstraintBuilder;
@@ -2730,6 +2745,7 @@ export declare namespace Knex {
     | PgConnectionConfig
     | RedshiftConnectionConfig
     | Sqlite3ConnectionConfig
+    | BetterSqlite3ConnectionConfig
     | SocketConnectionConfig;
 
   type ConnectionConfigProvider =
@@ -3044,6 +3060,14 @@ export declare namespace Knex {
     expirationChecker?(): boolean;
   }
 
+  /** Used with `better-sqlite3` adapter */
+  interface BetterSqlite3ConnectionConfig {
+    filename: string;
+    options?: {
+      nativeBinding?: string;
+    };
+  }
+
   interface SocketConnectionConfig {
     socketPath: string;
     user: string;


Test patch:
diff --git a/.github/workflows/integration-tests.yml b/.github/workflows/integration-tests.yml
index 3dd2afc7..72a865f7 100644
--- a/.github/workflows/integration-tests.yml
+++ b/.github/workflows/integration-tests.yml
@@ -10,6 +10,9 @@ on:
     branches:
       - master
 
+permissions:
+  contents: read
+
 jobs:
   build:
     runs-on: ubuntu-latest
@@ -28,7 +31,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.5.1
+        uses: actions/setup-node@v3.6.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
@@ -105,7 +108,7 @@ jobs:
         with: { fetch-depth: 1 }
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.5.1
+        uses: actions/setup-node@v3.6.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/.github/workflows/unit-tests.yml b/.github/workflows/unit-tests.yml
index 36d848be..dd744034 100644
--- a/.github/workflows/unit-tests.yml
+++ b/.github/workflows/unit-tests.yml
@@ -10,6 +10,9 @@ on:
     branches:
       - master
 
+permissions:
+  contents: read
+
 jobs:
   build:
     runs-on: ubuntu-latest
@@ -26,7 +29,7 @@ jobs:
           fetch-depth: 1
 
       - name: Setup Node ${{ matrix.node-version }}
-        uses: actions/setup-node@v3.5.1
+        uses: actions/setup-node@v3.6.0
         with:
           always-auth: false
           node-version: ${{ matrix.node-version }}
diff --git a/test-tsd/having.test-d.ts b/test-tsd/having.test-d.ts
new file mode 100644
index 00000000..ed2a6bd5
--- /dev/null
+++ b/test-tsd/having.test-d.ts
@@ -0,0 +1,22 @@
+import Knex from '../types';
+import { clientConfig, User } from './common';
+import { expectType } from 'tsd';
+
+const knex = Knex(clientConfig);
+
+const main = async () => {
+  expectType<User[]>(
+    await knex<User>('users')
+      .groupBy('count')
+      .orderBy('name', 'desc')
+      .havingNull('age')
+  );
+
+  expectType<User[]>(
+    await knex<User>('users')
+      .groupBy('count')
+      .orderBy('name', 'desc')
+      .havingNotNull('age')
+  );
+
+};
\ No newline at end of file
diff --git a/test/integration2/migrate/alter-table-ignore-case-sensitive/01_create.js b/test/integration2/migrate/alter-table-ignore-case-sensitive/01_create.js
new file mode 100644
index 00000000..4d96064c
--- /dev/null
+++ b/test/integration2/migrate/alter-table-ignore-case-sensitive/01_create.js
@@ -0,0 +1,9 @@
+exports.up = async (knex) => {
+  await knex.schema.createTableIfNotExists('some_table', (table) => {
+    table.increments();
+  });
+};
+
+exports.down = async (knex) => {
+  await knex.schema.dropTableIfExists('some_table');
+};
diff --git a/test/integration2/migrate/alter-table-ignore-case-sensitive/02_add_column.js b/test/integration2/migrate/alter-table-ignore-case-sensitive/02_add_column.js
new file mode 100644
index 00000000..946d5221
--- /dev/null
+++ b/test/integration2/migrate/alter-table-ignore-case-sensitive/02_add_column.js
@@ -0,0 +1,13 @@
+exports.up = async (knex) => {
+  // Use the same table but different case.
+  await knex.schema.table('Some_Table', (table) => {
+    table.string('other_id').after('id');
+    table.string('name').after('other_id');
+  });
+};
+
+exports.down = async (knex) => {
+  await knex.schema.table('Some_Table', (table) => {
+    table.dropColumns('other_id', 'name');
+  });
+};
diff --git a/test/integration2/migrate/migration-integration.spec.js b/test/integration2/migrate/migration-integration.spec.js
index 599c0f3e..ffb4b06c 100644
--- a/test/integration2/migrate/migration-integration.spec.js
+++ b/test/integration2/migrate/migration-integration.spec.js
@@ -115,6 +115,31 @@ describe('Migrations', function () {
             await db.migrate.rollback();
             await knexInstance.destroy();
           });
+
+          it('should not fail alter-table-ignore-case-sensitive', async () => {
+            await knex.migrate.latest({
+              directory:
+                'test/integration2/migrate/alter-table-ignore-case-sensitive',
+            });
+            expect(await knex.schema.hasColumn('Some_Table', 'id')).to.be.true;
+            expect(await knex.schema.hasColumn('Some_Table', 'other_id')).to.be
+              .true;
+            expect(await knex.schema.hasColumn('Some_Table', 'name')).to.be
+              .true;
+            // try different case
+            expect(await knex.schema.hasColumn('some_table', 'id')).to.be.true;
+            expect(await knex.schema.hasColumn('some_table', 'other_id')).to.be
+              .true;
+            expect(await knex.schema.hasColumn('some_table', 'name')).to.be
+              .true;
+
+            await knex.migrate.rollback({
+              directory:
+                'test/integration2/migrate/alter-table-ignore-case-sensitive',
+            });
+            expect(await knex.schema.hasTable('Some_Table')).to.be.false;
+            expect(await knex.schema.hasTable('some_table')).to.be.false;
+          });
         }
 
         it('should not fail drop-and-recreate-column operation when using async/await', () => {
diff --git a/test/integration2/query/select/unions.spec.js b/test/integration2/query/select/unions.spec.js
index 7226c44c..12eede35 100644
--- a/test/integration2/query/select/unions.spec.js
+++ b/test/integration2/query/select/unions.spec.js
@@ -25,12 +25,10 @@ const {
 } = require('../../util/knex-instance-provider');
 const logger = require('../../../integration/logger');
 
-describe('unions', function () {
+describe('All Union functions [.union(), .unionAll(), .intersect(), .except()]', function () {
   getAllDbs().forEach((db) => {
     describe(db, () => {
       let knex;
-      const unionCols = ['last_name', 'phone'];
-
       before(async () => {
         knex = logger(getKnexForDb(db));
 
@@ -59,128 +57,267 @@ describe('unions', function () {
         await knex.destroy();
       });
 
-      it('handles unions with a callback', async () => {
-        await knex('accounts')
-          .select(unionCols)
-          .where('id', '=', 1)
-          .union(function () {
-            this.select(unionCols).from('accounts').where('id', 2);
+      describe('.union()', () => {
+        before(async function () {
+          await knex.schema.createTable('union_test', function (t) {
+            t.integer('id');
+            t.integer('test_col_1');
+            t.integer('test_col_2');
+            t.integer('test_col_3');
           });
-      });
+        });
 
-      it('handles unions with an array of callbacks', async () => {
-        await knex('accounts')
-          .select(unionCols)
-          .where('id', '=', 1)
-          .union([
-            function () {
-              this.select(unionCols).from('accounts').where('id', 2);
+        beforeEach(async function () {
+          await knex('union_test').insert([
+            {
+              id: 1,
+              test_col_1: 1,
+              test_col_2: 2,
+              test_col_3: 1,
+            },
+            {
+              id: 2,
+              test_col_1: 2,
+              test_col_2: 3,
+              test_col_3: 1,
+            },
+            {
+              id: 3,
+              test_col_1: 2,
+              test_col_2: 3,
+              test_col_3: 2,
+            },
+            {
+              id: 4,
+              test_col_1: 1,
+              test_col_2: 2,
+              test_col_3: 2,
             },
-            function () {
-              this.select(unionCols).from('accounts').where('id', 3);
+            {
+              id: 5,
+              test_col_1: 1,
+              test_col_2: 2,
+              test_col_3: 1,
             },
           ]);
-      });
+        });
 
-      it('handles unions with a list of callbacks', async () => {
-        await knex('accounts')
-          .select(unionCols)
-          .where('id', '=', 1)
-          .union(
-            function () {
-              this.select(unionCols).from('accounts').where('id', 2);
-            },
-            function () {
-              this.select(unionCols).from('accounts').where('id', 3);
-            }
-          );
-      });
+        after(async function () {
+          await knex.schema.dropTable('union_test');
+        });
 
-      it('handles unions with an array of builders', async () => {
-        await knex('accounts')
-          .select(unionCols)
-          .where('id', '=', 1)
-          .union([
-            knex.select(unionCols).from('accounts').where('id', 2),
-            knex.select(unionCols).from('accounts').where('id', 3),
-          ]);
-      });
+        it('handles unions with a callback', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union(function () {
+              this.select('*').from('union_test').where('test_col_2', 2);
+            })
+            .then(function (result) {
+              expect(result.length).to.equal(3);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 4, 5]
+              );
+            });
+        });
 
-      it('handles unions with a list of builders', async () => {
-        await knex('accounts')
-          .select(unionCols)
-          .where('id', '=', 1)
-          .union(
-            knex.select(unionCols).from('accounts').where('id', 2),
-            knex.select(unionCols).from('accounts').where('id', 3)
-          );
-      });
+        it('handles unions with an array of callbacks', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union([
+              function () {
+                this.select('*').from('union_test').where('test_col_2', 2);
+              },
+              function () {
+                this.select('*').from('union_test').where('test_col_3', 1);
+              },
+            ])
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
+        });
+
+        it('handles unions with a list of callbacks', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union(
+              function () {
+                this.select('*').from('union_test').where('test_col_2', 2);
+              },
+              function () {
+                this.select('*').from('union_test').where('test_col_3', 1);
+              }
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
+        });
 
-      it('handles unions with a raw query', async () => {
-        await knex('union_raw_test')
-          .select('*')
-          .where('id', '=', 1)
-          .union(
-            knex.raw('select * from ?? where ?? = ?', [
-              'union_raw_test',
-              'id',
-              2,
+        it('handles unions with an array of builders', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union([
+              knex.select('*').from('union_test').where('test_col_2', 2),
+              knex.select('*').from('union_test').where('test_col_3', 1),
             ])
-          );
-      });
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
+        });
 
-      it('handles unions with an array raw queries', async () => {
-        await knex('union_raw_test')
-          .select('*')
-          .where('id', '=', 1)
-          .union([
-            knex.raw('select * from ?? where ?? = ?', [
-              'union_raw_test',
-              'id',
-              2,
-            ]),
-            knex.raw('select * from ?? where ?? = ?', [
-              'union_raw_test',
-              'id',
-              3,
-            ]),
-          ]);
-      });
+        it('handles unions with a list of builders', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union(
+              knex.select('*').from('union_test').where('test_col_2', 2),
+              knex.select('*').from('union_test').where('test_col_3', 1)
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
+        });
 
-      it('handles unions with a list of raw queries', async () => {
-        await knex('union_raw_test')
-          .select('*')
-          .where('id', '=', 1)
-          .union(
-            knex.raw('select * from ?? where ?? = ?', [
-              'union_raw_test',
-              'id',
-              2,
-            ]),
-            knex.raw('select * from ?? where ?? = ?', [
-              'union_raw_test',
-              'id',
-              3,
+        it('handles unions with a raw query', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 2)
+            .union(
+              knex.raw('select * from ?? where ?? = ?', [
+                'union_test',
+                'test_col_2',
+                3,
+              ])
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(2);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [2, 3]
+              );
+            });
+        });
+
+        it('handles unions with an array raw queries', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union([
+              knex.raw('select * from ?? where ?? = ?', [
+                'union_test',
+                'test_col_2',
+                2,
+              ]),
+              knex.raw('select * from ?? where ?? = ?', [
+                'union_test',
+                'test_col_3',
+                1,
+              ]),
             ])
-          );
-      });
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
+        });
 
-      it('handles nested unions with limit', async function () {
-        if (!isPostgreSQL(knex)) {
-          return this.skip();
-        }
-        const results = await knex('accounts')
-          .select('last_name')
-          .unionAll(function () {
-            this.select('last_name').from('accounts');
-          })
-          .first();
-        expect(results).to.eql({
-          last_name: 'User',
+        it('handles unions with a list of raw queries', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('union_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .union(
+              knex.raw('select * from ?? where ?? = ?', [
+                'union_test',
+                'test_col_2',
+                2,
+              ]),
+              knex.raw('select * from ?? where ?? = ?', [
+                'union_test',
+                'test_col_3',
+                1,
+              ])
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(4);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [1, 2, 4, 5]
+              );
+            });
         });
       });
 
-      describe('unions with wrapped queries', () => {
+      describe('.unionAll()', () => {
+        it('handles nested unions with limit', async function () {
+          if (!isPostgreSQL(knex)) {
+            return this.skip();
+          }
+          const results = await knex('accounts')
+            .select('last_name')
+            .unionAll(function () {
+              this.select('last_name').from('accounts');
+            })
+            .first();
+          expect(results).to.eql({
+            last_name: 'User',
+          });
+        });
+
         it('nested unions with group by in subqueries and limit and orderby', async function () {
           if (!isPostgreSQL(knex) && !isMysql(knex)) {
             return this.skip();
@@ -505,7 +642,7 @@ describe('unions', function () {
         });
       });
 
-      describe('intersects', function () {
+      describe('.intersect()', function () {
         before(async function () {
           await knex.schema.createTable('intersect_test', function (t) {
             t.integer('id');
@@ -796,6 +933,251 @@ describe('unions', function () {
             });
         });
       });
+
+      describe('.except()', function () {
+        before(async function () {
+          await knex.schema.createTable('except_test', function (t) {
+            t.integer('id');
+            t.integer('test_col_1');
+            t.integer('test_col_2');
+            t.integer('test_col_3');
+          });
+        });
+
+        beforeEach(async function () {
+          await knex('except_test').insert([
+            {
+              id: 1,
+              test_col_1: 1,
+              test_col_2: 2,
+              test_col_3: 1,
+            },
+            {
+              id: 2,
+              test_col_1: 2,
+              test_col_2: 3,
+              test_col_3: 1,
+            },
+            {
+              id: 3,
+              test_col_1: 2,
+              test_col_2: 3,
+              test_col_3: 2,
+            },
+            {
+              id: 4,
+              test_col_1: 2,
+              test_col_2: 2,
+              test_col_3: 2,
+            },
+            {
+              id: 5,
+              test_col_1: 1,
+              test_col_2: 3,
+              test_col_3: 3,
+            },
+          ]);
+        });
+
+        after(async function () {
+          await knex.schema.dropTable('except_test');
+        });
+
+        it('handles excepts with a callback', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except(function () {
+              this.select('*').from('except_test').where('test_col_2', 2);
+            })
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with an array of callbacks', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except([
+              function () {
+                this.select('*').from('except_test').where('test_col_2', 2);
+              },
+              function () {
+                this.select('*').from('except_test').where('test_col_3', 1);
+              },
+            ])
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with a list of callbacks', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except(
+              function () {
+                this.select('*').from('except_test').where('test_col_2', 2);
+              },
+              function () {
+                this.select('*').from('except_test').where('test_col_3', 1);
+              }
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with an array of builders', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except([
+              knex.select('*').from('except_test').where('test_col_2', 2),
+              knex.select('*').from('except_test').where('test_col_3', 1),
+            ])
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with a list of builders', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except(
+              knex.select('*').from('except_test').where('test_col_2', 2),
+              knex.select('*').from('except_test').where('test_col_3', 1)
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with a raw query', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 2)
+            .except(
+              knex.raw('select * from ?? where ?? = ?', [
+                'except_test',
+                'test_col_2',
+                3,
+              ])
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [4]
+              );
+            });
+        });
+
+        it('handles excepts with an array raw queries', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except([
+              knex.raw('select * from ?? where ?? = ?', [
+                'except_test',
+                'test_col_2',
+                2,
+              ]),
+              knex.raw('select * from ?? where ?? = ?', [
+                'except_test',
+                'test_col_3',
+                1,
+              ]),
+            ])
+            .then(function (result) {
+              expect(result.length).to.equal(1);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                [5]
+              );
+            });
+        });
+
+        it('handles excepts with a list of raw queries', async function () {
+          if (!isPostgreSQL(knex) && !isMysql(knex)) {
+            return this.skip();
+          }
+          await knex('except_test')
+            .select('*')
+            .where('test_col_1', '=', 1)
+            .except(
+              knex.raw('select * from ?? where ?? = ?', [
+                'except_test',
+                'test_col_2',
+                2,
+              ]),
+              knex.raw('select * from ?? where ?? = ?', [
+                'except_test',
+                'test_col_3',
+                3,
+              ])
+            )
+            .then(function (result) {
+              expect(result.length).to.equal(0);
+              assertNumberArray(
+                knex,
+                result.map((r) => r.id),
+                []
+              );
+            });
+        });
+      });
     });
   });
 });
diff --git a/test/integration2/transaction/set-readonly.spec.js b/test/integration2/transaction/set-readonly.spec.js
new file mode 100644
index 00000000..ccc11424
--- /dev/null
+++ b/test/integration2/transaction/set-readonly.spec.js
@@ -0,0 +1,58 @@
+const { getAllDbs, getKnexForDb } = require('../util/knex-instance-provider');
+const {
+  isSQLite,
+  isMssql,
+  isOracle,
+  isCockroachDB,
+} = require('../../util/db-helpers');
+const { expect } = require('chai');
+
+describe('Transaction', () => {
+  describe('setReadOnly', () => {
+    getAllDbs().forEach((db) => {
+      describe(db, () => {
+        let knex;
+        const tableName = 'key_value';
+
+        before(() => {
+          knex = getKnexForDb(db);
+        });
+
+        after(() => {
+          return knex.destroy();
+        });
+
+        beforeEach(async () => {
+          await knex.schema.createTable(tableName, (table) => {
+            table.integer('id').primary().notNull();
+            table.integer('value').notNull();
+          });
+        });
+
+        afterEach(async () => {
+          await knex.schema.dropTable(tableName);
+        });
+
+        it('Expect insert in read only transaction to be rejected', async () => {
+          if (
+            isSQLite(knex) ||
+            isOracle(knex) ||
+            isMssql(knex) ||
+            isCockroachDB(knex)
+          ) {
+            return;
+          }
+
+          await expect(
+            knex.transaction(
+              async (trx) => {
+                await trx(tableName).insert({ id: 1, value: 1 });
+              },
+              { readOnly: true }
+            )
+          ).to.be.rejected;
+        });
+      });
+    });
+  });
+});
diff --git a/test/unit/dialects/better-sqlite3.js b/test/unit/dialects/better-sqlite3.js
new file mode 100644
index 00000000..dbf3dfe2
--- /dev/null
+++ b/test/unit/dialects/better-sqlite3.js
@@ -0,0 +1,42 @@
+'use strict';
+const path = require('path');
+const expect = require('chai').expect;
+const knex = require('../../../knex');
+
+describe('better-sqlite3 unit tests', () => {
+  describe('nativeBinding', () => {
+    it('should work with a custom `nativeBinding`', async () => {
+      const knexInstance = knex({
+        client: 'better-sqlite3',
+        useNullAsDefault: true,
+        connection: {
+          filename: ':memory:',
+          options: {
+            nativeBinding: path.resolve(__dirname, '../../../node_modules/better-sqlite3/build/Release/better_sqlite3.node'),
+          },
+        },
+      });
+
+      const result = await knexInstance.select(knexInstance.raw('2 + 2 as answer')).first().catch(err => err);
+      expect(result.answer).to.equal(4);
+    });
+
+    it('should fail with a broken custom `nativeBinding`', async () => {
+      const knexInstance = knex({
+        client: 'better-sqlite3',
+        useNullAsDefault: true,
+        connection: {
+          filename: ':memory:',
+          options: {
+            nativeBinding: 'not/a/real/path',
+          },
+        },
+      });
+
+      const result = await knexInstance.select(knexInstance.raw('2 + 2 as answer')).first().catch(err => err);
+      console.dir(result);
+      expect(result).to.be.an('error');
+      expect(result.code).to.equal('MODULE_NOT_FOUND');
+    });
+  });
+});
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index 712c00ae..5fc31e81 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -2740,6 +2740,266 @@ describe('QueryBuilder', () => {
     });
   });
 
+  it('excepts', () => {
+    const chain = qb()
+      .select('*')
+      .from('users')
+      .where('id', '=', 1)
+      .except(function () {
+        this.select('*').from('users').where('id', '=', 2);
+      });
+
+    testsql(chain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ?',
+        bindings: [1, 2],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2],
+      },
+      oracledb: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2],
+      },
+      sqlite3: {
+        sql: 'select * from `users` where `id` = ? except select * from `users` where `id` = ?',
+        bindings: [1, 2],
+      },
+    });
+
+    const multipleArgumentsChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except(
+        function () {
+          this.select('*').from('users').where({ id: 2 });
+        },
+        function () {
+          this.select('*').from('users').where({ id: 3 });
+        }
+      );
+    testsql(multipleArgumentsChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ? except select * from [users] where [id] = ?',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      oracledb: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      sqlite3: {
+        sql: 'select * from `users` where `id` = ? except select * from `users` where `id` = ? except select * from `users` where `id` = ?',
+        bindings: [1, 2, 3],
+      },
+    });
+
+    const arrayChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except([
+        function () {
+          this.select('*').from('users').where({ id: 2 });
+        },
+        function () {
+          this.select('*').from('users').where({ id: 3 });
+        },
+      ]);
+    testsql(arrayChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ? except select * from [users] where [id] = ?',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      oracledb: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      sqlite3: {
+        sql: 'select * from `users` where `id` = ? except select * from `users` where `id` = ? except select * from `users` where `id` = ?',
+        bindings: [1, 2, 3],
+      },
+    });
+  });
+
+  it('wraps excepts', () => {
+    const wrappedChain = qb()
+      .select('*')
+      .from('users')
+      .where('id', 'in', function () {
+        this.table('users')
+          .max('id')
+          .except(function () {
+            this.table('users').min('id');
+          }, true);
+      });
+    testsql(wrappedChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] in (select max([id]) from [users] except (select min([id]) from [users]))',
+        bindings: [],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") except (select min("id") from "users"))',
+        bindings: [],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" in ((select max("id") from "users") except (select min("id") from "users"))',
+        bindings: [],
+      },
+    });
+
+    // worthwhile since we're playing games with the 'wrap' specification with arguments
+    const multipleArgumentsWrappedChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except(
+        function () {
+          this.select('*').from('users').where({ id: 2 });
+        },
+        function () {
+          this.select('*').from('users').where({ id: 3 });
+        },
+        true
+      );
+    testsql(multipleArgumentsWrappedChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except (select * from [users] where [id] = ?) except (select * from [users] where [id] = ?)',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: '(select * from "users" where "id" = ?) except (select * from "users" where "id" = ?) except (select * from "users" where "id" = ?)',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: '(select * from "users" where "id" = ?) except (select * from "users" where "id" = ?) except (select * from "users" where "id" = ?)',
+        bindings: [1, 2, 3],
+      },
+    });
+
+    const arrayWrappedChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except(
+        [
+          function () {
+            this.select('*').from('users').where({ id: 2 });
+          },
+          function () {
+            this.select('*').from('users').where({ id: 3 });
+          },
+        ],
+        true
+      );
+    testsql(arrayWrappedChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except (select * from [users] where [id] = ?) except (select * from [users] where [id] = ?)',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: '(select * from "users" where "id" = ?) except (select * from "users" where "id" = ?) except (select * from "users" where "id" = ?)',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: '(select * from "users" where "id" = ?) except (select * from "users" where "id" = ?) except (select * from "users" where "id" = ?)',
+        bindings: [1, 2, 3],
+      },
+    });
+  });
+
+  it('multiple excepts', () => {
+    const chain = qb()
+      .select('*')
+      .from('users')
+      .where('id', '=', 1)
+      .except(qb().select('*').from('users').where('id', '=', 2))
+      .except(function () {
+        this.select('*').from('users').where('id', '=', 3);
+      });
+    testsql(chain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ? except select * from [users] where [id] = ?',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from "users" where "id" = ?',
+        bindings: [1, 2, 3],
+      },
+    });
+
+    const arrayChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except([
+        qb().select('*').from('users').where({ id: 2 }),
+        raw('select * from users where id = ?', [3]),
+      ]);
+    testsql(arrayChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+    });
+
+    const multipleArgumentsChain = qb()
+      .select('*')
+      .from('users')
+      .where({ id: 1 })
+      .except(
+        qb().select('*').from('users').where({ id: 2 }),
+        raw('select * from users where id = ?', [3])
+      );
+    testsql(multipleArgumentsChain, {
+      mssql: {
+        sql: 'select * from [users] where [id] = ? except select * from [users] where [id] = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+      pg: {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+      'pg-redshift': {
+        sql: 'select * from "users" where "id" = ? except select * from "users" where "id" = ? except select * from users where id = ?',
+        bindings: [1, 2, 3],
+      },
+    });
+  });
+
   it('sub select where ins', () => {
     testsql(
       qb()
diff --git a/test/unit/schema-builder/oracledb.js b/test/unit/schema-builder/oracledb.js
index 4b43064d..66795948 100644
--- a/test/unit/schema-builder/oracledb.js
+++ b/test/unit/schema-builder/oracledb.js
@@ -1096,6 +1096,30 @@ describe('OracleDb SchemaBuilder', function () {
     }).to.throw(TypeError);
   });
 
+  it('allows adding default json objects when the column is json', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('user', function (t) {
+        t.json('preferences').defaultTo({}).notNullable();
+      })
+      .toSQL();
+    expect(tableSql[0].sql).to.equal(
+      'alter table "user" add "preferences" varchar2(4000) default \'{}\' not null check ("preferences" is json)'
+    );
+  });
+
+  it('allows adding default jsonb objects when the column is json', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('user', function (t) {
+        t.jsonb('preferences').defaultTo({}).notNullable();
+      })
+      .toSQL();
+    expect(tableSql[0].sql).to.equal(
+      'alter table "user" add "preferences" varchar2(4000) default \'{}\' not null check ("preferences" is json)'
+    );
+  });
+
   it('is possible to set raw statements in defaultTo, #146', function () {
     tableSql = client
       .schemaBuilder()