Build SQL Migrations with Dbmate

Create and test a local PostgreSQL migration pipeline with Dbmate.

Introduction

30 Second Summary

A project can start with a simple data table. As requirements grow, remembering which change happened when becomes surprisingly difficult.

In this project, you will build a local SQL migration pipeline for a tasks table with Dbmate and PostgreSQL. Each numbered file becomes a readable record of one schema change that you can apply or reverse.

What You'll Build

Imagine running your migration pipeline to watch the tasks table gain new columns, roll back cleanly, then return to its latest schema.

By the end of this project, you'll have:

  • A live tasks table that you can inspect after the first migration, with id and title proving the SQL changed the database.
  • A version-only tracking demonstration where editing applied SQL leaves PostgreSQL unchanged.
  • A tested forward-fix and rollback workflow that adds priority, reverses the latest schema change, then safely reapplies it.
  • Secret Mission: Design a third reversible migration that adds a due_date column without changing either applied migration.

Are there any prerequisites?

You need basic SQL and permission to create a local PostgreSQL database. The project guides you through setting up Dbmate.

Before We Start

This opening step is where you commit to the migration workflow you are about to build. Versioned SQL files under db/migrations will control PostgreSQL schema changes. Applied files will stay unchanged because Dbmate records applied versions instead of rerunning edited history.

Set Up Dbmate and the Migration Lab

Your local PostgreSQL installation already supplies the database engine for this lab. This step adds Dbmate 2.37.0 as the tool that applies your versioned SQL files.

A non-admin Windows PowerShell session keeps the installation tied to your account. An isolated practice database keeps later schema changes away from your other local work.

In this step, get ready to:
  • Install Dbmate 2.37.0 through Scoop.
  • Create the isolated migration_lab database with psql.
  • Connect Dbmate to migration_lab through the project’s .env file.
Install Dbmate in PowerShell

Dbmate is the only new system tool this project needs. Scoop provides a current-user installation path when the command is missing.

  • Press the Windows key to open Windows search.
  • Type Windows PowerShell into the search box.
  • Select the regular Windows PowerShell result without the administrator option.
  • Check the installed Dbmate release by running:
dbmate --help

What does this check prove?

The help output identifies the installed release. It also confirms that PowerShell can find the Dbmate command.

✔️ I see version 2.37.0

Your Dbmate installation is ready. You now have the exact CLI release used throughout this project.

ⓧ I see an older version

The installed command works. Scoop needs fresh package information before it can replace the older release.

  • Refresh Scoop’s package information by running:
scoop update

What does this command update?

Scoop refreshes its own files. It also retrieves the latest app manifests used to locate available releases.

  • Update the installed Dbmate package by running:
scoop update dbmate

What does this command update?

Scoop compares the installed Dbmate package with its current manifest. It replaces the older package with the available release.

  • Confirm the updated Dbmate release by running:
dbmate --help

What should the help output confirm?

The help output should now identify Dbmate 2.37.0. It should also list the migration commands used later in the project.

Still seeing the older version?

Close the current PowerShell window. Start a new non-admin PowerShell session so Windows reloads your command path.

If the version remains unchanged, the older executable may have been installed outside Scoop.

Help me find which Dbmate executable PowerShell is using.

ⓧ Command not found

PowerShell cannot find Dbmate yet. The next check determines whether Scoop is ready to install it.

  • Check whether Scoop is available by running:
scoop --version

What does this check decide?

A version number means Scoop is available for your Windows account. A command failure means Scoop needs to be installed first.

✔️ I see a Scoop version

  • Install Dbmate for your Windows account by running:
scoop install dbmate
What did Scoop install?

Scoop downloads Dbmate into your user profile. It also makes the dbmate command available through your path.

  • Verify the Dbmate release by running:
dbmate --help
What should the help output confirm?

The help output should identify Dbmate 2.37.0. It should also display the available migration commands.

Did the Dbmate installation fail?

Check that PowerShell can reach the internet. Retry the installation after the connection is stable.

Help me diagnose the Scoop installation output.

ⓧ Scoop is unavailable

Scoop installs inside your Windows user profile. The setup does not require an administrator PowerShell window.

Changing the execution policy can feel risky. The command below limits the change to your current Windows account.

  • Allow the installer to run by using this command only if PowerShell blocks script execution:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
What does this policy change do?

The command permits remote scripts that meet the selected signing policy. The CurrentUser scope limits the policy change to your account.

PowerShell may ask you to confirm the current-user policy change. This confirmation happens before the command finishes.

  • Install Scoop for your Windows account by running:
irm get.scoop.sh | iex
What does the installer do?

PowerShell retrieves the Scoop installation script from its documented address. The script installs Scoop for the current user.

  • Install Dbmate through Scoop by running:
scoop install dbmate
What did Scoop install?

Scoop downloads Dbmate into your user profile. It also creates the command link that PowerShell uses.

  • Verify the Dbmate release by running:
dbmate --help
What should the help output confirm?

The help output should identify Dbmate 2.37.0. It should also display the available migration commands.

Is Dbmate still unavailable?

Close PowerShell after the installation. Start a new non-admin PowerShell session so Windows reloads your command path.

Help me check the Scoop installation and my command path.

Create the migration database

The migration exercises need their own PostgreSQL database. Keeping them inside migration_lab protects your other local databases from these schema changes.

  • Confirm that the PostgreSQL command-line client is available by running:
psql --version

What does this check prove?

The version output confirms that PowerShell can find psql. This client sends the database creation command to your local PostgreSQL server.

The next command may request your local PostgreSQL password. Your password stays hidden while you type it.

  • Replace your-postgres-username with your local PostgreSQL username before running this command:
psql -U your-postgres-username -d postgres -c "CREATE DATABASE migration_lab;"

What does this command do?

  • The -U option selects your local PostgreSQL username.
  • The -d option connects to the existing postgres database.
  • The -c option executes the database creation statement before psql exits.

You should see confirmation that PostgreSQL accepted the database command. Your isolated migration_lab database is now ready for Dbmate.

Could PostgreSQL create the database?

Confirm that your local PostgreSQL service is running. Check that your username has permission to create databases.

A message mentioning an existing database means migration_lab was created during an earlier attempt. Continue with that database.

Help me troubleshoot the database creation command.

Connect Dbmate to the project

Dbmate looks for numbered migration files under db/migrations. A DATABASE_URL entry in .env gives the CLI a consistent route to the local database.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search box.
  • Press Enter to open Visual Studio Code.

You should see the Visual Studio Code window. The project folder comes next.

  • Click File in the top menu.
  • Select Open Folder.
  • Select Desktop in the folder picker.
  • Select New Folder.
  • Enter dbmate-migration-lab as the folder name.
  • Select Select Folder to open the new folder.
  • Select Yes, I trust the authors if the Workspace Trust dialog appears.

The Explorer sidebar should show dbmate-migration-lab as the open workspace.

  • Select dbmate-migration-lab in the Explorer sidebar.
  • Select the New Folder icon.
  • Enter db as the folder name.
  • Select db in the Explorer sidebar.
  • Select the New Folder icon.
  • Enter migrations as the folder name.

You should now see db/migrations in the Explorer tree. This folder holds every numbered migration file you create.

  • Select dbmate-migration-lab in the Explorer sidebar.
  • Select the New File icon.
  • Enter .env as the file name.
  • Add the database connection template to .env by copying this line:
DATABASE_URL="postgres://your-postgres-username:your-postgres-password@127.0.0.1:5432/migration_lab?sslmode=disable"

What does this connection string do?

  • The postgres scheme selects Dbmate’s PostgreSQL driver.
  • The address 127.0.0.1:5432 points to the PostgreSQL server on your computer.
  • The path selects the migration_lab database.
  • The sslmode=disable setting configures this local connection without SSL.

