How to run migration from Docker container

Run your database migration thru a docker container. This is specially helpful on linux environments and CI/CD pipelines running on Linux Agents as it facilitates your migration without having to worry any local installations or runtime dependencies.

How does this works?

When you run yuniql init command, a baseline directory structure will be created automatically. This includes a ready-to-use Dockerfile.

When you call docker build, we pull the base image containing the nightly build of yuniql and all of your local structure is copied into the image. When you call docker run, yuniql run it executes the migration inside the container and targets your desired server.

NOTE: The container must have access to the target database and server. You may need to configure a firewall rule to accept login requests from the container hosts especially for cloud-based databases.

Pre-requisites

• Docker Client
• SQL Server or Azure SQL Database

Prepare database migration project

1. Download the latest yuniql build for windows and extract locally.

2. Create new migration workspace
   

   yuniql-latest> yuniql init
   yuniql-latest> dir /O:N
   
   10/21/2019  22:41    <DIR>          _draft
   10/21/2019  22:41    <DIR>          _init
   10/21/2019  22:41    <DIR>          _post
   10/21/2019  22:41    <DIR>          _pre
   10/21/2019  22:41    <DIR>          v0.00
   10/21/2019  22:41                   Dockerfile
   10/21/2019  22:41                   README.md
   10/21/2019  22:41                   .gitignore

3. Create first script file setup_tables.sql on v0.00

   CREATE TABLE [dbo].[Visitor](
   	[VisitorID] [int] IDENTITY(1000,1) NOT NULL,
   	[FirstName] [nvarchar](255) NULL,
   	[LastName] [varchar](255) NULL,
   	[Address] [nvarchar](255) NULL,
   	[Email] [nvarchar](255) NULL
   ) ON [PRIMARY];
   GO
   

4. Build docker image

   docker build -t visitph-example .

5. Run migration from docker

   docker run visitph-example -c "<your-connection-string>" -a
   docker logs <your-container-id>-f

6. Commit your project into git and use it as input in creating CI/CD pipelines.

Setup Azure DevOps Pipelines

The following pipelines runs visiph-db project under sqlserver-samples into Azure SQL Database. The samples are available on Yuniql GitHub repository. You may clone the repo to test the pipelines.

Agent task: docker build

steps:
- task: Docker@2
  displayName: 'docker build'
  inputs:
    command: build
    Dockerfile: '$(System.DefaultWorkingDirectory)/_rdagumampan_yuniql/sqlserver-samples/visitph-db/Dockerfile'
    arguments: '-t visitph-example'
    addPipelineData: false

Agent task: docker run

variables:
  AzSqlDemoDatabase: '<YOUR-SQLDATABASE-CONNECTIONSTRING>'

steps:
- task: Docker@2
  displayName: 'docker run'
  inputs:
    command: run
    arguments: 'visitph-example -c "$(AzSqlDemoDatabase)" --debug'
    addPipelineData: false

Private Container Registry

If your company policy disallow pulling images DockerHub, you may build the image internally by cloning the GitHub repo and building docker images locally. The docker files are multi-staged. Please refer to dockerfile.multi-stage-linux-x64 and dockerfile.multi-stage-win-x64.

1. Build images and push to your preferred registry

docker build -t yuniql -f dockerfile.multi-stage-linux-x64 .
docker tag yuniql:latest rdagumampan/yuniql:linux-x64-latest

docker login -u="%DOCKERHUB_USERNAME%" -p="%DOCKERHUB_PASSWORD%"
docker push yuniql:linux-x64-latest

docker build -t yuniql -f dockerfile.multi-stage-win-x64 .
docker tag yuniql:latest rdagumampan/yuniql:win-x64-latest

docker login -u="%DOCKERHUB_USERNAME%" -p="%DOCKERHUB_PASSWORD%"
docker push yuniql:win-x64-latest

2. Modify the Docker file of your database project

#FROM rdagumampan/yuniql:linux-x64-latest
FROM <your-internal-repository>/yuniql:linux-x64-latest
COPY . ./db

Found bugs?

Help us improve further please create an issue.