Databases have proper context isolation and conflict detection (constraints, triggers) at least. You cannot break the invariants of a properly designed database (though some effort is required to create one).
I often miss transactions and checks on regular in-memory data. While it must be operative, and cannot be done out-of-process or even out-of-context, locks and dispatching is a mess at the end of the day, because correct paths are usually independent, and vice versa — conficting paths can simply fail or retry at the end, wasting cpu time, not developer's. Actually, good principles (i.e. explicit context, no globals, strict hierarchy, etc) are resembling transactions in a sense; you copy parts of state to worker(-s), delay updates into results and reintegrate these in dispatcher in a blocking manner. Only by hand.
One thing that stops me from doing everything in just sqlite is copying. You always copy to and copy back and every += becomes a copy operation. Right now (well for some background time) I'm thinking of the language that has the concept of transactions and relational algebra built right into the base types, but is not so cumbersome to write and/or poorly optimized like conventional database scripting. Mating rdbms internals with e.g. C-likes (as the common least) would be nice to test in real tasks.
Ps. I'm sure I'm missing some past info on that; if someone is aware of the details, you're welcome to share.
I previously worked on an extremely large (in whatever sense you wish to look at, size of codebase, number of users, transaction volume, whatever) system that was originally written in a language like you describe: Pro*C. It basically permitted in-lining SQL with your C code. It might've not been too bad on small systems, but shared Cs scaling problems. It was difficult to track where database changes were actually coming from, but I don't know of any other solution that would have made that easier. If you have a dozen applications across tens of millions of lines of code, all of which could potentially be responsible (or maybe whatever wrote the value they based their change off of was actually the problem...) things get hairy.
I am curious if you've looked at C# and their LINQ stuff though as it sounds like it might check a lot of those boxes. I'm no C# expert myself, haven't used it for several years (since right before LINQ was introduced) so I'm not sure, but it looked to be along those lines when it was being proposed.
Integrated language+database solutions are a recurring motivator for new one-off languages: Pick System and FoxPro are classic examples of writing around a custom language vs "scripting" inside SQL. Applications with custom database-like systems such as game engines also frequently lean in this direction(UnrealScript, GDScript). The main difficulty is in the practicals of getting all of the desired functionality into a useful package - not the theoretical aspects.
As of right now (for game projects) my strategy is to use source code generation to make the working language behave in a database-like fashion and emit a static result, rather than to engineer a complete dynamic schema with a scripting runtime and all the bells and whistles of a standalone database. The latter is both harder to build and lets me leverage fewer of the tools of the host environment.
I often miss transactions and checks on regular in-memory data. While it must be operative, and cannot be done out-of-process or even out-of-context, locks and dispatching is a mess at the end of the day, because correct paths are usually independent, and vice versa — conficting paths can simply fail or retry at the end, wasting cpu time, not developer's. Actually, good principles (i.e. explicit context, no globals, strict hierarchy, etc) are resembling transactions in a sense; you copy parts of state to worker(-s), delay updates into results and reintegrate these in dispatcher in a blocking manner. Only by hand.
One thing that stops me from doing everything in just sqlite is copying. You always copy to and copy back and every += becomes a copy operation. Right now (well for some background time) I'm thinking of the language that has the concept of transactions and relational algebra built right into the base types, but is not so cumbersome to write and/or poorly optimized like conventional database scripting. Mating rdbms internals with e.g. C-likes (as the common least) would be nice to test in real tasks.
Ps. I'm sure I'm missing some past info on that; if someone is aware of the details, you're welcome to share.