Hire the author: Adeyemi A

SOURCE CODE TO THIS PROJECT IS FOUND HERE

Why do we need to store our application data into more than one database?

The following are a few of the reasons why we might decide to distribute our web API data into multiple databases:

1. Polyglot persistence: This concept stresses that during the architectural design of a software application, numerous databases are available to solve different problems. Using a single database to satisfy all these problems can result in an inefficient system in terms of accuracy, efficiency and speed.

2. Redundancy: It ensures that there is provision of duplicates or alternatives of critical components of an application to act as a backup in cases such as system failure. In such case, we can distribute our web API data in two or more databases, so some part of our application can function even when one of our databases is down.

SKILLS TO BE ACQUIRED

  • How To Scaffold a dotnet web API project.
  • Setup Entity Framework for your dotnet core application.
  • Code First Approach in Entity Framework.
  • Repository pattern in dotnet web API.

DEVELOPMENT RESOURCES

What are our development environments?
I’ll be using visual studio code for the development of this sample project.
Download and Install the following on your machine: visual studio code.

.net core 3.0 SDK
Vscode C# Extension
c# Vscode extension

When you install the dotnet 3.0 SDK, it automatically installs the dotnet CLI so you can run dotnet commands on your terminal.

PROJECT SCOPE
We’ll be designing a dummy library management system that exposes functionality to add a book and user. We’ll also get the lists of books and users added into the system.


For this application, we’ll create two tables as follows:

  1. User Table: The user table will contain the details of our user, i.e. the FirstName and LastName. I limited the scope of the user’s table to this for demonstration purposes only. For production application, dotnet core includes a membership system called ASP.NET core Identity which can be used to manage the user details.
    The details of the users will be stored in a PostgreSQL database.
  2. Book Table: The book table will contain the details of a dummy book and will include the ISBN of the book and the Author’s name. The details of the book will be stored in a MSSQL database.

SCAFFOLD THE PROJECT

cd project_directory
dotnet new webapi -o webApiMultipleDb

From the above we:
1. navigated to our project directory
2. scaffolded a new web api project in dotnet core

Setup Entity Framework
Entity Framework: The entity framework is an ORM (Object Relational Mapper) created by Microsoft.

In simple terms this means it abstracts away the need to run manual SQL queries on entities of a database, by providing an API (based on object oriented programming) to interact with the entities of the database.

To install entity framework in our application run the following on your terminal:

dotnet add package Microsoft.EntityFrameworkCore --version 3.0.0

Add MSSQL server provider instance:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 3.0.0

Add PostgreSQL server provider instance:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 3.0.1

The postgreSQL and MSSQL server provider instance were added to enhance us to instantiate communication with postgreSQL and MSSQL database.

Starting from dotnet core 3.0 the command for dotnet ef, which enables us to run our migrations and update the database in the previous version of dotnet core, is not included into the dotnet core SDK we installed earlier. So we have to run the following script to install it.

dotnet tool install --global dotnet-ef --version 3.0.0

DATABASE MODELS

The model basically helps in conceptualizing how the data will be represented, the relationship between such data and the rules guiding the data in the database.

ADD THE USER MODEL

public class User {
 public int Id { get; set; }
 public string FirstName {get; set;}
 public string LastName {get; set; } 
}

ADD THE BOOK MODEL

public class Book { 
 public int Id { get; set; } 
 public int ISBN { get; set; } 
 public string Author {get; set; } 
 }

CREATING DBContext
A DbContext instance represents a session with the database and can be used to query and save instances of your entities (Microsoft Docs).

Since we want to interact with two different databases we’ll require two DbContexts i.e. the UserDbContext and BookDbContext.

If our application is interacting with a single database, it’s preferable to have a single DbContext i.e. the AppDbContext which represents a session with the database.

Create a directory called Data, navigate to the directory and create BookDbContext.cs and add the following:


using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using webApiMultipleDb.Models;

namespace webApiMultipleDb.Data{
  public class BookDbContext : DbContext { 
  public BookDbContext(DbContextOptions<BookDbContext> options)
  : base(options) {}
  public DbSet<Book> Book { get; set; }
  }
}

From the above, what we did is to create a BookDbContext class and ensure it inherits from the base class DbContext which enables our BookDbContext to communicate with our database.


