Flowsy.Db.Agent 2.0.1

Flowsy Db Agent

This package provides mechanisms to execute administrative tasks on a database, and it's intended to be used as a tool to prepare databases before your application begins to interact with them in your execution or testing environment, as it allows you to create, drop, and migrate databases, as well as import data from CSV or JSON files.

IDbAgent Interface

The IDbAgent interface defines the properties and methods that a database agent must implement.

• Host: Information about the host where the database is running.
• DefaultDatabase: The default database to connect to.
• GetConnection: Creates a connection to the database.
• DatabaseExistsAsync: Checks for the existence of a database.
• CreateDatabaseAsync: Creates a new database.
• DropDatabaseAsync: Drops an existing database.
• GetTableAsync: Retrieves information about a table.
• MigrateDatabaseAsync: Runs migration scripts on the database.
• ExecuteStatementAsync: Executes a SQL statement on the database.
• ExecuteStatementsAsync: Executes a list of SQL statements on the database.
• RunScriptAsync: Executes SQL scripts on the database.
• CanImportFile: Checks if a file can be imported.
• CanImportFileAsync: Asynchronously checks if a file can be imported.
• ImportDataAsync: Imports data from CSV or JSON files into the database.
• ImportCsvFileAsync: Imports data from a CSV file into the database.
• ImportJsonFileAsync: Imports data from a JSON file into the database.
• StatementPrepared: Event that is raised when a statement is prepared.
• StatementExecuted: Event that is raised when a statement is executed.
• MigrationStepExecuted: Event that is raised when a migration step is executed.
• RecordImporting: Event that is raised when a record is being imported.
• RecordImported: Event that is raised when a record has been imported.

DbAgent Class

The DbAgent class is an implementation of the IDbAgent interface that provides functionality to execute administrative tasks on a database. This class uses generic commands to support different database providers. If specific behavior is required for a particular provider, a new implementation of the IDbAgent interface should be created, maybe by inheriting from the DbAgent class.

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 DbAgentTest : IClassFixture<PostgresDatabaseFixture>
{
    private readonly DbAgent _dbAgent;
    private readonly DbCredentials _superUserCredentials;
    private readonly ITestOutputHelper _output;

    public DbAgentTest(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!);
        
        _dbAgent = new DbAgent(host, credentials, connectionStringBuilder.Database!);
        _dbAgent.StatementPrepared += (_, args) => output.WriteLine($"StatementPrepared{(args.ScriptPath is not null ? $" [{args.ScriptPath}]" : "")}: {Environment.NewLine}{args.Statement}");
        _dbAgent.StatementExecuted += (_, args) => output.WriteLine($"StatementExecuted{(args.ScriptPath is not null ? $" [{args.ScriptPath}]" : "")} [{args.RecordsAffected} record(s) affected]: {Environment.NewLine}{args.Statement}");
        _dbAgent.MigrationStepExecuted += (_, args) => output.WriteLine(args.Statement);
        _dbAgent.RecordImporting += (_, args) => output.WriteLine($"RecordImporting: {args}");
        _dbAgent.RecordImported += (_, args) => output.WriteLine($"RecordImported: {args}");

        _superUserCredentials = postgresDatabaseFixture.SuperUserCredentials;
        
        _output = output;
        
        _output.WriteLine($"DbAgent created: [{_dbAgent.Host.Provider}] {_dbAgent.Host.Address}:{_dbAgent.Host.Port}/{_dbAgent.DefaultDatabase}");;
    }
    
    [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_MigrateDatabase()
    {
        // Arrange
        var migrationOptions = new DbMigrationOptions(
            sourceDirectory: "Database/PgTest/Migrations",
            metadataSchema: "migration",
            metadataTable: "changelog",
            outOfOrder: true
        );
        
        // Act
        var exception = await Record.ExceptionAsync(() => _dbAgent.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(() => _dbAgent.ExecuteStatementsAsync(statements, CancellationToken.None));

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

    [Fact]
    [Priority(6)]
    public async Task Should_ImportData()
    {
        // Arrange
        // The fictitious "Database/PgTest/Fake/Data" directory contains CSV and JSON files with data to be imported.
        var options = new DbImportOptionsBuilder()
            .WithSourcePath("Database/PgTest/Fake/Data")
            .Build();

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

        var userCount = 0;
        var customerCount = 0;
        var employeeCount = 0;
        if (exception is null)
        {
            using var connection = _dbAgent.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);
    }
}