Evolve a Task Database with dbmate

Build and safely evolve a SQLite task database with reversible SQL migrations.

Introduction

30 Second Summary

Database changes can feel risky because one mistake may damage valuable information. Without a recorded path through each change, recovery becomes guesswork.

In this project, you will build a local task database from ordered plain SQL schema migrations using dbmate with SQLite. You will prove that a planned failure leaves no half-applied schema behind.

What You'll Build

Picture your Windows PowerShell terminal showing a complete task database after a planned failure plus a full rollback cycle.

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

A one-command database build that fills your local SQLite file with sample tasks plus notes ready to query.
A visible migration history paired with a generated schema snapshot for inspecting every applied change.
A failure-and-recovery demo proving that a broken transactional migration leaves no partial table behind.
Secret Mission: Add a reversible relationship between tasks and projects.

Are there any prerequisites?

You only need a Windows computer. The guide covers every required installation before the database work begins.

Before We Start

Before any tools enter the picture, this step locks in the database you are building and why its changes need a safe route back. Your goal is a local SQLite task database whose ordered Dbmate SQL migrations can apply schema changes and restore the earlier schema.

Set Up the Windows Migration Lab

A dependable migration lab needs a consistent place to edit SQL. It also needs a reliable terminal for running commands.

You will install Visual Studio Code first. Scoop will then install dbmate 2.37.0. It will also install the SQLite CLI 3.54.1.

In this step, get ready to:

Install Visual Studio Code from Windows PowerShell.
Install Scoop plus the pinned database tools.
Create the dbmate-task-lab workspace and verify both tools.

Install Visual Studio Code

Visual Studio Code will hold every migration file you create. Its integrated terminal keeps your editor beside the commands that apply those migrations.

Press the Windows key to open search.
Type PowerShell into the search field.
Press Enter to open a regular non-admin PowerShell window.
Install Visual Studio Code by running this command:

winget install Microsoft.VisualStudioCode

What does this command do?

The winget command asks Windows Package Manager to install an application.
The Microsoft.VisualStudioCode package ID selects the official Visual Studio Code package.

The PowerShell output should report that Visual Studio Code installed successfully.

Visual Studio Code installation failed?

Confirm that Windows Package Manager is available on your computer. Check that the PowerShell window has an internet connection.

Help me fix the Visual Studio Code installation.

Close the PowerShell window so the new code command can be added to your terminal environment.
Press the Windows key to reopen search.
Type PowerShell into the search field.
Press Enter to open a fresh regular non-admin PowerShell window.

Install Scoop and the pinned tools

Scoop installs command-line tools for your Windows user account. The pinned package versions keep every command in this project reproducible.

Why dbmate with SQLite?

dbmate keeps each schema change in a plain SQL file. This gives you a short path from reading a migration to applying it.

SQLite stores the database in one local file. You can practise migration history without configuring a database server.

Confirm that you are using the regular non-admin PowerShell window from the previous substep.
Allow local scripts for your Windows account by running this command:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

What does this command do?

The CurrentUser scope limits the policy change to your Windows account.
The RemoteSigned policy allows local scripts to run.

Confirm the policy change if PowerShell asks for approval.

PowerShell should return to its prompt after the policy is applied.

Execution policy did not change?

Check that the command includes the current-user scope. A managed computer may enforce an organisation-wide policy that your account cannot change.

Help me diagnose the PowerShell execution policy problem.

Install Scoop for your Windows account by running this command:

Invoke-RestMethod -Uri https://get.scoop.sh | Invoke-Expression

What does this command do?

PowerShell downloads the Scoop installer from its official address.
The pipeline passes that installer to PowerShell for execution.

The installer output should confirm that Scoop is ready for your user account.

Scoop installation failed?

Confirm that you used a regular non-admin PowerShell window. Check that the execution policy command completed before you ran the installer.

Help me fix the Scoop installation.

Install the pinned database tools by running these commands:

