SQL::Ab...ference(3)

📦

version 2.000001 · 7 versions

2.000001 · opensuse-leap 16.0 (latest)
2.000001 · epel 9
2.000001 · epel 10
2.000001 · alpine 3.24
2.000001 · fedora 43
2.000001 · fedora 44
2.000001 · debian bookworm, debian trixie, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking

💖 NAME 💖

Okay so the header sitch: this is the SQL::Ab...ference(3) page, filed under User Contributed Perl Documentation, and the actual name is SQL::Abstract::Reference. It is literally the reference documentation for SQL::Abstract. Periodt.

🌸 TERMS 🌸

🦋 Expression (expr) 🦋

The DWIM structure that gets passed to most methods by default? That is what we call expression syntax. If you spot a variable with expr in the name, or a comment before a code block saying # expr, that is the thing being described. Bestie, that is the vibe.

🦋 Abstract Query Tree (aqt) 🦋

The explicit structure that an expression gets converted into before it is rendered into SQL is called an abstract query tree. If you see a variable with aqt in the name, or a comment before a code block saying # aqt, that is what is being described.

🦋 SQL and Bind Values (query) 🦋

The final result of SQL::Abstract rendering is generally an SQL statement plus bind values for passing to DBI, ala:

my ($sql, @bind) = $sqla->some_method(@args);
my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};

If you see a comment before a code block saying # query, the SQL + bind array is what is being described.

🦋 Expander 🦋

An expander subroutine looks like this, bestie:

sub {
  my ($sqla, $name, $value, $k) = @_;
  ...
  return $aqt;
}

$name is the expr node type for node expanders, the op name for op expanders, and the clause name for clause expanders.
$value is the body of the thing being expanded.
If an op expander is being called as the binary operator in a hashtriple expression, $k will be the hash key to be used as the left hand side identifier.

This can trivially be converted to an "ident" type AQT node with:

my $ident = $sqla->expand_expr({ -ident => $k });

🦋 Renderer 🦋

A renderer subroutine looks like:

sub {
  my ($sqla, $type, $value) = @_;
  ...
  $sqla->join_query_parts($join, @parts);
}

...and it can be registered on a per-type, per-op or per-clause basis.

👑 AQT node types 👑

An AQT node consists of a hashref with a single key, whose name is -type — where type is the node type — and whose value is the data for the node. The following is an explanation of the built-in AQT type renderers; additional renderers can be registered as part of the extension system.

🎀 literal 🎀

# expr
{ -literal => [ 'SPANG(?, ?)', 1, 27 ] }

# query
SPANG(?, ?)
[ 1, 27 ]

🎀 ident 🎀

# expr
{ -ident => 'foo' }

# query
foo
[]

# expr
{ -ident => [ 'foo', 'bar' ] }

# query
foo.bar
[]

🎀 bind 🎀

# expr
{ -bind => [ 'colname', 'value' ] }

# query
?
[ 'value' ]

🎀 row 🎀

# expr
{
  -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
}

# query
(?, clown.car)
[ 1 ]

🎀 func 🎀

# expr
{
  -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
}

# query
FOO(bar, ?)
[ 7 ]

🎀 op 🎀

Standard binop:

# expr
{ -op => [
    '=', { -ident => [ 'bomb', 'status' ] },
    { -value => 'unexploded' },
] }

# query
bomb.status = ?
[ 'unexploded' ]

Prefix unop:

# expr
{ -op => [ '-', { -ident => 'foo' } ] }

# query
- foo
[]

Not as special case parenthesised unop:

# expr
{ -op => [ 'not', { -ident => 'explosive' } ] }

# query
(NOT explosive)
[]

Postfix unop: (is_null, is_not_null, asc, desc)

# expr
{ -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }

# query
bobby IS NULL
[]

AND and OR:

