SQL Endpoints

SQL endpoints turn a table, view, or stored procedure into a REST resource with OData querying, without you writing any SQL. Four backends are supported (SQL Server, PostgreSQL, MySQL, and SQLite), and Portway picks the correct driver automatically from the connection string in the environment's settings.json, so your endpoint configuration stays identical across providers.

Configuration ​

Create 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"]
}

Configuration properties ​

Column aliases ​

Map internal column names to API-facing names using semicolon syntax in AllowedColumns:

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

The API accepts and returns ProductNumber, ProductName, and Category. Portway maps them to the underlying column names before querying the database.

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

Querying with OData ​

All GET requests support OData query parameters:

Filter operators: eq, ne, gt, lt, ge, le, and, or, contains()

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"
}

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 ​

Include the primary key in the request body:

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 ​

For write operations that require business logic, validation, or audit logging, configure a stored procedure:

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

The procedure receives the HTTP method as @Method (INSERT, UPDATE, PATCH, DELETE):

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 ​

When a stored procedure is more setup than the job needs, or the database cannot provide one at all (SQLite), an endpoint can opt into direct table writes:

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

Portway then generates parameterized INSERT, UPDATE and DELETE statements through the same query compiler that powers OData reads. The mode is deliberately strict:

• AllowedColumns and PrimaryKey are required; an endpoint missing either refuses all writes and logs a configuration error at startup.
• Payload fields outside AllowedColumns reject the whole request rather than being dropped.
• Updates and deletes only ever filter on the primary key, and a key that matches nothing returns 404.
• WriteMode and Procedure are mutually exclusive; pick one strategy per endpoint.

Table mode works on every provider and is what enables full CRUD on SQLite. For production endpoints with business rules, validation chains or audit requirements, stored procedures remain the recommended path. A working example ships in the repository as WMS/Bins against the SQLite demo environment.

Table-valued functions ​

TVFs support parameterized queries, useful for reporting, generated datasets, or complex parameterized lookups that views cannot express.

{
  "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"]
}

Parameters can be sourced from Path, Query, or Header. Example calls:

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

Column-level access control ​

Use AllowedColumns to exclude sensitive fields from API responses and requests. Any column not listed is invisible to callers, it is neither returned in GET results nor accepted in POST/PUT bodies.

{
  "DatabaseObjectName": "Customers",
  "AllowedColumns": [
    "CustomerID",
    "CompanyName",
    "ContactName"
  ]
}

Columns containing credentials, SSNs, financial data, or internal system fields should be excluded explicitly rather than relying on callers not to request them.

Troubleshooting ​

"Column not allowed": The column is not listed in AllowedColumns, or the name does not match exactly (case-sensitive).

"Method not allowed": Add the HTTP method to AllowedMethods, and ensure the stored procedure handles it if one is configured.

No results returned: Verify filter syntax, check that data exists in the target environment, and confirm database permissions for the connection string account.

Performance issues: Add indexes on columns used in $filter and $orderby. Use $top to limit result set size. Consider stored procedures for complex multi-table queries.

To increase log verbosity:

{
  "Logging": {
    "LogLevel": {
      "PortwayApi.Classes.EndpointController": "Debug"
    }
  }
}

Next steps ​

• Proxy Endpoints
• Composite Endpoints
• Environments
• Security