Database connection issues when deploying c# to railway

Solution for Database Connection Issues When Deploying C# .NET 8 API to Railway

📋 Project Background

This article documents the database connection issues encountered when deploying a C# .NET 8 Web API project from local development environment to Railway cloud platform. The project uses Supabase as PostgreSQL database and EntityFramework Core as ORM. While it worked perfectly in local development, all data APIs returned 500 errors after deployment to production.

Tech Stack:

• C# .NET 8 Web API
• Entity Framework Core with Npgsql (PostgreSQL)
• Supabase (Managed PostgreSQL)
• Railway (Cloud deployment platform)
• Docker containerized deployment

🚨 Problem Symptoms

1. Issue Description

• ✅ Local development environment: All APIs working normally
• ✅ Railway health check endpoint: Normal 200 response
• ✅ Railway CORS configuration: Working normally
• ❌ Railway data API endpoints: All returning 500 internal server error

2. Initial Error Information

# Response from all data-related APIs
HTTP 500 Internal Server Error
{
  "type": "https://tools.ietf.org/html/rfc7231#section-6.6.1",
  "title": "An error occurred while processing your request.",
  "status": 500,
  "traceId": "00-xxx-xxx-00"
}

3. Environment Comparison

| Environment | Status | Database Connection | API Response | |————-|——–|——————-|————-| | Local Development | ✅ Normal | ✅ Success | ✅ 200 OK | | Railway Production | ❌ Error | ❌ Failed | ❌ 500 Error |

🔍 Troubleshooting Process

Stage 1: Verify Basic Configuration

First verified Railway deployment basic configuration:

1. Environment Variables Check: Confirmed all necessary environment variables were set correctly
2. Health Check Verification: /api/health endpoint responding normally
3. CORS Configuration Verification: Cross-origin requests working normally
4. Supabase Connection Verification: Confirmed data exists in database via REST API

Stage 2: Add Diagnostic Tools

To get detailed information from production environment, created diagnostic controller:

[ApiController]
[Route("api/[controller]")]
public class DiagnosticController : ControllerBase
{
    [HttpGet("connection")]
    public async Task<IActionResult> TestConnection()
    {
        // Check environment variables, connection string and database connection status
        var canConnect = await _context.Database.CanConnectAsync();
        // Return detailed diagnostic information
    }
}

Stage 3: Discover Key Issue

Through diagnostic endpoint, discovered the core problem:

{
  "databaseConnection": {
    "canConnect": false,
    "connectionState": "postgresql://postgre..."
  }
}

Key Discovery: Database connection failed!

Stage 4: Deep Error Analysis

After adding detailed error capture, obtained key error information:

{
  "success": false,
  "message": "Connection test failed",
  "connectionError": "Format of the initialization string does not conform to specification starting at index 0.",
  "stackTrace": [
    "at Npgsql.NpgsqlConnectionStringBuilder..ctor(String connectionString)"
  ]
}

Core Problem Identified: Connection string format incompatibility!

🐛 Root Cause Analysis

Problem Source

The root cause was inconsistent database connection string formats between local development and production environments:

Local Development Environment

// Read individual environment variables from .env file
SUPABASE_URL=https://xxx.supabase.co
SUPABASE_DB_HOST=aws-1-ap-southeast-2.pooler.supabase.com
SUPABASE_DB_PORT=5432
SUPABASE_PASSWORD=xxx

// Assemble connection string in Program.cs
var connectionString = $"Host={dbHost};Port={dbPort};Database=postgres;Username=postgres.{projectRef};Password={password};SSL Mode=Require";

Railway Production Environment

// Railway provides complete PostgreSQL URI
ConnectionStrings__DefaultConnection=postgresql://postgres.xxx:password@host:port/postgres

// Code directly uses this URI, but Npgsql doesn't support this format
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

Format Conflict Details

• Railway provided format: postgresql://username:password@host:port/database (Standard PostgreSQL URI)
• Npgsql expected format: Host=host;Port=port;Database=database;Username=username;Password=password;SSL Mode=Require (Key-value pair format)

🛠️ Solution Implementation

Step 1: Add URI Format Detection and Conversion

Modified connection string handling logic in Program.cs:

// Build database connection string from environment variables or configuration
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") 
                      ?? Environment.GetEnvironmentVariable("SUPABASE_CONNECTION_STRING");

// Check if it's PostgreSQL URI format (postgresql://...)
if (!string.IsNullOrEmpty(connectionString) && connectionString.StartsWith("postgresql://"))
{
    // Railway provided PostgreSQL URI format, convert to Npgsql format
    try
    {
        var uri = new Uri(connectionString);
        var host = uri.Host;
        var port = uri.Port != -1 ? uri.Port : 5432;
        var database = uri.AbsolutePath.TrimStart('/');
        var username = uri.UserInfo.Split(':')[0];
        var password = uri.UserInfo.Split(':')[1];
        
        connectionString = $"Host={host};Port={port};Database={database};Username={username};Password={password};SSL Mode=Require";
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Failed to parse PostgreSQL URI: {ex.Message}");
        connectionString = null; // Let it fallback to component method
    }
}

