TypeORM Migrations Explained

Safely make changes to your production database schema

Songtham Tung
Dec 18, 2019 · 7 min read
Photo by Barth Bailey on Unsplash

A migration in TypeORM is a single file with SQL queries to update a database schema. This is important to know as a database administrator, back-end engineer, or tech lead because it’s one of the safest ways for making database changes in production. In this piece, we’ll go over the best practices on how to perform migrations in MySQL with TypeORM.

This guide builds upon a previous piece that showed how to quickly get started with TypeORM, MySQL, and ExpressJS. If you don’t have an existing TypeORM project, I’d highly recommend for you to create one following the steps in the piece so you can follow along in this guide. #learningbydoing

When reading this, keep in mind that:

  • Object-relational mapping (ORM) is the bridge between the API and database
ORM is the bridge between the API and database
  • Changing the entity — aka data model (TypeORM class that stores an object’s state in the database) — will update the schema during synchronization or migration.

Note: Although I make references to MySQL, you can use other databases as well.

Prerequisites

Outline

  1. Migrations
  2. Closing

Synchronize

ormconfig.json

Notice that synchronize: true. This is the default setting, but what exactly is it? And how does it relate to migrations? According to the TypeORM README.md:

Synchronize makes sure your entities will be synced with the database, every time you run the application.”

What this means is anytime you make changes to your entity, it’ll automatically update those schema changes with the database linked to your app.

For the rest of this section, we’ll go over three scenarios of synchronization:

  1. Add a new column to an entity
  2. Create a new table by creating a new entity
  3. Remove a column and/or table by modifying an entity

Let’s see this in action.

1. Add a new column to an entity

src/entity/User.ts

Let’s add a new column, birthplace, to the User entity by copy and pasting the code below.

@Column()

birthplace: string;

Save. Restart the local server, and you should see the birthplace column added to the User table.

Before and after. Notice the addition of the birthplace column.

That’s it. See how easy it is to add a new column to your database using synchronize in TypeORM?

You can even create a new table using this approach.

2. Create a new table by creating a new entity

src/entity/Company.ts

Save. Restart the local server, and you should see the Company table added to your database with the columns id and name.

Create a new table by creating a new entity

3. Remove a column and/or table by modifying an entity

OK, so if synchronize automatically syncs our code with the database, why might migrations be necessary?

Migrations are necessary because typically it’s unsafe to use synchronize:true for schema synchronization on production once you get data in your database.

To put it in another way, automatically updating the production schema with synchronization is dangerous. What happens if something breaks? What happens if data is lost? What happens if you want to version control any database schema changes?

This is where TypeORM migrations come in to help.


Migrations

For the rest of this section, we’ll go over three scenarios in a migration:

  1. Generate migration
  2. Run migration
  3. Revert migration

Let’s get started.

1. Generate migration

@Column()

city: string;

Save. Try restarting the server now. The database shouldn’t get updated since synchronize is false.

The next step is to use the CLI command typeorm migration:generate. This generates a new migration file with SQL needs to be executed to update schema. Running this will show the helper menu since we didn’t specify the name argument.

typeorm migration:generate

The name argument is the name of the migration class and should be something descriptive like a Git commit. In our example, let’s go with AddCityColumnToCompany. The problem, however, is if we were to run the following command, we might get the following error:

Unexpected token { Error!

This is a known issue that has to do with our node environment trying to load .ts instead of .js. If you’re receiving this error, the quick fix is to run the following command:

./node_modules/.bin/ts-node ./node_modules/.bin/typeorm migration:generate -n AddCityColumnToCompany

typeorm migration:generate

If successful, you’ll see a new autogenerated file in your migration folder called {TIMESTAMP}-AddCityColumnToCompany.ts with the following content:

Notice the two methods: (1) up (2) down. These are SQL commands.

  1. up —contains the code you need to perform the migration.
  2. down — contains the code to revert whatever up has changed.

Great! Now that we’ve generated a migration file, let’s run it.

2. Run migration

./node_modules/.bin/ts-node ./node_modules/.bin/typeorm migration:run
typeorm migration:run

Successful migrations will run the code in the up method. Let’s take a look, line by line, at what statements are being executed.

1. SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ‘test’ AND `TABLE_NAME` = ‘migrations’: This is checking to see if there’s a migrations table in your database.

2. CREATE TABLE `test`.`migrations` (`id` int NOT NULL AUTO_INCREMENT, `timestamp` bigint NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) SONGTHAM ENGINE=InnoDB: If there’s no table, create it.

3. SELECT * FROM `test`.`migrations` `migrations`: This looks up the migrations table and crosschecks to see if there are any migrations that match the name of the migration file. If one exists, skip. Otherwise, continue.

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.

4. query: START TRANSACTION
query: ALTER TABLE `company` ADD `city` varchar(255) NOT NULL: This is the SQL statement that’s running the migration script.

5. INSERT INTO `test`.`migrations`(`timestamp`, `name`) VALUES (?, ?) — PARAMETERS: [1576405409745,”AddCityColumnToCompany1576405409745"]: If the migration ran successfully, this statement inserts a new record into the migration table. This log ensures if we were to run the migration command again, TypeORM will skip it since it knows from step 3 that it’s been run before.

No pending migrations

Take a look at our database, and we shall see the changes made from the script above.

Notice the migrations table and the city column in the company table

3. Revert migration

Reverting a migration runs the down method in the migration file. This is useful in case we made a schema change we no longer want.

To revert a migration, use this command:

./node_modules/.bin/ts-node ./node_modules/.bin/typeorm migration:revert
typeorm migration:revert

Let’s take a look, line by line, at what statements are being executed.

1. query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ‘test’ AND `TABLE_NAME` = ‘migrations’
query: SELECT * FROM `test`.`migrations` `migrations`
: This looks up the migrations table and crosschecks to see if there are any migrations that match the name of the migration file. If yes, perform the reversion.

1 migrations are already loaded in the database.
AddCityColumnToCompany1576405409745 is the last executed migration. It was executed on Sun Dec 15 2019 17:23:29 GMT+0700 (Indochina Time).
Now reverting it…

2. query: START TRANSACTION
query: ALTER TABLE `company` DROP COLUMN `city`
: This is the SQL statement that’s running the migration revert script.

(3)query: DELETE FROM `test`.`migrations` WHERE `timestamp` = ? AND `name` = ? — PARAMETERS: [1576405409745,”AddCityColumnToCompany1576405409745"]: This removes the record from the migration table. That way, if we were to revert the migration, nothing will happen since TypeORM wasn’t able to find the migration log in the database. It also means we can now run the migration again if we choose to do so.

Notice the empty migrations table and that the city column has been removed from the company table

Closing

Migrations is an essential feature for handling schema updates to your database. The great thing about this is it’s all handled in code, which means it can be version controlled. You don’t have to execute SQL statements manually anymore. The cherry on top is you can integrate this with your CI/CD pipeline since running TypeORM migrations involves using the CLI.

Thanks for reading!


Better Programming

Advice for programmers.

Thanks to Zack Shapiro

Songtham Tung

Written by

Technical Product Director @ Geddit | From the bay to bkk | #b2b #saas #cloud

Better Programming

Advice for programmers.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade