FSharpWorks.Outbox.MSSQL 0.1.22

FSharpWorks.Outbox.MSSQL

An MSSQL backend for FSharpWorks.Outbox: transactional outbox pattern implementation.

Using the MSSQL backend

Please refer to Readme in FSharpWorks.Outbox project for a complete example.

In order to create a backend, MSSQL.createBackend function is used:

Example (F#)

open FSharpWorks.Outbox

let dbOptions =
    { connectionString = ConnectionString "Server=127.0.0.1;Database=service;User Id=sa;Password=Password1"
      schema = SchemaName "dbo"
      name = OutboxName "my-outbox" }


// Use Postgres backend in this example
let mssqlBackend = MSSQL.createBackend dbOptions

Example (C#)

using FSharpWorks.Outbox
using FSharpWorks.Outbox.CSharp

var dbOptions = OutboxFactory.CreateOutboxParams(
    ConnectionString.NewConnectionString("Server=127.0.0.1;Database=service;User Id=sa;Password=Password1"),
    SchemaName.NewSchemaName("dbo"),
    OutboxName.NewOutboxName("my-outbox"));

// Use MSSQL backend in this example
var mssqlBackend = MSSQL.CreateBackend(dbOptions);

The backend then can be used for creating FSharpWorks.Outbox.

Schema

DB Schema can be automatically set up by the backend or it can be set up externally (for example, using DB deployment/provisioning tools).

When an auto-initialisation is called for an outbox named MyOutbox, the following schema will be generated in the DB:

CREATE SEQUENCE MyOutboxSequence
    AS [bigint]
    START WITH 1
    INCREMENT BY 1
    NO CYCLE
    NO CACHE

GO

CREATE TABLE [MyOutbox] (
    [SequenceId] BIGINT NOT NULL DEFAULT NEXT VALUE FOR MyOutboxSequence,
    [RowVersion] ROWVERSION NOT NULL,
    [MessageType] VARCHAR(255) NOT NULL,
    [Payload] VARBINARY(MAX) NOT NULL,
    [DateAddedUtc] DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_MyOutbox] PRIMARY KEY NONCLUSTERED([SequenceId])
        WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
)

GO

CREATE CLUSTERED INDEX [IX_MyOutbox]
    ON [MyOutbox]([RowVersion])
    WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE)

GO

CREATE PROCEDURE MyOutbox_Enqueue (
    @MessageType AS VARCHAR(255),
    @Payload AS VARBINARY(MAX)
) AS
    INSERT INTO [MyOutbox] ([MessageType], [Payload])
    VALUES (@MessageType, @Payload)

GO

CREATE PROCEDURE MyOutbox_Dequeue (
    @MAX_MESSAGES_NUM AS INT = 5
) AS
    WITH OrderedOutboxMessages AS (
        SELECT TOP (@MAX_MESSAGES_NUM) *
        FROM
            [MyOutbox] WITH(UPDLOCK, READPAST)
        WHERE
            [RowVersion] <= MIN_ACTIVE_ROWVERSION()
        ORDER BY
            [RowVersion]
    )
    DELETE
    FROM OrderedOutboxMessages
    OUTPUT DELETED.*

Tuning the schema

The backend only relies on two stored procedures:

• <outbox-name>_Enqueue for enqueuing messages into the outbox
• <outbox-name>_Dequeue for dequeuing messages and dispatching them externally.

As long as they exist (and comply with their expected signatures, see the schema above), the rest of the schema (tables, indices, procedures' implementations) can be changed freely as it is never used by the backend directly.