How Chat2DB's SQL Completion System Functions: Architecture and Implementation

Chat2DB implements SQL auto-completion as a layered pipeline that bridges UI requests, a generic completion engine, and per-dialect plugins to provide context-aware suggestions across multiple database types.

This open-source database management tool from OtterMind/Chat2DB delivers intelligent SQL completion through a modular architecture that supports both dialect-specific plugins and a fallback generic engine. The system processes cursor position metadata, parses SQL fragments using regular-expression patterns, and caches database metadata to generate relevant table, column, and keyword suggestions. Understanding this architecture reveals how the controller delegates to service layers, how plugins register via SPI, and how the generic engine handles unsupported dialects.

From REST Request to Service Layer

The completion flow begins at the REST controller layer. The frontend sends a SqlCompletionRequest payload containing the raw SQL, cursor position, database type, and connection context. In DbSqlParserController.java (lines 101–105), the controller converts this to a DbSqlCompletionGetRequest and forwards it to the service interface IDbSqlCompletionService.

// DbSqlParserController (lines 101-105)
DataResult<SqlCompletionResponse> sqlCompletion(@Valid @RequestBody SqlCompletionRequest request) {
    DbSqlCompletionGetRequest sqlCompletionParam = dbWebConverter.request2completionParam(request);
    return DataResult.of(SqlCompletionResponse.from(sqlSqlCompletionService.sqlCompletion(sqlCompletionParam)));
}

This conversion separates the API contract from the internal domain model, allowing the service layer to operate independently of transport protocols.

Service Orchestration: Plugin vs. Generic Engine

The core logic resides in DbSqlCompletionServiceImpl, which determines whether to use a dialect-specific plugin or the generic completion engine. The service builds a DbSqlCompletionRequest containing the parsed SQL and cursor metadata, then applies conditional routing based on the database type.

For MySQL and other plugin-supported dialects, the service delegates to DefaultSqlSyntaxHandler.complete(request). For all other dialects, it falls back to GenericSqlCompletionEngine.complete(param).

// DbSqlCompletionServiceImpl (lines 31-49)
if (StringUtils.equalsIgnoreCase(DatabaseTypeEnum.MYSQL.name(), dbConfig.getDbType())) {
    return DefaultSqlSyntaxHandler.complete(request);          // plugin path
}
SqlCompletionResponse response = genericSqlCompletionService.complete(param);
response.setEditorHints(DefaultSqlSyntaxHandler.editorHints(request)); // add hints
return response;

This dual-path approach ensures that optimized, dialect-aware completions are available for major databases while maintaining broad compatibility through the generic engine.

Plugin Dispatch Architecture

The DefaultSqlSyntaxHandler acts as a dispatcher that loads dialect-specific implementations at startup. It retrieves ISqlSyntaxPlugin instances from Chat2DBContext.PLUGIN_MAP and extracts the ISqlCompletionProvider for the target database type.

// DefaultSqlSyntaxHandler.complete (lines 80-96)
ISqlSyntaxPlugin sqlSyntaxPlugin = sqlSyntaxPluginMap.get(resolvePluginKey(databaseType));
ISqlCompletionProvider completionProvider = sqlSyntaxPlugin.getSqlCompletionProvider();
return completionProvider.complete(request);

To extend Chat2DB with a new dialect, developers implement the ISqlCompletionProvider interface and register the plugin via the SPI mechanism. The handler automatically resolves the correct provider using resolvePluginKey(databaseType), enabling seamless integration of custom completion logic without modifying core service code.

The Generic Completion Engine

When no plugin is available, GenericSqlCompletionEngine analyzes the SQL context using regex pattern matching against the text surrounding the cursor. The engine first constructs contextual fragments via DefaultSqlSyntaxHandler.buildSqlByKeywordsBeforeCursor and buildSqlByKeywordsAfterCursor, then matches against predefined patterns.

