SQL::Wizard

Composable SQL query builder for Perl — expression trees, immutable queries, zero dependencies.

---

Features

• Everything is an expression — SELECTs, CASE, functions, arithmetic, subqueries all compose freely
• Immutable queries — modifier methods return new objects; build a base query once, derive many variants
• Bind parameters by default — plain values always produce ? placeholders; use raw() only when you mean it
• SQL::Abstract-compatible WHERE syntax — the hashref/arrayref conditions Perl developers already know
• Zero non-core dependencies — no SQL::Abstract, no Moose, nothing outside the Perl core

---

Installation

cd SQL-Wizard
perl Makefile.PL
make
make test
sudo make install

---

Quick Start

use SQL::Wizard;

my $q = SQL::Wizard->new;

# SELECT with JOIN, WHERE, ORDER BY, LIMIT
my ($sql, @bind) = $q->select(
    -columns  => ['u.id', 'u.name', $q->func(COUNT => 'o.id')->as('orders')],
    -from     => ['users|u', $q->left_join('orders|o', 'u.id = o.user_id')],
    -where    => { 'u.status' => 'active', 'u.age' => { '>' => 18 } },
    -group_by => 'u.id',
    -order_by => [{ -desc => 'orders' }],
    -limit    => 20,
)->to_sql;

# Pass directly to DBI
my $sth = $dbh->prepare($sql);
$sth->execute(@bind);

---

Expression Primitives

Method	Output
$q->col('u.name')	column reference (no bind)
$q->val(42)	bound value → ?
$q->raw('NOW()')	literal SQL fragment
$q->func('COUNT', '*')	COUNT(*)
$q->coalesce('a', $q->val(0))	COALESCE(a, ?)
$q->cast('price', 'INTEGER')	CAST(price AS INTEGER)
$q->greatest('a', 'b')	GREATEST(a, b)
$q->between('age', 18, 65)	age BETWEEN ? AND ?
$q->exists($subquery)	EXISTS(SELECT ...)

Every expression supports .as('alias'), .asc(), .desc(), .over(...), and .to_sql().

---

Arithmetic

Operator overloading on expression objects:

my $subtotal = $q->col('price') * $q->col('qty');
my $tax      = $subtotal * $q->val(0.2);
my $total    = $subtotal + $tax;

$q->select(
    -columns => [
        $subtotal->as('subtotal'),
        $tax->as('tax'),
        $total->as('total'),
    ],
    -from => 'line_items',
);

Supported: +, -, *, /, %. Plain Perl numbers are auto-coerced to bind parameters.

---

WHERE Clause

Hashref conditions (AND by default):

-where => { status => 'active', age => { '>' => 18 } }
# status = ? AND age > ?

-where => { id => { -in     => [1, 2, 3] } }   # id IN (?, ?, ?)
-where => { id => { -not_in => $subquery  } }   # id NOT IN (SELECT ...)
-where => { deleted_at => undef }               # deleted_at IS NULL

Explicit AND / OR nesting:

-where => [-and => [
    { status => 'active' },
    [-or => [
        { role => 'admin' },
        { role => 'editor' },
    ]],
]]
# (status = ? AND (role = ? OR role = ?))

Expression objects in WHERE:

-where => [
    $q->exists($subquery),
    $q->between('age', 18, 65),
    $q->raw('ST_DWithin(location, ?, ?)', $point, $radius),
]

---

Joins

$q->join('orders|o',       'u.id = o.user_id')   # INNER JOIN
$q->left_join('orders|o',  'u.id = o.user_id')   # LEFT JOIN
$q->right_join('orders|o', 'u.id = o.user_id')   # RIGHT JOIN
$q->full_join('orders|o',  'u.id = o.user_id')   # FULL OUTER JOIN
$q->cross_join('sizes')                           # CROSS JOIN (no ON)

# table|alias expands to: table alias
# ON clause accepts a string or hashref (same syntax as WHERE)

---

CASE Expressions

# Searched CASE
$q->case(
    [$q->when({ total => { '>' => 10000 } }, 'Platinum')],
    [$q->when({ total => { '>' => 5000  } }, 'Gold')],
    $q->else('Bronze'),
)->as('tier')

# Simple CASE (CASE ON)
$q->case_on(
    $q->col('u.role'),
    [$q->when($q->val('admin'),  'Full Access')],
    [$q->when($q->val('editor'), 'Edit Access')],
    $q->else('Read Only'),
)->as('access_level')

---

Window Functions

# Inline specification
$q->func('ROW_NUMBER')->over(
    -partition_by => 'department',
    -order_by     => [{ -desc => 'salary' }],
)->as('rank')
# ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank

