Yuniql: Plain SQL Database Migrations

Yuniql is a lightweight, open-source migration engine through which we can maintain version and deploy database changes using plain SQL with CI/CD pipelines. It uses migration-based and database-first delivery models.

Why Yuniql?

1. Plain SQL and Folder based migration: Yuniql uses simple, intuitive approach for db versioning. Each migration has a dedicated directory (v0.00, v0.01 etc). This directory contains plain .sql files and optional csv files for the data that need to bulk-seed master tables.
2. No runtime dependencies: The CLI is distributed as fully self-contained executable. It doesn’t need to install .NET CLR or any additional framework on target machines.
3. Cross Platform and Cloud Ready: It is fully tested across major cloud providers like Azure SQL, Amazon RDS, Google Cloud SQL etc. It also supports db engines like SQL Server, PostgreSQL, MySQL, MariaDB and preview drivers available for Snowflake, Redshift, and Oracle. This ensures flexibility for hybrid and multi cloud environments.
4. DevOps Friendly: Yuniql integrates with CI/CD pipelines seamlessly. It offers first-class support for Azure pipeline tasks and provides official docker images. It makes automated deployments and containerized environments straight forward and reliable.

Yuniql Installation:

Yuniql can be installed in various ways.

• Download yuniql.exe directly from Github. (Windows)
• Choco Package on windows (x64): Get the latest Yuniql CLI from Chocolatey Package Manager. Run below commands with admin access.

choco install yuniql -y
yuniql version

• .NET Global Tool Install latest Yuniql CLI with .Net global tool. It needs .Net core 3.0 SDK installed.

dotnet tool install -g yuniql.cli
yuniql version

• Azure Devops Extension: The free extension is available on Azure Marketplace. Install it into organization.
• Docker Image: Get the official docker image for yuniql and run the migration from containerized environment. For more information, visit: https://yuniql.io/docs/migrate-via-docker-container/ Docker hub: https://hub.docker.com/repository/docker/yuniql/yuniql

For other methods, check this: https://yuniql.io/docs/install-yuniql/

Directory Structure and Execution Flow in Yuniql:

When you run below command after installation, it frames directory structure to stage the migration and utility scripts.

yuniql init

Here is the default directory structure:

Db-name/
├─ _init/      -- It executes only once during first migration. It sets up objects and configuration.
├─ _pre/       -- It runs before every versioned migration for pre-check or environment setup.
├─ v0.00/      -- baseline version folder (initial schema and seed data)
├─ v1.00/      -- next version folder 
├─ _draft/     -- run every time after latest version. Typically for work in-progress scripts or temporary fixes.
├─ _post/      -- Runs at the end of migration. For cleanup or post deployment scripts.
└─ _erase/     -- Runs manual cleanup scripts for yuniql erase command for controlled db cleanup.

Azure Devops YAML Pipeline using Yuniql.

Below are the steps to setup an Azure DevOps YAML pipeline.

1. Yuniql Workspace: Run the command yuniql init in the directory where the db migration will run from. Once it is run, it will create yuniql workspace (above folder structure)
2. Azure DevOps setup: As mentioned above, acquire the free Yuniql extension for Azure Devops from Azure Marketplace and install into the organization.

Pipeline:

Here is the sample pipeline for db migration for PostgreSql

- task: UseYuniqlCLI@1
    inputs:
      version: "latest"

- task: RunYuniqlCLI@1
    inputs:
      version: "latest"
      connectionString: "$(PostgresDBConnectionString)"
      workspacePath: "$(Build.SourcesDirectory)"
      targetPlatform: "postgresql"
      additionalArguments: "--meta-schema public --meta-table -yuniql_version --debug"

- task: VerifyYUNIQLCLI@1
    displayName: ‘Run verify task (no commit)'
    inputs:
      version: 'latest'
      connectionString: "$(PostgresDBConnectionString)"
      workspacePath: "$(Build.SourcesDirectory)"
      targetPlatform: “postgresql”
      additionalArguments: '--debug'

Here are the properties used in this pipeline:

• Version: It shows yuniql version. It not mentioned, it will install latest version of yuniql cli.
• connectionString: database connection string
• workspacePath: The yuniql workspace location from where the db migration will take place.
• targetPlatform: The target db platform. Here it is PostgreSQL.
• additionalArguments: any additional CLI arguments. Here 2 arguments are used. -meta-schema: postgres schema where the db migration version is logged. -meta-table: The db version will be stored in this table. -debug: it enables detailed trace messages.

Here are the additional properties that can be used:

• autoCreateDatabase: Yuniql will create and configure database of this property is set to true.
• tokenKeyValuePair: It allows custom tokens and values to migration scripts at runtime.

Here one more task is added for verification. It performs uncommitted migration just to see if the migration scripts succeed before applying changes.
Note: Instead of exposing db connection publicly, use Azure Keyvault to fetch values for db connection and other required details.

Practical considerations:

• Keep the v0.00 as clean baseline. Write forward only scripts.
• Use token or tokenkeyvaluepair for environment specific names
• Bulk upload: use csv to see master data. Separator and batch size can be configured.
• Tracing: Use meta-schema/table to log and trace migration.

Rollback in Yuniql:

Yuniql is forward only migration tool. So, the rollback is handled differently here.

• There is no automatic rollback in Yuniql. The version is logged in db in table. So, it must be deleted manually
• Yuniql provides _erase folder. Place the cleanup script in this folder and run below command.

yuniql erase --platform postgres \
-c "<connection-string>" \
--debug

This is used for non-production environments.

• For production environments, create reverse script for each version and run manually. Common approach is to maintain separate folder like rollback/v1.00 with undo scripts and execute manually.

Limitations:

• It supports Plain-SQL only: Yuniql is designed for Plain-SQL intentionally for organized versioned folders. It needs more manual efforts for ORM/native code migration.
• Platform differences: Yuniql supports multiple engines. It means to account for different syntax and batch processes. It is not portable across engines.
• Manual rollback: As mentioned earlier, yuniql doesn’t support automatic rollback. Rollback strategies must be engineered during design.
• No operational features: Operational features like checks, pre-deployment validations, tests are outside of Yuniql. Additional scripting is needed.

My take on Yuniql:

Even though it has several drawbacks, Yuniql is a great fit for Plain-SQL migration which has zero dependencies as it has self-contained CLI. The integration with Azure pipeline and Docker is straightforward with tasks and images.