Support PostgreSql

[kuromukira]

do you support version 11 or 12 with the azure pipelines task?

[rdagumampan]

Hi @kuromukira, thanks for reaching out. No, I have yet to support PostgreSql in the Az DevOps Task. I think in the next week or two, I would be able to implement this.

I just hit a hard wall when dynamically loading plugins in .NET Core 3.0 self-contained app. But I think figured it now and just reviewing the platform tests. The platform tests have passed on v9.6 and v12.1.

[kuromukira]

Woah. That's really great! Can't wait for the release! Thank you and good luck.

[rdagumampan]

@kuromukira, good day. The latest build of az devops task now supports PostgreSql and MySql. Can you please test this if it works out for you. Please use connection string in this format. Would love to hear your findings. Thanks!

Server={HOST};Port=5432;Database={DATABASENAME};User Id={USERID};Password={USERPASSWORD};

References: https://www.connectionstrings.com/postgresql/

[kuromukira]

Hi @rdagumampan , thank you so much. Will try implementing this in one of our upcoming milestones. Will let you know. Thank you.

[kuromukira]

Hi @rdagumampan

I'm getting this error with Run Yuniql

Any ideas?

[rdagumampan]

Hi, can you shared the full log?

You can enrich the log by adding --debug in the Additional Arguments field.

[rdagumampan]

Hi @kuromukira, I just test run again against empty pgsql database and it went OK. I created a pgsql instance on https://www.elephantsql.com and create release pipeline.

As pre-requisite, the source db project should be in compliant structure like the samples here https://github.com/rdagumampan/yuniql/tree/master/samples/basic-postgresql-sample. I use this sample to feed into my az devops pipeline.

Here my task setup:

Results:

[kuromukira]

Hi @rdagumampan

Sharing the az pipeline log (from our release pipeline)

The platformdb-migration.sql was generated through our build pipeline and was uploaded as a build artifact. dotnet ef migrations script -o $(Build.ArtifactStagingDirectory)/migrations/platformdb-migration.sql

[rdagumampan]

@kuromukira, got it thanks! At a minimum, yuniql requires the main db migration directory to have these conventions. You can copy our sample or use yuniql CLI to generate this.

You can create this structure in several ways: Option 1: Clone yuniql repo and copy the structure from our samples for pgsql. See this https://github.com/rdagumampan/yuniql/tree/master/samples/basic-postgresql-sample. You can commit this as separate git repo and place your baseline scripts in v0.00 folder.

Option 2: Install yuniql locally to setup the structure. You can do this like this if you have choco.

choco install yuniql --version 0.328.0

cd c:\temp
md testdb

yuniql init

You can also do dotnet tool install -g yuniql.cli or download directly from github/releases here https://github.com/rdagumampan/yuniql/releases/download/latest/yuniql-cli-win-x64-latest.zip

I hope this works out for you. HTH/Rodel

[rdagumampan]

If you intend to use yuniql primarily as an execution engine, you can the create structure before you run the migration:

1. Add task "Command Line", before Run YUNIQL task
2. Add the following scripts

#requires .netcore 3.0
dotnet tool install -g yuniql.cli

cd  $(Build.ArtifactStagingDirectory)/migrations
yuniql init

dotnet ef migrations script -o $(Build.ArtifactStagingDirectory)/migrations/v0.00/platformdb-migration.sql

This is not the ideal usage fore yuniql but it may work for you :) HTH/Rodel

[kuromukira]

Hi @rdagumampan, thank you for the suggestions.

I have tried your suggestion here and my release pipe failed with the same error. Below are my build and release pipeline setups:

Build Pipeline: with the Publish Artifact task configured as:

Release Pipeline:

Our current architecture also has around 7 Web API projects and only 1 API handles the migration (set as migration assembly in EF Core). I looked at the logs and yuniql initializes the default conventions you mentioned here in the API project folder and not in the migrations folder.

[kuromukira]

one more thing, you seem to have a value for

[rdagumampan]

Hi @kuromukira,

I think you are very close to getting this to work :)

Try to change the value of Target workspace directory in the Run YUNIQL task. This parameter refers to root directory where the base structure is placed. Try these value: $(System.DefaultWorkingDirectory)/_db-ci/Migration Script/migrations

