publicclass| source

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 |

dropAllSchemas(options: Object): Promise

Drop all schemas

| | | public |

dropAllTables(options: Object): Promise

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 |

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

showAllSchemas(options: Object): Promise<Array>

Show all schemas

| | | public |

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

Upsert

| |

Public Methods

publicaddColumn(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:

Table to add column to

| | key | string | |

Column name

Attribute definition

| | options | Object |

optional

Query options

Return:

| Promise |

publicaddConstraint(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:

Table name where you want to add a constraint

Array of column names to apply the constraint over

An object to define the constraint name, type etc

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

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

Add an index to a column

Params:

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

indexes options

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 |

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

Delete multiple records from a table

Params:

table name from where to delete records

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 |

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

Insert multiple records into a table

Params:

Table name to insert record to

List of records to insert

Various options, please see Model.bulkCreate options

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()
 }]);

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

Update multiple records of a table

Params:

Table name to update

Values to be inserted, mapped to field name

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

publicchangeColumn(tableName: string, attributeName: string, dataTypeOrOptions: Object, options: Object): Promise source

Change a column definition

Params:

Table name to change from

Column name

Attribute definition for new column

| | options | Object |

optional

Query options

Return:

| Promise |

publiccreateDatabase(database: string, options: Object): Promise source

Create a database

Params:

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 |

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

Create an SQL function

Params:

Name of SQL function to create

List of parameters declared for SQL function

SQL type of function returned value

The name of the language that the function is implemented in

Source code of function

Extra-options for creation

| | options | Object |

optional

query options

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

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

publiccreateSchema(schema: string, options: Object): Promise source

Create a schema

Params:

Schema name to create

| | options | Object |

optional

Query options

Return:

| Promise |

publiccreateTable(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 of table to create

Object representing a list of table attributes to create

| | options | Object |

optional

create table and query options

| | model | Model |

optional

model class

Return:

| Promise |

publicdescribeTable(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:

table name

| | options | Object |

optional

Query options

Return:

| Promise<Object> |

publicdropAllSchemas(options: Object): Promise source

Drop all schemas

Params:

optional

Query options

Return:

| Promise |

publicdropAllTables(options: Object): Promise source

Drop all tables from database

Params:

optional

query options

| | options.skip | Array |

optional

List of table to skip

Return:

| Promise |

publicdropDatabase(database: string, options: Object): Promise source

Drop a database

Params:

Database name to drop

| | options | Object |

optional

Query options

Return:

| Promise |

publicdropFunction(functionName: string, params: Array, options: Object): Promise source

Drop an SQL function

Params:

Name of SQL function to drop

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

publicdropSchema(schema: string, options: Object): Promise source

Drop a schema

Params:

Schema name to drop

| | options | Object |

optional

Query options

Return:

| Promise |

publicdropTable(tableName: string, options: Object): Promise source

Drop a table from database

Params:

Table name to drop

Query options

Return:

| Promise |

publicgetForeignKeyReferencesForTable(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:

table name

| | options | Object |

optional

Query options

Return:

| Promise |

publicremoveColumn(tableName: string, attributeName: string, options: Object): Promise source

Remove a column from a table

Params:

Table to remove column from

Column name to remove

| | options | Object |

optional

Query options

Return:

| Promise |

publicremoveConstraint(tableName: string, constraintName: string, options: Object): Promise source

Remove a constraint from a table

Params:

Table name to drop constraint from

Constraint name

Query options

Return:

| Promise |

publicremoveIndex(tableName: string, indexNameOrAttributes: string, options: Object): Promise source

Remove an already existing index from a table

Params:

Table name to drop index from

Index name

| | options | Object |

optional

Query options

Return:

| Promise |

publicrenameColumn(tableName: string, attrNameBefore: string, attrNameAfter: string, options: Object): Promise source

Rename a column

Params:

Table name whose column to rename

Current column name

New column name

| | options | Object |

optional

Query option

Return:

| Promise |

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

Rename an SQL function

Params:

Current name of function

List of parameters declared for SQL function

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

publicrenameTable(before: string, after: string, options: Object): Promise source

Rename a table

Params:

Current name of table

New name from table

| | options | Object |

optional

Query options

Return:

| Promise |

publicshowAllSchemas(options: Object): Promise<Array> source

Show all schemas

Params:

optional

Query options

Return:

| Promise<Array> |

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

Upsert

Params:

table to upsert on

values to be inserted, mapped to field name

values to be updated, mapped to field name

various conditions

Model to upsert on

query options

Return:

| Promise<boolean, ?number> |

Resolves an array with <created, primaryKey>