Chat2DB Database Schema and Data Diff Service Architecture Explained
The Chat2DB diff service uses a layered Java architecture that converts REST requests into Liquibase operations, generating portable SQL diffs between any two JDBC-compliant databases while isolating temporary changelog tables from user schemas.
The OtterMind/Chat2DB repository implements a robust database comparison capability through a clean separation between web API and domain logic. Understanding the Chat2DB database schema and data diff service architecture reveals how the application leverages Liquibase's introspection engine to support MySQL, PostgreSQL, Oracle, and SQL Server without custom diff logic. The implementation spans four logical stages, from HTTP request mapping to SQL filtering.
Four-Stage Diff Pipeline
The service processes schema comparisons through distinct layers that transform user requests into executable SQL.
Request Mapping and Conversion
The flow begins in DbDiffController at /api/db/diff, which receives a StructureDiffRequest payload containing source and target datasource IDs along with database and schema names. The DiffConverter.structureInfo2param() method transforms this HTTP DTO into two DbConnectionDiffRequest objects—one for the source connection and one for the target.
@PostMapping("/diff")
public DataResult<String> diff(@RequestBody StructureDiffRequest request) {
DbConnectionDiffRequest src = diffConverter.structureInfo2param(request.getSource());
DbConnectionDiffRequest tgt = diffConverter.structureInfo2param(request.getTarget());
return DataResult.ok(dbDiffService.diff(src, tgt));
}
Connection Building
The DbDiffServiceImpl.buildConnectInfo() method resolves metadata for each datasource ID using IDbWorkspaceDataSourceService.queryDisplayDataSourceById(). It normalizes JDBC URLs via JdbcUrlUtils.resetUrl() and constructs a ConnectInfo object encapsulating the driver, connection URL, and schema context.
Liquibase Diff Generation
The heavy lifting occurs in DbDiffServiceImpl.generateDiff() and generateChangeLog(). The implementation initializes Liquibase Database objects via DatabaseFactory, then invokes DiffGeneratorFactory.getInstance().compare() to produce a DiffResult. This result feeds into DiffToChangeLog to generate a changelog XML file representing the structural differences.
DiffResult diffResult = generateDiff(initDb(source), initDb(target));
generateChangeLog(diffResult, diffFile);
Execution and SQL Filtering
Finally, DbDiffServiceImpl.executeLiquibaseUpdate() runs the changelog against the target database using Liquibase.update(). To prevent contamination of user schemas, the service creates uniquely-named temporary tables prefixed with chat2db_database_change_<id>. The filter() method (lines 61-73 in DbDiffServiceImpl.java) parses the generated SQL via SqlUtils.parse() and strips any statements referencing these temporary changelog or lock tables before returning the clean DDL to the caller.
Why Liquibase Powers the Engine
Chat2DB delegates database introspection to Liquibase rather than implementing custom comparison logic. This delegation enables immediate compatibility with any JDBC-compliant database that Liquibase supports, including exotic configurations without database-specific code in the Chat2DB repository. The DiffGeneratorFactory and DatabaseFactory classes from the Liquibase library handle catalog and schema snapshotting, while Chat2DB manages the orchestration and cleanup.
Isolating Temporary Changelog Tables
To avoid polluting target databases, the architecture employs temporary changelog tables with generated IDs. During executeLiquibaseUpdate(), the service configures Liquibase to use these temporary table names instead of the default DATABASECHANGELOG. After the diff completes, the implementation deletes the entire temporary directory via FileUtil.del(filePath), ensuring no metadata tables remain in the user's schema.
The filtering logic specifically removes CREATE TABLE, DROP TABLE, INSERT, and DELETE statements targeting these temporary tables, returning only the relevant schema migration SQL to the API consumer.
Key Implementation Files
Practical Usage Examples
Calling the REST API
Invoke the diff service via HTTP POST with datasource IDs and schema names:
curl -X POST http://localhost:10825/api/db/diff \
-H "Content-Type: application/json" \
-d '{
"source": {
"dataSourceId": 123,
"databaseName": "prod_db",
"schemaName": "public"
},
"target": {
"dataSourceId": 124,
"databaseName": "test_db",
"schemaName": "public"
}
}'
Response:
{
"code": 0,
"message": "OK",
"data": "CREATE TABLE new_table (\n id BIGINT PRIMARY KEY,\n name VARCHAR(255)\n);\n\nALTER TABLE existing_table ADD COLUMN extra VARCHAR(100);\n"
}
Programmatic Service Access
Inject IDbDiffService to compare databases within Java code:
@Autowired
private IDbDiffService diffService;
public void compareSchemas() {
DbConnectionDiffRequest src = new DbConnectionDiffRequest(123L, "prod_db", "public");
DbConnectionDiffRequest tgt = new DbConnectionDiffRequest(124L, "test_db", "public");
String sqlDiff = diffService.diff(src, tgt);
System.out.println(sqlDiff);
}
Customizing the Filter Logic
Extend DbDiffServiceImpl to modify SQL post-processing:
@Service
public class CustomDiffService extends DbDiffServiceImpl {
@Override
protected String filter(String raw, String dbType, String tableName, String tableName2) {
String base = super.filter(raw, dbType, tableName, tableName2);
// Remove generator comments
return base.replaceAll("(?m)^-- CHAT2DB.*\\n", "");
}
}
Summary
- Layered architecture: The web layer remains thin, delegating to domain services that orchestrate Liquibase operations.
- Liquibase integration:
DbDiffServiceImplusesDiffGeneratorFactoryandDiffToChangeLogto generate portable diffs across database types. - Temporary table isolation: Unique
chat2db_database_change_<id>tables prevent schema pollution, withfilter()removing them from final output. - Key classes:
DbDiffControllerhandles HTTP mapping,DiffConvertertransforms requests, andDbDiffServiceImplmanages the comparison lifecycle. - Extensibility: Developers can subclass
DbDiffServiceImplto customize SQL filtering or extend connection building logic.
Frequently Asked Questions
How does Chat2DB prevent temporary Liquibase tables from appearing in the final diff output?
The DbDiffServiceImpl.filter() method parses the raw SQL generated by Liquibase using SqlUtils.parse() and programmatically removes any statements referencing the temporary changelog or lock tables. This ensures the returned SQL contains only relevant schema changes, not the internal tracking tables created during the comparison process.
What databases are supported by Chat2DB's diff service?
Any JDBC-compliant database supported by Liquibase works with the Chat2DB architecture, including MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and others. The service relies on Liquibase's DatabaseFactory for introspection rather than database-specific comparison code, providing broad compatibility without custom implementations.
Can I use the diff service programmatically without the REST API?
Yes, autowire the IDbDiffService interface and invoke diff(DbConnectionDiffRequest source, DbConnectionDiffRequest target) directly. This bypasses the web layer entirely, accepting domain request objects containing datasource IDs, database names, and schema names to generate the SQL diff string.
Where does the actual database comparison logic reside?
The comparison logic resides in DbDiffServiceImpl.java within the domain core module. This class coordinates the entire pipeline: building ConnectInfo objects, initializing Liquibase Database instances, generating the DiffResult, executing the changelog, and filtering the output. The web layer (DbDiffController) merely handles HTTP request mapping and delegates to this service.
Have a question about this repo?
These articles cover the highlights, but your codebase questions are specific. Give your agent direct access to the source. Share this with your agent to get started:
curl -s "https://instagit.com/install.md" 
Maintain an open-source project? Get it listed too →