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?
- Many top companies uses database migration for maintaining and operating the database. Some of them are mentioned below along with the description in links:
Participants will be able to:
- Add a field to a table
- Remove a field from a table
- Change the properties of a field (name, data type)
- Change the name of a table
- Know what database transactions are and when to apply them
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 sign up 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 sign up webpage you want to:
- include a new field for phone number
- remove the field for pet (unnecessary info to collect and many people don't have pets)
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.
- Write a SQL command to generate the account table, and populate it with the sample data
- Apply a migration to modify the table
- Insert a new user Alexandria with:
- email orange1777@gmail.com
- phone number (515) 525-5151 (stored as INT)
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!)
Further learning: Database Migrations Done Right