Spaces:
Running
Running
File size: 5,247 Bytes
3f2d4aa | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | # PostgreSQL Connection String Formation - Complete Guide
## Current Status
Your application now supports **both** naming conventions for PostgreSQL configuration:
### Option 1: POSTGRES_* Variables (Recommended)
```bash
POSTGRES_URI=postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```
### Option 2: DB_* Variables (For Compatibility)
```bash
DB_PROTOCOL=postgresql+asyncpg
DB_USER=trans_owner
DB_PASSWORD=BookMyService7
DB_HOST=ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
DB_PORT=5432
DB_NAME=cuatrolabs
DB_SSLMODE=require
```
## Connection String Formation Logic
The `app/core/config.py` now handles connection string formation as follows:
### Step 1: Read Environment Variables
The config tries both naming conventions:
```python
POSTGRES_HOST = os.getenv("POSTGRES_HOST") or os.getenv("DB_HOST") or "localhost"
POSTGRES_PORT = os.getenv("POSTGRES_PORT") or os.getenv("DB_PORT") or "5432"
POSTGRES_DB = os.getenv("POSTGRES_DB") or os.getenv("DB_NAME") or "cuatrolabs"
POSTGRES_USER = os.getenv("POSTGRES_USER") or os.getenv("DB_USER") or "postgres"
POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD") or os.getenv("DB_PASSWORD") or ""
POSTGRES_SSL_MODE = os.getenv("POSTGRES_SSL_MODE") or os.getenv("DB_SSLMODE") or "disable"
```
### Step 2: Build Connection URI
If `POSTGRES_URI` is not provided, it builds one from components:
```python
from urllib.parse import quote_plus
# URL-encode the password (important for special characters)
encoded_password = quote_plus(POSTGRES_PASSWORD)
# Get protocol (supports DB_PROTOCOL for compatibility)
protocol = os.getenv("DB_PROTOCOL", "postgresql+asyncpg")
# Build the URI
POSTGRES_URI = f"{protocol}://{POSTGRES_USER}:{encoded_password}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}"
```
### Step 3: Result
For your deployment with DB_* variables:
```
postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```
## Key Features
### 1. URL Encoding
- Passwords are URL-encoded using `quote_plus()`
- Handles special characters like `@`, `#`, `%`, etc.
- Example: `Pass@word#123` becomes `Pass%40word%23123`
### 2. Dual Variable Support
- Checks `POSTGRES_*` variables first
- Falls back to `DB_*` variables if not found
- Maintains backward compatibility
### 3. Protocol Flexibility
- Supports `DB_PROTOCOL` environment variable
- Defaults to `postgresql+asyncpg`
- Can be changed to `postgresql` for sync drivers
### 4. SSL Mode Mapping
- Maps `DB_SSLMODE` to `POSTGRES_SSL_MODE`
- Supports: `disable`, `require`, `verify-full`
## Deployment Configuration
### For Your Current Deployment
Your deployment environment has these variables set:
- ✅ `DB_USER`
- ✅ `DB_PASSWORD`
- ✅ `DB_HOST`
- ✅ `DB_PORT`
- ✅ `DB_NAME` (or `POSTGRES_DB`)
- ✅ `DB_SSLMODE` (or `POSTGRES_SSL_MODE`)
**The config will now automatically build the connection string from these!**
### What Will Happen
When your app starts, you'll see:
```
[CONFIG] Built POSTGRES_URI from components
[CONFIG] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[CONFIG] Port: 5432
[CONFIG] Database: cuatrolabs
[CONFIG] User: trans_owner
[CONFIG] SSL Mode: require
```
Then:
```
======================================================================
[POSTGRES] Starting Database Connection
======================================================================
[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy...
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require
======================================================================
[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)
```
## Troubleshooting
### If Connection Still Fails
1. **Check the logs** for the exact connection string being used
2. **Verify SSL mode** - Neon requires `require` or higher
3. **Check firewall** - Ensure port 5432 is accessible
4. **Verify credentials** - User and password must be correct
5. **Test DNS** - Ensure the hostname resolves
### Common Issues
#### Issue: "Connection timeout"
- **Cause**: Firewall blocking port 5432
- **Solution**: Check network/firewall rules
#### Issue: "Authentication failed"
- **Cause**: Wrong username or password
- **Solution**: Verify `DB_USER` and `DB_PASSWORD`
#### Issue: "SSL required"
- **Cause**: Database requires SSL but `DB_SSLMODE=disable`
- **Solution**: Set `DB_SSLMODE=require`
#### Issue: "Database does not exist"
- **Cause**: Wrong database name
- **Solution**: Verify `DB_NAME` matches your database
## Testing Locally
To test the connection string formation:
```bash
source venv/bin/activate
python test_connection_string.py
python test_config_vars.py
```
## Next Steps
1. **Redeploy** your application with the updated code
2. **Monitor logs** during startup
3. **Look for** the connection string in logs (password will be masked)
4. **Verify** successful connection message
The connection string formation is now robust and should work with your deployment environment's `DB_*` variables!
|