# Running total with frame
$q->func('SUM', 'amount')->over(
    -partition_by => 'account_id',
    -order_by     => 'transaction_date',
    -frame        => 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW',
)->as('running_total')

# Named windows (defined in -window)
$q->select(
    -columns => [
        $q->func('RANK')->over('w')->as('r'),
        $q->func('DENSE_RANK')->over('w')->as('dr'),
    ],
    -from   => 'employees',
    -window => { w => { '-partition_by' => 'dept', '-order_by' => 'salary' } },
)

---

UNION / INTERSECT / EXCEPT

my $a = $q->select(-columns => [qw/id name/], -from => 'active_users');
my $b = $q->select(-columns => [qw/id name/], -from => 'legacy_users');
my $c = $q->select(-columns => [qw/id name/], -from => 'pending_users');

my ($sql, @bind) = $a->union($b)->union_all($c)->order_by('name')->limit(100)->to_sql;

Methods: union, union_all, intersect, except.

---

CTEs (WITH clauses)

my ($sql, @bind) = $q->with(
    recent_orders => $q->select(
        -columns => ['*'],
        -from    => 'orders',
        -where   => { created_at => { '>' => $q->raw("NOW() - INTERVAL '30 days'") } },
    ),
    big_spenders => $q->select(
        -columns  => ['user_id', $q->func(SUM => 'total')->as('spent')],
        -from     => 'recent_orders',
        -group_by => 'user_id',
        -having   => $q->raw('SUM(total) > ?', 1000),
    ),
)->select(
    -columns  => ['u.name', 'bs.spent'],
    -from     => ['users|u', $q->join('big_spenders|bs', 'u.id = bs.user_id')],
    -order_by => [{ -desc => 'bs.spent' }],
)->to_sql;

Recursive CTEs:

$q->with_recursive(
    org_tree => {
        -initial => $q->select(-columns => [qw/id name parent_id/], -from => 'employees',
                               -where => { parent_id => undef }),
        -recurse => $q->select(-columns => ['e.id', 'e.name', 'e.parent_id'],
                               -from => ['employees|e', $q->join('org_tree|t', 'e.parent_id = t.id')]),
    },
)->select(-columns => ['*'], -from => 'org_tree', -order_by => 'name')->to_sql;

---

INSERT

# Single row
$q->insert(-into => 'users', -values => { name => 'Alice', email => 'alice@example.com' })->to_sql;

# Multi-row
$q->insert(
    -into    => 'users',
    -columns => [qw/name email/],
    -values  => [['Alice', 'alice@example.com'], ['Bob', 'bob@example.com']],
)->to_sql;

# INSERT ... SELECT
$q->insert(-into => 'archive', -columns => [qw/id name/],
           -select => $q->select(-columns => [qw/id name/], -from => 'users', -where => { status => 'deleted' }))->to_sql;

# PostgreSQL upsert
$q->insert(
    -into    => 'counters',
    -values  => { key => 'hits', value => 1 },
    -on_conflict => { -target => 'key', -update => { value => $q->raw('counters.value + EXCLUDED.value') } },
)->to_sql;

# MySQL ON DUPLICATE KEY
$q->insert(
    -into         => 'counters',
    -values       => { key => 'hits', value => 1 },
    -on_duplicate => { value => $q->raw('value + VALUES(value)') },
)->to_sql;

---

UPDATE

# Simple
$q->update(
    -table => 'users',
    -set   => { status => 'inactive', updated_at => $q->raw('NOW()') },
    -where => { last_login => { '<' => '2023-01-01' } },
)->to_sql;

# With JOIN (MySQL style)
$q->update(
    -table => ['users|u', $q->join('orders|o', 'u.id = o.user_id')],
    -set   => { 'u.last_order' => $q->col('o.created_at') },
    -where => { 'o.status' => 'completed' },
)->to_sql;

---

DELETE

# Simple
$q->delete(-from => 'users', -where => { status => 'deleted' })->to_sql;

# With subquery
$q->delete(
    -from  => 'users',
    -where => { id => { -not_in => $q->select(-columns => ['user_id'], -from => 'active_sessions') } },
)->to_sql;

# PostgreSQL USING
$q->delete(
    -from  => 'orders',
    -using => 'users',
    -where => { 'orders.user_id' => $q->col('users.id'), 'users.status' => 'banned' },
)->to_sql;

---

Immutable Query Modification

my $base   = $q->select(-from => 'users', -where => { status => 'active' });
my $admins = $base->add_where({ role => 'admin' });
my $page   = $admins->order_by('name')->limit(20)->offset(0);
my $count  = $base->columns([$q->func('COUNT', '*')->as('n')]);

# $base is unchanged throughout

Methods: add_where, columns, order_by, limit, offset.

---

License

See the LICENSE file for details.