go-migrateでテーブルで管理

インストール

brew install golang-migrate

マイグレーションファイル作成

migrate create -ext sql -dir db/migrations -seq create_init_table

db/migrations/000001_create_init_table.up.sql
db/migrations/000001_create_init_table.down.sql
が作られる

migrate create -ext sql -dir db/migrations -seq create_option_table

db/migrations/000002_create_option_table.up.sql
db/migrations/000002_create_option_table.down.sql
が作られる

マイグレーションファイルにSQL書いていく

000001_create_init_table.up.sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR UNIQUE NOT NULL,
  email VARCHAR UNIQUE NOT NULL,
  password VARCHAR NOT NULL,
  role VARCHAR DEFAULT 'user',
  bio TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR NOT NULL,
  body TEXT,
  user_id INTEGER NOT NULL REFERENCES users(id),
  status VARCHAR DEFAULT 'draft',
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMP
);

CREATE TABLE likes (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  post_id INTEGER NOT NULL REFERENCES posts(id),
  created_at TIMESTAMP,
  UNIQUE (user_id, post_id)
);

CREATE TABLE follows (
  id SERIAL PRIMARY KEY,
  following_user_id INTEGER NOT NULL REFERENCES users(id),
  followed_user_id INTEGER NOT NULL REFERENCES users(id),
  created_at TIMESTAMP,
  UNIQUE (following_user_id, followed_user_id)
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  created_at TIMESTAMP,
  PRIMARY KEY (post_id, tag_id)
);

000001_create_init_table.down.sql

DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS follows;
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS users;

000002_create_option_table.up.sql

CREATE TABLE settings (
  id SERIAL PRIMARY KEY,
  setting_key VARCHAR UNIQUE NOT NULL,
  setting_value TEXT,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR NOT NULL,
  message TEXT,
  type VARCHAR DEFAULT 'info',
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR UNIQUE NOT NULL,
  description TEXT,
  color VARCHAR(7),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_preferences (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  theme VARCHAR DEFAULT 'light',
  language VARCHAR DEFAULT 'en',
  timezone VARCHAR DEFAULT 'UTC',
  email_notifications BOOLEAN DEFAULT TRUE,
  push_notifications BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id)
);

000002_create_option_table.down.sql

DROP TABLE IF EXISTS user_preferences;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS settings;

マイグレーション実施

migrate --path migrations --database　'postgresql://postgres:postgres@localhost:5432/mydb?sslmode=disable' -verbose up 2

2025/08/24 10:37:38 Start buffering 1/u create_init_table
2025/08/24 10:37:38 Start buffering 2/u create_option_table
2025/08/24 10:37:38 Read and execute 1/u create_init_table
2025/08/24 10:37:38 Finished 1/u create_init_table (read 5.65775ms, ran 26.767791ms)
2025/08/24 10:37:38 Read and execute 2/u create_option_table
2025/08/24 10:37:38 Finished 2/u create_option_table (read 34.162583ms, ran 15.745ms)
2025/08/24 10:37:38 Finished after 54.163083ms
2025/08/24 10:37:38 Closing source and database

migrate --path migrations --database 'postgresql://postgres:postgres@localhost:5432/mydb?sslmode=disable' -verbose down 1

2025/08/24 11:34:28 Start buffering 2/d create_option_table
2025/08/24 11:34:28 Read and execute 2/d create_option_table
2025/08/24 11:34:28 Finished 2/d create_option_table (read 9.743541ms, ran 10.961459ms)
2025/08/24 11:34:28 Finished after 27.477125ms
2025/08/24 11:34:28 Closing source and database

memo

• migrateコマンドを実行するときは、schema_migrationsのversionからいくつバージョンをupさせるかdownさせるかを指定する