Try using sql-migrate

Introduction

This article is the 18th article of ITRC Advent Calendar 2020. Previous article? → [Neovim] Recommended text editor taught by Aoi Kotoha [Voiceroid] Hello RIN1208. This time it was convenient to use sql-migrate, so I will talk about it.

Required environment

• docker-compose
• mac os

I will explain in the above environment

What is sql-migrate?

--Supports various databases such as SQLite, PostgreSQL, MySQL, Oracle, etc. --Available as a CLI tool or library --Can be obtained with go get

To construct

The repository is here

The following four files are required to use sql-migrate this time.

• dbconfig.yml --docker-compose.yml (I'll talk about it using docker-compose) --Dockerfile (I am in a go environment to install with go get) --sql migration file

Write docker-compose.yml

I will write docker-compose.yml. This time I'm using phpmyadmin for confirmation

version: '3.7'
services:
  api:
    build: .
    ports:
      - "8080:8080"
    tty: true
    volumes:
      - ./:/app
    working_dir: /app
    environment:
     - MYSQL_USER=root
     - MYSQL_PASSWORD=root
     - MYSQL_HOST=[mysql]
     - MYSQL_PORT=3306
     - MYSQL_DATABASE=test
  mysql:
    image: mysql:5.6
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=test
    command: >
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --innodb_file_per_table
      --innodb_file_format=BARRACUDA
      --innodb_large_prefix=1
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    ports:
      - 8000:80
    environment:
      - PMA_HOST=mysql
      - PMA_PASSWORD=root
      - PMA_USER=root
      - PMA_ARBITRARY=1
    links:
      - mysql
    depends_on:
      - mysql

Write a Dockerfile

Next, I will write a docker file

FROM golang:1.14

RUN go get github.com/rubenv/sql-migrate/...

Write sql migration file

Next, write the sql migration file. Since sql is written as it is here, you can easily set the primary key and foreign key. The file name can be any .sql file.

-- +migrate Up
CREATE TABLE IF NOT EXISTS user (
    user_id VARCHAR(128) NOT NULL PRIMARY KEY,
    user_name VARCHAR(128)

);
-- +migrate Down
DROP TABLE IF EXISTS user;

Write dbconfig.yml

Then write dbconfig.yml. Here, write the config of sql-migrate.

development:
    dialect: mysql 
    datasource: ${MYSQL_USER}:${MYSQL_PASSWORD}@tcp(${MYSQL_HOST}:${MYSQL_PORT})/${MYSQL_DATABASE}?parseTime=true
    dir: .

• dialect
  In this part, the DB to be used is selected. Since mysql is used this time, it is written as mysql.
• datasource
  This part contains the information to connect to mysql. This time, instead of writing it as it is, it is obtained from the environment variable. Environment variables are defined in docker-compose.yml.
• dir
  In this part, the location of the sql file to execute is selected. If it is located under/api/db, enter api/db.

Try to run

Now, let's execute the following command to migrate.

docker-compose exec api bash -c "sql-migrate up"

Connect to http: // localhost: 8000/and check with phpmyadmin If you can successfully create the user table and enter it, it is successful.

At the end

Thank you for reading this far. I wrote this article because sql-migrate was convenient. I hope it helps those who are trying to use sql-migrate. Also, if you have any mistakes, please let us know in the comments.