QueryInterface

Direct Subclass:

MSSqlQueryInterface, MySQLQueryInterface, PostgresQueryInterface, SQLiteQueryInterface

The interface that Sequelize uses to talk to all databases

Method Summary

Public Methods
public	async addColumn(table: string, key: string, attribute: object, options: object): Promise Add a new column to a table
public	async addConstraint(tableName: string, options: object): Promise Add a constraint to a table
public	async addIndex(tableName: string \| object, attributes: Array, options: object, rawTablename: string): Promise Add an index to a column
public	async bulkDelete(tableName: string, where: object, options: object, model: Model): Promise Delete multiple records from a table
public	async bulkInsert(tableName: string, records: Array, options: object, attributes: object): Promise Insert multiple records into a table
public	async bulkUpdate(tableName: string, values: object, identifier: object, options: object, attributes: object): Promise Update multiple records of a table
public	async changeColumn(tableName: string, attributeName: string, dataTypeOrOptions: object, options: object): * Change a column definition
public	async createDatabase(database: string, options: object): Promise Create a database
public	async createFunction(functionName: string, params: Array, returnType: string, language: string, body: string, optionsArray: Array, options: object): Promise Create an SQL function
public	async createSchema(schema: string, options: object): Promise Create a schema
public	async createTable(tableName: string, attributes: object, options: object, model: Model): Promise Create a table with given set of attributes
public	async describeTable(tableName: string, options: object): Promise<object> Describe a table structure
public	async dropAllSchemas(options: object): Promise Drop all schemas
public	async dropAllTables(options: object): Promise Drop all tables from database
public	async dropDatabase(database: string, options: object): Promise Drop a database
public	async dropFunction(functionName: string, params: Array, options: object): Promise Drop an SQL function
public	async dropSchema(schema: string, options: object): Promise Drop a schema
public	async dropTable(tableName: string, options: object): Promise Drop a table from database
public	async getForeignKeyReferencesForTable(tableName: string, options: object): * Get foreign key references details for the table
public	async getForeignKeysForTables(tableNames: string[], options: object): Promise Returns all foreign key constraints of requested tables
public	async removeColumn(tableName: string, attributeName: string, options: object): * Remove a column from a table
public	async removeConstraint(tableName: string, constraintName: string, options: object): * Remove a constraint from a table
public	async removeIndex(tableName: string, indexNameOrAttributes: string \| string[], options: object): Promise Remove an already existing index from a table
public	async renameColumn(tableName: string, attrNameBefore: string, attrNameAfter: string, options: object): Promise Rename a column
public	async renameFunction(oldFunctionName: string, params: Array, newFunctionName: string, options: object): Promise Rename an SQL function
public	async renameTable(before: string, after: string, options: object): Promise Rename a table
public	async showAllSchemas(options: object): Promise<Array> Show all schemas
public	async upsert(tableName: string, insertValues: object, updateValues: object, where: object, options: object): Promise<boolean, ?number> Upsert

Public Methods

public async addColumn(table: string, key: string, attribute: object, options: object): Promise source

Add a new column to a table

queryInterface.addColumn('tableA', 'columnC', Sequelize.STRING, {
   after: 'columnB' // after option is only supported by MySQL
});

Params:

Name	Type	Attribute	Description
table	string		Table to add column to
key	string		Column name
attribute	object		Attribute definition
options	object	optional	Query options

Return:

Promise

public async addConstraint(tableName: string, options: object): Promise source

Add a constraint to a table

Available constraints:

UNIQUE
DEFAULT (MSSQL only)
CHECK (MySQL - Ignored by the database engine )
FOREIGN KEY
PRIMARY KEY

Params:

Name	Type	Attribute	Description
tableName	string		Table name where you want to add a constraint
options	object		An object to define the constraint name, type etc
options.type	string		Type of constraint. One of the values in available constraints(case insensitive)
options.fields	Array		Array of column names to apply the constraint over
options.name	string	optional	Name of the constraint. If not specified, sequelize automatically creates a named constraint based on constraint type, table & column names
options.defaultValue	string	optional	The value for the default constraint
options.where	object	optional	Where clause/expression for the CHECK constraint
options.references	object	optional	Object specifying target table, column name to create foreign key constraint
options.references.table	string	optional	Target table name
options.references.field	string	optional	Target column name