Password encoding is the fiddly part of this setup. Special password characters need percent encoding so Dbmate reads the complete credential.

The populated .env file contains your local password. Keep this file on your computer.

  • Replace your-postgres-username with the username used to create migration_lab.
  • Enter your percent-encoded PostgreSQL password in place of your-postgres-password.
  • Save .env by pressing Ctrl+S.

The unsaved-change indicator should disappear from the .env tab. Your local connection details are now stored for Dbmate.

✔️ Awesome, I've got everything!

Your .env file is saved with your local credentials. Keep those populated values private.

ⓧ I'd like to double check the full code

Compare the structure of your local .env file with this template. Your saved username and password should replace the plain text placeholders.

DATABASE_URL="postgres://your-postgres-username:your-postgres-password@127.0.0.1:5432/migration_lab?sslmode=disable"

What should match?

Keep the protocol in the same position. Keep the local address unchanged.

Keep the database name unchanged. Keep the query string unchanged.

  • Click View in the Visual Studio Code menu.
  • Select Terminal to open the integrated PowerShell terminal.

The terminal prompt should end inside dbmate-migration-lab. This location lets Dbmate find both .env and db/migrations.

Before you run the connection check, do you expect Dbmate to find any migration files in the empty db/migrations folder?

  • Test the project connection by running:
dbmate status

What does this status check prove?

Dbmate loads DATABASE_URL from .env. It uses that value to connect to migration_lab.

Dbmate may create its schema_migrations tracking table during the check. That table records applied versions in later steps.

You should see an empty migration list without a connection error. That result proves the CLI can reach your project database.

Did Dbmate report a connection problem?

Confirm that the PostgreSQL service is running. Check that migration_lab exists on the local server.

Compare the username in .env with the account accepted by psql. Recheck percent encoding when your password contains special characters.

Help me troubleshoot the Dbmate connection without exposing my password.

Your migration lab is connected. Next, you’ll create the first versioned SQL file and watch it build the tasks table.

Apply Your First SQL Migration

Your local PostgreSQL lab already accepts commands from Dbmate. The next goal is a schema change you can inspect.

A migration tool becomes useful when a versioned SQL file creates something visible. You'll start with a table built entirely from one migration.

In this step, get ready to:
  • Create a numbered SQL migration that defines the tasks table.
  • Complete the migration with hand-written reverse SQL.
  • Prove the applied version against the live PostgreSQL schema.
Create the up migration

An up migration contains the SQL that moves a database schema forward. This direction creates the tasks table with its first two columns.

  • In the Visual Studio Code Explorer, select the db/migrations folder.
  • Select New File in the Explorer title bar.
  • Enter 20261103000100_create_tasks.sql as the filename.
  • Press Enter.
  • Add the following up migration to the new file:
-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(100) NOT NULL
);

What does this SQL do?

  • The -- migrate:up marker tells Dbmate where the forward SQL begins.
  • The CREATE TABLE tasks statement creates the table.
  • The id column is an automatically generated integer primary key.
  • The title column stores required task text in varchar(100).
  • Save 20261103000100_create_tasks.sql by pressing Ctrl+S.
  • Confirm the filename appears under db/migrations in the Explorer.

File in the wrong folder?

Make sure the migration file sits directly inside db/migrations. Check that its filename ends with .sql.

Help me place the migration file correctly.

Add the down migration

A down migration records how to reverse the forward change. For this version, the reversal removes the tasks table.

  • In 20261103000100_create_tasks.sql, place your cursor after the closing line of the up section.
  • Add the down section so the complete file matches this reference:
-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(100) NOT NULL
);

-- migrate:down
DROP TABLE tasks;

How do the directions work?

  • The -- migrate:up section creates the intended schema.
  • The -- migrate:down section stores the reverse operation.
  • The DROP TABLE tasks statement removes the table created by the up section.
  • Save 20261103000100_create_tasks.sql.
  • Read the up section from its marker through the table definition.
  • Read the down section from its marker through the reverse operation.

