Changelog

Version 2.8.1 (2024-05-10)

Upgrade Npgsql from 8.0.0 to 8.0.3
Fix null dereference of a possibly null build warning.

Client Version 1.2.1

New option UseHsts: Adds middleware for using HSTS, which adds the Strict-Transport-Security header. See https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.builder.

{
    //
    //...
    //
    "Ssl": {
        "HttpsRedirection": true,
        //
        // Adds middleware for using HSTS, which adds the Strict-Transport-Security header. See https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.builderhstsbuilderextensions.usehsts?view=aspnetcore-2.1
        //
        "UseHsts": true
    }
    //
    //...
    //
}

{
    //
    //...
    //
    "Ssl": {
        "HttpsRedirection": true,
        //
        // Adds middleware for using HSTS, which adds the Strict-Transport-Security header. See https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.builderhstsbuilderextensions.usehsts?view=aspnetcore-2.1
        //
        "UseHsts": true
    }
    //
    //...
    //
}

Version 2.8.0 (2024-05-02)

Full Changelog

New Option: Dictionary<string, StringValues> CustomRequestHeaders

Custom request headers dictionary that will be added to NpgsqlRest requests.

Note: these values are added to the request headers dictionary before they are sent as a context or parameter to the PostgreSQL routine and as such not visible to the browser debugger.

TsClient Version 1.7.0

Sanitaze generated TypeScript names.
Add SkipRoutineNames, SkipFunctionNames and SkipPaths options.

Client Version 1.2.0

Bugfix with missing dictionary key when using external auth.
New Config section:

"Config": {
    //
    // Expose current configuration to the endpoint for debugging and inspection. Note, the password in the connection string is not exposed.
    //
    "ExposeAsEndpoint": "/config",
    //
    // Add the environment variables to configuration first.
    //
    "AddEnvironmentVariables": false
}

"Config": {
    //
    // Expose current configuration to the endpoint for debugging and inspection. Note, the password in the connection string is not exposed.
    //
    "ExposeAsEndpoint": "/config",
    //
    // Add the environment variables to configuration first.
    //
    "AddEnvironmentVariables": false
}

Support for the CustomRequestHeaders option.
Support for the new options in TsClient.

Version 2.7.1 (2024-04-30)

Full Changelog

NpgsqlRest 2.7.1

Small fix on the Login endpoint that fixed the problem with the custom message not being written to the response in some rare circumstances.
Redesigned the auth module and changed the access modifiers to the public of the ClaimTypes Dictionary to be used with the client application.

NpgsqlRest Client 1.1.0

Client application new release with massive improvements.
External auth logins implementation (Google, LinkedIn, GitHub)

Version 2.7.0 (2024-04-17)

Full Changelog

New callback option: Action<NpgsqlConnection, Routine, RoutineEndpoint, HttpContext>? BeforeConnectionOpen.

This is used to set the application name parameter (for example) without having to use the service provider. It executes before the new connection is open for the request. For example:

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    BeforeConnectionOpen = (NpgsqlConnection connection, Routine routine, RoutineEndpoint endpoint, HttpContext context) =>
    {
        var username = context.User.Identity?.Name;
        connection.ConnectionString = new NpgsqlConnectionStringBuilder(connectionString)
        {
            ApplicationName = string.Concat(
                    "{\"app\":\"",
                    builder.Environment.ApplicationName,
                    username is null ? "\",\"user\":null}" : string.Concat("\",\"user\":\"", username, "\"}"))
        }.ConnectionString;
    }
}

Version 2.6.1 (2024-04-16)

Full Changelog

Don't write the response body on status 205, which is forbidden.

Version 2.6.0 (2024-04-16)

Full Changelog

Improved error handling. Two new options are available:

`ReturnNpgsqlExceptionMessage`:

Set to true to return message property on exception from the NpgsqlException object on response body. The default is true.
Set to false to return empty body on exception.

`PostgreSqlErrorCodeToHttpStatusCodeMapping`

