Postgresql is a free open-source relationship database management system and is well-known in the development community for its reliability, robustness and performance.

A number of developer tools exist to create and manage a postgresql database. DbUp is an example of such a tool. It is a .net library providing the ability to deploy changes to a SQL database. This library also tracks which SQL scripts have already been run and only executes the scripts that have not been run.

This article will focus on creating a postgresql database with versioning using DbUp.

Getting started

Using an IDE for .net projects, create a new .net core c# console application named Demo.Database. Install the dbup-postgresql nuget package.

dotnet add package dbup-postgresql

Create a Scirpts folder in the console application project with one sub folder called src.

Replace the Program.cs file with the below code base.

DbUp provides a simple fluent interface to set up the console application. The DbUp nuget package is also well documented making it very easy to follow.

At the time of writing this article, DbUp does not support dropping of a postgresql database. To drop a postgresql database, create an extension method using the below code base.

When inspecting the Program.cs class above, the program requires two arguments.

• connectionString – the postgresql connection string.
• dropDatabase – true to indicate that the database must be dropped before executing the scripts; otherwise false.

When working in a Windows development environment, it will be easier to run a bat file to run scripts against the database. Create the following Publish.bat file.

Note that dotnet core 3.1 was used. If a different version of donet core is used, then the bat file will need to be modified accordingly.

When executing the Publish.bat file, the DemoDb database will be dropped and re-created.

The Demo.Database project should be similar to the below.

Now that all the boilerplate code is completed, build the solution. This concludes all the c# .net programming.

Creating a table

For this demo, a new schema will be created named auth, having one table named Role. This table will store a list of unique roles.

Since DbUp keeps track of all SQL scripts that have run, it is good practice to have a good naming convention for script files.

In the Scripts/src folder, create a new folder named 001. 001 will indicate the initial version and will have the following sub scripts.

• Create a file named 001-Create-auth-schema.sql with the below SQL statement.

• Create a second file named 002-create-role-table.sql with the below SQL statements.

The solution should now look similar to the below.

Run the Publish.bat file. The output in the bat file should be similar to the below.

Using an IDE for postgres, open the database. The below is a screenshot of the DemoDb postgres database using DataGrip as the IDE.

DbUp automatically created the public.SchemaVersions table and managed the versioning. Below are the records contained in the public.SchemaVersions table.

Summary

The DbUp implementation used in this article made it very easy to create a new postgresql database. Also, the DbUp package handled versioning of SQL scripts by creating a SQL table to store a list of executed scripts. Using the bat file provides enough flexibility to re-create the database whenever it is needed.

DbUp provides simple and neat documentation, thus making it very easy to use.

The source code used in this article can be found here.

Further information on DbUp can be found at the following link:

• DbUp

Software versions used in this article

• Jetbrains Rider v2020.2.4
• dotnet core v3.1
• DbUp v4.4.0
• DataGrip v2020.2.3
• Postgresql v12.4