NpgsqlRest Version 2 Update 🐘🔥

• NpgsqlRest is .NET8 Nuget library for automatic REST API from PostgreSQL databases, implemented as .NET8 Middleware.
• See the GitHub Readme file.
• NpgsqlRest Version 2 is a major upgrade that introduces extendiblity with the concept of plugins..

There are two types of plugins:

1. Routine Source Plugins
2. Code Generation Plugins

Routine Source Plugins

Routine Source Plugins are plugins that define various sources - from which automatic REST API can be built.

For example, functions are stored procedures source is already built-in into the library and it has been since version 1.

With version 2, you can add the CRUD source plugin, published as a separate, plugin library NpgsqlRest.CrudSource. This plugin can generate automatic REST API for PostgreSQL tables and views, that can:

• Insert into tables and insertable views with or without conflict resolutions and with or without returning the inserted record (Create).
• Read data from tables and views using select operation and filtering by provided parameters (Read).
• Update data in tables and updateable views with or without returning updated data (Update).
• Delete data in tables and deletable views with or without returning deleted data (Delete).

See NpgsqlRest.CrudSource for more information.

Code Generation Plugins

Code Generation Plugins are plugins that are executed after the REST API has been defined and generated from the source. As such, they are used mostly for automatic generations of, well, more code.

Code Generation Plugins automatically generate code.

From the first version - there was always a feature that, based on generated REST APIs - could generate the HTTP Code File.

Those HTTP Code files are the REST Client files, with syntax based on the Visual Studio Code REST Client extension. These files can be used with Visual Studio Code or with the latest Visual Studio to test and run easily REST endpoints.

With version 2, the code generation interface is standardized and the HTTP Code Files functionality is moved to a separate plugin: NpgsqlRest.HttpFiles

Now, there are no more default code generators in the core library - they are all added through plugins as additional libraries.

Besides HTTP Code Files - there is also a Typescript Client Code Generator Plugin: NpgsqlRest.TsClient.

This plugin was created and developed to avoid writing tedious and boring Typescript code. Since REST API endpoints are now automatically generated - there is no reason not to generate API calls for the frontend in Typescript.

Short example of the part of the generated Typescript module:

interface IGetDuplicateEmailsResponse {
    email: string | null;
    count: number | null;
}

const _baseUrl = "";

/**
* function public.get_duplicate_emails()
* returns table(
*     email text,
*     count bigint
* )
* 
* @remarks
* GET /api/get-duplicate-emails
* 
* @see FUNCTION public.get_duplicate_emails
*/
export async function getDuplicateEmails() : Promise<IGetDuplicateEmailsResponse[]> {
    const response = await fetch(_baseUrl + "/api/get-duplicate-emails", {
        method: "GET",
        headers: { "Content-Type": "application/json" },
    });
    return await response.json() as IGetDuplicateEmailsResponse[];
}

interface IGetDuplicateEmailsResponse {
    email: string | null;
    count: number | null;
}

const _baseUrl = "";

/**
* function public.get_duplicate_emails()
* returns table(
*     email text,
*     count bigint
* )
* 
* @remarks
* GET /api/get-duplicate-emails
* 
* @see FUNCTION public.get_duplicate_emails
*/
export async function getDuplicateEmails() : Promise<IGetDuplicateEmailsResponse[]> {
    const response = await fetch(_baseUrl + "/api/get-duplicate-emails", {
        method: "GET",
        headers: { "Content-Type": "application/json" },
    });
    return await response.json() as IGetDuplicateEmailsResponse[];
}

For more information and full examples, see the NpgsqlRest.TsClient plugin.

Notice something interesting: The Typescript module can be re-generated on every build which effectively gives a static type-checking of your database.

Library Design

The overall design of version 2 of the library now looks like this:

The initial setup with all available plugins may look like this:

dotnet add package NpgsqlRest.CrudSource

