Add CDC (Change Data Capture) Support for Ballerina MsSQL Connector

[niveathika]

Problem

Currently, the Ballerina MSSQL connector lacks native support for CDC (mechanism for tracking and capturing changes in a database table), limiting its usability for real-time data streaming and event-driven applications.

Proposed Solution

Debezium-Based Approach
Integrate with Debezium, a log-based CDC solution, for a more efficient and real-time change tracking mechanism.
Debezium can capture changes by reading SQL Server’s transaction logs, reducing database load compared to polling.

Alternatives

Polling Using JDBC
Implement a scheduled polling mechanism that queries the CDC tables (cdc.<table_name>_CT) at a defined interval.
Use JDBC queries to fetch changes from SQL Server’s built-in CDC tables.
Simple but more costly in terms of work needed.

[niveathika]

Design moved to spec repo ballerina-platform/ballerina-spec#1342

[niveathika]

The design has changed to supporting CDC for all databases

[niveathika]

Progress Update

Completed Subtasks

• Implementation: MySQL & MSSQL listeners, CDC service, dependency resolution and drivers code (4.5 days)
• Compiler plugin: validation logic and basic code actions (3 days)
• Compiler plugin tests (1 day)
• Test cases for MySQL listener (1 day)
• Test cases for MSSQL listener (1 day)
• API documentation, Module Documentation and Specification (0.5 day)
• GraalVM support (1 day)
• Examples and draft blog post (2 examples – 2 days)
• Demo of CDC functionalities (1 day)
• Code Review and followup (1 day)

Pending Tasks

• PostgreSQL support and testing (1 days)
• OracleDB support and testing (1 days)
• Improve usability (5 days)
  - Completions for CDC listener and service
  - Code actions / quick fixes for compiler plugin errors

[niveathika]

Progress Update

Support for both PostgreSQL and Oracle has been added.
The code is available at: https://github.com/niveathika/module-ballerinax-cdc

The code review is scheduled for Monday, and we're getting close to the first release.

Identified Improvements for Future Iteration

During development, the following potential enhancements were identified:

• Improved usability through code completions and quick fixes.
• Constraint support for better schema handling.
• JSON data support to allow renaming columns in emitted events.
• Performance config inference based on service declaration patterns (e.g., skipping operations from undefined functions).
• Native log exposure via configurable options for easier debugging.

[niveathika]

The code review is finished and I have followed up and updated the code with the suggestions.

Currently this will be put on hold until the concern raised on the spec proposal is resolved. ballerina-platform/ballerina-spec#1341 (comment)

I have created a sub task for the following improvements pending,

• Improve CDC Module Usability with Code Completions and Quick Fixes #7864
• Expose Native Logs via Configurable Options for Easier Debugging #7866