TypeORM Migrations with Postgres. TypeScript Express #9

January 28, 2019

This entry is part 9 of 15 in the TypeScript Express tutorial

In this article, we go through migrations, which is a significant thing when working with Postgres. We learn what they are and the basics of how to write them. Although the knowledge of SQL is useful here, we also learn how to have them auto-generated for us with the help of TypeORM CLI and how to use the TypeORM migrations API to avoid writing SQL queries.

TypeORM migrations: what is a schema?

The first thing to cover to understand migrations is the idea of a schema. It is a structure described in a SQL language that describes a blueprint of how the database is constructed. When using Postgres with TypeORM, the schema is generated based on your entities. When you instantiate a database, you create a default schema named public. It includes all the tables that you defined in your application.

If you look at the screenshot above, you can observe that it acts as a namespace, organizing and grouping your entities.

In the first part of the tutorial covering Postgres, we use synchronize: true in our connection options. It indicates that we want to generate the database schema on every application launch. While it is very convenient during development, it is not suitable for production because it may cause you to lose data.

Migrations

After turning off the automatic schema synchronization, we need to alter it ourselves by writing migrations. Migration is a single file with queries that update the schema and apply changes to an existing database. Let’s bring up our User entity:

src/user/user.entity.ts

import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

import Address from '../address/address.entity';

import Post from '../post/post.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: string;

@Column()

public name: string;

@Column()

public email: string;

@Column()

public password: string;

@OneToOne(() => Address, (address: Address) => address.user, {

cascade: true,

eager: true,

})

@JoinColumn()

public address: Address;

@OneToMany(() => Post, (post: Post) => post.author)

public posts: Post[];

}

export default User;

We already have users registered and saved in the database using this entity. Imagine a situation in which you want to rename the name column to fullName. To do that, we need to create a migration. We can use the TypeORM CLI to help us do that.

Using TypeORM CLI

We use the TypeORM CLI installed in the node_modules directory. To do this, we need an additional script in our package.json file:

"scripts": {

"dev": "ts-node ./src/server.ts",

"lint": "tslint -p tsconfig.json -c tslint.json",

"typeorm:cli": "ts-node ./node_modules/typeorm/cli -f ./src/ormconfig.ts"

}

You can also install typeorm globally, but it won’t be able to read the typescript config file out of the box

Due to some issues, we need to use ts-node here and export our CLI config in a CommonJS way.

src/ormconfig.ts

import { ConnectionOptions } from 'typeorm';

