TypeORM Migrations Explained

Safely make changes to your production database schema

Songtham Tung
Dec 18, 2019 · 7 min read
Image for post
Image for post
Original Photo by Barth Bailey on Unsplash. Modified by Songtham Tungkitkancharoen.

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
Image for post
Image for post
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.

  • Existing TypeORM project connected to a database
  1. Synchronize
  2. Migrations
  3. Closing

Synchronize

When discussing migrations in TypeORM, we need to first talk about synchronize. Let’s begin at ormconfig.json — the config file that gets generated when you initialize a new TypeORM project with typeorm init.

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.

Navigate to User.ts, and you should see the following code:

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.

Image for post
Image for post
Image for post
Image for post
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.

To create a new Entity, create a new file called Company.ts in the same folder as User.ts, and paste in the following snippet:

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.

Image for post
Image for post
Image for post
Image for post
Create a new table by creating a new entity

The same logic applies if you want to remove any tables or columns — simply make the changes in code, save, and restart the server.

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

Don’t skip this step. To get started with migrations, the first thing you should do is set synchronize: false in ormconfig.json. This will prevent schema synchronization.

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.

To generate a migration file, we need to make changes to an entity. Open Company.ts, and add a new column:

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

Image for post
Image for post
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:

Image for post
Image for post
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

Image for post
Image for post
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.

To run a migration, use this command:

./node_modules/.bin/ts-node ./node_modules/.bin/typeorm migration:run
Image for post
Image for post
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.

Image for post
Image for post
No pending migrations

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

Image for post
Image for post
Notice the migrations table and the city column in the company table

The last step in this guide is to revert a 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
Image for post
Image for post
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.

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

Closing

That’s it. Congrats if you made it through the entire tutorial — this isn’t easy stuff. You’ve just successfully generated, ran, and reverted migrations using TypeORM.

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.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store