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.

  1. 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).

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

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

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

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