# How to generate a database per tenant in multi-tenant system ?

The purpose is to be able to create a multi-tenant database system where each tenant have its own database. We will see the different steps to implement this configuration

Let’s go, begin with the **STARTER KIT** !

## The database schema in code first

First of all, we will create the database context. No matter what we put inside, it is just to describe the project.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760223754588/3beca959-c834-4006-b24e-ae5777fcdff5.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760223779702/e21ffa9b-dba3-47f0-97d9-75a96d965940.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760223794165/ba997b12-bcac-4a24-a00c-b1776a77931f.png align="center")

Import **Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Tools** in version **<mark>9.0.9</mark>**

### The **Client** class

```csharp
namespace Multitenant;

/// <summary>
/// The client entity
/// </summary>
public class Client
{
    /// <summary>
    /// The identifier
    /// </summary>
    public int Id { get; set; }

    /// <summary>
    /// The name
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// The birthdate
    /// </summary>
    public DateTime BirthDate { get; set; }
}
```

### The **MultitenantContext** class (using SQL Server)

> You have to import :
> 
> * **Microsoft.EntityFrameworkCore.SqlServer** in version **<mark>9.0.9</mark>**
>     
> * **Microsoft.Extensions.Configuration.Abstractions** in version **<mark>9.0.9</mark>**
>     

```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace Multitenant
{
    public class MultitenantContext : DbContext
    {
        /// <summary>
        /// The clients DbSet
        /// </summary>
        public DbSet<Client> Clients { get; set; }

        private readonly IConfiguration _configuration;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="options">The database context options instance</param>
        /// <param name="configuration">The configuration instance</param>
        public MultitenantContext(DbContextOptions<MultitenantContext> options, IConfiguration configuration)
         : base(options)
        {
            _configuration = configuration;
        }

        /// <inheritdoc />
        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(_configuration.GetConnectionString("Default"));
        }

        /// <inheritdoc />
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<Client>(b =>
            {
                b.HasKey(x => x.Id);
                b.Property(x => x.Name).IsRequired().HasMaxLength(256);
                b.Property(x => x.BirthDate).IsRequired();
            });
        }
    }
}
```

## The API project

Next is the API project to request data

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760225302520/8ca940ac-8871-436a-9071-3726f25bc9da.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760225328034/99ae248f-deb7-4eac-a141-c9ee4e2a5a20.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760225350912/f4365921-1e21-4b2b-b4b8-dfe2a36c8ad3.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760225364166/8dce627c-8f50-4492-8b40-ccac86557fe2.png align="center")

### The **ClientsController** class

> Remove the **WeatherForecastController** class and **WeatherForecast** model

```csharp
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Multitenant.Api.Models;

namespace Multitenant.Api.Controllers
{
    [ApiController]
    public class ClientsController : ControllerBase
    {
        private readonly MultitenantContext _context;
        private readonly ILogger<ClientsController> _logger;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="context">The database context</param>
        /// <param name="logger">The logger instance</param>
        public ClientsController(MultitenantContext context, ILogger<ClientsController> logger)
        {
            _context = context;
            _logger = logger;
        }

        [HttpGet("clients")]
        [ProducesResponseType(typeof(IEnumerable<ClientResource>), (int)HttpStatusCode.OK)]
        [ProducesResponseType((int)HttpStatusCode.NoContent)]
        public async Task<IActionResult> GetAll()
        {
            await _context.Database.MigrateAsync();
            var response = await _context.Clients.ToListAsync();
            var result = response.ConvertAll(x => new ClientResource { Name = x.Name, BirthDate = x.BirthDate });

            if(result.Count > 0)
                return Ok(result);
            return NoContent();
        }

        [HttpGet("clients/{id:int}")]
        [ProducesResponseType(typeof(ClientResource), (int)HttpStatusCode.OK)]
        [ProducesResponseType((int)HttpStatusCode.NotFound)]
        public async Task<IActionResult> GetById(int id)
        {
            await _context.Database.MigrateAsync();
            var client = await _context.Clients.FindAsync(id);
            if (client == null)
            {
                return NotFound();
            }
            var result = new ClientResource { Name = client.Name, BirthDate = client.BirthDate };
            return Ok(result);
        }
    }
}
```

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">The <strong>await _context.Database.MigrateAsync()</strong> is here to ensure the database will be created and migrated. In real implementation, this call should be done in the Store implementation and not here !</div>
</div>