scoop install dbmate@2.37.0
scoop install sqlite@3.54.1

What do these commands install?

The first command installs dbmate 2.37.0 for creating and applying migrations.
The second command installs SQLite CLI 3.54.1 for querying the database and generating the schema snapshot.

Match the PowerShell result to one of the outcomes below.

✔️ Both pinned installs completed

That is the tool installation complete. dbmate and the SQLite CLI are now available to your Windows account.

ⓧ I see an older version

An older package can produce different output from the commands used in this project. Reapply the project pins before continuing.

Install the required package versions by running these commands:

scoop install dbmate@2.37.0
scoop install sqlite@3.54.1

Why repeat the pinned installs?

The version suffixes tell Scoop which releases this project expects. This keeps the migration workflow aligned with the verified tool behaviour.

PowerShell should finish with dbmate 2.37.0 and SQLite CLI 3.54.1 installed.

ⓧ PowerShell cannot run Scoop

The Scoop installation did not become available in this PowerShell session. Reinstall it before adding the pinned tools.

Close the current PowerShell window.
Open a fresh regular non-admin PowerShell window through Windows search.
Install Scoop again by running this command:

Invoke-RestMethod -Uri https://get.scoop.sh | Invoke-Expression

What should happen now?

The fresh session applies your current-user execution policy. Scoop should finish installing for the same Windows account.

Install the pinned tools by running these commands:

scoop install dbmate@2.37.0
scoop install sqlite@3.54.1

What should the output confirm?

The installation output should identify the selected dbmate and SQLite package versions. Both commands should finish without a package failure.

Scoop is still unavailable?

Confirm that each PowerShell window uses the same Windows account. Check that the Scoop installer completed before running the package commands.

Help me restore the Scoop command.

Create the workspace and verify the tools

The workspace gives every migration a predictable home. Keeping it on your Desktop also makes the local database easy to find later.

Move to your Desktop by running this command:

Set-Location -Path "$HOME\Desktop"

What does this command do?

PowerShell changes its current location to the Desktop folder inside your Windows home directory. The workspace you create next will appear there.

Create the dbmate-task-lab folder by running these commands:

mkdir dbmate-task-lab
cd dbmate-task-lab

What do these commands do?

The first command creates the dbmate-task-lab folder on your Desktop.
The second command moves PowerShell into that folder.

The next command launches Visual Studio Code with the current folder as its workspace.

Open the workspace in Visual Studio Code by running this command:

code .

What does this command do?

The code command launches Visual Studio Code. The dot selects your current dbmate-task-lab folder.

You should see dbmate-task-lab as the open workspace in Visual Studio Code.

The code command is unavailable?

Confirm that you closed the original PowerShell window after installing Visual Studio Code. A fresh terminal session is required before the new command becomes available.

Help me restore the Visual Studio Code command.

Select Terminal from the Visual Studio Code menu bar.
Select New Terminal.
Confirm that the integrated terminal uses PowerShell.
Select PowerShell from the terminal profile dropdown if another shell opens.

Before you run the checks, what should each tool print if the pinned setup is ready?

Verify both tools from the integrated terminal by running these commands:

dbmate --help
sqlite3 --version

What do these checks prove?

The dbmate help command proves that PowerShell can locate and start the migration tool.
The SQLite version command proves that PowerShell can locate the database CLI.

You should see dbmate's command list. The SQLite output should begin with 3.54.1.

Your Windows migration toolchain is ready. The empty dbmate-task-lab workspace can now become a versioned task database.

A verification command is unavailable?

Close Visual Studio Code after the Scoop installations finish. Reopen the workspace from a fresh PowerShell window so the integrated terminal receives the updated command paths.

Help me diagnose the missing database command.

Your editor, terminal, and migration tools now share one workspace. Next, you will turn that empty folder into a working SQLite database with its first reversible migration.

Create Your First Migration

