NpgsqlRest 1.6.2 Update

I'm glad to see that the NpgsqlRest Nuget library is getting matured and refined. Here are some highlights from the changelog since version 1.4.0:

Strict Function Support

Functions in a strict mode (labeled as STRICT or RETURNS NULL ON NULL INPUT) are the functions that will always return NULL if at least one of the parameters is NULL. In that case, the function isn't even executed.

NpgsqlRest will not even try to execute strict functions when at least one of the parameters is NULL. Instead, the call will return HTTP 204 NoContent and bypass talking to the database altogether.

Command Callback

You can add a lambda callback that will receive a created data reader for the PostgreSQL function and the current HTTP context. If you choose to modify the HTTP response, the default behavior will be skipped in favor of your customized response.

For example, if we have some function that returns data, we can add a handler to render CSV instead of the default JSON response:

app.UseNpgsqlRest(new(connectionString)
{
    CommandCallbackAsync = async p =>
    {
        if (string.Equals(p.routine.Name , "get_csv_data"))
        {
            p.context.Response.ContentType = "text/csv";
            await using var reader = await p.command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var line = $"{reader[0]},{reader[1]},{reader.GetDateTime(2):s},{reader.GetBoolean(3).ToString().ToLowerInvariant()}\n";
                await p.context.Response.WriteAsync(line);
            }
        }
    }
});

app.UseNpgsqlRest(new(connectionString)
{
    CommandCallbackAsync = async p =>
    {
        if (string.Equals(p.routine.Name , "get_csv_data"))
        {
            p.context.Response.ContentType = "text/csv";
            await using var reader = await p.command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var line = $"{reader[0]},{reader[1]},{reader.GetDateTime(2):s},{reader.GetBoolean(3).ToString().ToLowerInvariant()}\n";
                await p.context.Response.WriteAsync(line);
            }
        }
    }
});

Logging Commands

There is now the LogCommands options switch (default is false) that, when enabled (true), logs current commands:

info: NpgsqlRest[0]
      -- POST http://localhost:5000/api/perf-test
      -- $1 integer = 1
      -- $2 text = 'XYZ'
      -- $3 integer = 3
      -- $4 timestamp without time zone = '2024-04-04T03:03:03'
      -- $5 boolean = false
      select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from public.perf_test($1, $2, $3, $4, $5)

info: NpgsqlRest[0]
      -- GET http://localhost:5000/api/get-csv-data
      select id, name, date, status from public.get_csv_data()

info: NpgsqlRest[0]
      -- POST http://localhost:5000/api/perf-test
      -- $1 integer = 1
      -- $2 text = 'XYZ'
      -- $3 integer = 3
      -- $4 timestamp without time zone = '2024-04-04T03:03:03'
      -- $5 boolean = false
      select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from public.perf_test($1, $2, $3, $4, $5)

info: NpgsqlRest[0]
      -- GET http://localhost:5000/api/get-csv-data
      select id, name, date, status from public.get_csv_data()

Other Fixes and Improvements

• Fix timestamp rendering in JSON arrays.
• Change in annotation comment parser: Annotations don't have to start with HTTP anymore.
• Fix HTTP files. Now they'll include a single body parameter if defined.
• Fix default name and URL converters (snake case, camel case) made public for use in custom builds.
• Fix edge case when quoted SQL identifier was used as a parameter or function name.
• Fix logging. The default logger is now created by the NpgsqlRest and is configurable through configuration.
• Fix - better options handling.
• Fix support for the standard PostgreSQL stored procedures instead of just user-defined functions.

AOT Build Template and AOT Release.

In addition to these, a template project was also added suitable for doing AOT builds. You can find it here.

This template also includes an appsettings.json configuration file that can be used to configure every available option.

In addition to this customizable template (instructions included), there is also the first official release that contains framework-independent executable binaries (from that same AOT template) for Windows and Linux ready for download and run.

Perfomance Benachmark Scripts

Finally, the performance benchmark tasks were consolidated and automated (see the scripts here).

Latest results are here (NpgsqlRest 1.6.1 vs PostgREST 12.0.2):