Connector Overview

​

Supported Databases

• PostgreSQL - Open-source relational database with advanced features
• MySQL - Popular open-source relational database
• MariaDB - MySQL-compatible database with enhanced features
• SQL Server - Microsoft’s enterprise relational database
• SQLite - Lightweight embedded database

​

Connector Architecture

​

ConnectorRegistry Pattern

// From src/connectors/manager.ts
export class ConnectorRegistry {
  private static connectors: Map<ConnectorType, Connector> = new Map();

  static register(connector: Connector): void {
    ConnectorRegistry.connectors.set(connector.id, connector);
  }

  static getConnectorForDSN(dsn: string): Connector | null {
    for (const connector of ConnectorRegistry.connectors.values()) {
      if (connector.dsnParser.isValidDSN(dsn)) {
        return connector;
      }
    }
    return null;
  }
}

• postgres:// or postgresql:// → PostgreSQL connector
• mysql:// → MySQL connector
• mariadb:// → MariaDB connector
• sqlserver:// → SQL Server connector
• sqlite:// → SQLite connector

​

Connection Management

• Multi-source connections: Connect to multiple databases simultaneously with unique IDs
• Lazy connection: Defer connection until first use to improve startup time
• SSH tunneling: Secure connections through bastion hosts
• Connection pooling: Efficient resource usage for network databases

// Example: Multi-source configuration in TOML
[[sources]]
id = "prod_pg"
dsn = "postgres://user:pass@localhost:5432/production"

[[sources]]
id = "staging_mysql"
dsn = "mysql://user:pass@localhost:3306/staging"

​

Connector Interface

​

Core Methods

• connect(dsn, initScript?, config?) - Establish database connection
• disconnect() - Close database connection
• clone() - Create new instance for multi-source support

​

Schema Discovery

• getSchemas() - List all schemas/databases
• getTables(schema?) - List tables in a schema
• getTableSchema(tableName, schema?) - Get column information
• getTableIndexes(tableName, schema?) - Get index information
• getTableComment(tableName, schema?) - Get table description (optional)
• getTableRowCount(tableName, schema?) - Get estimated row count (optional)

​

Stored Procedures

• getStoredProcedures(schema?, routineType?) - List procedures/functions
• getStoredProcedureDetail(procedureName, schema?) - Get procedure details

​

Query Execution

• executeSQL(sql, options, parameters?) - Execute SQL with options and parameters

​

Common Connection Options

​

Timeouts

[[sources]]
id = "mydb"
dsn = "postgres://user:pass@host/db"
connection_timeout = 30  # Seconds to wait for connection
query_timeout = 60       # Seconds to wait for query results

​

SSL/TLS Modes

• sslmode=disable - No SSL connection (default for development)
• sslmode=require - SSL without certificate verification
• Other values - SSL with certificate verification

# Example DSN with SSL
postgres://user:pass@host:5432/db?sslmode=require

​

Read-Only Mode

[[sources]]
id = "readonly_db"
dsn = "postgres://user:pass@host/db"
readonly = true  # SDK-level enforcement

​

PostgreSQL-Specific Options

[[sources]]
id = "pg_db"
dsn = "postgres://user:pass@host/db"
search_path = "myschema,public"  # Schema search path

​

DSN Format Overview

protocol://[username[:password]@]host[:port]/database[?options]

# PostgreSQL
postgres://user:pass@localhost:5432/mydb?sslmode=require

# MySQL
mysql://root:pass@localhost:3306/mydb?sslmode=disable

# MariaDB
mariadb://user:pass@localhost:3306/mydb

# SQL Server
sqlserver://user:pass@localhost:1433/mydb?instanceName=SQLEXPRESS

# SQLite (file-based)
sqlite:///path/to/database.db
sqlite:///:memory:  # In-memory database

​

SSH Tunnel Support

[[sources]]
id = "remote_db"
dsn = "postgres://user:pass@db-host:5432/mydb"
ssh_host = "bastion.example.com"
ssh_user = "admin"
ssh_key = "~/.ssh/id_rsa"
ssh_passphrase = "keypass"  # Optional

​

Building Custom Connectors

1. Create a new directory: src/connectors/{database-type}/
2. Implement Connector and DSNParser interfaces from src/connectors/interface.ts
3. Register your connector:

import { ConnectorRegistry } from "../interface.js";

export class MyDatabaseConnector implements Connector {
  id: ConnectorType = "mydatabase";
  name = "My Database";
  dsnParser = new MyDatabaseDSNParser();
  
  clone(): Connector {
    return new MyDatabaseConnector();
  }
  
  // Implement other interface methods...
}

// Register the connector
const myConnector = new MyDatabaseConnector();
ConnectorRegistry.register(myConnector);

4. Add your connector type to the ConnectorType union in src/connectors/interface.ts:4

​

Multi-Statement Support

• PostgreSQL: Uses transactions for consistency (src/connectors/postgres/index.ts:569)
• MySQL/MariaDB: Native multi-statement support enabled by default
• SQL Server: Limited multi-statement support
• SQLite: Executes statements individually, tracks changes

​

Error Handling

• DSN parsing errors include obfuscated DSN and expected format
• Connection errors include specific database error messages
• Query errors log SQL and parameters (when available)
• All errors preserve stack traces for debugging

​

Performance Optimizations

​

Connection Pooling

• PostgreSQL: pg.Pool
• MySQL: mysql2.createPool()
• MariaDB: mariadb.createPool()
• SQL Server: mssql.ConnectionPool

​

Row Count Estimation

• PostgreSQL: pg_class.reltuples (src/connectors/postgres/index.ts:337)
• SQL Server: System catalog statistics
• MySQL/MariaDB/SQLite: Use COUNT(*) as fallback

​

Query Result Limiting

// Applied at database level for efficiency
await connector.executeSQL(
  "SELECT * FROM large_table",
  { maxRows: 100 }
);

​

Next Steps

• Configure your first connection: Configuration Guide
• Explore connector-specific features:
  • PostgreSQL - Schemas, full-text search, JSON operations
  • MySQL - IAM authentication, multi-database support
  • MariaDB - MySQL compatibility with enhancements
  • SQL Server - Named instances, Azure AD authentication
  • SQLite - Embedded databases, in-memory mode
• Set up secure connections: SSH Tunnels