Avoiding the "End Weight Problem" when Building SQL Queries
Tags: rakulang, raku
Published: 2023-05-28 00:24:07

In my [previous post, I introduced SQL::Builder](https://dev.to/avuserow/sqlbuilder-making-the-complex-sql-query-manageable-in-raku-4mgn). I briefly alluded to an extension to allow running the queries, looking something like this:

```raku
my @data = $sql.from('table').select('a', 'b').all;
# roughly equivalent to:
# my $b = $sql.from('table').select('a', 'b').build;
# my @data = $db.execute($b.sql, $b.bind.flat);
```

This is much nicer, but I ran into a familiar roadblock: what happens when I inevitably forget the call to `.all`? I was especially prone to forgetting the final call to `.execute` for update statements. A strongly typed language would help with the former case, but the latter would be permitted in basically every language.

My first attempt to solve this was to use Raku's concept of context. By implementing a `sink` method, I could turn this into a runtime error:

```raku
# in my class {
    method sink() {die "Forgot to call .all on a StatementBuilder!"}
# }


# later:
$sql.update('table').set(foo => "bar"); # oops, forgot to execute, dies
$sql.update('table').set(foo => "bar").execute; # fine, .execute called
```

This helps, but it doesn't address the real problem: there's an [Obvious Final Step](https://akrzemi1.wordpress.com/2023/04/23/the-obvious-final-step/) but it's not obvious when the final step happens. This is an end-weight problem.

## The End Weight Problem

In a nearly 20-year old article about Raku (then known as Perl 6), [this is called the "principle of end weight"](https://www.perl.com/pub/2003/06/25/perl6essentials.html/). Raku solves this in a number of places relative to Perl 5:

A classic example of this change in Raku is that regex flags are moved to the front of `m//`:

```
$string =~ m/foobar/ig; # Perl 5
$string ~~ m:ig/foobar/; # Raku
```

The article also mentions re-arranging the arguments to `grep` and `map` to address this. It ended up happening, though not quite like the article suggested:

```
my @foo = grep {$_ % 2} @bar; # Perl 5
my @foo = grep {$_ % 2}, @bar; # Raku
my @foo = @bar.grep({$_ % 2}); # Raku, using the method form
```

In fact, most functions gained method variants in Raku, allowing you flexibility that can help avoid this issue:

```raku
my @parts = split /\/, $string; # Raku, in the style of Perl 5
my @parts = $string.split(/some really long sequence here/); # Raku, using a method call to make this more obvious
```

## Applying the Lesson

With this in mind, a simple solution comes to mind:

```raku
my @data = $sql.all($sql.from('table').select('a', 'b'));
$sql.execute($sql.update('table').set(foo => 'bar'));
```

This avoids the end-weight problem: the tiny portion `all` is moved earlier, making it easier to see what's going on. By using a method call instead of method chaining, the parentheses do the work of telling us when the query is built and should be called.

As a bonus, this moves `all` next to the variable assignment, making it more apparent that `@data` will contain all matching rows.

This solution is simple enough to implement as a subclass of `SQL::Builder`, and it's now available as [SQL::Builder::ExecuteWithDBIish](https://modules.raku.org/dist/SQL::Builder::ExecuteWithDBIish:zef:zef:avuserow).

This module adds helper functions for fetching all rows, fetching a single row, and fetching a single row with a single column (`all`, `one`, and `scalar`, respectively). More helpers can be added in the future, so file an issue if you have an idea.

As the name implies, this provides the given API for `DBIish` connections. Eventually I plan to make a variant for the `DB::Pg` family of modules, so making these separate modules keeps the dependency tree under control.

I think APIs can be made safer and easier to use when taking this into account. Let me know if you come across the end-weight problem in your API design.