PostgreSQL, Migrations & Development Data

The saved todo remains available when we reload the browser or restart the API. Where is it stored, and how does the practice application create its table? This chapter builds on your earlier database studies, concentrating on how SQL and database changes fit into a running web application.

The todo table

Start by opening database-migrations/001_create_todos.sql:

-- migrate:up

CREATE TABLE todos (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO todos (name) VALUES ('Finish walking skeleton');

-- migrate:down

DROP TABLE todos;

The todos table is the only application table in the supplied baseline. The local database is named walking_skeleton in project.env.

The first section creates the table and inserts our demonstration row; the second removes the table. Dbmate recognizes the -- migrate:up and -- migrate:down markers and runs only the section for the requested direction. Notice that reversing this change would also destroy the table’s rows.

The course requires both directions. In Part 1, use exactly one plain up marker followed by exactly one plain down marker in each file. Keep dbmate’s default transaction behavior, without marker options such as transaction:false.

Do not execute a complete dbmate file directly through psql, postgres.js, or another raw SQL runner. The markers are SQL comments, so a raw runner could execute both the up and down statements in sequence. Apply or reverse these files through dbmate.

How does dbmate know which changes have already run? It manages a schema_migrations table that records the leading numeric version of each applied migration. This table holds migration versions rather than application records; it stores neither the complete filename nor a checksum of the file’s contents.

Inspect the stored rows

With the application running, open psql inside the database container:

docker compose exec database sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB"'

Inside the database shell:

SELECT id, name FROM todos ORDER BY id;

A freshly extracted practice application has a row named Finish walking skeleton. The generated identifier is a database value, not something the browser invents. Use \dt to list tables and \q to exit psql.

Compare the result with GET /api/todos, then click Load todos in the browser. When the same row appears in all three places, you have connected a value stored in PostgreSQL to the response and the page. The todo route reads this table through todoRepository.ts; the separate /health route makes no database query. We will follow the todo’s path through the code in Chapter 1.7.

Migrations are application history

The filename 001_create_todos.sql identifies our first migration. Dbmate reads names in the form [version]_[description].sql and uses the leading number as the version. The course uses fixed-width versions such as 001 so filenames sort clearly.

Renaming the description while retaining 001 would still identify the same migration. Keep the complete filename after applying it so that the files and instructions continue to describe the same chain.

The practice application’s one-shot service applies any pending migrations to the database that PostgreSQL has already created. Its command is equivalent to:

dbmate --no-dump-schema migrate --strict

The command has three parts:

• migrate applies changes whose versions have not yet been recorded.
• --strict rejects a newly discovered pending migration whose version belongs before an applied migration. This keeps new changes in order.
• --no-dump-schema prevents a separate schema.sql from being generated. The ordered migration files remain the scaffold’s schema source.

The options have different positions: --no-dump-schema is a global option before the command, while --strict follows migrate in dbmate 2.34.1. The rollback command does not use --strict.

Run the configured service rather than installing dbmate on the host:

docker compose run --rm database-migrations

On an unchanged database, the result should report no pending migration. That is the expected result: dbmate finds the recorded 001 version and leaves our existing row in place. Running the INSERT itself again would insert another row. The dbmate 2.34.1 migration documentation describes the file format, ordering, and recorded versions.

For the next schema change, add a new migration and preserve the applied file. To see why, imagine editing 001_create_todos.sql after one database has recorded 001. Dbmate stores only the version and does not checksum the contents, so it would neither reapply nor reject the edited file there.

A fresh database would receive the edited SQL. The two databases would then be built from different SQL but both claim to have version 001. Keeping applied files unchanged avoids this hidden difference.

Every migration file also needs a deliberate down section. dbmate rollback runs the down section of the most recently applied migration and removes its recorded version. It does not infer how to restore discarded rows, and a down migration can itself be destructive.

Review the down section before using rollback, and use it only where losing the affected state is acceptable, such as while developing the latest change against a disposable local or test database. Shared and valuable databases normally need a reviewed forward fix or a separate recovery plan, not an automatic rollback assumption.

Rehearse both directions on disposable state

We can try applying, reversing, and reapplying the migration in a separate Compose project without touching the normal development database. Run the commands below one at a time, pausing to inspect the database between migration commands:

docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml up -d database
docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml run --rm database-migrations
docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml run --rm database-migrations --no-dump-schema rollback
docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml run --rm database-migrations
docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml down

Use a Compose project name that is different from development and any other practice copy. Both Compose files are required: the override supplies temporary database storage and removes published application ports. The run command after the service name replaces the configured dbmate arguments, which is why the rollback command explicitly includes --no-dump-schema.

Inspect the schema, rows, and schema_migrations after the first upward migration, after rollback, and after reapplication. This lets you see which changes each direction makes. Open the disposable database shell with:

docker compose -p wsd-migration-practice -f compose.yaml -f compose.test.yaml exec database sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB"'

The final down removes this practice application’s test database container and its memory-backed data. It needs no -v. Verify the Compose project name before cleaning up. If you see the table disappear after rollback and return with its demonstration row after reapplication, you have checked this migration in both directions. Before rolling back valuable data, you would still need to review what the down migration removes and how to recover it.

This is a useful place to pause. Explain what happened to the todos table, its row, and the recorded migration version at each step. Keep the file open if you need to follow its SQL again.

Adding a column in a later migration

Suppose a separate study-room application originally stored only room names. A later requirement adds a capacity. Its next migration might contain:

-- migrate:up

ALTER TABLE rooms
ADD COLUMN capacity INTEGER NOT NULL DEFAULT 1;

-- migrate:down

ALTER TABLE rooms DROP COLUMN capacity;

Read this as an example for the separate application; the practice application has no rooms table to alter. Existing rooms would receive a capacity of 1. In a real change, we would first ask whether that is a sensible capacity for those rooms, then choose a default that matches the answer.

Next, try the same pattern with an equipment schema. The activity gives its requirements.

-- migrate:up
CREATE TABLE equipment (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO equipment (name) VALUES ('multimeter');

-- migrate:down
DROP TABLE equipment;

Seed data and ordinary user data

The row named Finish walking skeleton gives us something recognizable to load when the application first starts. Data added to establish a useful known starting state is called seed data. In the practice application, the 001_create_todos.sql migration includes that one synthetic demonstration row.

This demonstration row is useful for our practice application. A production database may need different starting data, so we would choose its seeds separately rather than include demonstration records automatically. Later parts can separate production schema migrations, development-only seeds, and test fixtures when those environments need different data.

Manual INSERTs are useful for investigation. To make an intended baseline change available to someone running your submission, also record it in source-controlled setup. Rows stored only in your current database will stay there when someone else downloads the code.

Persistent development state

The database-data volume survives ordinary container removal:

docker compose down
docker compose up

Rebuilding an API image also leaves the database in place. This explains why an old row can still appear after you change or rebuild the application. If a row surprises you, first check which database you are reading and which migrations it has recorded.

A deliberate reset is destructive

Only when you intend to discard the practice application’s local database, use:

docker compose down -v
docker compose up --build

Docker’s shutdown reference distinguishes ordinary removal from volume removal.

Development state and test state

Tests use a separate Compose project. The development and test PostgreSQL instances can both contain a database named walking_skeleton because their containers and stored state are different. project.env supplies the same disposable configuration; compose.test.yaml changes storage and network exposure.

API and browser tests run sequentially within the chosen Compose test project. Their temporary database can remain running after a one-off runner exits, so its rows are not automatically reset between suites or assertions. Each test cleans up the rows it created, and a deliberate Compose project shutdown creates a fresh database for the next run. We will follow that setup and cleanup in Chapter 1.8; for now, the important connection is that we can preserve development data while giving tests their own database.

Check Your Understanding

1. What do the up and down sections of 001_create_todos.sql change, and why should the file be run through dbmate?
2. What does schema_migrations record, and why does rerunning dbmate leave an already applied migration alone?
3. Why should a new schema change use a new migration file instead of editing an applied one?
4. What can a rollback remove, and why does reversing the schema not necessarily recover earlier data?
5. Why can a row survive docker compose down, and how does adding -v change the result?
6. Why do tests need to arrange and clean up their rows even when they use a separate temporary database?