> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Database Credentials
> Safely configuring PostgreSQL connection information
Sonamu uses PostgreSQL as its database. Database connection information is managed through environment variables to separate it from code and enable different DBs per environment.
Since Sonamu v2, PostgreSQL is officially supported, with migration support from MySQL.
## Required Environment Variables
Environment variables required for database connection.
```bash title=".env" theme={null}
# Database Host
DB_HOST=localhost
# Port (PostgreSQL default: 5432)
DB_PORT=5432
# Username
DB_USER=postgres
# Password
DB_PASSWORD=your-db-password
# Database Name
DATABASE_NAME=myproject_dev
```
`DB_PASSWORD` should never be hardcoded in your code! Manage it only through environment
variables.
## sonamu.config.ts Configuration
### Basic Configuration
```typescript title="sonamu.config.ts" theme={null}
import { defineConfig } from "sonamu";
export default defineConfig({
database: {
database: "pg", // PostgreSQL
name: process.env.DATABASE_NAME ?? "myproject",
defaultOptions: {
connection: {
host: process.env.DB_HOST || "0.0.0.0",
port: Number(process.env.DB_PORT) || 5432,
user: process.env.DB_USER || "postgres",
password: process.env.DB_PASSWORD,
},
},
},
// ...
});
```
Don't set a default value for `password`. Leave it as `undefined` when the environment variable is
missing to explicitly trigger an error.
### Connection Pool Configuration
```typescript theme={null}
database: {
database: "pg",
name: process.env.DATABASE_NAME ?? "myproject",
defaultOptions: {
connection: {
host: process.env.DB_HOST || "0.0.0.0",
port: Number(process.env.DB_PORT) || 5432,
user: process.env.DB_USER || "postgres",
password: process.env.DB_PASSWORD,
},
pool: {
min: 2,
max: 10,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
},
},
}
```
**Connection Pool Options:**
| Option | Default | Description |
| ------------------------- | ------- | --------------------------------- |
| `min` | 2 | Minimum connections |
| `max` | 10 | Maximum connections |
| `idleTimeoutMillis` | 10000 | Idle connection removal time (ms) |
| `connectionTimeoutMillis` | 0 | Connection wait time (ms) |
For high-traffic production environments, increase `max` to 20-50.
## Environment-Specific Configuration
```bash title=".env.development" theme={null}
# Local PostgreSQL
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your-dev-password
DATABASE_NAME=myproject_dev
```
**Running PostgreSQL with Docker Compose:**
```yaml title="docker-compose.yml" theme={null}
version: '3.8'
services:
postgres:
image: postgres:16
ports:
- "5432:5432"
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: your-dev-password
POSTGRES_DB: myproject_dev
volumes:
- postgres_data:/var/lib/postgresql/data
volumes:
postgres_data:
```
```bash theme={null}
# Start
docker-compose up -d
# Stop
docker-compose down
```
```bash title=".env.staging" theme={null}
# Remote database
DB_HOST=staging-db.mycompany.com
DB_PORT=5432
DB_USER=myproject_staging
DB_PASSWORD=your-staging-password # At least 32 characters strong password
DATABASE_NAME=myproject_staging
```
**Characteristics:**
* Dedicated DB user (read/write permissions only)
* SSL connection recommended
* Similar data volume to production
**SSL Configuration (optional):**
```typescript theme={null}
connection: {
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
ssl: {
rejectUnauthorized: false, // Self-signed certificate
},
}
```
```bash title=".env.production" theme={null}
# High-availability database
DB_HOST=prod-db-primary.mycompany.com
DB_PORT=5432
DB_USER=myproject_prod
DB_PASSWORD=your-production-password # At least 64 characters very strong password
DATABASE_NAME=myproject_prod
```
**Connection Pool Optimization:**
```typescript theme={null}
pool: {
min: 5,
max: 50,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
}
```
**Read Replica Configuration (optional):**
```typescript theme={null}
database: {
database: "pg",
name: process.env.DATABASE_NAME ?? "myproject",
defaultOptions: {
connection: {
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
},
},
environments: {
production: {
connection: {
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
},
pool: {
min: 5,
max: 50,
},
},
// Read-only replica (Slave DB)
production_slave: {
connection: {
host: process.env.DB_READ_HOST,
port: Number(process.env.DB_READ_PORT),
user: process.env.DB_READ_USER,
password: process.env.DB_READ_PASSWORD,
},
pool: {
min: 3,
max: 30,
},
},
},
}
```
Sonamu automatically recognizes the `environments.production_slave` configuration and uses it for read-only queries. See [Database Configuration](/en/configuration/sonamu-config/database) for details.
## Creating PostgreSQL User
```bash theme={null}
# Local
psql -U postgres
# Remote
psql -h db.example.com -U postgres
```
`sql CREATE DATABASE myproject_dev; `
```sql theme={null}
-- Create user
CREATE USER myproject_user WITH PASSWORD 'your-secure-password';
-- Grant database permissions
GRANT ALL PRIVILEGES ON DATABASE myproject_dev TO myproject_user;
-- Grant schema permissions
\c myproject_dev
GRANT ALL PRIVILEGES ON SCHEMA public TO myproject_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myproject_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myproject_user;
```
```bash theme={null}
psql -h localhost -U myproject_user -d myproject_dev
```
Success if connected after entering password!
## Security Best Practices
### Password Requirements
**Development Environment:**
* At least 8 characters
* Mix of letters and numbers
**Staging/Production:**
* At least 32 characters
* Mix of uppercase, lowercase, numbers, and special characters
* Random string not found in dictionary
### Password Generation
```bash theme={null}
# 32-character random password
openssl rand -base64 24
# Or
pwgen -s 32 1
```
**Example:**
```bash theme={null}
DB_PASSWORD=your-32-character-secure-password
```
### Permission Separation
```sql theme={null}
-- ✅ Application user (read/write only)
CREATE USER app_user WITH PASSWORD 'your-app-password';
GRANT CONNECT ON DATABASE myproject TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- ✅ Read-only user (analytics/reporting)
CREATE USER readonly_user WITH PASSWORD 'your-readonly-password';
GRANT CONNECT ON DATABASE myproject TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- ❌ Never use SUPERUSER permission
-- CREATE USER admin WITH SUPERUSER;
```
Never give SUPERUSER permissions to production applications! This is a major security risk.
### SSL Connection Configuration
**Required environments:**
* Production (required)
* Staging (recommended)
* Development (optional)
```typescript theme={null}
connection: {
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
ssl: process.env.NODE_ENV === 'production' ? {
rejectUnauthorized: true,
ca: fs.readFileSync('/path/to/ca-certificate.crt').toString(),
} : false,
}
```
### AWS RDS SSL
```bash theme={null}
# Download AWS RDS CA certificate
wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
# Add path to .env
DB_SSL_CA=/path/to/global-bundle.pem
```
```typescript theme={null}
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync(process.env.DB_SSL_CA!).toString(),
}
```
### Using Vault (Recommended)
```bash theme={null}
# Get password from HashiCorp Vault
export DB_PASSWORD=$(vault kv get -field=password secret/database/prod)
```
### AWS Secrets Manager
```typescript theme={null}
import { SecretsManagerClient, GetSecretValueCommand } from "@aws-sdk/client-secrets-manager";
const client = new SecretsManagerClient({ region: "ap-northeast-2" });
const response = await client.send(
new GetSecretValueCommand({ SecretId: "prod/database/credentials" }),
);
const secrets = JSON.parse(response.SecretString!);
export default defineConfig({
database: {
defaultOptions: {
connection: {
host: secrets.host,
port: secrets.port,
user: secrets.username,
password: secrets.password,
},
},
},
});
```
## Connection Testing
```bash CLI theme={null}
# Direct connection test with psql
psql "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DATABASE_NAME"
# On successful connection
# myproject_dev=>
```
```typescript Node.js theme={null}
// Programmatic testing
import pg from "pg";
const client = new pg.Client({
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DATABASE_NAME,
});
try {
await client.connect();
const result = await client.query("SELECT NOW()");
console.log("✅ Connection successful:", result.rows[0]);
} catch (error) {
console.error("❌ Connection failed:", error);
} finally {
await client.end();
}
```
```typescript Sonamu theme={null}
// Verify connection in Sonamu
import { db } from "sonamu";
// Test connection by executing a query
const result = await db.query("SELECT NOW() as current_time");
console.log("✅ DB connection successful:", result.rows[0]);
```
## Troubleshooting
**Symptoms:**
```
Error: connect ECONNREFUSED 127.0.0.1:5432
```
**Causes:**
1. PostgreSQL is not running
2. Incorrect host/port
3. Firewall blocking
**Solution:**
```bash theme={null}
# 1. Check PostgreSQL is running
sudo systemctl status postgresql
# Or (macOS)
brew services list | grep postgresql
# 2. Start PostgreSQL
sudo systemctl start postgresql
# Or (macOS)
brew services start postgresql
# 3. Check port
netstat -an | grep 5432
# 4. Check .env file
cat .env | grep DB_
```
**Symptoms:**
```
error: password authentication failed for user "myproject_user"
```
**Causes:**
1. Incorrect username/password
2. User doesn't exist
3. `pg_hba.conf` configuration issue
**Solution:**
```bash theme={null}
# 1. Check user
psql -U postgres -c "\du"
# 2. Reset password
psql -U postgres
```
```sql theme={null}
ALTER USER myproject_user WITH PASSWORD 'your-new-password';
```
```bash theme={null}
# 3. Check pg_hba.conf (Linux)
sudo cat /etc/postgresql/16/main/pg_hba.conf
# Verify local connection allowed
# local all all trust
# host all all 127.0.0.1/32 md5
```
**Symptoms:**
```
Error: Connection timeout
```
**Causes:**
1. Network issues
2. DB server overloaded
3. Connection pool exhausted
**Solution:**
```typescript theme={null}
// 1. Increase timeout
connection: {
connectionTimeoutMillis: 10000, // 10 seconds
}
// 2. Increase connection pool size
pool: {
max: 20,
}
// 3. Check network
// ping DB server
// telnet db.example.com 5432
```
**Symptoms:**
```
FATAL: sorry, too many clients already
```
**Causes:**
* Exceeded PostgreSQL's `max_connections` limit
**Solution:**
```sql theme={null}
-- Check current connection count
SELECT count(*) FROM pg_stat_activity;
-- Check max_connections
SHOW max_connections;
-- Increase max_connections (postgresql.conf)
-- max_connections = 200
```
```typescript theme={null}
// Limit application connection pool
pool: {
max: 10, // Keep well below max_connections
}
```
Increasing `max_connections` arbitrarily increases memory usage. Adjust the connection pool size first.
## Performance Optimization
### Connection Pool Tuning
```typescript theme={null}
// Configuration based on traffic patterns
// Low traffic (development)
pool: {
min: 2,
max: 10,
}
// Medium traffic (staging)
pool: {
min: 5,
max: 20,
}
// High traffic (production)
pool: {
min: 10,
max: 50,
}
```
**Calculation formula:**
```
max = (server CPU cores × 2) + number of disks
```
Example: 8-core CPU, 1 SSD → max = (8 × 2) + 1 = 17
### Query Performance Monitoring
```typescript theme={null}
// Slow query logging
database: {
defaultOptions: {
connection: {
// ...
},
// Log queries taking more than 1 second
log: {
warn(message) {
if (message.includes('slow query')) {
console.warn('🐌', message);
}
},
},
},
}
```
## Next Steps
Safely configure external API keys
Learn environment variable basics
Manage database schemas
Check detailed database settings