Your Windows migration lab is ready. You proved that Dbmate and the SQLite CLI are available from the workspace terminal.

A schema migration becomes useful when one command turns versioned SQL into a repeatable database state. In this step, you create that first state with a tasks table and two sample rows.

In this step, get ready to:

Configure the local SQLite database address in .env.
Create a reversible migration for the tasks table.
Apply the migration and inspect the resulting database state.

Configure the local database address

Dbmate reads DATABASE_URL from a .env file in the current folder. The SQLite address keeps the database inside your existing dbmate-task-lab workspace.

Click the New File icon in the Explorer sidebar.
Name the file .env.
Set the database address by pasting this configuration:

DATABASE_URL="sqlite:db/tasks.sqlite3"

What does this configuration do?

The DATABASE_URL setting tells Dbmate which database to manage.
The sqlite: prefix selects Dbmate's SQLite driver.
The db/tasks.sqlite3 path places the database inside the workspace.

Save .env.
Confirm the editor shows the database address on one line.

Your workspace now has the connection information Dbmate needs to create the local database.

Dbmate cannot find the database address?

Check that .env sits directly inside dbmate-task-lab.
Check that the filename starts with a dot and has no extra file extension.
Check that the database address includes the double quotes shown above.

Help me fix Dbmate not loading my .env file.

✔️ Awesome, I've got everything!

Your .env file is saved with the local SQLite database address.

ⓧ I'd like to double check the full code

DATABASE_URL="sqlite:db/tasks.sqlite3"

What should match?

Compare the filename and database address character by character. The path must point to db/tasks.sqlite3.

Create the tasks migration

Dbmate stores migrations as ordered SQL files with separate sections for applying and reversing a change. The leading numeric version determines the order in which pending files run.

Generate the first migration from the existing PowerShell terminal by running this command:

dbmate new create_tasks_table

What does this command do?

The new command creates the default migration folders when needed.
The create_tasks_table description becomes part of the generated SQL filename.
The generated file contains sections for the up migration and the down migration.

You should see a new SQL file inside db/migrations with a numeric prefix and the create_tasks_table description.

Migration file missing?

Confirm the terminal prompt shows the dbmate-task-lab folder.
Confirm .env is saved before running the command again.
Expand the db folder in the Explorer sidebar to reveal the migrations folder.

Help me find the migration created by dbmate new create_tasks_table.

Expand the db folder in the Explorer sidebar.
Expand the migrations folder.
Rename the generated SQL file to 20261103000100_create_tasks_table.sql.

The Explorer sidebar should now show db/migrations/20261103000100_create_tasks_table.sql.

Select 20261103000100_create_tasks_table.sql in the Explorer sidebar.
Replace its generated contents with this migration:

-- migrate:up
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0 CHECK (completed IN (0, 1))
);

INSERT INTO tasks (title) VALUES
  ('Learn dbmate'),
  ('Ship a safe migration');

-- migrate:down
DROP TABLE tasks;

What does this migration do?

The -- migrate:up section describes the change Dbmate applies.
The tasks table stores an integer ID and a required title.
The completed column defaults to 0 and accepts only 0 or 1.
The insert adds two rows that make the first database state visible.
The -- migrate:down section removes the table when this migration is rolled back.

Save db/migrations/20261103000100_create_tasks_table.sql.
Confirm the file contains both migration sections.

The migration file now contains a complete forward change and its matching reversal.

Migration code looks different?

Check that the filename starts with exactly 20261103000100.
Check that each SQL statement ends with the punctuation shown above.
Check that the down section appears after the sample task rows.

Help me compare my first Dbmate migration with the expected tasks migration.

✔️ Awesome, I've got everything!

Your first timestamp-versioned migration is saved with matching up and down sections.

ⓧ I'd like to double check the full code

-- migrate:up
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0 CHECK (completed IN (0, 1))
);

INSERT INTO tasks (title) VALUES
  ('Learn dbmate'),
  ('Ship a safe migration');

