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

Express JavaScript

This entry is part 9 of 10 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.

typeorm migrations pgadmin

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   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

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:

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

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:

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

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

Running the migrations with the CLI

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

We are presented with the result in the console:

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

typeorm migrations pgadmin

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

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:

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.

src/migrations/1548598555048-UserFullName.ts

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

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 >>
avatar
  Subscribe  
newest oldest most voted
Notify of
andries
andries

awesome post. Thanks for sharing.

ando
ando

is there a way to change the name of the default schema? Thanks for sharing, it’s help me a lot !