Add the UserDbContext.cs and add the following:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using webApiMultipleDb.Models;
namespace webApiMultipleDb.Data{
    public class UserDbContext : DbContext {
    private readonly IConfiguration _config;
    public UserDbContext(IConfiguration config) { 
       _config = config; 
    }
    public DbSet<User> User { get; set; } 
    protected override void OnConfiguring(DbContextOptionsBuilder options) 
    => options.UseNpgsql(_config["ConnectionStrings:UserDBConnection"]); 
   }
}

Things to note from the UserDbContext is that it contains a configuration to connect it to a PostgreSQL database via the OnConfiguring method which is an overload of its base implementation.

It also receive the IConfiguration via Dependency Injection in its constructor for setting the connection string which we’ll store in our appsettings.json.

appsettings.json contains all our secret keys and connection string so we open it and append the following json:

 "ConnectionStrings": {
   "UserDBConnection": "postgreSQL connection string",
   "DefaultConnection": "mssql connection string"
 }

REPOSITORY PATTERN

The Repository pattern is responsible for creating an abstraction between the data access layer and the business logic of the application.

The repository pattern ensures we have a loosely coupled application. i.e. a change in the data access logic, such as change in the implementation of our ORM API, will not cause change in the implementation of the business logic, e.g controller logic implementation of our application.

IMPLEMENTING REPOSITORY PATTERN
Create a directory named Repository and add the interface IUserRepository.cs.


using System.Collections.Generic;
using System.Threading.Tasks;
using webApiMultipleDb.Models;
namespace webApiMultipleDb.Repository {

public interface IUserRepository{
  Task<IEnumerable<User>> GetUsers();
  Task AddUser(User user);
  }
}

From the interface above we declared that we want to expose a method GetUsers and AddUser for getting the list of all users and Adding a single user.

Also Add a new file in the same directory called IBookRepository.cs and add the following;


using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using webApiMultipleDb.Models;
namespace webApiMultipleDb.Repository{

  public interface IBookRepository {
    Task<IEnumerable<Book>> GetBooks(); 
    Task AddBook(Book book); 
   }
}

Create a new file named UserRepository.cs that will implement the IUserRepository Interface in the same directory.


using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using webApiMultipleDb.Models;
using webApiMultipleDb.Data;

namespace webApiMultipleDb.Repository
{
 public class UserRepository : IUserRepository {
   private readonly UserDbContext _context;
   public UserRepository(UserDbContext context)
   {
     _context = context;
   }
   public async Task<IEnumerable<User>> GetUsers()
   {
     return await _context.User.ToListAsync();
   } 
   public async Task AddUser(User user) {
     await _context.User.AddAsync(user);
     await _context.SaveChangesAsync();
       }
   }
}

From the above, the function to GetUsers and AddUser defined in the IUserRepository was implemented by the UserRepository  class.

Create a new file named BookRepository.cs that will implement the IBookRepository interface in the same directory. 


using System;
using System.Text;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using webApiMultipleDb.Data;
using webApiMultipleDb.Models;

namespace webApiMultipleDb.Repository{
 public class BookRepository : IBookRepository {
   private readonly BookDbContext _context; 
   public BookRepository (BookDbContext context) {
     _context = context;
   } 
   public async Task<IEnumerable<Book>> GetBooks() {
   var books = await _context.Book.ToListAsync();
   return books; 
   }
   public async Task AddBook(Book book) {
   await _context.Book.AddAsync(book);
   await _context.SaveChangesAsync();
      }
   }
}

After adding all the files specified above your project directory should look like this below:

Then we’ll proceed to register the all interfaces and the classes that implement such interfaces in the startup.cs file which is in the root directory. We do this by replacing the ConfigureServices method with the following.


public void ConfigureServices(IServiceCollection services) 
 {  
 services.AddControllers();
 string UserconnectionString = 
 Configuration.GetConnectionString("UserDBConnection");
 string BooksconnectionString =  
 Configuration.GetConnectionString("DefaultConnection");     
 services.AddDbContext<UserDbContext>option =>
 option.UseSqlServer(UserconnectionString);
 services.AddDbContext<BookDbContext>(option => 
 option.UseSqlServer(BooksconnectionString));
 services.AddScoped<IUserRepository, UserRepository>();
 services.AddScoped<IBookRepository, BookRepository>();       
}

