Query Runner API

In order to use an API to change a database schema you can use QueryRunner.

import {
    MigrationInterface,
    QueryRunner,
    Table,
    TableIndex,
    TableColumn,
    TableForeignKey,
} from "typeorm"

export class QuestionRefactoringTIMESTAMP implements MigrationInterface {
    async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(
            new Table({
                name: "question",
                columns: [
                    {
                        name: "id",
                        type: "int",
                        isPrimary: true,
                    },
                    {
                        name: "name",
                        type: "varchar",
                    },
                ],
            }),
            true,
        )

        await queryRunner.createIndex(
            "question",
            new TableIndex({
                name: "IDX_QUESTION_NAME",
                columnNames: ["name"],
            }),
        )

        await queryRunner.createTable(
            new Table({
                name: "answer",
                columns: [
                    {
                        name: "id",
                        type: "int",
                        isPrimary: true,
                    },
                    {
                        name: "name",
                        type: "varchar",
                    },
                    {
                        name: "created_at",
                        type: "timestamp",
                        default: "now()",
                    },
                ],
            }),
            true,
        )

        await queryRunner.addColumn(
            "answer",
            new TableColumn({
                name: "questionId",
                type: "int",
            }),
        )

        await queryRunner.createForeignKey(
            "answer",
            new TableForeignKey({
                columnNames: ["questionId"],
                referencedColumnNames: ["id"],
                referencedTableName: "question",
                onDelete: "CASCADE",
            }),
        )
    }

    async down(queryRunner: QueryRunner): Promise<void> {
        const table = await queryRunner.getTable("answer")
        const foreignKey = table.foreignKeys.find(
            (fk) => fk.columnNames.indexOf("questionId") !== -1,
        )
        await queryRunner.dropForeignKey("answer", foreignKey)
        await queryRunner.dropColumn("answer", "questionId")
        await queryRunner.dropTable("answer")
        await queryRunner.dropIndex("question", "IDX_QUESTION_NAME")
        await queryRunner.dropTable("question")
    }
}

getDatabases(): Promise<string[]>

Returns all available database names including system databases.

getSchemas(database?: string): Promise<string[]>

database - If database parameter specified, returns schemas of that database

Returns all available schema names including system schemas. Useful for SQLServer and Postgres only.

getTable(tableName: string): Promise<Table|undefined>

tableName - name of a table to be loaded

Loads a table by a given name from the database.

getTables(tableNames: string[]): Promise<Table[]>

tableNames - name of a tables to be loaded

Loads a tables by a given names from the database.

hasDatabase(database: string): Promise<boolean>

database - name of a database to be checked

Checks if database with the given name exist.

hasSchema(schema: string): Promise<boolean>

schema - name of a schema to be checked

Checks if schema with the given name exist. Used only for SqlServer and Postgres.

hasTable(table: Table|string): Promise<boolean>

table - Table object or name

Checks if table exist.

hasColumn(table: Table|string, columnName: string): Promise<boolean>

table - Table object or name
columnName - name of a column to be checked

Checks if column exist in the table.

createDatabase(database: string, ifNotExists?: boolean): Promise<void>

database - database name
ifNotExists - when set to true, silently ignores if the database already exists; otherwise throws an error (default)

Creates a new database.

dropDatabase(database: string, ifExists?: boolean): Promise<void>

database - database name
ifExists - when set to true, silently ignores if the database does not exist; otherwise throws an error (default)

Drops database.

createSchema(schemaPath: string, ifNotExists?: boolean): Promise<void>

schemaPath - schema name. For SqlServer can accept schema path (e.g. 'dbName.schemaName') as parameter. If schema path passed, it will create schema in specified database
ifNotExists - when set to true, silently ignores if the schema already exists; otherwise throws an error (default)

Creates a new table schema.

dropSchema(schemaPath: string, ifExists?: boolean, isCascade?: boolean): Promise<void>

schemaPath - schema name. For SqlServer can accept schema path (e.g. 'dbName.schemaName') as parameter. If schema path passed, it will drop schema in specified database
ifExists - when set to true, silently ignores if the schema does not exist; otherwise throws an error (default)
isCascade - If true, automatically drop objects (tables, functions, etc.) that are contained in the schema. Used only in Postgres.

Drops a table schema.

createTable(table: Table, ifNotExists?: boolean, createForeignKeys?: boolean, createIndices?: boolean): Promise<void>

table - Table object.
ifNotExists - when set to true, silently ignores if the table already exists; otherwise throws an error (default)
createForeignKeys - indicates whether foreign keys will be created on table creation. Default true
createIndices - indicates whether indexes will be created on table creation. Default true

Creates a new table.

dropTable(table: Table|string, ifExists?: boolean, dropForeignKeys?: boolean, dropIndices?: boolean): Promise<void>

table - Table object or table name to be dropped
ifExists - when set to true, silently ignores if the table does not exist; otherwise throws an error (default)
dropForeignKeys - indicates whether foreign keys will be dropped on table deletion. Default true
dropIndices - indicates whether indexes will be dropped on table deletion. Default true

