Flowsy.Db.Agent.Postgres 4.0.2

Flowsy Db Agent Postgres

This package provides the DbPostgresAgent class which extends the DbAgent class from the Flowsy.DbAgent package to improve performance for PostgreSQL databases.

The DbPostgresAgent overrides some methods from the DbAgent class to improve performance using the Npgsql library directly.

Using this Package as a Testing Tool

The following code snippet shows the features provided by this package, which can be used to prepare a database before operation in your execution or testing environment.

[TestCaseOrderer(PriorityOrderer.Name, PriorityOrderer.Assembly)]
public class DbPostgresAgentTest : IClassFixture<PostgresDatabaseFixture>
{
    private readonly DbPostgresAgent _dbAgent;
    private readonly ITestOutputHelper _output;

    public DbPostgresAgentTest(PostgresDatabaseFixture postgresDatabaseFixture, ITestOutputHelper output)
    {
        var provider = DbProvider.GetInstance(DbProviderFamily.PostgreSql);
        
        _dbAgent = new DbPostgresAgent(provider, postgresDatabaseFixture.ConnectionString);
        
        _dbAgent.StatementPrepared += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            if (string.IsNullOrEmpty(args.ScriptPath))
                output.WriteLine("[ {0} ] [ statement prepared ]{1}{2}", databaseUrl, Environment.NewLine, args.Statement);
            else
                output.WriteLine("[ {0} ] [ statement prepared ] [ {1} ]{2}{3}", databaseUrl, args.ScriptPath, Environment.NewLine, args.Statement);
            
            output.WriteLine(string.Empty);
        };
        _dbAgent.StatementExecuted += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            if (string.IsNullOrEmpty(args.ScriptPath))
                output.WriteLine("[ {0} ] [ statement executed ]{1}{2}", databaseUrl, Environment.NewLine, args.Statement);
            else
                output.WriteLine("[ {0} ] [ statement executed ] [ {1} ]{2}{3}", databaseUrl, args.ScriptPath, Environment.NewLine, args.Statement);
            
