This part of the readme file from my postgrest.net POC project. It turns your PostgreSQL database directly into a RESTful API with .NET Core and thus moving your entire application/business logic to PostgreSQL

I've found that there is a lot of flexibility that boosts productivity when using PostgreSQL JSON parameters as input and result.

Especially with new upcoming support with PostgreSQL

However, a lot of people are reluctant to use SQL at all, let alone. I think that is a mistake.

Here are the benefits and reasons why would you want to build an application like that:

Benefits

A lot has changed since SQL-92.

Modern SQL language is turing complete calculus engine. It is only ever a successful programming language of fourth-generation with elements of the fifth generation.

As a contrast, C# (as well as Java, JavaScript, Python, etc) - are advanced third-generation languages.

So it makes very much sense to use Modern SQL, such as PostgreSQL implementation for your entire application backend logic.

Benefits are the following:

Productivity

SQL is also a declarative language that abstracts (or hides) hardware and as well as algorithms from the user (or programmer in this case).

That means that we program with SQL by telling the machine WHAT to do - not HOW to do what we want. The machine will figure out HOW for us (in most cases). All we have to do, ideally, as programmers developers - is to declare the results we want.

It could be argued that it is the final form of functional programming - instead of declaring a function to return the results we want - we just simply declare the results we want, let the machines sort it out how to do it. Simple as that. But it requires practice.

To be able to achieve such advanced and high concept - SQL needs to abstract or hide unimportant details from the programmer, such as entire hardware, operating system, and algorithms. So there are no processors, there is no memory, there are no file systems and files whatsoever, there are no processes, no threads and of course locks. Also, there no data structures dictionaries, hash tables, linked lists, no nothing. The only thing that is actually left is - your data and your business logic.

SQL lets you focus on what you really need, on your data, and your business logic.

So yes, SQL, given that programmers have the right amount of training - SQL can make an entire development team and individual programmers - extremely productive. I suggest strongly watching tech talk How Modern SQL Databases Come up with Algorithms that You Would Have Never Dreamed Of by Lukas Eder

Productivity can give you a serious competitive advantage on the market.

Maintainability and Changeability

Maintainability and changeability are two attributes that are somewhat interchangeable. It is also true that if productivity is at a high level that maintenance and change rate is also higher.

However, when using PostgreSQL JSON fields we can change the result without stopping or re-deploying anything. For example following endpoint:

create function rest__get_my_data_set(_query json) returns json as
$$
declare _company json;
declare _company_id integer;
begin
    select to_json(c), c.id into _company, _company_id
    from (
        select id, name
        from companies c
        where user_id = (_query->>'user_id')::int --_query parameter is query string serialized to json
    ) c;
    return json_build_object(
        'company', _company,
        'sectors', (
            select coalesce(json_agg(s), '[]') from (
                select id, name from sectors where company_id = _company_id order by company_id
            ) s
        )
    );
end
$$ language plpgsql;

We can replace it safely with the new version that inserts a log statement and adds new fields to result (address):

create function rest__get_my_data_set(_query json) returns json as
$$
declare _company json;
declare _company_id integer;
begin
    select to_json(c), c.id into _company, _company_id
    from (
        select id, name, address
        from companies c
        where user_id = (_query->>'user_id')::int --_query parameter is query string serialized to json
    ) c;

    -- this will create log as normal .net core log.information would
    raise info 'selected company: %', _company;

    return json_build_object(
        'company', _company,
        'sectors', (
            select coalesce(json_agg(s), '[]') from (
                select id, name, address from sectors where company_id = _company_id order by company_id
            ) s
        )
    );
end
$$ language plpgsql;

And that is it.

Now we have a log statement that will help us to see what is going on and we are returning to some new fields. We can add aggregates or anything that SQL will allow and that is literally anything.

Reaction time for maintenance issue is reduced to a minimum. You can always do the software change bureaucracy later - customers can't wait.

Performances

Just using PostgreSQL functions doesn't mean that the execution plans will be automatically cached.

Execution plans are algorithms that SQL engine has preselected for your declaration of your results when you told it what you want. That is the element of 5th generation language - it abstracts (hides) algorithms, so the programmer can focus on business logic.

Executions plans are determined on the fly, before execution. For the same statements that are repeated, they can be cached. Currently, the execution plan of a PostgreSQL function may be cached, only if:

• The function is written in plpgsql.

• The function is run more than 5 times in a single session,

• The generic plan is not significantly worse than the non-generic plan,

• Dynamic SQL is not used,

• The plans are not shared between sessions.

However, besides caching of your algorithms or execution plans - there is a lot of performance gains just by saving of network operations (bandwidth + latency). Because most of the operations are happening on the server and there is no need for network operations as much. In the example above rest__get_my_data_set, normally there would be two queries issued to the server and takes a toll on network latency.

Certainly, some anti-patterns, such as the N+1 query are guaranteed to be avoided.

Furthermore, having high cohesion of SQL code (close to each other and easier to find) - iit is easier to determine performance bottlenecks and easier to tune indexing.

Security

Virtually all database systems have their own security. By using PostgreSQL function you can take full advantage of PostgreSQL role-based security and add another security layer around your application and take your application security to another level.

Here are full instruction on how to leverage PostgreSQL to protect your system from unauthorized access and SQL injections: How to write super-uber-mega secure, sql-injection bullet-proof PostgreSQL queries