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

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.

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: .

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.

Recommended Posts

Try using sql-migrate
Try using libGDX
Try using Maven
Try using powermock-mockito2-2.0.2
Try using GraalVM
Try using jmockit 1.48
Try using SwiftLint
Try using Log4j 2.0
Try using Axon Framework
Try using JobScheduler's REST-API
Try using java.lang.Math methods
Try using Talend Part 2
Try using Talend Part 1
Try using F # list
Try using each_with_index method
Try using Spring JDBC
Try using RocksDB in Java
Try using GloVe with Deeplearning4j
Try using view_component with rails
Try scraping using java [Notes]
Try using Cocoa from Ruby
Try using letter_opener_web for inquiries
[Swift] Try using Collection View
Try using IntelliJ IDEA once
Try using Spring Boot Security
Try using gRPC in Ruby
[Rails] Try using Faraday middleware
[Processing] Try using GT Force.
[Programming Encyclopedia] §2 Try using Ruby
People using docker Try using docker-compose
Try using Redis with Java (jar)
[Java] Try to implement using generics
Try using the messaging system Pulsar
Try using IBM Java method tracing
Try using Hyperledger Iroha's Java SDK
[Java] Where did you try using java?
Try using Java framework Nablarch [Web application]
Try using || instead of the ternary operator
Try HiveRunner
Try using the Stream API in Java
Try using the Rails API (zip code)
Try Mockito
Study Java Try using Scanner or Map
Try Selenium
Try using JSON format API in Java
Try DbUnit
Try using Spring Boot with VS Code
Try using Reladomo's MT Loader (Multi-Threaded Matcher Loader)
try docker-compose
Try using JobScheduler's REST-API --Java RestClient implementation--
Try using Kong + Konga with Docker Compose.
Try using the Emotion API from Android
Try using the Wii remote with Java
Try Lombok
Try using simple_form / edit even child models
Try implementing GraphQL server using grahpql-java-tools (+ kotlin)
Try using JobScheduler's REST-API --Java RestClient Test class-
Try using conditional branching other than if statement
Beginners try using android studio Part 2 (event processing)
Try local file search using Fess on CentOS7
Try local file search using Fess on CentOS8