Return:

Promise

Example:

UNIQUE

queryInterface.addConstraint('Users', {
  fields: ['email'],
  type: 'unique',
  name: 'custom_unique_constraint_name'
});

CHECK

queryInterface.addConstraint('Users', {
  fields: ['roles'],
  type: 'check',
  where: {
     roles: ['user', 'admin', 'moderator', 'guest']
  }
});

Default - MSSQL only

queryInterface.addConstraint('Users', {
   fields: ['roles'],
   type: 'default',
   defaultValue: 'guest'
});

Primary Key

queryInterface.addConstraint('Users', {
   fields: ['username'],
   type: 'primary key',
   name: 'custom_primary_constraint_name'
});

Foreign Key

queryInterface.addConstraint('Posts', {
  fields: ['username'],
  type: 'foreign key',
  name: 'custom_fkey_constraint_name',
  references: { //Required field
    table: 'target_table_name',
    field: 'target_column_name'
  },
  onDelete: 'cascade',
  onUpdate: 'cascade'
});

public async addIndex(tableName: string | object, attributes: Array, options: object, rawTablename: string): Promise source

Add an index to a column

Params:

Name	Type	Attribute	Description
tableName	string \| object		Table name to add index on, can be a object with schema
attributes	Array	optional	Use options.fields instead, List of attributes to add index on
options	object		indexes options
options.fields	Array		List of attributes to add index on
options.concurrently	boolean	optional	Pass CONCURRENT so other operations run while the index is created
options.unique	boolean	optional	Create a unique index
options.using	string	optional	Useful for GIN indexes
options.operator	string	optional	Index operator
options.type	string	optional	Type of index, available options are UNIQUE\|FULLTEXT\|SPATIAL
options.name	string	optional	Name of the index. Default is <table><attr1><attr2>
options.where	object	optional	Where condition on index, for partial indexes
rawTablename	string	optional	table name, this is just for backward compatibiity

Return:

Promise

public async bulkDelete(tableName: string, where: object, options: object, model: Model): Promise source

Delete multiple records from a table

Params:

Name	Type	Attribute	Description
tableName	string		table name from where to delete records
where	object		where conditions to find records to delete
options	object	optional	options
options.truncate	boolean	optional	Use truncate table command
options.cascade	boolean	optional default: false	Only used in conjunction with TRUNCATE. Truncates all tables that have foreign-key references to the named table, or to any tables added to the group due to CASCADE.
options.restartIdentity	boolean	optional default: false	Only used in conjunction with TRUNCATE. Automatically restart sequences owned by columns of the truncated table.
model	Model	optional	Model

Return:

Promise

public async bulkInsert(tableName: string, records: Array, options: object, attributes: object): Promise source

Insert multiple records into a table

Params:

Name	Type	Description
tableName	string	Table name to insert record to
records	Array	List of records to insert
options	object	Various options, please see Model.bulkCreate options
attributes	object	Various attributes mapped by field name

Return:

Promise

Example:

queryInterface.bulkInsert('roles', [{
   label: 'user',
   createdAt: new Date(),
   updatedAt: new Date()
 }, {
   label: 'admin',
   createdAt: new Date(),
   updatedAt: new Date()
 }]);

public async bulkUpdate(tableName: string, values: object, identifier: object, options: object, attributes: object): Promise source

Update multiple records of a table

Params:

Name	Type	Attribute	Description
tableName	string		Table name to update
values	object		Values to be inserted, mapped to field name
identifier	object		A hash with conditions OR an ID as integer OR a string with conditions
options	object	optional	Various options, please see Model.bulkCreate options
attributes	object	optional	Attributes on return objects if supported by SQL dialect

Return:

Promise

Example:

queryInterface.bulkUpdate('roles', {
    label: 'admin',
  }, {
    userType: 3,
  },
);

public async changeColumn(tableName: string, attributeName: string, dataTypeOrOptions: object, options: object): * source

Change a column definition

Params:

Name	Type	Attribute	Description
tableName	string		Table name to change from
attributeName	string		Column name
dataTypeOrOptions	object		Attribute definition for new column
options	object	optional	Query options

Return:

