Microsoft Business Intelligence: Deploying SQL Server Database objects using Yuniql CLI

We can deploy our SQL server database objects using the Yuniql CLI. Yuniql support SQL Server , Azure SQL Database.

Below are the folder structure.

We must have these folders otherwise it will throw an error.

Directory / File	Usage Description	Execution
*_init*	Initialization scripts. Executed the first time Yuniql run is issued	Executed once
_pre	Pre migration scripts. Executed every time before any version.	Every migration run
v0.00	Baseline scripts. Executed when Yuniql run.	Executed once
_draft	Scripts in progress. Scripts that you are currently working and have not moved to specific version directory yet. Executed every time after the latest version.	Every migration run
_post	Post migration scripts. Executed every time and always the last batch to run.	Every migration run
v0.01	This directory has script of current version, which we are going to deploy.	Executed on demand when run command issued
_erase	Database cleanup scripts. Executed once only when Yuniql erase is issued.	Executed on demand when erase command issued
Dockerfile	A template docker files to run your migration. Uses docker base images with Yuniql installed.	Executed on docker build
README.md	A template README file.
.gitignore	A template git ignore file to skip yuniql.exe from being committed.

Let’s see here we have some script we need to deploy this script using Yuniql CLI

We have a database “Test_DB” where we need to deploy the build.

In this example we are going to create two tables and insert some records in this table.

We have kept our script in the v0.01 directory.

_sequence.ini file :

This file has the sequence of the file execution. Based on the sequence list our script will be running.

In this example we are running our script on below sequence

· DDL_employees.sql
· DDL_departmnet.sql

If we will not provide the _sequence.ini file the it will run based on the alphabetically like below

· DDL_department.sql
· DDL_employees.sql

After DDL script we want to run our DML script. I means insert script. We have kept our DML script into the _post directory.

We don’t have any pre build deployment script such as connection close , due to that we have kept our _pre directory as empty.

In _erase directory we have rollback script.

We have table drop script into this directory.

Let’s run the Yuniql CLI.

Open the CMD and pointing where we have kept the Yuniql exe.

Below is the command to deploy the script in SQL Server

Yuniql run -p "C:\Yuniql\DBOBJECTS" -c "Server=DESKTOP-DEF14CQ\SQLSERVER2019;Database=Test_DB;User Id=SA;Password=*****;TrustServerCertificate=true" -a "false"

Yuniql run CLI Arguments

-a

--auto-create-db

Runs migration using connection string from environment variable YUNIQL_CONNECTION_STRING

Auto-create target database if not exists

-c "<value>"

--connection-string "<value>"

Runs migration using the specified connection string

-p c:\temp\demo

--path c:\temp\demo

Runs migration from target directory

-t v1.05

--target-version v1.05

Runs migration only up to the version v1.05 skipping v1.06 or later

-k "<key>=<value>,<key>=<value>"

--token "<key>=<value>,<key>=<value>"

Replace each token in each script file. This is very helpful when you have environment specific sql-statements such as cross-server queries where database names are suffixed by the environment.

--delimiter ";"

Runs migration using ; as CSV file delimiter

-d

--debug

Runs migration with DEBUG tracing enabled

--platform "sql server"

Runs migration in sql server database.

Running this commend

Scripts will run as listed in the content of _sequence.ini file. Any scripts not listed in the manifest will be skipped and will not be committed in the version where it is placed. Skipped scripts can only be executed by moving them to the next version.

Build deploy successfully deploy.

See the objects in the Database.

Employees and Departments table created successfully. _yuniql_scema_version table I created by default when we are running Yuniql first time. Yuniql use this table for version control.

Currently we have deployed the v0.01 version. If we are trying to run this version again yuniql will not allow running the same version again but _pre, _draft and _post script will run again.

Rollback Run

In case we want to rollback we need to run the erase command. I will run the erase directory scripts.

Yuniql erase -p "C:\Yuniql\DBOBJECTS" -c "Server=DESKTOP-DEF14CQ\SQLSERVER2019;Database=Test_DB;User Id=SA;Password=*******;TrustServerCertificate=true" --force --debug

Objects have been drop. See in the database.

Tables have been dropped.

See the yuniql_schema_version table.

Drawback of the erase is , it will not remove the current version from the yuniql_schema_version table. We need to provide delete script in the _erase directory to delete the current version in case of rollback.