Drops a table.

createView(view: View, syncWithMetadata?: boolean, oldView?: View): Promise<void>

view - View object
syncWithMetadata - indicates whether to sync view with metadata (optional)
oldView - old View object to be replaced (optional)

Creates a new view.

dropView(view: View|string, ifExists?: boolean): Promise<void>

view - View object or view name to be dropped
ifExists - when set to true, silently ignores if the view does not exist; otherwise throws an error (default)

Drops a view.

renameTable(oldTableOrName: Table|string, newTableName: string): Promise<void>

oldTableOrName - old Table object or name to be renamed
newTableName - new table name

Renames a table.

addColumn(table: Table|string, column: TableColumn): Promise<void>

table - Table object or name
column - new column

Adds a new column.

addColumns(table: Table|string, columns: TableColumn[]): Promise<void>

table - Table object or name
columns - new columns

Adds a new column.

renameColumn(table: Table|string, oldColumnOrName: TableColumn|string, newColumnOrName: TableColumn|string): Promise<void>

table - Table object or name
oldColumnOrName - old column. Accepts TableColumn object or column name
newColumnOrName - new column. Accepts TableColumn object or column name

Renames a column.

changeColumn(table: Table|string, oldColumn: TableColumn|string, newColumn: TableColumn): Promise<void>

table - Table object or name
oldColumn - old column. Accepts TableColumn object or column name
newColumn - new column. Accepts TableColumn object

Changes a column in the table.

changeColumns(table: Table|string, changedColumns: { oldColumn: TableColumn, newColumn: TableColumn }[]): Promise<void>

table - Table object or name
changedColumns - array of changed columns.
- oldColumn - old TableColumn object
- newColumn - new TableColumn object

Changes a columns in the table.

dropColumn(table: Table|string, column: TableColumn|string, ifExists?: boolean): Promise<void>

table - Table object or name
column - TableColumn object or column name to be dropped
ifExists - when set to true, silently ignores if the column does not exist; otherwise throws an error (default)

Drops a column in the table.

dropColumns(table: Table|string, columns: TableColumn[]|string[], ifExists?: boolean): Promise<void>

table - Table object or name
columns - array of TableColumn objects or column names to be dropped
ifExists - when set to true, silently ignores if the columns do not exist; otherwise throws an error (default)

Drops columns in the table.

createPrimaryKey(table: Table|string, columnNames: string[]): Promise<void>

table - Table object or name
columnNames - array of column names which will be primary

Creates a new primary key.

updatePrimaryKeys(table: Table|string, columns: TableColumn[]): Promise<void>

table - Table object or name
columns - array of TableColumn objects which will be updated

Updates composite primary keys.

dropPrimaryKey(table: Table|string, constraintName?: string, ifExists?: boolean): Promise<void>

table - Table object or name
constraintName - constraint name (optional)
ifExists - when set to true, silently ignores if the primary key does not exist; otherwise throws an error (default)

Drops a primary key.

createUniqueConstraint(table: Table|string, uniqueConstraint: TableUnique): Promise<void>

table - Table object or name
uniqueConstraint - TableUnique object to be created

Creates new unique constraint.

Note: does not work for MySQL, because MySQL stores unique constraints as unique indexes. Use createIndex() method instead.

createUniqueConstraints(table: Table|string, uniqueConstraints: TableUnique[]): Promise<void>

table - Table object or name
uniqueConstraints - array of TableUnique objects to be created

Creates new unique constraints.

Note: does not work for MySQL, because MySQL stores unique constraints as unique indexes. Use createIndices() method instead.

dropUniqueConstraint(table: Table|string, uniqueOrName: TableUnique|string, ifExists?: boolean): Promise<void>

table - Table object or name
uniqueOrName - TableUnique object or unique constraint name to be dropped
ifExists - when set to true, silently ignores if the constraint does not exist; otherwise throws an error (default)

Drops a unique constraint.

Note: does not work for MySQL, because MySQL stores unique constraints as unique indexes. Use dropIndex() method instead.

dropUniqueConstraints(table: Table|string, uniqueConstraints: TableUnique[], ifExists?: boolean): Promise<void>

table - Table object or name
uniqueConstraints - array of TableUnique objects to be dropped
ifExists - when set to true, silently ignores if the constraints do not exist; otherwise throws an error (default)

Drops unique constraints.

Note: does not work for MySQL, because MySQL stores unique constraints as unique indexes. Use dropIndices() method instead.

createCheckConstraint(table: Table|string, checkConstraint: TableCheck): Promise<void>

table - Table object or name
checkConstraint - TableCheck object

Creates a new check constraint.

Note: MySQL does not support check constraints.

createCheckConstraints(table: Table|string, checkConstraints: TableCheck[]): Promise<void>

table - Table object or name
checkConstraints - array of TableCheck objects

Creates a new check constraint.

Note: MySQL does not support check constraints.

