Evolve the database

Add a priority to tasks, then apply and roll back that change in a local database. You will also record a seed separately from the migrations.

Starting point

Continue after search and results. The application still creates a fresh database on each run. For the CLI exercise, use a separate tasks.db file in your practice directory.

Step 1: Add the next version

Create db/migrations/0004_priority.up.sql:

ALTER TABLE tasks ADD COLUMN priority INTEGER NOT NULL DEFAULT 1;

Create its reverse operation in db/migrations/0004_priority.down.sql:

ALTER TABLE tasks DROP COLUMN priority;

Append a query to db/queries/tasks.sql:

-- sgl.query(TaskPriority, one)
SELECT priority FROM tasks WHERE id = ?;

Regenerate, then append this code before run returns:

../sgl generate

	priority, err := queries.TaskPriority(ctx, task.ID)
	if err != nil {
		return err
	}
	fmt.Fprintln(out, "priority:", priority.Priority)

go run .

1 Feed the seagulls
Bring chips
missing: true
Feed the seagulls in Harbour
priority: 1

The task receives the default priority of one.

Step 2: Apply and inspect

Point the CLI at the practice database and check its history:

export SGL_DATABASE_URL="file:$PWD/tasks.db"
../sgl migrate status
../sgl migrate up
../sgl migrate validate
../sgl migrate status

The four versions change from pending to applied. migrate up created the tables and columns in this database.

Step 3: Roll back and reapply

../sgl migrate down 1
../sgl migrate status
../sgl migrate up

Status shows version four pending after the rollback. The next up adds the priority column again. Dropping a column loses its values, so this reversal is appropriate for our practice database.

Step 4: Seed this local database

Create db/seeds/0001_tasks.sql:

INSERT INTO tasks (title) VALUES ('Review the migration');

Apply it and check the seed history:

../sgl seed apply
../sgl seed status

The seed is recorded as applied. Repeating seed apply skips it. Your application still uses its own fresh database, so its output stays the same.

Continue with testing. Migration safety explains how this workflow changes for a shared database.