Home

DatabaseBuilder is .NET library to build a versioned database from SQL script files in a directory structure.

Prepare the following directory structure which contains SQL scripts to build a versioned database:

├───ChangeScripts
│       1.0.0.1.sql
│       1.0.0.2.sql
│
└───ReRunnableScripts
    ├───01-Views
    │       0010-SomeDto.sql
    │       0020-SomeOtherDto.sql
    │
    └───02-StoredProcedures
            0010-SomeStoredProc.sql
            0020-SomeOtherStoredProc.sql

Example directory structure can be found here.

The ChangeScripts directory contains SQL change scripts, each script representing a database version. These scripts usually modify the database structure - create, modify or delete tables, create indexes, etc. The first script should create Version table, here is SQL server example. The built database will have the version (table Version - the version table name is configurable) updated to the highest change script version. Only scripts with a higher version are applied when building an existing database.

The database version number has the following structure: Major.Minor.Revision.ScriptNumber

The ReRunnableScripts directory contains other SQL scripts which are executed every time when building a database. These scripts are meant to create database objects (e.g. database views, stored procedures, etc.). It's a good practice to conditionally drop the object first, and then create it, SQL server example.

Once you have your scripts directory and an empty database or an existing database ready, you can build the database. SQL server example:

    var builderOfDatabase = new BuilderOfDatabase(() => new SqlConnection(connectionString));
    builderOfDatabase.BuildDatabase("path to a directory with sql scripts");

Example of usage with all constructor parameters and different database connections:

    var builderOfDatabase = new BuilderOfDatabase(
        createConnectionFunc: () => new SqlConnection("connection string"), // sql server
        //createConnectionFunc: () => new SQLiteConnection("connection string"), // sqlite
        //createConnectionFunc: () => new NpgsqlConnection("connection string"), // postresql
        versionTableName: "Version", // optional parameter
        changeScriptsDirectoryName: "ChangeScripts", // optional parameter
        reRunnableScriptsDirectoryName: "ReRunnableScripts", // optional parameter
        sqlScriptFileExtension: ".sql", // optional parameter
        logAction: loggedMessage => { /* custom logging code */ } // optional parameter
        );
    builderOfDatabase.BuildDatabase("path to a directory with sql scripts");