// Fallback to original environment variable assembly method
if (string.IsNullOrEmpty(connectionString))
{
    // Original individual environment variable assembly logic
    var supabaseUrl = Environment.GetEnvironmentVariable("SUPABASE_URL");
    var supabasePassword = Environment.GetEnvironmentVariable("SUPABASE_PASSWORD");
    var dbHost = Environment.GetEnvironmentVariable("SUPABASE_DB_HOST");
    var dbPort = Environment.GetEnvironmentVariable("SUPABASE_DB_PORT");

    var hostName = supabaseUrl?.Replace("https://", "").Replace("http://", "");
    var projectRef = hostName?.Split('.')[0];

    connectionString = $"Host={dbHost};Port={dbPort};Database=postgres;Username=postgres.{projectRef};Password={supabasePassword};SSL Mode=Require";
}

Step 2: Fix Compilation Errors

During implementation, encountered variable name conflict error:

CS0136: A local or parameter named 'port' cannot be declared in this scope

Solution: Rename variables to avoid conflicts

// Before
var port = uri.Port != -1 ? uri.Port : 5432;

// After  
var dbPortNum = uri.Port != -1 ? uri.Port : 5432;

Step 3: Fix Null Reference Warnings

// Add null check
var stackTrace = ex.StackTrace?.Split('\n').Take(5).ToArray() ?? Array.Empty<string>();

✅ Verification and Testing

Testing Steps

1. Deploy updated code to Railway
2. Verify diagnostic endpoint:

   curl "https://xxx.railway.app/api/diagnostic/connection-error"
   

3. Confirm database connection success:

   {
     "success": true,
     "message": "Connection successful",
     "data": {
       "connectionString": "Host=aws-1-ap-southeast-2.pooler.supabase.com;Port=5432;Database=postgres;Username=postgres.xxx;Password=xxx;SSL Mode=Require",
       "canConnect": true
     }
   }
   

4. Run complete API test suite:

   ./test-production-apis.sh
   

Final Test Results

🎉 Testing Complete!

✅ Response Chart API - All passed
✅ Customer Satisfaction Trend API - All passed  
✅ NPS API - All passed
✅ Service Attributes API - All passed
✅ Error Handling - Properly returns 400 errors
✅ CORS Configuration - Working normally

🚀 Prevention Measures and Best Practices

1. Environment Compatibility Design

// Recommended connection string handling pattern
public static class ConnectionStringHelper 
{
    public static string BuildConnectionString(IConfiguration configuration)
    {
        // 1. Try to get connection string directly
        var directConnectionString = configuration.GetConnectionString("DefaultConnection");
        
        // 2. Detect and convert URI format
        if (IsPostgreSqlUri(directConnectionString))
        {
            return ConvertUriToKeyValueFormat(directConnectionString);
        }
        
        // 3. Fallback to environment variable assembly
        return BuildFromEnvironmentVariables();
    }
}

2. Comprehensive Diagnostic Tools

[HttpGet("health/database")]
public async Task<IActionResult> DatabaseHealth()
{
    return Ok(new {
        connectionString = _context.Database.GetConnectionString(),
        canConnect = await _context.Database.CanConnectAsync(),
        providerName = _context.Database.ProviderName,
        environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")
    });
}

3. Standardize Local Environment Configuration

// appsettings.Development.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=testdb;Username=test;Password=test"
  }
}

4. Pre-deployment Checklist

• Confirm all environment variable formats
• Verify connection string conversion logic
• Test local and cloud environment compatibility
• Add detailed error logging and diagnostic endpoints

5. CI/CD Integration Recommendations

# Pre-deployment automated testing
- name: Test Database Connection
  run: |
    dotnet test --filter "Category=Integration"
    curl -f $DEPLOYED_URL/api/diagnostic/connection || exit 1

📚 Lessons Learned

1. Environment Differences are Hidden Killers

Even when using the same technology stack, configuration differences between local development and production environments can lead to completely different behaviors.

2. Importance of Diagnostic Tools

When unable to directly access production environment logs, built-in diagnostic endpoints are crucial tools for troubleshooting issues.

3. Connection String Format Standardization

Different platforms and tools may use different connection string formats, requiring code to handle these differences.

4. Progressive Problem Investigation

Gradually narrowing down the problem scope from basic configuration to specific errors is the most effective troubleshooting method.

🔗 Related Resources

• Npgsql Connection String Parameters
• Railway Environment Variables
• Supabase Connection Pooling
• Entity Framework Core Connection Strings

💡 Summary

The resolution process of this issue demonstrates common environment difference problems in cloud deployment. The key points are:

1. Systematic problem investigation methods
2. Comprehensive diagnostic tools
3. Understanding configuration differences across environments
4. Robust error handling and compatibility design

Hope this summary helps other developers locate and resolve similar issues more quickly when encountered.