Dictionary setting that maps the PostgreSQL Error Codes (see the errcodes-appendix to HTTP Status Codes.

Default is { "57014", 205 } which maps PostgreSQL query_canceled error to HTTP 205 Reset Content. If the mapping doesn't exist, the standard HTTP 500 Internal Server Error is returned.

Version 2.5.0 (2024-04-15)

Full Changelog

New endpoint parameter option BufferRows.

Now it's possible to set the number of buffered rows that are returned before they are written to HTTP response on an endpoint level.

It is also possible to set this endpoint parameter as a comment annotation:

comment on function my_streaming_function() is 'HTTP GET
bufferrows 1';

comment on function my_streaming_function() is 'HTTP GET
bufferrows 1';

See the full comment annotation list here.

Setting this parameter to 1 is useful in the HTTP streaming scenarios.

New TsClient plugin options and fixes

/// <summary>
/// Module name to import "baseUrl" constant, instead of defining it in a module.
/// </summary>
public string? ImportBaseUrlFrom { get; set; } = importBaseUrlFrom;

/// <summary>
/// Module name to import "pasreQuery" function, instead of defining it in a module.
/// </summary>
public string? ImportParseQueryFrom { get; set; } = importParseQueryFrom;

Version 2.4.2 (2024-04-14)

Full Changelog

Fix double logging the same message on the connection notice.

Version 2.4.1 (2024-04-12)

Full Changelog

Fix missing Text type for types in need of JSON escaping.

Version 2.4.0 (2024-04-08)

Full Changelog

Remove AotTemplate subproject directory. AOT Template is now NpgsqlRestTestWebApi with full configuration for the entire application.
The auth handler doesn't complete the response if it doesn't have to on login and logout.
Changed wording Schema to Scheme everywhere because that appears to be standard with the auth (unlike with databases).
Changed IRoutineSource interface to:
- Have CommentsMode mode fully exposed with getters and setters.
- Have Query property exposed.
- Every parameter for the query exposed (SchemaSimilarTo, SchemaNotSimilarTo, etc).
Replaced interfaces with concrete implementation for better performance.
Fixed bug with service scope disposal when using ConnectionFromServiceProvider.
Obfuscated auth parameters in logs (with ObfuscateAuthParameterLogValues option).
Implemented SerializeAuthEndpointsResponse to serialize the auth (log in or log out) when it's possible.
Fixed ParameterValidationValues to use NpgsqlRestParameter instead of NpgsqlParameter to expose the actual parameter name.
Added IsAuth read-only property to the endpoint.
Fixed automatic port detection in the code-gen plugins.
Added CommentHeaderIncludeComments to the TsClient plugin.

Version 2.3.1 (2024-04-05)

Full Changelog

Fix the "Headers are read-only, response has already started." error during the logout execution.

Version 2.3.0 (2024-04-04)

Full Changelog

Login Endpoints can return text messages.
A new option that supports this feature: AuthenticationOptions.MessageColumnName.
Login endpoints always return text.
Interface IRoutineSource exposes string Query { get; set; }. If the value doesn't contain blanks it is interpreted as the function name.
TsClient plugin new version (1.2.0):
- New TsClweint option BySchema. If true, create a file by schema. The default is false.
- Fix handling login endpoints.
- Bugfixes.

Version 2.2.0 (2024-04-02)

Full Changelog

Login endpoints
Logout endpoints
Small name refactoring (ReturnRecordNames -> ColumnNames)

To enable authentication, the authentication service first needs to be enabled in the application:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddAuthentication().AddCookie();

The login-enabled endpoints must return a named record.

The program will result in the ArgumentException exception if the login-enabled routine is either of these:

void
returns simple value
returns set of record unnamed records

The login operation will be interpreted as an unsuccessful login attempt and return the status code 401 Unauthorized without creating a user identity if either:

The routine returns an empty record.
The returned record includes a status column, and the value of the status column name is:
- False for boolean types.
- Not 200 for numeric types.

The login operation will be interpreted as a successful login attempt and return the status code 200 OK with creating a new user identity if either:

The routine returns a record without status status column.
The returned record includes a status column, and the value of the status column name is:
- True for boolean types.
- 200 for numeric types.

To authorize a different authorization scheme, return a schema column name with the value of that schema.

Any other records will be set as new claims for the created user identity on successful login, where:

The column name is claim type. This type will by default try to match the constant name in the ClaimTypes class to retrieve the value. Use the [UseActiveDirectoryFederationServicesClaimTypes Option][https://vb-consulting.github.io/npgsqlrest/options/#authenticationoptionsuseactivedirectoryfederationservicesclaimtypes] to control this behavior.
The record value (as string) is the claim value.

Version 2.1.0 (2024-03-29)

Full Changelog

Role-Based Security

This version supports the Roles-Based Security mechanism.

The Endpoint can be authorized for only certain roles.

For example, all endpoints must be in admin or superadmin roles:

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreated = (routine, endpoint) => endpoint with { AuthorizeRoles = ["admin", "super"] }
});

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreated = (routine, endpoint) => endpoint with { AuthorizeRoles = ["admin", "super"] }
});