-- migrate:down
DROP TABLE tasks;

What should match?

Compare the table definition and sample rows with your saved migration. Both migration section markers must remain in the file.

Apply and inspect the migration

Dbmate applies each pending migration inside an atomic transaction by default. It also records the applied version in schema_migrations.

Before you run the migration, make a quick prediction about which database artifacts Dbmate is about to create.

Create the database and apply the pending migration by running this command:

dbmate up

What does this command do?

Dbmate creates the SQLite database when it does not exist.
Dbmate applies pending migrations in numeric order.
Dbmate records version 20261103000100 in its migration history.
Dbmate writes the resulting schema to db/schema.sql when the SQLite CLI is available.

You should see output that includes Applying and Applied. You should also see output showing that the schema snapshot was written.

Migration did not apply?

Check that the terminal is still inside dbmate-task-lab.
Check that .env contains the exact SQLite address from the first substep.
Check the migration file for missing punctuation or misspelled migration markers.

Help me diagnose why dbmate up did not apply my first migration.

Expand the db folder in the Explorer sidebar.
Select schema.sql.
Confirm the snapshot contains the CREATE TABLE tasks definition.

The generated snapshot reflects the database after migration 20261103000100 was applied.

Before you run the final checks, predict whether the migration history and both sample tasks now agree with the SQL file.

Inspect the migration history and task rows by running these commands:

dbmate status
sqlite3 db/tasks.sqlite3 "SELECT id, title, completed FROM tasks;"

How do these checks work?

The first command shows whether migration 20261103000100 is applied.
The second command reads the ID and title from each task row.
The same query also shows the default completion value stored for each task.

The status output should show the first migration as applied. The query output should show Learn dbmate and Ship a safe migration with completion values of 0.

Tasks missing from the query?

Check that the migration status shows version 20261103000100 as applied.
Check that the query targets db/tasks.sqlite3.
Check that the migration contains both sample insert values.

Help me understand why my applied migration does not show both tasks rows.

That is your first migration complete: your database can now be rebuilt from versioned SQL.

Your task database now has a migration history and a generated schema snapshot. Next, you will add task notes without discarding the rows you already created.

Add Task Notes

Your first dbmate migration turned plain SQL into a working SQLite task database. Those two task rows give you state worth protecting.

Rebuilding the database for every schema change would erase that state. This migration adds notes while preserving the tasks already stored.

In this step, get ready to:

Create the fixed-version task-notes migration.
Define a reversible task_notes table with a sample note.
Prove the new note exists without losing the original tasks.

Create the task-notes migration

The leading number controls the order in migration history. A fixed version keeps this lab reproducible.

This step adds one migration file. Your .env file and first migration remain unchanged.

Return to the integrated Windows PowerShell terminal.
Create the starter migration by running this command:

dbmate new add_task_notes

What does this command do?

The command creates a timestamped SQL file inside db/migrations. Dbmate includes forward and reverse migration sections in the new file.

Expand db/migrations in the Explorer sidebar.
Find the new file whose name ends with _add_task_notes.sql.
Right-click the generated migration file.
Choose the rename option from its context menu.
Enter 20261103000200_add_task_notes.sql as the new filename.
Press Enter.

You should now see 20261103000200_add_task_notes.sql beside your first migration in the Explorer sidebar.

Can't find the new migration?

Confirm the terminal is still inside the dbmate-task-lab folder.
Check the terminal output for the path of the generated migration file.
Refresh the Explorer sidebar if the new file is missing from the list.

Help me create and rename the task-notes migration.

Add the reversible note schema

The up section moves the schema forward. The down section makes the same change reversible.

Select db/migrations/20261103000200_add_task_notes.sql in the Explorer sidebar.
Replace the generated contents by pasting the code below:

