Database Setup

Overview

RealtimeChat uses PostgreSQL with Entity Framework Core for data persistence. The database includes support for ASP.NET Core Identity, chat rooms, messages with JSONB storage, and full-text search using trigram indexes.

PostgreSQL Setup

Install PostgreSQL

Install PostgreSQL 15 or later on your system:

macOS
Ubuntu/Debian
Docker

brew install postgresql@15
brew services start postgresql@15

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

docker run -d \
  --name realtimechat-postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=realtime_chat_db \
  -p 5432:5432 \
  postgres:15-alpine

Create the Database

Connect to PostgreSQL and create the database:

psql -U postgres
CREATE DATABASE realtime_chat_db;

Or let Entity Framework create it automatically when running migrations.

Enable Required Extensions

RealtimeChat requires the pg_trgm extension for full-text search:

\c realtime_chat_db
CREATE EXTENSION IF NOT EXISTS pg_trgm;

The migration will attempt to create this extension automatically, but you may need superuser privileges.

Connection Strings

The application uses different connection strings for different environments:

Development
Docker
Production

From appsettings.Development.json:

{
  "ConnectionStrings": {
    "DefaultConnectionString": "Server=localhost;Port=5432;Username=postgres;Password=postgres;Database=realtime_chat_db;Include Error Detail=true;"
  }
}

From appsettings.Docker.json:

{
  "ConnectionStrings": {
    "DefaultConnectionString": "Server=host.docker.internal;Port=5432;Username=postgres;Password=postgres;Database=realtime_chat_db;Include Error Detail=true;"
  }
}

The Docker configuration uses host.docker.internal to connect to PostgreSQL running on the host machine.

From appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnectionString": "Server=localhost;Port=5432;Username=postgres;Password=postgres;Database=realtime_chat_db;Include Error Detail=true;MaxPoolSize=10;MinPoolSize=2;"
  }
}

In production, use strong passwords and consider using managed PostgreSQL services. Configure connection pooling with MaxPoolSize and MinPoolSize for optimal performance.

Database Context

The RealtimeChatDbContext extends ASP.NET Core Identity and includes custom entities:

public class RealtimeChatDbContext : IdentityDbContext<ApplicationUser>
{
    public override DbSet<ApplicationUser> Users { get; set; } = null!;
    public DbSet<ChatRoomEntity> ChatRooms { get; set; } = null!;
    public DbSet<MessageEntity> Messages { get; set; } = null!;
    public DbSet<ChatRoomParticipantEntity> ChatRoomParticipants { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseExceptionProcessor();
        optionsBuilder.UseSnakeCaseNamingConvention();
        optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
}

The context uses snake_case naming convention for database tables and columns, and NoTracking query behavior by default for better performance.

Database Schema

Core Tables

AspNetUsers - User Authentication

Stores user accounts with ASP.NET Core Identity:

id (text, PK) - User identifier
user_name (varchar 256) - Username
normalized_user_name (varchar 256, indexed) - Normalized username
email (varchar 256) - Email address
normalized_email (varchar 256, indexed) - Normalized email
email_confirmed (boolean) - Email verification status
password_hash (text) - Hashed password
first_name (text) - User’s first name
last_name (text) - User’s last name
security_stamp (text) - Security token
concurrency_stamp (text) - Concurrency token
phone_number (text) - Phone number
two_factor_enabled (boolean) - 2FA status
lockout_end (timestamptz) - Lockout expiration
access_failed_count (integer) - Failed login attempts

ChatRooms - Chat Rooms

Stores chat room information:

public class ChatRoomEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<MessageEntity> Messages { get; set; }
    public ICollection<ChatRoomParticipantEntity> ChatRoomParticipants { get; set; }
}

id (uuid, PK) - Chat room identifier
name (text) - Room name
Relationships: One-to-many with Messages and ChatRoomParticipants

Messages - Chat Messages

Stores chat messages with JSONB content:

public class MessageEntity
{
    public Guid Id { get; set; }
    public string UserId { get; set; }
    public Guid ChatRoomId { get; set; }
    public MessageContentEntity Content { get; set; }  // Stored as JSONB
    public DateTime SentAt { get; set; }
    
    public ApplicationUser User { get; set; }
    public ChatRoomEntity ChatRoom { get; set; }
}

id (uuid, PK) - Message identifier
user_id (text, FK) - Message author
chat_room_id (uuid, FK) - Associated chat room
content (jsonb) - Message content (text, images, etc.)
sent_at (timestamptz) - Timestamp (default: CURRENT_TIMESTAMP)

Messages use JSONB storage for flexible content types, allowing text messages, images, and other media.

ChatRoomParticipants - Room Membership

Maps users to chat rooms:

public class ChatRoomParticipantEntity
{
    public Guid Id { get; set; }
    public Guid ChatRoomId { get; set; }
    public string UserId { get; set; }
    
    public ChatRoomEntity ChatRoom { get; set; }
    public ApplicationUser User { get; set; }
}

id (uuid, PK) - Participant record identifier
chat_room_id (uuid, FK) - Chat room reference
user_id (text, FK) - User reference

Identity Tables

ASP.NET Core Identity creates additional tables:

AspNetRoles - User roles
AspNetUserRoles - User-role assignments
AspNetUserClaims - User claims
AspNetUserLogins - External login providers
AspNetUserTokens - Authentication tokens
AspNetRoleClaims - Role claims

Entity Framework Migrations

Creating Migrations

When you modify entity models, create a new migration:

dotnet ef migrations add MigrationName \
  --project Infrastructure/RealtimeChat.Infrastructure.DB \
  --startup-project Applications/RealtimeChat.API

Running Migrations

Update Database

Apply all pending migrations:

dotnet ef database update \
  --project Infrastructure/RealtimeChat.Infrastructure.DB \
  --startup-project Applications/RealtimeChat.API

Target Specific Migration

Roll back to a specific migration:

dotnet ef database update MigrationName \
  --project Infrastructure/RealtimeChat.Infrastructure.DB \
  --startup-project Applications/RealtimeChat.API

Generate SQL Script

Generate SQL script without applying:

dotnet ef migrations script \
  --project Infrastructure/RealtimeChat.Infrastructure.DB \
  --startup-project Applications/RealtimeChat.API \
  --output migration.sql

Key Migrations

InitialCreate - Database Foundation

Creates the initial database schema with ASP.NET Identity tables:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "AspNetUsers",
        columns: table => new
        {
            id = table.Column<string>(type: "text", nullable: false),
            user_name = table.Column<string>(type: "character varying(256)", 
                maxLength: 256, nullable: true),
            email = table.Column<string>(type: "character varying(256)", 
                maxLength: 256, nullable: true),
            // ... other Identity columns
        });
    
    // Creates indexes on normalized_user_name and normalized_email
}

AddTrgmIndexToTextMessageContent - Search Optimization

Adds PostgreSQL trigram index for fast full-text search:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS pg_trgm;");
    
    migrationBuilder.Sql(@"
        CREATE INDEX IF NOT EXISTS idx_messages_content_text_trgm
        ON messages
        USING GIN (
            jsonb_extract_path_text(content, 'Text') gin_trgm_ops
        )
        WHERE content->>'Type' = 'text';
    ");
}

This index enables fast fuzzy text search across message content using PostgreSQL’s trigram similarity.

ChangeMessageContentToJson - JSONB Storage

Converts message content to JSONB for flexible content types:

builder.Property(e => e.Content)
    .HasColumnType("jsonb")
    .HasConversion(ContentConverter);

Allows storing various message types (text, images, files) in a single column.

Database Indexes

Performance Indexes

The database includes several indexes for optimal query performance:

CREATE INDEX idx_messages_content_text_trgm
ON messages
USING GIN (
    jsonb_extract_path_text(content, 'Text') gin_trgm_ops
)
WHERE content->>'Type' = 'text';

The TRGM index requires the pg_trgm extension. Ensure it’s enabled before running migrations.

Seeding Data

Create a data seeding class for development:

public static class DataSeeder
{
    public static async Task SeedAsync(RealtimeChatDbContext context, 
        UserManager<ApplicationUser> userManager)
    {
        // Seed users
        if (!context.Users.Any())
        {
            var user = new ApplicationUser
            {
                UserName = "testuser",
                Email = "test@example.com",
                FirstName = "Test",
                LastName = "User",
                EmailConfirmed = true
            };
            
            await userManager.CreateAsync(user, "Test123!");
        }
        
        // Seed chat rooms
        if (!context.ChatRooms.Any())
        {
            context.ChatRooms.AddRange(
                new ChatRoomEntity { Id = Guid.NewGuid(), Name = "General" },
                new ChatRoomEntity { Id = Guid.NewGuid(), Name = "Random" }
            );
            
            await context.SaveChangesAsync();
        }
    }
}

Backup and Restore

Backup Database

pg_dump
Docker

# Full backup
pg_dump -U postgres -d realtime_chat_db -F c -f backup.dump

# SQL format
pg_dump -U postgres -d realtime_chat_db -f backup.sql

# Schema only
pg_dump -U postgres -d realtime_chat_db -s -f schema.sql

docker exec realtimechat-postgres pg_dump -U postgres \
  realtime_chat_db > backup.sql

Restore Database

pg_restore
Docker

# From custom format
pg_restore -U postgres -d realtime_chat_db -c backup.dump

# From SQL file
psql -U postgres -d realtime_chat_db -f backup.sql

docker exec -i realtimechat-postgres psql -U postgres \
  realtime_chat_db < backup.sql

Troubleshooting

Migration fails with permission error

The pg_trgm extension requires superuser privileges:

# Connect as superuser
psql -U postgres -d realtime_chat_db
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Connection refused errors

Verify PostgreSQL is running and accepting connections:

# Check PostgreSQL status
sudo systemctl status postgresql

# Test connection
psql -U postgres -h localhost -p 5432

Check pg_hba.conf for connection authentication rules.

EF Core migrations out of sync

Reset migrations (⚠️ destroys data):

dotnet ef database drop --force
dotnet ef database update

Or manually sync:

dotnet ef migrations remove
dotnet ef migrations add RecreatedMigration
dotnet ef database update

Best Practices

Use Migrations for Schema Changes

Always create migrations for schema changes instead of modifying the database directly.

Connection Pooling

Configure connection pooling in production:

MaxPoolSize=10;MinPoolSize=2;Connection Lifetime=300;

Regular Backups

Schedule automated backups using pg_dump or your cloud provider’s backup solution.

Monitor Performance

Use pg_stat_statements extension to monitor slow queries:

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

Secure Credentials

Use environment variables or secret management for database credentials in production.

Next Steps

Configuration - Configure application settings
Docker Deployment - Deploy with Docker
API Reference - Explore GraphQL API

Get Started

Authentication

Guides

Overview

PostgreSQL Setup

Connection Strings

Database Context

Database Schema

Core Tables

Identity Tables

Entity Framework Migrations

Creating Migrations

Running Migrations

Key Migrations

Database Indexes

Performance Indexes

Seeding Data

Backup and Restore

Backup Database

Restore Database

Troubleshooting

Best Practices

Next Steps

Build docs developers (and LLMs) love

Get Started

Authentication

Guides

​Overview

​PostgreSQL Setup

​Connection Strings

​Database Context

​Database Schema

​Core Tables

​Identity Tables

​Entity Framework Migrations

​Creating Migrations

​Running Migrations

​Key Migrations

​Database Indexes

​Performance Indexes

​Seeding Data

​Backup and Restore

​Backup Database

​Restore Database

​Troubleshooting

​Best Practices

​Next Steps

Build docs developers (and LLMs) love

Overview

PostgreSQL Setup

Connection Strings

Database Context

Database Schema

Core Tables

Identity Tables

Entity Framework Migrations

Creating Migrations

Running Migrations

Key Migrations

Database Indexes

Performance Indexes

Seeding Data

Backup and Restore

Backup Database

Restore Database

Troubleshooting

Best Practices

Next Steps