            output.WriteLine(string.Empty);
        };
        _dbAgent.MigrationStepExecuted += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            output.WriteLine("[ {0} ] [ migration step executed ] [ {1} ]", databaseUrl, args.Statement);
            output.WriteLine(string.Empty);
        };
        _dbAgent.RecordImporting += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            var data = string.Join(" | ", args.Data.Select((k, v) => $"{k}: {v}"));;
            output.WriteLine("[ {0} ] [ record importing ] [ {1} ]", databaseUrl, data);
            output.WriteLine(string.Empty);
        };
        _dbAgent.RecordImported += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            var data = string.Join(" | ", args.Data.Select((k, v) => $"{k}: {v}"));;
            output.WriteLine("[ {0} ] [ record imported ] [ {1} ]", databaseUrl, data);
            output.WriteLine(string.Empty);
        };
        _dbAgent.SetupOperationPrepared += (_, args) =>
        {
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            if (args.Operation.Risk == DbOperationRisk.High)
                args.Cancel = true;
            
            output.WriteLine("[ {0} ] [ setup operation {1} ] [ {2} : {3} ] [ Risk : {4} ] {5}", databaseUrl, args.Cancel ? "canceled" : "prepared", args.Operation.Key, args.Operation.Type, args.Operation.Risk, args.Operation.Description);
            output.WriteLine(string.Empty);
        };
        _dbAgent.SetupOperationExecuted += (_, args) =>
        { 
            var databaseUrl = GetDatabaseUrl(args.Provider, args.Host, args.Database);
            output.WriteLine("[ {0} ] [ setup operation executed ] [ {1} : {2} ] [ Risk : {3} ] {4}", databaseUrl, args.Operation.Key, args.Operation.Type, args.Operation.Risk, args.Operation.Description);
            output.WriteLine(string.Empty);
        };
        
        _output = output;
        
        _output.WriteLine($"DbAgent created: [{_dbAgent.Provider}] {_dbAgent.Host.Address}:{_dbAgent.Host.Port}/{_dbAgent.DefaultDatabase}");;
    }
    
    private string GetDatabaseUrl(DbProvider provider, DbHost host, string? databaseName)
    {
        var databaseUrl = $"{provider.Family}:://{host.Address}:{host.Port}";
        if (databaseName is not null)
            databaseUrl += $"/{databaseName}";
        return databaseUrl;
    }
    
    [Fact]
    [Priority(1)]
    public async Task Should_CreateAndDropFakeDatabase()
    {
        // Arrange
        const string databaseName = "fake_db";
        
        // Act & Assert
        var databaseExists = await _dbAgent.DatabaseExistsAsync(databaseName);
        _output.WriteLine($"Database {databaseName} exists: {databaseExists}");
        Assert.False(databaseExists);
        
        var databaseCreated = await _dbAgent.CreateDatabaseAsync(true, databaseName);
        _output.WriteLine($"Database {databaseName} created: {databaseCreated}");
        Assert.True(databaseCreated);
        
        databaseExists = await _dbAgent.DatabaseExistsAsync(databaseName);
        _output.WriteLine($"Database {databaseName} exists: {databaseExists}");
        Assert.True(databaseExists);
        
        var databaseDropped = await _dbAgent.DropDatabaseAsync(true, databaseName);
        _output.WriteLine($"Database {databaseName} dropped: {databaseDropped}");
        Assert.True(databaseDropped);
    }

    [Fact]
    [Priority(2)]
    public async Task DefaultDatabase_Should_Exist()
    {
        // Act
        var databaseExists = await _dbAgent.DatabaseExistsAsync();
        
        // Assert
        Assert.True(databaseExists);
    }
    
    [Fact]
    [Priority(3)]
    public async Task Should_Setup_Database()
    {
        // Arrange
        var setupConfiguration = Configuration.Instance.GetSection("Databases:PgTest:Setup");
        var operationConfigurations = setupConfiguration.GetChildren();
        var setupOptionsBuilder = new DbSetupOptionsBuilder();
        
        foreach (var operationConfiguration in operationConfigurations)
        {
            var operationKey = operationConfiguration.Key;
            var operationType = Enum.Parse<DbOperationType>(operationConfiguration["Type"]!);
            var operationRisk = Enum.Parse<DbOperationRisk>(operationConfiguration["Risk"]!);
            var operationDescription = operationConfiguration["Description"]!;
            
            DbOperationOptions? options = null;
            var optionsConfiguration = operationConfiguration.GetSection("Options");
            if (optionsConfiguration.Exists())
            {
                switch (operationType)
                {
                    case DbOperationType.DatabaseCreation:
                    {
                        var databaseName = optionsConfiguration["Database"];
                        var ifNotExists = optionsConfiguration.GetValue<bool>("IfNotExists");
                        options = new DbCreationOptions(databaseName, ifNotExists);
                        break;
                    }

                    case DbOperationType.DatabaseMigration:
                    {
                        options = new DbMigrationOptionsBuilder()
                            .WithSourceDirectory(optionsConfiguration["SourceDirectory"]!)
                            .WithMetadataSchema(optionsConfiguration["MetadataSchema"])
                            .WithMetadataTable(optionsConfiguration["MetadataTable"])
                            .Build();
                        break;
                    }
                    
                    case DbOperationType.ScriptExecution:
                    {
                        options = new DbScriptExecutionOptions(optionsConfiguration["ScriptPath"]!);
                        break;
                    }

                    case DbOperationType.DataImporting:
                    {
                        options = new DbImportOptionsBuilder()
                            .WithSourcePath(optionsConfiguration["SourcePath"]!)
                            .Build();
                        break;
                    }
                }
            }
            
            var operation = new DbOperation(operationKey, operationDescription, operationType, operationRisk, options);
            setupOptionsBuilder.WithOperation(operation);
        }
        var setupOptions = setupOptionsBuilder.Build();
        
        // Act
        var exception = await Record.ExceptionAsync(() => _dbAgent.SetupDatabaseAsync(setupOptions));
        
        // Assert
        Assert.Null(exception);
    }
    
    [Fact]
    [Priority(4)]
    public void Should_Find_Users_Customers_And_Employees()
    {
        // Arrange
        using var connection = _dbAgent.GetConnection();
        var userCount = 0;
        var customerCount = 0;
        var employeeCount = 0;
        
        
        // Act
        {
            using var userSelectionCommand = connection.CreateCommand();
            userSelectionCommand.CommandText =
                "SELECT user_id, email, password_hash, forename, surname, gender::text as gender, birthdate, creation_instant FROM security.user order by creation_instant;";
        
            using var userDataReader = userSelectionCommand.ExecuteReader();
            while (userDataReader.Read())
            {
                userCount++;
            
                var userId = userDataReader.GetGuid(0);
                var email = userDataReader.GetString(1);
                var passwordHash = userDataReader.GetString(2);
                var forename = userDataReader.GetString(3);
                var surname = userDataReader.GetString(4);
                object? gender = userDataReader.IsDBNull(5) ? null : userDataReader.GetString(5);
                object? birthdate = userDataReader.IsDBNull(6) ? null : DateOnly.FromDateTime(userDataReader.GetDateTime(6));
                var creationInstant = new DateTimeOffset(userDataReader.GetDateTime(7));
            
                _output.WriteLine($"User => {userId}, {email}, {passwordHash}, {forename}, {surname}, Gender: {gender ?? "NULL"}, Birthdate: {birthdate ?? "NULL"}, Creation: {creationInstant.LocalDateTime}");
            }
        }

        {
            using var customerSelectionCommand = connection.CreateCommand();
            customerSelectionCommand.CommandText = "SELECT * FROM sales.customer order by creation_instant;";
        
            using var customerDataReader = customerSelectionCommand.ExecuteReader();
            while (customerDataReader.Read())
            {
                customerCount++;
            
                var customerId = customerDataReader.GetGuid(0);
                object? userId = customerDataReader.IsDBNull(1) ? null : customerDataReader.GetGuid(1);
                var tradeName = customerDataReader.GetString(2);
                object? legalName = customerDataReader.IsDBNull(3) ? null : customerDataReader.GetString(3);
                object? taxId = customerDataReader.IsDBNull(4) ? null : customerDataReader.GetString(4);
                var creationInstant = new DateTimeOffset(customerDataReader.GetDateTime(5));
            
                _output.WriteLine($"Customer => {customerId}, {userId ?? "NULL"}, {tradeName}, {legalName ?? "NULL"}, {taxId ?? "NULL"}, Creation: {creationInstant.LocalDateTime}");
            }
        }
        
        {
            using var employeeSelectionCommand = connection.CreateCommand();
            employeeSelectionCommand.CommandText = "SELECT employee_id, user_id, forename, surname, gender::text as gender, birthdate, creation_instant FROM personnel.employee order by creation_instant;";
        
            using var employeeReader = employeeSelectionCommand.ExecuteReader();
            while (employeeReader.Read())
            {
                employeeCount++;
            
                var employeeId = employeeReader.GetGuid(0);
                object? userId = employeeReader.IsDBNull(1) ? null : employeeReader.GetGuid(1);
                var forename = employeeReader.GetString(2);
                var surname = employeeReader.GetString(3);
                object? gender = employeeReader.IsDBNull(4) ? null : employeeReader.GetString(4);
                object? birthdate = employeeReader.IsDBNull(5) ? null : DateOnly.FromDateTime(employeeReader.GetDateTime(5));
                var creationInstant = new DateTimeOffset(employeeReader.GetDateTime(6));
            
                _output.WriteLine($"Employee => {employeeId}, {userId ?? "NULL"}, {forename}, {surname}, Gender: {gender ?? "NULL"}, Birthdate: {birthdate ?? "NULL"}, Creation: {creationInstant.LocalDateTime}");
            }
        }
        
        // Assert
        Assert.NotEqual(0, userCount);
        Assert.NotEqual(0, customerCount);
        Assert.NotEqual(0, employeeCount);
    }
    
    [Fact]
    [Priority(5)]
    public async Task Should_ExecuteStatements()
    {
        // Arrange
        var statements = new[]
        {
            "CREATE SCHEMA IF NOT EXISTS test;",
            "CREATE TABLE test.beatle (id SERIAL NOT NULL PRIMARY KEY, name VARCHAR(255), creation_instant TIMESTAMPTZ DEFAULT CLOCK_TIMESTAMP());",
            "INSERT INTO test.beatle (name) VALUES ('John Lennon');",
            "INSERT INTO test.beatle (name) VALUES ('Paul McCartney');",
            "INSERT INTO test.beatle (name) VALUES ('George Harrison');",
            "INSERT INTO test.beatle (name) VALUES ('Ringo Starr');"
        };
        
        // Act
        var exception = await Record.ExceptionAsync(() => _dbAgent.ExecuteStatementsAsync(statements, CancellationToken.None));

        // Assert
        Assert.Null(exception);
    }
    
    [Fact]
    [Priority(6)]
    public void TestTable_Should_HaveData()
    {
        // Arrange
        using var connection = _dbAgent.GetConnection();
        using var command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM test.beatle;";
        
        // Act
        var recordCount = 0;
        using var dataReader = command.ExecuteReader();
        while (dataReader.Read())
        {
            var id = dataReader.GetInt32(0);
            var name = dataReader.GetString(1);
            var creationInstant = new DateTimeOffset(dataReader.GetDateTime(2));
            
            _output.WriteLine($"Id: {id}, Name: {name}, Creation Instant: {creationInstant.LocalDateTime}");
            
            recordCount++;
        }
        
        // Assert
        Assert.Equal(4, recordCount);
    }
}