Same thing, only for the function with name protected_func:

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreated = (routine, endpoint) => routine.Name == "protected_func" ? endpoint with { AuthorizeRoles = ["admin", "super"] } : endpoint
});

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreated = (routine, endpoint) => routine.Name == "protected_func" ? endpoint with { AuthorizeRoles = ["admin", "super"] } : endpoint
});

There is also support for the comment annotations. Add a list of roles after the RequiresAuthorization annotation tag:

comment on function protected_func() is 'authorize admin, superadmin';

comment on function protected_func() is 'authorize admin, superadmin';

See more details on the RequiresAuthorization annotation tag.

Note: If the user is authorized but not in any of the roles required by the authorization, the endpoint will return the status code 403 Forbidden.

TsClient IncludeStatusCode

The New version of the NpgsqlRest.TsClient (1.1.0) plugin now includes the IncludeStatusCode option.

When set to true (default is false), the resulting value will include the response code in the function result, for example:

export async function getDuplicateEmailCustomers() : Promise<{status: number, response: IGetDuplicateEmailCustomersResponse[]}> {
    const response = await fetch(_baseUrl + "/api/get-duplicate-email-customers", {
        method: "GET",
        headers: { "Content-Type": "application/json" },
    });
    return {status: response.status, response: await response.json() as IGetDuplicateEmailCustomersResponse[]};
}

export async function getDuplicateEmailCustomers() : Promise<{status: number, response: IGetDuplicateEmailCustomersResponse[]}> {
    const response = await fetch(_baseUrl + "/api/get-duplicate-email-customers", {
        method: "GET",
        headers: { "Content-Type": "application/json" },
    });
    return {status: response.status, response: await response.json() as IGetDuplicateEmailCustomersResponse[]};
}

Version 2.0.0 (2024-03-10)

Version 2.0.0 is the major redesign of the entire library. This version adds extendibility by introducing the concept of plugins.

There are two types of plugins:

1) Routine Source Plugins

The concept of a routine in NpgsqlRest refers to an action that is executed on the PostgreSQL server when an API endpoint is called. This can include PostgreSQL functions, procedures, custom queries, or commands.

In previous versions of the library, only PostgreSQL functions and procedures were considered routine sources. The REST API was built on the available functions and procedures in the PostgreSQL database and provided configuration.

However, in the latest version, the routine source has been abstracted, allowing for the addition of new routine sources as plugins. This provides extendibility and flexibility in building the REST API based on different sources of routines.

For example, a plugin that can build CRUD (create, read, update, delete) support for tables and views is published as an independent, standalone package: NpgsqlRest.CrudSource.

To add CRUD support to API generation, first, reference NpgsqlRest.CrudSource plugin library with:

dotnet add package NpgsqlRest.CrudSource --version 1.0.0

dotnet add package NpgsqlRest.CrudSource --version 1.0.0

And then include the CrudSource source into existing sources:

using NpgsqlRest;
using NpgsqlRest.CrudSource;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    SourcesCreated = sources => sources.Add(new CrudSource())
});

