Live Database Connection API

Complete technical guide to connecting and querying your live databases through the Yardee Vector API. PostgreSQL, MySQL, and SQL Server support.

Get Your Free API Key Learn More

Database Connection Management

Connect and manage live database connections for real-time querying.

Add Database Connection

POST /api/v1/knowledgebases/{id}/connections/

curl -X POST https://app.yardee.ai/api/v1/knowledgebases/123/connections/ \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "connection_type": "database",
    "database_type": "postgresql",
    "host": "your-db.example.com",
    "port": 5432,
    "database_name": "your_database",
    "username": "readonly_user",
    "password": "secure_password",
    "use_ssl": true,
    "nickname": "Production Database"
  }'

Response:
{
  "id": 456,
  "connection_type": "database",
  "database_type": "postgresql", 
  "host": "your-db.example.com",
  "port": 5432,
  "database_name": "your_database",
  "username": "readonly_user",
  "nickname": "Production Database",
  "status": "pending",
  "created_at": "2025-01-20T10:30:00Z"
}

SSH Tunnel Connection

POST /api/v1/knowledgebases/{id}/connections/

curl -X POST https://app.yardee.ai/api/v1/knowledgebases/123/connections/ \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "connection_type": "database",
    "database_type": "postgresql",
    "host": "localhost",
    "port": 5432,
    "database_name": "your_database", 
    "username": "readonly_user",
    "password": "secure_password",
    "use_ssh_tunnel": true,
    "ssh_host": "bastion.example.com",
    "ssh_port": 22,
    "ssh_user": "ssh_user",
    "ssh_password": "ssh_password",
    "nickname": "Production DB via SSH"
  }'

List Database Connections

GET /api/v1/knowledgebases/{id}/connections/

curl -H "Authorization: Bearer YOUR_API_KEY" \
  https://app.yardee.ai/api/v1/knowledgebases/123/connections/

Response:
{
  "connections": [
    {
      "id": 456,
      "connection_type": "database",
      "database_type": "postgresql",
      "host": "your-db.example.com",
      "nickname": "Production Database",
      "status": "active",
      "last_indexed": "2025-01-20T11:00:00Z",
      "table_count": 25,
      "created_at": "2025-01-20T10:30:00Z"
    }
  ],
  "count": 1
}

Delete Database Connection

DELETE /api/v1/connections/{connection_id}/

curl -X DELETE https://app.yardee.ai/api/v1/connections/456/ \
  -H "Authorization: Bearer YOUR_API_KEY"

Response: 204 No Content

Supported Database Types

Connection parameters for different database systems.

PostgreSQL

{
  "database_type": "postgresql",
  "host": "your-postgres.example.com",
  "port": 5432,
  "database_name": "your_database",
  "username": "readonly_user",
  "password": "your_password",
  "use_ssl": true
}

MySQL

{
  "database_type": "mysql", 
  "host": "your-mysql.example.com",
  "port": 3306,
  "database_name": "your_database",
  "username": "readonly_user",
  "password": "your_password",
  "use_ssl": true
}

SQL Server

{
  "database_type": "sqlserver",
  "host": "your-sqlserver.example.com", 
  "port": 1433,
  "database_name": "your_database",
  "username": "readonly_user",
  "password": "your_password",
  "use_ssl": true
}

Querying Live Databases

Use the same search endpoint to query live database connections.

Basic Database Query

POST /api/v1/knowledgebases/{id}/search/

curl -X POST https://app.yardee.ai/api/v1/knowledgebases/123/search/ \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "How many customers do we have?",
    "top_k": 1
  }'

Response:
{
  "query": "How many customers do we have?",
  "knowledge_base_id": 123,
  "total_results": 1,
  "results": [
    {
      "chunk": null,
      "content": "You currently have 15,847 customers in your database.",
      "similarity_score": null,
      "document_title": "live_database_connection",
      "metadata": {
        "type": "live_database_query",
        "source": "Production Database",
        "connection_id": 456,
        "execution_time_ms": 45
      }
    }
  ]
}

Complex Analytics Queries

POST /api/v1/knowledgebases/{id}/search/

