Ahhh... a fellow traveller on the road.

I have been hacking on a Clojure/Script library (https://github.com/wotbrew/relic) to experiment with exactly this kind of thing. / PLUG

For most problems I encounter I want to focus on the data and its relationships.

I cannot help feeling like programming languages and idioms have me deal with too much 'mechanism', the machine like structures and forms that are mostly incidental to the data domains I work in, and are entirely about performance, layout, access pattern etc - when a machine could do an ok job of making those decisions for me until optimisation becomes important.

Glad to see there's more than one of us out there.

> I cannot help feeling like programming languages and idioms have me deal with too much 'mechanism'

Absolutely. Most "best practices" represent a lot of noise that gets in between me and solving my problem. My latest prototypes have been reduced to using static classes/methods in virtually all areas. Getting everything out of the way is so important. When data is king, your code can turn into this ultra-utilitarian apparatus.

I've had partial success writing code generators that can output supporting infrastructure models/code based upon my SQL table definitions. Assuming those generators are well-tested, high-quality code supporting a schema of arbitrary complexity could be produced instantly.

Do you have any additional resources about this model of thought? It's like Redux on steroids lol. I wonder if anybody has done a SQLite-as-the-Store pattern library for front end apps before. I'd use the hell out of that!

One of these days I am just going to have to write a book about it. There are so many layers and perspectives to consider. Maybe another small rant about our roadmap will help you see more clearly how it could work for you:

I am currently looking at an iteration that will use event sourcing at the core (i.e. append-only logs which record the side-effects of commands), with real-time replays of these events into SQLite databases (among other in-memory working sets). The SQLite databases would serve as the actual business customer front-end. We would also now have a very powerful audit log query capability that would go directly against this event source. I would just think about the database as the layer the business can communicate with. It is your internal/technical customer. As long as that database is proper, everything else downstream works without you thinking about it.

The biggest reason for pursing this is to decouple the schema from the logical reality as much as possible. The business likes to change their mind (sometimes for very good reason) and we have to keep them honest at some level. As proposed here, that source of truth will be the read-only event logs.

When you look at this on a whiteboard, you may recognize that it resembles a textbook definition of CQRS. Perhaps try reading up on: CQRS, event sourcing, database normalization, SQLite's application-defined function capability, and anything else that looks adjacent.

So you are talking about something like SQLite-as-the-API? I was a big fan of this idea for a while. Difficulties in streaming query updates from SQL dbs and difficulties in working with deeply nested, hierarchical data made me shy away from SQL though.

Having a client-side relational db to work with would remove huge amounts of complexity to most client apps though.

Client-side databases are relatively popular in ClojureScript land. DataScript and Fulcro both come to mind immediately. DataScript is an immutable in-memory database that uses Datalog to describe queries and Fulcro is a full-stack framework that automatically normalises your database.

> SQLite-as-the-store pattern

I remain consistently amazed at what one can do with lowly little SQLite. I can't count the number of times I struggled with some scripts performance issues when handling a large amount of data that came through as a CSV or text file, which immediately evaporated when i realized "why am I asking the program to handle a million tuples at a time via internal data structures when an indexed database would do it so much faster?"

An in-memory (or even SSD-resident) SQLite database can be ridiculously fast for handling in-process data and not just the stuff you want to keep when you're done.

I think your example is a solid illustration as to why you may not want non-developers writing SQL:

    SELECT 
        CASE WHEN o.EmailAddress IS NULL OR o.PhoneNumber IS NULL
            THEN 1
            ELSE 0
        END AS [IsSubmitDisabled]
    FROM Customer c 
    INNER JOIN Order o ON c.Id = o.CustomerId
        AND o.IsActiveOrder = 1
    WHERE c.IsActiveCustomer = 1

These queries aren't identical in their output, though they are logically the same. Is that a good thing? What are the implications of allowing such ad-hoc queries within a system?

The SQL was only intended for illustrative purposes against a contrived domain model. Actual correctness of the query in whatever hypothetical domain was imagined at that time is not something I would focus too much on.

My point exactly. "Not focusing too much" on the SQL is a road you likely don't want to go down, and is rather inevitable when you bestow that power unto non-developers! (You were too quick. I made an edit)

Why does someone need to be a "developer" to be highly-competent at authoring SQL?

You know that's not the point I'm making.

But even for authors that _are_ highly competent at authoring SQL, it is dubious to allow them to compose ad-hoc queries ad-nauseum (let's even pretend these "developers" are _also_ excellent at performance tuning).

This is death-by-a-thousand-cuts. The approach is just too granular. Databases _notoriously_ don't have unlimited throughput, and depending on your particular RDBMS it may be prohibitively expensive to just "throw more hardware" at the problem (I'm looking at you MS SQL Server).

For any system that you can imagine there is a point (i.e. scale) at which the above paradigm becomes a very big problem. Believe me. We are in the process of moving logic _out_ of SQL for this exact reason at my current company!

Depends what you mean by developer, but there a heaps of ways to accidentally write code that doesn't use an index, and then you need to understand db internals and how indexing works, and so you are on your way to being a SQL dev. A non-developer I wouldn't expect to understand db internals. You can get quite far though not understanding db internals as a non-dev for simple querying until you write some complex mission-critical query that must continue running, but needs maintenance/starts over-burdening the db/etc.

The promise of low code.

I’m sure there is a time and place for it (Power Apps is a billion dollar business), like time keeping frontend logic. Apps with significant logic and complexity will always need developers.

I’m not critical of your app as I have no real understanding of what it does and why the design choices were made. I do not think embedding logic in SQL would be an ideal way for any app I’ve worked on to work.

> The promise of low code.

Not exactly. We still have shitloads of code. It has just been modularized & standardized such that each component can now mostly be configured by SQL the business writes.

Our product is a B2B application, and each one of our customers has very precise & unique business rules they would like to see applied.

Every customer uses the same code pile, but with varying configuration (SQL).

These contrived examples seem appealing at first, but when you start having deeply relational, nested data, with M-M joins, the relational model is much less appealing. SQL is just one popular declarative approach that people are familiar with and is reasonably easy to read by non-devs for simple things.

Modeling all data on SQL is definitely an improvement on most ad-hoc data manipulations people do.

But say you want to stream updates from your SQL queries, this is something the relational model and SQL dbs are not great at doing efficiently and actually hinder.