That's your first complete reversible migration. Its forward direction creates tasks. Its reverse direction removes the same table.

Do the sections look incomplete?

Check that each section starts with its migration marker. Make sure every SQL statement ends with a semicolon.

Help me check both migration directions.

Your migration file is ready for a final comparison before execution.

✔️ Awesome, I've got everything!

Your migration now has both directions. Keep the .env file from the previous step unchanged.

ⓧ I'd like to double check the full code

  • Keep the populated .env file from the previous step unchanged.
  • Compare 20261103000100_create_tasks.sql with this complete reference:
-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(100) NOT NULL
);

-- migrate:down
DROP TABLE tasks;

The up section creates the table. The down section records its exact reversal.

Prove the migration ran

Applying the file stores its numeric migration version in schema_migrations. That record links the SQL file to the live schema.

  • Switch back to the PowerShell terminal from earlier.

Before you run the migration, predict which table the up section should create.

  • Apply the pending migration by running this command:
dbmate up

What does this command do?

Dbmate reads the connection from .env. It executes the up section for each pending version.

After the SQL succeeds, Dbmate records 20261103000100 in schema_migrations.

You should see Dbmate report that version 20261103000100 ran successfully.

Migration did not apply?

Confirm PowerShell is still in the Visual Studio Code project folder. Check that .env remains in that folder.

Help me troubleshoot the migration run.

Before you inspect the table, predict which two columns PostgreSQL should show.

  • Describe tasks by running this command with your PostgreSQL username in place of your-postgres-username:
psql -U your-postgres-username -d migration_lab -c "\d tasks"

What does this inspection command do?

  • The -U option selects your PostgreSQL username.
  • The -d option selects migration_lab.
  • The -c option runs one command before exiting.
  • The \d tasks command displays the table's columns plus their types.

You should see a row for id. You should also see a row for title.

Cannot describe the table?

Check that you replaced your-postgres-username with the username you used earlier. Enter your local PostgreSQL password if PowerShell requests it.

Help me inspect the tasks table.

Before you check migration history, predict whether the first version should appear as pending or applied.

  • Check the recorded migration status by running this command:
dbmate status

What does the status prove?

Dbmate compares the migration files with the versions recorded in schema_migrations. This connects the file on disk to the version applied in PostgreSQL.

You should see 20261103000100_create_tasks.sql marked as applied. You now have a live table backed by recorded migration history.

Migration still appears pending?

Confirm the status command is running from the folder that contains .env. Check the earlier migration output for a failed SQL statement.

Help me fix the migration status.

Your first version now controls a live table. Next, you'll test what happens when an applied migration file changes.

Expose Version-Only Tracking

Your first migration created the tasks table in PostgreSQL. Dbmate recorded 20261103000100 as applied.

Dbmate uses a version-based migration history. This step tests what happens when the SQL inside an applied version changes after the database has recorded it.

In this step, get ready to:
  • Confirm that the first migration is already applied.
  • Change the title declaration inside the applied migration file.
  • Test the edited version against the real PostgreSQL table.
Confirm the applied version

The current migration status gives you a baseline for the experiment. It proves the database already recognizes the first version.

  • Confirm the recorded migration baseline by running this command:
dbmate status

What does this status prove?

Dbmate reads its migration records. It lists 20261103000100_create_tasks.sql as applied.

The database already knows this numeric version. That recorded state becomes the control for your experiment.

Edit the applied migration

The migration version stays unchanged when you edit its contents. This deliberate edit creates the test condition without adding another version.

  • Return to db/migrations/20261103000100_create_tasks.sql in Visual Studio Code.
  • Find this line:
  title varchar(100) NOT NULL

What does this line represent?

This is the exact column definition that originally ran. PostgreSQL created tasks.title with a maximum length of 100 characters.

  • Replace that line with the following declaration:
  title varchar(120) NOT NULL

What changed in the file?

