# How to Create Custom SQL Builders for Different Database Dialects in Chat2DB > Learn to create custom SQL builders for various database dialects in Chat2DB by extending DefaultSqlBuilder. Add new dialect support without altering the core code. - Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB) - Tags: how-to-guide - Published: 2026-07-28 --- **Extend `DefaultSqlBuilder` and expose it through your plugin's `MetaData` implementation to add support for new database dialects without modifying Chat2DB's core codebase.** Chat2DB generates DDL and DML statements through a flexible plugin architecture that keeps the core codebase free of database-specific conditionals. To create custom SQL builders for different database dialects in Chat2DB, you implement the `ISqlBuilder` contract and register your builder through the metadata system. This approach allows you to override only the SQL generation methods that differ from standard syntax while inheriting generic implementations for common operations. ## Understanding the ISqlBuilder Architecture The SQL generation system in Chat2DB revolves around four key components that work together to support multiple database dialects. **`ISqlBuilder`** serves as the unified entry point for all SQL generation. Defined in [`chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java), this interface declares methods for building identifiers, DQL statements, DML operations, and DDL commands. **`DefaultSqlBuilder`** provides the base implementation that most dialects can inherit. Located in the SPI module, this class implements standard SQL generation for common operations like `CREATE TABLE`, `ALTER TABLE`, and pagination. **Dialect-Specific Builders** extend `DefaultSqlBuilder` and override only the methods where the target database deviates from standard SQL. For example, `XUGUDBSqlBuilder` in the XU GU DB plugin customizes table creation logic while reusing the base implementation for other operations. **`MetaData`** implementations expose the dialect-specific builder to the runtime. The `IDbMetaData` interface requires a `getSqlBuilder()` method that returns your custom builder instance, allowing `Chat2DBContext` to locate the correct implementation based on the connection's database type. ## Step 1: Extend DefaultSqlBuilder for Your Dialect Create a new class that extends `DefaultSqlBuilder` and place it in your plugin module under `chat2db-community-plugins/chat2db-community-`. Override only the methods that require dialect-specific syntax. ```java package ai.chat2db.plugin.mydb.builder; import ai.chat2db.spi.DefaultSqlBuilder; import ai.chat2db.spi.model.request.PageLimitRequest; import ai.chat2db.community.domain.api.model.metadata.Table; import ai.chat2db.community.domain.api.model.metadata.TableColumn; import ai.chat2db.community.domain.api.config.TableBuilderConfig; import static ai.chat2db.plugin.mydb.constant.MyDbSqlBuilderConstants.*; public class MyDbSqlBuilder extends DefaultSqlBuilder { @Override public String buildCreateTable(Table table, TableBuilderConfig config) { // Custom CREATE TABLE syntax for MyDB StringBuilder sb = new StringBuilder(); sb.append(SQL_CREATE_TABLE).append("\"").append(table.getName()).append("\" ("); for (TableColumn col : table.getColumnList()) { if (col.getName() == null || col.getColumnType() == null) continue; sb.append("\"").append(col.getName()).append("\" ") .append(mapMyDbType(col.getColumnType())) .append(col.getNullable() == 1 ? " NULL" : " NOT NULL") .append(", "); } sb.setLength(sb.length() - 2); // Remove trailing comma sb.append(");"); return sb.toString(); } private String mapMyDbType(String genericType) { // Example mapping logic return switch (genericType.toUpperCase()) { case "VARCHAR" -> "VARCHAR2"; case "INT" -> "INTEGER"; default -> genericType; }; } @Override public String buildPageLimit(PageLimitRequest request) { // MyDB uses `OFFSET … ROWS FETCH NEXT … ROWS ONLY` int offset = request.getOffset(); int pageSize = request.getPageSize(); StringBuilder sql = new StringBuilder(request.getSql()); sql.append(" OFFSET ").append(offset).append(" ROWS FETCH NEXT ") .append(pageSize).append(" ROWS ONLY"); return sql.toString(); } // Override other methods only when MyDB deviates from the default implementation. } ``` This example demonstrates overriding `buildCreateTable` to handle custom type mapping (VARCHAR to VARCHAR2) and `buildPageLimit` to implement Oracle-style pagination syntax. The class inherits all other SQL generation methods from `DefaultSqlBuilder`. ## Step 2: Expose the Builder via MetaData Implement the `IDbMetaData` interface in your plugin to provide the custom builder instance. The `getSqlBuilder()` method acts as the factory that Chat2DB calls when it needs to generate SQL for your dialect. ```java package ai.chat2db.plugin.mydb; import ai.chat2db.spi.IDbMetaData; import ai.chat2db.spi.ISqlBuilder; import ai.chat2db.plugin.mydb.builder.MyDbSqlBuilder; import java.util.List; import java.util.Arrays; public class MyDbMetaData implements IDbMetaData { @Override public ISqlBuilder getSqlBuilder() { return new MyDbSqlBuilder(); // <-- custom builder } @Override public List getSystemSchemas() { return Arrays.asList("SYS", "SYSTEM"); // example } @Override public String getMetaDataName(String... names) { // MyDB quotes identifiers with back‑ticks return Arrays.stream(names) .filter(name -> name != null && !name.isBlank()) .map(name -> "`" + name + "`") .reduce((a, b) -> a + "." + b) .orElse(""); } // Implement other metadata methods (type maps, default values, etc.) as needed. } ``` The `MetaData` implementation also defines system schemas, identifier quoting rules, and type mappings that the SQL builder may reference during generation. ## Step 3: Plugin Registration and Discovery Place your plugin module under the `chat2db-community-plugins/` directory structure. Ensure your Maven [`pom.xml`](https://github.com/OtterMind/Chat2DB/blob/main/pom.xml) declares the `chat2db-community-spi` dependency to access the base classes and interfaces. ```xml ai.chat2db chat2db-community-spi ${project.version} ``` Chat2DB automatically discovers plugins through the Java SPI mechanism. No additional registration code is required. The framework scans the classpath for `IDbMetaData` implementations and associates them with the appropriate database type based on your plugin configuration. ## Runtime Integration When a user connects to your database dialect, Chat2DB retrieves the appropriate SQL builder through the context. Higher-level services like `DefaultSQLExecutor` and `DefaultMetaService` invoke the builder through the unified `ISqlBuilder` interface without knowing the specific dialect implementation. ```java // Inside Chat2DB core (e.g., DefaultSQLExecutor) ISqlBuilder sqlBuilder = Chat2DBContext.getSqlBuilder(); // Returns MyDbSqlBuilder for MyDB String createTableSql = sqlBuilder.ddl().buildCreateTable(table, config); ``` This design maintains clean separation between the core platform and database-specific logic. The [`java-plugin-contracts.md`](https://github.com/OtterMind/Chat2DB/blob/main/java-plugin-contracts.md) specification outlines the complete contract that custom builders must respect, including preferred patterns for DDL and DML construction. ## Key Source Files The following files illustrate the complete implementation pattern in the Chat2DB source: - **[`ISqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/ISqlBuilder.java)**: Core interface defining all builder segments. Located at [`chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java). - **[`DefaultSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/DefaultSqlBuilder.java)**: Base implementation providing generic SQL generation. Located at [`chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java). - **[`XUGUDBSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/XUGUDBSqlBuilder.java)**: Reference dialect-specific builder overriding table creation logic. Located at [`chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/builder/XUGUDBSqlBuilder.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/builder/XUGUDBSqlBuilder.java). - **[`XUGUDBMetaData.java`](https://github.com/OtterMind/Chat2DB/blob/main/XUGUDBMetaData.java)**: Reference metadata implementation exposing the custom builder. Located at [`chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/XUGUDBMetaData.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/XUGUDBMetaData.java). ## Summary - **Extend `DefaultSqlBuilder`** to create dialect-specific SQL generation while inheriting standard implementations for common operations. - **Implement `IDbMetaData`** and override `getSqlBuilder()` to expose your custom builder to the Chat2DB runtime. - **Place plugins under `chat2db-community-plugins/`** to enable automatic discovery via the SPI mechanism. - **Access builders through `Chat2DBContext.getSqlBuilder()`** to ensure the correct dialect-specific implementation is used for each connection. - **Reference `XUGUDBSqlBuilder`** as a working example of dialect customization in the Chat2DB repository. ## Frequently Asked Questions ### How does Chat2DB determine which SQL builder to use for a specific connection? Chat2DB uses the `Chat2DBContext.getDbMetaData()` method to retrieve metadata based on the connection's `dbType` property. The framework automatically locates the appropriate plugin and calls `IDbMetaData.getSqlBuilder()`, which returns the dialect-specific builder instance. This lookup occurs transparently when services like `DefaultSQLExecutor` request a SQL builder through `Chat2DBContext.getSqlBuilder()`. ### What methods must I override when extending DefaultSqlBuilder? You only need to override methods where your target database diverges from standard SQL syntax. Common overrides include `buildCreateTable` for custom type mappings, `buildAlterTable` for schema modification quirks, and `buildPageLimit` for database-specific pagination syntax (such as Oracle's `OFFSET/FETCH` or MySQL's `LIMIT`). All other SQL generation methods inherit behavior from `DefaultSqlBuilder`. ### Can I use a completely custom implementation instead of extending DefaultSqlBuilder? Yes, you can implement the `ISqlBuilder` interface directly if your database requires radically different SQL syntax. However, extending `DefaultSqlBuilder` is recommended because it provides working implementations for dozens of common operations, reducing the code you must write and maintain. The `XUGUDBSqlBuilder` example in the repository demonstrates the preferred extension approach. ### Where should I place my custom SQL builder code in the project structure? Create a new Maven module under `chat2db-community-plugins/` with the naming convention `chat2db-community-`. Place your builder class in the `builder` package and your metadata implementation in the root plugin package. Ensure your module depends on `chat2db-community-spi` to access the base classes and interfaces required for integration.