Connection Configuration¶

The connection configuration is the most important part of your database setup. It defines how the MCP server connects to your specific database system. Each database type (db_type) requires a different connection configuration.

Connection Types Overview¶

The mcp_ohmy_sql server supports different connection types based on your database system:

{
    "connection": {
        "type": "connection_type_name",
        # ... type-specific configuration
    }
}

The type field determines which connection handler to use, and each type has its own specific configuration options.

Supported Connection Types¶

SQLAlchemy-based Connections ("sqlalchemy")

Used for traditional relational databases:

SQLite
PostgreSQL
MySQL/MariaDB
Microsoft SQL Server
Oracle

{
    "db_type": "postgresql",
    "connection": {
        "type": "sqlalchemy",
        "url": "postgresql://user:password@host:5432/database"
    }
}

AWS Redshift Connections ("aws_redshift")

Used for Amazon Redshift data warehouses:

{
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "my-cluster",
        "database": "warehouse"
    }
}

Future Connection Types

Planned support for additional database systems:

"mongodb" - MongoDB with SQL interface
"elasticsearch" - Elasticsearch SQL
"opensearch" - OpenSearch SQL
"snowflake" - Snowflake cloud database
"duckdb" - DuckDB analytical database

Database Type to Connection Type Mapping¶

Each db_type uses a specific connection type:

Database Type Mapping¶
Database Type	Connection Type	Documentation
`sqlite`	`sqlalchemy`	Relational Database Connection Configuration
`postgresql`	`sqlalchemy`	Relational Database Connection Configuration
`mysql`	`sqlalchemy`	Relational Database Connection Configuration
`mssql`	`sqlalchemy`	Relational Database Connection Configuration
`oracle`	`sqlalchemy`	Relational Database Connection Configuration
`aws_redshift`	`aws_redshift`	AWS Redshift Connection Configuration

Connection Configuration Structure¶

The connection object always starts with a type field, followed by type-specific configuration:

SQLAlchemy Connection Structure:

{
    "connection": {
        "type": "sqlalchemy",
        "url": "database_connection_string",
        "create_engine_kwargs": {
            # Optional SQLAlchemy engine parameters
        }
    }
}

AWS Redshift Connection Structure:

{
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        # Authentication method 1: Direct credentials
        "host": "cluster.region.redshift.amazonaws.com",
        "port": 5439,
        "database": "warehouse",
        "username": "user",
        "password": "password",

        # OR Authentication method 2: IAM-based
        "cluster_identifier": "my-cluster",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            "profile_name": "default"
        }
    }
}

Common Configuration Patterns¶

Development Environment:

Simple local databases for development:

{
    "identifier": "dev_db",
    "db_type": "sqlite",
    "connection": {
        "type": "sqlalchemy",
        "url": "sqlite:///./dev.db"
    }
}

Production Environment:

Production databases with connection pooling and optimization:

{
    "identifier": "prod_postgres",
    "db_type": "postgresql",
    "connection": {
        "type": "sqlalchemy",
        "url": "postgresql://user:password@prod-host:5432/database",
        "create_engine_kwargs": {
            "pool_size": 10,
            "max_overflow": 20,
            "pool_pre_ping": true,
            "pool_recycle": 3600
        }
    }
}

Cloud Data Warehouse:

AWS Redshift with IAM authentication:

{
    "identifier": "analytics_warehouse",
    "db_type": "aws_redshift",
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "analytics-cluster",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            "profile_name": "analytics"
        }
    }
}

Security Considerations¶

Credential Management:

Never store passwords directly in configuration files:

# ❌ Don't do this
{
    "url": "postgresql://user:mypassword123@host:5432/db"
}

# ✅ Use environment variables instead
{
    "url": "postgresql://user:${DB_PASSWORD}@host:5432/db"
}

Best Practices:

Use environment variables for sensitive data
Use IAM authentication when available (AWS, GCP, Azure)
Limit database user permissions to read-only when possible
Use SSL/TLS connections for remote databases
Store configuration files securely with proper file permissions

AWS Redshift IAM Example:

{
    "connection": {
        "type": "aws_redshift",
        "method": "sqlalchemy",
        "cluster_identifier": "my-cluster",
        "database": "warehouse",
        "boto_session_kwargs": {
            "region_name": "us-east-1",
            # Uses AWS credentials from environment, IAM roles, or profiles
            # No hardcoded passwords needed
        }
    }
}

Troubleshooting Connection Issues¶

Common Connection Problems:

Invalid connection strings: Check URL format and parameters
Network connectivity: Verify host, port, and firewall rules
Authentication failures: Check username, password, and permissions
Missing database drivers: Install required Python packages
SSL/TLS issues: Configure SSL settings properly

Testing Connections:

You can test your connection configuration using SQLAlchemy directly:

from sqlalchemy import create_engine

# Test your connection URL
engine = create_engine("postgresql://user:password@host:5432/db")
with engine.connect() as conn:
    result = conn.execute("SELECT 1")
    print(result.fetchone())

Error Messages:

The MCP server provides detailed error messages for connection failures:

File access errors: Check configuration file path and permissions
JSON parsing errors: Validate JSON syntax
Configuration validation errors: Check required fields and types
Database connection errors: Verify connection parameters and network access

Next Steps¶

Learn about specific connection types:

Relational Database Connection Configuration - SQLAlchemy-based database connections
AWS Redshift Connection Configuration - AWS Redshift connection configuration