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

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>