Connecting to Dockerized databases from Asp.Net Core
In this article, we will see how we connect to a dockerized instance of PostgreSQL, Redis and MongoDB from an Asp.Net Core project. We will be using the docker-compose file to pass on the connection string and other configs to the code which will be overriding the values mentioned in the appsettings.json file.
We will be using the Web API project template in Visual Studio. To make use of the docker-compose file we will be adding Container Orchestration Support to the project. This adds a Dockerfile and docker-compose.dcproj project to the solution.
The Dockerfile is standard and contains the bare minimum code to run a rest API-based project and no changes are required there.
This will contain the keys with which we can get the connection strings for the three resources we are planning to connect. Since we will be overriding them from the docker-compose file we can just define them without any values.
"CacheSettings": { "RedisCache": "" }, "DatabaseSettings": { "PostgressConnectionString": "", "MongoConnectionString": ""
}
The next step would be to read these connection parameters in the startup.cs file so that the clients can be configured for injection.
var mongoConnectionString = Configuration.GetValue<string>("DatabaseSettings:MongoConnectionString"); var postgressConnectionString = Configuration.GetValue<string>("DatabaseSettings:PostgressConnectionString"); var redisConnectionString = Configuration.GetValue<string>("CacheSettings:RedisCache");
The default file generated on adding the orchestration support contains the service reference for just the Dockerfile of the API project as below.
version: '3.4' services: stackup_docker_db_demo: image: ${DOCKER_REGISTRY-}stackupdockerdbdemo build: context: . dockerfile: stackup_docker_db_demo/Dockerfile
Let us now add the service references for PostgreSQL, Redis-alpine and MongoDB so that those containers are also running once we build and run the project
stackupMongo: image: mongo container_name: stackupMongo restart: always ports: - "27017:27017" stackupRedis: image: redis:alpine container_name: stackupRedis restart: always ports: - "6379:6379" stackupPostgres: image: postgres container_name: stackupPostgres restart: always ports: - "5432:5432" environment: - POSTGRES_USER=admin - POSTGRES_PASSWORD=admin - POSTGRES_DB=stackup_postgress volumes: - stackup_postgres_data:/var/lib/postgresql/data/ pgadmin: container_name: pgadmin4_container image: dpage/pgadmin4:6.17 restart: always environment: PGADMIN_DEFAULT_EMAIL: admin@admin.com PGADMIN_DEFAULT_PASSWORD: password PGADMIN_LISTEN_PORT: 80 ports: - "8009:80" volumes: - pgadmin-data:/var/lib/pgadmin volumes: stackup_postgres_data: pgadmin-data:
I have also added a service for pgAdmin so that we get a UI for our PostgreSQL DB through which we can connect and create the database and tables for our demo manually.
We now need to update the service of the WebAPI project with the development environment and environment variables with the same keys as those used in appsettings.json earlier so that they can be overridden at run time.
services: stackup_docker_db_demo: image: ${DOCKER_REGISTRY-}stackupdockerdbdemo build: context: . dockerfile: stackup_docker_db_demo/Dockerfile environment: - ASPNETCORE_ENVIRONMENT=Development - "DatabaseSettings __MongoConnectionString=mongodb://stackupMongo:27017" - "DatabaseSettings__ PostgressConnectionString=host=stackupPostgres;Port=5432;Database=Stackup;User Id=admin;Password=admin;" - "CacheSettings__RedisCache=stackupRedis:6379"
For nested properties, we have to use double underscore "__" so that compiler can be parsed and mapped to the correct key.
Note: We need to use the service name provided for the resources as the server name in the connection strings like stackupMongo , stackupPostgres and stackupRedis in the example above.
We now have a skeleton app ready and we can run it to see our variables in the startup file with the connecting string populated.
The main requirement is now complete we can read the values defined in the docker-compose.yml file via the appsettings.json into our C# code. Let us now right some quick code to make sure that we can perform all the CRUD operations on these dockerized resources.
Setting up database and table in PostgreSQL
PgAdmin is running on localhost:8009 as defined in our configuration. We will log in to it via the credentials admin@admin.com & password and then create a database name Stackup and a table named blogpost.
The hostname address would be the same as the service name and username/password both are admin
CREATE TABLE BlogPost ( id VARCHAR (100) PRIMARY KEY, BlogName VARCHAR ( 500 ) NOT NULL, BlogTitle VARCHAR ( 500 ) NOT NULL, BlogDescription VARCHAR ( 1000 ) NOT NULL, Tags VARCHAR ( 500 )
);
Run the above script once the database is created to have the table ready in which we will be writing the data from our Asp.Net Core WebAPI project.
Firstly, install the following Nuget packages which we will be needing for our implementation
<PackageReference Include="Dapper" Version="2.0.123" />
<PackageReference Include="Microsoft.Extensions.Caching.StackExchangeRedis" Version="7.0.2" />
<PackageReference Include="MongoDB.Driver" Version="2.19.0" />
<PackageReference Include="Newtonsoft.Json" Version="13.0.2" />
<PackageReference Include="Npgsql" Version="7.0.1" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
We will have two models, one two create a simple blog post and the other to generate a response of blogpost fetched from all three of our storages.
using MongoDB.Driver;
using System.Collections.Generic; namespace stackup_docker_db_demo.Model
{ public class BlogPost { public string Id { get; set; } public string BlogName { get; set; } public string BlogTitle { get; set; } public string BlogDescription { get; set;} public string Tags { get; set; } } public class BlogPostResponse { public IEnumerable<BlogPost> MongoBlogPost { get; set; } public BlogPost RedisBlogPost { get; set; } public List<BlogPost> PostgressBlogPost { get; set;}
}
}
We will need three providers one each for PostgreSQL, Redis and MongoDB. For this demo, I am directly creating classes that interact with the underlying database but ideally, we should follow some pattern.
MongoDB Provider
using Microsoft.Extensions.Configuration;
using MongoDB.Driver;
using stackup_docker_db_demo.Model;
using System.Collections;
using System.Collections.Generic;
using System.Threading.Tasks; namespace stackup_docker_db_demo.DBProviders
{ public class MongoProvider { public IMongoClient _mongoClient; public IMongoDatabase _mongoDatabase; public IMongoCollection<BlogPost> BlogPosts { get; } public MongoProvider(IConfiguration config, IMongoClient _client) { _mongoClient = _client; _mongoDatabase = _mongoClient.GetDatabase("stackupMongodb"); BlogPosts = _mongoDatabase.GetCollection<BlogPost>("BlogPosts"); } public async Task CreatePost(BlogPost post) { await BlogPosts.InsertOneAsync(post); } public async Task<IEnumerable<BlogPost>> GetPost() { return await BlogPosts.Find(x => true).ToListAsync();
}
}
}
Redis Provider
using Microsoft.Extensions.Caching.Distributed;
using MongoDB.Bson.IO;
using System.Threading.Tasks;
using System;
using stackup_docker_db_demo.Model;
using Newtonsoft.Json;
using JsonConvert = Newtonsoft.Json.JsonConvert; namespace stackup_docker_db_demo.DBProviders
{ public class RedisProvider { private readonly IDistributedCache _redisCache; public RedisProvider(IDistributedCache cache) { _redisCache = cache; } public async Task<BlogPost> GetKeyFromRedis() { string postJson = await _redisCache.GetStringAsync("default"); if (string.IsNullOrEmpty(postJson)) return null; var post = Newtonsoft.Json.JsonConvert.DeserializeObject<BlogPost>(postJson); return post; } public async Task<BlogPost> UpdateKeyValue(BlogPost post) { string postJson = JsonConvert.SerializeObject(post); await _redisCache.SetStringAsync("default", postJson); return await GetKeyFromRedis();
}
}
}
PostgreSQL Provider
using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;
using stackup_docker_db_demo.Model;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks; namespace stackup_docker_db_demo.DBProviders
{ public class PostgressProvider { IConfiguration _configuration; string _connectionString; public PostgressProvider(IConfiguration config) { _configuration = config; _connectionString = _configuration.GetValue<string>("DatabaseSettings:PostgressConnectionString"); } public async Task<bool> CreateBlogPost(BlogPost post) { using var _connection = new NpgsqlConnection(_connectionString); string query = ""; ; var fullPath = $"stackup_docker_db_demo.SqlQueries.CreateBlogPost.sql"; var assembly = Assembly.GetExecutingAssembly(); using (Stream stream = assembly.GetManifestResourceStream(fullPath)) using (StreamReader reader = new StreamReader(stream)) { query = reader.ReadToEnd(); } var inserted = await _connection.ExecuteAsync(query, new { Id = post.Id, BlogName = post.BlogName, BlogDescription = post.BlogDescription, BlogTitle = post.BlogTitle, Tags = post.Tags }); return inserted == 0 ? false : true; } public async Task<List<BlogPost>> GetBlogPost() { using var _connection = new NpgsqlConnection(_connectionString); string query = "select * from blogpost"; var blog = await _connection.QueryAsync<BlogPost>(query); return blog == null ? null : blog.ToList();
}
}
}
I have directly used the dapper framework here to connect to the database and the script file CreateBlogPost.sql lies inside a folder named SqlQueries in the main project directory.
INSERT INTO BlogPost (Id, BlogName, BlogTitle, BlogDescription, Tags) Values (@Id, @BlogName, @BlogTitle, @BlogDescription, @Tags);
The startup file also needs to be updated for the dependency injection and configuration of the Mongo and Redis clients which are accessed by their respective providers.
services.AddControllers(); var mongoConnectionString = Configuration.GetValue<string>("DatabaseSettings:MongoConnectionString"); var postgressConnectionString = Configuration.GetValue<string>("DatabaseSettings:PostgressConnectionString"); var redisConnectionString = Configuration.GetValue<string>("CacheSettings:RedisCache"); services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Version = "v1" }); }); services.AddSingleton<IMongoClient>(x => { return new MongoClient(mongoConnectionString);
});
services.AddStackExchangeRedisCache(options =>
{
options.Configuration = redisConnectionString;
});
services.AddSingleton<RedisProvider>();
services.AddSingleton<MongoProvider>();
services.AddSingleton<PostgressProvider>();
We have a simple BlogPostController with two endpoints, the first creates a blogpost into each of our three dockerized resources. Second is a GET endpoint that returns a response object fetching the created blog post from all three dockerized resources at once.
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using stackup_docker_db_demo.DBProviders;
using stackup_docker_db_demo.Model;
using System;
using System.Runtime.InteropServices;
using System.Threading.Tasks; namespace stackup_docker_db_demo.Controllers
{ [] [] public class BlogPostController : ControllerBase { RedisProvider _redis; PostgressProvider _postgress; MongoProvider _mongo; public BlogPostController(RedisProvider redis, PostgressProvider postgress, MongoProvider mongo) { _redis = redis; _postgress = postgress; _mongo = mongo; } [] public async Task<IActionResult> CreateDiscountCoupon(BlogPost post) { post.Id = Guid.NewGuid().ToString(); post.BlogTitle = "Connecting to Dockerized Redis from Asp.net Core"; await _redis.UpdateKeyValue(post); post.BlogTitle = "Connecting to Dockerized postgres from Asp.net Core"; await _postgress.CreateBlogPost(post); post.BlogTitle = "Connecting to Dockerized mongodb from Asp.net Core"; await _mongo.CreatePost(post); return Ok(); } [] public async Task<IActionResult> GetBlogPost() { var response = new BlogPostResponse(); response.PostgressBlogPost = await _postgress.GetBlogPost(); response.MongoBlogPost = await _mongo.GetPost(); response.RedisBlogPost = await _redis.GetKeyFromRedis(); return Ok(response);
}
}
}
That's it, our code is ready to add and fetch data from our databases and cache services. Let us run our project and first create a new blog post via the POST endpoint.
{ "blogName": "StackUp", "blogTitle": "Connecting to Dockerized databases from Asp.net Core", "blogDescription": "Tutorial on how to connect to Dockerized Postgress, MongoDB and Redis from an asp.net core application", "tags": "Programming, Asp.Net Core, Docker"
}
Once created, we can use the GET endpoint which summarizes the blog post into a new object and returns a response like below
We have gone through the steps to connect to databases and cache services running in docker containers from an Asp.NET core project.
Using something similar we can access and work with various docker images available for integration. Thank you for spending your time and reading this post and I hope this helped you in any way.
The source code for the demo application described here is available at GitHub Link.
