Permalink
Reply
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?