// GenericSqlCompletionEngine.buildCandidates (excerpt)
String beforeSql = DefaultSqlSyntaxHandler.buildSqlByKeywordsBeforeCursor(originalBeforeSql.trim(), dbType);
String afterSql = DefaultSqlSyntaxHandler.buildSqlByKeywordsAfterCursor(originalAfterSql.trim(), dbType);

if (matchesPattern(beforeSql, SELECT_STAR_FROM_PATTERN)) {
    // build table candidates
}
if (matchesPattern(beforeSql, LAST_WORD_WHERE_OR_AND_PATTERN)) {
    // build column candidates for WHERE clause
}

The engine queries metadata (tables, columns, schemas, foreign keys) through Chat2DBContext and IDbMetaData, caching results in MemoryCacheManage to minimize database round trips. It returns SqlCompletionCandidate objects typed as TABLE, COLUMN, VIEW, or JOIN_CLAUSE.

Response Structure and Frontend Integration

Both the plugin provider and generic engine return a SqlCompletionResponse containing cursor offsets and candidate lists. The response includes start and end positions for text replacement, a list of SqlCompletionCandidate objects with labels and insert text, and optional editorHints for syntax guidance.

A typical REST interaction looks like this:

POST /api/sql/completion
{
  "sql": "SELECT * FROM users WHERE ",
  "cursor": 28,
  "dataSourceId": 12,
  "databaseName": "test_db",
  "schemaName": "public",
  "keywordCase": "UPPER"
}

The server responds with typed candidates:

{
  "start": 28,
  "end": 28,
  "candidates": [
    {"type":"COLUMN","label":"id","insertText":"id"},
    {"type":"COLUMN","label":"name","insertText":"name"},
    {"type":"COLUMN","label":"email","insertText":"email"}
  ]
}

Summary

• Layered Architecture: The system uses DbSqlParserController for transport, DbSqlCompletionServiceImpl for orchestration, and pluggable providers for dialect-specific logic.
• Dual-Path Completion: MySQL and registered dialects use optimized plugin implementations via DefaultSqlSyntaxHandler, while unsupported databases fall back to GenericSqlCompletionEngine.
• Pattern-Driven Analysis: The generic engine parses SQL fragments using regex patterns like SELECT_STAR_FROM_PATTERN to determine context (tables vs. columns).
• Metadata Caching: Database metadata is fetched through Chat2DBContext and cached in MemoryCacheManage to improve performance.
• Extensible Design: New dialects integrate by implementing ISqlCompletionProvider and registering with the plugin map.

Frequently Asked Questions

How does Chat2DB decide between the plugin engine and the generic completion engine?

The DbSqlCompletionServiceImpl checks the database type against DatabaseTypeEnum.MYSQL.name() (or other registered plugins). If the dialect has a registered ISqlSyntaxPlugin in Chat2DBContext.PLUGIN_MAP, it delegates to DefaultSqlSyntaxHandler.complete(). Otherwise, it routes to GenericSqlCompletionEngine.complete().

Can I add SQL completion support for a custom database dialect?

Yes. Implement the ISqlCompletionProvider interface in a new plugin module, package it as an SPI service, and ensure it registers in Chat2DBContext.PLUGIN_MAP. The DefaultSqlSyntaxHandler automatically discovers and delegates to your implementation based on the database type key.

What information does the SqlCompletionResponse contain?

The response includes start and end cursor offsets indicating the text range to replace, a candidates list of SqlCompletionCandidate objects (each with type, label, and insertText), and optional editorHints providing syntax suggestions or keyword completions.

How does the generic engine determine what to complete?

GenericSqlCompletionEngine extracts SQL fragments before and after the cursor using DefaultSqlSyntaxHandler.buildSqlByKeywordsBeforeCursor and buildSqlByKeywordsAfterCursor. It matches these fragments against regex patterns (such as SELECT_STAR_FROM_PATTERN or LAST_WORD_WHERE_OR_AND_PATTERN) to infer whether the user needs table names, column names, or join clauses.

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