DEV Community: vbilopav

Modern Software Architecture is a Joke

vbilopav — Sun, 07 Jul 2024 10:28:49 +0000

QL is the most misunderstood language in computer history. By far.

It’s the HIGHER-level language, meaning the higher-level generation than today’s common languages. As such, it aims to provide an abstraction for your hardware, memory, disks, operating system, and everything. Including algorithms to a certain point. Have you heard about execution plans? Well, that’s just the engine selecting the most appropriate algorithm for your declared intention with data, so you don’t have to write it yourself.

And, it is also a domain-specific language where that specific domain is just, literally, data itself.

On the other hand, the domain of your non-domain-specific everyday common languages such as JavaScript, Java, or CSharp is, of course, the operating system, disks, memory, devices, and hardware, and you typically use it to write algorithms and use devices.

That is why SQL is a higher language with a higher level of abstraction. When I create some data with SQL, I have no idea where it is physically; that is not even the point, and I don’t care. All I need is to declare my intent and engine is suppose to find the most appropriate algorithm for me.

So, if you remove all these things for the equation: disks, memory, files, locks, threads, processes, etc, you have none of that; none of that exists anymore. Besides data, what else is left?

That’s right, your business logic.

Not once in my 25 years of writing SQL have I been concerned with storage concerns. Not once. I have written SQL every day, all day, for decades now. That is because, as I said, devices are abstracted and hidden, which isnot your concern.

Now, read how the grandfather of the entire modern software architecture calls databases just an IO device. Quote:

The database is merely an IO device.

…

Blessed is the team whose architects have so isolated them from slow and resource hungry IO devices …

A Little Architecture

Unit Testing and TDD With PostgreSQL is Easy

vbilopav — Wed, 03 Jul 2024 08:14:13 +0000

Note: this is a repost from my personal blog: Unit Testing and TDD With PostgreSQL is Easy

I believe this subject matter is important and it deserves a bigger audience.

I keep hearing that PostgreSQL, as well as all other databases - are not testable. That is, of course, completely wrong. Not only it is possible but I found it to be even easier and faster than traditional methods.

And no, I'm not talking about some integration testing or some Docker magic. Just plain-old PostgreSQL, that's all.

So, let's do some TDD with PostgreSQL, shall we?

Note: this article is neither an endorsement nor even a criticism of TDD, it is merely a demonstration of how easy is to do such things with PostgreSQL.

The Problem

Suppose we have a schema:

create table devices (
    device_id int generated always as identity primary key,
    name text
);

create table measurements (
    timestamp timestamp not null,
    device_id int not null references devices(device_id),
    primary key(timestamp, device_id),
    value numeric not null
);

We want to write a functionality that will have the following parameters:

Period (starting and ending timestamps).
Time interval.
Device.

The result will be a cumulative sum for a device and for each given period between start and end, divided by the interval parameter.

Sounds good?

Let's totally do it, it will be fun, I promise.

Database Setup

First, we need a little schema setup to make it a little bit more testable.

Unit tests, by definition, must not interfere with each other in any shape or form and they must be able to run in parallel. That means that tests will have their own transactions, and any test data inserted will be rollback-ed at the end of tests.

However, inserting test data into a relational database can be a bit tricky. Usually, tables will reference some other tables that will reference some other tables too, and before you know it - in order to insert a few test records - we must insert data in all tables in the database. That is certainly possible, but still, inconvenient and tedious. So we don't want to do that. Luckily, PostgreSQL offers a simple solution to this.

In our example, we have table measurements that reference the devices table. This relation will be checked immediately: meaning, a moment when we insert a new measurement the PostgreSQL will check does that device even exists in a database to keep data integrity in check. We can change the behavior of that check to be performed at the end of the transaction. And since the transaction in our unit tests will be rollback-ed anyhow, that will allow us to insert some test data safely without inserting it into a dozen other tables, not concerned with our tests.

To enable this deferred check, a reference has to be created with deferrable initially deferred declaration:

create table measurements (
    timestamp timestamp not null,
    device_id int not null references devices(device_id) deferrable initially deferred,
    primary key(timestamp, device_id),
    value numeric not null
);

