Guides Reference Source
public class | source

QueryInterface

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

quoteIdentifier(identifier: string, force: boolean): string

Split a list of identifiers by "." and quote each part

public

quoteIdentifiers(identifiers: string): string

Split a list of identifiers by "." and quote each part.

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 tableExists(tableName: TableName, options: QueryOptions): Promise<boolean>

Returns a promise that will resolve to true if the table exists in the database, false otherwise.

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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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

options.references.fields string
  • optional

Target column names for a composite primary key. Must match the order of fields in options.fields.

options.deferrable string
  • optional

Sets the constraint to be deferred or immediately checked. See Sequelize.Deferrable. PostgreSQL Only

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'
});
Composite Foreign Key
queryInterface.addConstraint('TableName', {
  fields: ['source_column_name', 'other_source_column_name'],
  type: 'foreign key',
  name: 'custom_fkey_constraint_name',
  references: { //Required field
    table: 'target_table_name',
    fields: ['target_column_name', 'other_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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
tableName string

table name

options object
  • optional

Query options

Return:

Promise<object>

public async dropAllSchemas(options: object): Promise source

Drop all schemas

Params:

NameTypeAttributeDescription
options object
  • optional

Query options

Return:

Promise

public async dropAllTables(options: object): Promise source

Drop all tables from database

Params:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
tableNames string[]

table names

options object
  • optional

Query options

Return:

Promise

public quoteIdentifier(identifier: string, force: boolean): string source

Split a list of identifiers by "." and quote each part

Params:

NameTypeAttributeDescription
identifier string
force boolean

Return:

string

public quoteIdentifiers(identifiers: string): string source

Split a list of identifiers by "." and quote each part.

Params:

NameTypeAttributeDescription
identifiers string

Return:

string

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

Remove a column from a table

Params:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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

options.concurrently boolean
  • optional

Pass CONCURRENTLY so other operations run while the index is created

Return:

Promise

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

Rename a column

Params:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
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:

NameTypeAttributeDescription
options object
  • optional

Query options

Return:

Promise<Array>

public async tableExists(tableName: TableName, options: QueryOptions): Promise<boolean> source

Returns a promise that will resolve to true if the table exists in the database, false otherwise.

Params:

NameTypeAttributeDescription
tableName TableName

The name of the table

options QueryOptions

Query options

Return:

Promise<boolean>

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

Upsert

Params:

NameTypeAttributeDescription
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>