# How To Use Golang Migrate for Database Migration

It is common for web application having a database and using concept of migration already considered as best practice. Instead of creating database schema directly to database, migration script created so we could control how we manage the schema programmatically and also would get benefit on CI/CD process.

There are several migration tools for Go but now let’s learn how to use [Golang Migrate](https://github.com/golang-migrate/migrate) to manage migration in Go.

# Preparation

* Database server in this post, I am using PostgreSQL
    
* For Windows, make sure it already installed [Scoop](https://scoop.sh/)
    
* Go
    
* Terminal
    

# CLI Installation

Make sure Scoop already been installed and In your Windows terminal run this command to install `migrate`

```bash
scoop install migrate
```

Once it done, run command `migrate` in Windows terminal. If everything is good, then it will indicated CLI correctly installed.

![image of migrate command](https://cdn.hashnode.com/res/hashnode/image/upload/v1728441397591/aea5f93f-2d22-436a-8d95-7c0a43f6ae6d.png align="center")

> For other OS installation can be check on the instruction detail [https://github.com/golang-migrate/migrate/tree/master/cmd/migrate](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate)

# Create Migration

We need a directory for storing the migration files. Create folder `migrations` in your Go project directory. You may create folder `database/migrations` or `db/migrations` depends on your preferred structure.

Go to your terminal and run the `migrate` command to start create migration files

```bash
 migrate create -ext sql -dir db/migrations -seq create_urls_table
```

* `create` option is to create migration files
    
* `-ext sql` is to create migration files with `.sql` extension
    
* `-dir db/migrations` path for migrations file will be stored
    
* `-seq create_shortens_table` will generate up/down migrations for your table schema file sequentially
    

> You may adjust the command and arguments based on your project structure

Generated files should looks like this

![image of generated migration files](https://cdn.hashnode.com/res/hashnode/image/upload/v1728444440465/60b894c3-e1c5-4694-8865-0a29485beed1.png align="center")

Now you just need to write SQL statement to create / altering / drop the table schema. Here’s sample of mine for PostgreSQL:

```sql
// 000001_create_urls_table.up.sql
CREATE TABLE IF NOT EXISTS urls(
    id BIGSERIAL PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    original_url VARCHAR(255) NOT NULL,
    user_id BIGINT NULL,
    visit_count INT DEFAULT 0 NULL,
    created_at timestamp DEFAULT current_timestamp,
    updated_at timestamp NULL,
    deleted_at timestamp NULL
);

// 000001_create_urls_table.down.sql
DROP TABLE IF EXISTS urls;
```

> To maintain idempotent, always check if the schema exists or not

# Running Migration

Migration files up/down already created meaning you will able to create the table or drop the table using recent migration files. Before running the migration, make sure you already setup the database because it will need database URL to run.

My URL for PostgreSQL is like below

```javascript
postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable

// convention
driver://username:pass@host:port/dbname?sslmode=disable
```

Then we can now run `up` migration to create new table schema

```bash
migrate -database postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable -source file://db/migrations up
```

* `-database` tells migrate to use particular database server
    
* `-source` locates migration files path which is in `db/migrations` directory
    
* `up` tells migrate to run `000001_create_urls_table.up.sql`
    

When we need to revert the migration, we can use `down` option to drop the table schema

```bash
migrate -database postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable -source file://db/migrations down
```

> Other command can be referred to this [https://github.com/golang-migrate/migrate/tree/master/cmd/migrate](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate)

# Getting Database Value From YAML File

Our project often uses config file to store the database configuration. In my case, it stored on YAML file `config.yml`. In [Migrate’s Github](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate) there’s example to do that.

First, install `Python` and `pip`. Then install `pyyaml` library for reading the `yaml` file.

```bash
 pip install pyyaml
```

Now we can modify our migration command to

```bash
migrate -database "$(cat config.yml | python -c "import yaml,sys; print(yaml.safe_load(sys.stdin)['database'])")" -source file://db/migrations up
```

It basically using same structure as previous command but now we read `yaml` file and parsing the `database` to get the URL value.

That’s it. Happy coding!