Modifying the database schema
sqlc understands ALTER TABLE
statements when parsing SQL.
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
birth_year int NOT NULL
);
ALTER TABLE authors ADD COLUMN bio text NOT NULL;
ALTER TABLE authors DROP COLUMN birth_year;
ALTER TABLE authors RENAME TO writers;
package db
type Writer struct {
ID int
Bio string
}
Handling SQL migrations
sqlc will ignore rollback statements when parsing migration SQL files. The following tools are current supported:
goose
-- +goose Up
CREATE TABLE post (
id int NOT NULL,
title text,
body text,
PRIMARY KEY(id)
);
-- +goose Down
DROP TABLE post;
package db
type Post struct {
ID int
Title sql.NullString
Body sql.NullString
}
sql-migrate
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);
-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;
package db
type People struct {
ID int32
}
tern
CREATE TABLE comment (id int NOT NULL, text text NOT NULL);
---- create above / drop below ----
DROP TABLE comment;
package db
type Comment struct {
ID int32
Text string
}
golang-migrate
Warning: golang-migrate specifies that the version number in the migration file name is to be interpreted numerically. However, sqlc executes the migration files in lexicographic order. If you choose to simply enumerate your migration versions, make sure to prepend enough zeros to the version number to avoid any unexpected behavior.
Probably doesn’t work as intended:
1_initial.up.sql
...
9_foo.up.sql
# this migration file will be executed BEFORE 9_foo
10_bar.up.sql
Works as was probably intended:
001_initial.up.sql
...
009_foo.up.sql
010_bar.up.sql
In 20060102.up.sql
:
CREATE TABLE post (
id int NOT NULL,
title text,
body text,
PRIMARY KEY(id)
);
In 20060102.down.sql
:
DROP TABLE post;
package db
type Post struct {
ID int
Title sql.NullString
Body sql.NullString
}
dbmate
-- migrate:up
CREATE TABLE foo (bar INT NOT NULL);
-- migrate:down
DROP TABLE foo;
package db
type Foo struct {
Bar int32
}