Note, there is ˙the other option too: deferrable initially immediate or simply deferrable. That means that we can tell the running transaction to defer all reference checks until the end of transactions with the declaration set all constraints deferred (docs).

If your table is already created then you'll have to recreate the constraint:

begin;
alter table only measurements drop constraint measurements_device_id_fkey;
alter table only measurements add constraint measurements_device_id_fkey 
    foreign key (device_id) references devices(device_id) deferrable initially deferred;
end;

Or, if you want to do that for the entire database because you had an architect or ORM who wasn't aware of this feature, you can simply execute this script:

$$
declare 
    _table text; 
    _fk text;
    _def text;
begin
    for _table, _fk, _def in ( 
        select conrelid::regclass, conname, pg_get_constraintdef(oid)
        from
            pg_constraint 
        where
            contype = 'f' 
            and condeferrable is false
            and connamespace 'public'
    )
    loop
        raise info 'setting fk % on table % to deferrable', _fk, _table;
        execute(format('alter table only %s drop constraint %s', _table, _fk));
        execute(format('alter table only %s add constraint %s %s deferrable initially deferred', _table, _fk, _def));
    end loop;
end;
$$

That will do it. And one more little thing: I usually like to create a special schema for unit tests called simply: test. And now we're ready.

TDD

Ok, let's first create a fresh SQL file, add an empty test and then execute it immediately:

create or replace procedure test.cumulative_sum() language plpgsql as $$
begin
    -- arange
    -- act
    -- assert

    rollback;
end;
$$;
call test.cumulative_sum();

With this approach when we execute a file in the editor, our changes to the test are applied, and the test is immediately executed. This allows for an extremely fast test loop.

Now, first, let's arrange some data:

create or replace procedure test.cumulative_sum() language plpgsql as $$
begin
    -- arange
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:02:00', 0, 0.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:03:00', 0, 2.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:07:00', 0, 2.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:08:00', 0, 3.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:11:00', 0, 3.0);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:12:00', 0, 4.0);

    -- act
    -- assert

    rollback;
end;
$$;
call test.cumulative_sum();

This will add some measurements to a non-existing device (id = 0).

If we execute our hypothetical calculation for this device from timestamps between 2021-01-01 00:00:00 and 2021-01-01 00:15:00 for 5-minute intervals, we should get the following results:

timestamp	sum
`2021-01-01 00:05:00`	3
`2021-01-01 00:10:00`	9
`2021-01-01 00:15:00`	16

You can use Excel or a calculator to verify the validity of these cumulative sum calculations.

Fine, now that we know what we must get, we can add act and assertion parts. First, we will add the act part, which we will call our non-existing function. Since we need to assert results multiple times (count and for each row), we can put the results into a temporary table:

create or replace procedure test.cumulative_sum() language plpgsql as $$
begin
    -- arange
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:02:00', 0, 0.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:03:00', 0, 2.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:07:00', 0, 2.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:08:00', 0, 3.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:11:00', 0, 3.0);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:12:00', 0, 4.0);

    -- act
    create temp table result on commit drop as
    select * from cumulative_sum('2021-01-01 00:00:00', '2021-01-01 00:15:00', '5 minutes', 0);

    -- assert

    rollback;
end;
$$;
call test.cumulative_sum();

This, of course, will fail, because we haven't written this cumulative_sum function yet. But, before we do that, let's also add the assertion part to verify our results. Luckily for us, PostgreSQL supports assertions and assert statements:

create or replace procedure test.cumulative_sum() language plpgsql as $$
begin
    -- arange
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:02:00', 0, 0.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:03:00', 0, 2.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:07:00', 0, 2.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:08:00', 0, 3.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:11:00', 0, 3.0);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:12:00', 0, 4.0);

    -- act
    create temp table result on commit drop as
    select * from cumulative_sum('2021-01-01 00:00:00', '2021-01-01 00:15:00', '5 minutes', 0);

    -- assert
    assert (select count(*) from result) = 3, 
        'Expected 3 rows, got ' || (select count(*) from result)::text;

    assert (select sum from result where timestamp = '2021-01-01 00:05:00') = 3, 
        'Expected 3, got ' || (select sum from result where timestamp = '2021-01-01 00:05:00')::text;

    assert (select sum from result where timestamp = '2021-01-01 00:10:00') = 9, 
        'Expected 9, got ' || (select sum from result where timestamp = '2021-01-01 00:10:00')::text;

    assert (select sum from result where timestamp = '2021-01-01 00:15:00') = 16, 
        'Expected 16, got ' || (select sum from result where timestamp = '2021-01-01 00:15:00')::text;

    rollback;
