Flowsy.Db.Agent 4.0.0

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.

• Provider: Information about the database provider.
• 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.
• SetupDatabaseAsync(): Sets up a database by executing a series of operations.
• 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.
• SupportsImportMethod: Checks if a specific import method is supported.
• 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.
• InsertRecordsFromCsvFileAsync: Imports data by inserting records from a CSV file into the database.
• CopyRecordsFromCsvFileAsync: Imports data by copying records from a CSV file into the database.
• InsertRecordsFromJsonFileAsync: Imports data by inserting records from a JSON file into the database.
• CopyRecordsFromJsonFileAsync: Imports data by copying records from a JSON file into the database.
• SetupOperationPrepared: Event that is raised when a setup operation is prepared.
• SetupOperationExecuted: Event that is raised when a setup operation is executed.
• 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 the DbAgent Class

The following code snippet shows the features provided by the DbAgent class being used in a testing environment, but you can also use it in your final execution environment.

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

    public DbAgentTest(PostgresDatabaseFixture postgresDatabaseFixture, ITestOutputHelper output)
    {
        var provider = DbProvider.GetInstance(DbProviderFamily.PostgreSql);
        
        _dbAgent = new DbAgent(provider, postgresDatabaseFixture.ConnectionString);
        _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}");
        
        _dbAgent.SetupOperationPrepared += (_, args) =>
        {
            var databaseUrl = $"{args.Host.Address}:{args.Host.Port}/{args.Database}";
            if (args.Operation.Risk == DbOperationRisk.High)
                args.Cancel = true;
            
            output.WriteLine("[ {0} ] [ operation {1} ] [ {2} : {3} ] [ Risk : {4} ] {5}", databaseUrl, args.Cancel ? "canceled" : "prepared", args.Operation.Key, args.Operation.Type, args.Operation.Risk, args.Operation.Description);
        };
        
        _dbAgent.SetupOperationExecuted += (_, args) =>
        { 
            var databaseUrl = $"{args.Host.Address}:{args.Host.Port}/{args.Database}";
            output.WriteLine("[ {0} ] [ operation executed ] [ {1} : {2} ] [ Risk : {3} ] {4}", databaseUrl, args.Operation.Key, args.Operation.Type, args.Operation.Risk, args.Operation.Description);
        };
        
        _output = output;
        
        _output.WriteLine($"DbAgent created: [{_dbAgent.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_Setup_Database()
    {
        // Arrange
        var setupConfiguration = Configuration.Instance.GetSection("Databases:DatabaseOne: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.