At this point we have successfully connected our application to two different database, so for testing sake I’ll create two simple controller for user and book.

Create a UserController.cs file and paste the following code into it.


using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using webApiMultipleDb.Models;
using webApiMultipleDb.Repository;

namespace webApiMultipleDb.Controllers{
  [Route("api/[controller]")]
  public class UserController : ControllerBase
   {
     private readonly IUserRepository _userRepository;
     public UserController(IUserRepository userRepository) {
     _userRepository = userRepository;
     }
     [HttpGet]
     public async Task<ActionResult<IEnumerable<User>>> GetAllUsers() { 
     var users = await _userRepository.GetUsers();
     return Ok(users);
     }
     [HttpPost]
     public async Task<ActionResult<User>> Post([FromBody]User user) { 
       await _userRepository.AddUser(user);
       return Ok();
     }
   }
}


From the above implementation, the IUserRepositor interface was passed as a parameter into the constructor of the UserController class, via dependency injection.

This made the methods implemented in our UserRepository class available in our UserController class. The UserController class implements two asynchronous methods GetAllUsers for returning all dummy users and  Post for adding users from the request body.

Create a BookController.cs in the controller directory and paste the following code to into it.


using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using webApiMultipleDb.Models;
using webApiMultipleDb.Repository;

namespace webApiMultipleDb.Controllers {
[Route("api/[controller]")]
public class BookController : ControllerBase
{
  private readonly IBookRepository _bookRepository;
  public BookController(IBookRepository bookRepository){ 
  _bookRepository = bookRepository;
  }
  [HttpGet]
  public async Task<ActionResult<IEnumerable<Book>>> GetAllBooks()  {
  var books = await _bookRepository.GetBooks();
  return Ok(books);
  }
  [HttpPost]
  public async Task<ActionResult<Book>> Post([FromBody]Book book)    {
  await _bookRepository.AddBook(book);
  return Ok(); 
     }
  }
}

The IBookRepository’s implementation was passed into the BookController constructor via dependency injection. The BookController contains two asynchronous method i.e. GetAllBooks responsible for getting all our books and Post to add books into the database.

Download PostMan for testing our endpoints
Download PostgreSQL with option to install PGAdmin
Download MSSQL SQL server and MSSQL Server management Studio

Migrations
The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application’s data model while preserving existing data in the database.

Run the following in your terminal one after the other to migrate your database schema.

dotnet ef migrations add InitialCreate --context BookDbContext
dotnet ef migrations add InitialCreate --context UsersDbContext

Running the above commands creates a migration folder in your root folder. To update your established database with the generated files run the following command on your terminal one after the other.

dotnet ef database update --context BookDbContext
dotnet ef database update --context UserDbContext

The above creates the database if it does not exist, update the database with the necessary entities, and give ability to update the database schema if it exist before.

TESTING OF OUR PROJECT
To test our project we’ll run the build script dotnet run build this starts the application.

Add a new user

Get the user data we added
Visualize the stored user data.
Add a book
Get the added book
Visualize the book added in MSSQL Management Studio.


SOURCE CODE TO THIS PROJECT IS FOUND HERE
Citations:
cover image

Learning Strategy
Prior to this project, I was only familiar with the concept of using a single database for persisting web API. I found out about this project and started my research.

During the course of my research I came across this Microsoft documentation on multiple providers which helped me understand how to work with multiple providers.

After making my research, I came up with a flow chart which helped me to conceptualize the flow of my solution. Then I made sure I implemented my solution based on this.

Reflective Analysis
I am comfortable with dotnet core web API v 2.2. As of the time of writing this article, Microsoft updated the dotnet core to v 3.0, which I had to level up on to implement this project within the shortest possible time.

I learnt a lot and recently found out that a lot of things had changed in the new update, such as the structure of the startup.cs file, removal of the functionality to run dotnet ef commands in the dotnet SDK 3.0 and many more.

I realized that having growth mindset is essential to meeting expectations, and the ability to resolve blockers and level up fast on any technology and update is key to being a great contributor in any team. I was satisfied and confident I have this ability. Which was why I was able to implement the whole solution within 1 hour of research.

Conclusion:
We were able to store dummy user data and books into 2 different database in real time. The project can be extended to accommodate storage of data into more than two different database depending on the needs by adding more DbContext instances.

Hire the author: Adeyemi A