Baza danych¶

Wprowadzenie¶

System Masaku używa SQL Server 2019+ jako głównej bazy danych z Liquibase do zarządzania migracjami schematu.

Technologie¶

Technologia	Wersja	Zastosowanie
SQL Server	2019+	Relacyjna baza danych
Entity Framework Core	6.0+	ORM
Liquibase	Latest	Database migrations
Azure SQL Database	-	Production (cloud)

Lokalne środowisko (Docker)¶

Uruchomienie SQL Server lokalnie¶

cd masaku-api/docker
docker-compose up -d

Uruchamia: - SQL Server na porcie 1433 - Login: sa - Hasło: Masaku123 - Baza: Masaku

Połączenie z bazą¶

Connection string:

Server=localhost,1433;Database=Masaku;User=sa;Password=Masaku123;TrustServerCertificate=True

Narzędzia do zarządzania: - DBeaver (zalecane) - Azure Data Studio - SQL Server Management Studio (SSMS)

Schema Database¶

Główne tabele¶

Users¶

CREATE TABLE Users (
    Id VARCHAR(450) PRIMARY KEY,
    Email NVARCHAR(256) NOT NULL,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0
);

Budgets¶

CREATE TABLE Budgets (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    StartDate DATETIME2 NOT NULL,
    EndDate DATETIME2,
    UserId VARCHAR(450) NOT NULL,
    Shared BIT NOT NULL DEFAULT 0,
    Currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    Status VARCHAR(20) NOT NULL DEFAULT 'Active',
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Budgets_Users FOREIGN KEY (UserId) REFERENCES Users(Id)
);

CREATE INDEX IX_Budgets_UserId ON Budgets(UserId);
CREATE INDEX IX_Budgets_UserId_Status ON Budgets(UserId, Status);

Clients¶

CREATE TABLE Clients (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(200) NOT NULL,
    Email NVARCHAR(256),
    Phone NVARCHAR(20),
    VatNumber NVARCHAR(50),
    Address NVARCHAR(500),
    City NVARCHAR(100),
    PostalCode NVARCHAR(20),
    Country VARCHAR(2),
    UserId VARCHAR(450) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Clients_Users FOREIGN KEY (UserId) REFERENCES Users(Id)
);

Expenses¶

CREATE TABLE Expenses (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Description NVARCHAR(500) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    ExpenseDate DATETIME2 NOT NULL,
    ExpenseType VARCHAR(20) NOT NULL,
    BudgetId INT NOT NULL,
    ReceiptId INT,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Expenses_Budgets FOREIGN KEY (BudgetId) REFERENCES Budgets(Id),
    CONSTRAINT FK_Expenses_Receipts FOREIGN KEY (ReceiptId) REFERENCES Receipts(Id)
);

Invoices¶

CREATE TABLE Invoices (
    Id INT PRIMARY KEY IDENTITY(1,1),
    InvoiceNumber NVARCHAR(50) NOT NULL UNIQUE,
    IssueDate DATETIME2 NOT NULL,
    DueDate DATETIME2 NOT NULL,
    ClientId INT NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    VatAmount DECIMAL(18,2) NOT NULL,
    Status VARCHAR(20) NOT NULL DEFAULT 'Draft',
    UserId VARCHAR(450) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Invoices_Clients FOREIGN KEY (ClientId) REFERENCES Clients(Id),
    CONSTRAINT FK_Invoices_Users FOREIGN KEY (UserId) REFERENCES Users(Id)
);

Receipts¶

CREATE TABLE Receipts (
    Id INT PRIMARY KEY IDENTITY(1,1),
    FileName NVARCHAR(255) NOT NULL,
    BlobUrl NVARCHAR(1000) NOT NULL,
    OcrText NVARCHAR(MAX),
    ProcessedAt DATETIME2,
    UserId VARCHAR(450) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2,
    IsDeleted BIT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Receipts_Users FOREIGN KEY (UserId) REFERENCES Users(Id)
);

Database Migrations (Liquibase)¶

Struktura changelogów¶

masaku-api/Sql/Liquibase/
├── liquibase.properties-template
├── 1_dbchangelog.xml           # Initial schema
├── 2_dbchangelog.xml           # Add indexes
├── 3_dbchangelog.xml           # Add new tables
└── ...

Przykład changelog¶

<!-- 3_dbchangelog.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">

    <changeSet id="3-1" author="dev">
        <comment>Add BudgetShares table for shared budgets</comment>
        <createTable tableName="BudgetShares">
            <column name="Id" type="INT" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="BudgetId" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="UserId" type="VARCHAR(450)">
                <constraints nullable="false"/>
            </column>
            <column name="Permission" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="CreatedAt" type="DATETIME2" defaultValueComputed="GETUTCDATE()">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint
            constraintName="FK_BudgetShares_Budgets"
            baseTableName="BudgetShares"
            baseColumnNames="BudgetId"
            referencedTableName="Budgets"
            referencedColumnNames="Id"
            onDelete="CASCADE"/>

        <addForeignKeyConstraint
            constraintName="FK_BudgetShares_Users"
            baseTableName="BudgetShares"
            baseColumnNames="UserId"
            referencedTableName="Users"
            referencedColumnNames="Id"
            onDelete="CASCADE"/>

        <createIndex indexName="IX_BudgetShares_BudgetId" tableName="BudgetShares">
            <column name="BudgetId"/>
        </createIndex>
    </changeSet>

    <changeSet id="3-2" author="dev">
        <comment>Add Shared column to Budgets</comment>
        <addColumn tableName="Budgets">
            <column name="Shared" type="BIT" defaultValue="0">
                <constraints nullable="false"/>
            </column>
        </addColumn>
    </changeSet>

