Configuration Guide

This page provides a comprehensive guide to configuring Legion Query Runners for various database systems.

Configuration Structure

Each query runner has its own specific configuration options, but they all follow a common pattern:

from legion_query_runner.query_runner import QueryRunner

# Create a configuration dictionary
config = {
    "option1": "value1",
    "option2": "value2",
    # ...other options
}

# Initialize the query runner with a type and configuration
runner = QueryRunner('database_type', config)

Common Settings

While each database has its own specific settings, many share common configuration options:

Setting	Description
Connection Parameters	Host, port, database name
Authentication	Username, password, or credentials
SSL Configuration	SSL mode, certificates, keys
SSH Tunnel	Bastion host connection settings
Performance Settings	Connection pool size, timeouts
Query Limits	Row limits, byte limits

Environment Variables and Secrets Management

For security best practices, avoid hardcoding sensitive information like passwords in your code. Instead:

import os

# Use environment variables for sensitive information
config = {
    "user": "database_user",
    "password": os.environ.get("DB_PASSWORD"),
    "host": os.environ.get("DB_HOST", "localhost")
}

Connection Testing

Always test your connection before executing queries:

try:
    runner.test_connection()
    print("Connection successful!")
except Exception as e:
    print(f"Connection failed: {e}")

Handling Connection Errors

Different databases may throw different types of connection errors. Here's a generalized way to handle them:

try:
    results = runner.run_query("SELECT * FROM users")
except Exception as e:
    if "timeout" in str(e).lower():
        print("Connection timed out. Check network or firewall settings.")
    elif "authentication" in str(e).lower():
        print("Authentication failed. Check credentials.")
    else:
        print(f"Query failed: {e}")

SSH Tunnel Configuration

For databases behind firewalls, you can use SSH tunneling through a bastion host:

config = {
    # Regular database connection settings
    "host": "internal-db.example.com",
    "port": 5432,
    "user": "dbuser",
    "password": "dbpass",
    "dbname": "mydb",
    
    # SSH tunnel settings
    "ssh_tunnel_enabled": True,
    "ssh_host": "bastion.example.com",
    "ssh_port": 22,
    "ssh_username": "ssh_user"
}

# The SSH tunnel will be automatically established when needed
runner = QueryRunner('pg', config)
results = runner.run_query("SELECT * FROM users")

Note: SSH key authentication is configured through the application settings.

Configuration Examples

Below are quick reference configuration examples for each supported database. See the specific documentation pages for each database for more details.

SQLite

sqlite_config = {
    "dbpath": "/path/to/database.sqlite"
}

MySQL

mysql_config = {
    "host": "mysql.example.com",
    "port": 3306,
    "user": "username",
    "passwd": "password",
    "db": "database_name",
    "charset": "utf8"
}

Microsoft SQL Server

mssql_config = {
    "server": "mssql.example.com",
    "port": 1433,
    "user": "username",
    "password": "password",
    "db": "database_name"
}

PostgreSQL

pg_config = {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "username",
    "password": "password",
    "dbname": "database_name"
}

Oracle

oracle_config = {
    "user": "username",
    "password": "password",
    "host": "oracle.example.com",
    "port": 1521,
    "servicename": "service_name"
}

BigQuery

bigquery_config = {
    "projectId": "your-gcp-project",
    "loadSchema": True,
    "useStandardSql": True
}