Automatic REST API for PostgreSQL Databases implemented as AOT Ready .NET8 Middleware

Read the introductory blog post.

See the changelog for the latest release changes changelog.md.

create function hello_world()                                    
returns text 
language sql
as $$
select 'Hello World'
$$;

var builder = WebApplication.CreateSlimBuilder(args);
var app = builder.Build();
var connectionStr = "Host=localhost;Port=5432;Database=my_db;Username=postgres;Password=postgres";
app.UseNpgsqlRest(new(connectionStr));
app.Run();

@host=http://localhost:5000                                      

// function public.hello_world()
// returns text
POST {{host}}/api/hello-world/

HTTP/1.1 200 OK                                                  
Connection: close
Content-Type: text/plain
Date: Tue, 09 Jan 2024 14:25:26 GMT
Server: Kestrel
Transfer-Encoding: chunked

Hello World

• Automatic generation of the HTTP REST endpoints from PostgreSQL functions and procedures.
• Native AOT Ready. AOT is ahead-of-time compiled to the native code. No dependencies, native executable, it just runs and it's very fast.
• Customization of endpoints with comment annotations. You can easily configure any endpoint by adding annotation labels to routine comments. Like for example HTTP GET if you want to change the method verb to GET.
• Automatic HTTP files. Create ready-to-run HTTP files easily, for testing, debugging and discovery.
• Interact seamlessly with .NET8 backend and take advantage of .NET8 features.
• High performance with or without native AOT, up to 6 times higher throughput than similar solutions.

See the introductory example above. Automatically build HTTP REST endpoints from PostgreSQL functions and procedures and configure them the way you like.

With the NET8 you can build into native code code (ahead-of-time (AOT) compilation).

NpgsqlRest is fully native AOT-ready and AOT-tested.

AOT builds have faster startup time, smaller memory footprints and don't require any .NET runtime installed.

Configure individual endpoints with powerful and simple routine comment annotations. You can use any PostgreSQL administration tool or a simple script:

Function:

create function hello_world_html()                               
language sql 
as 
$$
select '<div>Hello World</div>';
$$

comment on function hello_world_html() is '
HTTP GET /hello
Content-Type: text/html';

Will have content type text/html as visible in comment annotation:

Connection: close                                                
Content-Type: text/html
Date: Thu, 18 Jan 2024 11:00:39 GMT
Server: Kestrel
Transfer-Encoding: chunked

<div>Hello World</div>

Create automatically [HTTP file(s) with ready-to-run randomized test example calls.

NpgsqlRest is implemented as a NET8 middleware component, which means that anything that is available in NET8 is also available to the NpgsqlRest REST endpoints.

And that is, well, everything... from rate limiters to all kinds of authorization schemas, to name a few.

You can also interact with the NET8 calling code to:

• Provide custom parameter validations.
• Pass custom values to function/procedure parameters.

For example, pass a Context.User.Identity.Name to every parameter named user:

var app = builder.Build();                                       
app.UseNpgsqlRest(new(connectionString)
{
    ValidateParameters = p =>
    {
        if (p.Context.User.Identity?.IsAuthenticated == true && 
            string.Equals(p.ParamName, "user", StringComparison.OrdinalIgnoreCase))
        {
            p.Parameter.Value = p.Context.User.Identity.Name;
        }
    } 
});
app.Run();

The latest performance comparison with the PostgREST:

See more details here.

1. Go to the release page.
2. Download the latest version of the binary for either Windows or Linux.
3. Set up the desired configuration in appsettings.json. This file is bundled together with the executable in the download package.
4. Run the executable. REST API is created for you.

Install the package from NuGet by using any of the available methods:

dotnet add package NpgsqlRest --version 1.6.0

NuGet\Install-Package NpgsqlRest -version 1.6.0

<PackageReference Include="NpgsqlRest" Version="1.6.0" />

#r "nuget: NpgsqlRest, 1.6.0"

Your application builder code:

var app = builder.Build();
app.UseNpgsqlRest(new("Host=localhost;Port=5432;Database=my_db;Username=postgres;Password=postgres"));
app.Run();

For all available build options, please consult a source code file, until the documentation website is built.

• net8.0
• Microsoft.NET.Sdk.Web 8.0
• Npgsql 8.0.1
• PostgreSQL >= 13

Note: PostgreSQL 13 minimal version is required to run the initial query to get the list of functions. The source code of this query can be found here. For versions prior to version 13, this query can be replaced with a custom query that can run on older versions.

Contributions from the community are welcomed. Please make a pull request with a description if you wish to contribute.

This project is licensed under the terms of the MIT license.