dpq2 postgresql example with vibe.d and GEOGRAPHY(POINT) usage

dpq2vibed.d

import vibe.vibe;
import dpq2;
import std.random;

// not shared/__gshared to have a connection on each thread (TLS)
Connection psql;
// not shared static this to instantiate the psql variable on each thread as well
// with shared static this, multithreading would break.
// Although default vibe.d behaviour is not multithreaded.
static this()
{
	psql = new Connection("user=tbluser password=passwd");
}

void main()
{
	auto settings = new HTTPServerSettings;
	settings.port = 2216;
	settings.bindAddresses = ["::1", "127.0.0.1"];

	// using PostGIS for geographic points: https://postgis.net/
	psql.exec(`CREATE TABLE IF NOT EXISTS locations(
		token varchar(40) PRIMARY KEY,
		position geography(POINT),
		rating integer
	)`);

	auto router = new URLRouter();
	router.get("/data", &getData);
	router.post("/submit", &postSubmit);
	router.get("*", serveStaticFiles("public"));
	listenHTTP(settings, router);

	runApplication();
}

struct APIPoint
{
	double[2] p;
	int r;
}

void getData(HTTPServerRequest req, HTTPServerResponse res)
{
	QueryParams p;
	p.sqlCommand = `SELECT ST_X(position::geometry) as lng, ST_Y(position::geometry) as lat, rating FROM locations`;
	scope data = psql.execParams(p);
	int numPoints = cast(int)data.length;
	APIPoint[] points = new APIPoint[numPoints];
	for (int i = 0; i < numPoints; i++)
	{
		auto row = data[i];
		auto lng = row[0].as!double;
		auto lat = row[1].as!double;
		auto rating = row[2].as!int;
		points[i] = APIPoint([lng, lat], rating);
	}
	randomShuffle(points);
	res.writeJsonBody(points);
}

void postSubmit(HTTPServerRequest req, HTTPServerResponse res)
{
	auto token = req.form.get("token");
	auto lat = req.form.get("lat").to!double;
	auto lng = req.form.get("lng").to!double;
	auto like = req.form.get("like").to!int;
	if (token.length < 2 || token.length > 50)
	{
		res.writeBody("invalid token (clear website data)");
		return;
	}

	QueryParams p;
	p.sqlCommand = `INSERT INTO
		locations (token, position, rating)
		VALUES ($1::text, ST_GeomFromText($2::text), $3::integer)
		ON CONFLICT (token) DO UPDATE SET
			position = EXCLUDED.position,
			rating = EXCLUDED.rating`;
	p.argsVariadic(token, format!"POINT(%s %s)"(lng, lat), like);
	scope result = psql.execParams(p);

	if (result.resultErrorMessage.length)
	{
		debug res.writeBody("Error submitting: " ~ result.resultErrorMessage);
		else res.writeBody("Internal error submitting");
	}
	else
		res.writeBody("Thanks!");
}