NAME

Badger - expose SQL queries as Raku subroutines

WHAT'S BADGER?

Badger is a SQL library that allows you to invoke SQL snippets as function as if it was Raku code. This way you can keep writing your SQL queries by hand for performance and tweakability, and your tools still recognize the .sql files to help you work with them.

What does a Badger SQL file look like?

A badger-compatible SQL file is just a normal SQL file, with signature headers. These signatures are intended to look like Raku signatures.

The most basic example:

-- sub my-query()
SELECT;

How do I feed Badger my SQL?

You have to pass the .sql file(s) to the use Badger statement:

use Badger <sql/my-query.sql>; # The file in the previous code block

This will generate this function Raku-side:

sub my-query(Database $db --> Int) { ... }

Which you can call just like any other Raku subroutine, by passing any object that has an interface similar to DB::Pg (for now at least) as the connection.

For parameters and return values, see below.

Parameters

A Badger SQL sub can have arguments that you can use in the SQL body. Interpolation works for sigilled variables:

-- sub query-with-params($a, $b)
SELECT $a + $b, @c

This will generate a prepared query with $a and $b replaced $1, $2 (or with ?s depending on the RDBMS).

Parameter Sigils

The Raku allowed sigils are $ and @.

Parameter typing

You can put type annotations on the parameters:

-- sub query-with-params(Int $x, Int @xs)
SELECT $x = ANY(@xs)

If a parameter is typed, Badger will try to help you by inserting coercions in the generated SQL. This is what the executed SQL looks like:

SELECT ($1::int) = ANY(($2::int[]))

Named Parameters

Parameters can be named, just like in Raku:

-- sub query-nameds(Int :$a, :$b)
SELECT $a + $b

Just like in Raku, you can't have a positional parameter after a named one.

Mandatory Named Parameters

Also just like in Raku, named parameters can be marked mandatory:

-- sub query-nameds(:$mandatory!)
SELECT $a * 2

Return Sigils

default: +

The default one -- in you don't specify a return sigil, you get this. Returns the number of affected rows (as an Int).

-- sub count-unnests(--> +)
-- ... or ...
-- sub count-unnests()
UPDATE products
   SET price = 999
   WHERE price IS NULL

$

Returns a single value. Nil is returned otherwise:

-- sub get-username(Str $token --> $)
SELECT username
FROM users
WHERE token = $token

Typed $

Calls .new on the given type with all the data returned from the SQL query:

-- sub get-user(Int $id --> MyApp::Models::User)
SELECT 1 AS id, 'steve' AS username

You'll usually need to import type module that provides the type, by placing a use at the top of the SQL file:

-- use MyApp::Models;

class MyApp::Models::User {
  has Int $.id;
  has Str $.username;
}
...
my MyApp::Models::User $user = get-user(db, 1);
# Result: MyApp::Models::User.new(id => 1, :username<steve>);

%

Returns a hash.

-- sub get-hash(--> %)
SELECT 'comment' as type, 'Hello world!' as txt

my %h = get-hash($db);
# Result: %(type => "comment", txt => "Hello world!")

If the database doesn't return anything, Badger gives you an empty hash back.

@

Returns an array of hashes.

-- sub get-hashes(--> @)
SELECT 'comment' as type, txt
FROM unnest(array['Hello', 'world!']) txt

my @hashes = get-hashes($db);
# Result: %(type => "comment", txt => "Hello"), %(type => "comment", txt => "world!")

Typed @

Calls .new on the given type on each row of the data returned from the SQL query:

-- sub get-data(--> Datum @)
SELECT row_number() OVER () as id
     , unnest(ARRAY['a','b']) as value

class Datum {
  has Int $.id;
  has Str $.value;                                                                                                                                                    
}
...
my Datum @data = get-data($db);
# Result: Datum.new(id => 1, :value<a>), Datum.new(id => 2, :value<b>)

DESCRIPTION

AUTHORS

vedethiel
Jonathan Worthington

Source can be located at: https://github.com/raku-community-modules/Badger . Comments and Pull Requests are welcome.

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the Artistic License 2.0.