Documentation
    Preparing search index...

    Class PostgresQueryInterface<Dialect>

    This interface exposes low-level APIs to interact with the database. Typically useful in contexts where models are not available, such as migrations.

    This interface is available through Sequelize#queryInterface.

    Type Parameters

    Hierarchy (View Summary)

    Index
    constructor
    dialect
    queryGenerator sequelize
    _commitTransaction _createSavepoint _rollbackSavepoint _rollbackTransaction _setIsolationLevel _startTransaction addColumn addConstraint addIndex assertTableHasColumn bulkDelete bulkInsert bulkUpdate changeColumn createDatabase createFunction createSchema createTable createTrigger decrement deferConstraints describeTable dropAllEnums dropAllSchemas dropAllTables dropDatabase dropFunction dropSchema dropTable dropTrigger fetchDatabaseVersion getForeignKeyReferencesForTable getForeignKeysForTables increment insert listDatabases listSchemas listTables nameIndexes quoteIdentifier quoteIdentifiers rawSelect removeColumn removeConstraint removeIndex renameColumn renameFunction renameTable renameTrigger select showAllSchemas showAllTables showConstraints showIndex tableExists truncate unsafeToggleForeignKeyChecks update upsert withoutForeignKeyChecks
    dialect: Dialect
    • get queryGenerator(): Dialect["queryGenerator"]

      Returns Dialect["queryGenerator"]

    • get sequelize(): Sequelize<Dialect>

      Returns Sequelize<Dialect>

    _commitTransaction

    _createSavepoint

    _rollbackSavepoint

    _rollbackTransaction

    _setIsolationLevel

    _startTransaction

    • Add a constraint to a table

      Available constraints:

      • UNIQUE
      • DEFAULT (MSSQL only)
      • CHECK (Not supported by MySQL)
      • FOREIGN KEY
      • PRIMARY KEY

      Parameters

      Returns Promise<void>

      queryInterface.addConstraint('Users', {
        fields: ['email'],
        type: 'UNIQUE',
        name: 'custom_unique_constraint_name'
      });

      queryInterface.addConstraint('Users', {
        fields: ['roles'],
        type: 'CHECK',
        where: {
           roles: ['user', 'admin', 'moderator', 'guest']
        }
      });

      queryInterface.addConstraint('Users', {
         fields: ['roles'],
         type: 'DEFAULT',
         defaultValue: 'guest'
      });

      queryInterface.addConstraint('Users', {
         fields: ['username'],
         type: 'PRIMARY KEY',
         name: 'custom_primary_constraint_name'
      });

      queryInterface.addConstraint('Users', {
         fields: ['first_name', 'last_name'],
         type: 'PRIMARY KEY',
         name: 'custom_primary_constraint_name'
      });

      queryInterface.addConstraint('Posts', {
        fields: ['username'],
        type: 'FOREIGN KEY',
        name: 'custom_fkey_constraint_name',
        references: { //Required field
          table: 'target_table_name',
          field: 'target_column_name'
        },
        onDelete: 'cascade',
        onUpdate: 'cascade'
      });

      queryInterface.addConstraint('TableName', {
        fields: ['source_column_name', 'other_source_column_name'],
        type: 'FOREIGN KEY',
        name: 'custom_fkey_constraint_name',
        references: { //Required field
          table: 'target_table_name',
          fields: ['target_column_name', 'other_target_column_name']
        },
        onDelete: 'cascade',
        onUpdate: 'cascade'
      });

    • Adds a new index to a table

      Parameters

      Returns Promise<void>

    • Adds a new index to a table

      Parameters

      • tableName: TableOrModel
      • options: {
            benchmark?: boolean;
            bind?: BindOrReplacements;
            concurrently?: boolean;
            connection?: AbstractConnection | null;
            fieldMap?: FieldMap;
            fields: (string | Fn | Literal | IndexField)[];
            include?: Literal | (string | Literal)[];
            instance?: Model<any, any>;
            logging?: false | ((sql: string, timing?: number) => void);
            mapToModel?: boolean;
            msg?: string;
            name?: string;
            nest?: boolean;
            operator?: string;
            parser?: string | null;
            plain?: boolean;
            prefix?: string;
            raw?: boolean;
            replacements?: Record<string, unknown>;
            retry?: Options;
            supportsSearchPath?: boolean;
            transaction?: Transaction | null;
            type?: IndexType;
            unique?: boolean;
            useMaster?: boolean;
            using?: string;
            where?: WhereOptions;
        }
        • Optionalbenchmark?: boolean

          Pass query execution time in milliseconds as second argument to logging function (options.logging).

        • Optionalbind?: BindOrReplacements

          Either an object of named parameter bindings in the format $param or an array of unnamed values to bind to $1, $2, etc in your SQL.

        • Optionalconcurrently?: boolean

          PostgreSQL will build the index without taking any write locks. Postgres only.

          false
        • Optionalconnection?: AbstractConnection | null

          The connection on which this query must be run. Mutually exclusive with Transactionable.transaction.

          Can be used to ensure that a query is run on the same connection as a previous query, which is useful when configuring session options.

          Specifying this option takes precedence over CLS Transactions. If a transaction is running in the current AsyncLocalStorage context, it will be ignored in favor of the specified connection.

        • OptionalfieldMap?: FieldMap

          Map returned fields to arbitrary names for SELECT query type if options.fieldMaps is present.

        • fields: (string | Fn | Literal | IndexField)[]

          The fields to index.

        • Optionalinclude?: Literal | (string | Literal)[]

          Non-key columns to be added to the lead level of the nonclustered index.

        • Optionalinstance?: Model<any, any>

          A sequelize instance used to build the return instance

        • Optionallogging?: false | ((sql: string, timing?: number) => void)

          A function that gets executed while running the query to log the sql.

        • OptionalmapToModel?: boolean

          Map returned fields to model's fields if options.model or options.instance is present. Mapping will occur before building the model instance.

        • Optionalmsg?: string

          The message to display if the unique constraint is violated.

        • Optionalname?: string

          The name of the index. Defaults to model name + _ + fields concatenated

        • Optionalnest?: boolean

          If true, transforms objects with . separated property names into nested objects using dottie.js. For example { 'user.username': 'john' } becomes { user: { username: 'john' }}. When nest is true, the query type is assumed to be 'SELECT', unless otherwise specified

          false
        • Optionaloperator?: string

          Index operator type. Postgres only

        • Optionalparser?: string | null

          For FULLTEXT columns set your parser

        • Optionalplain?: boolean

          Sets the query type to SELECT and return a single row

        • Optionalprefix?: string

          Prefix to append to the index name.

        • Optionalraw?: boolean

          If true, sequelize will not try to format the results of the query, or build an instance of a model from the result

        • Optionalreplacements?: Record<string, unknown>

          Only named replacements are allowed in query interface methods.

        • Optionalretry?: Options
        • OptionalsupportsSearchPath?: boolean

          If false do not prepend the query with the search_path (Postgres only)

        • Optionaltransaction?: Transaction | null

          The transaction in which this query must be run. Mutually exclusive with Transactionable.connection.

          If the Sequelize disableClsTransactions option has not been set to true, and a transaction is running in the current AsyncLocalStorage context, that transaction will be used, unless null or another Transaction is manually specified here.

        • Optionaltype?: IndexType

          Index type. Only used by mysql. One of UNIQUE, FULLTEXT and SPATIAL

        • Optionalunique?: boolean

          Should the index be unique? Can also be triggered by setting type to UNIQUE

          false
        • OptionaluseMaster?: boolean

          Force the query to use the write pool, regardless of the query type.

          false
        • Optionalusing?: string

          The method to create the index by (USING statement in SQL). BTREE and HASH are supported by mysql and postgres. Postgres additionally supports GIST, SPGIST, BRIN and GIN.

        • Optionalwhere?: WhereOptions

          Optional where parameter for index. Can be used to limit the index to certain rows.

      • OptionalrawTablename: string

      Returns Promise<void>

    • Create a new database schema.

      Note: We define schemas as a namespace that can contain tables. In mysql and mariadb, this command will create what they call a database.

      Parameters

      Returns Promise<void>

    • Postgres only. Creates a trigger on specified table to call the specified function with supplied parameters.

      Parameters

      Returns Promise<void>

    • Drop a single schema

      Note: We define schemas as a namespace that can contain tables. In mysql and mariadb, this command will create what they call a database.

      Parameters

      Returns Promise<void>

    • Put a name to an index

      Parameters

      • indexes: string[]
      • rawTablename: string

      Returns Promise<void>

    • Escape an identifier (e.g. a table or attribute name). If force is true, the identifier will be quoted even if the quoteIdentifiers option is false.

      Parameters

      • identifier: string
      • Optionalforce: boolean

      Returns string

    • Split an identifier into .-separated tokens and quote each part.

      Parameters

      • identifiers: string

      Returns string

    • Disables foreign key checks for the duration of the callback. The foreign key checks are only disabled for the current connection. To specify the connection, you can either use the "connection" or the "transaction" option. If you do not specify a connection, this method will reserve a connection for the duration of the callback, and release it afterwards. You will receive the connection or transaction as the first argument of the callback. You must use this connection to execute queries

      Type Parameters

      • T

      Parameters

      Returns Promise<T>

      await this.queryInterface.withoutForeignKeyChecks(options, async connection => {
        const truncateOptions = { ...options, connection };

        for (const model of models) {
          await model.truncate(truncateOptions);
        }
      });

    • Disables foreign key checks for the duration of the callback. The foreign key checks are only disabled for the current connection. To specify the connection, you can either use the "connection" or the "transaction" option. If you do not specify a connection, this method will reserve a connection for the duration of the callback, and release it afterwards. You will receive the connection or transaction as the first argument of the callback. You must use this connection to execute queries

      Type Parameters

      • T

      Parameters

      Returns Promise<T>

      await this.queryInterface.withoutForeignKeyChecks(options, async connection => {
        const truncateOptions = { ...options, connection };

        for (const model of models) {
          await model.truncate(truncateOptions);
        }
      });

    Settings
    Member Visibility

    On This Page
    Constructors
    Properties
    Accessors
    Methods