Seamlessly create use case oriented REST APIs based on T-SQL stored procedures.

Name	Version
Dibix
Dibix.Dapper
Dibix.Http.Client
Dibix.Http.Server
Dibix.Http.Server.AspNet
Dibix.Http.Server.AspNetCore
Dibix.Sdk
Dibix.Testing
Dibix.Worker.Abstractions
Dibix.Http.Host
Dibix.Worker.Host

The aim of Dibix is to rapidly create use case oriented REST APIs without writing any boilerplate code, unlike the general approach of designing ASP.NET APIs by writing controllers and actions. It strictly focuses on a hand-written T-SQL stored procedure, which is described with a bit of metadata markup. The APIs and contracts involved are specified in a declarative JSON format. Basically, each URL defined in an API endpoint results in invoking the SQL stored procedure, materializing the relational result into a hierarchical result and then return that to the client.

Dibix follows a database first approach therefore most of the work is done in aSQL server database project.This is where you create use case oriented stored procedures which will later turn into working REST APIs.
We currently offer to split your artifacts into two separate projects:

• Component.Database (DDL)
  ContainsDDL (Data definition language).
  We consider this the default behavior of a database project, where tables, stored procedures, etc. are defined and its intention is to publish these database artifacts to the target database at some point.
• Component.Database.DML (DML)
  ContainsDML (Data manipulation language).
  This project should contain only stored procedures. These will not be published to the target database and instead their statement body will be extracted and compiled into an assembly.

Since DDL gets published at the target database, this means that basically any simple T-SQL statement will end up inside a stored procedure. So far we don't have an exact idea if this is good or bad. The advantage of DDL over DML is that DDL can be easily devop'd at the customer site usingSSMS, whereas the DML is compiled into an assembly and therefore harder to patch, especially during development.

Dibix providesMSBuild targets to integrate it seamlessly into the database project build pipeline. The idea is to install theDibix.Sdk NuGet package into your project, which will automatically add the necessary imports.
Unfortunately NuGet isnot supported in database projects (yet?). Therefore the import has to happen manually. Please check if there is any existing documentation in the product you are working on or askme for assistance.

In this walkthrough, we try to create the following endpoints, that make up a RESTful API:

• Ensure, that there is a folder named "Contracts" at the root of the project
• Create a new .json file named "Person.json" with the following content:

{"AccessRights": [    {"Read":1 },    {"Write":2 },    {"Execute":4 }  ],"Gender": ["Unsure","Male","Female"  ],"BankAccount": {"Id":"uuid","Name":"string"  },"PersonInfo": {"Id": {"type":"int32","isPartOfKey":"true"    },"Name":"string"  },"PersonDetail": {"Id": {"type":"int32","isPartOfKey":"true"    },"Name":"string","Gender":"Gender","AccessRights":"AccessRights","BankAccounts":"BankAccounts*","PetId":"int64?"  },"CreatePersonRequest": {"Name":"string","Gender":"Gender","AccessRights":"AccessRights","PetId":"int64?"  },"Pet":{"Name":"string","Kind":"byte"  },"UpdatePersonRequest": {"Name":"string","Gender":"Gender","AccessRights":"AccessRights","Pets":"Pet*"  }}

The previous example demonstrates the following things:

• Flagged enums (AccessRights)
• Unflagged enums (Gender)
• Primitive types (uuid, string, int32, int64)
• Contract references (#Gender, #AccessRights #BankAccounts; always prefixed with '#')
• Array properties (#BankAccounts*; always suffixed with '*')
• Primary keys ('isPartOfKey')

To pass in multiple ids for the 'DeletePerson' endpoint, we need to create a user-defined table type. Create a new .sql file name 'udt_intset.sql' with the following content:

-- @Name IdSetCREATE TYPE [dbo].[udt_intset]AS TABLE(  [id]INTNOT NULLPRIMARY KEY)

To pass in multiple items ofPet to theUpdatePerson endpoint, we need another user-defined table type. Create a new .sql file name 'udt_petset.sql' with the following content:

-- @Name PetSetCREATE TYPE [dbo].[udt_petset]AS TABLE(  [position] TINYINTNOT NULLPRIMARY KEY, [type]     TINYINTNOT NULL, [name]     NVARCHAR(50)NOT NULL)

• Ensure, that there is a folder named "Endpoints" at the root of the project
• Create a new .json file named "Person.json" with the following content:

{"Person": [    {"method":"GET","target":"GetPersons"    },    {"method":"GET","target":"GetPerson","childRoute":"{personId}"    },    {"method":"POST","target":"CreatePerson","childRoute":"{personId}","body":"CreatePersonRequest","params": {"accessrights":"BODY.Rights"      }    },    {"method":"PUT","target":"CreatePerson","childRoute":"{personId}","body":"UpdatePersonRequest","params": {"pets": {"source":"BODY.Pets","items": {"position":"ITEM.$INDEX","type":"ITEM.Kind"          }        }      }    },    {"method":"PATCH","target":"UpdatePersonName","childRoute":"{personId}/Name/{name}"    },    {"method":"DELETE","target":"DeletePersons"    }  ]}

In the following sections, each endpoint is implemented using a stored procedure. Each procedure is decorated with a few metadata properties inside T-SQL comments in the header.

-- @Name GetPersons-- @Return PersonInfoCREATE PROCEDURE [dbo].[getpersons]ASSELECT [id]= [p].[personid]         , [name]= [p].[name]FROM (VALUES (1, N'Luke')               , (2, N'Maria'))AS [p]([personid], [name])

The previous example describes two metadata properties:

• @Name controls the name of the target
• @Return describes an output.
  For each SELECT a @Return hint has to be defined. The @Return property has several sub properties. In the previous statement we rely on the default which is equivalent to 'ClrTypes:PersonInfo Mode:Multiple'. This means, that multiple rows are returned and each should be mapped to the 'PersonInfo' contract.

GET /api/Person

[  {"id":1,"name":"Luke"  },  {"id":2,"name":"Maria"  }]

-- @Name GetPerson-- @Return ClrTypes:PersonDetail;BankAccount SplitOn:id Mode:SingleCREATE PROCEDURE [dbo].[getperson] @personidINTASSELECT [id]= [p].[personid]         , [name]= [p].[name]         , [gender]= [p].[gender]         , [accessrights]= [p].[accessrights]         , [petid]= [p].[petid]         , [id]= [b].[bankaccountid]         , [name]= [b].[name]FROM (VALUES (1, N'Luke',1/* Male*/,7/* All*/,10)               , (2, N'Maria',2/* Female*/,1/* Read*/,NULL))AS [p]([personid], [name], [gender], [accessrights], [petid])LEFT JOIN (VALUES (100, N'Personal',1)                    , (101, N'Savings',1))AS [b]([bankaccountid], [name], [personid])ON [p].[personid]= [b].[personid]WHERE [p].[personid]= @personid

The previous sample is a bit trickier. Here we expect a single result of the 'PersonDetail' contract. The related entity 'BankAccount' is loaded within the same query. This requires that two entity contracts are specified for the 'ClrTypes' property combined with the ';' separator. The 'SplitOn' is also required to mark where the next related entity starts. In this case 'id' is the bank account id column. If you have more related entities, the split on columns are combined with a ',' separator.
Important: If you are working with multi map, make sure to define a key on each parent entity using theisPartOfKey property as defined in the contractsabove. Otherwise you might end up with duplicated results.

GET /api/Person/1

{"Id":1,"Name":"Luke","Gender":1,"AccessRights":7,"BankAccounts": [    {"Id":100,"Name":"Personal"    },    {"Id":101,"Name":"Savings"    }  ],"PetId":10}

-- @Name CreatePerson-- @Return ClrTypes:int Mode:SingleCREATE PROCEDURE [dbo].[createperson] @name NVARCHAR(255), @gender TINYINT, @accessrights TINYINT, @petidBIGINTAS    DECLARE @personidINT=1    DECLARE @persons TABLE    (        [personid]INTNOT NULL      , [name]         NVARCHAR(128)NOT NULL      , [gender]       TINYINTNOT NULL      , [accessrights] TINYINTNOT NULL      , [petid]BIGINTNULL      ,PRIMARY KEY([personid])    )INSERT INTO @persons ([personid], [name], [gender], [accessrights], [petid])VALUES (@personid, @name, @gender, @accessrights, @petid)SELECT @personid

POST /api/Person

{"Name":"Luke","Gender":1,"Rights":7,"PetId":10}

As you can see here the stored procedure parameteraccessrights doesn't match a property on the body. It will however be mapped fromRights, because a custom parameter mapping using theBODY source was defined in the endpoint configurationabove. This is useful if the names of the client property and the parameter name in the target stored procedure differ.

1

-- @Name UpdatePersonCREATE PROCEDURE [dbo].[updateperson] @personidINT, @name NVARCHAR(255), @gender TINYINT, @accessrights TINYINT, @pets [dbo].[udt_petset] READONLYASUPDATE @personsSET [name]= @name, [gender]= @gender, [accessrights]= @accessrightsWHERE [personid]= @personid-- Do something with @pets, like MERGE

PUT /api/Person/1

{"Name":"Luke","Gender":1,"AccessRights":7,"Pets": [    {"Name":"Pet","Kind":1    }  ]}

The body contains a collection property namedPets. Collections will be mapped to a UDT, which needs to exist in the target database. In this case[dbo].[udt_petset]. The properties of the collection items will be mapped to matching columns of the UDT.
For this endpoint there are some custom parameter mappings defined in the endpoint configurationabove:

• Theposition column of the UDT just serves as a primary key and will be mapped from the index of the item in the collection. This is done using the internal$INDEX property on theITEM source.
• Thetype column of the UDT will be mapped from theKind property of each instance ofPet.
• Thename column doesn't require a mapping and will be automatically mapped from the matchingName property of each instance ofPet.

-- @Name UpdatePersonNameCREATE PROCEDURE [dbo].[updatepersonname] @personidINT, @name NVARCHAR(255)ASUPDATE @personsSET [name]= @nameWHERE [personid]= @personid

PATCH /api/Person/1/Name/Luke

-- @Name DeletePersonsCREATE PROCEDURE [dbo].[deletepersons] @personids [dbo].[udt_intset] READONLYASDELETE [p]FROM @personsAS [p]INNER JOIN @personidsAS [pi]ON [p].[personid]= [pi].[personid]

DELETE /api/Person?personIds[]=1&personIds[]=2

Once you have created all the necessary artifacts, you can build the database project. With the Dibix MSBuild targets automatically integrated into the build pipeline, you end up with a couple of additional files along with the.dacpac file in your output directory:

1. An<Area>.dbx endpoint package file that contains everything to feed theDibix.Http.Host with the REST endpoints and their SQL targets defined in this project.
2. An<OutputName>.dll assembly, that contains only the C# accessors for the SQL artifacts defined in the project. This can be useful in any C# application, such an integration test project or backend application, like theDibix Worker Host, for example.
3. An<Area>.Client.dll assembly, that contains the C# http client which can be used, to contact the REST endpoints, defined within the project. Seethis section for more details.
4. TheOpenAPI definition as<Area>.yml and<Area>.json.

There are currently two hosting applications for different purposes. You can download both as zip from thelatest release. See below for more detail.

This application hosts REST endpoint packages generated by database projects. For first time use, these are the minimum steps, that must be configured in theappsettings.json file within the root folder of the application:

1. The connection string to the database (Database:ConnectionString)
2. The URL of the OIDC authority used to verify incoming JWT bearer tokens (Authentication:Authority)

To register a package, place it in thePackages folder and add it to theHosting:Packages section in theappsettings.json.

This application hosts worker assemblies that can contain long running background jobs, such as a simple worker orService Broker message subscribers.

These workers can be developed using the abstractions defined in theDibix.Worker.Abstractions nuget package.For first time use, the only required setting in theappsettings.json file is the connection string to the database (Database:ConnectionString)

To register a worker assembly, place it in theWorkers folder and add it to theHosting:Workers section in theappsettings.json.

If the project contains any HTTP endpoints, a client assembly and anOpenAPI document are also created during compilation. The client assembly contains a service interface and implementation for each endpoint defined in the project along with their referenced contracts. A host project can consume these client assemblies and register the implementation in the DI container to make the interface available to consumers via IoC.
The implementation is based on theDibix.Http.Client runtime and the generated services may require a few dependencies:

The OpenAPI document will be generated in YAML and JSON format and can be used to generate other artifacts, for example clients in other languages like TypeScript.

In this section, the markup properties to declare input and output of the stored procedure is explained in more detail. The documentation is still in progress. You can also have a look atthese tests for more examples.

PascalCase naming is recommended for referencing actions in API definitions.If all lower case naming is used in T-SQL, this enables you to generate a PascalCase name for the action.

-- @Name GetPersons

{"Person": [    {"target":"GetPersons"    }  ]}

Allows to group actions into a separate (relative) namespace.

-- @Name GetPersons-- @Namespace Group

{"Person": [    {"target":"Group.GetPersons"    }  ]}

To be continued...

In this section the schema for defining contracts is described. The documentation is still in progress. For now you can usethe JSON schema as a reference or have a look atthese tests as samples.

In this section the schema for defining endpoints is described. The documentation is still in progress. For the sake of completeness, you can usethe JSON schema as a reference.

An endpoint JSON starts with a root object. Each property inside the root object maps to an endpoint. An endpoint is similar to a controller in ASP.NET. The property name defines the name of the endpoint. Along with the area name (based on the component name), it controls the URL of the API:api/{areaName}/{endpointName}.

{"EndpointName": [    {"method":"GET","target":"GetEntity","childRoute":"{id}"    }  ]}

Each endpoint object consists of an array, in which the respective actions are defined. To ensure a RESTful API, each action is distinguished by its HTTP verb, which followsCRUD operations, and a unique path.To extend the path to the API, thechildRoute property can be used, which is appended to the path base, extending the route template as such:api/{areaName}/{endpointName}/{childRoute}.

The target property should contain the name of the stored procedure that is invoked by this API action.

To be continued...

By default Dibix endpoints return200 OK for operations that have a result and204 NoContent for those that do not return a result.
However sometimes you need to return a different HTTP status code, for example to indicate that the request is invalid.Ideally you could return a different response body along with a specific HTTP status code, however this is not an easy task and gets very complex with the current way how response types are declared and also validated with the according T-SQL output statements.
Therefore currently it's only possible to return a specific HTTP status code (supported are currently some client and some server errors) along with an additional error code and a message, both which are returned as custom HTTP response headers.

To return an error response, use the T-SQLTHROW statement

Supported:

THROW404017, N'Service not available',1

The error code of the THROW statement is used to indicate the HTTP status code (first three digits) and a custom error code (last three digits) for the application/feature, which can be used for custom handling or resolve a translation for the error message on the client.

HTTP/1.1 404 Not FoundX-Error-Code: 17X-Error-Description: Service not available

For server errors, custom error codes are not supported, since they quite possibly cannot be fixed/handled by the client and could also disclose sensitive information.

Supported:

THROW504000, N'Request with id'' + @id +'' timed out',1

HTTP/1.1 504 Gateway Timeout

This section describes known parameter sources that are already registered and can help to dynamically map a stored procedure parameter from. They are used in theendpoint definition json and are accessible within the parameter configuration.

This source provides access to the query string arguments.

This source provides access to the path segment arguments. For example usePATH.userId to access theuserId parameter in the URLUser/{userId}.

This source provides access to the properties on a JSON object supplied in the body. It requires the body property to be set on the action definition to specify the expected contract of the body.

Sample:

{"Person": [    {"method":"POST","target":"CreatePerson","body":"CreatePersonRequest","params": {"accessrights":"BODY.Rights"      }    }  ]}

This source provides access to the request headers. For exampleHEADER.Authorization.

This source provides access to the HTTP request. It supports the following properties:

This source provides access to the server environment. It supports the following properties:

Popular repositoriesLoading

1. ppg408331701lppg408331701lPublic

   简单的仿微信数字键盘

   Java 1

2. daniel-beckmdaniel-beckmPublic

   Allows configuring slaves to not reconnect when temporarily marked offline (JENKINS-13140)

   JavaScript

3. bedel-lemotioobedel-lemotiooPublic

   annuaire d'entreprise

   Lua

4. DPS03405DPS03405Public

   코로나19 백신 접종 현황 / 산기대 대면 예상일을 확인할 수 있는 API (Spring Boot)

   Shell

5. SymphobrnettSymphobrnettPublic

   C#

6. IoT-ToolkitIoT-ToolkitPublic

   Forked fromIoT-Technology/IoT-Toolkit

   Toolkit is a IoT protocol client CLI for IoT developer and learners. CoAP and MQTT protocol are currently supported. It supports colors, autocompletion, internationalization(chinese, english and ge…

   Java