yuniql

Free and open source schema versioning and database migration made natively with .NET/6. NEW THIS MAY 2022! v1.3.15 released!

Programming language: C#

License: Apache License 2.0

Tags: Deployment    

Latest version: v1.0.1

README

yuniql

**** HELP DATA DEVELOPERS DISCOVER YUNIQL, PLEASE STAR THIS REPO. ITS FREE. :) THANKS! ****

yuniql (yuu-nee-kel). Free and open source schema versioning and database migration engine made natively with .NET Core. Use plain SQL scripts, bulk import CSV, integrate CI/CD pipelines, zero runtime dependencies and works with windows and linux. Supports SqlServer, PostgreSql, MySql, MariaDB, Snowflake, Redshift and Oracle*.

<!--

Inspired by Evolutionary Database Design by Martin Fowler and Pramod Sadalage. -->

Working with CLI

Manage local db versions and run database migrations from your CLI tool. Perform local migration run or verify with uncommitted runs to test your scripts. Download latest release here. Install yuniql CLI with Chocolatey or use alternative ways listed here https://yuniql.io/docs/install-yuniql

dotnet tool install -g yuniql.cli
choco install yuniql
docker run --rm yuniql/cli:linux-x64-latest run --platform sqlserver --help

# powershell
Invoke-WebRequest -Uri https://github.com/rdagumampan/yuniql/releases/download/latest/yuniql-cli-win-x64-latest.zip -OutFile  "c:\temp\yuniql-win-x64-latest.zip"
Expand-Archive "c:\temp\yuniql-win-x64-latest.zip" -DestinationPath "c:\temp\yuniql-cli-latest"
cd c:\temp\yuniql-cli-latest
.\yuniql.exe run --platform sqlserver --help

Run migrations for SQL Server

docker run -d -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd!" -p 1400:1433 -d mcr.microsoft.com/mssql/server:2017-latest
SETX YUNIQL_CONNECTION_STRING "Server=localhost,1400;Database=yuniqldb;User Id=SA;Password=P@ssw0rd!;TrustServerCertificate=True"
SETX YUNIQL_WORKSPACE "c:\temp\yuniql-cli\samples\basic-sqlserver-sample"

git clone https://github.com/rdagumampan/yuniql.git c:\temp\yuniql-cli
cd c:\temp\yuniql-cli\samples\basic-sqlserver-sample

yuniql run -a --platform sqlserver
yuniql list --platform sqlserver

Running yuniql v1.0.1 for windows-x64
Copyright 2019 (C) Rodel E. Dagumampan. Apache License v2.0
Visit https://yuniql.io for documentation & more samples

+---------------+----------------------+------------+---------------+----------------------+--------------+
| SchemaVersion | AppliedOnUtc         | Status     | AppliedByUser | AppliedByTool        | Duration     |
+---------------+----------------------+------------+---------------+----------------------+--------------+
| v0.00         | 2021-02-04 06:06:46Z | Successful | sa            | yuniql-cli v1.1.55.0 | 164 ms / 0 s |
+---------------+----------------------+------------+---------------+----------------------+--------------+

Supported databases and platform tests

Amazon Aurora, Azure Synapse and Alibaba Aspara are being evaluated/developed/tested. For running migration from docker container, see instructions here.

Platforms	Build Status	Description	Cloud Infrastructure	Documentation
sqlserver		Sql Server 2017 and later	Azure, GCP, AWS	Get started
postgresql		PostgreSql v9.6 and later	Azure, GCP, AWS	Get started
mysql		MySql v5.7 and later	Azure, GCP, AWS	Get started
mariadb		MariaDb v10.2 and later	Azure, GCP, AWS	Get started
snowflake		Last verified 12.2021	GCP	Get started
redshift		Last verified 12.2021	AWS	Get started
oracle		Preview build. Oracle 11g and later	Oracle Cloud	Get started
sap hana	Analysis phase	Expected Q1 2022	Azure
synapse	Analysis phase	Expected Q2 2022	Azure
aurora	Ideation phase	No release date yet	AWS
asparadb	Ideation phase	No release date yet	Alibaba

NOTE: Supported cloud platforms are based on limited testing and community feedbacks where users indicated the platform they were attempting to run against. yuniql primarily uses Amazon RDS as cloud provider for non-vendor specific platforms.

Distributions	Build Status	Description
Docker image linux-x64		docker pull yuniql/yuniql:linux-x64-latest
Docker imiage win-x64		docker pull yuniql/yuniql:win-x64-latest

Run migrations for PostgreSql, MySql and others

docker run -d -e POSTGRES_USER=sa -e POSTGRES_PASSWORD=P@ssw0rd! -e POSTGRES_DB=yuniqldb -p 5432:5432 postgres
SETX YUNIQL_CONNECTION_STRING "Host=localhost;Port=5432;Username=sa;Password=P@ssw0rd!;Database=yuniqldb"
SETX YUNIQL_WORKSPACE "c:\temp\yuniql-cli\samples\basic-postgresql-sample"

