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

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

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

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

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

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

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

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

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

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

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 |

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:

| Name | Type | Attribute | Description | | tableName | String | | | | options | Object |

• optional

|

Query options

|

Return:

| Promise<Object> |

publicdropAllSchemas(options: Object): Promise source

Drop all schemas

Params:

| Name | Type | Attribute | Description | | options | Object |

• optional

|

Query options

|

Return:

| Promise |

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

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

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

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

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:

| Name | Type | Attribute | Description | | tableName | String | | | | options | Object |

• optional

|

Query options

|

Return:

| Promise |

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

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

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

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

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

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

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

Show all schemas

Params:

| Name | Type | Attribute | Description | | options | Object |

• optional

|

Query options

|

Return:

| Promise<Array> |

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