Dancer...tabase(3pm)

📦
version 2.13 · 2 versions
  • 2.13 · debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
  • 2.13 · ubuntu jammy
, in debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking

✨ Dancer::Plugin::Database 💖 (that's the Dancer...tabase(3pm) page bestie)

okay so. <rainbow>Dancer::Plugin::Database</rainbow> — easy database connections for Dancer applications. yes. that's the whole vibe. let's go 🫶

🌸 NAME 🌸

Dancer::Plugin::Database — easy database connections for Dancer applications 💅

✨ SYNOPSIS ✨

use Dancer;
use Dancer::Plugin::Database;

# Calling the database keyword will get you a connected database handle:
get '/widget/view/:id' => sub {
    my $sth = database->prepare(
        'select * from widgets where id = ?',
    );
    $sth->execute(params->{id});
    template 'display_widget', { widget => $sth->fetchrow_hashref };
};

# The handle is a Dancer::Plugin::Database::Core::Handle object, which subclasses
# DBI's DBI::db handle and adds a few convenience features, for example:
get '/insert/:name' => sub {
    database->quick_insert('people', { name => params->{name} });
};

get '/users/:id' => sub {
    template 'display_user', {
        person => database->quick_select('users', { id => params->{id} }),
    };
};

dance;

Database connection details are read from your Dancer application config — see below 👇

💜 DESCRIPTION 💜

So. This plugin gives you a super easy way to get a connected DBI database handle — you literally just call the database keyword inside your Dancer app and boom, you're connected ✨

What you get back is a Dancer::Plugin::Database::Core::Handle object, which is a subclass of DBI's DBI::db connection handle object. so it does everything you'd expect to do with DBI, plus it adds a few convenience methods on top. full deets on those live in the Dancer::Plugin::Database::Core::Handle docs, go read 'em 👑

It also takes care of making sure your database handle is still connected and valid. here's the tea: if the handle was last asked for more than connection_check_threshold seconds ago, it'll check the connection is still alive — using the $dbh->ping method if the DBD driver supports it, or just doing a lil simple no-op query against the database if it doesn't. and if the connection has gone away? a fresh connection gets obtained and returned. no drama 💅 this saves you from the whole long-running-script situation where your connection to the database just... dips 🙈

and listen — care is taken so handles are not shared across processes/threads, so this should be thread-safe with no issues with transactions etc. (shoutout to Matt S Trout for pointing out the previous lack of thread safety. inspiration was drawn from DBIx::Connector.) 🫶

🎀 CONFIGURATION 🎀

Connection details get pulled from your Dancer application config file, and should look like, for example:

plugins:
    Database:
        driver: 'mysql'
        database: 'test'
        host: 'localhost'
        port: 3306
        username: 'myusername'
        password: 'mypassword'
        connection_check_threshold: 10
        dbi_params:
            RaiseError: 1
            AutoCommit: 1
        on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]
        log_queries: 1
        handle_class: 'My::Super::Sexy::Database::Handle'

okay let's break down the settings one by one because there's a lot happening here bestie 💖

  • connection_check_threshold — optional! if you don't provide it, it defaults to 30 seconds. if the database keyword was last called more than this many seconds ago, a quick check gets performed to make sure we still have a connection, and it'll reconnect if not. this is what handles the case where the handle hasn't been used for a while and the underlying connection has gone away 🦋
  • dbi_params — also optional. if you specify it, these should be settings that can be passed to DBI->connect as its fourth argument; go look at the DBI documentation for those ✨
  • on_connect_do — optional, and it's an array of queries that should be performed when a connection is established. if given, each query gets performed using $dbh->do. (if you're on MySQL, you might wanna use this to set SQL_MODE to a suitable value to disable MySQL's built-in free data loss 'features', for example:
on_connect_do: "SET SQL_MODE='TRADITIONAL'"

