Lazy replication of tables with NodeJS, Custom APIs and Webhooks in Business Central (Part 1)

“What if I could replicate a table to a system in an entirely different database with a different language in an entirely different OS evenly?”

Wondered what I could do with webhooks which wasn’t just a standard use case. This post isn’t a how-to for creating custom API pages in AL or how webhooks work, other people have done this and they are way better at explaining it than me.

The flow I wanted:

  1. Add generic Business Central AL extension, which exposes the installed APIs and the Field table. This is the main, it will not expose any data tables itself, but will be used to handle the communication.
  2. Add extension with custom API pages for the tables needing replication. (In this example it will contain the table also, but normally not.)
  3. Server (called CopyCat) will call main extension (ALleyCat) and get a list of all API pages within a specific APIGroup (BadSector) and their table structure.
  4. CopyCat will connect to a secondary database and create tables.
  5. CopyCat will copy all records.
  6. CopyCat will subscribe via webhooks for further changes.
  7. Each webhook response is just a record ID and the change type, ex. Updated or Deleted. So CopyCat will either request new/updated record or delete if needed.
    Will keep a list of new or modified records in an array and only request a predefined records pr. sec, so the main service-tier won’t be overloaded.
  8. Periodic request for table changes in BC and if new fields are detected, they are added to the table.

Goals:

  • Use something different than MSSQL, maybe MYSQL or even MongoDB.
  • Use NodeJS.
  • Have it all running on a Raspberry Pi.

What I wanted to end up with was a “lazy” copy of a or multiple tables. This could be used for BI or other processes where changes aren’t needed in the instant they are made.

Pro:

  • OS independent
  • Programming language independent (Your job, I will use NodeJS)
  • DB independent
  • Will not overflow the copy database with writes, as they are delayed by default in the service tier. So multiple changes to the same record are bundled up. (One write on multiple field updates, instead of one for each field)

Cons:

  • Data is not perfectly up-to-date.
  • Not dependent on Azure (So no shares of the blog post by MVPs) 😉
  • Requires a custom API Page for each table

I started by researching a bit on how to use the APIs and webhooks. The standard documentation wasn’t that good, but by mixing it with some of Dmitry Katson‘s great posts, I got what I need to get started.

Quick note, I could properly have used the light version of BC (Cloud) but used the full version (on-prem) just to be sure I had full control over my environment.

To get a table replicated I needed to expose the tables data and supply some form of field type info. But I didn’t want the last part to be a manual step, so I thought; maybe use the Fields table.

But sadly, the field SQLDataType is bugged when shown on an Page of type API, as it showed all fields as being Varchar as seen below.

Think using Varchar for Booleans is a bit over the top. 🙂

So I added my own field to the API Page and exported the formats I remembered them to be.

page 50500 FieldApi
{
    PageType = API;
    Caption = 'field';
    APIPublisher = 'BadSector';
    APIGroup = 'ALleyCat';
    APIVersion = 'beta';
    EntityName = 'field';
    EntitySetName = 'fields';
    SourceTable = Field;
    DelayedInsert = true;
    Editable = false;

    layout
    {
        area(Content)
        {
            repeater(General)
            {
                field(TableNo; TableNo)
                {
                    ApplicationArea = All;
                }
                field(No; "No.")
                {
                    ApplicationArea = All;
                }
                field(TableName; TableName)
                {
                    ApplicationArea = All;
                }
                field(FieldName; FieldName)
                {
                    ApplicationArea = All;
                }
                field(Type; Type)
                {
                    ApplicationArea = All;
                }
                field(Len; Len)
                {
                    ApplicationArea = All;
                }
                field(Class; Class)
                {
                    ApplicationArea = All;
                }
                field(Enabled; Enabled)
                {
                    ApplicationArea = All;
                }
                field(TypeName; "Type Name")
                {
                    ApplicationArea = All;
                }
                field(FieldCaption; "Field Caption")
                {
                    ApplicationArea = All;
                }
                field(RelationTableNo; RelationTableNo)
                {
                    ApplicationArea = All;
                }
                field(RelationFieldNo; RelationFieldNo)
                {
                    ApplicationArea = All;
                }
                field(SQLDataType; SQLDataType)
                {
                    ApplicationArea = All;
                }
                field(OptionString; OptionString)
                {
                    ApplicationArea = All;
                }
                field(ObsoleteState; ObsoleteState)
                {
                    ApplicationArea = All;
                }
                field(ObsoleteReason; ObsoleteReason)
                {
                    ApplicationArea = All;
                }
                field(DataClassification; DataClassification)
                {
                    ApplicationArea = All;
                }
                field(CustomSQLDataType; CustomSQLDataType)
                {
                    ApplicationArea = All;
                }
            }
        }
    }

    var
        CustomSQLDataType: Text[20];

    trigger OnAfterGetRecord()
    begin
        CASE Type of
            Type::Boolean:
                begin
                    CustomSQLDataType := 'tinyint';
                end;

            Type::Code:
                begin
                    CustomSQLDataType := 'nvarchar';
                end;

            Type::Date:
                begin
                    CustomSQLDataType := 'datetime';
                end;

            Type::DateTime:
                begin
                    CustomSQLDataType := 'datetime';
                end;

            Type::Decimal:
                begin
                    CustomSQLDataType := 'decimal';
                end;

            Type::Duration:
                begin
                    CustomSQLDataType := 'int';
                end;

            Type::Integer:
                begin
                    CustomSQLDataType := 'int';
                end;

            Type::Option:
                begin
                    CustomSQLDataType := 'int';
                end;

            Type::Text:
                begin
                    CustomSQLDataType := 'nvarchar';
                end;

            Type::Time:
                begin
                    CustomSQLDataType := 'datetime';
                end;

            else
                CustomSQLDataType := 'Unsupported';
        end;
    end;
}