one more thing, you seem to have a value for

The values here are to demonstrate token replacement feature. We have described some of the use cases here https://github.com/rdagumampan/yuniql/wiki/How-to-apply-token-replacement. You can leave this blank.

NOTE: If this runs successfully, your next run will skip v0.00 because the DB already runs in this version of your ef generated schema. If your intent for recreate db every time, you will have to create cleanup script and place in _erase directory. Then you can run yuniql erase -p <your-db-project-directory> -p <your-connection-string>

This sample how to do this via Command Line task:

dotnet tool install -g yuniql.cli
cd $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\basic-postgresql-sample

yuniql erase --platform postgresql -c "$(AzPostgreSqlDemoDatabase)"

[rdagumampan]

Would be helpful maybe if you try to simulate this locally. We can do it like this.

choco install yuniql --version 0.328.0

cd c:/temp
md testdb
cd testdb

yuniql init
dotnet ef migrations script -o "c:/temp/testdb/v0.00/platformdb-migration.sql"

Run migrations

yuniql run -p "c:/temp/testdb" -c "<your-connection-string>"
yuniql info "<your-connection-string>"

Reference: https://github.com/rdagumampan/yuniql/wiki/Install-yuniql

[kuromukira]

Hi @rdagumampan

This fixed the issue I've been having. Now I think my problem is with the connection string.

[rdagumampan]

@kuromukira, goo to hear. Try to use connection string in this format Server={HOST_SERVER};Port=5432;Database={DB_NAME};User Id={USER_ID};Password={USER_PASSWORD};

Reference: https://www.connectionstrings.com/postgresql/

HTH/Rodel

[kuromukira]

@rdagumampan it seems that the task does not pick up the connection string if it's set as a secret in the variables tab of the release pipeline. I managed to make it work by adding it directly to the task and by adding as a plain text variable

Additional question, Did this create a new table?

[rdagumampan]

Yuniql will attempt to create this table __yuniqldbversion once. This is used for tracking versions already applied in the target database. When your first run failed due to connection issues, this will not be created. Appreciate if you can verify, I think I covered this with test.

[rdagumampan]

@rdagumampan it seems that the task does not pick up the connection string if it's set as a secret in the variables tab of the release pipeline. I managed to make it work by adding it directly to the task and by adding as a plain text variable

Hmm.. I just tried to reproduce and it seems to have worked fine.

[kuromukira]

NOTE: If this runs successfully, your next run will skip v0.00 because the DB already runs in this version of your ef generated schema. If your intent for recreate db every time, you will have to create cleanup script and place in _erase directory. Then you can run yuniql erase -p <your-db-project-directory> -p <your-connection-string>

In my release pipeline, the v0.00 is constant and will remain unchanged regardless of the number of releases created.

And in relation to

Yuniql will attempt to create this table __yuniqldbversion once. This is used for tracking versions already applied in the target database. When your first run failed due to connection issues, this will not be created. Appreciate if you can verify, I think I covered this with test.

Will it fail if it detects that the version created every release remains unchanged? Idk why but here in the pipeline, it gets connection timeout. The connection string is working fine when connecting via pgAdmin and the asp.net core APIs themselves.

[rdagumampan]

On connection timeout issue, it maybe a firewall issue. When i test in Azure SQL i have to grant access to az devops, it may be the same for AWS. I can try later at home. Is it Amazon RDS for PostgreSql? Or Aurora?

On v0.00, every version folder is an atomic version of your database. V0.00 is what we called baseline version. When it runs OK, a row is created in __yuniqldbversion table. The next time you run migration, yuniql checks the latest changes applied in target db and whats newer locally. It expects at least a version folder higher than v0.00 or it will do nothing. The sql dump from ef in v0.00 while an updated one will not be executed.

You may dynamically generate the version folder by using the Build variables in az devops to emulate increasing versions but this can be problematic if you have to rexexute scripts that create table that already exists from your previous exexutions.

[kuromukira]

@rdagumampan i see. it's probably an issue with AWS RDS for PgSQL.

Additional information below: I modified my pipeline to create a custom directory name for each execution of the release pipeline eg 1.0.$(Build.BuildNumber)

[rdagumampan]

