Flowsy.Db.Agent.Postgres 2.0.0

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 _postgresAgent;
    private readonly ITestOutputHelper _output;

    public DbPostgresAgentTest(PostgresDatabaseFixture postgresDatabaseFixture, ITestOutputHelper output)
    {
        var connectionStringBuilder = new NpgsqlConnectionStringBuilder
        {
            ConnectionString = postgresDatabaseFixture.ConnectionString
        };
        
        var host = new DbHost(DbProvider.PostgreSql, connectionStringBuilder.Host!, connectionStringBuilder.Port);
        var credentials = new DbCredentials(connectionStringBuilder.Username!, connectionStringBuilder.Password!);
        
        _postgresAgent = new DbPostgresAgent(host, credentials, connectionStringBuilder.Database!);
        _postgresAgent.StatementPrepared += (_, args) => output.WriteLine($"StatementPrepared{(args.ScriptPath is not null ? $" [{args.ScriptPath}]" : "")}: {Environment.NewLine}{args.Statement}");
        _postgresAgent.StatementExecuted += (_, args) => output.WriteLine($"StatementExecuted{(args.ScriptPath is not null ? $" [{args.ScriptPath}]" : "")} [{args.RecordsAffected} record(s) affected]: {Environment.NewLine}{args.Statement}");
        _postgresAgent.MigrationStepExecuted += (_, args) => output.WriteLine(args.Statement);
        _postgresAgent.RecordImporting += (_, args) => output.WriteLine($"RecordImporting: {args}");
        _postgresAgent.RecordImported += (_, args) => output.WriteLine($"RecordImported: {args}");
        
        _output = output;
        
        _output.WriteLine($"DbAgent created: [{_postgresAgent.Host.Provider}] {_postgresAgent.Host.Address}:{_postgresAgent.Host.Port}/{_postgresAgent.DefaultDatabase}");;
    }
    
    [Fact]
    [Priority(1)]
    public async Task Should_CreateAndDropFakeDatabase()
    {
        // Arrange
        const string databaseName = "fake_db";
        
        // Act & Assert
        var databaseExists = await _postgresAgent.DatabaseExistsAsync(databaseName);
        _output.WriteLine($"Database {databaseName} exists: {databaseExists}");
        Assert.False(databaseExists);
        
        var databaseCreated = await _postgresAgent.CreateDatabaseAsync(true, databaseName);
        _output.WriteLine($"Database {databaseName} created: {databaseCreated}");
        Assert.True(databaseCreated);
        
        databaseExists = await _postgresAgent.DatabaseExistsAsync(databaseName);
        _output.WriteLine($"Database {databaseName} exists: {databaseExists}");
        Assert.True(databaseExists);
        
        var databaseDropped = await _postgresAgent.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 _postgresAgent.DatabaseExistsAsync();
        
        // Assert
        Assert.True(databaseExists);
    }

    [Fact]
    [Priority(3)]
    public async Task Should_MigrateDatabase()
    {
        // Arrange
        var migrationOptions = new DbMigrationOptions(
            sourceDirectory: "Database/PgTest/Migrations",
            metadataSchema: "migration",
            metadataTable: "changelog",
            outOfOrder: true
        );
        
        // Act
        var exception = await Record.ExceptionAsync(() => _postgresAgent.MigrateDatabaseAsync(migrationOptions, CancellationToken.None));
        
        // Assert
        Assert.Null(exception);
    }
    
    [Fact]
    [Priority(4)]
    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(() => _postgresAgent.ExecuteStatementsAsync(statements, CancellationToken.None));

        // Assert
        Assert.Null(exception);
    }
    
    [Fact]
    [Priority(5)]
    public void TestTable_Should_HaveData()
    {
        // Arrange
        using var connection = _postgresAgent.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);
    }
    
    [Fact]
    [Priority(6)]
    public async Task Should_RunScripts()
    {
        // Arrange
        const string scriptPath = "Database/PgTest/Fake/Scripts";
        
        // Act
        var exception = await Record.ExceptionAsync(() => _postgresAgent.RunScriptAsync(scriptPath, CancellationToken.None));
        
        // Assert
        Assert.Null(exception);
    }

    [Fact]
    [Priority(6)]
    public async Task Should_ImportData()
    {
        // Arrange
        var options = new DbImportOptionsBuilder()
            .WithSourcePath("Database/PgTest/Fake/Data")
            .Build();

        // Act
        var exception = await Record.ExceptionAsync(() => _postgresAgent.ImportDataAsync(options, CancellationToken.None));

        var userCount = 0;
        var customerCount = 0;
        var employeeCount = 0;
        if (exception is null)
        {
            using var connection = _postgresAgent.GetConnection();

            {
                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.Null(exception);
        Assert.NotEqual(0, userCount);
        Assert.NotEqual(0, customerCount);
        Assert.NotEqual(0, employeeCount);
    }
}