Postgres with Drizzle · Chapter 7 of 40
The migration trail
By the end of this chapter you can change a table that already holds data (add, drop and rename a column, add an index), read the SQL Drizzle writes for such a change, and prove from the catalog that it happened. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Core CRUD & Querying
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Meera wants members to add a phone number, for sign-in codes. Rohan's search by display name is slow and needs an index. The users table holds real members, so dropping it and creating it again is not an option: the change has to happen to the table as it is, rows and all.
In a Drizzle project you edit schema.ts, and drizzle-kit writes the change as a migration: a file of SQL statements. One wrong answer to its questions drops a column with its data, and a statement that "succeeded" may have done nothing. So read the statements, run them, and check the result in the catalog from chapter 2.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
addColumnToTable: a phone column
Write addColumnToTable(). Add a column named phone, of type VARCHAR(20), to the users table, written so that a second run is not an error (IF NOT EXISTS). Then look phone up in information_schema.columns (table users, schema public).
Return true if the column is there and false if not: a real boolean, not the rows. Afterwards phone stays on the table (the check removes it before the next task).
- Task 2
dropColumnFromTable: add a column and drop it
Write dropColumnFromTable(). Add a TEXT column named temp_col to the users table (IF NOT EXISTS), drop it again (IF EXISTS), then look temp_col up in information_schema.columns (table users, schema public).
Return true if the column is gone (the lookup finds nothing) and false if it is still there.
- Task 3
renameColumn: old_name becomes new_name
Write renameColumn(). Add a TEXT column named old_name to the users table (IF NOT EXISTS), rename it to new_name, and look new_name up in information_schema.columns (table users, schema public). Then drop new_name (IF EXISTS).
Return true if new_name was there before you dropped it. Afterwards neither old_name nor new_name is on the table.
- Task 4
addIndexToTable: an index on display_name
Write addIndexToTable(). Create an index named idx_users_display_name on the display_name column of the users table (IF NOT EXISTS). Look it up by name in pg_indexes (tablename users). Then drop it (IF EXISTS).
Return true if the index was there before the drop, as a real boolean. Afterwards the index is gone.
- Task 5
getTableMigrationInfo: the users table in numbers
Write getTableMigrationInfo(). Count three things about the users table in the public schema: its rows in pg_tables (tableCount: 1 when the table exists), its columns in information_schema.columns (columnCount) and its indexes in pg_indexes (indexCount).
Return an object with exactly the keys tableCount, columnCount and indexCount, each a JavaScript number: the check expects tableCount to be the number 1, so the string '1' fails.
What you will learn
- ALTER TABLE: add, drop and rename a column of a table that keeps its rows.
- CREATE INDEX and pg_indexes: add an index, and list a table's indexes.
- IF EXISTS and IF NOT EXISTS: statements that are safe to run twice, and what they hide.
- Proving a change: looking it up in information_schema.columns, pg_indexes and pg_tables, and counting with numbers.
More in Core CRUD & Querying
Build The migration trail yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.