State Farming

Islands in a sea of state.

Coding is State Farming

If you just see all the code involved in a typical business app, it is tempting to see it as a single, grand piece of computational architecture.

While in some sense it must be that, business software in practice is nearly always small pieces of fairly simple code that run asynchronously, serialising and deserialising bits of the state of whatever is being worked on. We write islands of code in a sea of state.

The instances of running code can be separated by:

• time — perhaps a backup process must be run every night at midnight;

• space — computation distributed in physically separate devices must be sent across a network;

• logic — Some of the logic of the final computation might be best executed in the database; or

• resource constraints — we must do some computation in pieces or in the database because the data involved don’t fit in memory.

In some cases, a computation might just be suspended in memory and resumed (e.g. when waiting to read a file), but mostly, some values must be serialized in one place and deserialized in another, and code finishes here and new code runs there. Through a file or a database or a network packet, the effect is in many ways the same: computations separated by time and space, connected through the sea of state.

These computation islands might be very different. You don’t control the web browser rendering your HTML, for example. Not only are we building asynchronous systems; we are building them out of different code written by different people — code we may or may not trust.

The fight to manage the retrieval or generation of the right state so we can carry out the next step of some computation is the source of most of the complexity in business software. An engineer must constantly bear in mind where something is running, with what privileges, able to assume such and so invariants (i.e. things that have been made true by computation before this). And “state” can be subtle — our state isn’t just the HTTP request we’re processing, but that at this point in the code, we have authenticated the user, who has these privileges, …

Turing Complete code is trouble

Every computation written in a Turing Complete (TC) programming language (as opposed to declarative/logic languages like SQL) which fetches some state in order to do a Very Important Thing is:

• a consumer of resources;

• a source of bugs;

• a source of latency;

• a maintenance burden,

• …

We can all agree that we should seek to minimise these issues. It’s why we use high level languages. It’s why we’re continually moving toward higher level programming languages (e.g. Rust and its affine types). It’s a big part of why we use frameworks and all the rest.

But code in TC languages will always be where we get most of our bugs and vulnerabilities. Better languages are great, but better still would be to just have fewer computations that must be written in regular programming languages.

Coding as State Farming

The database is the most stable part of any business application. Code comes and goes, waxes and wanes, we use different whole applications, but you’ll still have a good chunk of database state that was put there in the first few days your app ran.

Maintaining that state is one of the two most important tasks your code does (the other is emitting the right outputs, where that is API calls or user interface generation). We are all in this sense state farmers.

The tasks we achieve with the code we write can be separated into three kinds:

• causing side-effects (e.g. sending an email);

• creating new state from other state (creating a new appointment based on a HTTP request); and

• performing complex computations (resizing an image).

The last of these is, kind of, the only one we really must perform in a regular language —  at least, in a way that is vulnerable to all of the issues that code brings.

Side Effects through New State

Sending emails and other sort of external state change must in the end be performed by regular code. But these effects are in general self-contained and can be represented as a consequence of a clear and simple set of input states.

This means that we can generally set things up so side effects can be represented as “creating state from other state”. So for sending out emails, we can marshall the template and its arguments in the database and then run the small amount of fixed code that causes the side-effect to occur. We can send any sort of email to any sort of address with any sort of content, all without having to change any TC code.

Creating State from other State

Much of the code we write just rearranges state. A HTTP request comes in from a user, we pull the request apart, and we create state as a result — a new appointment, say.

Since we can just rearrange state in a relational query language, we don’t need to use our TC languages for this.

However.

SQL is an abomination. It hides the sublime elegance of the relational model inside a language and a set of assumptions that would cause the creators of COBOL to blush.

There are alternatives…

Setting SQL aside…

My discussion here leads to two conclustions with regard to SQL:

1. SQL is awful, but it’s really well worth using, much more than most folks do, in any event. This also means that you should learn to use your database well, which is more than most folks do either. Postgres in particular has a lot of features that make the awfulness of SQL not quite so bad; and

2. We should be seeking — demanding! — alternatives. CozoDB is the best project I know of right now. Check it out. It’s really cool.

Rules

SQL is a logic engine, although its design tries hard to hide this. In particular, it’s a bit odd to use a rule in SQL. A rule expresses how to derive some state from other state. We can say if a customer is on the gold list, they get a 10% discount. Or if an order is created, a shipping task should be created for the warehouse.

It is useful to think of rules as being either:

• forward, meaning that whenever a new case of the antecedent of the rule becomes true, we immediately generate and insert its consequent; or

• backward, meaning that when we want to know if the consequent is true, we go looking for matching antecedents

In SQL databases, a forward rule is a trigger, and a backward rule is a view or query.

Declarative Triggers

In most relational databases, triggers execute code in a regular programming language. In many cases (SQL Server, Oracle), we have a choice between a clunky-but-close-to-the-data language like PL/SQL or TSQL, or Python.

However, Postgres and SQLite both support triggers in something like pure SQL, giving us declarative rules, letting us have rules while avoiding the issues TC languages bring. Even within the limits of the other databases, we can write state-rearranging triggers in a simple, essentially declarative way, just chaining together SQL queries.

Limiting Turing Completeness

Much — most, even — of what you do in your TC language can be straightforwardly expressed in the data layer, especially once we can represent functions in the data layer.

A widely used example of a Rails application for tutorials is a blogging app. Here is the code for creating a following relationship from one user to another:

  # Relationship Controller action
  def create
    @user = User.find(params[:followed_id])
    current_user.follow(@user)
    respond_to do |format|
      format.html { redirect_to @user }
      format.js
    end
  end

  # User Model
  has_many :following, through: :active_relationships,  source: :followed

  def follow(other_user)
    following << other_user
  end

Rails is doing a lot of the work here, so this code is simple. Still, it could just be done in SQL, in a lot fewer lines

INSERT INTO active_relationships(follower_id, followed_id) VALUES(current_user_id, followed_id) RETURNING follower_id, followed_id

Imagine a very simple framework where the TC language inserts all incoming requests as JSON into a requests table (you would probably be logging the request anyway, so if we stop doing that, we’re not using significantly different storage). Now, a trigger on the requests table can extract perform the above insert and then gather values and a mustache template for the resulting web page.

The only reason you’re balking at this is because SQL is terrible. Modern SQL (particularly SQLite and Postgres) is somewhat less terrible than you imagine if only you’ll learn to use it — start with the utterly wonderful The Art of SQL. Still, it’s bad. If we had a modern Datalog with at least the sophistication of SQLite, this would all look almost obvious.

Shaping the inputs to code

Such TC code as we must write should be written such that the data given to it has exactly the shape needed to carry out the must-be-Turing-Complete part of the computation in the most straightforward way possible. Note that this is just good coding practice anyway.

The consequence of the above following might be rendering a web page to the user’s browser. We might have one function that turns values and a template into HTML. Another would take HTML and send it to the user. The data layer can invoke User Defined Functions for the first, and return the second as the result of the action that inserted the original request into the requests table.

What does this Mean?

I am arguing for writing software differently in two different ways:

1. We should do more in the query language, limiting the use of TC programming languages to where we must; and

2. We should simplify the TC code we write. Since we have to travel through the sea of state in the database anyway, we should use the power of the query engine to present our TC code with exactly the data in exactly the shape that will make that code easiest to write.

Also, our industry should support development of a better alternative to SQL (i.e. Datalog). The success of SurrealDB, Supabase and the like demonstrates that developers are open to change in this area. A few million dollars of VC investment could stand up a proper commercial Datalog database which might well take over the world.