2

We have a scenario that is in a VERY tightly controlled industrial environment. This is all C# on Windows.

We gather data from different types of sources - sensors monitoring environmental data, sensors monitoring production line stats, sensors monitoring production line health, etc - with specific "service applications" for each type. These send their data to a central collating service that is capable of seeing the larger picture. The collated data is mostly latest current state with little historical information and is currently stored in a SQLite database.

We have a handful, up to six, of GUI "Monitoring & Control Panels" that are to be written using WPF on Windows clients. They will display that current state in different explorable ways and issue control commands back to the central collating service to effect any changes.

Since the GUI clients will only ever be reading the data we have an internal debate about approach for accessing the data.

  1. WebAPI in the "Central Collator" that serves data requests.
  2. Direct, read-only, access - via Entity Framework - to the database on a fileshare. There will of course be all of the interfaced layering within the GUI application so it won't be ViewModels performing queries.

My concern with 1 is that;

a. The "Central Collator" is becoming monolithic - the data collation and control should in my opinion already be split apart as they are genuinely separate - adding a "database server" element to it makes this worse. b. The faff of the to-ing and fro-ing whilst we develop - since we need lots of different API methods (or a more generic one with the consequent "language" to drive it which would follow) which will evolve as the GUI evolves - given we need to create "client access code" for each API call, WebAPI Controllers to serve the calls etc etc.

There are some valid concerns with 2, but MY wish is to use the lighter approach whilst we allow the "shape" of the data queries to evolve. Then when that is pretty much settled, switch to 1 by simply taking the "QueryManager" code that gets created in the client and shift that into a stand-alone "Query API Application", create the QueryController to handle the WebAPI aspect and turn the QueryManager in the client simply to calls to the WebAPI.

BTW this is for a "Big Bang" project that will not really be subject to ongoing maintenance updates - only occassional larger updates every few years.

1 Answer 1

5

It is almost always simpler to create a central server.

Normally the argument here focuses on security, that a central server makes it possible to implement fine-grained access control without having to spray full database credentials all across your client deployments. That does not seem to be a concern for you.

SQLite doesn't mesh well with your intended approach

But here the crucial issue is that you're intending to publish a SQLite database over a network file share. That might technically work, but it's really not what SQLite is intended for and you are likely to run into technical problems or performance limitations. From the SQLite document Appropriate Uses For SQLite:

Situations Where A Client/Server RDBMS May Work Better

  • Client/Server Applications

    If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

    A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

So the authors of SQLite recommend that you choose solution #1, that your central collator completely owns the database and provides an interface through which clients can interact. As the same document summarizes the intended use of SQLite succinctly:

SQLite does not compete with client/server databases. SQLite competes with fopen().

If you were to use a client/server RDBMS such as Postgres, there might be an argument that client could access it directly. That would definitely work on a technical level. But that would just be a central server solution, albeit using the Postgres wire protocol instead of a HTTP web API.

Development effort seems pretty balanced.

Now lets ignore technical details of SQLite and talk about the effort of developing this solution. You raise a couple of concerns with a centralized solution.

Before we discuss those, I'd like to point out that your project features some “essential complexity” that has to be stuffed somewhere into the architecture. In particular, I see the following aspects:

  • the clients need the results of queries to render the dashboards/control panels
  • queries are likely to change during development, but will stay fixed afterwards
  • the structure of queries is tightly coupled to the structure of the database
  • server–client communication involves significant boilerplate

Indeed, this suggests that solution #2 will be simpler, just because it has fewer components: if the clients communicate directly with the DB, then you don't have to spend the effort for creating a server–client API. Not infrequently, wrappers, anti-corruption layers, interfaces, and other extra layers are just a waste of time.

However, I suspect that you're making that effort sound larger than it is. The clients don't have to make custom queries, they need the results of queries. An API that lobs a bunch of JSON back and forth is comparatively easy to write, especially in languages like C# that have good tooling for serialization etc. I suspect that the structure of the JSON (or other interchange format) will stay fairly stable even if the user interface and the queries evolve, since the purpose of a query or API endpoint won't change wildly.

This is not an argument for solution #1, but I think there wouldn't be an immense difference in development effort. Either way, you'll have to implement the difficult parts, like rendering the dashboards and making the queries. These parts do not get simpler if you make the queries in the client.

It's worth pointing out that I could be entirely wrong here. I am assuming that your queries will eventually be fairly static. However, if your user interface allows very complex and custom user-defined query operations that would have to be integrated in the database queries, then it would likely take excessive effort to describe that via a web API. Such extremely dynamic queries would be a good argument in favour of doing as much as possible in the client.

Discussion of your concerns

You voiced a couple of concerns with regards to having a central server.

The "Central Collator" is becoming monolithic - the data collation and control should in my opinion already be split apart as they are genuinely separate - adding a "database server" element to it makes this worse.

If the Central Collator owns the database, then it should typically broker any access to the data source. Sharing databases between services is generally considered to be an anti-pattern.

Instead of thinking about something being “monolithic”, I think it's better to talk about coupling. Which software components are forced to change together?

For example, when using a shared database, that database schema becomes an interface. All components that use this database must evolve their use of the database together. Since your Central Collector component writes to the database, and queries are made to the same database, whoever makes these queries is already coupled with the Central Collector. It's then worth a thought whether these queries should also be made by the Central Collector, or whether coupled code should be spread out across all clients.

In contrast, I mentioned a hunch above that a web API might be a fairly stable interface, allowing the Central Collector and the data sources to evolve independently from the user interface in the clients. Such independence may or may not simplify development.

The faff of the to-ing and fro-ing whilst we develop - since we need lots of different API methods (or a more generic one with the consequent "language" to drive it which would follow) which will evolve as the GUI evolves - given we need to create "client access code" for each API call, WebAPI Controllers to serve the calls etc etc.

Yes, there is some development overhead here – but perhaps it's not that much.

MY wish is to use the lighter approach whilst we allow the "shape" of the data queries to evolve. Then when that is pretty much settled, switch to 1 by simply taking the "QueryManager" code that gets created in the client and shift that into a stand-alone "Query API Application"

If you want to migrate to approach #1 anyway, then using that architecture right from the start might be simpler.

Conclusion

While you're probably right that approach #2 simplifies development, the limitations of SQLite probably mean that approach #1 is the only sensible solution. If you're going from the server component, you should probably develop that architecture from the start, instead of fixing things later. Introducing a web API might not be that much extra development overhead, especially since it is likely to serve as a fairly stable interface that allows the UI and queries to evolve somewhat independently.

1
  • Thanks for a really fast, comprehensive and considered answer. Greatly appreciated "amon". Jul 23, 2022 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.