TypeScript Express tutorial #9. The basics of migrations using TypeORM and Postg

TypeORM migrations: what is a schema?

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.

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

// src/entities/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;

Imagine a situation in which you want to rename the name column to fullName.

Using TypeORM CLI

ref the doc of typeorm migration

1. change datasource

typeorm migration:create

// ormconfig.ts
import { DataSourceOptions } from "typeorm";

console.log("ormconfig.ts: ", __dirname + "/**/*.entity{.ts,.js}");

const config: DataSourceOptions = {
	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}"],
	// synchronize: true,
	migrations: [
		/*...*/
	],
	migrationsTableName: "custom_migration_table",
};

export default config;

Here we setup two options:

• "migrationsTableName": "migrations" - Specify this option only if you need migration table name to be different from "migrations".
• "migrations": [/*...*/] - list of migrations need to be loaded by TypeORM

package.json

  "scripts": {
    "d": "nodemon -e ts  --exec ts-node ./src/server.ts",
    "dev": "ts-node ./src/server.ts",
    "mig:create": "typeorm migration:create ./src/migrations/PostRefactoring",
    "mig:generate": "typeorm-ts-node-esm migration:generate ./src/migrations/update-post-table -d ./src/data-source.ts",
    "mig:run": "typeorm-ts-node-esm migration:run --  -d ./src/data-source.ts",
    "mig:revert": "typeorm migration:revert -- -d ./src/data-source.ts"
  },

2. run migration script, create migration file

pnpm mig:create

Here, PostRefactoring is the name of the migration - you can specify any name you want. After you run the command you can see a new file generated in the "migration" directory named {TIMESTAMP}-PostRefactoring.ts where {TIMESTAMP} is the current timestamp when the migration was generated. Now you can open the file and add your migration sql queries there.

You should see the following content inside your migration:

// src/migrations/1689777012187-PostRefactoring.ts

import { MigrationInterface, QueryRunner } from "typeorm"

export class PostRefactoring1689777012187 implements MigrationInterface {

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

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

There are two methods you must fill with your migration code: up and down. up has to contain the code you need to perform the migration. down has to revert whatever up changed. down method is used to revert the last migration.

Inside both up and down you have a QueryRunner object. All database operations are executed using this object. Learn more about query runner.

Let's see what the migration looks like with our Post changes:

// src/migrations/1689777012187-PostRefactoring.ts

import { MigrationInterface, QueryRunner } from "typeorm"

export class PostRefactoring1689777012187 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "user" RENAME "name" to "fullName"`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "user" RENAME "fullName" to "name"`);
    }
}

3. generate

pnpm mig:generate

4. revert if needed

pnpm mig:revert

source