dbup / dbup-postgresql Goto Github PK

PostgreSQL provider for DbUp

License: MIT License

C# 99.46% Smalltalk 0.54%

dbup-postgresql's Introduction

DbUp is a set of .NET libraries that helps you to deploy changes to different databases like SQL Server. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

Documentation

To learn more about DbUp check out the documentation.

Status

Package		Stable	Latest Stable	Latest Prerelease	Issues	Pull Requests
Documentation
DbUp-Core
DbUp-SqlServer
DbUp-MySql
DbUp-SQLite
DbUp-PostgreSQL
DbUp-Firebird
DbUp-Oracle
DbUp-Redshift

End of Life Providers

The following providers are End of Life (EOL) and are no longer maintained:

Sql Anywhere
SqlCE
SQLite (Mono)

Extensions by the community

Maintenance and support for the extensions where not provided by the DbUp project. For questions and support on these extensions, please contact the project directly.

DbUpX

https://github.com/fiscaltec/DbUpX

Extensions to DbUp supporting easy filtering, ordering and versioning:

a journaling system that stores hashes of script contents, so we know if they need to rerun,
a concept of "dependency comments" in scripts that let you more easily control the ordering of scripts,
protection against code reorganisation affecting long script names,
utilities for sorting and filtering scripts in helpful ways.

Contributing

NuGet Feed

To build the providers, you will need to add the DbUp NuGet feed to your local machine. Do this by generating a GitHub token with read:package access and running:

dotnet nuget add source --name DbUp --username <YourUsername> --password <TheToken> https://nuget.pkg.github.com/DbUp/index.json

dbup-postgresql's People

Contributors

Stargazers

Watchers

Forkers

dterziev toizo scrocquesel tributech-solutions markotter campbellhardingdeason

dbup-postgresql's Issues

Need an API to Ensure schema exists in postgressql

When working with Postgresql. I am using docker instance as I develop locally to spin up and test my code, in this case I am using ensure database so that it creates the database when I spin up new docker container. But the code fails as the schema does not exist and I have to create a schema manually. It would help if we have a ensure schema API.

Allow variable escaping for PostgreSQL scripts

If SQL script contains something like $BODY$ (used in postgresql to create functions), exception happens:

Upgrade failed due to an unexpected exception: System.InvalidOperationException: Variable BODY has no value defined at DbUp.Engine.Preprocessors.VariableSubstitutionSqlParser.<>c__DisplayClass3_0.<ReplaceVariables>b__1(String name) at DbUp.Support.SqlParser.Parse() at DbUp.Engine.Preprocessors.VariableSubstitutionSqlParser.ReplaceVariables(IDictionary2 variables) at DbUp.Engine.Preprocessors.VariableSubstitutionPreprocessor.Process(String contents) at DbUp.Support.ScriptExecutor.PreprocessScriptContents(SqlScript script, IDictionary2 variables) at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary2 variables) at DbUp.Engine.UpgradeEngine.PerformUpgrade()

SQL script is like this:

CREATE FUNCTION public.fn_something( b_id integer) RETURNS integer LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ BEGIN RETURN ( select count(*) as something from sometable where id=b_id); END $BODY$;

Any other idea than renaming $BODY$ to something which will not be picked up as variable?

PostgreSQL 14.7, 11.19 incompatibility "CREATE INDEX CONCURRENTLY cannot be executed within a pipeline"

Environment

DbUp Version: 6.0.4
Database Vendor & Version: PostgreSQL 14.7, 11.19
Operating System Type & Version: Debian 10, Windows 11, MacOS 16.1

Description

After upgrade of PostgreSQL to the latest minor versions: 14.7 and 11.19 (we support and test on these two branches 14 & 11) any migration with "CREATE INDEX CONCURRENTLY" failing with:

DB exception has occured in script: '1.56.0#0005/changes/1.56.0/MLRSSL-732 - jrnl_ff_transaction.sql'
Script block number: 0; Message: 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
Npgsql.PostgresException (0x80004005): 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at DbUp.Support.ScriptExecutor.ExecuteNonQuery(IDbCommand command)
   at DbUp.Postgresql.PostgresqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
  Exception data:
    Severity: ERROR
    SqlState: 25001
    MessageText: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline
    File: xact.c
    Line: 3433
    Routine: PreventInTransactionBlock

