Flowsy.Db.Agent 1.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 methods that a database agent must implement.

Implementations of this interface must be created from a DbConnectionOptions object from the Flowsy.Db.Abstractions package and provide behavior for the following methods:

• 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.
• 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.

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 connectionOptions = new DbConnectionOptions(DbProvider.PostgreSql, postgresDatabaseFixture.ConnectionString);
        
        _dbAgent = new DbAgent(connectionOptions);
        _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;
    }
    
    [Fact]
    [Priority(1)]
    public async Task Should_CreateAndDropDatabase()
    {
        // Arrange
        const string databaseName = "fake_db";
        
        // Act
        var databaseCreated = await _dbAgent.CreateDatabaseAsync(databaseName, true, _superUserCredentials);
        var databaseExists = await _dbAgent.DatabaseExistsAsync(databaseName, _superUserCredentials); 
        var databaseDropped = await _dbAgent.DropDatabaseAsync(databaseName, true, _superUserCredentials);

        // Assert
        Assert.True(databaseCreated);
        Assert.True(databaseExists);
        Assert.True(databaseDropped);
    }

    [Fact]
    [Priority(2)]
    public async Task Database_Should_Exist()
    {
        // Act
        var databaseExists = await _dbAgent.DatabaseExistsAsync(_superUserCredentials);
        
        // 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));
        
        // 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));

        // 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));
        
        // Assert
        Assert.Null(exception);
    }

    [Fact]
    [Priority(6)]
    public async Task Should_ImportData()
    {
        // Arrange
        var options = new DbImportOptionsBuilder()
            .WithSourcePath("Database/PgTest/Fake/Data")
            .WithTarget("security.user.csv", [
                new DbImportColumnTarget("user_id", typeof(Guid), isUnique: true),
                new DbImportColumnTarget("email", typeof(string), isUnique: true),
                new DbImportColumnTarget("password_hash", typeof(string)),
                new DbImportColumnTarget("forename", typeof(string)),
                new DbImportColumnTarget("surname", typeof(string)),
                new DbImportColumnTarget("gender", typeof(string), transformParameterPlaceholder: p => $"{p}::kernel.gender"),
                new DbImportColumnTarget("birthdate", typeof(DateTime)),
            ])
            .WithTarget("sales.customer.csv", [
                new DbImportColumnTarget("customer_id", typeof(Guid), isUnique: true),
                new DbImportColumnTarget("user_id", typeof(Guid?), transformParameterPlaceholder: p => $"{p}::uuid"),
                new DbImportColumnTarget("trade_name", typeof(string)),
                new DbImportColumnTarget("legal_name", typeof(string)),
                new DbImportColumnTarget("tax_id", typeof(string)),
            ], [["customer_id", "user_id"]])
            .WithTarget("personnel.employee.json", new Dictionary<string, DbImportColumnTarget>
            {
                { "employeeId", new DbImportColumnTarget("employee_id", typeof(Guid), isUnique: true) },
                { "userId", new DbImportColumnTarget("user_id", typeof(Guid), transformParameterPlaceholder: p => $"{p}::uuid") },
                { "forename", new DbImportColumnTarget("forename", typeof(string)) },
                { "surname", new DbImportColumnTarget("surname", typeof(string)) },
                { "gender", new DbImportColumnTarget("gender", typeof(string), transformParameterPlaceholder: p => $"{p}::kernel.gender") },
                { "birthdate", new DbImportColumnTarget("birthdate", typeof(DateTime)) },
            }, [["employee_id", "user_id"]])
            .Build();

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

        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);
    }
}