W3cubDocs

/Sequelize

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

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