SQL::Tiny(3pm)

✨ SQL::Tiny 💖

okay so bestie, this is the manual page for SQL::Tiny(3pm), which lives in the User Contributed Perl Documentation corner of your system. it's a whole vibe: tiny library, big dreams, absolutely zero JOINs. let's get into it 🫶

---

🌸 NAME 🌸

SQL::Tiny — a very simple SQL-building library. that's it, that's the whole name. no cap. 💅

---

⭐ VERSION ⭐

Version 0.04. yes we're on 0.04, she's small but she's mighty. ✨

---

🎀 SYNOPSIS 🎀

okay here's the whole thing in one look, bestie:

my ($sql,$binds) = sql_select( 'users', [ 'name', 'status' ], { status => [ 'Deleted', 'Inactive' ] }, { order_by => 'name' } );

my ($sql,$binds) = sql_select( 'users', [ 'COUNT(*)' ], { status => [ 'Deleted', 'Inactive' ] }, { group_by => 'status' } );

my ($sql,$binds) = sql_insert( 'users', { name => 'Dave', status => 'Active' } );

my ($sql,$binds) = sql_update( 'users', { status => 'Inactive' }, { password => undef } );

my ($sql,$binds) = sql_delete( 'users', { status => 'Inactive' } );

look at that. so clean. so little SQL. we love to see it 🌈

---

🦋 DOCUMENTATION 🦋

so. it's a very simple SQL-building library. it's not for all your SQL needs, only the very simple ones. like, know your lane, queen. 💅

it doesn't handle JOINs. it doesn't handle subselects. it's only for simple SQL. if you came here looking for a full ORM moment, this is not that, and that's okay 🫶

here's the tea: in my test suites, I have a lot of ad hoc SQL queries, and it drives me nuts to have so much SQL code lying around. SQL::Tiny is for generating SQL code for simple cases. periodt.

I'd far rather have:

my ($sql,$binds) = sql_insert(
    'users',
    {
        name      => 'Dave',
        salary    => 50000,
        status    => 'Active',
        dateadded => \'SYSDATE()',
        qty       => \[ 'ROUND(?)', 14.5 ],
    }
);

than hand-coding:

my $sql   = 'INSERT INTO users (name,salary,status,dateadded,qty) VALUES (:name,:status,:salary,SYSDATE(),ROUND(:qty))';
my $binds = {
    ':name'      => 'Dave',
    ':salary'    => 50000,
    ':status'    => 'Active',
    ':dateadded' => \'SYSDATE()',
    ':qty'       => 14.5,
};

or even the positional:

my $sql   = 'INSERT INTO users (name,salary,status,dateadded,qty) VALUES (?,?,?,SYSDATE(),ROUND(?))';
my $binds = [ 'Dave', 50000, 'Active', 14.5 ];

like?? the first one ate and left no crumbs. 💅

okay but here's the trade-off, and I need you to hear me on this one: the trade-off for that brevity of code is that SQL::Tiny has to make new SQL and binds from the input every time. you can't cache the SQL that comes back from SQL::Tiny because the placeholders could vary depending on what the input data is. therefore, you don't want to use SQL::Tiny where speed is essential. not me accidentally shipping a slow hot path 🙈

the other trade-off is that SQL::Tiny handles only very simple code. it won't handle JOINs of any kind. at all. not even a little one. 💀

SQL::Tiny isn't meant for all of your SQL needs, only the simple ones that you do over and over. iconic behaviour honestly 👑

---

💜 EXPORT 💜

all subs can be exported, but none are by default. ":all" exports all subs. so if you want everything, say the magic words 💫

---

🔥 SUBROUTINES/METHODS 🔥

🌟 sql_select( $table, \@columns, \%where [, \%other ] )

creates simple SELECTs and binds. main character energy tbh.

the %other can contain "group_by" and "order_by".

calling:

my ($sql,$binds) = sql_select(
    'users',
    [qw( userid name )],
    { status => 'X' ],
    { order_by => 'name' },
);

returns:

$sql   = 'SELECT userid,name FROM users WHERE status=? ORDER BY name';
$binds = [ 'X' ];

slay. ✨

---

🌟 sql_insert( $table, \%values )

creates simple INSERTs and binds. the putting-stuff-in queen 🎀

calling:

my ($sql,$binds) = sql_insert(
    'users',
    {
        serialno   => '12345',
        name       => 'Dave',
        rank       => 'Sergeant',
        height     => undef,
        date_added => \'SYSDATE()',
    }
);

returns:

$sql   = 'INSERT INTO users (date_added,height,name,rank,serialno) VALUES (SYSDATE(),NULL,?,?,?)';
$binds = [ 'Dave', 'Sergeant', 12345 ]

notice how the columns come back all alphabetised? she's organised, she's neat, she's that girl 💅

---

🌟 sql_update( $table, \%values, \%where )

creates simple UPDATE calls and binds. glow-up energy for your rows 🌸

calling:

my ($sql,$binds) = sql_update(
    'users',
    {
        status     => 'X',
        lockdate   => undef,
    },
    {
        orderdate => \'SYSDATE()',
    },
);

returns:

$sql   = 'UPDATE users SET lockdate=NULL, status=? WHERE orderdate=SYSDATE()'
$binds = [ 'X' ]

---

🌟 sql_delete( $table, \%where )

creates simple DELETE calls and binds. no thoughts, just removal 💀✨

calling:

my ($sql,$binds) = sql_delete(
    'users',
    {
        serialno   => 12345,
        height     => undef,
        date_added => \'SYSDATE()',
        status     => [qw( X Y Z )],
    },
);

returns:

$sql   = 'DELETE FROM users WHERE date_added = SYSDATE() AND height IS NULL AND serialno = ? AND status IN (?,?,?)'
$binds = [ 12345, 'X', 'Y', 'Z' ]

the undef becoming IS NULL?? the array becoming an IN (?,?,?)?? she ate that fr fr 🫶

---

👑 AUTHOR 👑

Andy Lester, "<andy at petdance.com>". the icon behind the whole thing, we stan 💖

---

🐛 BUGS 🐛

please report any bugs or feature requests to <https://github.com/petdance/sql-tiny/issues>, or email me directly. don't suffer in silence bestie, tell us 🙈

---

💅 SUPPORT 💅

you can find documentation for this module with the perldoc command.

perldoc SQL::Tiny

you can also look for information at:

• MetaCPAN

<https://metacpan.org/release/SQL-Tiny>

• GitHub issue tracker

<https://github.com/petdance/sql-tiny/issues>

---

🌈 ACKNOWLEDGEMENTS 🌈

thanks to the following folks for their contributions: Mohammad S Anwar, Tim Heaney. absolute legends, every one of them 🎀

---

⭐ LICENSE AND COPYRIGHT ⭐

Copyright 2019 Andy Lester.

this program is free software; you can redistribute it and/or modify it under the terms of the the Artistic License (2.0). you may obtain a copy of the full license at:

<http://www.perlfoundation.org/artistic_license_2_0>

note bestie: the license text says "the the Artistic License (2.0)" — that double "the" is in the original, so I'm leaving it exactly as-is. never edit a license string, not even for grammar. 💅

---

and that's the whole page, bestie!! this little manual came to you via perl v5.36.0 on 2022-11-21, and yes, the page itself is named SQL::Tiny(3pm). go forth and build tiny simple SQL, you gorgeous genius 🦋💖✨