sqlc/docs/howto/ddl.md

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:

• dbmate
• golang-migrate
• goose
• sql-migrate
• tern

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
}