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

> Discover how Chat2DB's SQL completion system works Explore its layered architecture generic engine and per-dialect plugins for context-aware suggestions across databases

- Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB)
- Tags: internals
- Published: 2026-07-28

---

**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`](https://github.com/OtterMind/Chat2DB/blob/main/DbSqlParserController.java) (lines 101–105), the controller converts this to a **`DbSqlCompletionGetRequest`** and forwards it to the service interface `IDbSqlCompletionService`.

```java
// 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)`**.

```java
// 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.

```java
// 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.

```java
// 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:

```json
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:

```json
{
  "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.