</databaseChangeLog>

Tworzenie nowej migracji¶

Utwórz nowy plik N_dbchangelog.xml (N = kolejny numer)

Zaktualizuj liquibase.properties-template:

changeLogFile=Sql/Liquibase/N_dbchangelog.xml

Merge do target brancha (automatyczny deployment)

Entity Framework Core¶

DbContext¶

public class MasakuDbContext : DbContext
{
    public MasakuDbContext(DbContextOptions<MasakuDbContext> options)
        : base(options) { }

    public DbSet<User> Users { get; set; }
    public DbSet<Budget> Budgets { get; set; }
    public DbSet<Client> Clients { get; set; }
    public DbSet<Expense> Expenses { get; set; }
    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<Receipt> Receipts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(MasakuDbContext).Assembly);

        // Global query filters (soft delete)
        modelBuilder.Entity<Budget>().HasQueryFilter(b => !b.IsDeleted);
        modelBuilder.Entity<Client>().HasQueryFilter(c => !c.IsDeleted);
    }
}

Queries¶

// Podstawowe query
var budgets = await _context.Budgets
    .Where(b => b.UserId == userId)
    .ToListAsync();

// Z relacjami (eager loading)
var budget = await _context.Budgets
    .Include(b => b.Expenses)
    .Include(b => b.User)
    .FirstOrDefaultAsync(b => b.Id == id);

// Split query (lepszy performance dla wielu includes)
var budget = await _context.Budgets
    .Include(b => b.Expenses)
    .Include(b => b.SharedWith)
    .AsSplitQuery()
    .FirstOrDefaultAsync(b => b.Id == id);

// Read-only (AsNoTracking)
var budgets = await _context.Budgets
    .AsNoTracking()
    .ToListAsync();

// Złożone query
var result = await _context.Budgets
    .Where(b => b.UserId == userId)
    .Where(b => b.Status == BudgetStatus.Active)
    .Where(b => b.Amount > 1000)
    .OrderByDescending(b => b.CreatedAt)
    .Skip(page * pageSize)
    .Take(pageSize)
    .Select(b => new BudgetSummaryDto
    {
        Id = b.Id,
        Name = b.Name,
        TotalSpent = b.Expenses.Sum(e => e.Amount)
    })
    .ToListAsync();

Indeksy¶

Strategia indeksowania¶

-- Single column indexes
CREATE INDEX IX_Budgets_UserId ON Budgets(UserId);
CREATE INDEX IX_Expenses_BudgetId ON Expenses(BudgetId);

-- Composite indexes (najczęstsze zapytania)
CREATE INDEX IX_Budgets_UserId_Status ON Budgets(UserId, Status);
CREATE INDEX IX_Expenses_BudgetId_ExpenseDate ON Expenses(BudgetId, ExpenseDate);

-- Covering index (include dodatkowe kolumny)
CREATE INDEX IX_Budgets_UserId_Status_Include
ON Budgets(UserId, Status)
INCLUDE (Name, Amount, StartDate);

Monitorowanie performance¶

-- Missing indexes
SELECT *
FROM sys.dm_db_missing_index_details
ORDER BY avg_user_impact DESC;

-- Index usage statistics
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks DESC;

Backup i Restore¶

Local (Docker)¶

# Backup
docker exec -it masaku-sql /opt/mssql-tools/bin/sqlcmd \
  -S localhost -U sa -P 'Masaku123' \
  -Q "BACKUP DATABASE [Masaku] TO DISK = '/var/opt/mssql/backup/Masaku.bak'"

# Restore
docker exec -it masaku-sql /opt/mssql-tools/bin/sqlcmd \
  -S localhost -U sa -P 'Masaku123' \
  -Q "RESTORE DATABASE [Masaku] FROM DISK = '/var/opt/mssql/backup/Masaku.bak' WITH REPLACE"

Azure SQL Database¶

Backup automatyczne (Point-in-Time Restore dostępny przez 7-35 dni)

# Restore via Azure CLI
az sql db restore \
  --resource-group <resource-group> \
  --server <server-name> \
  --name <database-name> \
  --dest-name <restored-database-name> \
  --time "2025-01-24T10:00:00Z"

Performance Optimization¶

Query optimization¶

// ❌ N+1 problem
var budgets = await _context.Budgets.ToListAsync();
foreach (var budget in budgets)
{
    var expenses = await _context.Expenses
        .Where(e => e.BudgetId == budget.Id)
        .ToListAsync();
}

// ✅ Eager loading
var budgets = await _context.Budgets
    .Include(b => b.Expenses)
    .ToListAsync();

// ✅ Explicit loading (gdy potrzebne)
var budget = await _context.Budgets.FindAsync(id);
await _context.Entry(budget)
    .Collection(b => b.Expenses)
    .LoadAsync();

// ✅ Projection (tylko potrzebne dane)
var budgetSummaries = await _context.Budgets
    .Select(b => new
    {
        b.Id,
        b.Name,
        ExpenseCount = b.Expenses.Count()
    })
    .ToListAsync();

Batch operations¶

// Bulk insert
await _context.Budgets.AddRangeAsync(budgets);
await _context.SaveChangesAsync();

// Bulk update
_context.Budgets.UpdateRange(budgets);
await _context.SaveChangesAsync();

// Bulk delete
_context.Budgets.RemoveRange(budgets);
await _context.SaveChangesAsync();

Connection Pooling¶

EF Core automatycznie używa connection poolingu:

services.AddDbContext<MasakuDbContext>(options =>
    options.UseSqlServer(
        connectionString,
        sqlOptions => {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 3,
                maxRetryDelay: TimeSpan.FromSeconds(5),
                errorNumbersToAdd: null
            );
        }
    )
);