vibe.d + dpq2 + json trick

[Robert Schadek]

Sorry for the gigantic code dump, but I think its useful to show 
the complete thing here.
I find myself often in a situation where I want to have a rest 
endpoint that returns json from a DB.

I bet all of you here know the following approach, but I didn't 
so here it goes.

Functions like `normalPG` and `oldSchool` I can find in my 
codebases.
While working on other stuff I thought why should I have to press 
the DB response into json in vibe at all, as Postgres can 
generate json?
Looking into it, it turns out that libpg which is used by dpq2 
returns json as a string.
So instead of parsing the json, or parsing a normal postgres row, 
and then generating json, you can just get the json as one string 
in one row, put it into the HttpResponse and set the json content 
type.

```
import std.stdio;
import vibe.vibe;
import vibe.data.json;
import vibe.db.postgresql;

/*
CREATE TABLE Person(
	id bigserial
);

WITH Pids AS (
	SELECT *
	  FROM generate_series(1,5000) AS id
)
INSERT INTO Person(id)
SELECT id
   FROM Pids;
*/

struct Person {
	long id;
}

void main() {
     setupWorkerThreads(logicalProcessorCount());
     runWorkerTaskDist(&runServer);
     runApplication();
}

PostgresClient client;

void runServer() nothrow {
	try {
		client = new PostgresClient("dbname=postgres user=postgres", 2);
		auto settings = new HTTPServerSettings;
		settings.options |= HTTPServerOption.reusePort;
		settings.port = 8080;
		settings.bindAddresses = ["127.0.0.1"];
		auto router = new URLRouter();
		router.get("/normal", &normalPG);
		router.get("/special", &specialPG);
		router.get("/oldschool", &oldSchool);
		listenHTTP(settings, router);
	} catch(Exception e) {
	}
}

void oldSchool(HTTPServerRequest req, HTTPServerResponse res) {
	Person[] pIds;
	client.pickConnection(
         (scope conn) {
			QueryParams query;
			query.sqlCommand = "SELECT id FROM person;";
			auto result = conn.execParams(query).rangify;
			if(!result.empty) {
				foreach(ref it; result) {
					pIds ~= Person(it["id"].as!(long)());
				}
			}
		}
	);
	res.writeJsonBody(pIds);
}

void normalPG(HTTPServerRequest req, HTTPServerResponse res) {
	Json[] pIds;
	client.pickConnection(
         (scope conn) {
			QueryParams query;
			query.sqlCommand = "SELECT id FROM person;";
			auto result = conn.execParams(query).rangify;
			if(!result.empty) {
				foreach(ref it; result) {
					Json tmp = Json.emptyObject();
					tmp["id"] = it["id"].as!(long)();
					pIds ~= tmp;
				}
			}
		}
	);
	Json r = Json(pIds);
	res.writeJsonBody(r);
}

void specialPG(HTTPServerRequest req, HTTPServerResponse res) {
	string ret;
	cast()(client).pickConnection(
         (scope conn) {
			QueryParams query;
			query.sqlCommand = `WITH Ps AS (
				SELECT json_build_object('id', id) AS d
				  FROM person
				)
				SELECT json_agg(d) AS ids
				  FROM Ps;
				`;
			auto result = conn.execParams(query).rangify;
			if(!result.empty) {
				ret = result.front["ids"].as!(string)();
			}
		}
	);
	res.writeBody(ret, 200, "application/json");
}
```

Testing this with `ab` I got nice improvements 2x - 200x 
depending. And yes I know this is a simplified example and bad 
benchmarking. But I think the general observation that doing less 
work is faster than doing more work will hold.

* ab -n 10000 -c 10 /oldschool Requests per second: 834.65
* ab -n 10000 -c 10 /normal Requests per second: 89.29
* ab -n 10000 -c 10 /special Requests per second: 1622.15

How are other people doing this kind of http -> vibe -> DB -> 
vibe -> http thing?