Avoiding the "End Weight Problem" when Building SQL Queries

Adrian — Sun, 28 May 2023 00:24:07 +0000

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.

SQL::Builder - making the complex SQL query manageable in Raku

Adrian — Sun, 07 May 2023 05:00:19 +0000

I just released the first version of SQL::Builder, a module to make SQL queries from data structures in Raku.

my $sql = SQL::Builder.new;
my $built = $sql.from('table').select('table.foo', 'other.bar').join('other', :using<id>).build;
say $built.sql;
# SELECT table.foo, other.bar FROM table JOIN other USING(id)

SQL::Builder: You're still basically writing SQL.

There's already other modules out there (Red, SQL::Abstract, among others), so what does this bring to the ecosystem?

Support for complex queries. This module can do nested joins and subselects, while enabling reusable logic.
SQL that's close to the metal. An ORM was not a good fit for this project, but generating queries from code is very helpful.
Better WHERE clause support. It can currently represent any condition in the form of left op right, and while optimized for the typical foo = ? query, it can safely handle functions and expressions.
This syntax is used for HAVING and JOIN ... ON as well, providing a lot of flexibility. This was easily the part requiring the most thought, and I plan to write more about the design choices here.
It's faster, mostly due to returning rows instead of objects and having a simpler interface.

In short, this module is great for queries that are complex, but have some level of variability, like a query that has optional JOINs depending on user input.

What's coming next?

support for UPDATE/INSERT/DELETE statements
better support for Arrays as values in WHERE clauses
optional support for actually running the queries, either via DBIish or DB::Pg (and related modules)
I'm sure there will be something else as I continue work on my other project that uses this :)

This module won't be for everyone, but I've found it very useful in another project. Any feedback is appreciated, please either leave a comment here, open an issue on GitHub, or find me in #raku on irc.libera.chat.

DEV Community: Adrian

Avoiding the "End Weight Problem" when Building SQL Queries

The End Weight Problem

Applying the Lesson

SQL::Builder - making the complex SQL query manageable in Raku