dropCheckConstraint(table: Table|string, checkOrName: TableCheck|string, ifExists?: boolean): Promise<void>

table - Table object or name
checkOrName - TableCheck object or check constraint name
ifExists - when set to true, silently ignores if the constraint does not exist; otherwise throws an error (default)

Drops a check constraint.

Note: MySQL does not support check constraints.

dropCheckConstraints(table: Table|string, checkConstraints: TableCheck[], ifExists?: boolean): Promise<void>

table - Table object or name
checkConstraints - array of TableCheck objects
ifExists - when set to true, silently ignores if the constraints do not exist; otherwise throws an error (default)

Drops check constraints.

Note: MySQL does not support check constraints.

createExclusionConstraint(table: Table|string, exclusionConstraint: TableExclusion): Promise<void>

table - Table object or name
exclusionConstraint - TableExclusion object

Creates a new exclusion constraint.

Note: only PostgreSQL supports exclusion constraints.

createExclusionConstraints(table: Table|string, exclusionConstraints: TableExclusion[]): Promise<void>

table - Table object or name
exclusionConstraints - array of TableExclusion objects

Creates new exclusion constraints.

Note: only PostgreSQL supports exclusion constraints.

dropExclusionConstraint(table: Table|string, exclusionOrName: TableExclusion|string, ifExists?: boolean): Promise<void>

table - Table object or name
exclusionOrName - TableExclusion object or exclusion constraint name
ifExists - when set to true, silently ignores if the constraint does not exist; otherwise throws an error (default)

Drops an exclusion constraint.

Note: only PostgreSQL supports exclusion constraints.

dropExclusionConstraints(table: Table|string, exclusionConstraints: TableExclusion[], ifExists?: boolean): Promise<void>

table - Table object or name
exclusionConstraints - array of TableExclusion objects
ifExists - when set to true, silently ignores if the constraints do not exist; otherwise throws an error (default)

Drops exclusion constraints.

Note: only PostgreSQL supports exclusion constraints.

createForeignKey(table: Table|string, foreignKey: TableForeignKey): Promise<void>

table - Table object or name
foreignKey - TableForeignKey object

Creates a new foreign key.

createForeignKeys(table: Table|string, foreignKeys: TableForeignKey[]): Promise<void>

table - Table object or name
foreignKeys - array of TableForeignKey objects

Creates a new foreign keys.

dropForeignKey(table: Table|string, foreignKeyOrName: TableForeignKey|string, ifExists?: boolean): Promise<void>

table - Table object or name
foreignKeyOrName - TableForeignKey object or foreign key name
ifExists - when set to true, silently ignores if the foreign key does not exist; otherwise throws an error (default)

Drops a foreign key.

dropForeignKeys(table: Table|string, foreignKeys: TableForeignKey[], ifExists?: boolean): Promise<void>

table - Table object or name
foreignKeys - array of TableForeignKey objects
ifExists - when set to true, silently ignores if the foreign keys do not exist; otherwise throws an error (default)

Drops foreign keys.

createIndex(table: Table|string, index: TableIndex): Promise<void>

table - Table object or name
index - TableIndex object

Creates a new index.

createIndices(table: Table|string, indices: TableIndex[]): Promise<void>

table - Table object or name
indices - array of TableIndex objects

Creates new indexes.

dropIndex(table: Table|string, index: TableIndex|string, ifExists?: boolean): Promise<void>

table - Table object or name
index - TableIndex object or index name
ifExists - when set to true, silently ignores if the index does not exist; otherwise throws an error (default)

Drops an index.

dropIndices(table: Table|string, indices: TableIndex[], ifExists?: boolean): Promise<void>

table - Table object or name
indices - array of TableIndex objects
ifExists - when set to true, silently ignores if the indexes do not exist; otherwise throws an error (default)

Drops indexes.

clearTable(tableName: string, options?: {cascade?: boolean}): Promise<void>

tableName - table name
options - additional options
- cascade - Indicates whether to clear rows of tables that have foreign keys (supported by PostgreSQL/CockroachDB and Oracle only; other databases throw an error if set to true). Default false

Clears all table contents.

Note: this operation uses SQL's TRUNCATE query which cannot be reverted in transactions.

enableSqlMemory(): void

Enables special query runner mode in which sql queries won't be executed, instead they will be memorized into a special variable inside query runner. You can get memorized sql using getMemorySql() method.

disableSqlMemory(): void

Disables special query runner mode in which sql queries won't be executed. Previously memorized sql will be flushed.

clearSqlMemory(): void

Flushes all memorized sql statements.

getMemorySql(): SqlInMemory

returns SqlInMemory object with array of upQueries and downQueries sql statements

Gets sql stored in the memory. Parameters in the sql are already replaced.

executeMemoryUpSql(): Promise<void>

Executes memorized up sql queries.

executeMemoryDownSql(): Promise<void>

Executes memorized down sql queries.

PreviousFaking Migrations and Rollbacks NextExtra options

Last updated 4 months ago