SQL Editability Analysis in DBX: How Query Modification Detection Works

DBX determines whether a SELECT statement can be edited by parsing the query and applying syntactic checks to ensure it can safely generate UPDATE, INSERT, or DELETE statements.

DBX is an open-source database management tool that enables users to edit data directly from query results. The SQL editability analysis engine inspects SELECT statements to determine if they can support write operations. This analysis prevents users from attempting to modify rows returned by complex queries involving joins, aggregations, or external data sources.

Core Implementation in sql_editability.rs

The editability logic resides in crates/dbx-core/src/sql_editability.rs. The main entry point is the analyze_editable_query_editability function (lines 85-149), which orchestrates the entire validation pipeline.

The QueryEditability Data Model

The analysis returns a QueryEditability struct defined in the same file:

pub struct QueryEditability {
    pub editable: bool,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub analysis: Option<EditableQueryInfo>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub reason: Option<QueryEditabilityReason>,
}

When editable is true, the analysis field contains an EditableQueryInfo with the schema, table name, column list, and alias. When false, the reason field provides a specific enum value such as ComplexSource, Aggregation, or ExternalSource.

Step-by-Step Analysis Workflow

The analysis follows a strict pipeline to validate query safety:

1. Normalize: The strip_sql_comments function (lines 333-359) removes -- line comments and /* … */ block comments, then trims trailing semicolons and whitespace.
2. Reject non-SELECT queries: The engine rejects empty strings, CTEs (WITH clauses), set operations (UNION, INTERSECT, EXCEPT), and multiple statements detected by semicolons (lines 88-101).
3. Locate the FROM clause: Using find_top_level_keyword (lines 71-73), the parser identifies the first top-level FROM keyword. Absence results in NoTable.
4. Analyze SELECT expressions: The parse_select_columns function (lines 55-96) validates each column. If is_select_star (lines 88-99) detects * or alias.*, it marks the query as using star selection; otherwise, it parses individual column expressions.
5. Detect aggregations: Presence of SELECT DISTINCT, GROUP BY, or HAVING triggers the Aggregation reason (lines 108-115).
6. Validate FROM sources: is_external_from_source (lines 37-40) flags file-based scans ('/path/file') and table-valued functions as ExternalSource. parse_from_source (lines 1-34) rejects joins, sub-queries, and multiple tables as ComplexSource.

Key Parsing Functions

The implementation relies on several specialized functions to handle SQL syntax safely.

parse_from_source extracts the schema, table name, and optional alias from simple FROM clauses. It strictly rejects any commas, parentheses, or join keywords that would indicate complex table expressions.

find_top_level_keyword walks the token stream while tracking nesting depth and quote contexts to ensure keywords are only matched at the outermost query level, avoiding false positives inside sub-queries.

strip_sql_comments handles comment removal without breaking string literals or quoted identifiers, ensuring the subsequent analysis works on clean SQL.

Integration with the Tauri Frontend

The Rust core exposes this functionality through the Tauri command layer. In src-tauri/src/commands/query.rs, the analyze_editable_query_editability command wraps the core library function:

// src-tauri/src/commands/query.rs
#[tauri::command]
async fn analyze_editable_query_editability(sql: String) -> Result<QueryEditability, Error> {
    Ok(dbx_core::sql_editability::analyze_editable_query_editability(&sql))
}

This allows the TypeScript frontend to invoke the analysis via the Tauri API bridge, receiving serialized QueryEditability JSON responses.

Practical Code Examples

These examples demonstrate how the analysis behaves with different query patterns.

Editable Simple Query

use dbx_core::sql_editability::analyze_editable_query_editability;

let sql = "SELECT id, name FROM public.users WHERE active = true ORDER BY id";
let result = analyze_editable_query_editability(sql);

assert!(result.editable);
let info = result.analysis.unwrap();
assert_eq!(info.table_name, "users");
assert_eq!(info.columns.len(), 2);

Rejected: Join Complexity

let sql = "SELECT u.id, o.amount FROM users u JOIN orders o ON o.user_id = u.id";
let result = analyze_editable_query_editability(sql);

assert!(!result.editable);
assert_eq!(result.reason.unwrap(), dbx_core::sql_editability::QueryEditabilityReason::ComplexSource);

Rejected: External File Source

let sql = "SELECT * FROM '/tmp/data.xlsx'";
let result = analyze_editable_query_editability(sql);
assert!(!result.editable);
assert_eq!(result.reason.unwrap(), dbx_core::sql_editability::QueryEditabilityReason::ExternalSource);

Rejected: Aggregation

let sql = "SELECT id, COUNT(*) FROM users GROUP BY id";
let result = analyze_editable_query_editability(sql);
assert!(!result.editable);
assert_eq!(result.reason.unwrap(), dbx_core::sql_editability::QueryEditabilityReason::Aggregation);

Summary

• DBX implements SQL editability analysis in crates/dbx-core/src/sql_editability.rs to determine if SELECT queries can generate modification statements.
• The analyze_editable_query_editability function pipelines through normalization, validation, and source checking to produce a QueryEditability result.
• Queries with joins, aggregations, external sources, or complex expressions are flagged as non-editable with specific reasons.
• The Tauri frontend accesses this logic via the analyze_editable_query_editability command in src-tauri/src/commands/query.rs.

Frequently Asked Questions

What makes a SQL query non-editable in DBX?

Queries are rejected when they contain joins, sub-queries, aggregations (GROUP BY, DISTINCT), set operations (UNION), external file sources, or table-valued functions. The analysis also fails if the parser cannot identify a single, explicit table source in the FROM clause.

How does DBX handle SQL comments during analysis?

The strip_sql_comments function removes both -- line comments and /* … */ block comments before processing. This normalization ensures that comment syntax does not interfere with keyword detection or parsing logic.

Can DBX edit SELECT * queries?

Yes, if the query is otherwise valid. When is_select_star detects * or alias.*, the system marks the query as using star selection and resolves the actual column names later when building the EditableQueryInfo. However, the query must still reference a single table without joins or aggregations.

Where is the editability logic tested?

The core logic is tested in crates/dbx-core/tests/sql_editability.test.rs with Rust unit tests. Integration tests in packages/app-tests/sqlAnalysis.test.ts verify the functionality through the Tauri command layer from the TypeScript frontend.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}