Working with yuniql and git repository

This 10-step tutorial shows you how to deploy your first sql-based migration into an sql server and store your version in a git repository. It demonstrates all the major features of yuniql. For simplicity, we assume you have a Docker service running but you may choose any local/remote instance.

Run these commands line by line via Command Prompt (CMD). Estimated completion time: 10 mins.

Pre-requisites

• SQL Server or Azure SQL Database
• Docker (only if you prefer SqlServer on Linux container)

Prepare your environment

Deploy a sql server linux container

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd!" -p 1400:1433 -d mcr.microsoft.com/mssql/server:2017-latest
docker ps

CONTAINER ID            IMAGE                                        PORTS                 
<dynamic-container-id>  mcr.microsoft.com/mssql/server:2017-latest   0.0.0.0:1400->1433/tcp

Prepare connection

Set your db connection string in environment variable. This demo uses SQL Server on Docker container. For more connection string samples, visit https://www.connectionstrings.com/sql-server/

SETX YUNIQL_CONNECTION_STRING "Server=localhost,1400;Database=VisitorDB;User Id=SA;Password=P@ssw0rd!" 

Run your first migration

Download yuniql

powershell Invoke-WebRequest -Uri https://github.com/rdagumampan/yuniql/releases/download/latest/yuniql-cli-win-x64-latest.zip -OutFile  "c:\temp\yuniql\yuniql-win-x64-latest.zip"
powershell Expand-Archive "c:\temp\yuniql\yuniql-win-x64-latest.zip" -DestinationPath "c:\temp\yuniql\samples\sqlserver-sample"

Expand-Archive requires at least powershell v5.0+ running on your machine. You may also download manually here and extract to desired directory.

Initialize your workspace

yuniql init
dir /O:N

Increment major version

The vnext -M creates a new major version with format v{major}.{minor}. You can of course create this manually in the directory!

yuniql vnext -M
dir /O:N

10/21/2019  22:41    <DIR>          _draft
10/21/2019  22:41    <DIR>          _erase
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:46    <DIR>          v1.00
10/21/2019  22:41                   Dockerfile
10/21/2019  22:41                   README.md
10/21/2019  22:41                   .gitignore

Create your first script file setup-tables.sql inside v1.00

--setup-tables.sql
CREATE TABLE Visitor (
	VisitorID INT IDENTITY(1000,1),
	FirstName NVARCHAR(255),
	LastName VARCHAR(255),
	Address NVARCHAR(255),
	Email NVARCHAR(255)
);

Run migration

yuniql run -a
yuniql info

Version         Created                         CreatedBy
v0.00           2019-10-21T21:16:48.8130000     sa
v1.00           2019-10-21T21:16:49.4130000     sa

Increment minor version

yuniql vnext
dir /O:N

10/21/2019  22:41    <DIR>          v0.00
10/21/2019  22:46    <DIR>          v1.00
10/21/2019  22:46    <DIR>          v1.01

Create your second script file initialize-tables.sql inside v1.01

--initialize-tables.sql
INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Jack','Poole','Manila','[email protected]')
INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Diana','Churchill','Makati','[email protected]')
INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Rebecca','Lyman','Rizal','[email protected]')
INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Sam','Macdonald','Batangas','[email protected]')
INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Matt','Paige','Laguna','[email protected]')

Run migration again

yuniql run
yuniql info

Verify that records has been inserted as part version v1.01

//SELECT * FROM [dbo].[Visitor]
VisitorID   FirstName   LastName    Address  Email
----------- ----------- ----------- ------------------------------------------
1000        Jack        Poole       Manila   jack.poole@never-exists.com
1001        Diana       Churchill   Makati   diana.churchill@never-exists.com
1002        Rebecca     Lyman       Rizal    rebecca.lyman@never-exists.com
1003        Sam         Macdonald   Batangas sam.macdonald@never-exists.com
1004        Matt        Paige       Laguna   matt.paige@never-exists.com

Initialize git repo

git init
git add -A
git commit -m "This is my first yuniql migration"

Create destination git repo and push your changes.

You may use any other git provider and replace the .git folder.

git remote add origin https://github.com/{your-github-account}/{your-github-database-repo}.git
git push -u origin master

NOTE: For simplicity I use HTTPS mode in setting git repo. If you use SSH, you need to download and configure your keys.

Further readings

• Migrate via ASP.NET Core
• Migrate via Azure DevOps
• Migrate via Docker

Found bugs?

Help us improve further please create an issue.