How Chat2DB Handles SQL Query Generation: A Deep Dive into DefaultSqlBuilder

Chat2DB centralizes SQL query generation through a fluent builder pattern implemented in DefaultSqlBuilder, which constructs DDL, DML, and pagination statements by combining metadata objects with dialect-specific constants.

Chat2DB is an open-source database management tool developed by OtterMind that requires robust, database-agnostic SQL generation capabilities. The project implements a comprehensive SQL builder architecture in the chat2db-community-spi module, leveraging interfaces like IDmlSqlBuilder and IDqlSqlBuilder to standardize statement construction across supported database engines.

The DefaultSqlBuilder Architecture

At the core of Chat2DB's SQL generation lies the DefaultSqlBuilder class found at chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java. This class implements a comprehensive suite of builder interfaces including ISqlBuilder, IIdentifierSqlBuilder, IDqlSqlBuilder, IDmlSqlBuilder, IDdlSqlBuilder, IDatabaseSqlBuilder, ISchemaSqlBuilder, ITableSqlBuilder, and IViewSqlBuilder.

The implementation follows a fluent API pattern where accessor methods such as identifier(), dql(), dml(), and ddl() return this, allowing chained construction of SQL statements. This design enables a unified entry point for generating any statement type while maintaining type safety through interface segregation.

DDL Generation: Creating and Modifying Schema Objects

The builder generates Data Definition Language (DDL) statements by concatenating static fragments defined in DefaultSqlBuilderConstants.java with metadata from model objects like Table, Column, and Database.

For table creation, the buildCreateTable() method combines the SQL_CREATE_TABLE constant with column and index definitions. The implementation at DefaultSqlBuilder.java:141‑182 handles complex scenarios including unique indexes and primary key constraints.

For schema modifications, buildAlterTable() delegates structural diffing to DBStructUtils.java. This utility compares old and new table structures to generate precise ALTER TABLE statements that add, drop, or modify columns while preserving existing data.

DML Generation: Query and Data Manipulation

Data Manipulation Language (DML) construction follows a templated approach. The buildTemplate() method (implemented at DefaultSqlBuilder.java:665‑779) selects appropriate helper methods—getInsertSql(), getUpdateSql(), getDeleteSql(), or getSelectSql()—based on the requested operation type.

The buildByQueryResult() method (lines 222‑259) provides advanced functionality by reconstructing mutating SQL from QueryResponse objects. It inspects primary key columns using Chat2DBContext.getDbMetaData() to generate UPDATE or DELETE statements that reproduce data changes observed in query results.

Pagination and Ordering

Chat2DB handles result set pagination through buildPageLimit(), which appends LIMIT offset, pageSize clauses (or LIMIT pageSize when offset is zero) as implemented at lines 241‑267.

For dynamic ordering, buildOrderBy() utilizes JSqlParser to parse existing SELECT statements, inject OrderByElement objects built from metadata, and return modified SQL. Error handling at lines 315‑321 captures parsing failures using the defined key ERROR_KEY_SQL_BUILDER_ORDER_BY_FAILED for consistent exception management.

Dialect Extensibility

While DefaultSqlBuilder provides database-agnostic logic, specific dialects extend this base through inheritance. For example, MysqlSqlBuilder and PostgreSQLSqlBuilder override methods in their respective plugin modules to emit dialect-specific syntax—such as MySQL's backtick quoting or PostgreSQL's LIMIT/OFFSET variations—while reusing the common construction logic.

This architecture allows Chat2DB to support new database engines by implementing only the divergent portions of SQL generation, maintaining consistency in the core builder patterns.

Practical Implementation Examples

Building a Simple SELECT Statement

DefaultSqlBuilder sqlBuilder = new DefaultSqlBuilder();
String sql = sqlBuilder.dql()
                       .buildSelectTable("test_db", "public", "users");

This returns SELECT * FROM test_db.public.users, demonstrating the fluent API for basic query construction.

