QueryInterface

The interface that Sequelize uses to talk to all databases

Method Summary

Public Methods
public

addColumn(table: string, key: string, attribute: Object, options: Object): Promise

Add a new column to a table

public

addConstraint(tableName: string, attributes: Array, options: Object, rawTablename: string): Promise

Add a constraint to a table

public

addIndex(tableName: string | Object, attributes: Array, options: Object, rawTablename: string): Promise

Add an index to a column

public

bulkDelete(tableName: string, where: Object, options: Object, model: Model): Promise

Delete multiple records from a table

public

bulkInsert(tableName: string, records: Array, options: Object, attributes: Object): Promise

Insert multiple records into a table

public

bulkUpdate(tableName: string, values: Object, identifier: Object, options: Object, attributes: Object): Promise

Update multiple records of a table

public

changeColumn(tableName: string, attributeName: string, dataTypeOrOptions: Object, options: Object): Promise

Change a column definition

public

createDatabase(database: string, options: Object): Promise

Create a database

public

createFunction(functionName: string, params: Array, returnType: string, language: string, body: string, optionsArray: Array, options: Object): Promise

Create an SQL function

public

createSchema(schema: string, options: Object): Promise

Create a schema

public

createTable(tableName: string, attributes: Object, options: Object, model: Model): Promise

Create a table with given set of attributes

public

describeTable(tableName: string, options: Object): Promise<Object>

Describe a table structure

public

Drop all schemas

public

Drop all tables from database

public

dropDatabase(database: string, options: Object): Promise

Drop a database

public

dropFunction(functionName: string, params: Array, options: Object): Promise

Drop an SQL function

public

dropSchema(schema: string, options: Object): Promise

Drop a schema

public

dropTable(tableName: string, options: Object): Promise

Drop a table from database

public

Get foreign key references details for the table

public

removeColumn(tableName: string, attributeName: string, options: Object): Promise

Remove a column from a table

public

removeConstraint(tableName: string, constraintName: string, options: Object): Promise

Remove a constraint from a table

public

removeIndex(tableName: string, indexNameOrAttributes: string, options: Object): Promise

Remove an already existing index from a table

public

renameColumn(tableName: string, attrNameBefore: string, attrNameAfter: string, options: Object): Promise

Rename a column

public

renameFunction(oldFunctionName: string, params: Array, newFunctionName: string, options: Object): Promise

Rename an SQL function

public

renameTable(before: string, after: string, options: Object): Promise

Rename a table

public

Show all schemas

public

upsert(tableName: string, insertValues: Object, updateValues: Object, where: Object, model: Model, options: Object): Promise<boolean, ?number>

Upsert

Public Methods

public 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 addConstraint(tableName: string, attributes: Array, options: Object, rawTablename: string): 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

attributes Array

Array of column names to apply the constraint over

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

rawTablename string
  • optional

Table name, for backward compatibility

Return:

Promise

Example:

UNIQUE
queryInterface.addConstraint('Users', ['email'], {
  type: 'unique',
  name: 'custom_unique_constraint_name'
});
CHECK
queryInterface.addConstraint('Users', ['roles'], {
  type: 'check',
  where: {
     roles: ['user', 'admin', 'moderator', 'guest']
  }
});
Default - MSSQL only
queryInterface.addConstraint('Users', ['roles'], {
   type: 'default',
   defaultValue: 'guest'
});
Primary Key
queryInterface.addConstraint('Users', ['username'], {
   type: 'primary key',
   name: 'custom_primary_constraint_name'
});
Foreign Key
queryInterface.addConstraint('Posts', ['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 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 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 bulkInsert(tableName: string, records: Array, options: Object, attributes: Object): Promise source

Insert multiple records into a table

Params:

Name Type Attribute 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 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 changeColumn(tableName: string, attributeName: string, dataTypeOrOptions: Object, options: Object): Promise 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:

Promise

public 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 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 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 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 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 dropAllSchemas(options: Object): Promise source

Drop all schemas

Params:

Name Type Attribute Description
options Object
  • optional

Query options

Return:

Promise

public 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 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 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 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 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 getForeignKeyReferencesForTable(tableName: string, options: Object): Promise 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:

Promise

public removeColumn(tableName: string, attributeName: string, options: Object): Promise 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:

Promise

public removeConstraint(tableName: string, constraintName: string, options: Object): Promise source

Remove a constraint from a table

Params:

Name Type Attribute Description
tableName string

Table name to drop constraint from

constraintName string

Constraint name

options Object

Query options

Return:

Promise

public removeIndex(tableName: string, indexNameOrAttributes: 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

Index name

options Object
  • optional

Query options

Return:

Promise

public 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 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 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 showAllSchemas(options: Object): Promise<Array> source

Show all schemas

Params:

Name Type Attribute Description
options Object
  • optional

Query options

Return:

Promise<Array>

public upsert(tableName: string, insertValues: Object, updateValues: Object, where: Object, model: Model, options: Object): Promise<boolean, ?number> source

Upsert

Params:

Name Type Attribute 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

various conditions

model Model

Model to upsert on

options Object

query options

Return:

Promise<boolean, ?number>

Resolves an array with <created, primaryKey>

Copyright © 2014–present Sequelize contributors
Licensed under the MIT License.
https://sequelize.org/v5/class/lib/query-interface.js~QueryInterface.html