end;
$$;
call test.cumulative_sum();

That looks a bit ugly, but to be fair, most of those assertions were autocompleted by the Copilot.

This will still fail because we haven't written this cumulative_sum function yet. But at least we can extract input and output data contracts for this function now.

The parameters will be these:

from timestamp,
to timestamp,
interval interval,
device_id int

The resulting table will be this:

table (
    "timestamp" timestamp, 
    sum numeric
)

So now, we know what our function should look like. We can add the first prototype, just above our failing tests:

create or replace function cumulative_sum(
    _from timestamp,
    _to timestamp,
    _interval interval,
    _device_id int
) 
returns table (
    "timestamp" timestamp, 
    sum numeric
) 
language sql
as $$ 
select null::timestamp, null::numeric;
$$;

Again, we've placed this create or replace function cumulative_sum above our failing tests, and again we are executing the entire file. Which in turn gives an extremely fast TDD refactor-red-green loop.

However, our tests are still in the red, since obviously, our newly created function is returning nonsense. So, let's refactor this:

create or replace function cumulative_sum(
    _from timestamp,
    _to timestamp,
    _interval interval,
    _device_id int
) 
returns table (
    "timestamp" timestamp, 
    sum numeric
) 
language sql
as $$ 
select
    p.period_to,
    sum(coalesce(m.sum, 0)) over (rows unbounded preceding)
from (
        select 
            series as period_from, 
            series + _interval as period_to 
        from generate_series(_from, _to - _interval, _interval) series
    ) p
    left join lateral (
        select 
            sum(coalesce(value, 0)) as sum
        from measurements 
        where 
            timestamp > p.period_from
            and timestamp <= p.period_to 
            and device_id = _device_id
    ) m on true
$$;

And now, this seems to be correct, and our tests are in green now. We can continue improving and optimizing this function while our tests are green as much as we want without any fear that something will be broken.

Here is the final content of our work in a single file:

create or replace function cumulative_sum(
    _from timestamp,
    _to timestamp,
    _interval interval,
    _device_id int
) 
returns table (
    "timestamp" timestamp, 
    sum numeric
) 
language sql
as $$ 
select
    p.period_to,
    sum(coalesce(m.sum, 0)) over (rows unbounded preceding)
from (
        select 
            series as period_from, 
            series + _interval as period_to 
        from generate_series(_from, _to - _interval, _interval) series
    ) p
    left join lateral (
        select 
            sum(coalesce(value, 0)) as sum
        from measurements 
        where 
            timestamp > p.period_from
            and timestamp <= p.period_to 
            and device_id = _device_id
    ) m on true
$$;

create or replace procedure test.cumulative_sum() language plpgsql as $$
begin
    -- arange
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:02:00', 0, 0.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:03:00', 0, 2.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:07:00', 0, 2.5);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:08:00', 0, 3.5);

    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:11:00', 0, 3.0);
    insert into measurements (timestamp, device_id, value) values ('2021-01-01 00:12:00', 0, 4.0);

    -- act
    create temp table result on commit drop as
    select * from cumulative_sum('2021-01-01 00:00:00', '2021-01-01 00:15:00', '5 minutes', 0);

    -- assert
    assert (select count(*) from result) = 3, 
        'Expected 3 rows, got ' || (select count(*) from result)::text;

    assert (select sum from result where timestamp = '2021-01-01 00:05:00') = 3, 
        'Expected 3, got ' || (select sum from result where timestamp = '2021-01-01 00:05:00')::text;

    assert (select sum from result where timestamp = '2021-01-01 00:10:00') = 9, 
        'Expected 9, got ' || (select sum from result where timestamp = '2021-01-01 00:10:00')::text;

    assert (select sum from result where timestamp = '2021-01-01 00:15:00') = 16, 
        'Expected 16, got ' || (select sum from result where timestamp = '2021-01-01 00:15:00')::text;

    rollback;
