DbEx

<br/>

<br/>

Introduction

DbEx provides database extensions for DbUp-inspired database migrations.

<br/>

Status

The build status is with the NuGet package status as follows, including links to the underlying source code and documentation:

Package | Status | Source & documentation -|-|- DbEx | | Link DbEx.MySql | | Link DbEx.Postgres | | Link DbEx.SqlServer | | Link

The included change log details all key changes per published version.

<br/>

DbUp-inspired

DbUp is a .NET library that is used to deploy changes to relational databases (supports multiple database technologies). It tracks which SQL scripts have been run already, and runs the change scripts in the order specified that are needed to get a database up to date.

Traditionally, a Data-tier Application (DAC) is used to provide a logical means to define all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC. This is largely specific to Microsoft SQL Server. Alternatively, there are other tools such as redgate that may be used. DbUp provides a more explicit approach, one that Microsoft similarily adopts with the likes of EF Migrations.

DbEx provides additional functionality to improve the end-to-end experience of managing database migrations/updates leveraging the concepts of DbUp. DbEx prior to version 1.0.14 exclusively leveraged DbUb; however, due to the slow uptake of some key pull requests by the maintainers of DbUp which was starting to impose limitations on DbEx. The decision was made to emulate some functionality internally to achieve the functionality goals of DbEx. The changes are compatible with the underlying journaling that DbUp leverages (i.e. simulates the same).

<br/>

Getting started

The easiest way to get started is to clone the repository and execute DbEx.Test.Console project, this will create a database with data.

DbEx.Test.Console git:(main)> export cs="Data Source=localhost, 1433;Initial Catalog=DbEx.Console;User id=sa;Password=Xxxxxx@123;TrustServerCertificate=true"
DbEx.Test.Console git:(main)> dotnet run -- -cv cs all

Next, create your own console app, follow the structure of DbEx.Test.Console project, add reference to https://www.nuget.org/packages/DbEx, then add in your SQL scripts.

Currently, the easiest way of generating scripts from an existing database, is to use the Generate Scripts feature of SQL Server Management Studio and copy its output.

<br/>

Commands (functions)

The DbEx DatabaseMigrationBase provides the base database provider agnostic capability, with the likes of the SqlServerMigrator providing the specific Microsoft SQL Server implementation. This automates the functionality as specified by the MigrationCommand. One or more commands can be specified, and they will be executed in the order listed.

Command | Description -|- Drop | Drop the existing database (where it already exists). Create | Create the database (where it does not already exist). Migrate | Being the upgrading of a database overtime using order-based migration scripts; the tool is consistent with the philosophy of DbUp to enable. CodeGen | Provides opportunity to integrate a code-generation step where applicable (none by default). Schema | There are a number of database schema objects that can be managed outside of the above migrations, that are dropped and (re-)applied to the database using their native Create statement. Reset | Resets the database by deleting all existing data (exclusions can be configured). Data | There is data, for example Reference Data that needs to be applied to a database. This provides a simpler configuration than specifying the required SQL statements directly (which is also supported). This is also useful for setting up Master and Transaction data for the likes of testing scenarios.

Additional commands available are:

Command | Description -|- All | Performs all the primary commands as follows; Create, Migrate, CodeGen, Schema and Data. Database | Performs Create, Migrate, CodeGen and Data. Deploy | Performs Migrate and Schema. DeployWithData | Performs Deploy and Data. DropAndAll | Performs Drop and All. DropAndDatabase | Performs Drop and Database. ResetAndAll | Performs Reset and All (designed primarily for testing). ResetAndData | Performs Reset and Data (designed primarily for testing). ResetAndDatabase | Performs Reset and Database (designed primarily for testing). Execute | Executes the SQL statement(s) passed as additional arguments. Script | Creates a new migration script file using the defined naming convention.

<br/>

Migrate

As stated, the DbUp approach is used enabling a database to be dropped, created and migrated. The migration is managed by tracking order-based migration scripts. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get the database up to date.

Over time there will be more than one script updating a single object, for example a Table. In this case the first script operation will be a Create, followed by subsequent Alter operations. The scripts should be considered immutable, in that they cannot be changed once they have been applied; ongoing changes will need additional scripts.

The migration scripts must be marked as embedded resources, and reside under the Migrations folder within the c# project. A naming convention should be used to ensure they are to be executed in the correct order; it is recommended that the name be prefixed by the date and time, followed by a brief description of the purpose. For example: 20181218-081540-create-demo-person-table.sql

A migration script can contain basic moustache value replacement syntax such as {{Company}}, this will then be replaced at runtime by the corresponding Company parameter value; see MigrationArgs.Parameters. These parameters (Name=Value pairs) can also be command-line specified.

It is recommended that each script be enclosed by a transaction that can be rolled back in the case of error; otherwise, a script could be partially applied and will then need manual intervention to resolve.

Note: There are special case scripts that will be executed pre- and post- migration deployments. In that any scripts ending with .pre.deploy.sql will always be executed before the migrations are attempted, and any scripts ending with .post.deploy.sql will always be executed after all the migrations have successfully executed. Finally, any scripts ending with .post.database.create.sql will only be executed when (after) the database is created.

<br/>

Schema

There are some key schema objects that can be dropped and created overtime without causing side-effects. Equally, these objects can be code-generated reducing the effort to create and maintain over time. As such, these objects fall outside of the Migrations above.

The currently supported objects are (order specified implies order in which they are applied, and reverse when dropped to allow for dependencies):

1. Type
2. Function
3. View
4. Procedure

The schema scripts must be marked as embedded resources, and reside under the Schema folder within the c# project. Each script should only contain a single Create statement. Each script will be parsed to determine type so that the appropriate order can be applied.

A schema script script can contain basic moustache value replacement syntax such as {{Company}}, this will then be replaced at runtime by the corresponding Company parameter value; see MigrationArgs.Parameters. These parameters (Name=Value pairs) can also be command-line specified.

The Schema folder is used to encourage the usage of database schemas. Therefore, directly under should be the schema name, for example dbo or Ref. Then sub-folders for the object types as per [Azure Data Studio](https://docs.mic