Database

This covers database connection settings. Sonamu uses PostgreSQL as the default database and supports different database configurations for each environment.

Basic Structure

import { defineConfig } from "sonamu";

export default defineConfig({
  database: {
    database: "pg",
    name: "mydb",
    defaultOptions: {
      connection: {
        host: "localhost",
        port: 5432,
        user: "postgres",
        password: "password",
      },
    },
    environments: {
      development: {
        /* ... */
      },
      production: {
        /* ... */
      },
    },
  },
  // ...
});

Specifies the PostgreSQL driver to use. Type: "pg" | "pgnative" (optional) Default: "pg"

export default defineConfig({
  database: {
    database: "pg", // Use default pg module
    // ...
  },
});

pg vs pgnative

"pg": PostgreSQL driver implemented in pure JavaScript (recommended)
- Install: pnpm add pg
- Cross-platform support
- Sufficient performance for most cases
- Sonamu automatically configures Node.js TCP socket-level keepAlive (keepAlive: true, keepAliveInitialDelayMillis: 10000)
"pgnative": Native driver using C bindings
- Install: pnpm add pg-native
- Faster performance (especially for large data processing)
- Requires compilation, platform-dependent
- Sonamu automatically converts the connection object to a libpq connection string and includes TCP keepAlive parameters (keepalives=1, keepalives_idle=10, keepalives_interval=10, keepalives_count=5)

Use "pg" unless you have specific performance requirements. Installation and deployment are simpler.

name

Specifies the database name. Type: string (required)

export default defineConfig({
  database: {
    database: "pg",
    name: "ecommerce", // Name of database to connect to
    // ...
  },
});

Using environment variables:

export default defineConfig({
  database: {
    database: "pg",
    name: process.env.DATABASE_NAME ?? "mydb",
    // ...
  },
});

defaultOptions

Database settings that apply to all environments. Uses Knex configuration options. Type: DatabaseConfig (required)

type DatabaseConfig = Omit<Knex.Config, "connection"> & {
  connection?: Knex.PgConnectionConfig;
};

connection Settings

Sets database connection information.

export default defineConfig({
  database: {
    database: "pg",
    name: "mydb",
    defaultOptions: {
      connection: {
        host: "localhost", // Database host
        port: 5432, // PostgreSQL port
        user: "postgres", // Username
        password: "password", // Password
      },
    },
  },
});

Managing securely with environment variables:

export default defineConfig({
  database: {
    database: "pg",
    name: process.env.DATABASE_NAME ?? "mydb",
    defaultOptions: {
      connection: {
        host: process.env.DB_HOST ?? "localhost",
        port: Number(process.env.DB_PORT ?? 5432),
        user: process.env.DB_USER ?? "postgres",
        password: process.env.DB_PASSWORD,
      },
    },
  },
});

Always manage database passwords with environment variables. Never write them directly in code!

Additional Knex Options

You can set various Knex options besides connection:

export default defineConfig({
  database: {
    database: "pg",
    name: "mydb",
    defaultOptions: {
      connection: {
        /* ... */
      },

      // Connection pool settings
      pool: {
        min: 2,
        max: 10,
      },

      // Query timeout
      acquireConnectionTimeout: 10000,

      // Debug mode
      debug: process.env.NODE_ENV === "development",
    },
  },
});

Sonamu automatically applies the following pool options internally. These are applied even without explicit user configuration, and will override user-provided values:

Option	Default	Description
`propagateCreateError`	`false`	Does not propagate connection creation errors to the entire pool
`idleTimeoutMillis`	`10000`	Idle connections are automatically released after 10 seconds
`reapIntervalMillis`	`1000`	Idle connection cleanup interval is set to 1 second
`acquireTimeoutMillis`	`30000`	Connection acquisition wait time is limited to 30 seconds
`createTimeoutMillis`	`30000`	Connection creation wait time is limited to 30 seconds

Additionally, the pool’s afterCreate callback registers socket-level keepAlive settings and error handlers for each connection. Connections that encounter errors are automatically excluded from the pool.

environments

Specifies different database settings for each environment. Overrides defaultOptions. Type: (optional)

environments?: {
  development?: DatabaseConfig;
  development_slave?: DatabaseConfig;
  production?: DatabaseConfig;
  production_slave?: DatabaseConfig;
  fixture?: DatabaseConfig;
  test?: DatabaseConfig;
}