I modified my pipeline to create a custom directory name for each execution of the release pipeline eg 1.0.$(Build.BuildNumber)

Looks like something is broken in your directory structure. Version folders have to be in this format v{major}.{minor}. When you create custom directory, check what value the BuildNumber gives. Maybe you have to create directory this way md v1.0.$(rev:r)

P.S. With connection timeout, let me know if this actually a firewall issue. I can spare sometime this weekend to pair with you on this. Just ping a time in Denmark time. HTH

[kuromukira]

Hi @rdagumampan , just an update, we decided to use the context.Database.Migrate instead (for now). There might be some performance bottlenecks tho. I will use this issue thread as reference in the future. Thank you.

[rdagumampan]

@kuromukira, thanks for trying. I will simulate your case anyway this week as I think we can find nice flavor between ef and yuniql. You can do code-first migrations, then dump sql file so you can further optimize and use yuniql as execution engine in pipeline.

I will keep you posted here when I got this to work and how.

[kuromukira]

@rdagumampan thank you. keep up the great work you have here. I will revisit this again as I'm pretty sure we will be needing this someday.

[kuromukira]

Hi @rdagumampan, I just confirmed it might be a firewall issue with AWS RDS. I recently tried creating a build pipeline that executes dotnet ef database update and got the same error with the run yuniql task regarding npgsql connection timeout.

I also tested the run yuniql against a Google Cloud SQL PostgreSQL instance. No errors and migration completed.

Question tho, what if I don't change the version folder for Yuniql (eg will remain as v0.00)? Will it still execute the migration?

[rdagumampan]

@kuromukira thanks for following through. Good to hear you have had progress. I have yet to post my notes here but yes I did created Amazon RDS PostgreSql and I was able to repro your connection timeout issue. I have set the firewal to Anywhere and it all works fine. I don't think this is ideal to use Anywhere but I will look at IP Range used by Azure DevOps Agents.

Meanwhile, I have made progress in finding the ef+yuniql flavor here. I find this very interesting use case where we use code-first for development, generate scripts for migration, review and optimize scripts and run migration with yuniql. I have yet to put this in CI/CD but this should describe the flow to you.

https://github.com/rdagumampan/yuniql/tree/master/samples/postgresql-efcore-sample

[rdagumampan]

Question tho, what if I don't change the version folder for Yuniql (eg will remain as v0.00)? Will it still execute the migration?

yuniql will skip this folder because it assumes that version has already been applied to target database. Imagine if that version is creating a table Employee, it will fail if it attempts to create a new employee table. If you are recreating the database every pipeline run, you can create cleanup scripts and place in _erase directory and use yuniql erase to execute before you run the migration. This would erase all tables in target db every time. With this v0.00 will always be executed.

If this is not the case, the best approach is to dynamically create the version directory using Build.BuildNumber variable. You just have to test it out first what the value it generates. I think this is the right value of your Release name format under Options v0.0.$(rev:r)

Then in your CommandLine Task, use md $(Build.BuildNumber)

[kuromukira]

hi @rdagumampan , thanks. that is exactly the use-case i was doing. ef core code-first migrations. so far, yuniql works as intended but only through GCP CloudSQL (which we decided to move to). i also got the $BuildNumber and $rev working for the folder names just to make sure yuniql executes the migration.

[rdagumampan]

@kuromukira , i have a working sample now to cover efcore+yuniql. See notes below. HTH

AWS Firewall settings, allow Anywhere for testing

Accessible via pgAdmin

Azure DevOps Release Pipeline

Options

Tasks

Powershell Inline Statements

#install cli tools
Write-Output "----installing yuniql cli"
dotnet tool install -g yuniql.cli
Write-Output "----installing ef cli"
dotnet tool install -g dotnet-ef

#build the ef project
cd $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\postgresql-efcore-sample
dotnet build efsample.csproj

#prepare yuniql required dir structure
md _db
cd _db
yuniql init

#create migration script from the last known migration id (not based on actual db history)
Write-Output "----capturing list of migration steps available"
cd $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\postgresql-efcore-sample
dotnet ef migrations list --no-build >> current_efversions.txt

#show the content of current_efversions.txt
Write-Output "----show the content of current_efversions.txt"
Get-Content -Path ".\current_efversions.txt"