public async createDatabase(database: string, options: object): Promise source

Create a database

Params:

Name	Type	Attribute	Description
database	string		Database name to create
options	object	optional	Query options
options.charset	string	optional	Database default character set, MYSQL only
options.collate	string	optional	Database default collation
options.encoding	string	optional	Database default character set, PostgreSQL only
options.ctype	string	optional	Database character classification, PostgreSQL only
options.template	string	optional	The name of the template from which to create the new database, PostgreSQL only

Return:

Promise

public async createFunction(functionName: string, params: Array, returnType: string, language: string, body: string, optionsArray: Array, options: object): Promise source

Create an SQL function

Params:

Name	Type	Attribute	Description
functionName	string		Name of SQL function to create
params	Array		List of parameters declared for SQL function
returnType	string		SQL type of function returned value
language	string		The name of the language that the function is implemented in
body	string		Source code of function
optionsArray	Array		Extra-options for creation
options	object	optional	query options
options.force	boolean		If force is true, any existing functions with the same parameters will be replaced. For postgres, this means using `CREATE OR REPLACE FUNCTION` instead of `CREATE FUNCTION`. Default is false
options.variables	Array<object>		List of declared variables. Each variable should be an object with string fields `type` and `name`, and optionally having a `default` field as well.

Return:

Promise

Example:

queryInterface.createFunction(
  'someFunction',
  [
    {type: 'integer', name: 'param', direction: 'IN'}
  ],
  'integer',
  'plpgsql',
  'RETURN param + 1;',
  [
    'IMMUTABLE',
    'LEAKPROOF'
  ],
  {
   variables:
     [
       {type: 'integer', name: 'myVar', default: 100}
     ],
     force: true
  };
);

public async createSchema(schema: string, options: object): Promise source

Create a schema

Params:

Name	Type	Attribute	Description
schema	string		Schema name to create
options	object	optional	Query options

Return:

Promise

public async createTable(tableName: string, attributes: object, options: object, model: Model): Promise source

Create a table with given set of attributes

queryInterface.createTable(
  'nameOfTheNewTable',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    createdAt: {
      type: Sequelize.DATE
    },
    updatedAt: {
      type: Sequelize.DATE
    },
    attr1: Sequelize.STRING,
    attr2: Sequelize.INTEGER,
    attr3: {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
      allowNull: false
    },
    //foreign key usage
    attr4: {
      type: Sequelize.INTEGER,
      references: {
        model: 'another_table_name',
        key: 'id'
      },
      onUpdate: 'cascade',
      onDelete: 'cascade'
    }
  },
  {
    engine: 'MYISAM',    // default: 'InnoDB'
    charset: 'latin1',   // default: null
    schema: 'public',    // default: public, PostgreSQL only.
    comment: 'my table', // comment for table
    collate: 'latin1_danish_ci' // collation, MYSQL only
  }
)

Params:

Name	Type	Attribute	Description
tableName	string		Name of table to create
attributes	object		Object representing a list of table attributes to create
options	object	optional	create table and query options
model	Model	optional	model class

Return:

Promise

public async describeTable(tableName: string, options: object): Promise<object> source

Describe a table structure

This method returns an array of hashes containing information about all attributes in the table.

{
   name: {
     type:         'VARCHAR(255)', // this will be 'CHARACTER VARYING' for pg!
     allowNull:    true,
     defaultValue: null
   },
   isBetaMember: {
     type:         'TINYINT(1)', // this will be 'BOOLEAN' for pg!
     allowNull:    false,
     defaultValue: false
   }
}

Params:

Name	Type	Attribute	Description
tableName	string		table name
options	object	optional	Query options

Return:

Promise<object>

public async dropAllSchemas(options: object): Promise source

Drop all schemas

Params:

Name	Type	Attribute	Description
options	object	optional	Query options

Return:

Promise

public async dropAllTables(options: object): Promise source

Drop all tables from database

Params:

Name	Type	Attribute	Description
options	object	optional	query options
options.skip	Array	optional	List of table to skip

Return:

Promise

public async dropDatabase(database: string, options: object): Promise source

Drop a database

Params:

Name	Type	Attribute	Description
database	string		Database name to drop
options	object	optional	Query options

Return:

Promise

