DEV Community

Adrian
Adrian

Posted on

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'));
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
xliff profile image
Xliff

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

My only problem with this solution is the need to repeat $sql. Maybe Whatever code could be leveraged to solve this? Consider:

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

This has the benefit of keeping the structure but eliminating the parens, AND the need to repeat the invocant...

Collapse
 
avuserow profile image
Adrian

Yes, the "stuttering" effect of $sql is not great. I considered WhateverCode (and the equivalent Callable), but decided to keep this simple for the 0.0.1 release.

That said, many of my queries are pre-made, so you end up with a different kind of structure:

method common-user-data {
    return self.sql.from("users").join("other-data", :using<user-id>).select(@favorite-columns);
}

# later:
my $data = self.sql.one: self.common-user-data.where(:$user-id);
Enter fullscreen mode Exit fullscreen mode

This kind of reuse was a big motivation for SQL::Builder in the first place.

Thanks for the comment, I'll definitely give this a try and see if I like the results.