Database Configuration

OPS Intermediate

Configure database connections for EZ-Console.

Overview

EZ-Console supports multiple database drivers: SQLite (default), MySQL, PostgreSQL, and ClickHouse. This guide covers configuration for each database type.

Supported Databases

• SQLite: Default, suitable for development and small deployments
• MySQL: Production-ready, widely used
• PostgreSQL: Advanced features, JSON support
• ClickHouse: Analytics and time-series data

SQLite Configuration

Basic Configuration

database:
  driver: "sqlite"
  path: "ez-console.db"
  table_prefix: "t_"
  slow_threshold: "3s"

Configuration Options

• driver: "sqlite" (required)
• path: Database file path (default: {app-name}.db)
• table_prefix: Table name prefix (default: "t_")
• slow_threshold: Slow query threshold (default: "3s")

Example

database:
  driver: "sqlite"
  path: "./data/myapp.db"
  table_prefix: "app_"
  slow_threshold: "1s"

Command-Line

./server \
  --global.encrypt-key=your-key \
  --database.driver=sqlite \
  --database.path=./data/myapp.db

MySQL Configuration

Basic Configuration

database:
  driver: "mysql"
  host: "localhost"
  port: 3306
  username: "root"
  password: "password"
  schema: "ez_console"
  charset: "utf8mb4"
  collation: "utf8mb4_unicode_ci"
  max_open_connections: 100
  max_idle_connections: 10
  max_connection_life_time: "30s"
  table_prefix: "t_"
  slow_threshold: "3s"

Configuration Options

• driver: "mysql" (required)
• host: Database host (default: "localhost")
• port: Database port (default: 3306)
• username: Database username (default: "root")
• password: Database password (required)
• schema: Database name (required)
• charset: Character set (default: "utf8mb4")
• collation: Collation (default: "utf8mb4_unicode_ci")
• max_open_connections: Maximum open connections (default: 100)
• max_idle_connections: Maximum idle connections (default: 10)
• max_connection_life_time: Maximum connection lifetime (default: "30s")
• table_prefix: Table name prefix (default: "t_")
• slow_threshold: Slow query threshold (default: "3s")

Production Setup

database:
  driver: "mysql"
  host: "db.example.com"
  port: 3306
  username: "myapp"
  password: "${DATABASE_PASSWORD}"
  schema: "myapp_production"
  charset: "utf8mb4"
  collation: "utf8mb4_unicode_ci"
  max_open_connections: 200
  max_idle_connections: 20
  max_connection_life_time: "5m"
  table_prefix: "t_"
  slow_threshold: "1s"

Command-Line

./server \
  --global.encrypt-key=your-key \
  --database.driver=mysql \
  --database.host=localhost \
  --database.username=root \
  --database.password=password \
  --database.schema=ez_console

Creating Database

CREATE DATABASE ez_console CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'%' IDENTIFIED BY 'secure-password';
GRANT ALL PRIVILEGES ON ez_console.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

PostgreSQL Configuration

Basic Configuration

database:
  driver: "postgres"
  host: "localhost"
  port: 5432
  username: "postgres"
  password: "password"
  schema: "ez_console"
  max_open_connections: 100
  max_idle_connections: 10
  max_connection_life_time: "30s"
  table_prefix: "t_"
  slow_threshold: "3s"

Configuration Options

• driver: "postgres" (required)
• host: Database host (default: "localhost")
• port: Database port (default: 5432)
• username: Database username (default: "postgres")
• password: Database password (required)
• schema: Database name (required)
• max_open_connections: Maximum open connections (default: 100)
• max_idle_connections: Maximum idle connections (default: 10)
• max_connection_life_time: Maximum connection lifetime (default: "30s")
• table_prefix: Table name prefix (default: "t_")
• slow_threshold: Slow query threshold (default: "3s")

Production Setup

database:
  driver: "postgres"
  host: "db.example.com"
  port: 5432
  username: "myapp"
  password: "${DATABASE_PASSWORD}"
  schema: "myapp_production"
  max_open_connections: 200
  max_idle_connections: 20
  max_connection_life_time: "5m"
  table_prefix: "t_"
  slow_threshold: "1s"

Command-Line

./server \
  --global.encrypt-key=your-key \
  --database.driver=postgres \
  --database.host=localhost \
  --database.username=postgres \
  --database.password=password \
  --database.schema=ez_console

Creating Database

CREATE DATABASE ez_console WITH ENCODING 'UTF8';
CREATE USER myapp WITH PASSWORD 'secure-password';
GRANT ALL PRIVILEGES ON DATABASE ez_console TO myapp;

ClickHouse Configuration

Basic Configuration

database:
  driver: "clickhouse"
  host: "localhost"
  port: 9000
  username: "default"
  password: "password"
  schema: "ez_console"
  read_timeout: "10s"
  dial_timeout: "10s"
  max_execution_time: "60s"
  enable_compression: true
  max_open_connections: 100
  max_idle_connections: 2
  max_connection_life_time: "30s"
  table_prefix: "t_"
  slow_threshold: "3s"

Configuration Options

• driver: "clickhouse" (required)
• host: Database host (default: "localhost")
• port: Database port (default: 9000)
• username: Database username (default: "default")
• password: Database password (required)
• schema: Database name (required)
• read_timeout: Read timeout (default: "10s")
• dial_timeout: Dial timeout (default: "10s")
• max_execution_time: Maximum execution time (default: "60s")
• enable_compression: Enable compression (default: true)
• max_open_connections: Maximum open connections (default: 100)
• max_idle_connections: Maximum idle connections (default: 2)
• max_connection_life_time: Maximum connection lifetime (default: "30s")
• table_prefix: Table name prefix (default: "t_")
• slow_threshold: Slow query threshold (default: "3s")

Connection Pooling

Tuning Connection Pool

For high-traffic applications, adjust connection pool settings:

database:
  max_open_connections: 200      # Increase for high traffic
  max_idle_connections: 20       # Keep some connections ready
  max_connection_life_time: "5m" # Recycle connections periodically

Best Practices

1. max_open_connections: Set based on expected concurrent requests
2. max_idle_connections: Keep 10-20% of max_open_connections
3. max_connection_life_time: Recycle connections every 5-10 minutes

Environment Variables

# Database configuration
export DATABASE_DRIVER=mysql
export DATABASE_HOST=localhost
export DATABASE_PORT=3306
export DATABASE_USERNAME=root
export DATABASE_PASSWORD=password
export DATABASE_SCHEMA=ez_console
export DATABASE_MAX_OPEN_CONNECTIONS=100
export DATABASE_MAX_IDLE_CONNECTIONS=10

Troubleshooting

Connection Timeout

Symptom: Database connection timeout

Solutions:

1. Check network connectivity
2. Verify firewall rules
3. Increase timeout settings
4. Check database server status

Too Many Connections

Symptom: too many connections error

Solutions:

1. Reduce max_open_connections
2. Increase database max_connections setting
3. Use connection pooling properly

Slow Queries

Symptom: Slow API responses

Solutions:

1. Enable slow query logging: slow_threshold: "1s"
2. Add database indexes
3. Optimize queries
4. Check database server resources

Migration

Database migrations run automatically on server startup. Ensure:

1. Database user has CREATE/ALTER permissions
2. Backup database before migration
3. Test migrations in staging first

Related Topics

• Configuration Guide - General configuration
• Database Migration - Migration guide
• Troubleshooting - Common issues

---

Need help? Ask in GitHub Discussions.