DEV Community: Rodel E. Dagumampan

Released yuniql v1.2.25. Multi-tenant support, Oracle and largest set of bug fixes

Rodel E. Dagumampan — Tue, 22 Feb 2022 06:14:18 +0000

Announcing release of yuniql v1.2.25. yuniql is a database version control and migration tool. Absolute zero runtime dependencies and use plain sql script files.

Every release is special and this is not an exception. This pandemic period presents a rare opportunity to review more PRs, follow-up on reported issues, build prioritized features and fix major bugs. In fact, perhaps this is the release with the most bug-fixes! A testament of our growing usage and feedback from our community. We can only thank you all for your issue tickets and contributions. And here are the highlights:

Multi-tenant database support

The idea behind this popular request is to perform schema versioning on shared database where objects are segmented by schema. This would also enable developers and teams to get dedicated schema version and evolve independently while using single installation. This is more important when teams are not permitted to create and destroy databases during dev/test. Get started here https://yuniql.io/docs/get-started/

yuniql run -a --meta-schema dev01 --platform sqlserver
yuniql run -a --meta-schema dev02 --platform sqlserver
yuniql run -a --meta-schema dev03 --platform sqlserver

yuniql run -a --meta-schema sales --platform sqlserver
yuniql run -a --meta-schema inventory --platform sqlserver
yuniql run -a --meta-schema manufacturing --platform sqlserver

Support Oracle Enterprise & Autonomous Database

This release comes with previous support for Oracle. The platform tests has been executed using Oracle Enterprise 12C on docker container and Oracle Autonomous Database. You can explore this feature and get started here https://yuniql.io/docs/get-started-oracle/

docker run --rm -dit --name oracle  -p 1521:1521  store/oracle/database-enterprise:12.2.0.1-slim

SETX YUNIQL_PLATFORM "oracle"
SETX YUNIQL_CONNECTION_STRING "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)));User Id=sys;Password=Oradoc_db1;DBA Privilege=SYSDBA;"
SETX YUNIQL_WORKSPACE "C:\temp\yuniql\samples\basic-oracle-sample"

yuniql run --debug
yuniql list --debug
yuniql erase --force --debug

Quick connectivity check with `yuniql check`

Can I resolve or ping the database server? Can I establish an Sql/Odbc connection to the master or catalog database? Will my connection string work if I run the migrations on this database server? Is my service account have permissions to establish a session? I hope this simple feature helps in diagnosing migration issues quickly.

yuniql check -c "Server=DESKTOP-ULR8GDO;Database=helloyuniql;Trusted_Connection=True;" -d --platform sqlserver

INF   2022-02-21 23:23:11Z   Verifying ping connectivity to server/cluster DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Ping connectivity to server/cluster DESKTOP-ULR8GDO - Successful
INF   2022-02-21 23:23:11Z   Verifying sql/odbc connectivity to master/catalog on DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Sql/odbc connectivity to master/catalog on DESKTOP-ULR8GDO - Successful
INF   2022-02-21 23:23:11Z   Verifying sql/odbc connectivity to database helloyuniql on DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Sql/odbc connectivity to database helloyuniql on DESKTOP-ULR8GDO - Successful

Drop your dev/test database with `yuniql destroy`

Warning: Needless to say, don't use this in production. Just don't. This feature enables testers to rapidly deploy test databases and destroy when the test execution completes. If yuniql erase is not sufficient, yuniql destroy might suffice. It's quite simple, you prepare the destroy scripts yourself and do the necessary handling and sequence to drop the database.

docker run --rm -dit -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=helloyuniql;User Id=SA;Password=P@ssw0rd!"
SETX YUNIQL_WORKSPACE "C:\temp\yuniql\samples\basic-sqlserver-sample"
SETX YUNIQL_PLATFORM "sqlserver" 

yuniql run -a --debug
yuniql list --debug
yuniql destroy --force --debug

Breaking changes

None

New features and enhancements

Support multi-tenant database
Support Oracle platform
yuniql check: Basic connectivity check to the target database.
yuniql destroy: Drops database using custom prepared scripts.
yuniql apply: A synonym for yuniql run to avoid conflict in docker run
trace-silent: Console window only log and no log files produced.
trace-sensitive-data: Print secrets and connection strings into console window and log files.
trace-directory: Set the directory where log files are created.
Many many bug fixes and code improvements

Bugs fixes

