> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cloosphere.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Database
> Natural-language database queries with NL-to-SQL — DbSphere connection setup and usage guide
Tired of writing SQL or asking the data team every time you need to analyze internal data?
DbSphere **auto-converts natural language to SQL** to query databases. Ask "show me this month's sales" and the AI generates SQL, executes it, and responds with the results in natural language and a table, visualizing them as a chart on request.
### Example
> "Compare sales by department this quarter"
| Method | Process | Time |
| ------------------ | -------------------------------------------------- | :-----------: |
| Write SQL yourself | Write SQL → execute → interpret | 10–30 min |
| Ask the data team | Request → wait → receive | Hours to days |
| DbSphere | Natural-language question → instant answer + chart | **10 sec** |
***
## NL-to-SQL Pipeline
```mermaid theme={null}
flowchart LR
A[Natural-language question] --> B[Schema analysis]
B --> C[SQL generation]
C --> D{SELECT only?}
D -->|No| E[Block]
D -->|Yes| F[Run query]
F --> G[Return results]
G --> H[Chart / NL response]
```
| Stage | Description |
| ------------------- | --------------------------------------------------------------------- |
| **Schema analysis** | The AI analyzes connected table structure and column descriptions |
| **SQL generation** | Converts the natural-language question to SQL in the right DB dialect |
| **Safety check** | Allows only SELECT queries; blocks INSERT, UPDATE, DELETE, etc. |
| **Run query** | Executes validated SQL on the database |
| **NL response** | Converts query results to natural-language text or chart |
***
## Supported Databases
10 databases are supported by default.
| Database | Type | Strengths |
| -------------------------------- | -------------- | ----------------------------------------------------- |
| **PostgreSQL** | RDBMS | Advanced features, JSON support, open-source |
| **MySQL** | RDBMS | Most widely used RDBMS |
| **Microsoft SQL Server (MSSQL)** | RDBMS | Enterprise environments |
| **Oracle** | RDBMS | Large enterprise |
| **SQLite** | RDBMS | Lightweight embedded database |
| **Snowflake** | Cloud DW | Cloud data warehouse |
| **BigQuery** | Cloud DW | Google Cloud data warehouse, service account key auth |
| **Databricks** | Lakehouse | Databricks SQL Warehouse, Delta Lake integrated |
| **Azure Synapse** | Cloud DW | Azure unified analytics platform |
| **Microsoft Fabric** | Lakehouse / DW | Power BI integrated analytics environment |
All 10 are **enabled by default**. Admins can narrow or expand the visible list with the `DBSPHERE_TYPES` environment variable.
***
## Connect a Database
Click **Workspace > Database > "+ New Database"** and enter basic info.
| Field | Description | Example |
| --------------- | ---------------------------------------------------- | ------------------------- |
| **Name** | Connection display name | "Sales Analytics DB" |
| **Description** | Database purpose | "Sales team revenue data" |
| **Access** | Access scope (Public · Private · Group/Organization) | "Group: Sales" |
In **DB Information**, click **Configure** to enter the credentials needed to connect to the database. The required fields depend on the **database type**.
**Common fields:**
| Field | Description |
| ------------------ | --------------------------------------------------------------- |
| **Host** | DB server address |
| **Port** | Connection port |
| **Database** | DB name to connect to |
| **Username** | DB account |
| **Password** | DB password |
| **Max query time** | Query timeout in seconds. Leave blank to use the server default |
Beyond the common fields (host, port, database, username, password, max query time), each DB type adds the fields below.
| DB Type | Additional Fields | Notes |
| -------------- | --------------------------------------------------------- | ------------------------------------------- |
| **MySQL** | — | Common fields only |
| **MSSQL** | Schema | Schema name (default: `dbo`) |
| **PostgreSQL** | Schema | Schema name (default: `public`) |
| **Snowflake** | Account, Warehouse, Role, Schema | No host/port needed |
| **Oracle** | Schema, Service Name, SID | Use Service Name or SID instead of Database |
| **SQLite** | Database file | No host/port/auth — only the file path |
| **Databricks** | Server hostname, HTTP path, Access token, Catalog, Schema | Delta Lake / SQL Warehouse |
| **Synapse** | Schema | Schema name (default: `dbo`) |
| **Fabric** | Schema | Schema name (default: `dbo`) |
| **BigQuery** | Project ID, Dataset, Service Account JSON | No host/port/username/password needed |
Click **"Test Connection"** to verify access.
After successful connection, select tables the AI will reference.
Always exclude tables containing sensitive info (PII, passwords, etc.). Selected tables are accessible by the AI.
Write a tool description that tells the agent when and how to use this database.
**AI auto-generation:** Click the auto-generate button next to the tool description field — the AI drafts it based on connected table structure and column info.
```
This database holds the sales team's order, customer, and inventory info.
Use for revenue analysis, customer lookup, inventory checks, and similar.
JOIN the orders table with the customers table
to get per-customer purchase history.
```
The more accurate the tool description, the more accurately the agent picks the right database among many.
Click the **lock** button at the top to set who can use the database.
| Option | Description |
| ---------------------- | --------------------------------------------------- |
| **Public** | Available to all users |
| **Private** | Available only to you |
| **Group/Organization** | Available only to specified groups or organizations |
***
## How the AI Generates SQL
When you connect a DB to an agent, user questions go through this process to become SQL.
```mermaid theme={null}
flowchart TD
A[User question] --> B[Look up DB schema]
B --> C[Memory semantic search]
C --> D["Collect past successful queries + business rules + SQL examples"]
D --> E[AI generates SQL]
E --> F{SELECT only?}
F -->|No| G[Block]
F -->|Yes| H[Run SQL]
H --> I{Success?}
I -->|Failure| J["Auto-retry (up to 3 times)"]
J --> E
I -->|Success| K[Generate results + chart]
K --> L["Auto-save successful query to memory (few-shot accumulation)"]
```
Successful queries auto-accumulate in memory. The more you use it, the more accurate SQL the AI generates for similar questions.
***
## Memory System
DbSphere uses 4 types of memory to improve SQL generation accuracy. The more memory, the more accurate the AI's queries.
| Memory | Badge | Content | Creation |
| ----------------- | :-----------: | -------------------------------------------- | ------------------------------------- |
| **DDL Schema** | DDL Schema | Table structure + column descriptions | Auto on schema extraction |
| **SQL Few-shot** | SQL Few-shot | Question-SQL pairs (past successful queries) | **Auto-accumulated** on query success |
| **Documentation** | Documentation | Business terms, rules, context | Manual entry |
| **SQL Example** | SQL Example | Reference SQL examples (use case, tags) | Manual entry or auto on extraction |
### Improve Accuracy with DDL Schema Descriptions
Adding business descriptions to the tables and columns in **DDL Schema** memory greatly improves the AI's SQL generation accuracy. After schema extraction, go to **Memory tab → click DDL Schema → Edit** and enter descriptions for each table and column.
You can also configure the LLM to auto-generate table/column descriptions and sample Q\&A during schema extraction. Reviewing and correcting the auto-generated results improves accuracy further.
```
Table: orders
Description: Order history table
Columns:
- order_id: Unique order number
- customer_id: Customer ID (references customers table)
- order_date: Order timestamp
- total_amount: Total order amount (KRW)
- status: Order status (pending/confirmed/shipped/delivered)
```
### SQL Few-shot is Key
SQL Few-shot is a few-shot learning memory where **successful queries are auto-saved**.
| Use Count | SQL Few-shot | AI Behavior |
| :-------: | :----------: | ---------------------------------------------- |
| First | 0 | Guess SQL from schema only |
| 5 | 5 | Generate referencing similar past queries |
| 50+ | 50+ | Generate accurate SQL using validated patterns |
### Documentation Use
Tell the AI about business rules it doesn't know via Documentation.
| Type | Example |
| ----------- | ---------------------------------------------------------------------------------------------- |
| **Term** | "MRR is the abbreviation for Monthly Recurring Revenue, stored in the monthly\_revenue column" |
| **Rule** | "When aggregating revenue, orders with status='cancelled' must be excluded" |
| **Context** | "Customer tiers are 4 levels: S/A/B/C, with S being the highest" |
When the AI repeatedly generates wrong SQL with a recurring pattern, **add the rule to Documentation**. The AI references it from the next question onward.
***
## Data Visualization
SQL results are provided as a **table by default**. When you **ask (e.g., "show me a chart")**, they're visualized with a chart suited to the data, and you can switch directly via the **chart-type toggle** at the top of the results.
| Chart Type | Suitable Data |
| --------------- | ---------------------------- |
| **Bar** | Comparison by category |
| **Line** | Time-series trend |
| **Pie** | Ratio / composition |
| **Scatter** | Correlation |
| **Heatmap** | Cross analysis |
| **Histogram** | Distribution |
| **Grouped bar** | Multi-dimensional comparison |
| **Table** | Detailed data |
When you request a chart, the chart type that fits the data structure is **recommended and auto-selected**, and you can switch to another type via the top toggle.
***
## Querying Databases
### Connect to an Agent
1. In the agent edit screen, click **"Select Database"** in the "Database" section
2. Pick the database to connect
3. Click **Apply**
### Use in Chat
When you chat with an agent that has databases connected, the AI analyzes the question and auto-generates and runs SQL.
```
User: What's the revenue growth rate this quarter vs. last?
AI: Quarterly revenue analysis:
| Quarter | Revenue | QoQ |
|---------|---------|-----|
| Q4 2024 | ₩1.25B | - |
| Q1 2025 | ₩1.42B | +13.6% |
This quarter's revenue grew 13.6% QoQ.
Key drivers: new customer acquisition (+23%), repeat-buyer rate up (+8%)
```
### Example Questions
```
- What's this month's revenue?
- Show daily revenue trend last week
- List the top 10 customers by revenue
- What's the share of revenue by product category?
```
```
- How many new sign-ups this month?
- Show me the VIP customer list
- Which customers haven't purchased in the last 3 months?
- Distribution of customers by region
```
```
- List products with inventory below 10
- What products are arriving this week?
- Top 5 products with low inventory turnover
```
```
- Show headcount by department
- How many joined/left this month?
- What's the average tenure?
```
### Reviewing SQL Execution Results
A **"SQL Query" button** appears below the AI response. Click to see the actual SQL run and detailed results.
| Feature | Description |
| ---------------- | ----------------------------------------------------------------------- |
| **View SQL** | Show the executed SQL with formatting (uppercase keywords, indentation) |
| **Copy SQL** | Copy SQL to clipboard — useful when re-running in a separate query tool |
| **Result table** | View query results as a table |
Result data is shown up to **100 rows**. When the total exceeds 100, a "Showing 100 of N rows" notice appears. If you need the complete data, download it via **`Export to CSV` at the top-right of the chat result table**.
***
## DB Dialect Differences
Each DB has slightly different SQL syntax. Cloosphere **dynamically injects per-DB dialect rules into the system prompt** when converting natural language to SQL, guiding the AI to produce correct SQL. Still, expressing intent clearly produces more accurate SQL.
### Key Differences
| Item | PostgreSQL | MySQL | Oracle | MSSQL/Synapse/Fabric | SQLite | Snowflake | BigQuery | Databricks |
| ------------------------------ | :------------------: | :---------------------------: | :-----------------------------------: | :------------------: | :-----------------: | :------------------: | :---------------------------: | :--------------: |
| **Identifier quoting** | `"name"` | `` `name` `` | `"NAME"` | `[name]` | `"name"` | `"NAME"` | `` `name` `` | `` `name` `` |
| **LIMIT expression** | `LIMIT n` | `LIMIT n` | `FETCH FIRST n ROWS ONLY` | `TOP n` | `LIMIT n` | `LIMIT n` | `LIMIT n` | `LIMIT n` |
| **Date literal** | `'2026-04-26'` | `'2026-04-26'` | `TO_DATE('2026-04-26', 'YYYY-MM-DD')` | `'2026-04-26'` | `'2026-04-26'` | `'2026-04-26'` | `DATE '2026-04-26'` | `'2026-04-26'` |
| **Date format function** | `TO_CHAR()` | `DATE_FORMAT()` | `TO_CHAR()` | `FORMAT()` | `strftime()` | `TO_CHAR()` | `FORMAT_DATE()` | `date_format()` |
| **Date arithmetic** | `+ INTERVAL '1 day'` | `DATE_ADD(d, INTERVAL 1 DAY)` | `+ 1` (direct) | `DATEADD(day, 1, d)` | `date(d, '+1 day')` | `DATEADD(day, 1, d)` | `DATE_ADD(d, INTERVAL 1 DAY)` | `date_add(d, 1)` |
| **String concat** | `\|\|` | `CONCAT()` | `\|\|` | `+` | `\|\|` | `\|\|` | `CONCAT()` | `\|\|` |
| **Empty string = NULL** | ✗ | ✗ | **✓ ⚠️** | ✗ | ✗ | ✗ | ✗ | ✗ |
| **Case-sensitive identifiers** | When quoted ✓ | Default ✗ | Uppercase by default | Default ✗ | When quoted ✓ | When quoted ✓ | Default ✗ | When quoted ✓ |
### System Auto-Correction vs. User Responsibility
**Handled automatically:**
* Dashboard period filter (`$st`/`$ed` placeholders): auto-converted to per-DB date literals
* Identifier quoting during Knowledge Graph value extraction: auto-selects per-DB quotes
* Per-DB dialect rules dynamically injected into the AI's system prompt (LIMIT, date, identifiers, etc.)
**User/AI responsibility:**
* When you write Korean/special-char column names directly in natural-language queries — the AI may miss quotes and fail
* Oracle empty-string comparisons (see Oracle quirks below)
* Complex window functions and CTEs vary by DB — verify AI output
***
## Oracle DB Quirks
Oracle has some behaviors that differ from ANSI SQL — worth knowing.
Oracle treats `''` (empty string) the same as `NULL`. So conditions like the following **always return 0 rows**:
```sql theme={null}
-- ❌ All wrong patterns (Oracle)
WHERE col <> ''
WHERE col = ''
WHERE TRIM(col) <> ''
```
**Correct usage:**
```sql theme={null}
-- ✅ Use NULL checks instead
WHERE col IS NOT NULL
WHERE TRIM(col) IS NOT NULL
```
Cloosphere's system prompt includes this rule, and the AI auto-avoids it. Still, when natural-language queries use ambiguous phrasing like **"non-empty items"** and 0 rows are returned, suspect this pattern.
Unlike other DBs, Oracle doesn't auto-cast strings to dates.
```sql theme={null}
-- ❌ Fails on Oracle
WHERE created_at >= '2026-04-01'
-- ✅ Oracle recommended
WHERE created_at >= TO_DATE('2026-04-01', 'YYYY-MM-DD')
```
BI dashboard period filters (`$st`/`$ed`) auto-substitute as `TO_DATE(...)` when using Oracle. But you must specify it yourself when writing SQL directly in the SQL result screen.
```sql theme={null}
-- ❌ Oracle doesn't support INTERVAL '1 day'
WHERE created_at >= SYSDATE - INTERVAL '1 day'
-- ✅ Oracle recommended
WHERE created_at >= SYSDATE - 1
```
Use `TRUNC(col, 'DD')` instead of `DATE_TRUNC()`, and `TO_CHAR(col, 'D')` instead of `EXTRACT(DOW FROM col)` for day-of-week.
In Oracle, using a Korean column alias (`AS 공장코드`) directly causes `ORA-00936: missing expression`.
```sql theme={null}
-- ❌ Fails on Oracle
SELECT plant_code AS 공장코드 FROM plants
-- ✅ Oracle recommended
SELECT plant_code AS "공장코드" FROM plants
```
The system prompt includes this rule and the AI auto-applies quotes — but in environments with many Korean columns, verify the result and fix manually if needed.
```sql theme={null}
-- ❌ Oracle 12c minimum, or wrong usage
SELECT * FROM users LIMIT 10
-- ✅ Oracle 12c+
SELECT * FROM users FETCH FIRST 10 ROWS ONLY
-- ✅ Oracle 11g compatible (subquery)
SELECT * FROM (SELECT * FROM users) WHERE ROWNUM <= 10
```
***
## Security
### Read-only
DbSphere only executes **SELECT queries**. Data modification is not possible.
| Allowed | Blocked |
| ------------------------------------- | ---------------------- |
| SELECT | INSERT, UPDATE, DELETE |
| Aggregate functions (COUNT, SUM, AVG) | DROP, ALTER, TRUNCATE |
| JOIN, subqueries | CREATE, GRANT |
### Credential Protection
* DB passwords are stored encrypted
* Connection info is only viewable by users with access permission
***
## Best Practices
### Database Account Setup
1. **Create a dedicated account**: Make a read-only account just for AI use
2. **Grant least privilege**: Grant SELECT permission only on needed tables
3. **Set query limits**: Configure timeout and result row limits
### Table Selection
1. **Pick only what's needed**: Connecting all tables can confuse the AI
2. **Exclude sensitive data**: Always exclude tables with PII or passwords
3. **Pick related tables together**: Pick tables that need to JOIN together
### Schema Description Writing
1. **Korean descriptions encouraged**: Describe tables and columns using business terminology
2. **Add business context**: Document possible values and meanings of "status" columns
3. **State relationships explicitly**: Describe foreign-key relationships and JOIN conditions
***
## Troubleshooting
| Cause | Solution |
| ------------- | ---------------------------------------- |
| Network issue | Check firewall rules, VPN connection |
| Auth failure | Re-verify account, password, permissions |
| Port blocked | Verify the DB port is open |
| SSL config | Check SSL certificate requirements |
| Cause | Solution |
| ------------------------------- | ----------------------------------------------------------------- |
| Insufficient schema description | Add detailed descriptions for tables and columns |
| Business rules not reflected | Add rules to **Documentation memory** |
| Related tables missing | Pick tables that need JOIN together |
| Ambiguous question | Include specific conditions (date range, filters) in the question |
| No past reference queries | Add correct query examples to **SQL Example memory** |
| Cause | Solution |
| ----------------- | ---------------------------------------------- |
| Large data volume | Add conditions like date range to the question |
| Complex query | Simplify the question or break into multiple |
| DB performance | Optimize indexes, check DB resources |
***
## FAQ
No — DbSphere is read-only. Only SELECT queries are executed; data can't be modified or deleted. Any SQL not starting with `SELECT` is blocked immediately.
Yes — the AI response shows the generated SQL. You can also see the executed SQL and results in detail under Tracing.
Yes — pick all related tables and describe inter-table relationships in the schema description. The AI will generate appropriate JOIN queries.
Yes — when multiple databases are connected to an agent, the AI auto-picks the right one based on the question. Writing detailed **tool descriptions** for each DB improves selection accuracy.
With a model, the LLM **auto-generates business descriptions** for tables/columns and **sample Q\&A pairs**. Without a model, only DDL structure is stored. Picking a model is recommended.
***
## Related Pages
Connect a DB to an agent to enable natural-language queries
Tell the AI about DB business terminology to improve accuracy
Track the SQL generation and execution process step by step