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 into a table
public

addConstraint(tableName: String, attributes: Array, options: Object): Promise

Add constraints to table
public

addIndex(tableName: String, options: Object): Promise

Add index to a column
public

bulkDelete(tableName: String, identifier: Object): Promise

Delete records from a table
public

bulkInsert(tableName: String, records: Array, options: Object, fieldMappedAttributes: Object): Promise

Insert records into a table
public

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

Change a column definition
public

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

Create SQL function
public

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

Creates 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

dropAllSchemas(options: Object): Promise

Drop all schemas
public

dropAllTables(options: Object): Promise

Drop all tables from database
public

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

Drop SQL function
public

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

Drops a schema
public

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

Drops a table from database
public

getForeignKeyReferencesForTable(tableName: String, options: Object): Promise

Get foreign key references details for the table.
public

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

Remove a column from table
public

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

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 SQL function
public

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

Renames a table
public

showAllSchemas(options: Object): Promise<Array>

Show all schemas
public

upsert(tableName: String, insertValues: Object, updateValues: Object, where: Object, model: Model, options: Object): Promise<created, primaryKey>

Upsert

Public Methods

public addColumn(table: String, key: String, attribute: Object, options: Object): Promise source

Add a new column into a table

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): Promise source

Add constraints to table

Available constraints:

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

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'
});

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

Return:

Promise

public addIndex(tableName: String, options: Object): Promise source

Add index to a column

Params:

Name Type Attribute Description
tableName String

Table name to add index on
options Object
options.fields Array

List of attributes to add index on
options.unique Boolean
  • optional

Create a unique index
options.using String
  • optional

Useful for GIN indexes
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

Return:

Promise

public bulkDelete(tableName: String, identifier: Object): Promise source

Delete records from a table

Params:

Name Type Attribute Description
tableName String

Table name from where to delete records
identifier Object

Where conditions to find records to delete

Return:

Promise

public bulkInsert(tableName: String, records: Array, options: Object, fieldMappedAttributes: Object): Promise source

Insert records into a table

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

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
fieldMappedAttributes Object

Various attributes mapped by field name

Return:

Promise

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 createFunction(functionName: String, params: Array, returnType: String, language: String, body: String, optionsArray: Array, options: Object): Promise source

Create SQL function

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

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

Return:

Promise

public createSchema(schema: String, options: Object): Promise source

Creates 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.
  }
)

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
model Model
  • optional

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
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
options.skip Array
  • optional

List of table to skip

Return:

Promise

public dropFunction(functionName: String, params: Array, options: Object): Promise source

Drop SQL function

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

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

Return:

Promise

public dropSchema(schema: String, options: Object): Promise source

Drops a schema

Params:

Name Type Attribute Description
schema String

Schema name to create
options Object
  • optional

Query options

Return:

Promise

public dropTable(tableName: String, options: Object): Promise source

Drops 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
options Object
  • optional

Query options

Return:

Promise

public removeColumn(tableName: String, attributeName: String, options: Object): Promise source

Remove a column from table

Params:

Name Type Attribute Description
tableName String

Table to remove column from
attributeName String

Columns name to remove
options Object
  • optional

Query options

Return:

Promise

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

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 SQL function

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

Params:

Name Type Attribute Description
oldFunctionName String
params Array

List of parameters declared for SQL function
newFunctionName String
options Object
  • optional

Return:

Promise

public renameTable(before: String, after: String, options: Object): Promise source

Renames 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<created, primaryKey> source

Upsert

Params:

Name Type Attribute Description
tableName String
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
options Object

Return:

Promise<created, primaryKey>

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