$fromVersion = Get-Content -Path ".\current_efversions.txt" | Select-Object -Skip 1 | Select-Object -Last 2 | Select-Object -First 1
Write-Output "----Scripting migration from: $fromVersion"
dotnet ef migrations script $fromVersion  -o $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\postgresql-efcore-sample\_db\$(Release.ReleaseName)\migrate.sql --no-build
Remove-Item .\current_efversions.txt

#show the content of migrate.sql
Write-Output "----show the content of migrate.sql"
Get-Content -Path $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\postgresql-efcore-sample\_db\$(Release.ReleaseName)\migrate.sql

Write-Output "----show directory structure of yuniql compliant db workspace"
cd $(System.DefaultWorkingDirectory)\_rdagumampan_yuniql\samples\postgresql-efcore-sample\_db
dir 

[kuromukira]

hi @rdagumampan , thank you for the example.

[rdagumampan]

@kuromukira,

hi @rdagumampan , thank you for the example.

Np. If the fix above is so far enough to get you going, appreciate if you can give the repo a star :). It'll be helpful when I start the campaign after release.

This is not robust solution as I rely on every migration run to be successful when I pickup the fromVersion . I wish efcore exposes API to capture the current version of database from EFMigrationHistory table so we can get the actual last migration succesfuly applied.

As alternative, you can expose an endpoint in your Web API to get last migration applied by efcore in your target database. We use that instead as value of from when doing ef migrations script.

HTH

[kuromukira]

hi @rdagumampan . I encountered a problem earlier on one of our release pipelines for our staging environment. Seems like there's an update to the table structure of __yuniqldbversion? That caused the pipeline to fail somehow. Will get to test this again after 2 weeks during the next milestone release to staging. Will keep you posted.

[rdagumampan]

@kuromukira lost track of our discussions here. I should have ping you but this is one of the breaking changes in the last release v0.350.0. I have renamed the columns, drop some and add new ones in this tracking table. I hope that this is final but I'll keep you posted should there be breaking change again.

[kuromukira]

@kuromukira thank you.

[rdagumampan]

@kuromukira , if you are still using yuniql in your project, I will be releasing a beta release this week. There are many breaking changes in this build. FYI.

I'm closing this case now. Please file new ticket should you still have issues. Thanks for trying.

[kuromukira]

Hi @rdagumampan , yup I am still using yuniql (it's very good btw). Thank you for informing me about the beta release. I will keep you posted for any issues from the breaking changes.

[rdagumampan]

@kuromukira ,

FYI, I have just release v0.465.0 this morning. This is a BETA RELEASE and maybe have breaking changes on your pipelines. Please refer to this release note and create ticket if you found an issue.

https://github.com/rdagumampan/yuniql/releases/tag/v0.465.0

Thanks for your interest with yuniql. Cheers, Rodel

[kuromukira]

hi @rdagumampan , we are on a 2 week-cycle for each milestone. last weekend we had a successful deployment to our staging using yuniql for db migrations. no problems so far. we will be able to test again next next weekend. will let you know if there are any issues.

[somilygz]

Hi @rdagumampan, 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. My tables contain large amounts of data and take a long time to delete. I am trying to delete more than 300 tables.

My script is similar to this wuth the all 300 tables. BEGIN; DROP TABLE IF EXISTS dd_dyp_pro.isrretencionesusopt CASCADE; COMMIT; BEGIN; DROP TABLE IF EXISTS dd_dyp_pro.ivapfpagodefinitivopt CASCADE; COMMIT; BEGIN; DROP TABLE IF EXISTS dd_dyp_pro.ivaprestacionservdigitales CASCADE; COMMIT; BEGIN; DROP TABLE IF EXISTS dd_dyp_pro.ivaretencionesusopt CASCADE; COMMIT; BEGIN; DROP TABLE IF EXISTS dd_dyp_pro.pd_pago CASCADE; COMMIT;

[Naimishah]

Hi @rdagumampan while setting up PostgreSQL Azure release am facing this error below is my connection string please help me Server=ella.db.elephantsql.com, Port=5432;Database=wlwvedlt; User ID=wlwvedlt; Password=G7HaU-zTjvgd5kI1F3t576XruLSiipvd;