Containerize ASP.NET Core API, Entity Framework with SQL Server, Let's Encrypt, Docker, and Nginx (Part 1)

To be honest, finding a comprehensive solution online for setting up Docker, ASP.NET Core, SQL Server, Let's Encrypt, and Nginx all together is like searching for a needle in a haystack. After hours of Googling and plenty of frustration, I decided to write down everything I learned and make life easier for everyone. Ready to get started?

How it works?

The idea is to containerize ASP.NET Core, SQL Server, Let's Encrypt, and Nginx using Docker and deploy it to an Ubuntu VPS. You can still manage the database via Azure Data Studio or SQL Server Management Studio. HTTPS will be enabled for free using Let's Encrypt, with a 3-month certificate renewal.

In this article, I'll guide you through containerizing an ASP.NET Core API with SQL Server. We'll start by setting up Entity Framework Core with Code First, then move on to configuring the containers, and add Nginx and SSL later.

Step 1: Dockerfile for ASP.NET

First, create a Dockerfile for ASP.NET. It’s simple to do in Microsoft Visual Studio. Even if you have multiple projects in your solution, just right-click on the main project, select Add > Docker Support, you can follow this article.

Step 2: Set Up Docker-Compose
Now that you’ve created the Dockerfile for your ASP.NET Core, the next step is to set up docker-compose to connect your application with the SQL Server database inside the container.

version: '3.4'

services:
  sqldb:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - SA_PASSWORD=your-password
      - ACCEPT_EULA=Y
    ports:
      - "8002:1433"
    restart: always
    volumes:
      #- sql_data:/var/opt/mssql 
    networks:
      - quochuynhwebsite-network

  quochuynhwebsite:
    image: ${DOCKER_REGISTRY-}quochuynhwebsite
    build:
      context: .
      dockerfile: QuocHuynhWebsite/Dockerfile
    ports:
      - "8003:80"
    expose:
      - "5000"
    environment:
      - ASPNETCORE_URLS=http://+:5000
    networks:
      - quochuynhwebsite-network
    restart: always

networks:
  quochuynhwebsite-network:
    driver: bridge

Important notes: Make sure your database is running before starting your application. Avoid using volumes on your local machine, as they may conflict with your local database. Also, don't forget to configure the network to ensure proper communication between the containers.

Step 3: Update Connection String in appsettings.json

After setting docker-compose file, update the connection string in appsettings.json to match the database configuration inside Docker. Since the database is running in a Docker container, you’ll use the service name (sqldb) as the Server value in your connection string.

Server: sqldb
UserId: sa

"ConnectionStrings": {
    "sqlConnection": "Server=sqldb;Database=DbName;User Id=sa;Password=yourpassword;TrustServerCertificate=True;"
},

Step 4: Setting up Entity Framework Core with Code First

If you think everything will run perfectly, you might be mistaken. The issue arises when you use Entity Framework to create the database from your classes via command line commands like add-migration and update-database. These commands are typically run using the .NET CLI, which means you'll need to create an additional image in your docker-compose setup. Is it necessary? No, you can use Entity Framework's built-in functionality to automatically create the database and tables on the first request, saving you the extra step of managing migrations manually.

Automating Database Creation with Entity Framework in ASP.NET Core

When deploying an ASP.NET Core application with Entity Framework, ensuring your database is created and tables are ready on the first run can save a lot of time and hassle. While migrations and CLI commands are often used, you can automate this process directly in your DbContext constructor.

public RepositoryContext(DbContextOptions options) : base(options)
{
    try
    {
        var databaseCreator = Database.GetService<IDatabaseCreator>() as RelationalDatabaseCreator;
        if (databaseCreator != null)
        {
            // Create the database if it doesn't exist
            if (!databaseCreator.CanConnect())
            {
                databaseCreator.Create();
            }

            // Create tables if they don't exist
            if (!databaseCreator.HasTables())
            {
                databaseCreator.CreateTables();
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Database creation failed: {ex.Message}");
    }
}

Now, let's access our ASP.NET Core Application:

docker-compose up --build 

// url: http://localhost:8003

But, how I can access my database? Oke Let me teach you:
Server name: localhost,8002
Authentication: SQL Server Authantication
Login: sa
Password: your password

Conclusion

Congratulations! You’ve successfully set up your ASP.NET Core application with SQL Server in Docker. By following these steps, you've containerized your app, configured Entity Framework for automated database creation, and established connectivity between your API and the SQL Server database.
But we’re not stopping here. To make your application production-ready, it’s crucial to implement secure HTTPS access. This is where Nginx and Let’s Encrypt SSL come into play.

Let's connect

Email: quochuynhdbcontact@gmail.com
Website: https://www.quochuynh.website