Environment-Specific Configuration Example

export default defineConfig({
  database: {
    database: "pg",
    name: "mydb",
    defaultOptions: {
      connection: {
        host: "localhost",
        port: 5432,
        user: "postgres",
        password: "dev-password",
      },
    },
    environments: {
      // Development environment (uses defaults)
      development: {
        connection: {
          host: "localhost",
          port: 5432,
          user: "dev_user",
          password: "dev_password",
        },
      },

      // Production environment
      production: {
        connection: {
          host: process.env.PROD_DB_HOST,
          port: Number(process.env.PROD_DB_PORT),
          user: process.env.PROD_DB_USER,
          password: process.env.PROD_DB_PASSWORD,
        },
        pool: {
          min: 5,
          max: 30, // Larger pool for production
        },
      },

      // Test environment
      test: {
        connection: {
          host: "localhost",
          port: 5432,
          user: "test_user",
          password: "test_password",
          database: "mydb_test", // Separate test DB
        },
      },
    },
  },
});

Determining Current Environment

Sonamu determines the current environment in this order:

NODE_ENV environment variable
Default: development

# Development environment
NODE_ENV=development pnpm dev

# Production environment
NODE_ENV=production pnpm start

# Test environment
NODE_ENV=test pnpm test

Slave DB Configuration

When using read-only replicas (slave):

export default defineConfig({
  database: {
    database: "pg",
    name: "mydb",
    defaultOptions: {
      /* ... */
    },
    environments: {
      production: {
        connection: {
          host: "master.db.example.com",
          port: 5432,
          user: "master_user",
          password: process.env.MASTER_DB_PASSWORD,
        },
      },
      production_slave: {
        connection: {
          host: "slave.db.example.com",
          port: 5432,
          user: "slave_user",
          password: process.env.SLAVE_DB_PASSWORD,
        },
      },
    },
  },
});

Slave DB is used for read-only queries to distribute load from the master DB.

Practical Examples

Basic Local Development Setup

import { defineConfig } from "sonamu";

export default defineConfig({
  database: {
    database: "pg",
    name: "myapp",
    defaultOptions: {
      connection: {
        host: "localhost",
        port: 5432,
        user: "postgres",
        password: "postgres",
      },
    },
  },
  // ...
});

Using Environment Variables (Recommended)

import { defineConfig } from "sonamu";

export default defineConfig({
  database: {
    database: "pg",
    name: process.env.DATABASE_NAME ?? "myapp",
    defaultOptions: {
      connection: {
        host: process.env.DB_HOST ?? "localhost",
        port: Number(process.env.DB_PORT ?? 5432),
        user: process.env.DB_USER ?? "postgres",
        password: process.env.DB_PASSWORD,
      },
      pool: {
        min: 2,
        max: 10,
      },
    },
  },
  // ...
});

.env file:

DATABASE_NAME=myapp
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your-password-here

Multi-Environment Configuration

import { defineConfig } from "sonamu";

const isDev = process.env.NODE_ENV === "development";
const isProd = process.env.NODE_ENV === "production";

export default defineConfig({
  database: {
    database: "pg",
    name: "ecommerce",
    defaultOptions: {
      connection: {
        host: process.env.DB_HOST ?? "localhost",
        port: Number(process.env.DB_PORT ?? 5432),
        user: process.env.DB_USER ?? "postgres",
        password: process.env.DB_PASSWORD,
      },
      debug: isDev,
    },
    environments: {
      development: {
        connection: {
          host: "localhost",
          port: 5432,
          user: "dev_user",
          password: "dev_password",
          database: "ecommerce_dev",
        },
        pool: {
          min: 2,
          max: 10,
        },
      },

      production: {
        connection: {
          host: process.env.PROD_DB_HOST,
          port: Number(process.env.PROD_DB_PORT),
          user: process.env.PROD_DB_USER,
          password: process.env.PROD_DB_PASSWORD,
          database: "ecommerce_prod",
        },
        pool: {
          min: 5,
          max: 30,
        },
        acquireConnectionTimeout: 60000,
      },

      production_slave: {
        connection: {
          host: process.env.PROD_SLAVE_DB_HOST,
          port: Number(process.env.PROD_SLAVE_DB_PORT),
          user: process.env.PROD_SLAVE_DB_USER,
          password: process.env.PROD_SLAVE_DB_PASSWORD,
          database: "ecommerce_prod",
        },
        pool: {
          min: 3,
          max: 20,
        },
      },

      test: {
        connection: {
          host: "localhost",
          port: 5432,
          user: "test_user",
          password: "test_password",
          database: "ecommerce_test",
        },
        pool: {
          min: 1,
          max: 5,
        },
      },

      fixture: {
        connection: {
          host: "localhost",
          port: 5432,
          user: "fixture_user",
          password: "fixture_password",
          database: "ecommerce_fixture",
        },
      },
    },
  },
  // ...
});