end;
$$;
call test.cumulative_sum();

Conclusion

The arguments I hear all the time are as follows:

Testing in a database is impossible. No, it isn't. I just showed you how. It may be in other databases, but PostgreSQL isn't that other database.
Testing in a database is hard. No, it isn't. SQL may be hard if you haven't honed your skills. So, start learning.
Testing in a database is slow. No, it isn't. It is way faster than anything out there. Again, hone your skills.

But what about running all tests in parallel, perhaps in a CI/CD pipeline you might ask.

Well, it's just a matter of a runner that will run all those parameterless procedures in parallel connections under some criteria. On my setup, that is the test schema. So if, it is a procedure and it is in test schema and it doesn't have parameters - it's a test.

Writing such a test runner would be really, really simple. In fact, that is precisely what I did by using NodeJS, for my projects: @vbilopav/pgmigrations. Here is an example project and how it is used in the GitHub actions: teamserator/.github/workflows /build-and-test.yml

But in all fairness, it's so easy that anyone could do it.

What Makes Norm Micro ORM for .NET Fast As Raw DataReader

vbilopav — Wed, 16 Dec 2020 09:45:01 +0000

Norm has been one of my favorites side projects that recently got very serious.

Basically, it's just, yet another micro ORM library (a Dapper clone) for .NET with a few extra tricks.

Originally, I wrote it so I can utilize features of modern C# like tuples and async streaming like for example:

// Map single values from a tuple to variables:
var (id, foo, bar) = connection.Single<int, string, string>("select id, foo, bar from my_table limit 1");

// Map to enumerable of named tuples:
IEnumerable<(int id, string foo, string bar)> results = connection.Read<int, string, string>("select id, foor, bar from my_table");

// Asynchronously stream values directly from database
await foreach(var (id, foo, bar) in connection.ReadAsync<int, string, string>("select id, foor, bar from my_table"))
{
    //...
}

// etc...

That works fine, however, in order to be real Micro ORM, it needs to have a proper object mapper, that would map results from your query to your classes.

And it did, however, the first versions although fairly decent, were not as nearly as performant as famous mapper from Dapper -which is labeled as the "King of Micro ORM"

Recently, I've got some insights and ideas that I wanted to try out, so I rewrote Norm mapper from scratch.

Results surprised even me, I wasn't expecting performances so fast that is basically indistinguishable from the raw DataReader.

How is that possible?

Let's deep dive into Norm:

Reading the data

Norm implements one basic extensions data is used for data reading in preparation for mapping:

public static IEnumerable<(string name, object value)[]> Read(this DbConnection connection, string command)

As we can see, it returns an enumerator that yields an array that contains the name and value tuple.

It does not create a list of any kind, it simply uses yield to return a value when the enumeration is triggered.

And that enumerator item is an array of fields in the form of name and value tuple, where name is field name and value is an actual field value of object type (that requires casting later).

Actual mapping itself is implemented as an extension to that same structure:

public static IEnumerable<T> Map<T>(this IEnumerable<(string name, object value)[]> tuples)

Similarly, this mapping extension will also yield the mapped result from enumeration, rather than creating some sort of list.

That's why when working with Norm, you would have to use Linq extension ToList to create an actual list and thus triggering the enumeration and mapping as well:

// build the enumerator, does not start reading 
// you can use also use connection.Query<MyClass>(query);
var enumaration = connection.Read().Map<MyClass>(query);
// start actual reading from the database and creates a list
var results = enumaration.ToList();

This is neat because now I can build my Linq expressions before any serialization or mapping.

So now, all we have to do is the map (string name, object value)[] to an actual instance.

Mapping the data

Mapping, in a nutshell, would be just copying values from this array (string name, object value)[] to a class instance.

Let's say for example that we have a simple class:

public class MyClass 
{
    public int Id { get; init; }
    public string Foo { get; init; }
    public string Bar { get; init; }
}

