Modifying the database schema
sqlc parses CREATE TABLE
and ALTER TABLE
statements in order to generate
the necessary code.
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 does not perform database migrations for you. However, sqlc is able to differentiate between up and down migrations. sqlc ignores down migrations when parsing SQL files.
sqlc supports parsing migrations from the following tools:
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 interprets migration filenames numerically. However, sqlc parses migration files in lexicographic order. If you choose to have sqlc enumerate your migration files, make sure their numeric ordering matches their lexicographic ordering to avoid unexpected behavior. This can be done by prepending enough zeroes to the migration filenames.
This doesn’t work as intended.
1_initial.up.sql
...
9_foo.up.sql
# this migration file will be parsed BEFORE 9_foo
10_bar.up.sql
This worked as 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
}