Skip to main content

Databases Integration (Natural Language to SQL)

Query databases using natural language with the Databases feature.

Overview​

The Databases integration allows users to query databases using natural language instead of writing SQL. It translates natural language questions into SQL queries and displays results.

Key Features:

  • Natural language to SQL translation
  • Support for multiple databases (PostgreSQL, MySQL, etc.)
  • Visual query builder
  • Query history
  • Result visualization

Access Databases​

Web Interface​

# Access Databases UI
https://YOUR_DATABASES_DOMAIN

# Example
https://databases.example.com

Authentication​

Uses Chatty AI authentication:

  • Login with your Chatty AI credentials
  • Same user management
  • No separate account needed

Configuration​

Environment Variables​

# Databases Domain
DATABASES_DOMAIN=databases.example.com
DATABASES_URL=https://databases.example.com

# Databases Configuration
ENABLE_DATABASES=true
DATABASES_GENERATION_MODEL=gpt-4o-mini

See Core Application Variables for all options.

Connecting Data Sources​

Step 1: Add Data Source​

  1. Login to Databases UI
  2. Click "Add Data Source"
  3. Select database type:
    • PostgreSQL
    • MySQL
    • SQLite
    • Others

Step 2: Configure Connection​

PostgreSQL Example:

Host: your-db-host.com
Port: 5432
Database: your_database
Username: readonly_user
Password: ••••••••••

MySQL Example:

Host: mysql-server.com
Port: 3306
Database: mydb
Username: readonly_user
Password: ••••••••••

Step 3: Test Connection​

Click "Test Connection" to verify settings.

Step 4: Save​

Click "Save" to add the data source.

Using Natural Language Queries​

Example Queries​

Simple Query:

How many users are in the database?

Filtered Query:

Show me all orders from last month

Aggregation:

What is the average order value by customer?

Join Query:

List customers with their total order count

Complex Query:

Show top 10 products by revenue in Q4 2023

Query Process​

  1. User enters natural language question
  2. AI generates SQL query
  3. User reviews generated SQL
  4. User approves or modifies query
  5. Query executes against database
  6. Results displayed in table/chart

Security Best Practices​

1. Read-Only Database User​

Always use read-only database accounts:

PostgreSQL:

-- Create read-only user
CREATE USER databases_readonly WITH PASSWORD 'secure_password';

-- Grant connect
GRANT CONNECT ON DATABASE mydb TO databases_readonly;

-- Grant select on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO databases_readonly;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT SELECT ON TABLES TO databases_readonly;

MySQL:

-- Create read-only user
CREATE USER 'databases_readonly'@'%' IDENTIFIED BY 'secure_password';

-- Grant select only
GRANT SELECT ON mydb.* TO 'databases_readonly'@'%';

FLUSH PRIVILEGES;

2. Network Security​

Restrict database access:

-- PostgreSQL: Allow only from Databases container
# pg_hba.conf
host    mydb    databases_readonly    172.18.0.0/16    md5

-- MySQL: Limit by host
CREATE USER 'databases_readonly'@'172.18.0.%' IDENTIFIED BY 'password';

3. Query Limits​

Set query limits to prevent resource exhaustion:

  • Row limit: 1000 rows default
  • Timeout: 30 seconds
  • Memory limit: 512MB

Troubleshooting​

Cannot Connect to Database​

# Check database is accessible
telnet your-db-host 5432

# Check firewall rules
sudo ufw status

# Verify credentials
psql -h your-db-host -U databases_readonly -d mydb

# Check Databases logs
docker compose logs chattydatabases-ai-service

Query Generation Fails​

# Check AI service is running
docker compose ps chattydatabases-ai-service

# Check API key configured
docker compose exec chattydatabases-ai-service env | grep OPENAI_API_KEY

# View logs
docker compose logs chattydatabases-ai-service --tail=100

Slow Queries​

# Check database performance
# Add indexes to frequently queried columns

# Increase query timeout
# In Databases UI: Settings → Query Timeout

# Optimize database
VACUUM ANALYZE;  # PostgreSQL
OPTIMIZE TABLE;  # MySQL

Advanced Features​

Custom SQL​

Users can write custom SQL directly:

  1. Click "SQL Mode"
  2. Write SQL query
  3. Execute
  4. View results

Query Templates​

Save frequently used queries as templates:

  1. Execute query
  2. Click "Save as Template"
  3. Name template
  4. Reuse later

Export Results​

Export query results:

  • CSV format
  • JSON format
  • Excel format

Visualizations​

Create charts from query results:

  • Bar charts
  • Line charts
  • Pie charts
  • Tables

Example Use Cases​

1. Business Analytics​

What were our top 5 products by revenue last quarter?

2. Customer Insights​

Show me customers who haven't ordered in 90 days

3. Inventory Management​

Which products have stock below 10 units?

4. Sales Reporting​

Compare sales by region for this year vs last year

Integration with Chatty AI​

Query from Chat​

Users can query databases directly from Chatty AI chat:

User: @databases How many active users do we have?

Chatty AI: [Queries database via Databases integration]
You have 1,234 active users.

Workflow Integration​

Use with n8n workflows:

  1. Trigger: Schedule
  2. Action: Query database via Databases
  3. Action: Process results
  4. Action: Send report