app.Run();

using NpgsqlRest;
using NpgsqlRest.CrudSource;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    SourcesCreated = sources => sources.Add(new CrudSource())
});

app.Run();

Note that the routine source for functions and procedures is already present in the basic library. It's part of the basic functionality and is not separated into a plugin package.

2) Code Generation Plugins

The second type is the code generator plugins, capable of generating a client code that can call those generated REST API endpoints.

In the previous version, there is support for the automatic generation of HTTP files. This support is now moved to a separate plugin library NpgsqlRest.HttpFiles.

To use the HTTP files support, first, reference NpgsqlRest.HttpFiles plugin library with:

dotnet add package NpgsqlRest.HttpFiles --version 1.0.0

dotnet add package NpgsqlRest.HttpFiles --version 1.0.0

And then include the HttpFiles in the list of generators in the EndpointCreateHandlers list option:

using NpgsqlRest;
using NpgsqlRest.HttpFiles;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new HttpFile(), /* other gnerator plugins */],
});

app.Run();

using NpgsqlRest;
using NpgsqlRest.HttpFiles;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new HttpFile(), /* other gnerator plugins */],
});

app.Run();

There is also a client code generator for Typescript that can generate a Typescript module to call the generated API: NpgsqlRest.TsClient

To include Typesscipt client:

dotnet add package NpgsqlRest.TsClient --version 1.0.0

dotnet add package NpgsqlRest.TsClient --version 1.0.0

And add TsClient to the list:

using NpgsqlRest;
using NpgsqlRest.TsClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new TsClient("../Frontend/src/api.ts")],
});

app.Run();

using NpgsqlRest;
using NpgsqlRest.TsClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new TsClient("../Frontend/src/api.ts")],
});

app.Run();

System Design

System design can be illustrated with the following diagram:

The initial bootstrap with all available plugins looks like this:

using NpgsqlRest;
using NpgsqlRest.CrudSource;
using NpgsqlRest.HttpFiles;
using NpgsqlRest.TsClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new HttpFile(), new TsClient("../Frontend/src/api.ts")],
    SourcesCreated = sources => sources.Add(new CrudSource())
});

app.Run();

using NpgsqlRest;
using NpgsqlRest.CrudSource;
using NpgsqlRest.HttpFiles;
using NpgsqlRest.TsClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.UseNpgsqlRest(new()
{
    ConnectionString = connectionString,
    EndpointCreateHandlers = [new HttpFile(), new TsClient("../Frontend/src/api.ts")],
    SourcesCreated = sources => sources.Add(new CrudSource())
});

app.Run();

Other Changes

Other changes include:

Optimizations
Bugfixes for edge cases

Full list of available options and annotations for version 2:

Older Versions

The changelog for the previous version can be found here: Changelog Version 1

Comments

Changelog

Version 2.8.1 (2024-05-10)

Client Version 1.2.1

Version 2.8.0 (2024-05-02)

TsClient Version 1.7.0

Client Version 1.2.0

Version 2.7.1 (2024-04-30)

NpgsqlRest 2.7.1

NpgsqlRest Client 1.1.0

Version 2.7.0 (2024-04-17)

Version 2.6.1 (2024-04-16)

Version 2.6.0 (2024-04-16)

ReturnNpgsqlExceptionMessage:

PostgreSqlErrorCodeToHttpStatusCodeMapping

Version 2.5.0 (2024-04-15)

Version 2.4.2 (2024-04-14)

Version 2.4.1 (2024-04-12)

Version 2.4.0 (2024-04-08)

Version 2.3.1 (2024-04-05)

Version 2.3.0 (2024-04-04)

Version 2.2.0 (2024-04-02)

Version 2.1.0 (2024-03-29)

Role-Based Security

TsClient IncludeStatusCode

Version 2.0.0 (2024-03-10)

1) Routine Source Plugins

2) Code Generation Plugins

System Design

Other Changes

Older Versions

`ReturnNpgsqlExceptionMessage`:

`PostgreSqlErrorCodeToHttpStatusCodeMapping`