# expr
{ -op =>
    [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
}

# query
( x AND y AND z )
[]

IN (and NOT IN):

# expr
{ -op => [
    'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
    { -bind => [ 'card', 'J' ] },
] }

# query
card IN ( ?, ? )
[ 3, 'J' ]

BETWEEN (and NOT BETWEEN):

# expr
{ -op => [
    'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
    { -bind => [ 'pints', 4 ] },
] }

# query
( pints BETWEEN ? AND ? )
[ 2, 4 ]

Comma (use -row for parens):

# expr
{ -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }

# query
1, 2
[]

🎀 values 🎀

# expr
{ -values =>
    { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
}

# query
VALUES (?, ?)
[ 1, 2 ]

# expr
{ -values => [
    { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
    { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
] }

# query
VALUES (1, 2), (3, 4)
[]

🎀 keyword 🎀

# expr
{ -keyword => 'insert_into' }

# query
INSERT INTO
[]

🎀 statement types 🎀

AQT node types are also provided for select, insert, update and delete. These types are handled by the clauses system as discussed later.

✨ EXPRESSIONS ✨

🌸 node expr

okay so the simplest expression ever? it's literally just an AQT node. that's it. that's the whole vibe:

# expr
{ -ident => [ 'foo', 'bar' ] }

# aqt
{ -ident => [ 'foo', 'bar' ] }

# query
foo.bar
[]

but bestie, plot twist 💅 even when it's an AQT node, the node value gets expanded if an expander has been registered for that node type:

# expr
{ -ident => 'foo.bar' }

# aqt
{ -ident => [ 'foo', 'bar' ] }

# query
foo.bar
[]

🎀 identifier hashpair types

🦋 hashtriple

# expr
{ id => { op => 'value' } }

# aqt
{ -op =>
    [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
}

# query
id OP ?
[ 'value' ]

and if the value is undef, it tries to convert equality and like ops into IS NULL, and inequality and not like into IS NOT NULL:

# expr
{ id => { '!=' => undef } }

# aqt
{ -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }

# query
id IS NOT NULL
[]

🦋 identifier hashpair w/simple value

this one's equivalent to a hashtriple with an op of '='. iconic behaviour honestly:

# expr
{ id => 'value' }

# aqt
{
  -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
}

# query
id = ?
[ 'value' ]

(and yeah, an object value will also follow this code path, no drama)

🦋 identifier hashpair w/undef RHS

this gets converted to IS NULL:

# expr
{ id => undef }

# aqt
{ -op => [ 'is_null', { -ident => [ 'id' ] } ] }

# query
id IS NULL
[]

which is also equivalent to the -is operator:

# expr
{ id => { -is => undef } }

# aqt
{ -op => [ 'is_null', { -ident => [ 'id' ] } ] }

# query
id IS NULL
[]

🦋 identifier hashpair w/literal RHS

this gets directly appended to the key — and remember, you need to provide an operator, bestie:

# expr
{ id => \"= dont_try_this_at_home" }

# aqt
{ -literal => [ 'id = dont_try_this_at_home' ] }

# query
id = dont_try_this_at_home
[]

# expr
{ id => \[
      "= seriously(?, ?, ?, ?)",
      "use",
      "-ident",
      "and",
      "-func",
    ]
}

# aqt
{ -literal =>
    [ 'id = seriously(?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
}

# query
id = seriously(?, ?, ?, ?)
[ 'use', -ident => 'and', '-func' ]

you may absolutely use this when there's no built-in expression type for what you need and registering a custom one would be more hassle than it's worth, but, y'know, do try and avoid it 🙈

🦋 identifier hashpair w/arrayref value

so this becomes equivalent to a -or over an arrayref of hashrefs, with the identifier as the key and each member of the original arrayref as the value:

# expr
{ id => [ 3, 4, { '>' => 12 } ] }

# aqt
{ -op => [
    'or',
    { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
    { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
    {
      -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
    },
] }

# query
( id = ? OR id = ? OR id > ? )
[ 3, 4, 12 ]

# expr
{ -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }

# aqt
{ -op => [
    'or',
    { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
    { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
    {
      -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
    },
] }

# query
( id = ? OR id = ? OR id > ? )
[ 3, 4, 12 ]

Special Case ⭐ — if the first element of the arrayref is -or or -and, that's used as the top level logic op:

# expr
{ id => [ -and => { '>' => 3 }, { '<' => 6 } ] }

# aqt
{ -op => [
    'and',
    { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
    { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
] }

# query
( id > ? AND id < ? )
[ 3, 6 ]

🦋 identifier hashpair w/hashref value

this becomes equivalent to a -and over an arrayref of hashtriples, constructed with the identifier as the key and each key/value pair of the original hashref as the value:

# expr
{ id => { '<' => 4, '>' => 3 } }

# aqt
{ -op => [
    'and',
    { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
    { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
] }

# query
( id < ? AND id > ? )
[ 4, 3 ]

it's sugar for:

# expr
{ -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }

# aqt
{ -op => [
    'and',
    { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
    { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
] }

# query
( id < ? AND id > ? )
[ 4, 3 ]

💅 operator hashpair types

so a hashpair whose key begins with a -, or whose key consists entirely of nonword characters (which is how we cover '=', '>', pg json ops, etc.) gets processed as an operator hashpair. iykyk 💜

🦋 operator hashpair w/node type

if a node type expander is registered for the key, the hashpair gets treated as a "node expr". simple as that.

🦋 operator hashpair w/registered op

if an expander is registered for the op name, that's run and the result is returned:

# expr
{ -in => [ 'foo', 1, 2, 3 ] }

# aqt
{ -op => [
    'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
    { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
] }

# query
foo IN ( ?, ?, ? )
[ 1, 2, 3 ]

🦋 operator hashpair w/not prefix

if the op name starts -not_, that gets stripped and turned into a -not wrapper around the result:

# expr
{ -not_ident => 'foo' }

# aqt
{ -op => [ 'not', { -ident => [ 'foo' ] } ] }

# query
(NOT foo)
[]

which is equivalent to:

# expr
{ -not => { -ident => 'foo' } }

# aqt
{ -op => [ 'not', { -ident => [ 'foo' ] } ] }

# query
(NOT foo)
[]

🦋 operator hashpair with unknown op

if the unknown_unop_always_func option is set (which is recommended but defaults to off for backwards compatibility reasons, no cap), an unknown op expands into a -func node:

# expr
{ -count => { -ident => '*' } }

# aqt
{ -func => [ 'count', { -ident => [ '*' ] } ] }

# query
COUNT(*)
[]

if it's not set, an unknown op will expand into a -op node instead. the more you know 🌈

⭐ hashref expr

a hashref with more than one pair becomes a -and over its hashpairs, i.e.

# expr
{ x => 1, y => 2 }

# aqt
{ -op => [
    'and',
    { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
    { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
] }

# query
( x = ? AND y = ? )
[ 1, 2 ]

is short hand for:

# expr
{ -and => [ { x => 1 }, { y => 2 } ] }

# aqt
{ -op => [
    'and',
    { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
    { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
] }

# query
( x = ? AND y = ? )
[ 1, 2 ]

🦋 arrayref expr

an arrayref becomes a -or over its contents. arrayrefs, hashrefs and literals are all expanded and added to the clauses of the -or. and if the arrayref contains a scalar, it's treated as the key of a hashpair and the next element as the value. sneaky but slay 💅

# expr
[ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]

# aqt
{ -op => [
    'or',
    { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
    { -op => [
        'or', {
          -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ]
        }, {
          -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ]
        },
    ] }, { -op =>
        [
          '=', { -ident => [ 'key' ] },
          { -bind => [ 'key', 'value' ] },
        ]
    },
    { -literal => [ 'lit()' ] },
] }

# query
( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
[ 1, 2, 3, 'value' ]

✨ Default Expanders ✨

Okay bestie, these are the built-in expanders — the little transformations that turn your expr into aqt and query output. They're lowkey iconic, and every example is staying right here. 💅

🍓 bool

Turns the old -bool syntax into the value expression, i.e.

# expr
{ -bool => { -ident => 'foo' } }

# aqt
{ -ident => [ 'foo' ] }

# query
foo
[]

behaves the same way as the now-directly-supported

# expr
{ -ident => 'foo' }

# aqt
{ -ident => [ 'foo' ] }

# query
foo
[]

🦋 row

Expands the elements of the value arrayref:

# expr
{ -row => [ 1, { -ident => 'foo' }, 2, 3 ] }

# aqt
{ -row => [
    { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] },
    { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
] }

# query
(?, foo, ?, ?)
[ 1, 2, 3 ]

💅 op

If an expander is registered for the op name, delegates to the expander; if not, expands the argument values:

# expr
{ -op => [ 'ident', 'foo.bar' ] }

# aqt
{ -ident => [ 'foo', 'bar' ] }

# query
foo.bar
[]

# expr
{ -op => [ '=', { -ident => 'foo' }, 3 ] }

# aqt
{ -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] }

# query
foo = ?
[ 3 ]

✨ func

Expands the argument values:

# expr
{ -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] }

# aqt
{ -func => [
    'coalesce', { -ident => [ 'thing' ] },
    { -bind => [ undef, 'fallback' ] },
] }

# query
COALESCE(thing, ?)
[ 'fallback' ]

🎀 values

A hashref value is expanded as an expression:

# expr
{ -values => { -row => [ 1, 2 ] } }

# aqt
{ -values => [
    { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
] }

# query
VALUES (?, ?)
[ 1, 2 ]

An arrayref value's elements are either expressions or arrayrefs to be treated as rows:

# expr
{ -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] }

# aqt
{ -values => [
    { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
    { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
] }

# query
VALUES (?, ?), (?, ?)
[ 1, 2, 3, 4 ]

🌸 list

Expects a value or an arrayref of values, expands them, and returns just the expanded aqt for a single entry or a comma operator for multiple:

# expr
{ -list => [ { -ident => 'foo' } ] }

# aqt
{ -op => [ ',', { -ident => [ 'foo' ] } ] }

# query
foo
[]

# expr
{ -list => [ { -ident => 'foo' }, { -ident => 'bar' } ] }

# aqt
{ -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }

# query
foo, bar
[]

🔥 between op

The RHS of between must either be a pair of exprs/plain values, or a single literal expr:

# expr
{ -between => [ 'size', 3, { -ident => 'max_size' } ] }

# aqt
{ -op => [
    'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
    { -ident => [ 'max_size' ] },
] }

# query
( size BETWEEN ? AND max_size )
[ 3 ]

# expr
{ size => { -between => [ 3, { -ident => 'max_size' } ] } }

# aqt
{ -op => [
    'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
    { -ident => [ 'max_size' ] },
] }

# query
( size BETWEEN ? AND max_size )
[ 3 ]

# expr
{ size => { -between => \"3 AND 7" } }

# aqt
{ -op =>
    [
      'between', { -ident => [ 'size' ] },
      { -literal => [ '3 AND 7' ] },
    ]
}

# query
( size BETWEEN 3 AND 7 )
[]

not_between is also expanded:

# expr
{ size => { -not_between => [ 3, 7 ] } }

# aqt
{ -op => [
    'not_between', { -ident => [ 'size' ] },
    { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
] }

# query
( size NOT BETWEEN ? AND ? )
[ 3, 7 ]

🧚 in op

The RHS of in/not_in is either an expr/value or an arrayref of exprs/values:

# expr
{ foo => { -in => [ 1, 2 ] } }

# aqt
{ -op => [
    'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
    { -bind => [ 'foo', 2 ] },
] }

# query
foo IN ( ?, ? )
[ 1, 2 ]

# expr
{ bar => { -not_in => \"(1, 2)" } }

# aqt
{ -op =>
    [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
}

# query
bar NOT IN ( 1, 2 )
[]

A non-trivial LHS is expanded with ident as the default rather than value:

# expr
{ -in => [
    { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
    { -row => [ 3, 4 ] },
] }

# aqt
{ -op => [
    'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] },
    { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
    { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
] }

# query
(x, y) IN ( (?, ?), (?, ?) )
[ 1, 2, 3, 4 ]

🫶 and/or ops

expands the same way as a plain arrayref/hashref expression but with the logic type set to the op name.

👑 is op

Expands is and is_not to null checks, RHS value must be undef:

# expr
{ -is => [ 'foo', undef ] }

# aqt
{ -op => [ 'is_null', { -ident => [ 'foo' ] } ] }

# query
foo IS NULL
[]

# expr
{ bar => { -is_not => undef } }

# aqt
{ -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }

# query
bar IS NOT NULL
[]

🌈 ident op

Expands a string ident to an arrayref by splitting on the configured separator, almost always '.':

# expr
{ -ident => 'foo.bar' }

# aqt
{ -ident => [ 'foo', 'bar' ] }

# query
foo.bar
[]

⭐ value op

Expands to a bind node with the currently applicable column name if known:

# expr
{ foo => { '=' => { -value => 3 } } }

# aqt
{ -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }

# query
foo = ?
[ 3 ]

✨ QUERY TYPES ✨

Okay bestie, this is the part where it all comes together — the actual query node types, like select, update and delete. Each one has its own little set of clauses it accepts, and each clause gets expanded by its own default expander. Let's go 👑

🌸 select

A select node accepts select, from, where and order_by clauses. Four little clauses, four little jobs, all iconic 💅

The select clause is expanded as a list expression with a -ident default:

# expr
{ -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } }

# aqt
{ -select => { select => { -op => [
        ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] },
        { -func => [ 'count', { -ident => [ 'baz' ] } ] },
] } } }

# query
SELECT foo, bar, COUNT(baz)
[]

The from clause is also expanded as a list expression with a -ident default:

# expr
{ -select => {
    from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ]
} }

# aqt
{ -select => { from => { -from_list => [
        { -ident => [ 'schema1', 'table1' ] },
        { -ident => [ 'schema2', 'table2' ] },
] } } }

# query
FROM schema1.table1, schema2.table2
[]

The where clause is expanded as a plain expression — no list wrapper, no extra default, just a straight-up expr:

# expr
{ -select => { where => { foo => 3 } } }

# aqt
{ -select => { where => {
      -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ]
} } }

# query
WHERE foo = ?
[ 3 ]

The order_by clause expands as a list expression at the top level, but fr fr, a hashref element inside it can be either an expr or a hashpair with key -asc or -desc to show which direction you're sorting 🦋

# expr
{ -select =>
    { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] }
}

# aqt
{ -select => { order_by => { -op => [
        ',', { -ident => [ 'foo' ] }, {
          -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ]
        }, { -func => [ 'max', { -ident => [ 'baz' ] } ] },
] } } }

# query
ORDER BY foo, bar DESC, MAX(baz)
[]

🎀 insert (yes, it lives down here, don't question it)

Plot twist bestie 💀 — insert is documented right here in the select section, so we're just rolling with it. Not me questioning the layout, nope, not today 🙈

An insert node accepts an into/target clause, a fields clause, a values/from clause, and a returning clause. So many clauses, so little time.

The target clause is expanded with an ident default.
The fields clause is expanded as a list expression if it's an arrayref — otherwise it's passed straight through, no touchy ✋
The from clause can be any of: an expr, a literal, an arrayref of column values, or a hashref mapping column names to values.
The returning clause is expanded as a list expr with an ident default.

First example — hashref values, because we love a convenient mapping:

# expr
{ -insert => {
    into => 'foo',
    returning => 'id',
    values => { bar => 'yay', baz => 'argh' },
} }

# aqt
{ -insert => {
    fields =>
      { -row => [ { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] },
    from => { -values => [ { -row => [
            { -bind => [ 'bar', 'yay' ] },
            { -bind => [ 'baz', 'argh' ] },
    ] } ] },
    returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
    target => { -ident => [ 'foo' ] },
} }

# query
INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
[ 'yay', 'argh' ]

Second example — an arrayref of fields and a whole nested select as the source. INSERT ... SELECT supremacy, honestly 👑

# expr
{ -insert => {
    fields => [ 'bar', 'baz' ],
    from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } },
    into => 'foo',
} }

# aqt
{ -insert => {
    fields => { -row => [ { -op =>
            [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
    } ] },
    from => { -select => {
        from => { -ident => [ 'other' ] },
        select => { -op =>
            [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
        },
    } },
    target => { -ident => [ 'foo' ] },
} }

# query
INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
[]

💜 update

An update node accepts update/target (either one may be used at expansion time, so pick your fighter), plus set, where, and returning clauses.

The target clause is expanded with an ident default.
The set clause — if it's not already a list expr — is expanded as a hashref where the keys are the identifiers to be set and the values are exprs/values. Keys get set, values do the setting, slay ✨
The where clause is expanded as a normal expr.
The returning clause is expanded as a list expr with an ident default.

Behold, the full glow-up from expr → aqt → query:

# expr
{ -update => {
    _ => 'foo',
    returning => [ 'id', 'baz' ],
    set => { bar => 3, baz => { baz => { '+' => 1 } } },
    where => { -not => { -ident => 'quux' } },
} }

# aqt
{ -update => {
    returning =>
      {
        -op => [ ',', { -ident => [ 'id' ] }, { -ident => [ 'baz' ] } ]
      },
    set => { -op => [
        ',', { -op =>
            [ '=', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 3 ] } ]
        }, { -op => [
            '=', { -ident => [ 'baz' ] }, { -op => [
                '+', { -ident => [ 'baz' ] },
                { -bind => [ 'baz', 1 ] },
            ] },
        ] },
    ] },
    target => { -ident => [ 'foo' ] },
    where => { -op => [ 'not', { -ident => [ 'quux' ] } ] },
} }

# query
UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
[ 3, 1 ]

🦋 delete

delete accepts from/target, where, and returning clauses. Short list, big energy.

The target clause is expanded with an ident default.
The where clause is expanded as a normal expr.
The returning clause is expanded as a list expr with an ident default.

# expr
{ -delete => {
    from => 'foo',
    returning => 'id',
    where => { bar => { '<' => 10 } },
} }

# aqt
{ -delete => {
    returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
    target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
    where => { -op =>
        [ '<', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 10 ] } ]
    },
} }

# query
DELETE FROM foo WHERE bar < ? RETURNING id
[ 10 ]

💀 Friendly reminder bestie: DELETE without a where is giving "main character who forgot the consequences." The docs here don't add a where for you — that's all on you, iykyk. Actually, the page never says that. Ignore me. Read the docs, not me. 🫶

and that's a wrap on the whole page, bestie!! 🎀 you made it to the end, iconic behavior. this lil beauty came from perl v5.42.0, dated 2021-01-24, and it's the SQL::Ab...ference(3) page from your friendly neighborhood User Contributed Perl Documentation. go forth and abstract them SQLs 💖✨

📦 where this page comes from

opensuse-leap 16.0: perl-SQL-Abstract 2.000001-bp160.1.10 · Artistic-1.0 OR GPL-1.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash-1017 on 2026-11-06: a language model's rewrite of the original, under its terms