database migration examples

tags: learning database

content

imagine we’re working on a project. start with one table, then add another one, then alter one of the table

• we can have the first migration file, and name it 001_users_table.up.sql

create table users (
	id integer primary key autoincrement,
	email varchar(50) not null unique,
	password varchar(255) not null
);
create index idx_user on users(email);

• the above is the up file, we should have a down, 001_users_table.down.sql:

drop table users;

• to add a table and alter users, we can have 002_users_table.up.sql

alter table users add column username varchar(255);

• since it’s a version control, wee need to know what’s the current version, usually we will have a schema_migrations table:

create table schema_migration (
	version integer,
	time datetime
);

• now we just need a script (migrate.go) to execute all these sql scripts
  • the script should be able to:
    • find out what current version is (from schema_migration)
    • read migration files (sql scripts)
    • execute migration files (both migrate up or down)
    • update current version
• migration tools like goose and golang-migrate is for running these migrations with better version controls and other features

up

• database-migration-basics

down

reference