Directory names like v10.0.0 and higher versions are not taking as latest #249
Failed database updates should ideally fail with a non-zero exit code #198
MSSQL/New version: SUSER_SNAME() throw an exception: String or binary data would be truncated. #201
Providing table name for the schema version table cause error #202
I am implementing Erease Yuniql and I get the following error This NpgsqlTransaction has completed; it is no longer usable, the process deletes my tables in PostgreSQL (HyperScale) but flag that error. #204
Error when setting up multi-tenant on shared databases instance #207
Environment-aware migration not working. #208
-- environment argument does not work #214
CSV import towards SQLServer with a uniqueidentifier column #239
Fixed failing bulk import when destination tables uses Pascal case

Refactorings

Revise all test scripts to CREATE TABLE instead of CREATE PROC #221
Run platform tests in single database and not creating database per test case #220
Why Core is linked to SqlServer ??? #241
dynamically extract manifest data using reflection https://github.com/rdagumampan/yuniql/commit/74cba91c4ef954d0493052a68a6fd0e8db6441e6

Credits

Special thanks to @bobi591, @wahmedswl, @zwdor20, @black-eagle17, @automada, @gfnavarro for your significant contributions in this release. Also for everyone filing issue tickets and asking questions, thanks for all your feedback.

Run environment-aware database migrations with yuniql

Rodel E. Dagumampan — Mon, 01 Jun 2020 11:15:35 +0000

Background

Let me start this again by saying, I am writing this both to create awareness on yuniql; an open source database devops tool I am maintaining and to share some experiences that I hope would help our community make software better, faster, safer.

The solutions here works with SqlServer, PostgreSql and MySql. :) Not let's get to meat of the story...

One of the challenges when working with databases are variations of scripts and schema definitions driven by compliance to existing governance and practicalities.

Cross-database queries 🍵

Some organization requires database names to represent the environment where it's created. Let’s say we have HR and PAYROLL databases where each have DEV, TEST and PROD environments. As a result, in DEVELOPMENT environment we have HRDB-DEV and PAYROLLDB-DEV. This script certainly cannot be applied to TEST and PROD databases as the database names are suffixed with an environment code DEV.

CREATE VIEW [dbo].[vw_employee_timesheets]
AS
    SELECT E.employee_id, E.first_name, E.last_name, E.position, T.checkin_time_utc, T.checkout_time_utc 
    FROM [HRDB-DEV].[dbo].[employees] E
    INNER JOIN [PAYROLLDB-DEV].[dbo].[timesheets] T
    ON E.employee_id = t.employee_id
GO

Environment-specific scripts 🍈

In another case, we may want to create partitioned tables best-fit for big data in TEST and PROD environment. We can observe the relatively complex configuration of the same table when created in PROD.

CREATE PARTITION FUNCTION RangePartFunction (datetime)
AS RANGE RIGHT FOR VALUES ('20200101', '20200201');

CREATE PARTITION SCHEME RangePartScheme AS PARTITION RangePartFunction
TO ([PartBefore2020], [Part202001], [Part202002]);

CREATE TABLE [dbo].[timesheets](
    [timesheet_id] [int] IDENTITY(1,1) NOT NULL,
    [employee_id] [int] NOT NULL,
    [checkin_time_utc] [datetime] NOT NULL,
    [checkout_timeutc] [datetime] NULL,
 CONSTRAINT [PK_timesheets] PRIMARY KEY CLUSTERED ([timesheet_id] ASC)
) ON RangePartScheme (checkin_time_utc);

CREATE CLUSTERED INDEX IDX_Part On [dbo].[timesheets](datetime) 
ON RangePartScheme (checkin_time_utc);

But wait, this may not be necessary for local development and testing where a minimal set of samples are being loaded. A simpler model like this may just work.

CREATE TABLE [dbo].[timesheets](
    [timesheet_id] [int] IDENTITY(1,1) NOT NULL,
    [employee_id] [int] NOT NULL,
    [checkin_time_utc] [datetime] NOT NULL,
    [checkout_timeutc] [datetime] NULL,
 CONSTRAINT [PK_timesheets] PRIMARY KEY CLUSTERED ([timesheet_id] ASC)
) ON [PRIMARY];

Solutions

yuniql addresses this in two ways:

via token replacement
via environment-reserved directories/folders.

To begin with, lets create a new repository repository. A yuniql repository is nothing but a git-ready repository to hold all your scripts files. For guide on how to get started with yuniql, please visit https://yuniql.io/docs/.

# install yuniql cli
dotnet tool install -g yuniql.cli

# prepare your repository
cd c:\temp
md yuniql-environment-aware
cd c:\temp\yuniql-environment-aware

