Database Credentials

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.

.env

# 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

sonamu.config.ts

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

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

Local Development
Staging
Production

.env.development

# 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:

docker-compose.yml

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:

# Start
docker-compose up -d

# Stop
docker-compose down

.env.staging

# 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):

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
  },
}

.env.production

# 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:

pool: {
  min: 5,
  max: 50,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
}

Read Replica Configuration (optional):

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 for details.

Creating PostgreSQL User

Connect to PostgreSQL

# Local
psql -U postgres

# Remote
psql -h db.example.com -U postgres

Create Database

CREATE DATABASE myproject_dev;

Create User and Grant Permissions

-- 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;

Test Connection

psql -h localhost -U myproject_user -d myproject_dev

Success if connected after entering password!

Security Best Practices

Strong Passwords

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

# 32-character random password
openssl rand -base64 24

# Or
pwgen -s 32 1

Example:

DB_PASSWORD=your-32-character-secure-password

Principle of Least Privilege

Permission Separation

-- ✅ 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/TLS Encryption

SSL Connection Configuration

Required environments:

Production (required)
Staging (recommended)
Development (optional)

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

# 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

ssl: {
  rejectUnauthorized: true,
  ca: fs.readFileSync(process.env.DB_SSL_CA!).toString(),
}

Connection Information Encryption

Using Vault (Recommended)

# Get password from HashiCorp Vault
export DB_PASSWORD=$(vault kv get -field=password secret/database/prod)

AWS Secrets Manager

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

# Direct connection test with psql
psql "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DATABASE_NAME"

# On successful connection
# myproject_dev=>

Troubleshooting

Connection Refused (ECONNREFUSED)

Symptoms:

Error: connect ECONNREFUSED 127.0.0.1:5432

Causes:

PostgreSQL is not running
Incorrect host/port
Firewall blocking

Solution:

# 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_

Authentication Failed

Symptoms:

error: password authentication failed for user "myproject_user"

Causes:

Incorrect username/password
User doesn’t exist
pg_hba.conf configuration issue

Solution:

# 1. Check user
psql -U postgres -c "\du"

# 2. Reset password
psql -U postgres

ALTER USER myproject_user WITH PASSWORD 'your-new-password';

# 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

Connection Timeout

Symptoms:

Error: Connection timeout

Causes:

Network issues
DB server overloaded
Connection pool exhausted

Solution:

// 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

Too Many Connections

Symptoms:

FATAL: sorry, too many clients already

Causes:

Exceeded PostgreSQL’s max_connections limit

Solution:

-- Check current connection count
SELECT count(*) FROM pg_stat_activity;

-- Check max_connections
SHOW max_connections;

-- Increase max_connections (postgresql.conf)
-- max_connections = 200

// 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

// 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

// 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

API Key Management

Safely configure external API keys

.env Setup

Learn environment variable basics

Migrations

Manage database schemas

Database Configuration

Check detailed database settings

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

Required Environment Variables

sonamu.config.ts Configuration

Basic Configuration

Connection Pool Configuration

Environment-Specific Configuration

Creating PostgreSQL User

Security Best Practices

Password Requirements

Password Generation

Permission Separation

SSL Connection Configuration

AWS RDS SSL

Using Vault (Recommended)

AWS Secrets Manager

Connection Testing

Troubleshooting

Performance Optimization

Connection Pool Tuning

Query Performance Monitoring

Next Steps

API Key Management

.env Setup

Migrations

Database Configuration

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Required Environment Variables

​sonamu.config.ts Configuration

​Basic Configuration

​Connection Pool Configuration

​Environment-Specific Configuration

​Creating PostgreSQL User

​Security Best Practices

​Password Requirements

​Password Generation

​Permission Separation

​SSL Connection Configuration

​AWS RDS SSL

​Using Vault (Recommended)

​AWS Secrets Manager

​Connection Testing

​Troubleshooting

​Performance Optimization

​Connection Pool Tuning

​Query Performance Monitoring

​Next Steps

API Key Management

.env Setup

Migrations

Database Configuration

Required Environment Variables

sonamu.config.ts Configuration

Basic Configuration

Connection Pool Configuration

Environment-Specific Configuration

Creating PostgreSQL User

Security Best Practices

Password Requirements

Password Generation

Permission Separation

SSL Connection Configuration

AWS RDS SSL

Using Vault (Recommended)

AWS Secrets Manager

Connection Testing

Troubleshooting

Performance Optimization

Connection Pool Tuning

Query Performance Monitoring

Next Steps