Migrations from the command line
On this page
On SQLite:
- migrate.dsn: file:library.db is resolved against the directory that holds sgl.yaml, like the file's other paths; a --dsn path is resolved against the directory sgl runs in
- SQLite runs DDL in transactions, so each migration commits or rolls back whole
- status prints applied times, recorded in the transcript as YYYY-MM-DD hh:mm:ss
The migrations Seagull replays to learn your schema are the same files that build the real database, and sgl can apply them too. One tool then covers the whole loop: start a project, apply its migrations, roll one back, check nothing has been edited behind the database's back, load seed data, and number the next change. This example does each of those against a SQLite file.
Starting a project
sgl init writes a starter project into the current directory: an sgl.yaml, a first migration with its .down.sql, an example query and a seed. --dialect picks the database; the session runs it in an empty starter directory. The rest of the session uses the example's own project, which grew from such a start.
The project
Two migrations create authors and then books:
db/migrations/0001_authors.up.sql
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
db/migrations/0002_books.up.sql
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors (id),
title TEXT NOT NULL
);
Each has a .down.sql that undoes it, which is what a rollback runs:
db/migrations/0002_books.down.sql
DROP TABLE books;
Seeds are data rather than schema, kept in their own directory. The second refers to the authors the first inserts:
db/seeds/0002_books.sql
INSERT INTO books (author_id, title) VALUES
((SELECT id FROM authors WHERE name = 'Ursula K. Le Guin'), 'The Dispossessed'),
((SELECT id FROM authors WHERE name = 'Terry Pratchett'), 'Night Watch');
sgl.yaml names the seeds directory and the database to migrate:
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: library
dialect: sqlite
migrations: db/migrations
queries: db/queries
seeds: db/seeds
migrate:
dsn: file:library.db
go:
package: store
out: internal/store
sgl takes the database from --dsn first, then the SGL_DATABASE_URL environment variable, then migrate.dsn. A server's DSN usually comes from the environment, written as ${env:DATABASE_URL} in sgl.yaml; a SQLite file can simply be named.
The session
sgl migrate status lists every migration with its state. library.db does not exist yet, so both are pending.
sgl migrate up applies every pending migration in order, printing a line for each, then the count: 2 applied. Each migration runs in its own transaction, so a failure part way leaves no half-applied file behind. sgl records each one in a history table, with a checksum of its file.
sgl migrate status now shows both applied, with the time each ran.
sgl migrate down rolls back the latest migration by running its .down.sql, and prints 1 rolled back. sgl migrate down 2 would roll back two.
sgl migrate to 2 moves to a version in whichever direction it lies. Here that is up, re-applying books. A lower version rolls back down to it, and the current version prints that there is nothing to do.
sgl migrate validate compares the history with the files. It fails, exiting 1, when a migration's file has changed since it was applied, or has gone missing, so a database and its migrations never quietly disagree.
sgl seed status and sgl seed apply are the seed counterparts of migrate status and migrate up. Each seed is applied once and recorded like a migration, so the second seed apply finds nothing to do and prints 0 applied. sgl seed reseed clears that record and applies every seed again.
sgl migrate new add_isbn starts the next change. It numbers the new pair of files after the last migration, 0003, with a placeholder comment in each, and migrate status lists it as pending until you write it and run migrate up.
Here is the session as recorded. Each $ line is a command, the lines after it are what it printed, and # exit N is its exit status.
# sgl init writes a starter project: sgl.yaml, a first migration, a seed and a query.
# In an empty directory:
$ mkdir starter
# exit 0
$ cd starter
# exit 0
$ sgl init --dialect sqlite
created sgl.yaml
created db/migrations/0001_init.up.sql
created db/migrations/0001_init.down.sql
created db/queries/example.sql
created db/seeds/0001_authors.sql
Next: edit the migration and queries, then run `sgl generate`.
# exit 0
$ cd ..
# exit 0
# This project grew from such a start into two migrations and two seeds. migrate.dsn in
# sgl.yaml names file:library.db, which does not exist yet.
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors pending -
2 books pending -
# exit 0
# up applies every pending migration in order, each in its own transaction.
$ sgl migrate up
up 1 authors
up 2 books
2 applied
# exit 0
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
# exit 0
# down rolls back the latest migration with its .down.sql; down N rolls back N.
$ sgl migrate down
down 2 books
1 rolled back
# exit 0
# to moves up or down to a version.
$ sgl migrate to 2
up 2 books
1 applied
# exit 0
# validate checks that no applied migration's file has changed since it ran.
$ sgl migrate validate
migrations are valid
# exit 0
# Seeds are data, applied once each and recorded like migrations.
$ sgl seed status
VERSION NAME STATE APPLIED
1 authors pending -
2 books pending -
# exit 0
$ sgl seed apply
2 applied
# exit 0
$ sgl seed status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
# exit 0
$ sgl seed apply
0 applied
# exit 0
# The next change starts with migrate new, which numbers the files after the last.
$ sgl migrate new add_isbn
created db/migrations/0003_add_isbn.up.sql
created db/migrations/0003_add_isbn.down.sql
# exit 0
$ cat db/migrations/0003_add_isbn.up.sql
-- 0003_add_isbn: write the up migration here.
# exit 0
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
3 add_isbn pending -
# exit 0
When sgl cannot reach the database, or a migration fails, it exits 4, so a deploy script can tell that apart from a finding. To apply the same files from your own program, see Migrations at startup; Migration lifecycle rolls back, validates and reads the status from Go.
Try it
After sgl migrate up, add a comment to 0001_authors.up.sql and run sgl migrate validate. It reports a checksum mismatch for migration 1 and exits 1, and sgl migrate status marks the migration changed. Put the file back and validate passes again.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/tooling/045_migrate_from_the_cli && go run seagull.sh/seagull/cmd/sgl vet
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: library
dialect: sqlite
migrations: db/migrations
queries: db/queries
seeds: db/seeds
migrate:
dsn: file:library.db
go:
package: store
out: internal/store
db/migrations/0001_authors.up.sql
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
db/migrations/0002_books.up.sql
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors (id),
title TEXT NOT NULL
);
db/migrations/0001_authors.down.sql
DROP TABLE authors;
db/migrations/0002_books.down.sql
DROP TABLE books;
db/queries/books.sql
-- sgl.query(ListBooks, many)
SELECT books.id, books.title, authors.name AS author
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY books.title;
db/seeds/0001_authors.sql
INSERT INTO authors (name) VALUES ('Ursula K. Le Guin'), ('Terry Pratchett');
db/seeds/0002_books.sql
INSERT INTO books (author_id, title) VALUES
((SELECT id FROM authors WHERE name = 'Ursula K. Le Guin'), 'The Dispossessed'),
((SELECT id FROM authors WHERE name = 'Terry Pratchett'), 'Night Watch');
transcript.txt
# sgl init writes a starter project: sgl.yaml, a first migration, a seed and a query.
# In an empty directory:
$ mkdir starter
# exit 0
$ cd starter
# exit 0
$ sgl init --dialect sqlite
created sgl.yaml
created db/migrations/0001_init.up.sql
created db/migrations/0001_init.down.sql
created db/queries/example.sql
created db/seeds/0001_authors.sql
Next: edit the migration and queries, then run `sgl generate`.
# exit 0
$ cd ..
# exit 0
# This project grew from such a start into two migrations and two seeds. migrate.dsn in
# sgl.yaml names file:library.db, which does not exist yet.
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors pending -
2 books pending -
# exit 0
# up applies every pending migration in order, each in its own transaction.
$ sgl migrate up
up 1 authors
up 2 books
2 applied
# exit 0
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
# exit 0
# down rolls back the latest migration with its .down.sql; down N rolls back N.
$ sgl migrate down
down 2 books
1 rolled back
# exit 0
# to moves up or down to a version.
$ sgl migrate to 2
up 2 books
1 applied
# exit 0
# validate checks that no applied migration's file has changed since it ran.
$ sgl migrate validate
migrations are valid
# exit 0
# Seeds are data, applied once each and recorded like migrations.
$ sgl seed status
VERSION NAME STATE APPLIED
1 authors pending -
2 books pending -
# exit 0
$ sgl seed apply
2 applied
# exit 0
$ sgl seed status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
# exit 0
$ sgl seed apply
0 applied
# exit 0
# The next change starts with migrate new, which numbers the files after the last.
$ sgl migrate new add_isbn
created db/migrations/0003_add_isbn.up.sql
created db/migrations/0003_add_isbn.down.sql
# exit 0
$ cat db/migrations/0003_add_isbn.up.sql
-- 0003_add_isbn: write the up migration here.
# exit 0
$ sgl migrate status
VERSION NAME STATE APPLIED
1 authors applied YYYY-MM-DD hh:mm:ss
2 books applied YYYY-MM-DD hh:mm:ss
3 add_isbn pending -
# exit 0