Database Setup

Overview

The Tournament Management App uses Entity Framework Core with support for both SQLite (default) and SQL Server databases. The application maintains two separate database contexts for different purposes.

Database Contexts

The application uses two Entity Framework Core contexts:

DataContext

Purpose: Tournament domain dataEntities:

Municipios (Municipalities)
DirectoresTecnicos (Technical Directors)
Equipos (Teams)
Jugadores (Players)
Partidos (Matches)
Posiciones (Positions)

IdentityDataContext

Purpose: User authenticationEntities:

AspNetUsers
AspNetRoles
AspNetUserClaims
AspNetRoleClaims
DataProtectionKeys

Database Providers

SQLite (Default)

SQLite is the default database provider, ideal for development and small deployments: Advantages:

No separate database server required
File-based storage
Zero configuration
Perfect for development and testing

Connection String:

Program.cs

var connectionString = Environment.GetEnvironmentVariable("DATABASE_CONNECTION_STRING") 
    ?? "Data Source=/app/Torneo.db";

The database file is created automatically at /app/Torneo.db inside the Docker container, or in the application directory for local development.

SQL Server

For production deployments with higher load, you can use SQL Server: Advantages:

Better performance at scale
Advanced features (stored procedures, full-text search)
Enterprise support and tooling
Concurrent access handling

Configuration:

Set environment variable

export DATABASE_CONNECTION_STRING="Server=localhost;Database=TorneoDb;User Id=sa;Password=YourPassword123!;TrustServerCertificate=True;"

Update DbContext configuration

The application automatically detects SQL Server connection strings. No code changes needed.

Run migrations

dotnet ef database update --project Torneo.App.Persistencia --startup-project Torneo.App.Frontend --context Torneo.App.Persistencia.DataContext

dotnet ef database update --project Torneo.App.Frontend --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext

Initial Setup

Docker Deployment (Automatic)

When using Docker, migrations run automatically during image build:

Dockerfile (lines 19-35)

# DataContext migrations
RUN dotnet ef migrations add InitialCreate \
    --project Torneo.App.Persistencia/Torneo.App.Persistencia.csproj \
    --startup-project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
    --context Torneo.App.Persistencia.DataContext \
    --no-build --configuration Release \
&& dotnet ef database update \
    --project Torneo.App.Persistencia/Torneo.App.Persistencia.csproj \
    --startup-project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
    --context Torneo.App.Persistencia.DataContext

# IdentityDataContext migrations  
RUN dotnet ef migrations add CreateIdentitySchema \
    --project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
    --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext \
    --no-build --configuration Release \
&& dotnet ef database update \
    --project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
    --startup-project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
    --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext

The database is fully initialized when the container starts.

Local Development (Manual)

For local development without Docker:

Install Entity Framework Tools

dotnet tool install --global dotnet-ef --version 8.0.0

Verify installation:

dotnet ef --version

Navigate to the repository

cd Tournament-Management-App/Torneo.App

Create DataContext migrations

dotnet ef migrations add InitialCreate \
  --project Torneo.App.Persistencia/Torneo.App.Persistencia.csproj \
  --startup-project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
  --context Torneo.App.Persistencia.DataContext

Apply DataContext migrations

dotnet ef database update \
  --project Torneo.App.Persistencia/Torneo.App.Persistencia.csproj \
  --startup-project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
  --context Torneo.App.Persistencia.DataContext

Create IdentityDataContext migrations

dotnet ef migrations add CreateIdentitySchema \
  --project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
  --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext

Apply IdentityDataContext migrations

dotnet ef database update \
  --project Torneo.App.Frontend/Torneo.App.Frontend.csproj \
  --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext

Run the application

cd Torneo.App.Frontend
dotnet run

The application will start on https://localhost:5001 and http://localhost:5000.

Database Schema

Tournament Domain Tables

Table	Description	Key Relationships
Municipios	Cities/municipalities where teams are based	Referenced by Equipos
DirectoresTecnicos	Technical directors (coaches)	Referenced by Equipos
Equipos	Tournament teams	References Municipio, DirectorTecnico; has many Jugadores and Partidos
Jugadores	Players on teams	References Equipo and Posicion
Posiciones	Player positions (Goalkeeper, Defender, etc.)	Referenced by Jugadores
Partidos	Matches between teams	References two Equipos (Local and Visitante)

Identity Tables

Table	Description
AspNetUsers	User accounts with email and password
AspNetRoles	User roles for authorization
AspNetUserRoles	User-to-role assignments
AspNetUserClaims	Additional user claims
AspNetUserLogins	External authentication providers
DataProtectionKeys	Encryption keys for data protection

Entity Relationships

Migration Management

List Migrations

View applied and pending migrations:

# DataContext migrations
dotnet ef migrations list \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

# IdentityDataContext migrations
dotnet ef migrations list \
  --project Torneo.App.Frontend \
  --context Torneo.App.Frontend.Areas.Identity.Data.IdentityDataContext

Create New Migration

When you modify entity models:

# Create migration for domain changes
dotnet ef migrations add <MigrationName> \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

# Apply the migration
dotnet ef database update \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

Rollback Migration

To undo the last migration:

# Rollback to previous migration
dotnet ef database update <PreviousMigrationName> \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

# Remove migration file
dotnet ef migrations remove \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

Rollbacks can cause data loss if the migration being removed includes DropTable or DropColumn operations. Always backup your database before rolling back migrations.

