Building an ESB with TSQL.APP: A Complete Guide

Introduction

One of the common questions about TSQL.APP is whether it includes Enterprise Service Bus (ESB) functionality. While TSQL.APP is primarily a no-code/low-code framework for building database-driven web applications within SQL Server, it doesn't come with built-in ESB capabilities. However, it does provide the building blocks needed to create your own ESB solution.

In this guide, we'll explore how to build a custom ESB using TSQL.APP's features.

Listen to the Podcast: Building an ESB with TSQL.APP: A Complete Guide

TSQL.APP Integration Capabilities

Before diving into the ESB implementation, let's look at TSQL.APP's relevant integration features:

  1. API Integration

    • HTTP/HTTPS communication via sp_api_fetch procedures
    • REST API integration
    • Integration with external services (e.g., Exactonline, WeFact)

  2. File Transfer

    • FTP/SFTP functionality (sp_api_ftp_put, sp_api_sftp_put)
    • File processing and management

  3. Asynchronous Processing

    • Task server for background operations
    • Queue-based processing

  4. Data Transformation

    • JSON processing
    • CSV processing
    • XML processing

Building an ESB with TSQL.APP

Let's walk through the steps to create a basic ESB implementation using TSQL.APP.

Step 1: Message Storage Setup

First, we need to create the necessary tables for message storage and routing:

-- Message storage table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'ESB_Messages')
CREATE TABLE ESB_Messages (
    MessageID INT IDENTITY(1,1) PRIMARY KEY,
    Source NVARCHAR(100),
    Destination NVARCHAR(100),
    MessageType NVARCHAR(50),
    Payload NVARCHAR(MAX),
    Status NVARCHAR(20),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ProcessedDate DATETIME2,
    RetryCount INT DEFAULT 0
);

-- Routing table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'ESB_Routes')
CREATE TABLE ESB_Routes (
    RouteID INT IDENTITY(1,1) PRIMARY KEY,
    SourceSystem NVARCHAR(100),
    DestinationSystem NVARCHAR(100),
    MessageType NVARCHAR(50),
    TransformationScript NVARCHAR(MAX),
    Active BIT DEFAULT 1
);

Step 2: Message Handler Interface

Create an action script for message submission:

-- Declare variables
DECLARE @MessageSource NVARCHAR(100);
DECLARE @MessageDest NVARCHAR(100);
DECLARE @MessageType NVARCHAR(50);
DECLARE @MessagePayload NVARCHAR(MAX);
DECLARE @SubmitButton NVARCHAR(100);

-- Synchronize with modal values
EXEC sp_api_modal_get_value @name='@MessageSource', @value=@MessageSource OUT;
EXEC sp_api_modal_get_value @name='@MessageDest', @value=@MessageDest OUT;
EXEC sp_api_modal_get_value @name='@MessageType', @value=@MessageType OUT;
EXEC sp_api_modal_get_value @name='@MessagePayload', @value=@MessagePayload OUT;
EXEC sp_api_modal_get_value @name='@SubmitButton', @value=@SubmitButton OUT;

-- Create input interface
EXEC sp_api_modal_text @text=N'ESB Message Handler';
EXEC sp_api_modal_input @name='@MessageSource', @value=@MessageSource OUT, @placeholder='Source System';
EXEC sp_api_modal_input @name='@MessageDest', @value=@MessageDest OUT, @placeholder='Destination System';
EXEC sp_api_modal_input @name='@MessageType', @value=@MessageType OUT, @placeholder='Message Type';
EXEC sp_api_modal_input @name='@MessagePayload', @value=@MessagePayload OUT, @type='textarea', @placeholder='Message Payload (JSON)';
EXEC sp_api_modal_button @name='@SubmitButton', @value='Submit Message', @valueout=@SubmitButton OUT;

IF @SubmitButton IS NOT NULL
BEGIN
    INSERT INTO ESB_Messages (Source, Destination, MessageType, Payload, Status)
    VALUES (@MessageSource, @MessageDest, @MessageType, @MessagePayload, 'NEW');
    
    EXEC sp_api_toast @text='Message submitted successfully', @class='success';
    EXEC sp_api_modal_clear;
END

Step 3: Message Processor Implementation

Create a stored procedure for message processing:

