Database
createDatabase
Type
declare function createDatabase(
context?: DatabaseCreateContextInput
): Promise<unknown>;Create a database.
Example: Simple
// Only create database if it does not exist.
await createDatabase({ ifNotExist: true });Example: Synchronization
// To not synchronize database schema after database creation.
await createDatabase({ synchronize: false });Example: Charset
It is possible to explizit specify charset & characterSet, besides defining it as part of the DataSourceOptions object. E.g.
- postgrestypescript
await createDatabase({ characterSet: "UTF8" }); - mysqltypescriptA collation (
await createDatabase({ charset: "utf8mb4_general_ci", characterSet: "utf8mb4" });utf8mb4_general_ci) yieldsCHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci. A plain character set name (utf8mb4) yieldsCHARACTER SET utf8mb4only, so the server picks that set's default collation.
Example: DataSourceOptions
The typeorm DataSourceOptions object can be provided as options property to the createDatabase method.
import { DataSourceOptions } from 'typeorm';
import { createDatabase } from 'typeorm-extension';
(async () => {
const options : DataSourceOptions = {
//...
};
// Create database
await createDatabase({
options
});
})();if entities & subscribers are defined as path, it is possible to use them with e.g. ts-node as well as with the compiled version. Therefore, extend the DataSourceOptions with the extendDataSourceOptions utility method.
import { DataSourceOptions } from 'typeorm';
import { createDatabase, extendDataSourceOptions } from 'typeorm-extension';
(async () => {
let options : DataSourceOptions = {
// ...
}
options = await extendDataSourceOptions(options);
// Create database
await createDatabase({
options
});
})();This is achieved by rewriting the src path and .ts extension, to the dist (outDir) path and .js extension.
If the function is not called within a just-in-time runtime environment (ts-node and tsx are detected automatically), the source path src/database/entities.ts for example, will be rewritten to dist/database/entities.js. If the rewritten path does not exist but the source path does (and the runtime can load it), the source path is kept. Node's type stripping cannot load decorated classes, so entities with @Entity() still need tsx or a build.
Parameters
| Name | Type | Description |
|---|---|---|
context | DatabaseCreateContext | Specify charset, coalition and DataSourceOptions. Details |
Returns
Promise<unknown>
The function returns a promise with the query result of the underlying database driver.
References
dropDatabase
declare function dropDatabase(
context?: DatabaseDropContextInput
): Promise<unknown>;Drop a database.
Example: Simple
import { dropDatabase } from 'typeorm-extension';
(async () => {
await dropDatabase();
})();Example: DataSourceOptions
The typeorm DataSourceOptions object can be provided as options property to the dropDatabase method.
import { DataSourceOptions } from 'typeorm';
import { dropDatabase } from 'typeorm-extension';
(async () => {
const options : DataSourceOptions = {
//...
};
// Drop database
await dropDatabase({
options
});
})();Parameters
| Name | Type | Description |
|---|---|---|
context | DatabaseDropContext | Specify charset, coalition and DataSourceOptions. Details |
Returns
Promise<unknown>
The function returns a promise with the query result of the underlying database driver.
References
checkDatabase
declare function checkDatabase(
context?: DatabaseCheckContext
): Promise<DatabaseCheckResult>;Check whether the database exists and whether its schema is up to date. The schema is never synchronized, migrated or dropped.
Example: DataSourceOptions
import { DataSourceOptions } from 'typeorm';
import { checkDatabase } from 'typeorm-extension';
(async () => {
const options : DataSourceOptions = {
//...
};
const { exists, schema, migrationsPending } = await checkDatabase({
options
});
})();Parameters
| Name | Type | Description |
|---|---|---|
context | DatabaseCheckContext | Specify a DataSource, DataSourceOptions or a registry alias. Details |
Returns
Promise<DatabaseCheckResult>
A missing database is reported as exists: false, on cockroachdb as well, and so is any failure to connect (also wrong credentials or an unreachable host). On mongodb, exists is true once the connection succeeds (a database is created lazily there), and schema is true when no migrations are configured, because there is no schema to compare.
A dataSource in the context takes precedence over options and alias. It and a data source registered under the alias follow the same rule: an initialized one is used as is and never destroyed, an uninitialized one is left untouched and the check runs on a copy. A copy, like any data source built from options, never synchronizes, runs migrations or drops the schema, and on postgres it installs no extensions.
References
getSchemaDrift
Compare the database schema against the entity metadata and return the statements which would reconcile them. The inspection never writes to the database.
declare function getSchemaDrift(
input: DataSource | DataSourceOptions,
options?: SchemaDriftOptions,
): Promise<SchemaDrift>;Example
import { getSchemaDrift } from 'typeorm-extension';
const drift = await getSchemaDrift(dataSource);
if (drift.exists) {
console.log(drift.up.map((statement) => statement.query));
}A data source built from the passed options is built with synchronize, migrationsRun and dropSchema disabled and destroyed afterwards. An existing DataSource is initialized as is (its own options still apply) and never destroyed.
References
assertSchemaMatchesMetadata
The assertion form of getSchemaDrift.
declare function assertSchemaMatchesMetadata(
input: DataSource | DataSourceOptions,
options?: SchemaDriftOptions,
): Promise<void>;Throws a SchemaDriftError whose message lists the reconciling statements and whose statements property carries them.
generateMigration
Generate a migration file from the difference between the database schema and the entity metadata, using the same comparison and the same file templates as typeorm migration:generate.
declare function generateMigration(
context: MigrationGenerateCommandContext,
): Promise<MigrationGenerateResult>;Example
import { generateMigration } from 'typeorm-extension';
await generateMigration({
dataSource,
name: 'add-role',
directoryPath: 'src/migrations',
});The data source must already be initialized, and it is not destroyed afterwards. The file is written as <timestamp>-<name>.<language> inside directoryPath (migrations/ relative to the working directory by default). When there is nothing to generate, no file is written and content is undefined. Pass preview: true to receive the statements without touching the file system.
References
renameIndex
Rename an index. Returns false when an index is already named to. When neither name exists (or the table does not exist), a SchemaAlterationError is raised unless strict: false is passed.
declare function renameIndex(
queryRunner: QueryRunner,
input: SchemaRenameIndexInput,
): Promise<boolean>;Supported for postgres, cockroachdb, mysql and mariadb; throws a DriverError for any other driver.
An index which backs a constraint is not reported as an index by the driver and is therefore not seen by this function. On mysql that is a foreign key's backing index, which only becomes visible once the constraint is dropped; it is handled for you by renameForeignKey. On postgres it is a unique constraint, which lives in table.uniques: renaming one is not covered by these helpers and needs a raw ALTER TABLE … RENAME CONSTRAINT.
renameForeignKey
Rename a foreign key constraint, preserving its columns, its referenced table/columns and its referential actions, all of which are read back from the database.
declare function renameForeignKey(
queryRunner: QueryRunner,
input: SchemaRenameForeignKeyInput,
): Promise<boolean>;postgres renames the constraint in place. mysql has no RENAME CONSTRAINT, so the constraint is dropped and re-added inside withForeignKeyChecksDisabled, and the backing index mysql may have created under the constraint name is renamed (or dropped, if the target name is already taken) in between.
Returns false when a constraint is already named to. When neither name exists and no meta describes the constraint (or the table does not exist), a SchemaAlterationError is raised unless strict: false is passed. Supported for postgres, cockroachdb, mysql and mariadb; throws a DriverError otherwise.
If neither name exists (what a mysql run interrupted between the drop and the re-add leaves behind, where the constraint took its own description with it), the optional meta is used to restore it:
await renameForeignKey(queryRunner, {
table: 'auth_permissions',
from: 'FK_old',
to: 'FK_new',
// only consulted when neither FK_old nor FK_new is present
meta: {
columns: ['client_id'],
referencedTable: 'auth_clients',
referencedColumns: ['id'],
onDelete: 'CASCADE',
},
});While from still exists meta is ignored and the description is read from the database instead, so the normal path can not silently change the constraint. Without meta the interrupted state stays a no-op.
changeColumnType
Change the type (and optionally the nullability) of a column, but only if it still matches the from description.
declare function changeColumnType(
queryRunner: QueryRunner,
input: SchemaChangeColumnTypeInput,
): Promise<boolean>;Returns false when the column already matches to. When it matches neither description (or the table/column does not exist), a SchemaAlterationError is raised unless strict: false is passed.
The column is altered in place, so it keeps the values it holds: ALTER COLUMN … TYPE on postgres/cockroachdb, MODIFY COLUMN on mysql/mariadb, ALTER COLUMN on mssql and MODIFY on oracle. That is the reason the statement is built here: typeorm's changeColumn drops and re-adds the column as soon as the type or the length differs, which empties it (and fails outright once the table holds a row it can not re-add as NOT NULL). Only sqlite still goes through typeorm, which recreates the table and copies the values over.
Since mysql replaces the column definition in full, every attribute of the column (default, comment, charset, collation, UNSIGNED, AUTO_INCREMENT, ON UPDATE, the values of an enum) is restated from the description the database reports back, not from the passed input.
postgres converts the values with the assignment cast between the two types and refuses a change which has none (text to integer, for example). Pass using (raw SQL computing the new value from the old one) for those:
await changeColumnType(queryRunner, {
table: 'auth_permissions',
column: 'client_id',
from: { type: 'varchar', length: 36 },
to: { type: 'uuid' },
using: '"client_id"::uuid',
});using is postgres/cockroachdb only. On any other dialect it raises a DriverError rather than being dropped, since ignoring it would leave the server to coerce the values on its own terms, which is what passing it was meant to avoid.
Two mysql caveats follow from that:
- A generated column can only be restated together with its expression, which typeorm reads from its own
typeorm_metadatatable. When that row is missing the expression comes back empty, and the helper throws aDriverErrorinstead of altering the column into a regular one. ZEROFILLis not preserved: typeorm does not read it intoTableColumnat all, so there is nothing to restate. It is a display attribute mysql deprecated in 8.0.17, and typeorm's own statements drop it just the same.
Guard semantics
Every guarded helper takes strict (default true):
| Database state | strict: true (default) | strict: false |
|---|---|---|
| Change is pending | applies it, returns true | applies it, returns true |
| Already in the desired state | returns false | returns false |
| In neither state (wrong name, wrong type, missing table/column) | throws SchemaAlterationError | returns false |
Being already in the desired state is never an error; that is what keeps a repair migration resumable. The strict mode only covers the third row, where the migration's description of the database is wrong and returning false would let a repair which never happened pass for a successful one.
NOTE
Every helper reads the table back with queryRunner.getTable(). For a table containing a generated column typeorm resolves the generation expression from its own typeorm_metadata table, so on a database where that table is absent the call fails with relation "typeorm_metadata" does not exist before the helper does anything.
withForeignKeyChecksDisabled
Run a callback with the mysql/mariadb foreign key checks disabled and restore the previous state afterwards.
declare function withForeignKeyChecksDisabled<T>(
queryRunner: QueryRunner,
fn: () => Promise<T>,
): Promise<T>;It reads @@SESSION.foreign_key_checks first and only restores it if it was enabled, so nesting is safe. On a driver without a session-level switch it is a transparent no-op wrapper, which keeps a migration using it portable.
withDatabaseLock
Run a callback while holding a named advisory lock, so only one session at a time (across processes) runs it.
declare function withDatabaseLock<T>(
queryRunner: QueryRunner,
name: string,
fn: () => Promise<T>,
options?: DatabaseLockOptions,
): Promise<T>;The lock is session scoped: the query runner must stay on one connection for the whole call, which a typeorm QueryRunner does until it is released. It is released afterwards, also when fn throws. A transaction fn leaves open on the query runner is rolled back first. Nested calls with the same name on the same query runner do not block.
If the release statement fails (for example postgres refuses it in a transaction a failed rollback left aborted), the session is ended instead of going back to the pool, which frees the lock. The query runner is released then, so a later queryRunner.release() does nothing, and it can not run further queries. Ending the session frees the locks of enclosing calls on it as well, so a callback which catches the error of a nested call and carries on runs without the lock.
The lock is taken with pg_try_advisory_lock (postgres) or GET_LOCK (mysql, mariadb) and retried every 100ms until timeout has passed. On mysql and mariadb the name is namespaced with the current database, so each database has its own locks.
Throws
DriverErrorfor any driver other thanpostgres,mysqlandmariadb(cockroachdb accepts the advisory lock functions, but they do not lock anything), unlessstrictisfalse: thenfnruns without a lock.DatabaseLockErrorif the lock can not be acquired withintimeout, if the query runner is in a transaction (the lock would outlive a rollback), or iffnreturned with a transaction left open on it.DatabaseLockError(releaseFailed, with the release error ascause) iffnreturned but the release failed. It takes precedence over a transaction left open. Iffnthrew, its error is rethrown instead.
withLockedEntity
Read one row with a write lock inside a transaction and hand it to a callback, so concurrent callers for the same row take turns.
declare function withLockedEntity<T extends ObjectLiteral, R>(
source: DataSource | EntityManager,
target: EntityTarget<T>,
where: FindOptionsWhere<T>,
fn: (entity: T, manager: EntityManager) => R | Promise<R>,
options?: EntityLockOptions,
): Promise<Awaited<R> | null>;The row is read with findOne and lock: { mode: 'pessimistic_write' }. Relations (eager ones included) are loaded in separate queries (relationLoadStrategy: 'query') and are not locked. fn receives the locked entity and the transaction's manager and writes through it; the helper saves nothing. It resolves what fn returns, or null without calling fn when no row matches. A throw rolls the transaction back. An EntityManager which is already in a transaction is reused (no savepoint), so a call inside fn locks a second row within the same transaction.
With onLocked: 'nowait' a locked row fails the call at once with the driver's error, which isDatabaseLockConflictError recognizes.
Throws
DriverErrorfor any driver other thanpostgres,cockroachdb,mysql,mariadb,oracleandmssql, before a transaction is opened, unlessstrictisfalse: thenfnruns without a lock (on mongodb without a transaction either).DriverErrorforonLockedon mssql, which typeorm would ignore.OptionsErrorwhenwherelooks the row up through a relation (a nested object such as{ owner: { id } }): the join would lock the related rows as well. A find operator on the relation ({ owner: Equal(id) },IsNull()) compares the join column and is accepted.
isRowLockSupported
declare function isRowLockSupported(
source: DataSource | EntityManager | DataSourceOptions['type'],
): boolean;True when typeorm emits a row lock (pessimistic_write) for the driver: postgres, cockroachdb, mysql, mariadb, oracle and mssql. It never throws. withLockedEntity decides by the same check, so a repository adapter building its own locked read can skip the lock find option where typeorm would throw LockNotSupportedOnGivenDriverError. On mssql the lock holds, but typeorm ignores onLocked.
isDatabaseUniqueViolationError
declare function isDatabaseUniqueViolationError(error: unknown): boolean;True when the error is a unique constraint (duplicate key) violation, a primary key included. Two writers inserting the same key at once end like this, and the loser usually wants to answer a conflict.
isDatabaseForeignKeyViolationError
declare function isDatabaseForeignKeyViolationError(error: unknown): boolean;True when the error is a foreign key violation, in either direction: a row references a missing one, or a row which is still referenced is deleted. On mssql, where error 547 covers CHECK constraints as well, the message decides. The same holds for better-sqlite3, which reports an ON DELETE / ON UPDATE RESTRICT key as a trigger failure, the code a trigger of your own raises as well.
isDatabaseLockConflictError
declare function isDatabaseLockConflictError(error: unknown): boolean;True when the database gave up on the transaction because of lock contention: a deadlock, a serialization failure or a lock wait timeout. Re-running the whole transaction is the usual answer, and it is only sound when the transaction derives its writes from reads made inside it. A NOWAIT lock request which finds the row locked is recognized too.
Not to be confused with DatabaseLockError, which withDatabaseLock throws when its advisory lock can not be acquired: this guard classifies an error the driver raised.
All three take unknown, so they can be called in a catch block as they are, and accept the QueryFailedError typeorm throws as well as the raw driver error. The codes they recognize:
| Driver | Unique violation | Foreign key violation | Lock conflict |
|---|---|---|---|
| postgres, cockroachdb | 23505 | 23503 | 40P01, 40001, 55P03 |
| mysql, mariadb | ER_DUP_ENTRY | ER_NO_REFERENCED_ROW(_2), ER_ROW_IS_REFERENCED(_2) | ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_LOCK_NOWAIT |
| better-sqlite3 | SQLITE_CONSTRAINT_UNIQUE, SQLITE_CONSTRAINT_PRIMARYKEY | SQLITE_CONSTRAINT_FOREIGNKEY, SQLITE_CONSTRAINT_TRIGGER with the message FOREIGN KEY constraint failed | not covered |
mssql (number) | 2627, 2601 | 547 naming a FOREIGN KEY or REFERENCE constraint | 1205, 1222 |
| oracle | ORA-00001 | ORA-02291, ORA-02292 | ORA-00060, ORA-08177, ORA-00054 |
| mongodb | 11000, 11001 | not applicable | not covered |
readDatabaseErrorCode
declare function readDatabaseErrorCode(error: unknown): string | undefined;The vendor code of a failed query as a plain token, e.g. to log which driver error happened: 23505, ER_DUP_ENTRY, SQLITE_CONSTRAINT_UNIQUE, ORA-00001, or a number as a string (mssql 2627, mongodb 11000). It reads the same fields as the three checks above, mssql's number before its generic EREQUEST code. A value which is not a short plain token (at most 64 letters, digits, _ or -) is skipped, so a message, which may quote stored values, is never returned. undefined when the error carries no such code.
mysql2 names a mysql or mariadb error after MySQL's error table. An error only mariadb has therefore comes back as its number (1927), and a number both servers use for different errors carries MySQL's name.
DatabaseLockOptions
export type DatabaseLockOptions = {
/**
* How long to wait for the lock, in milliseconds.
* 0 tries once, unset waits until the lock is free.
*/
timeout?: number,
/**
* Throw a DriverError on a driver which has no lock. Set to false to run
* the callback without a lock there instead (e.g. sqlite in tests).
*
* default: true
*/
strict?: boolean,
};EntityLockOptions
export type EntityLockOptions = {
/**
* 'nowait' fails at once with the driver's error (see
* isDatabaseLockConflictError) instead of waiting for a locked row.
* Not supported on mssql.
*
* default: wait until the row is free
*/
onLocked?: 'nowait',
/**
* Throw a DriverError on a driver which has no row lock. Set to false to
* run the callback without a lock there instead (e.g. sqlite in tests).
*
* default: true
*/
strict?: boolean,
};SchemaDrift
export type SchemaDriftStatement = {
query: string,
parameters?: unknown[]
};
export type SchemaDrift = {
/**
* Whether the database schema deviates from the entity metadata.
*/
exists: boolean,
up: SchemaDriftStatement[],
down: SchemaDriftStatement[]
};SchemaDriftOptions
export type SchemaDriftOptions = {
/**
* Report no drift if the data source has no migrations registered.
*
* default: false
*/
skipWithoutMigrations?: boolean
};SchemaRenameIndexInput
export type SchemaRenameIndexInput = {
/**
* Table name, optionally schema qualified (e.g. `public.user`).
*/
table: string,
from: string,
to: string
};SchemaRenameForeignKeyInput
export type SchemaForeignKeyMeta = {
columns: string[],
referencedTable: string,
referencedColumns: string[],
onDelete?: string,
onUpdate?: string
};
export type SchemaRenameForeignKeyInput = {
/**
* Table name, optionally schema qualified (e.g. `public.user`).
*/
table: string,
from: string,
to: string,
/**
* Only consulted when neither `from` nor `to` exists.
*/
meta?: SchemaForeignKeyMeta
};SchemaChangeColumnTypeInput
export type SchemaColumnType = {
type: string,
length?: string | number,
/**
* Only compared/applied if defined.
*/
nullable?: boolean
};
export type SchemaChangeColumnTypeInput = {
/**
* Table name, optionally schema qualified (e.g. `public.user`).
*/
table: string,
column: string,
/**
* The column type expected to be present. The helper is a no-op if the
* column does not match it (e.g. because it is already migrated).
*/
from: SchemaColumnType,
to: SchemaColumnType
};DatabaseBaseContext
import { DataSourceOptions } from 'typeorm';
import { DataSourceFindOptions } from 'typeorm-extension';
export type DatabaseBaseContext = {
/**
* Options for finding the typeorm DataSource.
*
* Default: undefined
*/
options?: DataSourceOptions,
/**
* Options for the find method, where to look for the data-source file.
*/
findOptions?: DataSourceFindOptions
};References
DatabaseCreateContext
import { DatabaseBaseContext } from 'typeorm-extension';
export type DatabaseCreateContext = DatabaseBaseContext & {
/**
* Only create database if not already exist.
*
* default: true
*/
ifNotExist?: boolean,
/**
* Initial database to connect.
*
* default: undefined
*/
initialDatabase?: string,
/**
* Synchronize database entities.
*
* default: true
*/
synchronize?: boolean
};createDatabase() accepts DatabaseCreateContextInput: this type with every property optional, plus findOptions. The resolved context passed on to the driver no longer carries findOptions, because it is consumed while the data source is discovered.
References
DatabaseDropContext
import { DatabaseBaseContext } from 'typeorm-extension';
export type DatabaseDropContext = DatabaseBaseContext & {
/**
* Only drop database if existed.
*
* Default: true
*/
ifExist?: boolean
};dropDatabase() accepts DatabaseDropContextInput, which follows the same rule as the create variant above.
References
DatabaseCheckContext
import { DataSource, DataSourceOptions } from 'typeorm';
export type DatabaseCheckContext = {
/**
* Options for finding the typeorm DataSource.
*
* Default: undefined
*/
options?: DataSourceOptions,
/**
* Use alias to access already registered DataSource / DataSourceOptions.
* Without options, a DataSource registered under the alias is
* handled like a passed dataSource. Otherwise the options
* registered or discovered for the alias are used.
*
* default: undefined
*/
alias?: string,
/**
* Indicates whether to destroy the data-source
* built from options afterward or not.
* Ignored for a passed or registered data source:
* an initialized one is never destroyed,
* a copy of an uninitialized one always is.
*
* default: true
*/
dataSourceCleanup?: boolean,
/**
* Use predefined data-source for checks.
* It takes precedence over options and alias.
* An initialized one is used as is and never destroyed.
* An uninitialized one is left untouched and the check runs on a copy.
* A copy never synchronizes, runs migrations or drops the schema.
*
* default: undefined
*/
dataSource?: DataSource
};DatabaseCheckResult
import { Migration } from 'typeorm';
export type DatabaseCheckResult = {
/**
* Indicates whether the database
* has already been created or not.
*/
exists: boolean,
/**
* Indicates whether the database's schema
* is up-to-date.
*/
schema: boolean,
/**
* Array of un applied migrations.
*/
migrationsPending: Migration[]
};MigrationGenerateCommandContext
import { DataSource } from 'typeorm';
export type MigrationGenerateCommandContext = {
/**
* Directory where the migration(s) should be stored.
*
* Default: 'migrations'
*/
directoryPath?: string,
/**
* Name of the migration class.
*
* Default: 'Default'
*/
name?: string,
/**
* DataSource used for reference of existing schema.
*/
dataSource: DataSource,
/**
* Timestamp in milliseconds.
*
* Default: Date.now()
*/
timestamp?: number,
/**
* Prettify sql statements.
*
* Default: false
*/
prettify?: boolean,
/**
* Language of the generated migration file. It also determines the file extension.
*
* Default: 'ts'
*/
language?: 'ts' | 'js',
/**
* Generate an ESM (export class) instead of a CommonJS (module.exports) migration.
* Only applies to the language js.
*
* Default: false
*/
esm?: boolean,
/**
* Only return up- & down-statements instead of backing up the migration to the file system.
*
* Default: false
*/
preview?: boolean
};MigrationGenerateResult
export type MigrationGenerateResult = {
/**
* Statements which bring the database schema in line with the entity metadata.
*/
up: string[],
/**
* Statements which undo the up statements, in reverse order.
*/
down: string[],
/**
* Content of the migration file. Undefined if there is nothing to generate.
*/
content?: string
};