Now I just needed to find out which fields a given API page relates to. This took a while to figure out, but I ended up making another API page with “Page Metadata” as sourcetable. This made it possible to find API pages with a specific APIGroup and at the same time give me the source table so I knew which fields to expect.

page 50502 "Page Metadata List"
{

    PageType = API;
    APIPublisher = 'BadSector';
    APIGroup = 'ALleyCat';
    APIVersion = 'beta';
    EntityName = 'page';
    EntitySetName = 'pages';
    DelayedInsert = true;
    Editable = false;
    SourceTable = "Page Metadata";
    Caption = 'Page Metadata List';
    ApplicationArea = All;
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(ID; ID)
                {
                    ApplicationArea = All;
                }

                field(Name; Name)
                {
                    ApplicationArea = All;
                }
                field(Caption; Caption)
                {
                    ApplicationArea = All;
                }
                field(Editable; Editable)
                {
                    ApplicationArea = All;
                }
                field(PageType; PageType)
                {
                    ApplicationArea = All;
                }
                field(CardPageID; CardPageID)
                {
                    ApplicationArea = All;
                }
                field(DataCaptionExpr; "DataCaptionExpr.")
                {
                    ApplicationArea = All;
                }
                field(RefreshOnActivate; RefreshOnActivate)
                {
                    ApplicationArea = All;
                }
                field(APIPublisher; APIPublisher)
                {
                    ApplicationArea = All;
                }
                field(APIGroup; APIGroup)
                {
                    ApplicationArea = All;
                }
                field(APIVersion; APIVersion)
                {
                    ApplicationArea = All;
                }
                field(EntitySetName; EntitySetName)
                {
                    ApplicationArea = All;
                }
                field(EntityName; EntityName)
                {
                    ApplicationArea = All;
                }
                field(SourceTable; SourceTable)
                {
                    ApplicationArea = All;
                }
                field(SourceTableView; SourceTableView)
                {
                    ApplicationArea = All;
                }
                field(InsertAllowed; InsertAllowed)
                {
                    ApplicationArea = All;
                }
                field(ModifyAllowed; ModifyAllowed)
                {
                    ApplicationArea = All;
                }
                field(DeleteAllowed; DeleteAllowed)
                {
                    ApplicationArea = All;
                }
                field(DelayedInsert; DelayedInsert)
                {
                    ApplicationArea = All;
                }
                field(ShowFilter; ShowFilter)
                {
                    ApplicationArea = All;
                }
                field(MultipleNewLines; MultipleNewLines)
                {
                    ApplicationArea = All;
                }
                field(SaveValues; SaveValues)
                {
                    ApplicationArea = All;
                }
                field(AutoSplitKey; AutoSplitKey)
                {
                    ApplicationArea = All;
                }
                field(DataCaptionFields; DataCaptionFields)
                {
                    ApplicationArea = All;
                }
                field(SourceTableTemporary; SourceTableTemporary)
                {
                    ApplicationArea = All;
                }
                field(LinksAllowed; LinksAllowed)
                {
                    ApplicationArea = All;
                }
                field(PopulateAllFields; PopulateAllFields)
                {
                    ApplicationArea = All;
                }
            }
        }
    }

}

When calling the API with the following query, I got the respond below:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages?$filter=APIGroup eq ‘CopyCat’

{
    "@odata.context": "https://blog:7048/NAV/api/BadSector/Alleycat/beta/$metadata#companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages",
    "value": [
        {
            "@odata.etag": "W/\"JzQ0OzZtU1pKNzY0Nms4ODhvNzRJR2FZMitOTGhoUjZuRXRESkpXVG1JalFFcEE9MTswMDsn\"",
            "ID": 50510,
            "Name": "TableWithDataToDuplicatedApi",
            "Caption": "TableWithDataToDuplicatedApi",
            "Editable": false,
            "PageType": "API",
            "CardPageID": 0,
            "DataCaptionExpr": "",
            "RefreshOnActivate": false,
            "APIPublisher": "BadSector",
            "APIGroup": "CopyCat",
            "APIVersion": "beta",
            "EntitySetName": "records",
            "EntityName": "record",
            "SourceTable": 50510,
            "SourceTableView": "",
            "InsertAllowed": true,
            "ModifyAllowed": true,
            "DeleteAllowed": true,
            "DelayedInsert": true,
            "ShowFilter": false,
            "MultipleNewLines": false,
            "SaveValues": false,
            "AutoSplitKey": false,
            "DataCaptionFields": "",
            "SourceTableTemporary": false,
            "LinksAllowed": true,
            "PopulateAllFields": true
        }
    ]
}

So now I have a dynamic list of table to replicate:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages?$filter=APIGroup eq ‘CopyCat’
and I can get a list of all the fields for each table exposed by replacing the Table No below:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/fields?$filter=TableNo eq 50510

Shows the flow
This made the initial flow end up like this.

PART 2 (Link here when live)

If you were hoping to see more AL you will be disappointed, part 2 will entirely be NodeJS and will focus on how to make the CopyCat server call Business Central APIs and subscribing to webhooks.

Part 3 will be having it run on a Raspberry Pi plus a bit of performance testing on some real server-grade hardware. It will also include a link to GitHub with the working prototype (all AL and NodeJS code) .

So stay tuned!

Leave a Reply

Your email address will not be published. Required fields are marked *