Query Interface

An instance of Sequelize uses something called Query Interface to communicate to the database in a dialect-agnostic way. Most of the methods you’ve learned in this manual are implemented with the help of several methods from the query interface.

The methods from the query interface are therefore lower-level methods; you should use them only if you do not find another way to do it with higher-level APIs from Sequelize. They are, of course, still higher-level than running raw queries directly (i.e., writing SQL by hand).

This guide shows a few examples, but for the full list of what it can do, and for detailed usage of each method, check the QueryInterface API.

Obtaining the query interface

From now on, we will call queryInterface the singleton instance of the QueryInterface class, which is available on your Sequelize instance:

  1. const { Sequelize, DataTypes } = require('sequelize');
  2. const sequelize = new Sequelize(/* ... */);
  3. const queryInterface = sequelize.getQueryInterface();

Creating a table

  1. queryInterface.createTable('Person', {
  2.   name: DataTypes.STRING,
  3.   isBetaMember: {
  4.     type: DataTypes.BOOLEAN,
  5.     defaultValue: false,
  6.     allowNull: false
  7.   }
  8. });

Generated SQL (using SQLite):

  1. CREATE TABLE IF NOT EXISTS `Person` (
  2.   `name` VARCHAR(255),
  3.   `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0
  4. );

Note: Consider defining a Model instead and calling YourModel.sync() instead, which is a higher-level approach.

Adding a column to a table

  1. queryInterface.addColumn('Person', 'petName', { type: DataTypes.STRING });

Generated SQL (using SQLite):

  1. ALTER TABLE `Person` ADD `petName` VARCHAR(255);

Changing the datatype of a column

  1. queryInterface.changeColumn('Person', 'foo', {
  2.   type: DataTypes.FLOAT,
  3.   defaultValue: 3.14,
  4.   allowNull: false
  5. });

Generated SQL (using MySQL):

  1. ALTER TABLE `Person` CHANGE `foo` `foo` FLOAT NOT NULL DEFAULT 3.14;

Removing a column

  1. queryInterface.removeColumn('Person', 'petName', { /* query options */ });

Generated SQL (using PostgreSQL):

  1. ALTER TABLE "public"."Person" DROP COLUMN "petName";

Changing and removing columns in SQLite

SQLite does not support directly altering and removing columns. However, Sequelize will try to work around this by recreating the whole table with the help of a backup table, inspired by these instructions.

For example:

  1. // Assuming we have a table in SQLite created as follows:
  2. queryInterface.createTable('Person', {
  3.   name: DataTypes.STRING,
  4.   isBetaMember: {
  5.     type: DataTypes.BOOLEAN,
  6.     defaultValue: false,
  7.     allowNull: false
  8.   },
  9.   petName: DataTypes.STRING,
  10.   foo: DataTypes.INTEGER
  11. });
  13. // And we change a column:
  14. queryInterface.changeColumn('Person', 'foo', {
  15.   type: DataTypes.FLOAT,
  16.   defaultValue: 3.14,
  17.   allowNull: false
  18. });

The following SQL calls are generated for SQLite:

  1. PRAGMA TABLE_INFO(`Person`);
  3. CREATE TABLE IF NOT EXISTS `Person_backup` (
  4.   `name` VARCHAR(255),
  5.   `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0,
  6.   `foo` FLOAT NOT NULL DEFAULT '3.14',
  7.   `petName` VARCHAR(255)
  8. );
  10. INSERT INTO `Person_backup`
  11.   SELECT
  12.     `name`,
  13.     `isBetaMember`,
  14.     `foo`,
  15.     `petName`
  16.   FROM `Person`;
  18. DROP TABLE `Person`;
  20. CREATE TABLE IF NOT EXISTS `Person` (
  21.   `name` VARCHAR(255),
  22.   `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0,
  23.   `foo` FLOAT NOT NULL DEFAULT '3.14',
  24.   `petName` VARCHAR(255)
  25. );
  27. INSERT INTO `Person`
  28.   SELECT
  29.     `name`,
  30.     `isBetaMember`,
  31.     `foo`,
  32.     `petName`
  33.   FROM `Person_backup`;
  35. DROP TABLE `Person_backup`;

Other

As mentioned in the beginning of this guide, there is a lot more to the Query Interface available in Sequelize! Check the QueryInterface API for a full list of what can be done.