sqlite – Offloading database joins to IOT devices


Solution as it is right now

I have this solution where I gather information from a proprietary product of a different company in various sites. The solution is based on a single go binary that contains everything needed to run the application (even an embedded sqlite) and is deployed to Windows computers running the proprietary software. All those Windows computers are connected to the Internet and sit behind a firewall that allows all outgoing traffic but incoming traffic is blocked and the owners of the computers don’t have the knowledge to configure their firewalls (to be honest for most of them it is already a demanding task to install the go program)

Users can access the data that is stored in a sqlite database using their mobile device or a different computer (1). The components needed for this are installed on a server that provides its services over the Internet. I created REST webservices (2) that send graphql queries to the computers on the respective site via RPC over NATS (3). The go program installed on the site computers (4) runs those queries against the local sqlite (5) and sends the result back to the NATS queue (6) (7). The result is taken from the NATS queue and returned to the caller by the same REST service that processed the incoming call (8)

enter image description here

Improvement I’m looking for

This setup works fine when I query single sites. But I should also be able to query several sites in parallel and retrieve a single “recordset”.

Here’s a made up example:

Lets assume there is a Persons table available on each site. I can query that table by running SELECT SiteNumber, PersonName FROM Persons

I need to run that query on for example 3 sites and merge/join them into one result that would look like this:

2, Daisy
2, Eve
2, Adam
5, Bob
7, Alice

The SQLs I need to run are much more complicated than this, I would need to do GROUP BY and `ORDER BY┬┤ for example. This excludes approaches where I would for example create three maps and join them into one.

So far I intentionally don’t store or accumulate data on the server. Which are my options to postprocess the data ? I would rather not INSERT all subresults into a temporary table on the server. I found no distributed database that can be embedded into go and works across firewall borders.