CREATE OR ALTER PROCEDURE sp_ESB_ProcessMessages
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @MessageID INT;
    DECLARE @Source NVARCHAR(100);
    DECLARE @Destination NVARCHAR(100);
    DECLARE @MessageType NVARCHAR(50);
    DECLARE @Payload NVARCHAR(MAX);
    
    -- Select unprocessed messages
    SELECT TOP 1
        @MessageID = MessageID,
        @Source = Source,
        @Destination = Destination,
        @MessageType = MessageType,
        @Payload = Payload
    FROM ESB_Messages
    WHERE Status = 'NEW'
    ORDER BY CreatedDate;
    
    -- Process message
    IF @MessageID IS NOT NULL
    BEGIN
        BEGIN TRY
            -- Find route
            DECLARE @TransformationScript NVARCHAR(MAX);
            SELECT @TransformationScript = TransformationScript
            FROM ESB_Routes
            WHERE SourceSystem = @Source
            AND DestinationSystem = @Destination
            AND MessageType = @MessageType
            AND Active = 1;
            
            -- Execute transformation
            IF @TransformationScript IS NOT NULL
            BEGIN
                EXEC sp_executesql @TransformationScript, 
                     N'@Payload NVARCHAR(MAX)', 
                     @Payload;
            END
            
            -- Update status
            UPDATE ESB_Messages
            SET Status = 'PROCESSED',
                ProcessedDate = GETDATE()
            WHERE MessageID = @MessageID;
        END TRY
        BEGIN CATCH
            UPDATE ESB_Messages
            SET Status = 'ERROR',
                RetryCount = RetryCount + 1
            WHERE MessageID = @MessageID;
        END CATCH
    END
END;

Step 4: Monitoring Dashboard

Create a monitoring interface:

-- Declare variables
DECLARE @RefreshButton NVARCHAR(100);
EXEC sp_api_modal_get_value @name='@RefreshButton', @value=@RefreshButton OUT;

-- Create statistics table
SELECT 
    Status,
    COUNT(*) as MessageCount,
    MAX(CreatedDate) as LastMessageDate
INTO #MessageStats
FROM ESB_Messages
GROUP BY Status;

-- Show statistics
EXEC sp_api_modal_text @text=N'ESB Message Statistics', @class='h2';
EXEC sp_api_modal_table @tmptable='#MessageStats', @print=1;

-- Show recent messages
SELECT TOP 10 
    MessageID,
    Source,
    Destination,
    MessageType,
    Status,
    CreatedDate,
    ProcessedDate
INTO #RecentMessages
FROM ESB_Messages
ORDER BY CreatedDate DESC;

EXEC sp_api_modal_text @text=N'Recent Messages', @class='h3';
EXEC sp_api_modal_table @tmptable='#RecentMessages', @print=1;

EXEC sp_api_modal_button @name='@RefreshButton', @value='Refresh', @valueout=@RefreshButton OUT;

IF @RefreshButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_restart;
    RETURN;
END

Step 5: Task Scheduling

Set up the message processor to run automatically:

EXEC sp_api_add_sql_task 
    @sql = N'EXEC sp_ESB_ProcessMessages',
    @seconds = 60,  -- Run every minute
    @repeat_task_in_seconds = 60,
    @description = N'ESB Message Processor';

Additional Considerations

To create a production-ready ESB, you should also implement:

  1. Error Handling and Retry Logic

    • Implement exponential backoff
    • Set maximum retry attempts
    • Create error notification system

  2. Message Transformation

    • Define transformation templates
    • Implement data mapping
    • Handle different message formats

  3. Logging and Auditing

    • Track all message operations
    • Monitor system performance
    • Store audit trails

  4. Security

    • Implement authentication
    • Add authorization checks
    • Secure message content

  5. Service Management

    • Service registration
    • Service discovery
    • Health monitoring

  6. Load Balancing

    • Message distribution
    • Queue management
    • Resource allocation

  7. Circuit Breaker Patterns

    • Failure detection
    • Service isolation
    • Graceful degradation

Conclusion

While TSQL.APP doesn't include built-in ESB functionality, it provides the necessary components to build a custom ESB solution. By leveraging TSQL.APP's features for HTTP communication, task scheduling, database operations, and UI components, you can create a robust ESB that meets your specific integration needs.

Remember that this implementation can be extended and customized based on your requirements. The example provided here serves as a foundation that you can build upon to create a more comprehensive ESB solution.