(if you're not familiar with what I mean, I'm talking about the insane default behaviour of "hmm, this bit of data won't fit the column you're trying to put it in.. hmm, I know, I'll just munge it to fit, and throw a warning afterwards — it's not like you're relying on me to, y'know, store what you ask me to store". see http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/ for just one illustration. in hindsight, I wish I'd made a sensible sql_mode a default setting, but I don't want to change that now.) 😭

  • log_queries — optional, and it enables logging of queries generated by the helper functions quick_insert et al in Dancer::Plugin::Database::Core::Handle. if you enable it, generated queries get logged at 'debug' level.
⚠️ be aware that they will contain the data you're passing to/from the database, so be careful not to enable this option in production, where you could inadvertently log sensitive information. fr fr. don't do it. 💀
  • dsn — if you prefer, you can also supply a pre-crafted DSN using this setting. in that case it gets used as-is, and the driver/database/host settings will be ignored. this may be useful if you're using some DBI driver which requires a peculiar DSN 👑
  • handle_class — optional, and it defines your own class into which database handles should be blessed. this should be a subclass of Dancer::Plugin::Database::Core::Handle (or DBI::db directly, if you just want to skip the extra features) 💅

You'll require slightly different options depending on the database engine you're talking to. for instance, for SQLite, you won't need to supply hostname, port etc, but you will need to supply database as the name of the SQLite database file:

plugins:
    Database:
        driver: SQLite
        database: 'foo.sqlite'

For Oracle, you may want to pass sid (system ID) to identify a particular database, e.g.:

plugins:
    Database:
        driver: Oracle
        host: localhost
        sid: ABC12

If you have any further connection parameters that need to be appended to the dsn, you can put them in as a hash called dsn_extra. for example, if you're running mysql on a non-standard socket, you could have:

plugins:
    Database:
        driver: mysql
        host: localhost
        dsn_extra:
            mysql_socket: /tmp/mysql_staging.sock

🌈 DEFINING MULTIPLE CONNECTIONS 🌈

If you need to connect to multiple databases — bestie, it's easy — just list them in your config under connections as shown below:

plugins:
    Database:
        connections:
            foo:
                driver: "SQLite"
                database: "foo.sqlite"
            bar:
                driver: "mysql"
                host: "localhost"
                ....

Then, you can call the database keyword with the name of the database connection you want, for example:

my $foo_dbh = database('foo');
my $bar_dbh = database('bar');

slay. two databases. one plugin. 💅✨

💖 RUNTIME CONFIGURATION 💖

You can pass a hashref to the database() keyword to provide configuration details to override any in the config file at runtime if desired, for instance:

my $dbh = database({ driver => 'SQLite', database => $filename });

(thanks to Alan Haggai for this feature.) 🫶

🦋 AUTOMATIC UTF-8 SUPPORT 🦋