And naturally, the database query returns id, foo, and bar.

In order to map those values, we would have to compare names for each iteration step to map id to Id, foo to Foo, and bar to Bar.

But what if I already know that filed id is always the first value and foo is always the second value, and so on. That would be much more efficient because we don't have to compare name strings on each iteration.

That's mapping by position - which is much more efficient than mapping by name that is normally used.

However, that is a suboptimal solution because if we switch the order in the query (or in the class for that matter) while leaving the names unchanged - it will introduce errors and confusion.

For example, trying to map the query select foo, id, bar - to a class in this example - would break the program.

But, what if we can do the mapping by name - only on the first record and remember the position indexes - so we can use them later in all other iterations of the same type - we would be doing mapping by name only once for each type. That would be fast, right.

And that is precisely how Norm mapping works.

This also gives me extra breathing space, so now, I can do more complex mappings, like for example the one that uses camel case naming or normal case naming - without sacrificing performances, because mapping by name is now pretty cheap.

Object construction

What have the STORED PROCEDURES ever done for us?

vbilopav — Fri, 07 Feb 2020 09:34:52 +0000

I'll use terms stored procedures and database functions in text interchangeably.

So, in all seriousness - what have the STORED PROCEDURES ever done for us?

Security?

You can very easily set up any database that has support for SP in a way that:

Your application is only aware of that one database account that can only execute SP required by your application and nothing more.
That application account can't access data at all directly.
That application account is created and managed by another database account with a higher level of privileges and that is stored and protected much more securely.

That system is usually called the least-privilege principle and it can help you to:

Protect your data from theft way more securely because the account exposed to the application doesn't have direct access to data.
Guard your system against SQL injection attacks. The SQL injection is an impossibility (unless you decide to use dynamic SQL).
Prevents well-meaning junior developers to write horribly inefficient queries. Well, that's not security per se, but it is a very useful side-effect.

Militaries around the world use this principle also called - "need-to-know-basis".

And the military takes security very, very seriously.

Do you?

Performances?

Well, this one is easy, everyone knows that at least - STORED PROCEDURES will give you performance gain.

Database code needs to be closer to the actual data and usually, there is much less network utilization between database and application.

Besides that, STORED PROCEDURE can be heavily optimized by the database engine and execution plans cached and ready.

Maintainability?

Imagine this scenario:

Users are seeing data on their screens that shouldn't be there. So, it's a bug, it needs a bit of maintenance obviously.

What do you do?

You can, of course, just create a Jira ticket ... or, or you can do something like this:

Connect to the database server with the query tool
Execute the STORED PROCEDURE that serves that screen and examine the data.
If data contains the bug, then you know that the problem is within the STORED PROCEDURE, if it doesn't then the bug is somewhere within the application layer.
Now, within seconds you narrowed a search for a bug to at least half of the system.

If it is in your database layer, you can fix it immediately with one ALTER PROCEDURE (or REPLACE FUNCTION) call ... and it's fixed within minutes or even less - with zero downtime.

If it is not, then at least you know that it doesn't have anything to do with the database layer, and you can focus your investigation on your classes, designed domain models with patterns, abstract factories, reducers API controllers, injected services and whatnot you name it. Good luck with that, it will take some time for sure, and the application needs to be patched, redeployed, and all that jazz.

Does your user care about that?

Or they usually want to see bugs fixed right here and right now?

But that is not the only factor in this story that affects maintainability.

Your database is a system of course, and if you surround your database with STORED PROCEDURES, which is essentially an API layer - you got yourself a black box.

A black box is a system with known inputs and known outputs - without any knowledge of inner workings.

Your database may serve dozens and dozens of applications, services, micro-services, reporting systems, integrations, etc, etc, and, by having a black box concept you can change the schema and inner workings while respecting known outputs for know inputs with supreme confidence and efficiency.

How's that for maintainability?

Availability?

You want to have a system with maximum availability, don't you? I mean, downtimes are bad for business. Very bad.

I may have already mentioned above that you can patch a bug in STORED PROCEDURE without having to stop anything. Hence, availability.

No downtime.

Simply run "ALTER PROCEDURE" or "REPLACE FUNCTION" and there you go.

