Add CockroachDB integration support

[viragtripathi]

Prerequisites

• Search the current open issues

What are you trying to do that currently feels hard or impossible?

Currently, genai-toolbox doesn't support CockroachDB, a cloud-native distributed SQL database. Users who need to work with CockroachDB clusters (especially multi-region deployments) cannot use genai-toolbox to:

• Query distributed CockroachDB databases with AI agents
• Execute SQL operations across geographically distributed clusters
• Leverage CockroachDB's horizontal scalability features
• Work with CockroachDB-specific features like multi-region survivability

Specific Use-Case:
Enterprise applications using CockroachDB for global distribution need AI agents to interact with their databases. For example, a fintech application with CockroachDB deployed across US, Europe, and Asia regions needs to query transaction data, manage schemas, and execute operations through LLM-powered tools. Without CockroachDB support, these users must either migrate to a supported database or cannot use genai-toolbox at all.

Suggested Solution(s)

Add a native CockroachDB integration following the same patterns as existing postgres and spanner integrations:

Implementation:

• Create internal/sources/cockroachdb/ with source implementation
• Use official github.com/cockroachdb/cockroach-go/v2 library for automatic transaction retry
• Implement 4 tools:
  1. cockroachdb-execute-sql - Execute ad-hoc SQL queries
  2. cockroachdb-sql - Parameterized SQL queries with template support
  3. cockroachdb-list-tables - List tables with schema details
  4. cockroachdb-list-schemas - List database schemas

Configuration Example:

sources:
  my-cockroachdb:
    kind: cockroachdb
    host: localhost
    port: "26257"
    user: root
    password: ""
    database: defaultdb
    maxRetries: 5
    retryBaseDelay: 500ms
    queryParams:
      sslmode: disable

Key Features:

• PostgreSQL wire protocol compatibility (uses pgx/v5)
• Automatic transaction retry using crdbpgx.ExecuteTx()
• UUID primary keys (CockroachDB best practice)
• Connection retry with exponential backoff
• Multi-region cluster support

Benefits:

• Native CockroachDB support without workarounds
• Follows CockroachDB best practices (retry logic, UUID PKs)
• Compatible with CockroachDB Cloud, Self-Hosted, and Kubernetes deployments
• Works with v25.4.0+ (tested on v25.4.0)

Alternatives Considered

Alternative 1: Use PostgreSQL integration as-is

• Why not: While CockroachDB is PostgreSQL-compatible, it requires:
  • Transaction retry logic (CockroachDB serializable isolation can cause retries)
  • Different system table queries (e.g., crdb_is_user_defined vs PostgreSQL columns)
  • UUID primary keys instead of SERIAL for optimal performance
  • The postgres integration doesn't use cockroach-go's automatic retry mechanism

Alternative 2: Create wrapper tools manually

• Why not: Users would need to implement retry logic themselves, which is error-prone and defeats the purpose of genai-toolbox providing ready-to-use tools

Alternative 3: Use raw database clients

• Why not: Doesn't integrate with AI agents/LLMs, no manifest generation, no MCP support

Why adding this feature is preferred:

• Native integration provides optimal performance with CockroachDB-specific features
• Official cockroach-go library ensures correct behavior (especially retry logic)
• Follows project patterns (postgres/spanner) for consistency
• Enables CockroachDB users to adopt genai-toolbox without workarounds

Additional Details

Implementation Status:
I have a working implementation ready to submit as a PR that includes:

• ✅ Source implementation using cockroach-go/v2
• ✅ All 4 tools with unit tests
• ✅ Integration tests verified on CockroachDB v25.4.0 multi-region cluster
• ✅ Cloud Build configuration
• ✅ Follows CONTRIBUTING.md guidelines

Testing:

• Tested on CockroachDB v25.4.0 (aarch64-unknown-linux-gnu)
• All 18 tests passing (8 source tests, 8 tool tests, 2 integration tests)
• Verified automatic retry with cockroach-go
• Verified UUID primary keys
• Verified multi-region zone configurations

CockroachDB Context:

• CockroachDB is used by major companies for distributed SQL needs
• Supports PostgreSQL wire protocol but has distinct characteristics
• Version compatibility: v23.x, v24.x, v25.x (tested on v25.4.0)
• Deployment options: CockroachDB Cloud, Self-Hosted, Kubernetes

Related Resources:

• CockroachDB Documentation: https://www.cockroachlabs.com/docs/
• cockroach-go library: https://github.com/cockroachdb/cockroach-go
• Best Practices: https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html

Contribution Guidelines:
This proposal follows:

• ✅ CONTRIBUTING.md
• ✅ CODE_OF_CONDUCT.md
• ✅ Security Policy

[duwenxin99]

Hi @viragtripathi, thanks for opening the issue! We are happy to start this integration and I'll take a look at the PR shortly.