Getting started

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 your SqlServer on Linux container)

Prepare your environment

1. Deploy a sql server linux container
   Skip this if your prefer to use a local or remote server instance.

   docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd\>" -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
   

2. 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/

   • If you use an sql account in the running containier
     Server=localhost,1400;Database=VisitorDB;User Id=SA;Password=<YourStrong@Passw0rd\>
   • If you use an sql account in local or remote instance
     Server=<server-instance>,[<port-number>];Database=VisitorDB;User Id=<sql-user-name>;Password=<sql-user-password>
   • If you use a trusted connection
     Server=<server-instance>,[<port-number>];Database=VisitorDB;Trusted_Connection=True;
     
     

   SETX YUNIQL_CONNECTION_STRING "Server=localhost,1400;Database=VisitorDB;User Id=SA;Password=<YourStrong@Passw0rd\>" 

Run your first migration

1. Download yuniql

   powershell Invoke-WebRequest -Uri https://github.com/rdagumampan/yuniql/releases/download/latest/yuniql-cli-win-x64-latest-full.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\sqlserver-samples\visitph-db"

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

2. Initialize your workspace

   yuniql init
   dir /O:N

3. Increment major version The vnext -M creates a new major version with format v{major}.{minor}. You can of course create this manually inthe 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

4. 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)
   );

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

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

7. 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','jack.poole@never-exists.com')
   INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Diana','Churchill','Makati','diana.churchill@never-exists.com')
   INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Rebecca','Lyman','Rizal','rebecca.lyman@never-exists.com')
   INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Sam','Macdonald','Batangas','sam.macdonald@never-exists.com')
   INSERT INTO [dbo].[Visitor]([FirstName],[LastName],[Address],[Email])VALUES('Matt','Paige','Laguna','matt.paige@never-exists.com')

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

   
   

9. Initialize git repo

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

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

Found bugs?

Help us improve further please create an issue.