public async dropFunction(functionName: string, params: Array, options: object): Promise source

Drop an SQL function

Params:

Name	Type	Attribute	Description
functionName	string		Name of SQL function to drop
params	Array		List of parameters declared for SQL function
options	object	optional	query options

Return:

Promise

Example:

queryInterface.dropFunction(
  'someFunction',
  [
    {type: 'varchar', name: 'param1', direction: 'IN'},
    {type: 'integer', name: 'param2', direction: 'INOUT'}
  ]
);

public async dropSchema(schema: string, options: object): Promise source

Drop a schema

Params:

Name	Type	Attribute	Description
schema	string		Schema name to drop
options	object	optional	Query options

Return:

Promise

public async dropTable(tableName: string, options: object): Promise source

Drop a table from database

Params:

Name	Type	Attribute	Description
tableName	string		Table name to drop
options	object		Query options

Return:

Promise

public async getForeignKeyReferencesForTable(tableName: string, options: object): * source

Get foreign key references details for the table

Those details contains constraintSchema, constraintName, constraintCatalog tableCatalog, tableSchema, tableName, columnName, referencedTableCatalog, referencedTableCatalog, referencedTableSchema, referencedTableName, referencedColumnName. Remind: constraint informations won't return if it's sqlite.

Params:

Name	Type	Attribute	Description
tableName	string		table name
options	object	optional	Query options

Return:

public async getForeignKeysForTables(tableNames: string[], options: object): Promise source

Returns all foreign key constraints of requested tables

Params:

Name	Type	Attribute	Description
tableNames	string[]		table names
options	object	optional	Query options

Return:

Promise

public async removeColumn(tableName: string, attributeName: string, options: object): * source

Remove a column from a table

Params:

Name	Type	Attribute	Description
tableName	string		Table to remove column from
attributeName	string		Column name to remove
options	object	optional	Query options

Return:

public async removeConstraint(tableName: string, constraintName: string, options: object): * source

Remove a constraint from a table

Params:

Name	Type	Description
tableName	string	Table name to drop constraint from
constraintName	string	Constraint name
options	object	Query options

Return:

public async removeIndex(tableName: string, indexNameOrAttributes: string | string[], options: object): Promise source

Remove an already existing index from a table

Params:

Name	Type	Attribute	Description
tableName	string		Table name to drop index from
indexNameOrAttributes	string \| string[]		Index name or list of attributes that in the index
options	object	optional	Query options

Return:

Promise

public async renameColumn(tableName: string, attrNameBefore: string, attrNameAfter: string, options: object): Promise source

Rename a column

Params:

Name	Type	Attribute	Description
tableName	string		Table name whose column to rename
attrNameBefore	string		Current column name
attrNameAfter	string		New column name
options	object	optional	Query option

Return:

Promise

public async renameFunction(oldFunctionName: string, params: Array, newFunctionName: string, options: object): Promise source

Rename an SQL function

Params:

Name	Type	Attribute	Description
oldFunctionName	string		Current name of function
params	Array		List of parameters declared for SQL function
newFunctionName	string		New name of function
options	object	optional	query options

Return:

Promise

Example:

queryInterface.renameFunction(
  'fooFunction',
  [
    {type: 'varchar', name: 'param1', direction: 'IN'},
    {type: 'integer', name: 'param2', direction: 'INOUT'}
  ],
  'barFunction'
);

public async renameTable(before: string, after: string, options: object): Promise source

Rename a table

Params:

Name	Type	Attribute	Description
before	string		Current name of table
after	string		New name from table
options	object	optional	Query options

Return:

Promise

public async showAllSchemas(options: object): Promise<Array> source

Show all schemas

Params:

Name	Type	Attribute	Description
options	object	optional	Query options

Return:

Promise<Array>

public async upsert(tableName: string, insertValues: object, updateValues: object, where: object, options: object): Promise<boolean, ?number> source

Upsert

Params:

Name	Type	Description
tableName	string	table to upsert on
insertValues	object	values to be inserted, mapped to field name
updateValues	object	values to be updated, mapped to field name
where	object	where conditions, which can be used for UPDATE part when INSERT fails
options	object	query options

Return:

Promise<boolean, ?number>

Resolves an array with <created, primaryKey>