Home Reference Source RepositoryJoin us on Slack
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:

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

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

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

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

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

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

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

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

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

NameTypeAttributeDescription
tableName String
options Object
  • optional

Query options

Return:

Promise<Object>

public dropAllSchemas(options: Object): Promise source

Drop all schemas

Params:

NameTypeAttributeDescription
options Object
  • optional

Query options

Return:

Promise

public dropAllTables(options: Object): Promise source

Drop all tables from database

Params:

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

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

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

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

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

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

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

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

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

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

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

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

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