Generate SQL Script

To review SQL without applying changes:

dotnet ef migrations script \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext \
  --output migration.sql

Database Seeding

To add initial data, you can seed the database in DataContext.cs using the OnModelCreating method:

Example Seeding

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Seed positions
    modelBuilder.Entity<Posicion>().HasData(
        new Posicion { Id = 1, Nombre = "Portero" },
        new Posicion { Id = 2, Nombre = "Defensa" },
        new Posicion { Id = 3, Nombre = "Mediocampista" },
        new Posicion { Id = 4, Nombre = "Delantero" }
    );
    
    // Seed municipalities
    modelBuilder.Entity<Municipio>().HasData(
        new Municipio { Id = 1, Nombre = "Medellín" },
        new Municipio { Id = 2, Nombre = "Bogotá" },
        new Municipio { Id = 3, Nombre = "Cali" }
    );
}

After adding seed data, create a new migration:

dotnet ef migrations add SeedInitialData \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

Health Checks

The application includes database health checks configured in Program.cs:

Program.cs (lines 43-44)

builder.Services.AddHealthChecks()
    .AddSqlite(connectionString, name: "database", failureStatus: HealthStatus.Unhealthy, tags: new[] { "db" });

Check database health:

curl http://localhost:5000/health

Expected Response:

{
  "status": "Healthy",
  "results": {
    "database": {
      "status": "Healthy"
    }
  }
}

Backup and Restore

SQLite Backup

For SQLite databases, simply copy the database file:

# Backup
docker cp torneo-app:/app/Torneo.db ./Torneo-backup-$(date +%Y%m%d).db

# Restore
docker cp ./Torneo-backup-20240315.db torneo-app:/app/Torneo.db
docker-compose restart

SQL Server Backup

For SQL Server, use standard backup commands:

-- Backup
BACKUP DATABASE TorneoDb 
TO DISK = '/var/opt/mssql/backups/TorneoDb.bak'
WITH FORMAT, MEDIANAME = 'TorneoDbBackup';

-- Restore
RESTORE DATABASE TorneoDb 
FROM DISK = '/var/opt/mssql/backups/TorneoDb.bak'
WITH REPLACE;

Troubleshooting

Migration fails with 'context not found' error

Solution: Ensure you specify the correct context and projects:

dotnet ef migrations add MyMigration \
  --project Torneo.App.Persistencia \
  --startup-project Torneo.App.Frontend \
  --context Torneo.App.Persistencia.DataContext

Always use the full namespace for the context.

Database locked errors (SQLite)

Solution: SQLite doesn’t handle concurrent writes well. Ensure:

Only one process accesses the database at a time
Close all connections properly
For high-concurrency scenarios, use SQL Server instead

Foreign key constraint violations

Solution: The application uses DeleteBehavior.Restrict to prevent cascading deletes. You must:

Delete dependent records first (e.g., delete Jugadores before deleting Equipo)
Or update relationships to null before deletion

See Data Context for relationship configurations.

Connection string not found

Solution: Set the DATABASE_CONNECTION_STRING environment variable:

export DATABASE_CONNECTION_STRING="Data Source=/app/Torneo.db"

Or update appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=/app/Torneo.db"
  }
}

'dotnet ef' command not found

Solution: Install Entity Framework Core tools globally:

dotnet tool install --global dotnet-ef --version 8.0.0

Add to PATH if needed:

export PATH="$PATH:$HOME/.dotnet/tools"

Next Steps

Data Context API

Explore the DataContext and IdentityDataContext APIs

Migrations Guide

Learn about Entity Framework Core migrations

Configuration

Configure environment variables and settings

Domain Models

Review the entity models and relationships

Get Started

Core Features

Deployment

Guides

Overview

Database Contexts

DataContext

IdentityDataContext

Database Providers

SQLite (Default)

SQL Server

Initial Setup

Docker Deployment (Automatic)

Local Development (Manual)

Database Schema

Tournament Domain Tables

Identity Tables

Entity Relationships

Migration Management

List Migrations

Create New Migration

Rollback Migration

Generate SQL Script

Database Seeding

Health Checks

Backup and Restore

SQLite Backup

SQL Server Backup

Troubleshooting

Next Steps

Data Context API

Migrations Guide

Configuration

Domain Models

Build docs developers (and LLMs) love

Get Started

Core Features

Deployment

Guides

​Overview

​Database Contexts

DataContext

IdentityDataContext

​Database Providers

​SQLite (Default)

​SQL Server

​Initial Setup

​Docker Deployment (Automatic)

​Local Development (Manual)

​Database Schema

​Tournament Domain Tables

​Identity Tables

​Entity Relationships

​Migration Management

​List Migrations

​Create New Migration

​Rollback Migration

​Generate SQL Script

​Database Seeding

​Health Checks

​Backup and Restore

​SQLite Backup

​SQL Server Backup

​Troubleshooting

​Next Steps

Data Context API

Migrations Guide

Configuration

Domain Models

Build docs developers (and LLMs) love

Overview

Database Contexts

Database Providers

SQLite (Default)

SQL Server

Initial Setup

Docker Deployment (Automatic)

Local Development (Manual)

Database Schema

Tournament Domain Tables

Identity Tables

Entity Relationships

Migration Management

List Migrations

Create New Migration

Rollback Migration

Generate SQL Script

Database Seeding

Health Checks

Backup and Restore

SQLite Backup

SQL Server Backup

Troubleshooting

Next Steps