# initialize your repository
yuniql init

c:\temp\yuniql-environment-aware>yuniql init
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\_init
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\_pre
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\v0.00
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\_draft
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\_post
INF   2020-05-31T...   Created script directory c:\temp\yuniql-environment-aware\_erase
INF   2020-05-31T...   Created file c:\temp\yuniql-environment-aware\README.md
INF   2020-05-31T...   Created file c:\temp\yuniql-environment-aware\Dockerfile
INF   2020-05-31T...   Created file c:\temp\yuniql-environment-aware\.gitignore
INF   2020-05-31T...   Initialized c:\temp\yuniql-environment-aware.

Token replacement with yuniql 🍒

When using yuniql, we can specify tokens to be replaced during migration run. For the given case, we use ENVIRONMENT as token key.

CREATE VIEW [dbo].[vw_employee_timesheets]
AS
    SELECT 'hello yuniql!' message;

    --SELECT E.employee_id, E.first_name, E.last_name, E.position, T.checkin_time_utc, T.checkout_time_utc 
    --FROM [HRDB-${ENVIRONMENT}].[dbo].[employees] E
    --INNER JOIN [PAYROLLDB-${ENVIRONMENT].[dbo].[timesheets] T
    --ON E.employee_id = t.employee_id

GO

Then during migration run, we pass the token key/value pair. In the process, yuniql inspects all tokens in script files and replaces them.

yuniql run -a -k "ENVIRONMENT=DEV"
yuniql run -a -k "ENVIRONMENT=TEST"
yuniql run -a -k "ENVIRONMENT=PROD

Alternatively, we can pass multiple tokens in a single call.

yuniql run -a -k "ENVIRONMENT=DEV,USERNAME=rdagumampan,SOURCE=AzDevOpsTask"

Environment-reserved directories with yuniql 🍋

Sometimes it would be simpler to group all scripts in a single environment-reserved directory. While it forces us to make duplicate script files, it can also help us stay organized. In this sample, let’s create _development, _test and _production directories.

When we call yuniql run, we can pass --environment to demand an environment-aware migrations. yuniql discover all directories, sort and preapare for execution. When environment-reserved directory is present, it only picks the right directory.

yuniql run -a --environment development
yuniql run -a --environment test
yuniql run -a --environment production

Parting words ㊗️

Whoah! You have reached this far! Thanks! 🍻

Environment-aware migrations using token replacements and environment -reserved directories addressed the variations of scripts demanded by internal enterprise policies and guidelines. You may also find other use cases for token replacements such as annotating the scripts and baseline data.

P.S. Please support yuniql by clicking GitHub Star! For a young project like this, a star can help capture more user experiences and improve the tool in its early stage. https://github.com/rdagumampan/yuniql

Cheers!

Seed your SqlServer, PostgreSql and MySql databases with CSV files using yuniql migrations

Rodel E. Dagumampan — Sun, 31 May 2020 22:18:23 +0000

Background

Let me start by saying, I am writing this both to create awareness of a tool I have built and also to share my experiences that I hope helps the community make software better, faster, safer. :)

In the past 8 months, I have been building project yuniql (yuu-nee-kel); an open source schema versioning and database migration tool made with .NET Core. From the beginning, one of the primary use cases we tried to address is seeding our databases both for environment-specific deployment and for supporting integration tests. Every fresh databases carries initial set of data such as lookup tables and baseline data. Running integration tests and regression tests requires snapshots of data or samples to support the scenario being tested.

While we can always script out the initial data, an efficient way is to just drop CSV files into a folder and have this imported into destination tables during deployment, test execution or application startup. Using CSV files also allows our Test Engineers and Business SMEs prepare data themselves and deliver to development teams so it can be can be fed into regression tests.

Such is the birth of bulk import support of yuniql for CSV files as seed data.

Seeding database with CSV files and yuniql

In this article, I am using a sample database from sqltutorial.org. Lets prepare a baseline schema version of the database and seed regions, countries, location, departments and jobs tables. Then we will create new version for employees and dependents tables.

Step 1: Install yuniql CLI 👉

We begin by getting yuniql cli installed. We can install yuniql CLI in several ways via chocolatey, dotnet global tool or direct from source. Choose what works for you.

Option 1: Install via .NET Core Global Tool. Requires .NET Core 3.0 SDK installed.

dotnet tool install -g yuniql.cli

Option 2: Install via Chocolatey package manager. Requires choco installed.

choco install yuniql

