Skip to content

Migrations with ORM

This project uses TypeORM to manage the database schema.

Here we will have a quick overview about how we can use TypeORM in this project. If you want more information about this ORM you can check out the TypeORM documentation.

Synchronizing schemas with TypeORM

Managing the schema can be done in two ways:

1. Automatically

Database settings have synchronized: true

What it means:

  • every time you run the application, the database schema will be synced to match entities code
  • it is alright for development, this way you don't need to manage the schema too much and you can focus on other things
  • it is not recommended to use in production: data can be lost during syncing, and database schema changes are not version controlled

2. With migrations

Database settings have synchronized: false

What it means:

  • the database schema will not be synced automatically
  • if you change an entity then launch the app again, at some point errors may arise due to a mismatch between entity code and database schema
  • now you manage the database schema through migrations

Manage migrations with TypeORM

A migration is a set of changes to be applied to a database. In TypeORM, these are inside a .ts of .js file.

The migration files have an up method that will be run when applying a migration, and a down method that can revert a migration.

Creating a new migration

Update any entity you want to update, then create a new migration file using the command:

npm run typeorm:migration:generate <migrationName>

This will generate a new migration file in the migrations directory.

Running migrations

To run all migrations, use the command:

npm run typeorm:migration:run

This runs all the migrations that were not run yet, and that are in the migrations directory. TypeORM uses a special database table to keep track by itself of which migrations have been applied or not.

Showing migrations

You can ask TypeORM to show all the existing migrations. This also tells whether they have been applied already.

npm run typeorm:migration:show

Warning: this command returns an error when some migrations haven't been applied yet.

Reverting migrations

Migrations are reverted one by one. Each time you perform a revert, the latest migration will be reverted. To run a migrations revert:

npm run typeorm:migration:revert

About migration files

For this project, migrations will be kept as .ts files inside management-api/src/migrations.

In the file dbconfig.ts:

  • migrations is where TypeORM will look to find migrations to run.
  • cli.migrationsDir is where newly generated migrations will be stored.

Some details about the migrations configuration (not necessarily needed to use migrations, useful if you are changing configuration):

For the migration files location settings, we will be using __dirname variable. This indicates the path to the file at execution time.

Since the TypeORM CLI can use ts-node to run migrations, we will be looking for .ts files in the original source files when using the TypeORM CLI.

However, TypeORM also reads migration files when NestJS starts. When starting up, NestJS compiles the TypeScript files to the dist/ directory and runs the code from there. If the migrations setting still references the .ts files, errors will show since it is not capable of interpreting these files. Using __dirname, we will now reference the compiled migrations files living in the dist/ directory.

We cannot apply the same logic to cli.migrationsDir since TypeORM will append this value to the current directory for creating the new file, whether we provide an absolute path or not.

Useful T-SQL snippets about database schema

Show existing tables:

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty
(Object_id(Table_name), 'IsMsShipped') = 0

Show table schema:

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table_name'