### The **ClientResource** model

> Create a folder **Models** in the project

```csharp
namespace Multitenant.Api.Models
{
    /// <summary>
    /// The client resource
    /// </summary>
    public class ClientResource
    {
        /// <summary>
        /// The name
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// The birthdate
        /// </summary>
        public DateTime BirthDate { get; set; }
    }
}
```

### The **appsettings**.json

```json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Default": "Data Source=.;Initial Catalog=Multitenant;Integrated Security=SSPI;Persist Security Info=False;TrustServerCertificate=false;"
  }
}
```

## The multi-tenant scenario

To handle the multi-tenant, try to understand the scenario. On each request, we want to identify the tenant calling the API and request its database to restitute their data.

So, to answer to this scenario, we have to do these steps:

1. Be able to identify the tenant
    
2. Use the tenant to configure the DbContext connection string to target the right database
    

## The multi-tenant implementation

### The interface

First of all, we have to define the tenant interface

```csharp
namespace Multitenant
{
    /// <summary>
    /// The tenant interface
    /// </summary>
    public interface ITenant
    {
        /// <summary>
        /// The tenant identifier
        /// </summary>
        public Guid TenantId { get; set; }
    }
}
```

### The instance

Now, the implementation

```csharp
namespace Multitenant;

/// <summary>
/// The tenant implementation
/// </summary>
public class Tenant : ITenant
{
    /// <inheritdoc/>
    public Guid TenantId { get; set; }
}
```

### The dependency injection

Next is the dependency injection, add scoped the **ITenant** implementation.

```csharp

using Microsoft.EntityFrameworkCore;

namespace Multitenant.Api
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            builder.Services.AddDbContext<MultitenantContext>();

            //NOTICE: Configure the ITenant service to be resolved from the X-Tenant-Id header
            builder.Services.AddHttpContextAccessor();
            builder.Services.AddScoped<ITenant>(sp =>
            {
                var tenantIdString = sp.GetRequiredService<IHttpContextAccessor>().HttpContext?.Request.Headers["X-Tenant-Id"];
                return (!string.IsNullOrEmpty(tenantIdString) && Guid.TryParse(tenantIdString, out var tenantId) ? new Tenant { TenantId = tenantId } : null)!;
            });

            builder.Services.AddControllers();
            // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
            builder.Services.AddEndpointsApiExplorer();
            builder.Services.AddSwaggerGen();

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseHttpsRedirection();

            app.UseAuthorization();

            app.MapControllers();

            app.Run();
        }
    }
}
```

### The connection string prefix or suffix

To be able to address a database per tenant, we have to change the database name in the connection string according to the tenant. To do it, we will pattern the connection string in the configuration file.

> I choose **SUFFIX** but you can change if you want to use **PREFIX** for sure

```json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Default": "Data Source=.;Initial Catalog=Multitenant__Suffix__;Integrated Security=SSPI;Persist Security Info=False;TrustServerCertificate=false;"
  }
}
```

### The database context

Now we have defined the bases, we have to change the database context to mutate per tenant. To do it, we will add the **ITenant** interface to change the connection string dynamically.

```csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace Multitenant
{
    public class MultitenantContext : DbContext
    {
        /// <summary>
        /// The clients DbSet
        /// </summary>
        public DbSet<Client> Clients { get; set; }

        private readonly IConfiguration _configuration;
        private readonly ITenant _tenant;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="options">The database context options instance</param>
        /// <param name="tenant">The tenant instance</param>
        /// <param name="configuration">The configuration instance</param>
        public MultitenantContext(DbContextOptions<MultitenantContext> options, ITenant tenant, IConfiguration configuration)
         : base(options)
        {
            _tenant = tenant;
            _configuration = configuration;
        }

        /// <inheritdoc />
        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            var connectionString = _configuration.GetConnectionString("Default")?.Replace("__Suffix__", _tenant?.TenantId.ToString() ?? "default");
            options.UseSqlServer(connectionString);
        }

        /// <inheritdoc />
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<Client>(b =>
            {
                b.HasKey(x => x.Id);
                b.Property(x => x.Name).IsRequired().HasMaxLength(256);
                b.Property(x => x.BirthDate).IsRequired();
            });
        }
    }
}
```

The **default** fallback value is used during the EF design-time and let you generate the **default** database and generate migrations.

### The migrations

To generate migrations, we need to use a special nuget package dedicated to this

* **Microsoft.EntityFrameworkCore.Design** in version **<mark>9.0.9</mark>**
    

Import this package in the **API project** and set it at **Startup project**

Now, open **Package Manager Console**, and type :

```powershell
Add-Migration Init -context MultitenantContext
```

You will have a new folder **Migrations** containing the scripts to migrate the database.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760230895483/aac9f57e-939e-4020-bbfe-30d4b78adb5f.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">ℹ</div>
<div data-node-type="callout-text">If you launch the command <strong>Update-Database</strong> you will generate the database Multitenant<strong>default</strong>. The <strong>default</strong> value comes from <strong>onConfiguring</strong> where we configure the fallback value in case the tenant value is null.</div>
</div>

### The POSTMAN collection

There are multi ways to do the test, I will illustrate one of them with POSTMAN but you can choose what you prefer.

1. Create a new collection by clicking on the **New** button and choose in the popup **Collection**
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760232215289/7ff88599-0585-4552-88ab-494c65923aa0.png align="center")

2. Click on **Add a request**
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760232277974/6ba0e351-aea0-4b46-ab4f-4f27a6e7d78a.png align="center")

3. Set the URI and keep the verb on **GET.** The host can be determined in the **Properties/launchSettings.json**. Search in **profiles**, the **https**. Take the first part of **applicationUrl** and add **/clients**
    

> For me, I will have **https://localhost:7208/clients**

4. Add a header `X-tenant-id` with the GUID `b9af52c6-0edd-4301-91fe-08dd10851075`
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760233817143/562868b2-9b31-45fd-80f5-384cc8e80468.png align="center")
    
5. Launch the API project
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760232518060/a0c5c195-0a2c-454f-805b-8cf968f92ee4.png align="center")
    

Launch the query in Postman by clicking on **Send** button. Show the result `204 No Content` and a new database created

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760233696529/ac65685f-7036-4081-8493-cc630f3ac91f.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760233962751/1f94add0-8dd7-4b3d-a332-4386ee2a5017.png align="center")

# Conclusion

Thanks for reading. Now you know how to create a multi-tenant system with one database per tenant. Enjoy !

[![](https://cdn.hashnode.com/res/hashnode/image/upload/v1760235238424/dbac30ce-8879-47bf-81b2-7de0288bb0b6.png align="center")](https://github.com/Toenn-Vaot/efcore-multitenant-database)

---

## **Let's Connect!**

Hi, I'm Yoann. I work as a full-stack developer, solution architect.

If you enjoyed this article, you might enjoy my other content, too.

**Github:** [**yblossier**](https://github.com/yblossier)

**LinkedIn:** [**/in/yoannblossier**](https://linkedin.com/in/yoannblossier)

**Buy Me A Coffee:** [**A special thank you for your support**](https://www.buymeacoffee.com/yoannblossier) **🍵**

Thank you for joining me today.

Yoann