SQL Endpoints

SQL endpoints expose a table, view, stored procedure or table-valued function as a REST resource. A plain GET returns rows (default $top=10); OData query options are optional on every SQL endpoint and need no configuration.

Supported providers are:

• SQL Server
• PostgreSQL
• MySQL/MariaDB
• SQLite

The provider is detected from the connection string in the environment's settings.json; endpoint configuration is the same for every provider. Provider differences: SQL Providers.

Configuration ​

Each endpoint is defined in endpoints/SQL/{EndpointName}/entity.json:

{
  "DatabaseObjectName": "Products",
  "DatabaseSchema": "dbo",
  "PrimaryKey": "ProductID",
  "AllowedColumns": [
    "ProductID",
    "ProductName",
    "Category",
    "Price",
    "InStock"
  ],
  "AllowedMethods": ["GET", "POST", "PUT", "DELETE"],
  "AllowedEnvironments": ["dev", "test", "prod"]
}

All properties, types and defaults: Entity configuration.

Column aliases ​

An AllowedColumns entry DbColumn;PublicName maps a database column to a public name:

{
  "DatabaseObjectName": "Items",
  "AllowedColumns": [
    "ItemCode;ProductNumber",
    "Description;ProductName",
    "Assortment;Category"
  ]
}

The API accepts and returns ProductNumber, ProductName and Category; queries use the database column names.

GET /api/prod/Items?$select=ProductNumber,ProductName&$filter=Category eq 'Electronics'

Querying with OData ​

GET requests accept these OData query options:

Filter operators: eq, ne, gt, lt, ge, le, and, or, not, contains(), startswith(), endswith().

GET /api/prod/Products?$filter=Price gt 100 and InStock eq true&$orderby=Price desc&$top=25

Response format ​

{
  "success": true,
  "count": 25,
  "value": [
    { "ProductID": "abc123", "ProductName": "Gadget", "Price": 99.99 }
  ],
  "nextLink": "/api/prod/Products?$top=25&$skip=25"
}

Related data with $expand ​

A to-one relationship to another SQL endpoint makes the related row available through $expand:

{
  "DatabaseObjectName": "Items",
  "DatabaseObjectType": "Table",
  "AllowedColumns": ["ItemCode;ProductNumber", "Assortment;AssortmentID"],
  "Relationships": [
    { "Name": "Category", "Target": "Assortments", "LocalColumn": "Assortment", "TargetColumn": "AssortmentID" }
  ]
}

GET /api/prod/Products?$expand=Category

The related row is nested under the navigation name and limited to the target endpoint's AllowedColumns. Supported on Table and View endpoints with to-one navigations; table-valued functions return 400. Details: Expanding Related Data.

Write operations ​

POST: create a record ​

POST /api/prod/Products
Content-Type: application/json

{
  "ProductName": "New Gadget",
  "Category": "Electronics",
  "Price": 299.99,
  "InStock": true
}

PUT: update a record ​

The request body includes the primary key:

PUT /api/prod/Products
Content-Type: application/json

{
  "ProductID": "abc123",
  "ProductName": "Updated Gadget",
  "Price": 249.99
}

DELETE: remove a record ​

DELETE /api/prod/Products?id=abc123

Stored procedures ​

Write operations with business logic, validation or audit logging use a stored procedure. Stored procedures require SQL Server, PostgreSQL or MySQL/MariaDB; SQLite uses table write mode.

{
  "DatabaseObjectName": "ServiceRequests",
  "DatabaseSchema": "dbo",
  "Procedure": "dbo.sp_ManageServiceRequests",
  "AllowedMethods": ["GET", "POST", "PUT", "DELETE"],
  "AllowedColumns": ["RequestId", "CustomerCode", "Title", "Status"]
}

The procedure receives the operation as @Method (INSERT, UPDATE, PATCH, DELETE). MERGE requests arrive as PATCH:

CREATE PROCEDURE [dbo].[sp_ManageServiceRequests]
    @Method      NVARCHAR(10),
    @id          UNIQUEIDENTIFIER = NULL,
    @CustomerCode NVARCHAR(20) = NULL,
    @Title       NVARCHAR(100) = NULL,
    @Status      NVARCHAR(20) = NULL,
    @UserName    NVARCHAR(50) = NULL
AS
BEGIN
    IF @Method = 'INSERT'
        -- insert logic
    ELSE IF @Method = 'UPDATE'
        -- update logic; use ISNULL(@Field, Field) to handle partial updates
    ELSE IF @Method = 'DELETE'
        -- delete logic
END

Table write mode ​

With WriteMode: Table, writes go directly to the table, without a stored procedure. This is the write path on SQLite.

{
  "DatabaseObjectName": "Bins",
  "WriteMode": "Table",
  "PrimaryKey": "Id",
  "AllowedMethods": ["GET", "POST", "PUT", "PATCH", "DELETE"],
  "AllowedColumns": ["Id", "Code", "Zone", "CapacityUnits"],
  "RequiredColumns": ["Code", "Zone"]
}

Portway generates parameterized INSERT, UPDATE and DELETE statements with the OData query compiler. Rules:

• AllowedColumns and PrimaryKey are required; without either, all writes are refused and a configuration error is logged at startup.
• A payload field outside AllowedColumns rejects the request.
• Updates and deletes filter on the primary key; an unmatched key returns 404.
• WriteMode and Procedure are mutually exclusive.

Table mode works on every provider. Example: WMS/Bins in the SQLite demo environment.

Table-valued functions ​

Table-valued functions accept parameters, for queries a view cannot express. They require SQL Server or PostgreSQL.

{
  "DatabaseObjectName": "fn_GetDepartmentUsers",
  "DatabaseSchema": "dbo",
  "DatabaseObjectType": "TableValuedFunction",
  "FunctionParameters": [
    {
      "Name": "DepartmentId",
      "SqlType": "int",
      "Source": "Path",
      "Position": 1,
      "Required": false,
      "DefaultValue": "DEFAULT",
      "ValidationPattern": "^[0-9]+$"
    },
    {
      "Name": "UserCount",
      "SqlType": "int",
      "Source": "Query",
      "Required": false,
      "DefaultValue": "DEFAULT"
    }
  ],
  "AllowedColumns": [
    "user_id;UserId",
    "first_name;FirstName",
    "department_name;DepartmentName"
  ],
  "AllowedMethods": ["GET"]
}

Parameter sources are Path, Query and Header. Header values are client-supplied; parameters named in Tenancy receive the token's tenant value. Example calls:

GET /api/dev/Departments/5?UserCount=25
GET /api/dev/Departments?UserCount=50&$top=20&$orderby=FirstName

Column-level access control ​

Columns missing from AllowedColumns are not returned by GET and not accepted in POST or PUT bodies. Leave credentials, personal identifiers, financial data and internal system fields out of AllowedColumns.

Troubleshooting ​

Debug logging:

{
  "Serilog": {
    "MinimumLevel": {
      "Default": "Debug"
    }
  }
}

Next steps ​

• Proxy Endpoints
• Composite Endpoints
• Environments
• Security