The saved SQL now declares a maximum length of 120 characters. The filename still contains the same migration version.

  • Save db/migrations/20261103000100_create_tasks.sql by pressing Ctrl+S.

✔️ Awesome, I've got everything!

Your saved migration now requests varchar(120) for title. Its numeric version remains unchanged.

ⓧ I'd like to double check the full code

  • Compare your saved migration with this complete file:
-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(120) NOT NULL
);

-- migrate:down
DROP TABLE tasks;

What should match?

The up section now declares title varchar(120) NOT NULL. The down section still removes the tasks table.

Test the edited migration

The file has changed under a version that Dbmate already considers applied. Running the migration command tests whether that recorded version executes again.

Before you run it, do you think the existing tasks.title type changes when Dbmate sees edited SQL under the same version?

  • Test the edited migration by running this command:
dbmate up

What happened to the version?

This command checks whether a migration version is pending. Version 20261103000100 is already recorded as applied.

Dbmate does not replay the edited SQL under that existing version. The migration status remains applied.

The migration file now says varchar(120). Before you inspect the table, which width do you expect PostgreSQL to report?

  • Inspect the real tasks table by running this command:
psql -U your-postgres-username -d migration_lab -c "\d tasks"

What does the table prove?

PostgreSQL still reports character varying(100) for title. The actual table keeps the definition that ran when version 20261103000100 was first applied.

The file now says varchar(120), while schema_migrations still records the same numeric version. This file-to-database mismatch proves that Dbmate tracks the applied version without replaying edited SQL.

That experiment exposed Dbmate's version-only tracking exactly as planned. Next up, you'll repair the mismatch without rewriting the original database history.

Fix the Mismatch with a Forward Migration

Earlier, you proved that changing an applied SQL migration does not change the PostgreSQL table. Dbmate still sees version 20261103000100 as complete because it tracks the numeric version.

An append-only history keeps the first file faithful to what originally ran. A new forward migration carries the desired schema change as a second auditable version.

In this step, get ready to:
  • Restore the first migration so its SQL matches the schema it originally created.
  • Create the reversible migration for the wider title plus priority.
  • Verify the updated table against Dbmate's two-version history.
Restore truthful history

The first migration currently claims that it created a varchar(120) title. PostgreSQL proves that the version originally created varchar(100) instead.

  • In the Visual Studio Code file tree, select db/migrations/20261103000100_create_tasks.sql.
  • Find this line:
  title varchar(120) NOT NULL

Why is this line misleading?

This line describes the deliberate edit from the previous step. The applied database still contains varchar(100) because Dbmate did not rerun the recorded version.

  • Replace the edited line with the restored definition below:
  title varchar(100) NOT NULL

Why restore the smaller length?

The restored line records the schema that version 20261103000100 actually created. Future changes can now build on an accurate history.

  • Save db/migrations/20261103000100_create_tasks.sql.

You should now see title varchar(100) NOT NULL in the saved historical migration.

Does the file still show 120?

  • Confirm that you edited 20261103000100_create_tasks.sql inside db/migrations.
  • Save the file after replacing only the title length.

Ask for help with the historical edit: Help me restore the first migration accurately.

Create the forward migration

The second migration holds the desired change as a new version. Its down section records how to reverse that version during the rollback drill.

  • In the Visual Studio Code file tree, select the existing db/migrations folder.
  • Right-click the db/migrations folder.
  • Choose the option that creates a new file.
  • Enter 20261103000200_widen_title_and_add_priority.sql as the filename.

You should see the new blank migration beneath 20261103000100_create_tasks.sql in the file tree.

  • Fill db/migrations/20261103000200_widen_title_and_add_priority.sql by pasting this migration:
-- migrate:up
ALTER TABLE tasks
  ALTER COLUMN title TYPE varchar(120),
  ADD COLUMN priority varchar(20);

-- migrate:down
ALTER TABLE tasks
  DROP COLUMN priority,
  ALTER COLUMN title TYPE varchar(100);