Docker Compose Environment

import { defineConfig } from "sonamu";

export default defineConfig({
  database: {
    database: "pg",
    name: "myapp",
    defaultOptions: {
      connection: {
        // Use Docker Compose service name
        host: process.env.DB_HOST ?? "postgres",
        port: Number(process.env.DB_PORT ?? 5432),
        user: process.env.DB_USER ?? "postgres",
        password: process.env.DB_PASSWORD ?? "postgres",
      },
    },
  },
  // ...
});

docker-compose.yml:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"

  api:
    build: .
    environment:
      DB_HOST: postgres # Service name
      DB_PORT: 5432
      DB_USER: postgres
      DB_PASSWORD: postgres
      DATABASE_NAME: myapp

Connection Testing

To verify your database settings are correct:

# Start development server
pnpm dev

# Displayed on console when connection succeeds
# ✅ Database connected: myapp

Common errors on connection failure:

# 1. Password error
❌ password authentication failed for user "postgres"

# 2. Host connection failure
❌ connect ECONNREFUSED 127.0.0.1:5432

# 3. Database doesn't exist
❌ database "myapp" does not exist

If connection issues occur, check that PostgreSQL is running:

# macOS
brew services list

# Linux

systemctl status postgresql

# Docker

docker ps

Important Notes

1. Password Security

// ❌ Bad example: Writing password directly in code
export default defineConfig({
  database: {
    defaultOptions: {
      connection: {
        password: "super-secret-password",  // Never do this!
      },
    },
  },
});

// ✅ Good example: Use environment variables
export default defineConfig({
  database: {
    defaultOptions: {
      connection: {
        password: process.env.DB_PASSWORD,
      },
    },
  },
});

2. Port Number Type

// ❌ Bad example: Port as string
port: process.env.DB_PORT,  // "5432" (string)

// ✅ Good example: Convert to number
port: Number(process.env.DB_PORT ?? 5432),  // 5432 (number)

3. Connection Pool Size

// Development: Small pool
pool: { min: 2, max: 10 }

// Production: Adjust for traffic
pool: { min: 5, max: 30 }

// Test: Minimal
pool: { min: 1, max: 5 }

Next Steps

After completing database settings:

server - Server options and plugin settings
database/migrations - Manage schema with migrations
troubleshooting - Connection problem troubleshooting

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

Database

Basic Structure

database

pg vs pgnative

name

defaultOptions

connection Settings

Additional Knex Options

environments

Environment-Specific Configuration Example

Determining Current Environment

Slave DB Configuration

Practical Examples

Basic Local Development Setup

Using Environment Variables (Recommended)

Multi-Environment Configuration

Docker Compose Environment

Connection Testing

Important Notes

1. Password Security

2. Port Number Type

3. Connection Pool Size

Next Steps

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Basic Structure

​database

​pg vs pgnative

​name

​defaultOptions

​connection Settings

​Additional Knex Options

​environments

​Environment-Specific Configuration Example

​Determining Current Environment

​Slave DB Configuration

​Practical Examples

​Basic Local Development Setup

​Using Environment Variables (Recommended)

​Multi-Environment Configuration

​Docker Compose Environment

​Connection Testing

​Important Notes

​1. Password Security

​2. Port Number Type

​3. Connection Pool Size

​Next Steps

Basic Structure

database

pg vs pgnative

name

defaultOptions

connection Settings

Additional Knex Options

environments

Environment-Specific Configuration Example

Determining Current Environment

Slave DB Configuration

Practical Examples

Basic Local Development Setup

Using Environment Variables (Recommended)

Multi-Environment Configuration

Docker Compose Environment

Connection Testing

Important Notes

1. Password Security

2. Port Number Type

3. Connection Pool Size

Next Steps