const config: ConnectionOptions = {

type: 'postgres',

host: process.env.POSTGRES_HOST,

port: Number(process.env.POSTGRES_PORT),

username: process.env.POSTGRES_USER,

password: process.env.POSTGRES_PASSWORD,

database: process.env.POSTGRES_DB,

entities: [

__dirname + '/../**/*.entity{.ts,.js}',

cli: {

migrationsDir: 'src/migrations',

}

};

export = config;

Now we can run TypeORM CLI through NPM scripts. You can add any additional parameters after the -- characters.

Creating migrations

Let’s create our first migration:

1	npm run typeorm:cli -- migration:create -n UserFullName

It generates a file for us that contains the current timestamp when the migration was generated. It looks like this:

src/migrations/1548548890100-UserFullName.ts

import { MigrationInterface, QueryRunner } from 'typeorm';

export class UserFullName1548548890100 implements MigrationInterface {

public async up(queryRunner: QueryRunner): Promise<any> {

}

public async down(queryRunner: QueryRunner): Promise<any> {

}

You can also create the migration files by hand

There are two methods that we need to write:

the up method performs the migration
the down method reverts it

When it comes to the QueryRunner, you can either build the query by hand or use the migration API. Let’s go with the first option for starters:

src/migrations/1548548890100-UserFullName.ts

import { MigrationInterface, QueryRunner } from 'typeorm';

export class UserFullName1548548890100 implements MigrationInterface {

public async up(queryRunner: QueryRunner): Promise<any> {

await queryRunner.query(`ALTER TABLE "user" RENAME "name" to "fullName"`);

}

public async down(queryRunner: QueryRunner): Promise<any> {

await queryRunner.query(`ALTER TABLE "user" RENAME "fullName" to "name"`);

}

Running the migrations with the CLI

Once we got that down, we can execute it using the CLI:

1	npm run typeorm:cli -- migration:run

We are presented with the result in the console:

query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'

query: SELECT * FROM "migrations" "migrations"

0 migrations are already loaded in the database.

1 migrations were found in the source code.

1 migrations are new migrations that needs to be executed.

query: START TRANSACTION

query: ALTER TABLE "user" RENAME "name" to "fullName"

query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1548548890100,"UserFullName1548548890100"]

Migration UserFullName1548548890100 has been executed successfully.

query: COMMIT

This runs the migrations in a sequence ordered by their timestamps. In this process, queries written in our up methods are executed.

Running the migrations in the code

Aside from running the migrations using the CLI, you can do it from within your application. Thanks to that, TypeORM always checks if there are any migrations that it needs to run at the start.

src/server.ts

import 'dotenv/config';

import 'reflect-metadata';

import { createConnection } from 'typeorm';

import AddressController from './address/address.controller';

import App from './app';

import AuthenticationController from './authentication/authentication.controller';

import CategoryController from './category/category.controller';

import * as config from './ormconfig';

import PostController from './post/post.controller';

import validateEnv from './utils/validateEnv';

validateEnv();

(async () => {

try {

const connection = await createConnection(config);

await connection.runMigrations();

} catch (error) {

console.log('Error while connecting to the database', error);

return error;

}

const app = new App(

[

new PostController(),

new AuthenticationController(),

new AddressController(),

new CategoryController(),

);

app.listen();

})();

If you are not doing that, make sure to run the migrations using the CLI when you are in the process of deployment.

Reverting migrations

If you decide that you need to cancel the changes that are applied it, you can do so with the revert command:

1	npm run typeorm:cli -- migration:revert

query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'

query: SELECT * FROM "migrations" "migrations"

1 migrations are already loaded in the database.

UserFullName1548548890100 is the last executed migration. It was executed on Sun Jan 27 2019 01:28:10 GMT+0100 (CET).

Now reverting it...

query: START TRANSACTION

query: ALTER TABLE "user" RENAME "fullName" to "name"

query: DELETE FROM "migrations" WHERE "timestamp" = $1 AND "name" = $2 -- PARAMETERS: [1548548890100,"UserFullName1548548890100"]

Migration UserFullName1548548890100 has been reverted successfully.

query: COMMIT

The command above executes the down method in the latest performed migration. If you need to revert more than one migration, you need to call revert multiple times.

The TypeORM knows the current state of migrations in your database thanks to the migrations table. It holds the data about migrations that are already completed. When we use the revert functionality, TypeORM knows what migration has been done last because of the timestamp that it holds in the migrations table.

Generating migrations

Thankfully, TypeORM can automatically generate migration files with the changes to your schema. After reverting our migration in the previous paragraph, we can let the TypeORM handle the writing of the migration for us.

1	npm run typeorm:cli -- migration:generate -n UserFullName

src/migrations/1548598555048-UserFullName.ts

import { MigrationInterface, QueryRunner } from 'typeorm';

export class UserFullName1548598555048 implements MigrationInterface {

public async up(queryRunner: QueryRunner): Promise<any> {

await queryRunner.query('ALTER TABLE "user" RENAME COLUMN "name" TO "fullName"');

}

public async down(queryRunner: QueryRunner): Promise<any> {

await queryRunner.query('ALTER TABLE "user" RENAME COLUMN "fullName" TO "name"');

}

If you look closely, you can see that the query is slightly different than the one that we wrote. It is a good idea to check it out first before running it. To make the work of the TypeORM CLI easier, generate migrations after each change you made to your entities, so it would have to generate relatively simple queries.

Using migration API

When writing your migration by hand, you don’t have to create SQL queries necessarily. You can use the migration API, that has a set of functions that can make changes in the schema. Let’s implement our example using it.

src/migrations/1548598555048-UserFullName.ts

import { MigrationInterface, QueryRunner } from 'typeorm';

export class UserFullName1548600643971 implements MigrationInterface {

public async up(queryRunner: QueryRunner): Promise<any> {

await queryRunner.renameColumn('user', 'name', 'fullName');

}

public async down(queryRunner: QueryRunner): Promise<any> {

await queryRunner.renameColumn('user', 'fullName', 'name');

}

1	npm run typeorm:cli -- migration:run

query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'

query: SELECT * FROM "migrations" "migrations"

0 migrations are already loaded in the database.

1 migrations were found in the source code.

1 migrations are new migrations that needs to be executed.

query: START TRANSACTION

query: SELECT * FROM current_schema()

query: SELECT * FROM "information_schema"."tables" WHERE ("table_schema" = 'public' AND "table_name" = 'user')

query: SELECT *, ("udt_schema" || '.' || "udt_name")::"regtype" AS "regtype" FROM "information_schema"."columns" WHERE ("table_schema" = 'public' AND "table_name" = 'user')

query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "cnst"."conname" AS "constraint_name", CASE "cnst"."contype" WHEN 'x' THEN pg_get_constraintdef("cnst"."oid", true) ELSE "cnst"."consrc" END AS "expression", CASE "cnst"."contype" WHEN 'p' THEN 'PRIMARY' WHEN 'u' THEN 'UNIQUE' WHEN 'c' THEN 'CHECK' WHEN 'x' THEN 'EXCLUDE' END AS "constraint_type", "a"."attname" AS "column_name" FROM "pg_constraint" "cnst" INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid" INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace" LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid" AND "a"."attnum" = ANY ("cnst"."conkey") WHERE "t"."relkind" = 'r' AND (("ns"."nspname" = 'public' AND "t"."relname" = 'user'))

query: SELECT "ns"."nspname" AS "table_schema", "t"."relname" AS "table_name", "i"."relname" AS "constraint_name", "a"."attname" AS "column_name", CASE "ix"."indisunique" WHEN 't' THEN 'TRUE' ELSE'FALSE' END AS "is_unique", pg_get_expr("ix"."indpred", "ix"."indrelid") AS "condition", "types"."typname" AS "type_name" FROM "pg_class" "t" INNER JOIN "pg_index" "ix" ON "ix"."indrelid" = "t"."oid" INNER JOIN "pg_attribute" "a" ON "a"."attrelid" = "t"."oid" AND "a"."attnum" = ANY ("ix"."indkey") INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "t"."relnamespace" INNER JOIN "pg_class" "i" ON "i"."oid" = "ix"."indexrelid" INNER JOIN "pg_type" "types" ON "types"."oid" = "a"."atttypid" LEFT JOIN "pg_constraint" "cnst" ON "cnst"."conname" = "i"."relname" WHERE "t"."relkind" = 'r' AND "cnst"."contype" IS NULL AND (("ns"."nspname" = 'public' AND "t"."relname" = 'user'))

query: SELECT "con"."conname" AS "constraint_name", "con"."nspname" AS "table_schema", "con"."relname" AS "table_name", "att2"."attname" AS "column_name", "ns"."nspname" AS "referenced_table_schema", "cl"."relname" AS "referenced_table_name", "att"."attname" AS "referenced_column_name", "con"."confdeltype" AS "on_delete", "con"."confupdtype" AS "on_update" FROM ( SELECT UNNEST ("con1"."conkey") AS "parent", UNNEST ("con1"."confkey") AS "child", "con1"."confrelid", "con1"."conrelid", "con1"."conname", "con1"."contype", "ns"."nspname", "cl"."relname", CASE "con1"."confdeltype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confdeltype", CASE "con1"."confupdtype" WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END as "confupdtype" FROM "pg_class" "cl" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid" WHERE "con1"."contype" = 'f' AND (("ns"."nspname" = 'public' AND "cl"."relname" = 'user')) ) "con" INNER JOIN "pg_attribute" "att" ON "att"."attrelid" = "con"."confrelid" AND "att"."attnum" = "con"."child" INNER JOIN "pg_class" "cl" ON "cl"."oid" = "con"."confrelid" INNER JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid" INNER JOIN "pg_attribute" "att2" ON "att2"."attrelid" = "con"."conrelid" AND "att2"."attnum" = "con"."parent"

query: ALTER TABLE "user" RENAME COLUMN "name" TO "fullName"

query: INSERT INTO "migrations"("timestamp", "name") VALUES ($1, $2) -- PARAMETERS: [1548600643971,"UserFullName1548600643971"]

Migration UserFullName1548600643971 has been executed successfully.

query: COMMIT

The above code generates quite a big query, so you might be better off with writing your SQL query if the performance is one of your concerns.

There are a lot more functions in the QueryRunner. If you want to check it out, look into the documentation.

Summary

In this article, we went through the basics of migrations. It includes explaining why they are essential and how to write them. We also covered how to generate and run them with TypeORM CLI. Aside from that, we also mentioned migrations API that allows us to write migrations without the knowledge of SQL.

Series Navigation<< TypeScript Express tutorial #8. Types of relationships with Postgres and TypeORMTypeScript Express tutorial #10. Testing Express applications >>