We use SQLite in-memory databases for executing 100% of our business logic these days. Letting the business write all the rules in SQL is the biggest win of my career so far.
Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.:
The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite.
Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data.
The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution.
You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts.
Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.
Queries are managed via a web interface. Nothing is known at compile time.
> What does the SQLite SQL dialect give you that LINQ/functions do not?
It's not about SQLite's specific dialect. It's just about SQL. The relational algebra/calculi are capable of expressing any degree of complexity. LINQ (functions) require compile-time, which breaks our objectives.
I agree, I was once the on the implementation side of this. It was lovely. All day writing pure, terse, bug-free logic. Until that utopia started to itch...
But the experience helped me to look for the SQL patterns in the logic of the codebase I am working with.
Often there is very little. Mostly meaning that the rest is just an annoying heap of plumbing. It is not like I can magically make it go away, but it still seems unnecessary to me.
My rule of thumb at the moment is that for anything up to 10GB of data SQLite basically Just Works. For 10-100GB it will work if you design your indexes carefully. Above 100GB gets harder - my hunch is that there are various tricks and optimizations I've not yet discovered to help make that work OK, but switching to PostgreSQL is probably easier for that kind of scale.
I've got SQLite databases in production that are well beyond 100 gigabytes. These size limits are completely meaningless without any background on actual usage patterns & business cases.
There is no arbitrary point in database size (prior to the exact stated maximums [1]) at which SQLite just magically starts sucking ass for no good reason.
In the (very common) case of a single node, single tenant database server, you will never be able to extract more throughput from that box with a hosted solution over a well-tuned SQLite solution running inside the application binary. It is simply impossible to overcome the latency & other overhead imposed by all hosted SQL solutions. SQLite operations are effectively a direct method invocation. Microseconds, if that. Anything touching the network stack will start you off with 10-1000x more latency.
Unless you can prove you will ever need more capabilities than a single server can offer, SQLite is clearly the best engineering choice.
My rules of thumb are based entirely off experiments I've done with Datasette, which tends towards ad-hoc querying, often without the best indexes and with a LOT of group-by/count queries to implement faceting.
You've made me realize that those rules of thumb (which are pretty unscientific already) likely don't apply at all to projects outside of Datasette, so I should probably keep them to myself!
Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.:
https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite.
Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data.
The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution.
You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts.
Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.