dotnet add package NpgsqlRest.CrudSource

dotnet add package NpgsqlRest.HttpFiles

dotnet add package NpgsqlRest.HttpFiles

dotnet add package NpgsqlRest.TsClient

dotnet add package NpgsqlRest.TsClient

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

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

app.UseNpgsqlRest(new("Host=;Port=5432;Database=;Username=;Password=")
{
    // add HttpFile and TsClient code generator plugins
    EndpointCreateHandlers = [new HttpFile(), new TsClient("../Frontend/src/api.ts")],
    // add CrudSource source plugin for tabels and views
    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("Host=;Port=5432;Database=;Username=;Password=")
{
    // add HttpFile and TsClient code generator plugins
    EndpointCreateHandlers = [new HttpFile(), new TsClient("../Frontend/src/api.ts")],
    // add CrudSource source plugin for tabels and views
    SourcesCreated = sources => sources.Add(new CrudSource())
});

app.Run();

For more information see the readme documentation for:

• NpgsqlRest.CrudSource
• NpgsqlRest.HttpFiles
• NpgsqlRest.TsClient

This diagram also shows things that are planned and not yet implemented (in dotted boxes). For example:

• Complex Query API Source and Paged Query API Source

It's possible to extend functionality with new plugins that would, based on the HTTP parameters - create some more complex queries, with joins, aggregates, paging, etc.

This opens the door to something resembling an ORM over HTTP.

Personally, I prefer using functions and stored procedures. But, I can see how people can find this approach useful.

• Code Generation for Other Types of Clients

As I use NpgsqlRest more intensively on different full-stack projects, there is more and more need for client code generator plugins.

For example, that might be different Typescript Client Code generators, that might use, perhaps a different library. Or, Javascript Client Code, or any other language that would require such code that calls those generated REST API endpoints.

It is certainly many times easier and faster to add another generator plugin than to write and maintain REST API Client calls manually, by hand.

I wish I didn't have to do that anymore.

Ever.

• Code Generation for UI Components

This is a very interesting aspect I intend to expand on in the future.

From the very start, NpgsqlRest was designed to support comment annotation syntax, that can utilize PostgreSQL Object Comment support - to alter the behavior of the endpoints. PostgreSQL comments are intended to be used as free text object comments. NpgsqlRest can read and parse those comments to detect special annotations that alter endpoint behavior, change path, change the method, enable and disable, etc. You can see annotation documentation on this link.

The plan is to use this annotation syntax to instruct the code generator plugins to generate pieces of the UI, not just client access code.

Performances

Exhaustive performance tests were conducted, you see on this page: performances tests.

For the following test PostgreSQL function:

create or replace function perf_test(
    _records int,
    _text_param text,
    _int_param int,
    _ts_param timestamp,
    _bool_param bool
) 
returns table(
    id1 int, 
    foo1 text, 
    bar1 text, 
    datetime1 timestamp, 
    id2 int, 
    foo2 text, 
    bar2 text, 
    datetime2 timestamp,
    long_foo_bar text, 
    is_foobar bool
)
language sql
as
$$
select
    i + _int_param as id1,
    'foo' || '_' || _text_param || '_' || i::text as foo1,
    'bar' || i::text as bar1,
    (_ts_param::date) + (i::text || ' days')::interval as datetime1,
    i+1 + _int_param as id2,
    'foo' || '_' || _text_param || '_' || (i+1)::text as foo2,
    'bar' || '_' || _text_param || '_' || (i+1)::text as bar2,
    (_ts_param::date) + ((i+1)::text || ' days')::interval as datetime2,
    'long_foo_bar_' || '_' || _text_param || '_' || (i+2)::text as long_foo_bar, 
    (i % 2)::boolean and _bool_param as is_foobar
from
    generate_series(1, _records) as i
$$;

create or replace function perf_test(
    _records int,
    _text_param text,
    _int_param int,
    _ts_param timestamp,
    _bool_param bool
) 
returns table(
    id1 int, 
    foo1 text, 
    bar1 text, 
    datetime1 timestamp, 
    id2 int, 
    foo2 text, 
    bar2 text, 
    datetime2 timestamp,
    long_foo_bar text, 
    is_foobar bool
)
language sql
as
$$
select
    i + _int_param as id1,
    'foo' || '_' || _text_param || '_' || i::text as foo1,
    'bar' || i::text as bar1,
    (_ts_param::date) + (i::text || ' days')::interval as datetime1,
    i+1 + _int_param as id2,
    'foo' || '_' || _text_param || '_' || (i+1)::text as foo2,
    'bar' || '_' || _text_param || '_' || (i+1)::text as bar2,
    (_ts_param::date) + ((i+1)::text || ' days')::interval as datetime2,
    'long_foo_bar_' || '_' || _text_param || '_' || (i+2)::text as long_foo_bar, 
    (i % 2)::boolean and _bool_param as is_foobar
from
    generate_series(1, _records) as i
$$;

Tests are executed with the K6 stress tool for:

• Duration of 60 seconds.
• 100 simultaneous virtual users.
• Retrieval of 10 and 100 records.

Numbers represent an overall number of successful requests (higher is better).

Interesting to notice is the difference between the AOT and the JIT versions of NpgsqlRest.

Usually, JIT versions are slightly faster because JIT has access to the internal optimizations that AOT does not. But, in this case, it's the other way around. I don't have an explanation for this.

Another thing to notice is that NpgsqlRest slows down with the higher number of rows in the JSON array. Over 100 records (which is a JSON array with 100 objects) are slightly slower than the raw ADO approach, and 10 records are noticeably faster.

In the previous version, all values were written directly to the response as soon as they were yielded from the reader. This, in turn, caused frequent small writes to the response stream. In this version, when generating a response from a record, records are buffered before being written to the response stream. This improved performances with the larger datasets since buffering is slightly cheaper than writing to the response stream.

However, the raw ADO approach reads value to an instance and then yields an instance to a minimal API handler, which means that those instances need to be serialized into JSON strings in response. That is not the case with NpgsqlRest which doesn't create instances at all but rather writes values directly from the reader, so theoretically NpgsqlRest should be a bit faster. And, it is on smaller datasets but it slightly degrades for some reason on larger datasets (100+ records), which means, there is still room for improvement.

In any case, performances are more than just good as far as I'm concerned - they are amazing thanks to internal optimizations in the NET8. Other platforms are not even in the same league. But that is not even the biggest thing by far. The biggest win is the sheer amount of code that I don't have to write anymore. Ever.

Summary

NpgsqlRest offers a great deal of expandability through the plugin system. It became a sort of PostgreSQL HTTP ORM and now, I can add anything in PostgreSQL as a new source that will be exposed as the REST API.

An even bigger thing is code generation. The amount of code that I don't have to write anymore is astounding.

Not only that, there are a couple of more unintended positive consequences, like:

• Strong type checking of the PostgreSQL database on a build, for example with the Typescript plugin on the Frontend build.
• Simplified deployment model. A single NpgsqlRest build deployment is enough for all versions since changes are focused on the database. It's easier to replace a database function or alter a procedure than to redeploy a new version of code, and with zero downtime.
• Performances. NpgsqlRest is a high-volume server outperforming almost all available platforms with far less code.

I think there is a lot of room for improvement in the direction of building a declarative, low-code application platform with comment annotations syntax support.

My goal was always simple: offer the highest possible application quality in the shortest amount of time.

Support

Developing tools and libraries like this tedious and daunting unpaid work is achieved mostly late in the evenings, on weekends and holidays, in short - after and outside the regular paid work.

If you find this work valuable for you personally or for your organization, please consider becoming a Patreon or buying me a coffee.

Your support will allow me to continue development on this project.

Thank you.