But it gets better. Imagine this. You have a huge table with a gazillion record. And for some reason, you have to change the data type of one field in that table. What do you do? Well, of course, you rush to create a migration that alters the table and alters the field type and then you deploy your update and ... nothing happens. For hours. The table is locked, the entire system is unresponsive because everybody has to wait for your alter to finish and that may take hours. Many, many expensive hours of downtime.

But not if you use STORED PROCEDURES!

You could also write a script that will do this.

Create an auxiliary table from your main table without relations
Alter procedures to insert data in that auxiliary table
Alter the main table to change the data type in your field, wait to finish
After it finishes, insert data from the auxiliary table and revert procedure to original version
No downtime.

Availability.

Apart from security, performances, maintainability, and availability, what STORED PROCEDURES have ever really done for us?

I really don't know.

About me

I'm an independent software developer and consultant - with more than 20 years of experience in the development of database-backed business applications.

If you're interested in this line of work or related consulting, send me a message or an email (details on my GitHub account or you can find me on LinkedIn) to see if I'm available at the moment.

You can also follow on LinkedIn where I post daily

postgexecute.net is not ORM

vbilopav — Sat, 14 Sep 2019 09:56:05 +0000

postgexecute.net is lightweight wrapper around NpgsqlConnection object to facilitate simple PostgreSQL execution and data retrieval in .NET Core.

This is NOT an ORM

I repeat:

This is NOT an ORM

There is not data conversion whatsoever, and so there is no impedance mismatch issue.

All read operations will serialize rows directly and only to IDictionary<string, object>>

or, even much, much better:

All read operations version with lambda callback for each row

This opens up new flexibility options. Now you can:

Serialize directly to structure of your choice. No need for extra transformation step. For example dictionary of instances that has key same as your database key.
Write directly to stream, json, etc ...

Developer notes

If someone wishes to build micro-ORM Dapper style based on this code base - he or she may freely do so as long as they give proper credits/mentions and link to this repository. And if I may suggest a name - NoORM would be just perfect.

Current version works only with PostgreSQL and there are no plans for now to expand to other databases.

Usage

Install PostgExecute.Net or clone this project repo.
Two ways to use this API:

1. `PostgreSQL` connection extensions

Extensions on NpgsqlConnection object (like Dapper).

For example:

using (var connection = new NpgsqlConnection("<connection string>"))
{
    // Execute can be chained. Two chained executes under same connection and/or transaction
    connection.Execute("<pgpsql command 1>").Execute("<pgpsql command 2>");

    // ALL methods not returning any results can be chained

    // Execute another command and read some data and return results
    var myResults1 = connection.Execute("<pgpsql command 3>").Read("<pgpsql read 1>");
    // myResults1 is enumerable of row dictionaries

    // Read into dictionary
    var myResults2 = new List<IDictionary<string, object>>();
    connection.Read("<pgpsql read 2>", result => myResults2.Add(result));

    // Read into custom class
    var myResults3 = List<MyResults>();
    connection.Read("<pgpsql read 3>", r => myResults3.Add(new MyResults{ Field1 = result["field1"] }));

    // etc..
}

2. Static methods

Each extension have same exact version as static method which takes first parameter connection string.

For example:

Postg.Execute("<connection string>", "pgpsql command 1");
Postg.Execute("<connection string>", "pgpsql command 2");
// ...

Notes:

No chaining available because connection is used once and disposed immediatel.

When using static methods PostgreSQL new connection will be created and disposed.

NpgsqlConnection will recycle connection made from same thread (they'll have same PID), but, any pending transaction will be lost and rolled back.

API

Full list of entire available API's and their overloads can be found on this interface definition.

They fall in following three categories:

Execute and ExecuteAsync - Execute PGPSQL command on PostgreSQL database
Single and SingleAsync - Fetch single row from PostgreSQL database
Read and ReadAsync - Read multiple rows from PostgreSQL database

Each version have it async version that ends with Async suffix and they process parameters in same way:

Working with parameters

By convention, parameters on query must start with letter @

Positional parameters

Example:


var result = connection.Single(
    @"select * from (
        select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
    ) as sub
    where first = @1 and bar = @2 and day = @3
    ",
    1, "foo", new DateTime(1977, 5, 19));

