Which ECS? :). Despite "clear" definition from Wikipedia, ECS as a pattern suffers from multiple personality disorder. There are several different goals that all share the same name, and different implementations pick a different goal set, ending up looking not quite the same, and getting different kinds of benefits. So outside games, if you pick "ECS as composition over inheritance", you get something like here, or…
I have gone down the exact same line of thinking. I think there is value in creating a simple relational data structure to have simple select and insert functions but not the overhead of sqlite. Many times when I think about it, the state of most programs could use a handful of the same structure and be done.
Before SQLite, I was working on my (yet another) own implementation, and eventually realized that I don't like the "Systems" part of the ECS, given I planned a lot of logic that required inspecting multiple components of any given entity. Thinking about it, I figured out that a better abstraction for a "System" would be a piece of code executed on a set of entities, defined by some selection logic. That is, a query. I quickly ended up with this interface:
(select-entities
:components (has-position has-health (not player))
:where ((entity-id (has-health (hit-points hp)))
(
(The example selects non-player entities with position and health components which have less than 50 HP, and orders them by HP and, if both compared entities have a "targetable" component, also by targeting priority.)Which, as you can see, looks essentially like an SQL query. :components is select + join, :where is filtering condition, :order-by saves me from doing an explicit sort on the results, and the :into my-array part allows this query to not allocate new storage for results each frame.
Now each time such call showed up in my code, some extra book keeping storage would be allocated, and underlying logic ensured that most of the query doesn't have to be recomputed every frame. For instance, the :where part was handled by tracking addition and deletion of components to/from an entity. Implementing similar optimizations for :order-by part, I quickly realized that most of my "book keeping" tricks are essentially equivalent to compiled queries and indices on tables. At which point I ditched it all and switched to SQLite.
(I meant to write a blog post about this at some point. I guess I just wrote half of it here.)