# Migrations

The `db/` directory should look like:

```text
  current.sql
  migrations/
    0001_init.sql
    0002_add_user_table.sql
    ...
```

The `current.sql` always records the latest schema definition.

## Prerequisites

1. [migrate](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate)

### Usage

#### 0. Prepare test environment

```sh
tiup playground
cd db
mysql -uroot -h127.0.0.1 -P4000 -e 'create database if not exists _test_infra'
mysql -uroot -h127.0.0.1 -P4000 _test_infra < current.sql

# the `migrate` command needs `GET_LOCK()` to create `schema_migrations` table
mysql -uroot -h127.0.0.1 -P4000 -e 'SET @@GLOBAL.tidb_enable_noop_functions=1'
```

#### 1. Change table schema in `current.sql`

#### 2. Create migration file with name `{seq}_{name}.up.sql` under `migrations` directory (the `.down.sql` is not necessary)

```sh
migrate create -ext sql -dir migrations -seq {name}
```

And then put patch SQLs into `{seq}_{name}.up.sql`.

#### 3. Run migrations

```sh
# Test it on your computer first
migrate -path migrations -database 'mysql://root:@tcp(127.0.0.1:4000)/_test_infra' up

# Run migrations on production DB
# TODO: move to CI
migrate -path migrations -database 'mysql://root:@tcp(xxx.xxx.xxx.xxx:xxxx)/test_infra' up
```