The Should_Setup_Database method from the previous example relies on the following appsettings.json file:

{
  "Databases": {
    "DatabaseOne": {
      "ProviderInvariantName": "Npgsql",
      "ConnectionString": "<ConnectionString>",
      "Setup": {
        "Operation1": {
          "Description": "Create database",
          "Type": "DatabaseCreation",
          "Risk": "Low",
          "Options": {
            "IfNotExists": true
          }
        },
        "Operation2": {
          "Description": "Reset database",
          "Type": "ScriptExecution",
          "Risk": "High",
          "Options": {
            "ScriptPath": "Database/DatabaseOne/Scripts/destroy_schemas.sql"
          }
        },
        "Operation3": {
          "Description": "Run database migrations",
          "Type": "DatabaseMigration",
          "Risk": "Low",
          "Options": {
            "SourceDirectory": "Database/DatabaseOne/Migrations",
            "MetadataSchema": "maintenance",
            "MetadataTable": "migration_history"
          }
        },
        "Operation4": {
          "Description": "Run seed scripts",
          "Type": "ScriptExecution",
          "Risk": "Medium",
          "Options": {
            "ScriptPath": "Database/DatabaseOne/Seed/Scripts"
          }
        },
        "Operation5": {
          "Description": "Import data",
          "Type": "DataImporting",
          "Risk": "Medium",
          "Options": {
            "SourcePath": "Database/DatabaseOne/Seed/Data"
          }
        }
      }
    }
  }
}

You can define an appsettings.json file for each environment and have their operations merged according to .NET configuration patterns. For instance, you could extract the "Operation2" section to an appsettings.Test.json file, so that operation is only executed in your testing environment.

{
  "Databases": {
    "DatabaseOne": {
      "Setup": {
        "Operation2": {
          "Description": "Reset database",
          "Type": "ScriptExecution",
          "Risk": "High",
          "Options": {
            "ScriptPath": "Database/DatabaseOne/Scripts/destroy_schemas.sql"
          }
        },
      }
    }
  }
}

When you run your application in the testing environment, the appsettings.json and appsettings.Test.json files will be merged, and the operation corresponding to the "Operation2" section will be executed after creating the database and before running the migrations.