git clone https://github.com/rdagumampan/yuniql.git c:\temp\yuniql-cli
cd c:\temp\yuniql-cli\samples\basic-postgresql-sample

yuniql run -a --platform postgresql
yuniql list --platform postgresql

Working with Azure DevOps Pipelines Tasks

Run your database migration from Azure DevOps Pipelines. The tasks downloads package and cache it for later execution just like how Use .NET Core or Use Node tasks works. Find Yuniql on Azure DevOps MarketPlace. Developer guide is available here https://yuniql.io/docs/migrate-via-azure-devops-pipelines.

Working with 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. Developer guide is available here https://yuniql.io/docs/migrate-via-docker-container.

git clone https://github.com/rdagumampan/yuniql.git c:\temp\yuniql-docker
cd c:\temp\yuniql-docker\samples\basic-sqlserver-sample

docker build -t sqlserver-example .
docker run sqlserver-example -c "<your-connection-string>" -a --platform sqlserver

Working with ASP.NET Core

Run your database migration when your ASP.NET Core host service starts up. This ensures that database is always at latest compatible state before operating the service. Applies to Worker and WebApp projects. Developer guide is available here https://yuniql.io/docs/migrate-via-aspnetcore-application.

dotnet add package Yuniql.AspNetCore

using Yuniql.AspNetCore;
...
...

//1. deploy new sql server on docker or use existing instance
//$ docker run -dit --name yuniql-sqlserver  -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd!" -p 1400:1433 -d mcr.microsoft.com/mssql/server:2017-latest

//2. create custom trace message sinks, this can be your own logger framework
var traceService = new ConsoleTraceService { IsDebugEnabled = true };

//3. run migrations
app.UseYuniql(traceService, new Yuniql.AspNetCore.Configuration
{
    Platform = SUPPORTED_DATABASES.SQLSERVER,
    Workspace = Path.Combine(Environment.CurrentDirectory, "_db"),
    ConnectionString = "Server=localhost,1400;Database=helloyuniql;User Id=SA;Password=P@ssw0rd!;TrustServerCertificate=True",
    IsAutoCreateDatabase = true, IsDebug = true
});

Working with .NET Core Console Application

Run your database migration when Console App starts. Developer guide is available here https://yuniql.io/docs/migrate-via-netcore-console-application.

dotnet add package Yuniql.Core

using Yuniql.Core;
...
...

static void Main(string[] args)
{
    //1. deploy new sql server on docker or use existing instance
    //$ docker run -dit -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd!" -p 1400:1433 -d mcr.microsoft.com/mssql/server:2017-latest

    //2. create custom trace message sinks, this can be your own logger framework
    var traceService = new ConsoleTraceService { IsDebugEnabled = true };

    //3. configure your migration run
    var configuration = Configuration.Instance;
    configuration.Platform = SUPPORTED_DATABASES.SQLSERVER;
    configuration.Workspace = Path.Combine(Environment.CurrentDirectory, "_db");
    configuration.ConnectionString = "Server=localhost,1400;Database=helloyuniql;User Id=SA;Password=P@ssw0rd!;TrustServerCertificate=True";
    configuration.IsAutoCreateDatabase = true;

    //4. run migrations
    var migrationServiceFactory = new MigrationServiceFactory(traceService);
    var migrationService = migrationServiceFactory.Create();
    migrationService.Run();

Advanced use cases

• How to bulk import CSV master data
• How to replace tokens in script files
• How to run environment-aware migrations
• How to baseline your database
• How yuniql works

Contributing & asking for help

Please submit ideas for improvement or report a bug by creating an issue. Alternatively, tag #yuniql on Twitter or drop me a message rdagumampanATgmail.com.

If this is your first time to participate in an open source initiative, you may look at issues labeled as first timer friendly issues. If you found an interesting case, you can fork this repository, clone to your dev machine, create a local branch, and make Pull Requests (PR) so I can review and merge your changes.

To prepare your dev machine, please visit https://github.com/rdagumampan/yuniql/wiki/Setup-development-environment

License

Copyright (C) 2019 Rodel E. Dagumampan

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Credits

Yuniql relies on many open-source projects and we would like to thanks:

• CommandlineParser for CLI commands
• CsvTextFieldParser for CSV file parsing
• Npgsql for PostgreSql drivers
• Shouldly for unit tests
• Moq for unit test mocks
• Evolutionary database design by Martin Fowler and Pramod Sadalage
• Microsoft, Oracle, for everything in dotnetcore seems open source now :)
• All the free devops tools! GitHub, AppVeyor, Docker, Shields.io ++

Maintainers