Creating a Table with Constraints

Table table = new Table()
    .setName("employee")
    .setColumnList(Arrays.asList(
        new TableColumn()
            .setName("id")
            .setColumnType("BIGINT")
            .setNullable(0),
        new TableColumn()
            .setName("name")
            .setColumnType("VARCHAR(255)")
            .setNullable(1)
    ))
    .setIndexList(Collections.singletonList(
        new TableIndex()
            .setName("idx_name")
            .setType(IndexTypeEnum.UNIQUE.getName())
            .setColumnList(Collections.singletonList(
                new TableIndexColumn().setColumnName("name")
            ))
    ));

String createSql = new DefaultSqlBuilder()
    .dml()
    .buildCreateTable(table, new TableBuilderConfig());

This generates a complete CREATE TABLE statement with a unique index definition.

Generating UPDATE from Query Results

QueryResponse response = /* result of a SELECT */;
String updateSql = new DefaultSqlBuilder()
    .dml()
    .buildByQueryResult(response);

The method inspects row data and primary keys to produce UPDATE ... SET ... WHERE ... statements for each changed row.

Adding Dynamic Ordering

String original = "SELECT id, name FROM employee";
List<OrderBy> orderBys = Collections.singletonList(
    new OrderBy().setColumnName("name").setAsc(true)
);
String ordered = new DefaultSqlBuilder()
    .dql()
    .buildOrderBy(original, orderBys);

The resulting SQL becomes SELECT id, name FROM employee ORDER BY name ASC, with parsing handled by JSqlParser.

Implementing Pagination

String baseSql = "SELECT * FROM employee";
PageLimitRequest request = new PageLimitRequest()
    .setSql(baseSql)
    .setOffset(20)
    .setPageSize(10);
String paged = new DefaultSqlBuilder()
    .dql()
    .buildPageLimit(request);

This produces SELECT * FROM employee LIMIT 20,10.

Summary

• Chat2DB uses a centralized builder pattern through DefaultSqlBuilder to generate all SQL statement types consistently across the application.
• Interface segregation allows specific handling of identifiers, DDL, DML, and database objects while maintaining a fluent API.
• Helper utilities like DBStructUtils handle complex schema diffs for ALTER TABLE generation.
• JSqlParser integration enables safe modification of existing SQL for ordering and pagination without string manipulation risks.
• Dialect inheritance allows database-specific plugins to override only necessary methods while inheriting common SQL construction logic.

Frequently Asked Questions

What is DefaultSqlBuilder in Chat2DB?

DefaultSqlBuilder is the core implementation class located at chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java that implements multiple SQL builder interfaces. It provides a fluent API for constructing DDL, DML, and DQL statements by combining metadata objects with SQL fragments defined in DefaultSqlBuilderConstants.java.

How does Chat2DB build ALTER TABLE statements?

ALTER TABLE generation delegates structural comparison to DBStructUtils.java. This utility diffs the old and new Table metadata objects to determine which columns to add, drop, or modify, then constructs the appropriate ALTER statements. The base DefaultSqlBuilder.buildAlterTable() method orchestrates this process and handles the final SQL assembly.

Can Chat2DB generate SQL from query results?

Yes, through the buildByQueryResult() method in DefaultSqlBuilder. This method accepts a QueryResponse object containing result set data and reconstructs INSERT, UPDATE, or DELETE statements that would reproduce the observed data state. It uses Chat2DBContext.getDbMetaData() to resolve column types and primary keys for accurate WHERE clause generation.

How does Chat2DB support multiple database dialects?

Chat2DB supports multiple dialects through an inheritance model where dialect-specific classes like MysqlSqlBuilder and PostgreSQLSqlBuilder extend DefaultSqlBuilder. These subclasses override specific methods to emit dialect-specific syntax—such as identifier quoting styles or pagination clauses—while inheriting the core SQL construction logic from the base class.

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