curl -X POST https://app.yardee.ai/api/v1/knowledgebases/123/search/ \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "What is the average order value by region for the last quarter?",
    "top_k": 1
  }'

Response:
{
  "query": "What is the average order value by region for the last quarter?",
  "total_results": 1,
  "results": [
    {
      "chunk": null,
      "content": "Average order values by region for Q4 2024: North: $245.67, South: $189.23, East: $312.45, West: $267.89",
      "similarity_score": null,
      "document_title": "live_database_connection",
      "metadata": {
        "type": "live_database_query",
        "source": "Production Database",
        "connection_id": 456,
        "execution_time_ms": 127
      }
    }
  ]
}

Mixed Results (Documents + Database)

When your knowledge base contains both uploaded documents and database connections, the search endpoint returns unified results from all sources.

{
  "query": "customer retention policy",
  "total_results": 3,
  "results": [
    {
      "chunk": {
        "id": 789,
        "content": "Our customer retention policy focuses on...",
        "document_id": 123
      },
      "content": "Our customer retention policy focuses on...",
      "similarity_score": 0.89,
      "document_title": "Customer_Policies.pdf",
      "metadata": {
        "type": "document_chunk",
        "page": 3
      }
    },
    {
      "chunk": null,
      "content": "Current customer retention rate is 84.3% based on 12-month analysis",
      "similarity_score": null,
      "document_title": "live_database_connection", 
      "metadata": {
        "type": "live_database_query",
        "source": "Production Database",
        "connection_id": 456
      }
    }
  ]
}

Security Best Practices

Secure your database connections and protect your data.

Recommended Practices

• Create dedicated read-only database users
• Use SSH tunnels for production databases
• Enable SSL/TLS encryption
• Grant minimal necessary table permissions
• Use strong, unique passwords
• Regularly rotate database credentials

Security Warnings

• Never use administrative or write-enabled accounts
• Don't expose databases directly to the internet when possible
• Avoid using default database ports for internet-facing servers
• Never store production credentials in code or logs
• Don't use password-protected SSH private keys

Creating Read-Only Database Users

-- PostgreSQL
CREATE ROLE yardee_readonly WITH LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE your_database TO yardee_readonly;
GRANT USAGE ON SCHEMA public TO yardee_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO yardee_readonly;

-- MySQL  
CREATE USER 'yardee_readonly'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON your_database.* TO 'yardee_readonly'@'%';
FLUSH PRIVILEGES;

-- SQL Server
CREATE LOGIN yardee_readonly WITH PASSWORD = 'strong_password';
CREATE USER yardee_readonly FOR LOGIN yardee_readonly;
EXEC sp_addrolemember 'db_datareader', 'yardee_readonly';

Error Handling

Common database connection errors and solutions.

Connection Failed

Cannot establish database connection. Check host, port, and credentials.

Authentication Failed

Invalid username or password. Verify database credentials.

SSH Tunnel Failed

Cannot establish SSH connection. Check SSH host, credentials, and key format.

Permission Denied

Database user lacks necessary permissions. Grant SELECT permissions on required tables.

Query Timeout

Database query took too long to execute. Consider optimizing database indexes.

Frequently Asked Questions

How do I connect my database to the API?

Use the POST /api/v1/knowledgebases/{id}/connections/ endpoint to add your database connection details. We support PostgreSQL, MySQL, and SQL Server with both direct connections and SSH tunnels.

What credentials do I need to provide?

You need database host, port, database name, and read-only user credentials. For SSH tunnels, you also need SSH server details and authentication credentials.

How does the API query my live database?

When you ask questions, our AI analyzes your query, generates appropriate SQL, and executes it against your database in real-time. Results are returned as natural language answers through the standard search endpoint.

Is my database data stored on your servers?

No, we only store lightweight schema metadata (table names, column types) to help the AI understand your database structure. Your actual data stays in your database.

Can I use the same search endpoint for both documents and database queries?

Yes! The /search/ endpoint automatically detects whether to perform document search or database queries based on your knowledge base contents. Mixed results are unified in a single response.

Ready to Connect Your Database?

Start querying your live database with AI-powered natural language processing. Free to get started, no credit card required.

Get Your API Key Learn More