-- migrate:up
CREATE TABLE task_notes (
  id INTEGER PRIMARY KEY,
  task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

INSERT INTO task_notes (task_id, body)
VALUES (1, 'Keep every schema change reversible.');

-- migrate:down
DROP TABLE task_notes;

What does this migration do?

The -- migrate:up section contains the changes dbmate applies when the database moves forward.
The task_notes table stores notes linked to existing tasks.
The task_id column is a foreign key that references tasks(id).
The ON DELETE CASCADE rule removes an associated note when its parent task is deleted.
The sample insert links one note to task ID 1.
The -- migrate:down section removes the note table during a rollback.

Save db/migrations/20261103000200_add_task_notes.sql.
Apply the saved migration by running this command:

dbmate up

What does this command do?

Dbmate finds the pending migration and applies its up section inside a transaction.

After the migration succeeds dbmate records version 20261103000200. It also regenerates the schema snapshot in db/schema.sql.

You should see the second migration complete successfully. The terminal should also report that the schema file was updated.

Migration did not apply?

Check that the migration filename is exactly 20261103000200_add_task_notes.sql.
Confirm the file contains both migration section markers.
Compare every parenthesis and quote with the reference below.

Help me fix the task-notes migration.

✔️ Awesome, I've got everything!

Good work. Your second migration now adds notes without rebuilding the database.

ⓧ I'd like to double check the full code

Compare your complete migration with this reference:

-- migrate:up
CREATE TABLE task_notes (
  id INTEGER PRIMARY KEY,
  task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

INSERT INTO task_notes (task_id, body)
VALUES (1, 'Keep every schema change reversible.');

-- migrate:down
DROP TABLE task_notes;

What should match?

The up section creates task_notes and inserts one sample row. The down section drops only task_notes.

Prove the existing data survived

An applied migration should add the new capability while preserving the database state you already trust. You can prove both outcomes from the terminal and generated schema.

Before you check, predict whether dbmate now reports one applied migration or two.

Check the migration history by running this command:

dbmate status

What does this command show?

Dbmate compares the migration files with the versions stored in its migration table. This reveals which migrations are applied and which remain pending.

You should see both 20261103000100 and 20261103000200 reported as applied.

Query the new task note by running this command:

sqlite3 db/tasks.sqlite3 "SELECT task_id, body FROM task_notes;"

What does this query check?

The query reads the task reference and note text from task_notes. A returned row proves the new table contains its sample data.

You should see 1|Keep every schema change reversible. in the terminal. That result proves the second migration added a note for task ID 1.

Use the PowerShell terminal history to rerun the tasks query from the previous step.

You should still see Learn dbmate and Ship a safe migration in the results. Your original data survived the schema change.

Select db/schema.sql in the Explorer sidebar.
Confirm the file contains a task_notes table definition.

You should see table definitions for both tasks and task_notes in the generated snapshot.

Can't see the task note?

Confirm the status output reports version 20261103000200 as applied.
Check that the query uses task_notes with the underscore included.
Confirm the terminal is still inside the dbmate-task-lab folder.

Help me find the missing task note.

Your database now supports task notes while keeping every original task intact. Next, you will test how a transaction protects the schema when a migration fails.

Test a Safe Migration Failure

The second applied migration added task notes. Your original tasks stayed intact.

Successful migrations can hide the risk of a later statement failing after earlier schema work has started. This step tests whether dbmate leaves a partial change behind.

In this step, get ready to:

Create a third migration with a deliberate failure.
Run the migration to trigger the planned error.
Prove the failed migration left the database schema unchanged.

Create the failing projects migration

A migration file groups the forward schema change with the instructions that reverse it. This third migration also contains one invalid statement that creates the controlled failure.

Generate the third migration template from the integrated PowerShell terminal by running:

dbmate new create_projects_table

What does this command create?

The command creates a timestamped SQL migration in db/migrations. Its filename ends with create_projects_table.sql.

The template includes separate -- migrate:up and -- migrate:down sections. These sections define the forward change and its reversal.

Confirm the generated migration appears in the VS Code file list under db/migrations.

Migration template missing?

Confirm the integrated terminal is still inside the dbmate-task-lab folder.
Check that .env remains inside the same folder.
Run the migration command again after correcting the terminal location.

Help me find why dbmate did not create the migration file.

Right-click the generated migration in the VS Code file list.
Use the file list option for renaming the file to 20261103000300_create_projects_table.sql.
Confirm the file list now shows db/migrations/20261103000300_create_projects_table.sql.

Good progress. The fixed numeric version places this migration after the two versions already applied.

Select db/migrations/20261103000300_create_projects_table.sql from the VS Code file list.
Replace the generated contents with this intentionally failing migration:

-- migrate:up
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO projects (name)
VALUES ('Migration Lab');
INSERT INTO missing_table (name) VALUES ('force rollback');

-- migrate:down
DROP TABLE projects;

What does this migration attempt?

The -- migrate:up section creates projects with a numeric primary key and a unique project name.
The first insert adds Migration Lab to the new table.
The next insert targets the absent missing_table table to trigger the planned failure.
The -- migrate:down section defines how a successfully applied projects migration can remove its table.

Save db/migrations/20261103000300_create_projects_table.sql.
Confirm the saved file contains the invalid insert immediately after the valid project insert.

Migration contents look different?

Check that the filename begins with 20261103000300.
Confirm the controlled failure targets missing_table.
Compare your saved file with the full-file reference below.

Help me compare my failing projects migration.

✔️ Awesome, I've got everything!

Your saved third migration now contains the projects table change and the controlled failure.

ⓧ I'd like to double check the full code

-- migrate:up
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO projects (name)
VALUES ('Migration Lab');
INSERT INTO missing_table (name) VALUES ('force rollback');

-- migrate:down
DROP TABLE projects;

What should match?

Compare every line with your saved migration. The invalid insert must appear after the valid project insert so the failure happens after earlier schema work begins.

Run the controlled failure

The migration begins with valid table creation and valid data insertion. Its final forward statement targets a table that does not exist.

Before you run this, do you expect the earlier projects changes to remain after the later statement fails?

Attempt to apply the pending migration by running:

dbmate up

This failure is intentional

Your terminal reports a migration error that references missing_table. The third migration does not finish applying.

Dbmate executes the migration inside a transaction. The failed statement causes the full transaction to roll back.

This all-or-nothing behavior is called atomicity. It removes the attempted table and its sample project together.

Did the migration succeed instead?

Return to db/migrations/20261103000300_create_projects_table.sql.
Confirm the file contains INSERT INTO missing_table (name) VALUES ('force rollback');.
Save the file before running the migration again.

Help me restore the intended migration failure.

Prove the schema stayed intact

The error shows that one statement failed. Migration history and the database table list now reveal whether the earlier statements survived.

Before you check, do you expect version 20261103000300 to appear as applied or pending?

Inspect the migration history by running:

dbmate status

What does the status prove?

Versions 20261103000100 and 20261103000200 remain applied. Version 20261103000300 remains pending.

The failed migration was never added to schema_migrations. Its source file remains available for the repair in the next step.

Migration history checks dbmate's record. The SQLite table list checks the database schema itself.

List the tables stored in the task database by running:

sqlite3 db/tasks.sqlite3 ".tables"

What does the table list prove?

You should see schema_migrations, task_notes, and tasks. You should not see projects.

The failed transaction removed the attempted table creation. No Migration Lab row can remain without that table.

Switch back to the existing db/schema.sql tab.
Confirm the schema snapshot still includes tasks and task_notes.
Confirm the schema snapshot does not include projects.

That is the proof you were looking for. The failed migration left your two successful schema changes untouched.

Your database survived a failure after valid schema work had started. Next, you will repair the pending migration and test its rollback path.

Repair and Reapply Projects

The previous run proved that dbmate wraps a schema migration in a transaction. That atomic failure protected your existing tasks when the projects migration broke.

The migration remains pending. You will repair it first. You will then prove that the projects change works in both directions.

In this step, get ready to:

Remove the deliberate failing statement from the pending projects migration.
Apply the repaired migration to create the projects table.
Roll back the projects migration before reapplying it.

Repair and apply the migration

The valid projects SQL already sits above the deliberate failure. Removing that one failing statement gives the pending migration a complete path through its up section.

In the VS Code file sidebar from earlier, select db/migrations/20261103000300_create_projects_table.sql.
Locate the line INSERT INTO missing_table (name) VALUES ('force rollback');.
Delete the deliberate failing line.
Compare the repaired migration with this complete file:

-- migrate:up
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO projects (name)
VALUES ('Migration Lab');

-- migrate:down
DROP TABLE projects;

What Does This Migration Do?

The -- migrate:up section creates projects with a numeric primary key.
The name column requires unique text. The sample insert adds Migration Lab.
The -- migrate:down section removes the table during a rollback.

Save db/migrations/20261103000300_create_projects_table.sql.

✔️ Awesome, I've got everything!

Your repaired migration now contains the projects table creation. It also contains its sample row and rollback section.

ⓧ I'd like to double check the full code

-- migrate:up
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO projects (name)
VALUES ('Migration Lab');

-- migrate:down
DROP TABLE projects;

Before you apply the repair, do you expect the pending migration to succeed this time? Hold that prediction while you test it.

Return to the integrated PowerShell terminal.
Apply the repaired migration by running:

dbmate up

What Does This Command Do?

The command applies every pending migration in numeric order. It also regenerates db/schema.sql after the database changes.

You should see output for Applying and Applied. The schema-writing output confirms that your SQLite snapshot was refreshed.

Migration Still Failing?

Confirm that the statement referencing the absent table is completely removed from the migration.
Check that the sample project insert still ends with a semicolon.
Save the migration before running the command again.

Ask for help with the repaired migration error.

Inspect the migration history by running:

dbmate status

What Does Migration Status Prove?

Migration status reads dbmate's recorded history. The projects migration should now appear as applied beside the first two migrations.

The repaired version 20261103000300 should now be applied. The database has accepted the projects change as one complete migration.

Select db/schema.sql in the VS Code file sidebar.

You should find CREATE TABLE projects in the generated schema snapshot. This proves the snapshot now reflects the repaired database schema.

Return to the integrated PowerShell terminal.
List the current database tables by running:

sqlite3 db/tasks.sqlite3 ".tables"

What Does the Table List Show?

The SQLite table list reads the schema stored in db/tasks.sqlite3. It provides a direct check that the projects table exists.

You should see projects beside tasks and task_notes. You may also see schema_migrations, which stores the applied migration versions.

Roll back the projects migration

A reversible migration needs a working down section. Rolling back the latest migration tests whether DROP TABLE projects removes only the new projects schema.

Before you run the rollback, which tables do you expect to remain? Hold that table list in mind before checking the result.

Reverse the latest applied migration by running:

dbmate rollback

What Does Rollback Do?

The rollback command runs the down section of the most recent applied migration. It then regenerates db/schema.sql from the rolled-back database.

The projects migration is pending again. The existing task and note migrations remain applied because the rollback reversed only the latest version.

Check which tables remain by running:

sqlite3 db/tasks.sqlite3 ".tables"

What Did the Down Migration Remove?

The table list now reads the database after the rollback. The absence of projects proves that the down section completed.

You should still see tasks and task_notes. Their continued presence proves that the rollback preserved the earlier schema.

Projects Still Listed?

Confirm that the projects migration contains DROP TABLE projects; below -- migrate:down.
Check the rollback output for a message describing why the down section stopped.

Ask for help with the projects rollback.

Reapply and verify the final database

The rollback proved that the projects change can move backward safely. Reapplying the same pending file now tests the forward path one final time.

Before you reapply it, do you expect the projects table to return without changing the task tables? Keep that expected result in mind.

Reapply the pending projects migration by running:

dbmate up

What Does Reapplying Prove?

Dbmate runs the repaired up section again from the same migration file. A successful application proves that one migration can move backward before moving forward again.

You should see the projects migration apply successfully again. The generated schema snapshot should also be refreshed.

Confirm the final migration history by running:

dbmate status

What Should the Final Status Show?

The final status should show versions 20261103000100, 20261103000200, and 20261103000300 as applied.

All three migrations should now be applied. The migration history has returned to the repaired end state.

Confirm the final table set by running:

sqlite3 db/tasks.sqlite3 ".tables"

What Should the Final Database Contain?

The final table list checks the live database after the reapplication. It should include all three project tables.

You should see projects, tasks, and task_notes. The earlier task data and note data remain intact.

Return to db/schema.sql in the VS Code file sidebar.
Confirm that the generated snapshot includes the projects table.

The snapshot now matches the final database again. Your source migrations and generated schema describe the same three-table design.

How Would PostgreSQL Differ?

A PostgreSQL target would use DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable" for a local development connection without TLS.

The core dbmate workflow stays the same. The connection URL selects the database target while the migration commands keep the same apply, status, and rollback cycle.

Projects Missing After Reapplying?

Confirm that migration 20261103000300 appears as applied in the final status.
Check that db/schema.sql contains the projects table definition.
Verify that the integrated terminal is still inside the dbmate-task-lab folder.

Ask for help with the final migration state.

You closed the full migration loop. The projects schema can now move forward or backward without disturbing the task data.

Secret mission

Link Tasks to Projects

Your database stores tasks and projects as separate records. Add a fourth reversible migration that links them through a join table, then prove the relationship survives a rollback and reapply cycle.

Clean Up Your Resources

Everything in this migration lab is local and free. Choose whether to keep the folder, close your workspace for now, or delete the lab entirely.

Resources you used:

The local dbmate-task-lab folder.

Keep everything running

No action needed. Choose this if you want to continue experimenting with your migration lab.

The folder remains available on your computer. Keeping it creates no cloud charges.

Keep creating new migration files for future schema changes.
Preserve the applied migration files as a record of how the database evolved.

Pause - I'll come back to this later

Close your workspace to end the integrated terminal session while keeping every project file.

Close the Visual Studio Code window from earlier.

The integrated terminal closes. Your local database and migration history remain inside dbmate-task-lab for your next session.

Delete - I don't want to use this again

Remove all project resources to start fresh. Permanent deletion removes the complete migration lab while leaving your installed tools available.

Close the editor window from earlier.

The integrated terminal is now closed. The folder is ready for removal.

Press the Windows key to open Windows search.
Type File Explorer into Windows search.
Press Enter to open File Explorer.
Enter dbmate-task-lab in the File Explorer search field.
Select the dbmate-task-lab folder in the search results.
Press Shift+Delete to remove the selected folder permanently.
Confirm the deletion if Windows asks.
Search for dbmate-task-lab again.

You should see no matching dbmate-task-lab folder. That is the cleanup complete.

Nice Work!

That is a strong finish. You built a working SQLite task database from ordered plain SQL migrations.

You've learned how to:

Build a working migration-driven database with dbmate. Confirm each applied version through its migration history.
Preserve existing task data while adding reversible task notes and projects migrations. Inspect db/schema.sql to confirm the schema snapshot evolves with the database.
Prove atomic failure by confirming that the deliberately broken migration leaves no projects table behind. Repair the migration to complete a clean rollback and reapply cycle.
Secret Mission: Add a reversible task-to-project relationship through task_projects. Create the join table with two cascading foreign keys. Protect each task-project pair with a composite primary key. Query the joined tables to show Learn dbmate linked to Migration Lab.

Ready to quiz yourself?