Simplified
Simplified

L5.1Adding a column to a live table

YOU

Adding a column to a live table

ALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;usersidnamexp………2Ben403Cy0………daily_goal303030…2,000,000 rowsAda's dashboarddaily goal30 XP a dayCREATE TABLE …??
ALTER TABLE profilesADD COLUMN pronouns text;no DEFAULTInstagram's profilesidusername………………pronounsyoupronouns
usersnamedaily_goalAda30Ben30……profilesusernamepronounsyouNULLyour friendNULL……

Databases · Lesson 5.1

Changing a Live Table

Adding a column to a live table

Simplified
0:00 / 1:42

Every learner who signs up to Simplified gets one row in its users table.

With 2,000,000 learners, that's 2,000,000 rows, like Ada's, with her 120 XP.

ALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;usersidnamexp1Ada1202Ben403Cy0………daily_goal303030…2,000,000 rowsAda's dashboarddaily goal30 XP a dayCREATE TABLE …

Now say the team wants to give every learner a daily goal: 30 XP a day, shown on their dashboard.

Each learner's row then needs to hold their goal, so users needs a new column: daily goal.

But how does a new column get into a table that already holds 2,000,000 rows?

CREATE TABLE only makes new tables, and users is already full of learners' rows.

So the change is made to users itself, in place.

The query for that is called ALTER TABLE.

ALTER TABLE changes a table that already exists, and keeps its rows.

ALTER TABLE usersADD COLUMNdaily_goalintegerDEFAULT 30;usersidnamexp1Ada1202Ben403Cy0………daily_goal303030…2,000,000 rowsAda's dashboarddaily goal30 XP a dayCREATE TABLE …

It starts with ALTER TABLE users: the table to change.

Then add column daily goal, integer: a new column, holding whole numbers.

And DEFAULT 30 gives 30 to every row that has no goal yet.

All 2,000,000 rows were saved before the column existed, so each gets 30: Ada's, Ben's, and every other learner's.

And what does a new column hold when it has no DEFAULT?

ALTER TABLE profilesADD COLUMNpronouns text;no DEFAULTInstagram's profilesidusername1you2your friend……pronounsNULLNULL…youpronouns

Say Instagram adds a pronouns column to its profiles table, with ALTER TABLE.

It's text, with no DEFAULT.

Your profile was saved before the column existed, so it leaves pronouns out.

With no DEFAULT to fill it, your row gets NULL there: a missing value.

So does your friend's, and every other profile already there.

That's why your profile shows no pronouns until you add yours.

So ALTER TABLE changes a table in place: each row already there gets the new column's DEFAULT, or NULL.

Example: Adding a column to a live table

Simplified's users table holds 2,000,000 rows. Dee's row was saved long ago, with 300 XP. Then Simplified runs ALTER TABLE users ADD COLUMN daily_goal integer DEFAULT 30;.

What does Dee's row hold in daily_goal?

Explanation

First, Dee's row was saved before daily_goal existed, so it leaves daily_goal out.

Then, the new column has DEFAULT 30, so every row that leaves it out gets 30.

Therefore, Dee's row holds 30.

Question 1

Say Duolingo's users table already holds millions of learners' rows. Duolingo wants a new column in it, daily_reminder, and must keep every row.

Which query does that?

The same change on every database

ALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;usersidnamexp1Ada1202Ben403Cy0………daily_goal303030…2,000,000 rowsAda's dashboarddaily goal30 XP a dayCREATE TABLE …
laptopusersidnamexpdaily_goaltestusersidnamexpdaily_goalproductionusersidnamexpdaily_goal2,000,000 rowsALTER TABLE users …SELECT daily_goal …?
Simplified's repocode001_create_users.sql002_create_lessons.sql003_add_daily_goal.sqlALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;migrationslaptoptestproduction?
laptop001002003usersidnamexpdaily_goallessonsidtest001002003usersidnamexpdaily_goallessonsidproduction001002003usersidnamexpdaily_goallessonsid
productionusersidnamexpdaily_goal001002003003 again
003_add_daily_goal.sqlALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;teammateproduction2,000,000 rows
laptop001002003usersidnamexpdaily_goallessonsidtest001002003usersidnamexpdaily_goallessonsidproduction001002003usersidnamexpdaily_goallessonsid

