Avoiding the "End Weight Problem" when Building SQL Queries

In my previous post, I introduced SQL::Builder. I briefly alluded to an extension to allow running the queries, looking something like this:

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:

# 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 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". 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:

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:

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.

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.