How does this migration work?

  • The -- migrate:up section describes the forward change.
  • The ALTER COLUMN clause widens title to varchar(120).
  • The ADD COLUMN clause creates the nullable priority column.
  • The -- migrate:down section removes priority before returning title to varchar(100).
  • Save db/migrations/20261103000200_widen_title_and_add_priority.sql.
  • Read the -- migrate:up section from its marker through the first SQL statement.
  • Read the -- migrate:down section from its marker through the final SQL statement.

The saved file now documents one forward schema change with its hand-written reversal.

Is the new migration incomplete?

  • Confirm that both migration markers begin with two hyphens.
  • Check that each ALTER TABLE tasks statement ends with a full SQL terminator.
  • Compare the column lengths in the up section with the opposite lengths in the down section.

Get help checking the reversible SQL: Help me check my second migration.

✔️ Awesome, I've got everything!

Your historical migration is truthful again. Your second migration now records the forward fix with its reversal.

ⓧ I'd like to double check the full code

  • Compare your three project files with these complete references.
DATABASE_URL="postgres://your-postgres-username:your-postgres-password@127.0.0.1:5432/migration_lab?sslmode=disable"

Why does the connection file stay unchanged?

The existing .env file still points Dbmate at migration_lab. This step changes the schema history instead of the database connection.

-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(100) NOT NULL
);

-- migrate:down
DROP TABLE tasks;

What should the first migration record?

The up section records the original tasks table with a varchar(100) title. The down section records the complete table removal.

-- migrate:up
ALTER TABLE tasks
  ALTER COLUMN title TYPE varchar(120),
  ADD COLUMN priority varchar(20);

-- migrate:down
ALTER TABLE tasks
  DROP COLUMN priority,
  ALTER COLUMN title TYPE varchar(100);

What should the second migration record?

The up section advances the schema to the desired state. The down section returns the schema to the state created by the first migration.

Apply the new version

Dbmate now sees one applied version plus one pending version. Applying the pending file should align the migration history with the live table.

  • Switch back to the Windows PowerShell panel from earlier.
  • Use your existing local PostgreSQL username in place of your-postgres-username when you reach the third command.

Before you run this, make a prediction about which status check will mark the second migration as applied.

  • Run the migration workflow by executing these commands in order:
dbmate status
dbmate up
psql -U your-postgres-username -d migration_lab -c "\d tasks"
dbmate status

What just happened?

  • The first status check proves that Dbmate discovered the second migration as pending.
  • The up command executes the second migration's forward SQL.
  • The psql check reads the real table definition from PostgreSQL.
  • The final status check confirms that schema_migrations records both numeric versions.

The table description now reports character varying(120) for title. It also lists the nullable priority column.

The final status marks both migration files as applied. That is the mismatch repaired. Your files now tell the same story as PostgreSQL.

Still seeing the old table shape?

  • Confirm that the new migration is saved inside db/migrations.
  • Check that its numeric version is 20261103000200.
  • Confirm that PowerShell still points to the Visual Studio Code project folder.

Get help tracing the pending migration: Help me find why the second Dbmate migration did not update tasks.

Your migration history is truthful again. Next, you will test the reverse path by rolling back the latest version.

Roll Back and Reapply Safely

Your second migration now produces the intended PostgreSQL schema. A reliable pipeline also needs a proven route backward when its latest change must be reversed.

Dbmate cannot invent reverse SQL. In this step, you will inspect your down section before using it to roll back the latest version.

In this step, get ready to:
  • Review the hand-written SQL that reverses the latest migration.
  • Roll back the latest version to verify the earlier schema.
  • Reapply the migration to restore the intended schema.
Review the reverse SQL

A down migration is a written rollback plan. Its statements must reverse the up migration in a deliberate order.

  • Click 20261103000200_widen_title_and_add_priority.sql in the Explorer sidebar in Visual Studio Code.
  • Scroll to the -- migrate:down marker.
  • Read each reverse operation from top to bottom.
  • Compare your down section with this reference:
-- migrate:down
ALTER TABLE tasks
  DROP COLUMN priority,
  ALTER COLUMN title TYPE varchar(100);

What Will the Down Section Reverse?

  • The DROP COLUMN priority operation removes the column introduced by the up section.
  • The ALTER COLUMN title TYPE varchar(100) operation restores the original title width.
  • Identify the column that disappears during the rollback.
  • Identify the data type that returns during the rollback.
Roll back the latest version

Dbmate reads the latest applied migration's down section when you request a rollback. The database becomes the evidence that your reverse SQL works.

  • Return to the Windows PowerShell terminal from earlier.

Before you run the rollback, which column do you expect to disappear from tasks?

  • Roll back the latest applied migration by running:
dbmate rollback

What Does This Command Do?

Dbmate executes the down section from the latest applied migration. It removes that version from the migration history after the reverse SQL succeeds.

You'll see Dbmate report that the latest migration was rolled back.

  • Replace your-postgres-username in the command below with the same local PostgreSQL username you used earlier.
  • Describe the rolled-back tasks table by running:
psql -U your-postgres-username -d migration_lab -c "\d tasks"

What Does This Check Show?

  • The -U option selects your local PostgreSQL username.
  • The -d migration_lab option selects the practice database.
  • The \d tasks command displays the table's columns and data types.

The priority column is absent. The title row reports character varying(100).

  • Check the recorded migration state by running:
dbmate status

Why Check the Migration Status?

The table description proves that PostgreSQL reversed the schema change. The status output proves that Dbmate also updated its version history.

The second migration now appears as pending. The first migration remains applied.

Rollback State Looks Wrong?

If priority still appears, confirm that the DATABASE_URL in .env points to migration_lab.

If the second version still appears as applied, confirm that PowerShell remains in the Visual Studio Code project folder where Dbmate can read .env.

Help me diagnose my rollback result

That is the reverse path proven. The original table remains usable after the latest version is removed.

Reapply the intended schema

The rollback was a controlled test of your reverse SQL. Your intended schema still needs the wider title and priority column from the second migration.

Before you reapply the migration, do you expect Dbmate to run the first version again or only the pending second version?

  • Reapply the pending migration by running:
dbmate up

What Does Reapplying Do?

Dbmate skips the version that remains applied. It executes the up section from the pending second migration.

You'll see Dbmate apply version 20261103000200 again.

Before the final check, what title width and columns should the restored table contain?

  • Replace your-postgres-username in the first command below with your local PostgreSQL username.
  • Verify the restored table and migration history by running these commands:
psql -U your-postgres-username -d migration_lab -c "\d tasks"
dbmate status

What Does the Final Check Prove?

  • The table description confirms that the up section restored the intended PostgreSQL schema.
  • The status output confirms that both migration versions are recorded as applied.

You'll see the priority column again. The title row reports character varying(120).

Dbmate status marks both migration files as applied.

Schema Did Not Return?

If priority is still absent, check that 20261103000200_widen_title_and_add_priority.sql appears as pending before you run the up command.

If title remains at character varying(100), compare the migration's up section with the full-file reference below.

Help me restore the migrated schema

You have now proved both directions of the migration. The same versioned files return the database to its intended state.

The rollback drill changes database state only. Your project files should still match these references.

✔️ Awesome, I've got everything!

Your connection file and both migration files are unchanged after the rollback drill.

ⓧ I'd like to double check the full code

  • Compare your .env file with this reference:
DATABASE_URL="postgres://your-postgres-username:your-postgres-password@127.0.0.1:5432/migration_lab?sslmode=disable"

What Does This File Control?

The DATABASE_URL value tells Dbmate how to connect to the local migration_lab database.

  • Compare your first migration with this reference:
-- migrate:up
CREATE TABLE tasks (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title varchar(100) NOT NULL
);

-- migrate:down
DROP TABLE tasks;

Why Keep the First Version Unchanged?

The first migration preserves the original varchar(100) definition. It remains a truthful record of the schema that originally ran.

  • Compare your second migration with this reference:
