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.
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
Scirpts folder in the console application project with one sub folder called
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.
trueto indicate that the database must be dropped before executing the scripts; otherwise
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
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.
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.
Scripts/src folder, create a new folder named
001 will indicate the initial version and will have the following sub scripts.
- Create a file named
001-Create-auth-schema.sqlwith the below SQL statement.
- Create a second file named
002-create-role-table.sqlwith the below SQL statements.
The solution should now look similar to the below.
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
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:
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