60 - 80 minutes
Understand how to change the schema of a database table. This includes operations to add, remove, and modify fields.
Making changes to a database is often necessary as an application evolves. While some database changes require creating or deleting tables, others require modifying an existing table (called a database migration).
Which companies use Database Migration?
Apprentices will be able to:
Read through lesson slides Database Migrations.
Make sure to backup your database before running a migration. If you’ve made a mistake in your SQL command (e.g. deleted incorrect field), reverting to a database back-up will allow you to easily get back to the previous state where your data is intact.
To ensure that your migration works correctly, it is a good idea to apply it to a fake dataset first.
Make sure to not slow down the database for other users during a migration. Migrations on large tables can be slow, and when migrations are in-progress, any other changes to modify the table are blocked until the migration finishes. It is a good idea to apply migrations during a low-traffic time, so it is the least disruptive to users.
You should almost always use transactions in a migration. This is especially important when you are doing operations that add or remove columns from the table.
Use transactions only when it’s important that separate SQL commands succeed or fail as a unit. If you use transactions when they are not needed, it will add unnecessary complexity to your SQL code and hurt performance.
Imagine you built a website to track the user’s physical activity. Information about each user is stored in a database table called account, and every time a new user signs up from the signup page, a new row is added to the table.
| id | name | pet | |
|---|---|---|---|
| 1 | Jeff | abcd@gmail.com | siberian cat |
| 2 | Sarah | helloworld@gmail.com | russian blue |
On the signup webpage you want to:
To support these changes, you need to modify the way data is stored in the account table. This includes adding a column for phone and dropping the pet column.
Activity #1 Create your own table with at least four columns. One of the columns should be type VARCHAR(5).
Populate the table with three entries.
Activity #2 Think about how you want the table to change. Are there additional columns you’d like to add or ones you want to remove?
Run a migration to add one column and delete one column. Also, change the field with type VARCHAR(5) to VARCHAR(50). (Don’t forget to run as part of a transaction!)
Practice project:
Further learning: Database Migrations Done Right