Comment by bob1029 | Hacker News Reader

Comment by bob1029 | Hacker News Reader

This whole area is so fascinating to me. I started my journey with Out of the Tar Pit [0]. We are now using SQL (SQLite in-memory) for virtually 100% of our business logic execution. We added a few UDFs and views throughout to make a really nice domain-specific language that the business can use to compose queries in terms they understand. Its really easy to map to/from SQL when you model your actual types in terms of tables/facts/relations as well. We do not nest complex types at all.

We were very strict with how the schema was developed, building upon many prior failed iterations. I don't believe you could ever get this stuff right the first time through. You must be OK with iterating the whole schema a few times. Maybe start in Excel and surround yourself with business stakeholders. I think this might be cheaper & easier than starting with whatever new-fangled postgres clustering thing we have on the front page.

Just piping simple things like .NET DateTime in as a UDF [1] turns SQLite from a fun idea into a scary weapon when compared to the competition from Oracle, SAP, IBM, et. al. Imagine where you could go with views on top of UDFs and recursive SQL evaluation.

One tip I can provide to wary travelers struggling in complex problem domains: Embrace the notion of circular dependencies. These exist in the real world and if you can model them properly, you win at a huge part of what fucks up other developers. Relational programming/modeling is 100% the answer to this problem. If 2 types seem hopelessly interconnected, they are either the same type (unlikely if you tried during excel phase) or simply related by way of other combinations of types. For instance, if you have an Account.Customers and Customer.Accounts type of problem, you draw a third CustomerAccountRelationship type, and maintain 2 separate collections for the main domain types. This allows for very clean modeling of hyper-complex domains. You always want to shoot for something around 3NF/BCNF/DKNF [2]

  [0]: http://curtclifton.net/papers/MoseleyMarks06a.pdf
  [1]: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions 
  [2]: https://en.wikipedia.org/wiki/Third_normal_form

> We are now using SQL (SQLite in-memory) for virtually 100% of our business logic execution

Any docs, open source etc?

I don't have any source I am able to share at this time, but I can share the core idea with a simple example.

Assume you have a business app where you need to conditionally do a thing. The predicates (conditions) for this thing can be expressed in terms of constraints in a SQL query, the result of which is a scalar value representing true or false. All complex decision trees can ultimately be turned into combinations of these things.

An example of this could be a decision point in a business app where you need to determine if the user should be allowed to proceed into a certain feature. Perhaps you call this knowledge IsUserAllowedIntoFeatureA. What we would do is expose a configurable SQL rule which allows for a business analyst to customize the behavior of this fact per-customer:

  SELECT 1 --1 == true, 0/null/empty == false
  FROM --My Domain
  WHERE --My Conditions

The above SQL query would be run against a SQLite database that is mapped out of the current instance of that user's state. Once we determine the outcome, we throw away the database.

This idea can also be extended to strings, arrays, etc.

I'd love to understand more. Is there anything like this that already exists that perhaps you modeled this approach from?

I'm having trouble envisioning the complete solution, but it really sounds intriguing and you've caught my attention.

I also am very curious to know more about the complete solution.

It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them. Is the overhead of moving state into and out of SQLite a lot?

It reminds me of SQL stream processing in Fluentbit https://docs.fluentbit.io/manual/stream-processing/getting-s...

> It sounds like you've delegated if statements to configurable SQL queries, does that mean you need to take the context/variables/code and put it into a temporary SQL db whenever you evaluate them.

This is precisely what we do. The schema of the target database is known at startup time, so we can build a template copy of the database in memory and use this as a starting point to fill in current copies of domain state.

Performance is very good for our requirements. We don't need indexes because the actual amount of data in each database is incredibly small. I have benchmarked 30k SELECT statements per second against this approach, which should be more than enough if you consider that all of this is on a per-user-session basis. We could have hundreds of these in-flight simultaneously without any contention.

Reply on news.ycombinator.com