query-generator.test.js

'use strict';

const chai = require('chai'),
  expect = chai.expect,
  Op = require('sequelize/lib/operators'),
  QueryGenerator = require('sequelize/lib/dialects/postgres/query-generator'),
  Support = require('../../support'),
  dialect = Support.getTestDialect(),
  DataTypes = require('sequelize/lib/data-types'),
  moment = require('moment'),
  current = Support.sequelize,
  _ = require('lodash');

const customSequelize = Support.createSequelizeInstance({
  schema: 'custom'
});

if (dialect.startsWith('postgres')) {
  describe('[POSTGRES Specific] QueryGenerator', () => {
    const suites = {
      createDatabaseQuery: [
        {
          arguments: ['myDatabase'],
          expectation: 'CREATE DATABASE "myDatabase";'
        },
        {
          arguments: ['myDatabase', { encoding: 'UTF8' }],
          expectation: 'CREATE DATABASE "myDatabase" ENCODING = \'UTF8\';'
        },
        {
          arguments: ['myDatabase', { collate: 'en_US.UTF-8' }],
          expectation: 'CREATE DATABASE "myDatabase" LC_COLLATE = \'en_US.UTF-8\';'
        },
        {
          arguments: ['myDatabase', { encoding: 'UTF8' }],
          expectation: 'CREATE DATABASE "myDatabase" ENCODING = \'UTF8\';'
        },
        {
          arguments: ['myDatabase', { ctype: 'zh_TW.UTF-8' }],
          expectation: 'CREATE DATABASE "myDatabase" LC_CTYPE = \'zh_TW.UTF-8\';'
        },
        {
          arguments: ['myDatabase', { template: 'template0' }],
          expectation: 'CREATE DATABASE "myDatabase" TEMPLATE = \'template0\';'
        },
        {
          arguments: ['myDatabase', { encoding: 'UTF8', collate: 'en_US.UTF-8', ctype: 'zh_TW.UTF-8', template: 'template0' }],
          expectation: 'CREATE DATABASE "myDatabase" ENCODING = \'UTF8\' LC_COLLATE = \'en_US.UTF-8\' LC_CTYPE = \'zh_TW.UTF-8\' TEMPLATE = \'template0\';'
        }
      ],

      dropDatabaseQuery: [
        {
          arguments: ['myDatabase'],
          expectation: 'DROP DATABASE IF EXISTS "myDatabase";'
        }
      ],

      arithmeticQuery: [
        {
          title: 'Should use the plus operator',
          arguments: ['+', 'myTable', {}, { foo: 'bar' }, {}, {}],
          expectation: 'UPDATE "myTable" SET "foo"="foo"+ \'bar\' RETURNING *'
        },
        {
          title: 'Should use the plus operator with where clause',
          arguments: ['+', 'myTable', { bar: 'biz' }, { foo: 'bar' }, {}, {}],
          expectation: 'UPDATE "myTable" SET "foo"="foo"+ \'bar\' WHERE "bar" = \'biz\' RETURNING *'
        },
        {
          title: 'Should use the plus operator without returning clause',
          arguments: ['+', 'myTable', {}, { foo: 'bar' }, {}, { returning: false }],
          expectation: 'UPDATE "myTable" SET "foo"="foo"+ \'bar\''
        },
        {
          title: 'Should use the minus operator',
          arguments: ['-', 'myTable', {}, { foo: 'bar' }, {}, {}],
          expectation: 'UPDATE "myTable" SET "foo"="foo"- \'bar\' RETURNING *'
        },
        {
          title: 'Should use the minus operator with negative value',
          arguments: ['-', 'myTable', {}, { foo: -1 }, {}, {}],
          expectation: 'UPDATE "myTable" SET "foo"="foo"- -1 RETURNING *'
        },
        {
          title: 'Should use the minus operator with where clause',
          arguments: ['-', 'myTable', { bar: 'biz' }, { foo: 'bar' }, {}, {}],
          expectation: 'UPDATE "myTable" SET "foo"="foo"- \'bar\' WHERE "bar" = \'biz\' RETURNING *'
        },
        {
          title: 'Should use the minus operator without returning clause',
          arguments: ['-', 'myTable', {}, { foo: 'bar' }, {}, { returning: false }],
          expectation: 'UPDATE "myTable" SET "foo"="foo"- \'bar\''
        }
      ],

      attributesToSQL: [
        {
          arguments: [{ id: 'INTEGER' }],
          expectation: { id: 'INTEGER' }
        },
        {
          arguments: [{ id: 'INTEGER', foo: 'VARCHAR(255)' }],
          expectation: { id: 'INTEGER', foo: 'VARCHAR(255)' }
        },
        {
          arguments: [{ id: { type: 'INTEGER' } }],
          expectation: { id: 'INTEGER' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', allowNull: false } }],
          expectation: { id: 'INTEGER NOT NULL' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', allowNull: true } }],
          expectation: { id: 'INTEGER' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', primaryKey: true, autoIncrement: true } }],
          expectation: { id: 'INTEGER SERIAL PRIMARY KEY' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', primaryKey: true, autoIncrement: true, autoIncrementIdentity: true } }],
          expectation: { id: 'INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', defaultValue: 0 } }],
          expectation: { id: 'INTEGER DEFAULT 0' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', unique: true } }],
          expectation: { id: 'INTEGER UNIQUE' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', unique: true, comment: 'This is my comment' } }],
          expectation: { id: 'INTEGER UNIQUE COMMENT This is my comment' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', unique: true, comment: 'This is my comment' } }, { context: 'addColumn', key: 'column', table: { schema: 'foo', tableName: 'bar' } }],
          expectation: { id: 'INTEGER UNIQUE; COMMENT ON COLUMN "foo"."bar"."column" IS \'This is my comment\'' }
        },
        // New references style
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' } } }],
          expectation: { id: 'INTEGER REFERENCES "Bar" ("id")' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar', key: 'pk' } } }],
          expectation: { id: 'INTEGER REFERENCES "Bar" ("pk")' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' }, onDelete: 'CASCADE' } }],
          expectation: { id: 'INTEGER REFERENCES "Bar" ("id") ON DELETE CASCADE' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' }, onUpdate: 'RESTRICT' } }],
          expectation: { id: 'INTEGER REFERENCES "Bar" ("id") ON UPDATE RESTRICT' }
        },
        {
          arguments: [{ id: { type: 'INTEGER', allowNull: false, defaultValue: 1, references: { model: 'Bar' }, onDelete: 'CASCADE', onUpdate: 'RESTRICT' } }],
          expectation: { id: 'INTEGER NOT NULL DEFAULT 1 REFERENCES "Bar" ("id") ON DELETE CASCADE ON UPDATE RESTRICT' }
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' } } }],
          expectation: { id: 'INTEGER REFERENCES Bar (id)' },
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar', key: 'pk' } } }],
          expectation: { id: 'INTEGER REFERENCES Bar (pk)' },
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' }, onDelete: 'CASCADE' } }],
          expectation: { id: 'INTEGER REFERENCES Bar (id) ON DELETE CASCADE' },
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ id: { type: 'INTEGER', references: { model: 'Bar' }, onUpdate: 'RESTRICT' } }],
          expectation: { id: 'INTEGER REFERENCES Bar (id) ON UPDATE RESTRICT' },
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ id: { type: 'INTEGER', allowNull: false, defaultValue: 1, references: { model: 'Bar' }, onDelete: 'CASCADE', onUpdate: 'RESTRICT' } }],
          expectation: { id: 'INTEGER NOT NULL DEFAULT 1 REFERENCES Bar (id) ON DELETE CASCADE ON UPDATE RESTRICT' },
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      createTableQuery: [
        {
          arguments: ['myTable', { int: 'INTEGER', bigint: 'BIGINT', smallint: 'SMALLINT' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("int" INTEGER, "bigint" BIGINT, "smallint" SMALLINT);'
        },
        {
          arguments: ['myTable', { serial: 'INTEGER SERIAL', bigserial: 'BIGINT SERIAL', smallserial: 'SMALLINT SERIAL' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("serial"  SERIAL, "bigserial"  BIGSERIAL, "smallserial"  SMALLSERIAL);'
        },
        {
          arguments: ['myTable', { int: 'INTEGER COMMENT Test', foo: 'INTEGER COMMENT Foo Comment' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("int" INTEGER , "foo" INTEGER ); COMMENT ON COLUMN "myTable"."int" IS \'Test\'; COMMENT ON COLUMN "myTable"."foo" IS \'Foo Comment\';'
        },
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("title" VARCHAR(255), "name" VARCHAR(255));'
        },
        {
          arguments: ['myTable', { data: current.normalizeDataType(DataTypes.BLOB).toSql() }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("data" BYTEA);'
        },
        {
          arguments: ['myTable', { data: current.normalizeDataType(DataTypes.BLOB('long')).toSql() }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("data" BYTEA);'
        },
        {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { title: 'VARCHAR(255)', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "mySchema"."myTable" ("title" VARCHAR(255), "name" VARCHAR(255));'
        },
        {
          arguments: ['myTable', { title: 'ENUM("A", "B", "C")', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("title" "public"."enum_myTable_title", "name" VARCHAR(255));'
        },
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)', id: 'INTEGER PRIMARY KEY' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("title" VARCHAR(255), "name" VARCHAR(255), "id" INTEGER , PRIMARY KEY ("id"));'
        },
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)', otherId: 'INTEGER REFERENCES "otherTable" ("id") ON DELETE CASCADE ON UPDATE NO ACTION' }],
          expectation: 'CREATE TABLE IF NOT EXISTS "myTable" ("title" VARCHAR(255), "name" VARCHAR(255), "otherId" INTEGER REFERENCES "otherTable" ("id") ON DELETE CASCADE ON UPDATE NO ACTION);'
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS myTable (title VARCHAR(255), name VARCHAR(255));',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, { title: 'VARCHAR(255)', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS mySchema.myTable (title VARCHAR(255), name VARCHAR(255));',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: ['myTable', { title: 'ENUM("A", "B", "C")', name: 'VARCHAR(255)' }],
          expectation: 'CREATE TABLE IF NOT EXISTS myTable (title public."enum_myTable_title", name VARCHAR(255));',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)', id: 'INTEGER PRIMARY KEY' }],
          expectation: 'CREATE TABLE IF NOT EXISTS myTable (title VARCHAR(255), name VARCHAR(255), id INTEGER , PRIMARY KEY (id));',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: ['myTable', { title: 'VARCHAR(255)', name: 'VARCHAR(255)', otherId: 'INTEGER REFERENCES otherTable (id) ON DELETE CASCADE ON UPDATE NO ACTION' }],
          expectation: 'CREATE TABLE IF NOT EXISTS myTable (title VARCHAR(255), name VARCHAR(255), otherId INTEGER REFERENCES otherTable (id) ON DELETE CASCADE ON UPDATE NO ACTION);',
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      dropTableQuery: [
        {
          arguments: ['myTable'],
          expectation: 'DROP TABLE IF EXISTS "myTable";'
        },
        {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }],
          expectation: 'DROP TABLE IF EXISTS "mySchema"."myTable";'
        },
        {
          arguments: ['myTable', { cascade: true }],
          expectation: 'DROP TABLE IF EXISTS "myTable" CASCADE;'
        },
        {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { cascade: true }],
          expectation: 'DROP TABLE IF EXISTS "mySchema"."myTable" CASCADE;'
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable'],
          expectation: 'DROP TABLE IF EXISTS myTable;',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }],
          expectation: 'DROP TABLE IF EXISTS mySchema.myTable;',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: ['myTable', { cascade: true }],
          expectation: 'DROP TABLE IF EXISTS myTable CASCADE;',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { cascade: true }],
          expectation: 'DROP TABLE IF EXISTS mySchema.myTable CASCADE;',
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      changeColumnQuery: [
        {
          arguments: ['myTable', {
            col_1: "ENUM('value 1', 'value 2') NOT NULL",
            col_2: "ENUM('value 3', 'value 4') NOT NULL"
          }],
          expectation: 'ALTER TABLE "myTable" ALTER COLUMN "col_1" SET NOT NULL;ALTER TABLE "myTable" ALTER COLUMN "col_1" DROP DEFAULT;DO \'BEGIN CREATE TYPE "public"."enum_myTable_col_1" AS ENUM(\'\'value 1\'\', \'\'value 2\'\'); EXCEPTION WHEN duplicate_object THEN null; END\';ALTER TABLE "myTable" ALTER COLUMN "col_1" TYPE "public"."enum_myTable_col_1" USING ("col_1"::"public"."enum_myTable_col_1");ALTER TABLE "myTable" ALTER COLUMN "col_2" SET NOT NULL;ALTER TABLE "myTable" ALTER COLUMN "col_2" DROP DEFAULT;DO \'BEGIN CREATE TYPE "public"."enum_myTable_col_2" AS ENUM(\'\'value 3\'\', \'\'value 4\'\'); EXCEPTION WHEN duplicate_object THEN null; END\';ALTER TABLE "myTable" ALTER COLUMN "col_2" TYPE "public"."enum_myTable_col_2" USING ("col_2"::"public"."enum_myTable_col_2");'
        }
      ],

      selectQuery: [
        {
          arguments: ['myTable'],
          expectation: 'SELECT * FROM "myTable";'
        }, {
          arguments: ['myTable', { attributes: ['id', 'name'] }],
          expectation: 'SELECT "id", "name" FROM "myTable";'
        }, {
          arguments: ['myTable', { where: { id: 2 } }],
          expectation: 'SELECT * FROM "myTable" WHERE "myTable"."id" = 2;'
        }, {
          arguments: ['myTable', { where: { name: 'foo' } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"name\" = 'foo';"
        }, {
          arguments: ['myTable', { where: { name: "foo';DROP TABLE myTable;" } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"name\" = 'foo'';DROP TABLE myTable;';"
        }, {
          arguments: ['myTable', { where: 2 }],
          expectation: 'SELECT * FROM "myTable" WHERE "myTable"."id" = 2;'
        }, {
          arguments: ['foo', { attributes: [['count(*)', 'count']] }],
          expectation: 'SELECT count(*) AS "count" FROM "foo";',
          context: { options: { attributeBehavior: 'unsafe-legacy' } }
        }, {
          arguments: ['myTable', { order: ['id'] }],
          expectation: 'SELECT * FROM "myTable" ORDER BY "id";',
          context: QueryGenerator
        }, {
          arguments: ['myTable', { order: ['id', 'DESC'] }],
          expectation: 'SELECT * FROM "myTable" ORDER BY "id", "DESC";',
          context: QueryGenerator
        }, {
          arguments: ['myTable', { order: ['myTable.id'] }],
          expectation: 'SELECT * FROM "myTable" ORDER BY "myTable"."id";',
          context: QueryGenerator
        }, {
          arguments: ['myTable', { order: [['myTable.id', 'DESC']] }],
          expectation: 'SELECT * FROM "myTable" ORDER BY "myTable"."id" DESC;',
          context: QueryGenerator
        }, {
          arguments: ['myTable', { order: [['id', 'DESC']] }, function(sequelize) {return sequelize.define('myTable', {});}],
          expectation: 'SELECT * FROM "myTable" AS "myTable" ORDER BY "myTable"."id" DESC;',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          arguments: ['myTable', { order: [['id', 'DESC'], ['name']] }, function(sequelize) {return sequelize.define('myTable', {});}],
          expectation: 'SELECT * FROM "myTable" AS "myTable" ORDER BY "myTable"."id" DESC, "myTable"."name";',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'uses limit 0',
          arguments: ['myTable', { limit: 0 }],
          expectation: 'SELECT * FROM "myTable" LIMIT 0;',
          context: QueryGenerator
        }, {
          title: 'uses offset 0',
          arguments: ['myTable', { offset: 0 }],
          expectation: 'SELECT * FROM "myTable" OFFSET 0;',
          context: QueryGenerator
        }, {
          title: 'sequelize.where with .fn as attribute and default comparator',
          arguments: ['myTable', function(sequelize) {
            return {
              where: sequelize.and(
                sequelize.where(sequelize.fn('LOWER', sequelize.col('user.name')), 'jan'),
                { type: 1 }
              )
            };
          }],
          expectation: 'SELECT * FROM "myTable" WHERE (LOWER("user"."name") = \'jan\' AND "myTable"."type" = 1);',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'sequelize.where with .fn as attribute and LIKE comparator',
          arguments: ['myTable', function(sequelize) {
            return {
              where: sequelize.and(
                sequelize.where(sequelize.fn('LOWER', sequelize.col('user.name')), 'LIKE', '%t%'),
                { type: 1 }
              )
            };
          }],
          expectation: 'SELECT * FROM "myTable" WHERE (LOWER("user"."name") LIKE \'%t%\' AND "myTable"."type" = 1);',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'functions can take functions as arguments',
          arguments: ['myTable', function(sequelize) {
            return {
              order: [[sequelize.fn('f1', sequelize.fn('f2', sequelize.col('id'))), 'DESC']]
            };
          }],
          expectation: 'SELECT * FROM "myTable" ORDER BY f1(f2("id")) DESC;',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'functions can take all types as arguments',
          arguments: ['myTable', function(sequelize) {
            return {
              order: [
                [sequelize.fn('f1', sequelize.col('myTable.id')), 'DESC'],
                [sequelize.fn('f2', 12, 'lalala', new Date(Date.UTC(2011, 2, 27, 10, 1, 55))), 'ASC']
              ]
            };
          }],
          expectation: 'SELECT * FROM "myTable" ORDER BY f1("myTable"."id") DESC, f2(12, \'lalala\', \'2011-03-27 10:01:55.000 +00:00\') ASC;',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'Combination of sequelize.fn, sequelize.col and { Op.in: ... }',
          arguments: ['myTable', function(sequelize) {
            return {
              where: sequelize.and(
                { archived: null },
                sequelize.where(sequelize.fn('COALESCE', sequelize.col('place_type_codename'), sequelize.col('announcement_type_codename')), { [Op.in]: ['Lost', 'Found'] })
              )
            };
          }],
          expectation: 'SELECT * FROM "myTable" WHERE ("myTable"."archived" IS NULL AND COALESCE("place_type_codename", "announcement_type_codename") IN (\'Lost\', \'Found\'));',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          title: 'single string argument should be quoted',
          arguments: ['myTable', { group: 'name' }],
          expectation: 'SELECT * FROM "myTable" GROUP BY "name";'
        }, {
          arguments: ['myTable', { group: ['name'] }],
          expectation: 'SELECT * FROM "myTable" GROUP BY "name";'
        }, {
          title: 'functions work for group by',
          arguments: ['myTable', function(sequelize) {
            return {
              group: [sequelize.fn('YEAR', sequelize.col('createdAt'))]
            };
          }],
          expectation: 'SELECT * FROM "myTable" GROUP BY YEAR("createdAt");',
          needsSequelize: true
        }, {
          title: 'It is possible to mix sequelize.fn and string arguments to group by',
          arguments: ['myTable', function(sequelize) {
            return {
              group: [sequelize.fn('YEAR', sequelize.col('createdAt')), 'title']
            };
          }],
          expectation: 'SELECT * FROM "myTable" GROUP BY YEAR("createdAt"), "title";',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          arguments: ['myTable', { group: ['name', 'title'] }],
          expectation: 'SELECT * FROM "myTable" GROUP BY "name", "title";'
        }, {
          title: 'HAVING clause works with where-like hash',
          arguments: ['myTable', function(sequelize) {
            return {
              attributes: ['*', [sequelize.fn('YEAR', sequelize.col('createdAt')), 'creationYear']],
              group: ['creationYear', 'title'],
              having: { creationYear: { [Op.gt]: 2002 } }
            };
          }],
          expectation: 'SELECT *, YEAR("createdAt") AS "creationYear" FROM "myTable" GROUP BY "creationYear", "title" HAVING "creationYear" > 2002;',
          context: QueryGenerator,
          needsSequelize: true
        }, {
          arguments: ['myTable', { limit: 10 }],
          expectation: 'SELECT * FROM "myTable" LIMIT 10;'
        }, {
          arguments: ['myTable', { limit: 10, offset: 2 }],
          expectation: 'SELECT * FROM "myTable" LIMIT 10 OFFSET 2;'
        }, {
          title: 'uses offset even if no limit was passed',
          arguments: ['myTable', { offset: 2 }],
          expectation: 'SELECT * FROM "myTable" OFFSET 2;'
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }],
          expectation: 'SELECT * FROM "mySchema"."myTable";'
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { where: { name: "foo';DROP TABLE mySchema.myTable;" } }],
          expectation: "SELECT * FROM \"mySchema\".\"myTable\" WHERE \"mySchema\".\"myTable\".\"name\" = 'foo'';DROP TABLE mySchema.myTable;';"
        }, {
          title: 'buffer as where argument',
          arguments: ['myTable', { where: { field: Buffer.from('Sequelize') } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"field\" = E'\\\\x53657175656c697a65';",
          context: QueryGenerator
        }, {
          title: 'string in array should escape \' as \'\'',
          arguments: ['myTable', { where: { aliases: { [Op.contains]: ['Queen\'s'] } } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"aliases\" @> ARRAY['Queen''s'];"
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable'],
          expectation: 'SELECT * FROM myTable;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { attributes: ['id', 'name'] }],
          expectation: 'SELECT id, name FROM myTable;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { where: { id: 2 } }],
          expectation: 'SELECT * FROM myTable WHERE myTable.id = 2;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { where: { name: 'foo' } }],
          expectation: "SELECT * FROM myTable WHERE myTable.name = 'foo';",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { where: { name: "foo';DROP TABLE myTable;" } }],
          expectation: "SELECT * FROM myTable WHERE myTable.name = 'foo'';DROP TABLE myTable;';",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { where: 2 }],
          expectation: 'SELECT * FROM myTable WHERE myTable.id = 2;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['foo', { attributes: [['count(*)', 'count']] }],
          expectation: 'SELECT count(*) AS count FROM foo;',
          context: { options: { quoteIdentifiers: false, attributeBehavior: 'unsafe-legacy' } }
        }, {
          arguments: ['myTable', { order: ['id DESC'] }],
          expectation: 'SELECT * FROM myTable ORDER BY id DESC;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { group: 'name' }],
          expectation: 'SELECT * FROM myTable GROUP BY name;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { group: ['name'] }],
          expectation: 'SELECT * FROM myTable GROUP BY name;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { group: ['name', 'title'] }],
          expectation: 'SELECT * FROM myTable GROUP BY name, title;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { limit: 10 }],
          expectation: 'SELECT * FROM myTable LIMIT 10;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { limit: 10, offset: 2 }],
          expectation: 'SELECT * FROM myTable LIMIT 10 OFFSET 2;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'uses offset even if no limit was passed',
          arguments: ['myTable', { offset: 2 }],
          expectation: 'SELECT * FROM myTable OFFSET 2;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }],
          expectation: 'SELECT * FROM mySchema.myTable;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { where: { name: "foo';DROP TABLE mySchema.myTable;" } }],
          expectation: "SELECT * FROM mySchema.myTable WHERE mySchema.myTable.name = 'foo'';DROP TABLE mySchema.myTable;';",
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'use != if Op.ne !== null',
          arguments: ['myTable', { where: { field: { [Op.ne]: 0 } } }],
          expectation: 'SELECT * FROM myTable WHERE myTable.field != 0;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'use IS NOT if Op.ne === null',
          arguments: ['myTable', { where: { field: { [Op.ne]: null } } }],
          expectation: 'SELECT * FROM myTable WHERE myTable.field IS NOT NULL;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'use IS NOT if Op.not === BOOLEAN',
          arguments: ['myTable', { where: { field: { [Op.not]: true } } }],
          expectation: 'SELECT * FROM myTable WHERE myTable.field IS NOT true;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'use != if Op.not !== BOOLEAN',
          arguments: ['myTable', { where: { field: { [Op.not]: 3 } } }],
          expectation: 'SELECT * FROM myTable WHERE myTable.field != 3;',
          context: { options: { quoteIdentifiers: false } }
        }, {
          title: 'Regular Expression in where clause',
          arguments: ['myTable', { where: { field: { [Op.regexp]: '^[h|a|t]' } } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"field\" ~ '^[h|a|t]';",
          context: QueryGenerator
        }, {
          title: 'Regular Expression negation in where clause',
          arguments: ['myTable', { where: { field: { [Op.notRegexp]: '^[h|a|t]' } } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"field\" !~ '^[h|a|t]';",
          context: QueryGenerator
        }, {
          title: 'Case-insensitive Regular Expression in where clause',
          arguments: ['myTable', { where: { field: { [Op.iRegexp]: '^[h|a|t]' } } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"field\" ~* '^[h|a|t]';",
          context: QueryGenerator
        }, {
          title: 'Case-insensitive Regular Expression negation in where clause',
          arguments: ['myTable', { where: { field: { [Op.notIRegexp]: '^[h|a|t]' } } }],
          expectation: "SELECT * FROM \"myTable\" WHERE \"myTable\".\"field\" !~* '^[h|a|t]';",
          context: QueryGenerator
        }
      ],

      insertQuery: [
        {
          arguments: ['myTable', {}],
          expectation: {
            query: 'INSERT INTO "myTable" DEFAULT VALUES;',
            bind: []
          }
        },
        {
          arguments: ['myTable', { name: 'foo' }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1);',
            bind: ['foo']
          }
        }, {
          arguments: ['myTable', { name: 'foo' }, {}, { ignoreDuplicates: true }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1) ON CONFLICT DO NOTHING;',
            bind: ['foo']
          }
        }, {
          arguments: ['myTable', { name: 'foo' }, {}, { returning: true }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1) RETURNING *;',
            bind: ['foo']
          }
        }, {
          arguments: ['myTable', { name: 'foo' }, {}, { ignoreDuplicates: true, returning: true }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1) ON CONFLICT DO NOTHING RETURNING *;',
            bind: ['foo']
          }
        }, {
          arguments: ['myTable', { name: "foo';DROP TABLE myTable;" }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1);',
            bind: ["foo';DROP TABLE myTable;"]
          }
        }, {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name","birthday") VALUES ($1,$2);',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate()]
          }
        }, {
          arguments: ['myTable', { data: Buffer.from('Sequelize') }],
          expectation: {
            query: 'INSERT INTO "myTable" ("data") VALUES ($1);',
            bind: [Buffer.from('Sequelize')]
          }
        }, {
          arguments: ['myTable', { name: 'foo', numbers: [1, 2, 3] }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name","numbers") VALUES ($1,$2);',
            bind: ['foo', [1, 2, 3]]
          }
        }, {
          arguments: ['myTable', { name: 'foo', foo: 1 }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name","foo") VALUES ($1,$2);',
            bind: ['foo', 1]
          }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name","nullValue") VALUES ($1,$2);',
            bind: ['foo', null]
          }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name","nullValue") VALUES ($1,$2);',
            bind: ['foo', null]
          },
          context: { options: { omitNull: false } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { omitNull: true } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: undefined }],
          expectation: {
            query: 'INSERT INTO "myTable" ("name") VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { omitNull: true } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: 'foo' }],
          expectation: {
            query: 'INSERT INTO "mySchema"."myTable" ("name") VALUES ($1);',
            bind: ['foo']
          }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: JSON.stringify({ info: 'Look ma a " quote' }) }],
          expectation: {
            query: 'INSERT INTO "mySchema"."myTable" ("name") VALUES ($1);',
            bind: ['{"info":"Look ma a \\" quote"}']
          }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: "foo';DROP TABLE mySchema.myTable;" }],
          expectation: {
            query: 'INSERT INTO "mySchema"."myTable" ("name") VALUES ($1);',
            bind: ["foo';DROP TABLE mySchema.myTable;"]
          }
        }, {
          arguments: ['myTable', function(sequelize) {
            return {
              foo: sequelize.fn('NOW')
            };
          }],
          expectation: {
            query: 'INSERT INTO "myTable" ("foo") VALUES (NOW());',
            bind: []
          },
          needsSequelize: true
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable', { name: 'foo' }],
          expectation: {
            query: 'INSERT INTO myTable (name) VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: "foo';DROP TABLE myTable;" }],
          expectation: {
            query: 'INSERT INTO myTable (name) VALUES ($1);',
            bind: ["foo';DROP TABLE myTable;"]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }],
          expectation: {
            query: 'INSERT INTO myTable (name,birthday) VALUES ($1,$2);',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate()]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', numbers: [1, 2, 3] }],
          expectation: {
            query: 'INSERT INTO myTable (name,numbers) VALUES ($1,$2);',
            bind: ['foo', [1, 2, 3]]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', foo: 1 }],
          expectation: {
            query: 'INSERT INTO myTable (name,foo) VALUES ($1,$2);',
            bind: ['foo', 1]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO myTable (name,nullValue) VALUES ($1,$2);',
            bind: ['foo', null]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO myTable (name,nullValue) VALUES ($1,$2);',
            bind: ['foo', null]
          },
          context: { options: { omitNull: false, quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: null }],
          expectation: {
            query: 'INSERT INTO myTable (name) VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { omitNull: true, quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', nullValue: undefined }],
          expectation: {
            query: 'INSERT INTO myTable (name) VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { omitNull: true, quoteIdentifiers: false } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: 'foo' }],
          expectation: {
            query: 'INSERT INTO mySchema.myTable (name) VALUES ($1);',
            bind: ['foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: JSON.stringify({ info: 'Look ma a " quote' }) }],
          expectation: {
            query: 'INSERT INTO mySchema.myTable (name) VALUES ($1);',
            bind: ['{"info":"Look ma a \\" quote"}']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: "foo';DROP TABLE mySchema.myTable;" }],
          expectation: {
            query: 'INSERT INTO mySchema.myTable (name) VALUES ($1);',
            bind: ["foo';DROP TABLE mySchema.myTable;"]
          },
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      bulkInsertQuery: [
        {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }]],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'),('bar');"
        }, {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }], { ignoreDuplicates: true }],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'),('bar') ON CONFLICT DO NOTHING;"
        }, {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }], { returning: true }],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'),('bar') RETURNING *;"
        }, {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }], { returning: ['id', 'sentToId'] }],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'),('bar') RETURNING \"id\",\"sentToId\";"
        }, {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }], { ignoreDuplicates: true, returning: true }],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'),('bar') ON CONFLICT DO NOTHING RETURNING *;"
        }, {
          arguments: ['myTable', [{ name: "foo';DROP TABLE myTable;" }, { name: 'bar' }]],
          expectation: "INSERT INTO \"myTable\" (\"name\") VALUES ('foo'';DROP TABLE myTable;'),('bar');"
        }, {
          arguments: ['myTable', [{ name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { name: 'bar', birthday: moment('2012-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"birthday\") VALUES ('foo','2011-03-27 10:01:55.000 +00:00'),('bar','2012-03-27 10:01:55.000 +00:00');"
        }, {
          arguments: ['myTable', [{ name: 'foo', foo: 1 }, { name: 'bar', foo: 2 }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"foo\") VALUES ('foo',1),('bar',2);"
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"nullValue\") VALUES ('foo',NULL),('bar',NULL);"
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"nullValue\") VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { omitNull: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"nullValue\") VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { omitNull: true } } // Note: We don't honour this because it makes little sense when some rows may have nulls and others not
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: undefined }, { name: 'bar', nullValue: undefined }]],
          expectation: "INSERT INTO \"myTable\" (\"name\",\"nullValue\") VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { omitNull: true } } // Note: As above
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: 'foo' }, { name: 'bar' }]],
          expectation: "INSERT INTO \"mySchema\".\"myTable\" (\"name\") VALUES ('foo'),('bar');"
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: JSON.stringify({ info: 'Look ma a " quote' }) }, { name: JSON.stringify({ info: 'Look ma another " quote' }) }]],
          expectation: "INSERT INTO \"mySchema\".\"myTable\" (\"name\") VALUES ('{\"info\":\"Look ma a \\\" quote\"}'),('{\"info\":\"Look ma another \\\" quote\"}');"
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: "foo';DROP TABLE mySchema.myTable;" }, { name: 'bar' }]],
          expectation: "INSERT INTO \"mySchema\".\"myTable\" (\"name\") VALUES ('foo'';DROP TABLE mySchema.myTable;'),('bar');"
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: 'foo' }, { name: 'bar' }], { updateOnDuplicate: ['name'], upsertKeys: ['name'] }],
          expectation: "INSERT INTO \"mySchema\".\"myTable\" (\"name\") VALUES ('foo'),('bar') ON CONFLICT (\"name\") DO UPDATE SET \"name\"=EXCLUDED.\"name\";"
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable', [{ name: 'foo' }, { name: 'bar' }]],
          expectation: "INSERT INTO myTable (name) VALUES ('foo'),('bar');",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', [{ name: "foo';DROP TABLE myTable;" }, { name: 'bar' }]],
          expectation: "INSERT INTO myTable (name) VALUES ('foo'';DROP TABLE myTable;'),('bar');",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { name: 'bar', birthday: moment('2012-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }]],
          expectation: "INSERT INTO myTable (name,birthday) VALUES ('foo','2011-03-27 10:01:55.000 +00:00'),('bar','2012-03-27 10:01:55.000 +00:00');",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', foo: 1 }, { name: 'bar', foo: 2 }]],
          expectation: "INSERT INTO myTable (name,foo) VALUES ('foo',1),('bar',2);",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO myTable (name,nullValue) VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO myTable (name,nullValue) VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { quoteIdentifiers: false, omitNull: false } }
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: null }, { name: 'bar', nullValue: null }]],
          expectation: "INSERT INTO myTable (name,nullValue) VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { omitNull: true, quoteIdentifiers: false } } // Note: We don't honour this because it makes little sense when some rows may have nulls and others not
        }, {
          arguments: ['myTable', [{ name: 'foo', nullValue: undefined }, { name: 'bar', nullValue: undefined }]],
          expectation: "INSERT INTO myTable (name,nullValue) VALUES ('foo',NULL),('bar',NULL);",
          context: { options: { omitNull: true, quoteIdentifiers: false } } // Note: As above
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: 'foo' }, { name: 'bar' }]],
          expectation: "INSERT INTO mySchema.myTable (name) VALUES ('foo'),('bar');",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: JSON.stringify({ info: 'Look ma a " quote' }) }, { name: JSON.stringify({ info: 'Look ma another " quote' }) }]],
          expectation: "INSERT INTO mySchema.myTable (name) VALUES ('{\"info\":\"Look ma a \\\" quote\"}'),('{\"info\":\"Look ma another \\\" quote\"}');",
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, [{ name: "foo';DROP TABLE mySchema.myTable;" }, { name: 'bar' }]],
          expectation: "INSERT INTO mySchema.myTable (name) VALUES ('foo'';DROP TABLE mySchema.myTable;'),('bar');",
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      updateQuery: [
        {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE "myTable" SET "name"=$1,"birthday"=$2 WHERE "id" = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          }
        }, {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE "myTable" SET "name"=$1,"birthday"=$2 WHERE "id" = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          }
        }, {
          arguments: ['myTable', { bar: 2 }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1 WHERE "name" = $2',
            bind: [2, 'foo']
          }
        }, {
          arguments: ['myTable', { bar: 2 }, { name: 'foo' }, { returning: true }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1 WHERE "name" = $2 RETURNING *',
            bind: [2, 'foo']
          }
        }, {
          arguments: ['myTable', { numbers: [1, 2, 3] }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "numbers"=$1 WHERE "name" = $2',
            bind: [[1, 2, 3], 'foo']
          }
        }, {
          arguments: ['myTable', { name: "foo';DROP TABLE myTable;" }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "name"=$1 WHERE "name" = $2',
            bind: ["foo';DROP TABLE myTable;", 'foo']
          }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1,"nullValue"=$2 WHERE "name" = $3',
            bind: [2, null, 'foo']
          }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1,"nullValue"=$2 WHERE "name" = $3',
            bind: [2, null, 'foo']
          },
          context: { options: { omitNull: false } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1 WHERE "name" = $2',
            bind: [2, 'foo']
          },
          context: { options: { omitNull: true } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: undefined }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=$1 WHERE "name" = $2',
            bind: [2, 'foo']
          },
          context: { options: { omitNull: true } }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE "mySchema"."myTable" SET "name"=$1,"birthday"=$2 WHERE "id" = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          }
        }, {
          arguments: [{ tableName: 'myTable', schema: 'mySchema' }, { name: "foo';DROP TABLE mySchema.myTable;" }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "mySchema"."myTable" SET "name"=$1 WHERE "name" = $2',
            bind: ["foo';DROP TABLE mySchema.myTable;", 'foo']
          }
        }, {
          arguments: ['myTable', function(sequelize) {
            return {
              bar: sequelize.fn('NOW')
            };
          }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"=NOW() WHERE "name" = $1',
            bind: ['foo']
          },
          needsSequelize: true
        }, {
          arguments: ['myTable', function(sequelize) {
            return {
              bar: sequelize.col('foo')
            };
          }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE "myTable" SET "bar"="foo" WHERE "name" = $1',
            bind: ['foo']
          },
          needsSequelize: true
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE myTable SET name=$1,birthday=$2 WHERE id = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE myTable SET name=$1,birthday=$2 WHERE id = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { bar: 2 }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET bar=$1 WHERE name = $2',
            bind: [2, 'foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { numbers: [1, 2, 3] }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET numbers=$1 WHERE name = $2',
            bind: [[1, 2, 3], 'foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { name: "foo';DROP TABLE myTable;" }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET name=$1 WHERE name = $2',
            bind: ["foo';DROP TABLE myTable;", 'foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET bar=$1,nullValue=$2 WHERE name = $3',
            bind: [2, null, 'foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET bar=$1,nullValue=$2 WHERE name = $3',
            bind: [2, null, 'foo']
          },
          context: { options: { omitNull: false, quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: null }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET bar=$1 WHERE name = $2',
            bind: [2, 'foo']
          },
          context: { options: { omitNull: true, quoteIdentifiers: false } }
        }, {
          arguments: ['myTable', { bar: 2, nullValue: undefined }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE myTable SET bar=$1 WHERE name = $2',
            bind: [2, 'foo']
          },
          context: { options: { omitNull: true, quoteIdentifiers: false } }
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, { name: 'foo', birthday: moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate() }, { id: 2 }],
          expectation: {
            query: 'UPDATE mySchema.myTable SET name=$1,birthday=$2 WHERE id = $3',
            bind: ['foo', moment('2011-03-27 10:01:55 +0000', 'YYYY-MM-DD HH:mm:ss Z').toDate(), 2]
          },
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, { name: "foo';DROP TABLE mySchema.myTable;" }, { name: 'foo' }],
          expectation: {
            query: 'UPDATE mySchema.myTable SET name=$1 WHERE name = $2',
            bind: ["foo';DROP TABLE mySchema.myTable;", 'foo']
          },
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      removeIndexQuery: [
        {
          arguments: ['User', 'user_foo_bar'],
          expectation: 'DROP INDEX IF EXISTS "user_foo_bar"'
        }, {
          arguments: ['User', ['foo', 'bar']],
          expectation: 'DROP INDEX IF EXISTS "user_foo_bar"'
        }, {
          arguments: ['User', ['foo', 'bar'], { concurrently: true }],
          expectation: 'DROP INDEX CONCURRENTLY IF EXISTS "user_foo_bar"'
        }, {
          arguments: ['User', 'mySchema.user_foo_bar'],
          expectation: 'DROP INDEX IF EXISTS "mySchema"."user_foo_bar"'
        }, {
          arguments: ['User', 'mySchema.user_foo_bar', { concurrently: true }],
          expectation: 'DROP INDEX CONCURRENTLY IF EXISTS "mySchema"."user_foo_bar"'
        },

        // Variants when quoteIdentifiers is false
        {
          arguments: ['User', 'user_foo_bar'],
          expectation: 'DROP INDEX IF EXISTS user_foo_bar',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['User', ['foo', 'bar']],
          expectation: 'DROP INDEX IF EXISTS user_foo_bar',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['User', 'mySchema.user_foo_bar'],
          expectation: 'DROP INDEX IF EXISTS mySchema.user_foo_bar',
          context: { options: { quoteIdentifiers: false } }
        }, {
          arguments: ['User', 'mySchema.user_foo_bar', { concurrently: true }],
          expectation: 'DROP INDEX CONCURRENTLY IF EXISTS mySchema.user_foo_bar',
          context: { options: { quoteIdentifiers: false } }
        }
      ],

      startTransactionQuery: [
        {
          arguments: [{}],
          expectation: 'START TRANSACTION;',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ parent: 'MockTransaction', name: 'transaction-uid' }],
          expectation: 'SAVEPOINT "transaction-uid";',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ parent: 'MockTransaction', name: 'transaction-uid' }],
          expectation: 'SAVEPOINT "transaction-uid";',
          context: { options: { quoteIdentifiers: true } }
        }
      ],

      rollbackTransactionQuery: [
        {
          arguments: [{}],
          expectation: 'ROLLBACK;',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ parent: 'MockTransaction', name: 'transaction-uid' }],
          expectation: 'ROLLBACK TO SAVEPOINT "transaction-uid";',
          context: { options: { quoteIdentifiers: false } }
        },
        {
          arguments: [{ parent: 'MockTransaction', name: 'transaction-uid' }],
          expectation: 'ROLLBACK TO SAVEPOINT "transaction-uid";',
          context: { options: { quoteIdentifiers: true } }
        }
      ],

      createTrigger: [
        {
          arguments: ['myTable', 'myTrigger', 'after', ['insert'],  'myFunction', [], []],
          expectation: 'CREATE TRIGGER "myTrigger" AFTER INSERT ON "myTable" EXECUTE PROCEDURE myFunction();'
        },
        {
          arguments: ['myTable', 'myTrigger', 'before', ['insert', 'update'],  'myFunction', [{ name: 'bar', type: 'INTEGER' }], []],
          expectation: 'CREATE TRIGGER "myTrigger" BEFORE INSERT OR UPDATE ON "myTable" EXECUTE PROCEDURE myFunction(bar INTEGER);'
        },
        {
          arguments: ['myTable', 'myTrigger', 'instead_of', ['insert', 'update'],  'myFunction', [], ['FOR EACH ROW']],
          expectation: 'CREATE TRIGGER "myTrigger" INSTEAD OF INSERT OR UPDATE ON "myTable" FOR EACH ROW EXECUTE PROCEDURE myFunction();'
        },
        {
          arguments: ['myTable', 'myTrigger', 'after_constraint', ['insert', 'update'],  'myFunction', [{ name: 'bar', type: 'INTEGER' }], ['FOR EACH ROW']],
          expectation: 'CREATE CONSTRAINT TRIGGER "myTrigger" AFTER INSERT OR UPDATE ON "myTable" FOR EACH ROW EXECUTE PROCEDURE myFunction(bar INTEGER);'
        }
      ],

      dropTrigger: [
        {
          arguments: ['myTable', 'myTrigger'],
          expectation: 'DROP TRIGGER "myTrigger" ON "myTable" RESTRICT;'
        }
      ],

      renameTrigger: [
        {
          arguments: ['myTable', 'oldTrigger', 'newTrigger'],
          expectation: 'ALTER TRIGGER "oldTrigger" ON "myTable" RENAME TO "newTrigger";'
        }
      ],

      getForeignKeyReferencesQuery: [
        {
          arguments: ['myTable', 'myDatabase'],
          expectation: 'SELECT ' +
            'DISTINCT tc.constraint_name as constraint_name, ' +
            'tc.constraint_schema as constraint_schema, ' +
            'tc.constraint_catalog as constraint_catalog, ' +
            'tc.table_name as table_name,' +
            'tc.table_schema as table_schema,' +
            'tc.table_catalog as table_catalog,' +
            'tc.initially_deferred as initially_deferred,' +
            'tc.is_deferrable as is_deferrable,' +
            'kcu.column_name as column_name,' +
            'ccu.table_schema  AS referenced_table_schema,' +
            'ccu.table_catalog  AS referenced_table_catalog,' +
            'ccu.table_name  AS referenced_table_name,' +
            'ccu.column_name AS referenced_column_name ' +
            'FROM information_schema.table_constraints AS tc ' +
            'JOIN information_schema.key_column_usage AS kcu ' +
            'ON tc.constraint_name = kcu.constraint_name ' +
            'JOIN information_schema.constraint_column_usage AS ccu ' +
            'ON ccu.constraint_name = tc.constraint_name ' +
            'WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name = \'myTable\' AND tc.table_catalog = \'myDatabase\''
        },
        {
          arguments: ['myTable', 'myDatabase', 'mySchema'],
          expectation: 'SELECT ' +
            'DISTINCT tc.constraint_name as constraint_name, ' +
            'tc.constraint_schema as constraint_schema, ' +
            'tc.constraint_catalog as constraint_catalog, ' +
            'tc.table_name as table_name,' +
            'tc.table_schema as table_schema,' +
            'tc.table_catalog as table_catalog,' +
            'tc.initially_deferred as initially_deferred,' +
            'tc.is_deferrable as is_deferrable,' +
            'kcu.column_name as column_name,' +
            'ccu.table_schema  AS referenced_table_schema,' +
            'ccu.table_catalog  AS referenced_table_catalog,' +
            'ccu.table_name  AS referenced_table_name,' +
            'ccu.column_name AS referenced_column_name ' +
            'FROM information_schema.table_constraints AS tc ' +
            'JOIN information_schema.key_column_usage AS kcu ' +
            'ON tc.constraint_name = kcu.constraint_name ' +
            'JOIN information_schema.constraint_column_usage AS ccu ' +
            'ON ccu.constraint_name = tc.constraint_name ' +
            'WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name = \'myTable\' AND tc.table_catalog = \'myDatabase\' AND tc.table_schema = \'mySchema\''
        }
      ],

      getForeignKeyReferenceQuery: [
        {
          arguments: ['myTable', 'myColumn'],
          expectation: 'SELECT ' +
            'DISTINCT tc.constraint_name as constraint_name, ' +
            'tc.constraint_schema as constraint_schema, ' +
            'tc.constraint_catalog as constraint_catalog, ' +
            'tc.table_name as table_name,' +
            'tc.table_schema as table_schema,' +
            'tc.table_catalog as table_catalog,' +
            'tc.initially_deferred as initially_deferred,' +
            'tc.is_deferrable as is_deferrable,' +
            'kcu.column_name as column_name,' +
            'ccu.table_schema  AS referenced_table_schema,' +
            'ccu.table_catalog  AS referenced_table_catalog,' +
            'ccu.table_name  AS referenced_table_name,' +
            'ccu.column_name AS referenced_column_name ' +
            'FROM information_schema.table_constraints AS tc ' +
            'JOIN information_schema.key_column_usage AS kcu ' +
            'ON tc.constraint_name = kcu.constraint_name ' +
            'JOIN information_schema.constraint_column_usage AS ccu ' +
            'ON ccu.constraint_name = tc.constraint_name ' +
            'WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name=\'myTable\' AND  kcu.column_name = \'myColumn\''
        },
        {
          arguments: [{ schema: 'mySchema', tableName: 'myTable' }, 'myColumn'],
          expectation: 'SELECT ' +
            'DISTINCT tc.constraint_name as constraint_name, ' +
            'tc.constraint_schema as constraint_schema, ' +
            'tc.constraint_catalog as constraint_catalog, ' +
            'tc.table_name as table_name,' +
            'tc.table_schema as table_schema,' +
            'tc.table_catalog as table_catalog,' +
            'tc.initially_deferred as initially_deferred,' +
            'tc.is_deferrable as is_deferrable,' +
            'kcu.column_name as column_name,' +
            'ccu.table_schema  AS referenced_table_schema,' +
            'ccu.table_catalog  AS referenced_table_catalog,' +
            'ccu.table_name  AS referenced_table_name,' +
            'ccu.column_name AS referenced_column_name ' +
            'FROM information_schema.table_constraints AS tc ' +
            'JOIN information_schema.key_column_usage AS kcu ' +
            'ON tc.constraint_name = kcu.constraint_name ' +
            'JOIN information_schema.constraint_column_usage AS ccu ' +
            'ON ccu.constraint_name = tc.constraint_name ' +
            'WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name=\'myTable\' AND  kcu.column_name = \'myColumn\'' +
            ' AND tc.table_schema = \'mySchema\''
        }
      ]
    };

    _.each(suites, (tests, suiteTitle) => {
      describe(suiteTitle, () => {
        beforeEach(function() {
          this.queryGenerator = new QueryGenerator({
            sequelize: this.sequelize,
            _dialect: this.sequelize.dialect
          });
        });

        tests.forEach(test => {
          const query = test.expectation.query || test.expectation;
          const title = test.title || `Postgres correctly returns ${query} for ${JSON.stringify(test.arguments)}`;
          it(title, function() {
            if (test.needsSequelize) {
              if (typeof test.arguments[1] === 'function') test.arguments[1] = test.arguments[1](this.sequelize);
              if (typeof test.arguments[2] === 'function') test.arguments[2] = test.arguments[2](this.sequelize);
            }

            // Options would normally be set by the query interface that instantiates the query-generator, but here we specify it explicitly
            this.queryGenerator.options = { ...this.queryGenerator.options, ...test.context && test.context.options };

            const conditions = this.queryGenerator[suiteTitle](...test.arguments);
            expect(conditions).to.deep.equal(test.expectation);
          });
        });
      });
    });

    describe('fromArray()', () => {
      beforeEach(function() {
        this.queryGenerator = new QueryGenerator({
          sequelize: this.sequelize,
          _dialect: this.sequelize.dialect
        });
      });

      const tests = [
        {
          title: 'should convert an enum with no quoted strings to an array',
          arguments: '{foo,bar,foobar}',
          expectation: ['foo', 'bar', 'foobar']
        }, {
          title: 'should convert an enum starting with a quoted string to an array',
          arguments: '{"foo bar",foo,bar}',
          expectation: ['foo bar', 'foo', 'bar']
        }, {
          title: 'should convert an enum ending with a quoted string to an array',
          arguments: '{foo,bar,"foo bar"}',
          expectation: ['foo', 'bar', 'foo bar']
        }, {
          title: 'should convert an enum with a quoted string in the middle to an array',
          arguments: '{foo,"foo bar",bar}',
          expectation: ['foo', 'foo bar', 'bar']
        }, {
          title: 'should convert an enum full of quoted strings to an array',
          arguments: '{"foo bar","foo bar","foo bar"}',
          expectation: ['foo bar', 'foo bar', 'foo bar']
        }
      ];

      _.each(tests, test => {
        it(test.title, function() {
          const convertedText = this.queryGenerator.fromArray(test.arguments);
          expect(convertedText).to.deep.equal(test.expectation);
        });
      });
    });

    describe('With custom schema in Sequelize options', () => {
      beforeEach(function() {
        this.queryGenerator = new QueryGenerator({
          sequelize: customSequelize,
          _dialect: customSequelize.dialect
        });
      });

      const customSchemaSuites = {
        showTablesQuery: [
          {
            title: 'showTablesQuery defaults to the schema set in Sequelize options',
            arguments: [],
            expectation: 'SELECT table_name FROM information_schema.tables WHERE table_schema = \'custom\' AND table_type LIKE \'%TABLE\' AND table_name != \'spatial_ref_sys\';'
          }
        ],
        describeTableQuery: [
          {
            title: 'describeTableQuery defaults to the schema set in Sequelize options',
            arguments: ['myTable', null],
            expectation: 'SELECT pk.constraint_type as "Constraint",c.column_name as "Field", c.column_default as "Default",c.is_nullable as "Null", (CASE WHEN c.udt_name = \'hstore\' THEN c.udt_name ELSE c.data_type END) || (CASE WHEN c.character_maximum_length IS NOT NULL THEN \'(\' || c.character_maximum_length || \')\' ELSE \'\' END) as "Type", (SELECT array_agg(e.enumlabel) FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid WHERE t.typname=c.udt_name) AS "special", (SELECT pgd.description FROM pg_catalog.pg_statio_all_tables AS st INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) WHERE c.ordinal_position=pgd.objsubid AND c.table_name=st.relname) AS "Comment" FROM information_schema.columns c LEFT JOIN (SELECT tc.table_schema, tc.table_name, cu.column_name, tc.constraint_type FROM information_schema.TABLE_CONSTRAINTS tc JOIN information_schema.KEY_COLUMN_USAGE  cu ON tc.table_schema=cu.table_schema and tc.table_name=cu.table_name and tc.constraint_name=cu.constraint_name and tc.constraint_type=\'PRIMARY KEY\') pk ON pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name WHERE c.table_name = \'myTable\' AND c.table_schema = \'custom\''
          }
        ]
      };

      _.each(customSchemaSuites, (customSchemaTests, customSchemaSuiteTitle) => {
        for (const customSchemaTest of customSchemaTests) {
          it(customSchemaTest.title, function() {
            const convertedText = customSchemaTest.arguments ? this.queryGenerator[customSchemaSuiteTitle](...customSchemaTest.arguments) : this.queryGenerator[customSchemaSuiteTitle]();
            expect(convertedText).to.equal(customSchemaTest.expectation);
          });
        }
      });
    });
  });
}