QueryInterface

QueryInterface is the imperative schema API: create and alter tables, manage
indexes, constraints, views, sequences, triggers, policies and partitions, and
run bulk data operations. It is what migrations are written
against, and it normalises the dialect differences so one migration file runs
everywhere.

const qi = prorm.getQueryInterface();

Every method takes an optional QueryInterfaceOptions:

Option	Effect
`transaction`	Run inside a transaction — thread this through a migration so a failure rolls the whole thing back.
`logging`	Per-call logging override (`false`, `true`, or a function).
`benchmark`	Report elapsed time.

Every method follows the same path: it asks a per-dialect builder for the
statement, and either sends it through the dialect's connection or — when the
engine has no such thing — throws an error that names the portable
alternative. Nothing is emulated silently, with the SQLite exceptions called
out below.

In a migration

module.exports = {
  async up(queryInterface, DataTypes) {
    await queryInterface.createTable('users', {
      id:    { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
      email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
      createdAt: { type: DataTypes.DATE(), allowNull: false },
    });
    await queryInterface.addIndex('users', ['email'], { name: 'idx_users_email', unique: true });
  },

  async down(queryInterface) {
    await queryInterface.removeIndex('users', 'idx_users_email');
    await queryInterface.dropTable('users');
  },
};

A fuller migration — the order_items join table of the shop schema, with a
cascade from its order, a restricted reference to its product, a CHECK, a
composite unique key and a lookup index:

import { DataTypes, Op } from 'ts-prorm-orm';
import type { CreateQueryInterface } from 'ts-prorm-orm';

export async function up(qi: CreateQueryInterface): Promise<void> {
  await qi.createTable('order_items', {
    id:        { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
    orderId:   {
      type: DataTypes.INTEGER(),
      allowNull: false,
      references: { model: 'orders', key: 'id', onDelete: 'CASCADE' },
    },
    productId: { type: DataTypes.INTEGER(), allowNull: false },
    quantity:  { type: DataTypes.INTEGER(), allowNull: false, defaultValue: 1 },
    unitPrice: { type: DataTypes.DECIMAL(10, 2), allowNull: false },
  });

  await qi.addConstraint('order_items', {
    type: 'FOREIGN KEY',
    name: 'fk_items_product',
    fields: ['productId'],
    references: { table: 'products', fields: ['id'] },
    onDelete: 'RESTRICT',
  });
  await qi.addConstraint('order_items', {
    type: 'CHECK',
    name: 'chk_items_quantity',
    check: { quantity: { [Op.gt]: 0 } },
  });
  await qi.addConstraint('order_items', {
    type: 'UNIQUE',
    name: 'uq_items_order_product',
    fields: ['orderId', 'productId'],
  });
  await qi.addIndex('order_items', ['productId'], { name: 'idx_items_product' });
}

export async function down(qi: CreateQueryInterface): Promise<void> {
  await qi.dropTable('order_items');
}

(The builders emit each statement on one line; they are wrapped here to read.)

Constraints on SQLite

SQLite cannot add a constraint to an existing table, so on SQLite
addConstraint() handles each type differently:

`type`	On SQLite
`'UNIQUE'`	Emulated with `CREATE UNIQUE INDEX IF NOT EXISTS`, which enforces the same rule.
`'PRIMARY KEY'`, `'FOREIGN KEY'`, `'CHECK'`	Returns without doing anything.

[!WARNING]
A FOREIGN KEY or CHECK added with addConstraint() does not exist on
SQLite, and no error says so. If SQLite is a target — including as the test
database for a PostgreSQL app — declare foreign keys in createTable()
(references, as orderId does above) and put checks in the model, so the
rule exists everywhere.

Dialect gaps are errors, not no-ops

Outside that SQLite case, a method the engine has no equivalent for throws
before sending anything, and the message names what to use instead:

Call	PostgreSQL	MySQL	SQLite	SQL Server
`createEnum('order_status', [...])`	`CREATE TYPE … AS ENUM`	throws: declare `ENUM` on the column	throws: use a `CHECK`	throws: use a `CHECK`
`createSequence('invoice_no', { start: 1000 })`	`CREATE SEQUENCE … START WITH 1000`	throws: use `AUTO_INCREMENT`	throws: use `AUTOINCREMENT`	`CREATE SEQUENCE … START WITH 1000`
`commentOnColumn('orders', 'total', '…')`	`COMMENT ON COLUMN`	throws: pass `comment` to `changeColumn()`	throws	throws
`addIndex(…, { where: { status: 'pending' } })`	partial index	throws: use a generated column	partial index	filtered index
`analyze('orders')`	`ANALYZE "orders"`	ANALYZE TABLE `orders`	`ANALYZE "orders"`	`UPDATE STATISTICS [orders]`

For example, a partial unique index — "one open order per user":

await qi.addIndex('orders', ['userId'], {
  name: 'idx_open_orders',
  unique: true,
  where: { status: 'pending' },
});

MySQL does not support partial/filtered indexes (CREATE INDEX ... WHERE ...). This is a
PostgreSQL/SQL Server/SQLite-only feature. Consider a generated column plus a regular
index, or a CHECK constraint, as a workaround.

`renameColumn` copies the column

renameColumn() does not issue RENAME COLUMN. It reads the column's
definition with describeTable(), then adds the new column, copies the data
and drops the old one:

await qi.renameColumn('orders', 'total', 'amount');

[!CAUTION]
That UPDATE rewrites every row, and whatever hung off the old column — its
indexes, constraints, comments and any view that selects it — goes with the
DROP. On a large or busy table, write the engine's own
ALTER TABLE … RENAME COLUMN through qi.query() instead, inside the
migration's transaction.

Every method

All 72 of them, grouped. Each links to its full signature in the
API reference, which
carries the full signature and every option.

Tables and databases

Method	Does
`createTable(tableName, columns, options?)`	Create a new table.
`dropTable(tableName, options?)`	Drop a table.
`dropAllTables(options?)`	Drop all tables (dialect-specific).
`renameTable(oldName, newName, options?)`	Rename a table.
`describeTable(tableName)`	Describe a table (get column information).
`tableExists(tableName)`	Check if a table exists.
`showTables()`	Show all tables in the database.
`getTableNames(schema?)`	Get all table names with schema (for PostgreSQL, MySQL, etc.).
`createDatabase(databaseName, options?)`	Create a database (for databases that support it).
`dropDatabase(databaseName, options?)`	Drop a database (for databases that support it).

Columns

Method	Does
`addColumn(tableName, columnName, definition, options?)`	Add a column to a table.
`removeColumn(tableName, columnName, options?)`	Remove a column from a table.
`changeColumn(tableName, columnName, definition, options?)`	Change a column definition.
`renameColumn(tableName, oldColumnName, newColumnName, options?)`	Rename a column.

Indexes

Method	Does
`addIndex(tableName, indexNameOrFields, fieldsOrOptions?, options?)`	Add an index to a table.
`removeIndex(tableName, indexName, options?)`	Remove an index from a table.
`showIndexes(tableName)`	Show all indexes for a table.
`indexExists(tableName, indexName)`	Check if an index exists on a table.
`createFullTextIndex(options)`	Create a full-text index.

Constraints

Method	Does
`addConstraint(tableName, constraintNameOrOptions, constraintOrOptions?, options?)`	Add a constraint to a table.
`removeConstraint(tableName, constraintName, typeOrOptions?)`	Remove a constraint from a table.
`getConstraints(tableName)`	Get constraints for a table.
`getForeignKeysForTable(tableName)`	Get foreign keys for a table.

Views

Method	Does
`createView(viewName, definition, options?)`	Create a database view.
`dropView(viewName, options?)`	Drop a database view.
`showViews()`	Show all views in the database.
`viewExists(viewName)`	Check if a view exists.
`createMaterializedView(options)`	Create a materialized view.
`dropMaterializedView(viewName, options?)`	Drop a materialized view.
`refreshMaterializedView(viewName, options?)`	Refresh a materialized view.
`showMaterializedViews()`	Show all materialized views in the database.
`materializedViewExists(viewName)`	Check if a materialized view exists.

Triggers and functions

Method	Does
`createTrigger(trigger)`	Create a trigger.
`dropTrigger(name, table, options?)`	Drop a trigger.
`createExternalFunction(options)`	Register a function written in a language other than SQL.
`dropExternalFunction(name, options?)`	Drop an external function.

Sequences

Method	Does
`createSequence(name, options)`	Create a sequence.
`dropSequence(name, options?)`	Drop a sequence.
`setSequenceValue(sequence, value, isCalled = true)`	Set a sequence's current value.
`restartSequence(sequence, value?)`	Restart a sequence, optionally at a given value.
`nextSequenceValue(sequence)`	Take the next value from a sequence, consuming it.

Extensions, types and domains

Method	Does
`createExtension(name, options?)`	Install a database extension.
`dropExtension(name, options?)`	Remove an extension.
`createEnum(name, values, options?)`	Create a named enum type.
`addEnumValue(name, value, options?)`	Add a value to an existing enum type.
`renameEnumValue(name, from, to)`	Rename one member of an enum type, leaving the rows using it alone.
`dropEnum(name, options?)`	Drop an enum type.
`createDomain(name, options)`	Create a domain — a named type carrying its own constraints.
`dropDomain(name, options?)`	Drop a domain.

Comments

Method	Does
`commentOnTable(tableName, comment)`	Comment on a table. `null` removes the comment.
`commentOnColumn(tableName, column, comment)`	Comment on a column. `null` removes the comment.

Row-level security

Method	Does
`enableRowLevelSecurity(table)`	Enable row-level security on a table (PostgreSQL family).
`disableRowLevelSecurity(table)`	Disable row-level security on a table (PostgreSQL family).
`createPolicy(options)`	Create a row-level security policy.
`dropPolicy(name, table, options?)`	Drop a row-level security policy.

Partitions

Method	Does
`createPartitionedTable(tableName, columns, options?)`	Create a partitioned table (PostgreSQL 10+).
`createPartition(options)`	Create a partition for an existing partitioned table (PostgreSQL 10+).
`attachPartition(options)`	Attach a partition to a partitioned table (PostgreSQL 11+).
`detachPartition(options)`	Detach a partition from a partitioned table (PostgreSQL 11+).
`dropPartition(partitionName, options?)`	Drop a partition (PostgreSQL 10+).

Maintenance

Method	Does
`analyze(tableName?, options?)`	Refresh the planner's statistics.
`vacuum(tableName?, options?)`	Reclaim storage occupied by dead rows.
`reindex(target, options?)`	Rebuild indexes, which drift as rows are updated and deleted.
`optimizeTable(tableName)`	Rebuild a table, defragmenting it and returning space. MySQL family.
`checkTable(tableName, level?)`	Check a table for corruption. Returns whatever the engine reports.
`repairTable(tableName, options?)`	Attempt an in-place repair of a corrupted table. MySQL family.
`checksumTable(tableName)`	A checksum over a table's contents, for comparing two copies.
`cluster(tableName, index?)`	Rewrite a table in the physical order of one index. PostgreSQL only.

Bulk data

Method	Does
`bulkInsert(tableName, records, options?)`	Bulk insert records.
`bulkUpdate(tableName, values, where, options?)`	Bulk update records.
`bulkDelete(tableName, where, options?)`	Bulk delete records.

The bulk methods are statement builders, not batchers. bulkInsert sends one
INSERT per record:

await qi.bulkInsert('products', [
  { sku: 'KB-01', name: 'Keyboard', price: 49 },
  { sku: 'MS-01', name: 'Mouse', price: 19.5 },
]);
await qi.bulkUpdate('products', { price: 45 }, { sku: 'KB-01' });
await qi.bulkDelete('products', { sku: { [Op.in]: ['OLD-1', 'OLD-2'] } });

[!TIP]
For seeding thousands of rows, pass a transaction so the inserts commit
once, or use the model-level paths in
Bulk operations.

Escape hatch

Method	Does
`query(sql, options?)`	Execute a raw query.

When to reach for `query()`

qi.quoteIdentifier('user table');   // "user table" · `user table` · [user table]
qi.getDialect();                    // the underlying Dialect

// Only for DDL with no method behind it — an engine-specific statement:
await qi.query('ALTER SYSTEM SET wal_compression = on');

query() is the way to run DDL the interface has no method for while staying
inside the migration's transaction and logging. Reach for it last: an extension
is createExtension('uuid-ossp'), an enum is createEnum(), a comment is
commentOnTable(), and a CHECK is a where object on addConstraint() —
the table above is the full list, and everything in it compiles for whichever
dialect you are connected to rather than only the one you wrote the SQL for.

`createQueryInterface`

import { createQueryInterface } from 'ts-prorm-orm';

const qi = createQueryInterface(prorm.getDialectInstance()!);

Useful when you hold a dialect but not a Prorm instance — in a test, or in
tooling built on the dialect layer.

Migrations — where this API is normally used

Schema objects

Indexes & constraints

Schema diffing — generating these calls from a model diff

In a migration#

Constraints on SQLite#

Dialect gaps are errors, not no-ops#

renameColumn copies the column#

Every method#

Tables and databases#

Columns#

Indexes#

Constraints#

Views#

Triggers and functions#

Sequences#

Extensions, types and domains#

Comments#

Row-level security#

Partitions#

Maintenance#

Bulk data#

Escape hatch#

When to reach for query()#

createQueryInterface#

Related reading#