-- migrate:up
ALTER TABLE tasks
  ALTER COLUMN title TYPE varchar(120),
  ADD COLUMN priority varchar(20);

-- migrate:down
ALTER TABLE tasks
  DROP COLUMN priority,
  ALTER COLUMN title TYPE varchar(100);

How Do Both Directions Match?

  • The up section widens title before adding priority.
  • The down section removes priority before restoring the original title width.

Secret mission

Add a Due Date Migration

Create a third reversible migration that adds a due date to every task. Test both directions while preserving the two applied migration files as truthful history.

Clean Up Your Resources

Clean Up Your Resources

All resources stay on your Windows machine at no ongoing cost. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Local PostgreSQL database migration_lab, including the tasks table and Dbmate's schema_migrations history.
  • Local .env file containing the DATABASE_URL connection value.
  • Migration files db/migrations/20261103000100_create_tasks.sql, db/migrations/20261103000200_widen_title_and_add_priority.sql, and db/migrations/20261103000300_add_due_date.sql in the project folder open in Visual Studio Code.

Keep everything running

No action is needed while you continue experimenting with the migration pipeline. The database and migration files remain ready for another schema change.

  • Keep the local PostgreSQL service running.
  • Keep the current Visual Studio Code project folder in place.
  • Keep .env private because it contains your local database credentials.

Pause - I'll come back to this later

Pausing stops the local PostgreSQL service to free system resources. Your database and project files remain intact for your return.

  • Stop the local PostgreSQL service using the service controls supplied with your installation.
  • Close Visual Studio Code.
  • Start the local PostgreSQL service with the same controls when you return.
  • Return to the same project folder in Visual Studio Code.
  • Confirm the migration lab is ready again by running this command:
dbmate status

What Should I See?

Dbmate should connect through .env and show all three migration versions as applied. Your paused migration lab is ready for its next schema change.

Status Cannot Connect?

  • Confirm the local PostgreSQL service is running.
  • Confirm Visual Studio Code has the same project folder open.
  • Confirm the project folder still contains .env.

Help me reconnect the paused migration lab.

Delete - I don't want to use this again

This permanently removes the practice database and its local migration files. Your PostgreSQL installation, Dbmate, and Scoop stay available for future work.

  • Prepare the command below by replacing your-postgres-username with the PostgreSQL username you used earlier.
  • Delete migration_lab by running the prepared command in the Windows PowerShell terminal:
psql -U your-postgres-username -d postgres -c "DROP DATABASE migration_lab;"

What Does This Command Remove?

The command connects to postgres so PostgreSQL can remove migration_lab. Removing the database also removes its tasks table and schema_migrations history.

  • Return to pgAdmin.
  • Refresh the local database list.

You should no longer see migration_lab in the database list. That's the database cleaned up: PostgreSQL no longer holds the task schema or migration history.

Database Not Deleted?

  • Close any pgAdmin query windows connected to migration_lab.
  • Stop any Dbmate command that still targets migration_lab.
  • Run the database deletion command again.

Help me diagnose the database deletion.

  • Return to the project window in Visual Studio Code.
  • Show the current project folder in Windows File Explorer using the folder's context menu.
  • Close Visual Studio Code.
  • Delete the revealed project folder in Windows File Explorer.

You should no longer see the project folder in Windows File Explorer. Your local migration files and .env file are now removed.

Nice Work!

Nice Work!

You did it! Your Dbmate pipeline now controls a PostgreSQL schema through readable SQL migration files.

You've learned how to:

  • Build a language-independent migration pipeline that creates the tasks table from numbered SQL files.
  • Diagnose version-only tracking by proving that an edited applied migration leaves the PostgreSQL schema unchanged.
  • Repair migration history with a forward migration. Prove its rollback and reapply workflow against the real table.
  • Secret Mission: Design an independent reversible migration that adds due_date without modifying either earlier migration.

Ready to quiz yourself?