Migration example:

drop index idx_ff_transaction_partner_ext_id2;
create index concurrently idx_ff_transaction_partner_ext_id2
    on jrnl_ff_transaction (partner_id, transaction_ext_id);

Table affected (minimal example to reproduce):

create table public.jrnl_ff_transaction
(
    transaction_ext_id        varchar(150),
    partner_id                integer
);

Steps To Reproduce

Create SQL migration, like in the example above, using clean PostgreSQL docker image (postgres:14.7-bullseye, postgres:11.19-bullseye).

Actual Behavior

Exception in migration log. See above.

Expected/Desired Behavior

Execution without exception.

Additional Context

Downgrading to 14.6, 11.18 respectively fixes the issue, and we used it as makeshift workaround.

Similar issue has been reported in Luquibase#3806 which also mentions that version 15.7 is also affected.

Also see PostgreSQL expert answer here.

PostgresqlConnectionManager create connection from NpgsqlDatasource

Is there an existing issue for this?

I have searched the existing open and closed issues

Description

Npgsql allows to configure the connection settings by code. For example, provide the password at connection time with help of password provider or periodic password rotation on the NpgsqlDatasource.

PostgresqlConnectionManager should take a NpgsqlDatasource and create connection from it.

public PostgresqlConnectionManager(NpgsqlDataSource datasource)
    : base(new DelegateConnectionFactory(l => datasource.OpenConnection()))
{
}

What is the impact?

NpgsqlDatasource has been introduced in a more recent Npgsql version than the one currently used and it doesn't target netstandard1.3.

Allow creating PostgreSQL database "with owner"

The PostgreSQL CREATE DATABASE statement requires a superuser account and the WITH OWNER clause allows you to change ownership to, say, a normal service account.

It would be super nice to have this option in the PostgresqlDatabase method to minimize the use of a superuser connection string.

            sqlCommandText = string.Format
                (
                    "create database \"{0}\";",
                    databaseName
                );

https://github.com/DbUp/DbUp/blob/master/src/dbup-postgresql/PostgresqlExtensions.cs#L189-L193

DbUp not creating schema for Journal

I'm trying to deploy a new PostgresSQL instance into Cloud SQL (GCP) using DbUp and i'm getting this error below:

Beginning database upgrade
Checking whether journal table exists..
Journal table does not exist
Executing Database Server script 'OperationalDatabase.OperationalDbUp.Gateway.Scripts.202209061040-00-gcp_gateway.sql'
Checking whether journal table exists..
Creating the "gateway"."schemaversions" table
DB exception has occured in script: 'OperationalDatabase.OperationalDbUp.Gateway.Scripts.202209061040-00-gcp_gateway.sql'
Script block number: -1; Message: 3F000: schema "gateway" does not exist
Npgsql.PostgresException (0x80004005): 3F000: schema "gateway" does not exist
   at Npgsql.NpgsqlConnector.DoReadMessage(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadMessage(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications)
   at Npgsql.NpgsqlConnector.ReadMessage(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at DbUp.Support.TableJournal.EnsureTableExistsAndIsLatestVersion(Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Engine.Transactions.NoTransactionStrategy.Execute(Action`1 action)
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection(Action`1 action)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
Upgrade failed due to an unexpected exception:
Npgsql.PostgresException (0x80004005): 3F000: schema "gateway" does not exist

CODE

GatewayDbUp:

using System;
using System.Reflection;
using DbUp.Builder;

namespace OperationalDatabase.OperationalDbUp.Gateway
{
    public class GatewayDbUp : OperationalDbUpHandler
    {
        private readonly bool _enabled;

        public GatewayDbUp()
        {
            bool.TryParse(Environment.GetEnvironmentVariable("GATEWAY_DB_UP_ENABLED"), out _enabled);
        }

        public override void Handle()
        {
            if (_enabled)
            {
                UpTables(GetUpgradeEngineBuilder());
            }

            base.Handle();
        }

        internal void UpTables(UpgradeEngineBuilder upgradeEngineBuilder)
        {
            var engine = upgradeEngineBuilder
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(),
                    name => name.StartsWith("OperationalDatabase.OperationalDbUp.Gateway.Scripts."))
                .JournalToPostgresqlTable("gateway", "schemaversions")
                .LogToConsole()
                .Build();

            CheckUpgrade(engine.PerformUpgrade());
        }
    }
}

OperationalDbUpHandler: (I have tried to enforce the journal here but it didn't work either)

using System;
using DbUp;
using DbUp.Builder;
using DbUp.Engine;

namespace OperationalDatabase.OperationalDbUp
{
    public class OperationalDbUpHandler : IHandler
    {
        private readonly string _connectionString;
        private static readonly bool _createDatabaseIfNotExists = false;
        private IHandler _nextHandler;

        protected OperationalDbUpHandler()
        {
            _connectionString =
                $"User ID={Environment.GetEnvironmentVariable("USERNAME")};" +
                $"Password={Environment.GetEnvironmentVariable("PASSWORD")};" +
                $"Host={Environment.GetEnvironmentVariable("HOST")};" +
                $"Port={Environment.GetEnvironmentVariable("PORT")};" +
                $"Database={Environment.GetEnvironmentVariable("DATABASE")};Pooling=true;";
        }

        public IHandler SetNext(IHandler handler)
        {
            _nextHandler = handler;
            return handler;
        }

        public virtual void Handle()
        {
            _nextHandler?.Handle();
        }

        public void CreateDb()
        {
            if (_createDatabaseIfNotExists)
            {
                EnsureDatabase.For.PostgresqlDatabase(_connectionString);
            }
        }

        protected UpgradeEngineBuilder GetUpgradeEngineBuilder()
        {
            return DeployChanges.To.PostgresqlDatabase(_connectionString);
            //return DeployChanges.To.PostgresqlDatabase(_connectionString).JournalToPostgresqlTable("gateway", "schemaversions");
        }

        protected void CheckUpgrade(DatabaseUpgradeResult result)
        {
            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Console.ReadLine();

                throw result.Error;
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine($"Success {GetType().Name}!");
            Console.ResetColor();
        }
    }
}

Packages

	<ItemGroup>
		<PackageReference Include="DbUp" Version="4.6.0" />
		<PackageReference Include="dbup-postgresql" Version="4.6.3" />
	</ItemGroup>

SCRIPT

Script for file 202209061040-00-gcp_gateway.sql

CREATE SCHEMA IF NOT EXISTS "gateway"

We've created the gateway schema manually using the same postgresql crecentials that is being used at dbup Environment Variables. The psql user can execute the scripts directly in the console.

DEPLOY TOOLS

docker
docker-compose
k8s
CloudSQL (PostgreSQL v14.0)

TL;DR

DbUp can't create "gateway" schema at 1st deploy
Deploy goes fine when the "gateway" schema is already created or "public" schema already has the table "schemaversions"
I have already read the docs about "how do I" here: https://dbup.readthedocs.io/en/latest/more-info/journaling/

PostgresqlConnectionManager SplitScriptIntoCommands isn't splitting the script, causing unexpected transaction errors

I noticed this when trying to create a script with multiple DROP INDEX CONCURRENTLY statements in it. I was getting weird transaction errors even when not running in a transaction.

The regex pattern is ^\\s*;\\s*$. The ^ is causing this to only match if a ; is on a line by itself surrounded by whitespace.

That means the following script is being executed as a single statement when it should be separated into multiple statements. By executing as a single statement you get the error DROP INDEX CONCURRENTLY must be first action in transaction for index_2:

DROP INDEX CONCURRENTLY index_1;
DROP INDEX CONCURRENTLY index_2;
DROP INDEX CONCURRENTLY index_3;

I'm sure there's some edge cases for this as well to avoid picking up ; from within strings etc. I'll put up a PR to resolve the issue as best I can soon.

For anyone else getting this problem right now, as a workaround you can do the following. It looks silly but it will match the regex and cause the script to be split properly:

DROP INDEX CONCURRENTLY individual_full_name_trgm
;
DROP INDEX CONCURRENTLY individual_name_trgm
;
DROP INDEX CONCURRENTLY individual_phone_trgm
;

ScriptExecutor does not respect variabledEnabled for VerifySchema()

Despite having the setting at it's immediate disposal in a private field, the ScriptExecutor ignores this value when verifying schema through the AdHocSqlRunner.
var sqlRunner = new AdHocSqlRunner(dbCommandFactory, sqlObjectParser, Schema, () => true);
https://github.com/DbUp/DbUp/blob/master/src/dbup-core/Support/ScriptExecutor.cs#L78

This is problematic for those extending the PostgresqlScriptExecutor and overriding GetVerifySchemaSql(string schema); to perform additional actions, such as assigning privileges to the schema and creating related roles if non-existent, as dbup-postgresql is particularly sensitive to variable issues as per #1 .

Passfile keyword support for Postgresql connection string

Database connection and other secrets are set in a folder that is not under source control. We make use of Postgresql's password file option and the connection string keyword Passfile to use this effectively.

Due to dbup-postgresql using Ngsql version without the support for the Passfile connection string keyword; any attempt to call the migration fails with exception Keyword not supported message.

Can we update the dependency of Npgsql to v4 so that it includes the PR for accepting passfile keyword.

Thanks.

Npgsql version should be upgraded

Is there an existing issue for this?

I have searched the existing open and closed issues

Description

I see that currently, this package uses Npgsql v3.2.7, that was released in 2018. This is a version that is out of support and therefore I believe it should be updated to at least v6, if not the latest (v8).

What is the impact?

Apart from possible security issues, there are some breaking changes, e.g. we could not use VerifyFull SSL mode in our connection string.

Variable substitution tokens incompatible with PostgreSQL

$variablename$ is used by PostgreSQL as dollar quoting: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

It would be nice to be able to decide which token to use, or completely swap out the VariableSubstitutionPreprocessor.

Check for journal table existence slow on large PostgreSQL instance.

We have a Postgres aurora instance with ~600K tables. The metadata query that checks if the journal table already exists is very slow in this environment (~76 seconds on a "cold" database instance). This can cause query timeouts and lead to failed service deployments.

The query used by PostgresqlTableJournal is the generic sql metadata query, it is inherited from TableJournal. We could solve the problem by inheriting from PostgresqlTableJournal and replacing the query with a postgres specific query:

        protected override string DoesTableExistSql()
        {
            string tableFullName = string.IsNullOrEmpty(SchemaTableSchema) ? UnquotedSchemaTableName : (SchemaTableSchema + '.' + UnquotedSchemaTableName);
            return $"SELECT CASE WHEN to_regclass('{tableFullName}') IS NOT NULL THEN 1 ELSE 0 END";
        }

This query takes about 8 ms on a "cold" database instance.

The to_regclass function was added in PostgresSQL 9.4. Versions before 9.4 are no longer officially supported, so it might be worthwhile to put this fix into PostgresqlTableJournal.

Add drop database support for PostgreSQL

I would like to make a PR to add drop database support for PostgreSQL. Should I go ahead and make a PR for this @AdrianJSClark @droyad ?

Npgsql CVE-2024-32655

Is there an existing issue for this?

I have searched the existing issues

Library Version

5.0.40

What happened?

Vulnerability found in dependency Npgsql, needs to be upgraded to at least >= 4.0.14.
docker scout

Relevant log output

No response

dbup / dbup-postgresql Goto Github PK

dbup-postgresql's Introduction

Documentation

Status

End of Life Providers

Extensions by the community

DbUpX

Contributing

NuGet Feed

dbup-postgresql's People

Contributors

Stargazers

Watchers

Forkers

dbup-postgresql's Issues

Environment

Description

Steps To Reproduce

Actual Behavior

Expected/Desired Behavior

Additional Context

Is there an existing issue for this?

Description

What is the impact?

CODE

Packages

SCRIPT

DEPLOY TOOLS

Is there an existing issue for this?

Description

What is the impact?

Is there an existing issue for this?

Library Version

What happened?

Relevant log output

Recommend Projects

Recommend Topics

Recommend Org