Note:

Positional parameters are assigned in order of appearance in query, name is completely irrelevant (but, they must have one).

Named parameters

Unlike positional parameters, when using named parameters - position is irrelevant and every parameter must have unique name.

To accept named parameters interface exposes parameters collection (type NpgsqlParameterCollection) - as lambda parameter:

var result = connection.Single(
    @"select * from (
        select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
    ) as sub
    where first = @1 and bar = @2 and day = @3
    ", p => {
        p.AddWithValue("3", new DateTime(1977, 5, 19));
        p.AddWithValue("2", "foo");
        p.AddWithValue("1", 1);
    });

This API also defines extensions for parameter collection type - that allows chaining. This allows muc mroe elegant syntax:

var result = connection.Single(
    @"select * from (
        select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
    ) as sub
    where first = @1 and bar = @2 and day = @3

    ", p => p.Add("3", new DateTime(1977, 5, 19)).Add("2", "foo").Add("1", 1));

There is also shorter alias named @P:

var result = connection.Single(
    @"select * from (
        select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
    ) as sub
    where first = @1 and bar = @2 and day = @3

    ", p => p.@P("3", new DateTime(1977, 5, 19)).@P("2", "foo").@P("1", 1));

API can also take async version of this lambda:

var result = connection.Single(
    @"select * from (
        select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
    ) as sub
    where first = @1 and bar = @2 and day = @3

    ", async p => {
        await Task.Delay(0); // some async operation...
        p.@P("3", new DateTime(1977, 5, 19)).@P("2", "foo").@P("1", 1);
    });

Fetching the data

Default result set for this API is IDictionary<string, object> for each row:

var result = connection.Single("select 1, 'foo' as bar, '1977-05-19'::date as day, null as null");
// result is `IDictionary<string, object>` that represent result row.

Empty result set will yield empty dictionary.

Note:

There is no data conversion whatsoever (this is not ORM).

null values will NOT have C# null - but DBNull.Value instead.

Reason for this is because your null and database null are not same thing.

Same logic applies for reading multiple rows. Read will return IEnumerable<IDictionary<string, object>>:

var result = connection.Read(
    @"select * from (
    values
        (1, 'foo1', '1977-05-19'::date),
        (2, 'foo2', '1978-05-19'::date),
        (3, 'foo3', '1979-05-19'::date)
    ) t(first, bar, day)");

// result is `IEnumerable<IDictionary<string, object>>`

Read callback lambda

Each read method has version that accepts lambda callback that is executed for each row:

var results = new List<IDictionary<string, object>>();
await connection.Read(
    @"select * from (
    values
        (1, 'foo1', '1977-05-19'::date),
        (2, 'foo2', '1978-05-19'::date),
        (3, 'foo3', '1979-05-19'::date)
    ) t(first, bar, day)",
    result => results.Add(result));

Each version also has lambda overload that have bool as return value.

In that case you can return false to break from iteration immediately and safely:

var results = new List<IDictionary<string, object>>();
connection.Read(
    @"select * from (
    values
        (1, 'foo1', '1977-05-19'::date),
        (2, 'foo2', '1978-05-19'::date),
        (3, 'foo3', '1979-05-19'::date)
    ) t(first, bar, day)",
    result =>
    {
        if ((int)result["first"] == 2)
        {
            return false;
        }
        results.Add(r);
        return true;
    });
// breaks on second row, third is never executed, result will have only one entry

Of course, there is async overload for each API version:

await connection.ReadAsync(@"
        select * from (
        values
            (1, 'foo1', '1977-05-19'::date),
            (2, 'foo2', '1978-05-19'::date),
            (3, 'foo3', '1979-05-19'::date)
        ) t(first, bar, day)",
async result =>
{
    await Task.Delay(0); // some async operation...
    results.Add(result);
});

Tests

Test coverage is 100% and it can be found here

Future plans

When C# 8.0 finally comes out - implement those fancy, ultra fast async streams for read operations supported by C# 8.0 (e.g. async return yield)