Database Setup with DbUp + Postgresql + Dapper in ASP.Net Core
In this tutorial, we are going to explore how we can setup our database on startup when using Dapper for accessing database.
When using Dapper, one of the key learning I came to know is that we have to have database and tables already created in advance in order to read/write data.
So we have to write some kind of migration logic in our code before we access database.
Now we could do that our own, but what if there is any library which could this for you.
Or if you have your SQL scripts already written and you want to reuse them for your database creation.
DbUp can help us in achieving all of it.
Let’s understand with example.
Example
Create a ASP.Net core web API in Visual Studio Or VSCode.
as mention I am using Postgresql for database, so will add the required nuget package.
Install-Package Dapper
Install-Package Npgsql
and Create a database and Create Table manually using PgAdmin tool.
Database creation
Table creation
Let’s define the connection string in appsetting.json
Connection string
Repository
Interface for Repository
One sample example using Dapper
Controller
Sample controller
We can run our app and test if we are able to read/write data.
Using DbUp
This was good till now, but there was one problem, I had to create database and tables manually.
Now this is ok for development but for production scenario specially automated deployment scenarios this will not be OK.
DbUp can help us integrate sql script and setup database on startup.
First cleanup the database by dropping previously created database and tables.
Add the required nuget package
Install-Package dbup-postgresql
Let’s add those script to project.
Project structure with script folder
SQL Script for creating table
Embedding the script as a project resource
and Create a extension class for adding Migration code using DbUp.
public static class DatabaseExtension
{
public static IHost MigrateDatabase<TContext>(this IHost host)
{
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
var configuration = services.GetRequiredService<IConfiguration>();
var logger = services.GetRequiredService<ILogger<TContext>>();
logger.LogInformation("Migrating postresql database.");
string connection = configuration.GetValue<string>("DatabaseSettings:ConnectionString");
EnsureDatabase.For.PostgresqlDatabase(connection);
var upgrader = DeployChanges.To
.PostgresqlDatabase(connection)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
logger.LogError(result.Error, "An error occurred while migrating the postresql database");
return host;
}
logger.LogInformation("Migrated postresql database.");
}
return host;
}
}
Program.cs
Changes in Program.cs
Our changes are ready. Let’s run the app and see the output window
Output window
As we can in the output window logs, it executed the SQL scripts and created the required database and tables.
Now we can call the API and query for data.
You can find full source code at GitHub.
Summary
Using DbUp we can easily integrate our existing SQL Scripts and make the database ready before any operation.
It is supported for different databases.
https://dbup.readthedocs.io/en/latest/supported-databases/
Hope it is helpful.