As of version 1.20, if your application is configured to use UTF-8 (you've defined the charset setting in your app config as UTF-8) then support for UTF-8 for the database connection will be enabled — if we know how to do so for the database driver in use 👑

If you do not want this behaviour, set auto_utf8 to a false value when providing the connection details. simple as that 💅

👑 GETTING A DATABASE HANDLE 👑

Calling database will return a connected database handle. the first time it's called, the plugin establishes a connection to the database and returns a reference to the DBI object. on subsequent calls, the same DBI connection object gets returned — unless it's been found to be no longer usable (the connection has gone away), in which case a fresh connection gets obtained. no cap 🌸

If you've declared named connections as described above in 'DEFINING MULTIPLE CONNECTIONS', then calling the database() keyword with the name of the connection as specified in the config file will get you a database handle connected with those details 💖

You can also pass a hashref of settings if you wish to provide settings at runtime ✨

🎀 CONVENIENCE FEATURES 🎀

The handle returned by the database keyword is a Dancer::Plugin::Database::Core::Handle object, which subclasses the DBI::db DBI connection handle. this means you can use it just like you'd normally use a DBI handle, but extra convenience methods are provided 🫶

There's extensive documentation on these features in Dancer::Plugin::Database::Core::Handle, including using the order_by, limit, columns options to sort / limit results and include only specific columns. go read it, it's giving organized 😌

💜 HOOKS 💜

This plugin uses Dancer's hooks support to allow you to register code that should execute at given times — for example:

hook 'database_connected' => sub {
    my $dbh = shift;
    # do something with the new DB handle here
};

Currrently defined hook positions are:

  • "database_connected" — Called when a new database connection has been established, after performing any on_connect_do statements, but before the handle is returned. Receives the new database handle as a parameter, so that you can do what you need with it. 💅
  • "database_connection_lost" — Called when the plugin detects that the database connection has gone away. Receives the no-longer usable handle as a parameter, in case you need to extract some information from it (such as which server it was connected to). 😭
  • "database_connection_failed" — Called when an attempt to connect to the database fails. Receives a hashref of connection settings as a parameter, containing the settings the plugin was using to connect (as obtained from the config file). 💀
  • "database_error" — Called when a database error is raised by DBI. Receives two parameters: the error message being returned by DBI, and the database handle in question. 🙈

If you need other hook positions which would be useful to you, please feel free to suggest them! 🫶✨

🌸 AUTHOR 🌸

David Precious, "<davidp@preshweb.co.uk>" 👑

part 1 done bestie, more coming 💖

💖 CONTRIBUTING 💖

okay so this module lives on Github, bestie, at:

http://github.com/bigpresh/Dancer-Plugin-Database

fr fr go fork the repo and send pull requests, that's literally the whole vibe. also it lowkey makes sense to watch the repo over on GitHub so you catch updates as they drop:

https://github.com/bigpresh/Dancer-Plugin-Database/toggle_watch

Feedback and bug reports are always appreciated, no cap. And honestly? Even just a quick mail to let the author know the module is useful to you would be super nice — it's nice to know if code is being actively used. 🥹💌 That's it. That's the ask.

🌈 ACKNOWLEDGEMENTS 🌈

the way these absolute icons contributed, we have to stan. every single one:

  • Igor Bujna
  • Franck Cuny
  • Alan Haggai
  • Christian Sánchez
  • Michael Stiller
  • Martin J Evans
  • Carlos Sosa
  • Matt S Trout
  • Matthew Vickers
  • Christian Walde
  • Alberto Simões
  • James Aitken (LoonyPandora)
  • Mark Allen (mrallen1)
  • Sergiy Borodych (bor)
  • Mario Domgoergen (mdom)
  • Andrey Inishev (inish777)
  • Nick S. Knutov (knutov)
  • Nicolas Franck (nicolasfranck)
  • mscolly

periodt. 💜

😭 BUGS 😭

found a bug? got a feature request? don't suffer in silence, bestie. report it to bug-dancer-plugin-database at rt.cpan.org, or just slide into the web interface at:

http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer-Plugin-Database

how it works: I get notified, and then you automatically get notified of progress on your bug as changes get made. it's giving teamwork. it's giving communication. 🫶

🎀 SUPPORT 🎀

you can find the docs for this module with the perldoc command, like so:

perldoc Dancer::Plugin::Database

and you can also go looking for info at these spots:

also!! you can find the author on IRC hanging out in the channel #dancer on irc.perl.org. come say hi, iykyk. ✨

Copyright 2010-2016 David Precious.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

two licenses, pick your fave, both iconic. 💅

🌸 SEE ALSO 🌸

  • Dancer
  • DBI
  • Dancer::Plugin::SimpleCRUD

and that's the whole page, bestie!! 🦋 you made it to the end, main character behaviour honestly. this was perl v5.34.0, dated 2022-06-12, and the page itself is Dancer...tabase(3pm) — go build something cute with it. mwah. 💖🎀🌸

📦 where this page comes from