Databases · Lesson 5.2

Changing a Live Table

The same change on every database

Simplified
0:00 / 2:10

The daily goal column got into users with ALTER TABLE, and every row got 30.

But Simplified doesn't have just one database.

Production's database is the one real learners use, with their 2,000,000 rows.

A test database is where the team tries each change, before learners meet it.

And each developer runs Postgres on their own laptop, to try code as they write it.

Say a developer runs that ALTER TABLE on production's database and the test one, but not their laptop's.

Then, on the laptop, a query that asks for daily goal fails: there's no such column.

laptopusersidnamexpdaily_goaltestusersidnamexpdaily_goalproductionusersidnamexpdaily_goal2,000,000 rowsALTER TABLE users …SELECT daily_goal …

So how does every database get the same changes, in the same order?

Each change to the schema is written down as a file, in Simplified's repo, beside its code.

The file holds the SQL for that one change, and its name starts with a number.

Simplified's repocode001_create_users.sql002_create_lessons.sql003_add_daily_goal.sqlALTER TABLE usersADD COLUMN daily_goal integerDEFAULT 30;migrationslaptoptestproduction

The first file, 001, creates the users table.

The second, 002, creates the lessons table.

And the third, 003, holds the ALTER TABLE that adds daily goal.

Each of these files is called a migration.

A migration is one schema change, saved as a numbered file, and run once on every database.

So how do the numbers keep every database the same?

laptop001002003usersidnamexpdaily_goallessonsidtest001002003usersidnamexpdaily_goallessonsidproduction001002003usersidnamexpdaily_goallessonsid

Say a new developer joins Simplified, and starts Postgres on their laptop, with no tables yet.

001 runs first, and makes users.

Then 002 makes lessons, and 003 adds daily goal to users.

The order matters: 003 changes users, so 001 must have made users first.

The test database and production's ran the same three, in the same order.

So all three databases end up with the same schema: users with its daily goal, and lessons.

productionusersidnamexpdaily_goal001002003003 again

And each migration runs only once on a database.

A second run of 003 would try to add a daily goal column that's already there.

And because a migration is a file in the repo, a teammate reviews it like any code, before it runs.

So a mistake can be caught before it reaches production's 2,000,000 rows.

Write each schema change once, as a numbered migration, and every database ends up the same.

Example: The same change on every database

Simplified's repo holds three migrations: 001_create_users.sql, 002_create_lessons.sql and 003_add_daily_goal.sql. Production's database has run all three. Then a developer adds 004_add_mentor_id.sql to the repo.

Which migrations run on production next?

Explanation

First, production has already run 001, 002 and 003, and each migration runs only once on a database.

Then, 004 is the only one production hasn't run.

Therefore, only 004 runs.

Question 1

A new developer joins Simplified and starts Postgres on their laptop, with no tables yet. Simplified's repo holds six migrations, 001 to 006, and each runs once on every database.

How many migrations run on the new laptop's database?

migrations

Question 2

Say Duolingo's repo holds 001_create_users.sql, which makes the users table, and 002_add_streak.sql, which adds a streak column to users with ALTER TABLE.

Why must 001 run before 002 on every database?

Question 3

Simplified has three databases: one on a developer's laptop, a test one, and production's. Each migration runs once on every database. A teammate adds migration 007 to the repo.

Counting all three databases, how many times does 007 run in all?

times

Question 4

Simplified has a database on each developer's laptop, a test one, and production's. Say a developer adds a column to production's users table by typing an ALTER TABLE straight into it, with no migration.

What happens?

Question 5

A Simplified developer writes 005_add_mentor_id.sql. Before it runs on production, a teammate reads it and spots a mistake in its SQL.

What made that possible?

Question 6

Production's database has run Simplified's migrations 001 to 004. Migration 003 adds a daily_goal column to users with ALTER TABLE.

Why must 003 never run on production again?

Assistant

Highlight lesson text and click Ask AI, or type a question below.