Option 3: Use Powershell and append to PATH

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"
$Env:Path += ";c:\temp\yuniql-cli"

Option 4: Download zipped package containing yuniql.exe file and extract to our workspace directory.

https://github.com/rdagumampan/yuniql/releases/download/latest/yuniql-cli-win-x64-latest.zip

Step 2: Initialize schema repository, call `yuniql init` 🍨

yuniql init

When yuniql init is issued, a baseline directory structure. Each version directory represents an atomic version of our database executed in an all-or-nothing fashion. Utility directories are also created to cover pre and post migration activities. A typical starter project workspace looks like this.

Step 3: Create baseline schema structure 🍭

Our next step is to prepare our Baseline version. A Baseline version, is the v0.00 of our database schema and initial data. Baseline helps create full visibility of our database schema evolution. Here, we prepare set of CREATE TABLE scripts in setup-tables.sql file.

Step 4: Prepare and add CSV files in the baseline 🍹

Lets now prepare the CSV files. Each CSV file represents a fully qualified name of the destination table. File dbo.regions.csv will be bulk loaded into dbo.regions table. We may use other schema or keep it simple to regions.csv and it will use dbo as default schema.

Step 5: Let's do this, call `yuniql run` 🏃

docker run -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=helloyuniql;User Id=SA;Password=P@ssw0rd!"

Lets verify now by deploying our database locally. Here, we can use SQL Server container to speed up the process. In principle, we just need a database server we can connect to.

yuniql run -a

When yuniql run is issued the first time, it inspects the target database and creates required table to track the versions applied. The -a tells yuniql to create new database also. All script files in _init directory and child directories will be executed only this time. The order of execution is as follows _init, _pre, vx.xx, vxx.xx+N, _draft , _post.

All CSV files are bulk loaded when all script files are executed.

Lets verify how data looks now at SQL Server. Notice that we created dbo.__yuniqldbversions to keep record of versions already applied. This ensures the CSV files will not be imported again the next time we call yuniql run

Where do we go from here? 🚧

This is one of the first steps to enable continuous delivery of changes to our databases. We can take this further by committing this workspace into a git repository and establish a CI/CD pipelines to run the migrations to different environments. Yuniql is released also as free Azure DevOps Task and Docker image.

What about other database platforms? ❄️

A PostgreSql and MySql compatible scripts can be executed in the same way with --platform parameter.

yuniql run -a --platform postgresql
yuniql run -a --platform mysql

The latest release of yuniql supports CSV import for SQL Server, PostgreSql and MySql. I am now maturing the implementation for Snowflake DW, Azure Synapse Analytics, and CockroachDB which are equally exciting data platforms. Contributors are welcome! :)

Parting words ㊗️

Using CSV files to bulk import baseline data and test samples is an effective approach to seeding databases. CSV files also allows non-SQL users help developers prepare datasets to support the software development process. As yuniql also leverage the native bulk import APIs of the target platform such SqlBulkCopy for SqlServer, we can import large CSV files pretty fast.

Thanks if you have reached this far!!! 🍻 This is my DEV first post and I guess I had fun with emojis :D The source for this article can be cloned here

Thanks thanks!

DEV Community: Rodel E. Dagumampan

Released yuniql v1.2.25. Multi-tenant support, Oracle and largest set of bug fixes

Multi-tenant database support

Support Oracle Enterprise & Autonomous Database

Quick connectivity check with yuniql check

Drop your dev/test database with yuniql destroy

Breaking changes

New features and enhancements

Bugs fixes

Refactorings

Credits

Run environment-aware database migrations with yuniql

Background

Cross-database queries 🍵

Environment-specific scripts 🍈

Solutions

Token replacement with yuniql 🍒

Environment-reserved directories with yuniql 🍋

Parting words ㊗️

Seed your SqlServer, PostgreSql and MySql databases with CSV files using yuniql migrations

Background

Seeding database with CSV files and yuniql

Step 1: Install yuniql CLI 👉

Step 2: Initialize schema repository, call yuniql init 🍨

Step 3: Create baseline schema structure 🍭

Step 4: Prepare and add CSV files in the baseline 🍹

Step 5: Let's do this, call yuniql run 🏃

Where do we go from here? 🚧

What about other database platforms? ❄️

Parting words ㊗️

Quick connectivity check with `yuniql check`

Drop your dev/test database with `yuniql destroy`

Step 2: Initialize schema repository, call `yuniql init` 🍨

Step 5: Let's do this, call `yuniql run` 🏃