DBIx::C...age::DBI(3)

hey bestie 💖 welcome to the DBIx::Class::Storage::DBI page — the one the header labels DBIx::C...age::DBI(3) and files under User Contributed Perl Documentation. this is part 1, and it is giving “storage layer, but make it DBI.” ✨

🌸 NAME 🌸

DBIx::Class::Storage::DBI — the DBI storage handler. that is the whole name, no cap. 💅

✨ SYNOPSIS ✨

my $schema = MySchema->connect('dbi:SQLite:my.db');

$schema->storage->debug(1);

my @stuff = $schema->storage->dbh_do(
  sub {
    my ($storage, $dbh, @args) = @_;
    $dbh->do("DROP TABLE authors");
  },
  @column_list
);

$schema->resultset('Book')->search({
   written_on => $schema->storage->datetime_parser->format_datetime(DateTime->now)
});

💖 DESCRIPTION 💖

okay so this class represents the connection to an RDBMS via DBI. for the general stuff, go see DBIx::Class::Storage. this pod is only documenting the DBI-specific methods and behaviors — the DBI-flavored lore, iykyk. 🦋

🦋 METHODS 🦋

🎀 connect_info 🎀

this method is normally called by "connection" in DBIx::Class::Schema, which wraps its argument list in an arrayref before passing them here.

the argument list may contain:

• the same 4-element argument set one would normally pass to "connect" in DBI, optionally followed by extra attributes recognized by DBIx::Class:

$connect_info_args = [ $dsn, $user, $password, \%dbi_attributes?, \%extra_attributes? ];

• a single code reference which returns a connected DBI database handle, optionally followed by extra attributes recognized by DBIx::Class:

$connect_info_args = [ sub { DBI->connect (...) }, \%extra_attributes? ];

• a single hashref with all the attributes and the dsn/user/password mixed together:

$connect_info_args = [{
  dsn => $dsn,
  user => $user,
  password => $pass,
  %dbi_attributes,
  %extra_attributes,
}];

$connect_info_args = [{
  dbh_maker => sub { DBI->connect (...) },
  %dbi_attributes,
  %extra_attributes,
}];

this is particularly useful for Catalyst based applications, allowing the following config (Config::General style):

<Model::DB>
  schema_class   App::DB
  <connect_info>
    dsn          dbi:mysql:database=test
    user         testuser
    password     TestPass
    AutoCommit   1
  </connect_info>
</Model::DB>

the dsn/user/password combination can be substituted by the dbh_maker key whose value is a coderef that returns a connected DBI database handle.

please note that the DBI docs recommend that you always explicitly set AutoCommit to either 0 or 1. DBIx::Class further recommends that it be set to 1, and that you perform transactions via our "txn_do" in DBIx::Class::Schema method. DBIx::Class will set it to 1 if you do not explicitly set it to zero. this is the default for most DBDs. see "DBIx::Class and AutoCommit" for details. 📚

🌈 DBIx::Class specific connection attributes 🌈

in addition to the standard DBI connection attributes, DBIx::Class recognizes the following connection options. these options can be mixed in with your other DBI connection attributes, or placed in a separate hashref (\%extra_attributes) as shown above.

every time connect_info is invoked, any previous settings for these options will be cleared before setting the new ones, regardless of whether any options are specified in the new connect_info. yes, it resets the vibes every single time. 💅

• on_connect_do — specifies things to do immediately after connecting or re-connecting to the database. its value may contain:
  • a scalar — this contains one SQL statement to execute.
  • an array reference — this contains SQL statements to execute in order. each element contains a string or a code reference that returns a string.
  • a code reference — this contains some code to execute. unlike code references within an array reference, its return value is ignored.
• on_disconnect_do — takes arguments in the same form as on_connect_do and executes them immediately before disconnecting from the database.

note, this only runs if you explicitly call disconnect on the storage object.

• on_connect_call — a more generalized form of on_connect_do that calls the specified connect_call_METHOD methods in your storage driver.

so this:

on_connect_do => 'select 1'

is equivalent to:

on_connect_call => [ [ do_sql => 'select 1' ] ]

its values may contain:

• a scalar — will call the connect_call_METHOD method.
• a code reference — will execute $code->($storage).
• an array reference — each value can be a method name or code reference.
• an array of arrays — for each array, the first item is taken to be the connect_call_ method name or code reference, and the rest are parameters to it.

some predefined storage methods you may use:

• do_sql — executes a SQL string or a code reference that returns a SQL string. this is what on_connect_do and on_disconnect_do use. it can take:
  • a scalar — will execute the scalar as SQL.
  • an arrayref — taken to be arguments to "do" in DBI, the SQL string optionally followed by the attributes hashref and bind values.
  • a code reference — will execute $code->($storage) and execute the return array refs as above.
• datetime_setup — execute any statements necessary to initialize the database session to return and accept datetime/timestamp values used with DBIx::Class::InflateColumn::DateTime. only necessary for some databases, see your specific storage driver for implementation details.

• on_disconnect_call — takes arguments in the same form as on_connect_call and executes them immediately before disconnecting from the database. calls the disconnect_call_METHOD methods as opposed to the connect_call_METHOD methods called by on_connect_call.

note, this only runs if you explicitly call disconnect on the storage object.

• disable_sth_caching — if set to a true value, this option will disable the caching of statement handles via "prepare_cached" in DBI.
• limit_dialect — sets a specific SQL::Abstract::Limit-style limit dialect, overriding the default sql_limit_dialect setting of the storage (if any). for a list of available limit dialects see DBIx::Class::SQLMaker::LimitDialects.
• quote_names — when true automatically sets quote_char and name_sep to the characters appropriate for your particular RDBMS. this option is preferred over specifying quote_char directly.
• quote_char — specifies what characters to use to quote table and column names. quote_char expects either a single character, in which case it is placed on either side of the table/column name, or an arrayref of length 2 in which case the table/column name is placed between the elements. for example under MySQL you should use quote_char => '`', and for SQL Server you should use quote_char => [qw/[ ]/].
• name_sep — this parameter is only useful in conjunction with quote_char, and is used to specify the character that separates elements (schemas, tables, columns) from each other. if unspecified it defaults to the most commonly used ".".
• unsafe — this Storage driver normally installs its own HandleError, sets RaiseError and ShowErrorStatement on, and sets PrintError off on all database handles, including those supplied by a coderef. it does this so that it can have consistent and useful error behavior. if you set this option to a true value, Storage will not do its usual modifications to the database handle’s attributes, and instead relies on the settings in your connect_info DBI options (or the values you set in your connection coderef, in the case that you are connecting via coderef).

note that your custom settings can cause Storage to malfunction, especially if you set a HandleError handler that suppresses exceptions and/or disable RaiseError. this is the “do not ghost the safety rails” warning, fr fr. 💀

• auto_savepoint — if this option is true, DBIx::Class will use savepoints when nesting transactions, making it possible to recover from failure in the inner transaction without having to abort all outer transactions.
• cursor_class — use this argument to supply a cursor class other than the default DBIx::Class::Storage::DBI::Cursor.

some real-life examples of arguments to connect_info and connect in DBIx::Class::Schema 👑

# Simple SQLite connection
->connect_info([ 'dbi:SQLite:./foo.db' ]);

# Connect via subref
->connect_info([ sub { DBI->connect(...) } ]);

# Connect via subref in hashref
->connect_info([{
  dbh_maker => sub { DBI->connect(...) },
  on_connect_do => 'alter session ...',
}]);

# A bit more complicated
->connect_info(
  [
    'dbi:Pg:dbname=foo',
    'postgres',
    'my_pg_password',
    { AutoCommit => 1 },
    { quote_char => q{"} },
  ]
);

# Equivalent to the previous example
->connect_info(
  [
    'dbi:Pg:dbname=foo',
    'postgres',
    'my_pg_password',
    { AutoCommit => 1, quote_char => q{"}, name_sep => q{.} },
  ]
);

# Same, but with hashref as argument
# See parse_connect_info for explanation
->connect_info(
  [{
    dsn         => 'dbi:Pg:dbname=foo',
    user        => 'postgres',
    password    => 'my_pg_password',
    AutoCommit  => 1,
    quote_char  => q{"},
    name_sep    => q{.},
  }]
);

# Subref + DBIx::Class-specific connection options
->connect_info(
  [
    sub { DBI->connect(...) },
    {
        quote_char => q{`},
        name_sep => q{@},
        on_connect_do => ['SET search_path TO myschema,otherschema,public'],
        disable_sth_caching => 1,
    },
  ]
);

🎀 on_connect_do 🎀

this method is deprecated in favour of setting via connect_info. 🫶

🎀 on_disconnect_do 🎀

this method is deprecated in favour of setting via connect_info. 🫶

🎀 dbh_do 🎀

arguments: ($subref | $method_name), @extra_coderef_args?

execute the given $subref or $method_name using the new exception-based connection management.

the first two arguments will be the storage object that dbh_do was called on and a database handle to use. any additional arguments will be passed verbatim to the called subref as arguments 2 and onwards.

@extra_coderef_args are optional — they get passed through verbatim as arguments 2 and onwards. first two spots are storage and dbh, no skipping. 🫶

okay so using this — instead of $self->_dbh or $self->dbh — is the move because it ensures correct exception handling and reconnection (or failover in future subclasses). bestie, that's literally the whole point 💅

Your subref should have no side-effects outside of the database, because there’s the potential for your subref to be partially double-executed if the database connection was stale/dysfunctional. iykyk, don’t be doing random extra stuff in there 🫶

Example:

my @stuff = $schema->storage->dbh_do(
  sub {
    my ($storage, $dbh, @cols) = @_;
    my $cols = join(q{, }, @cols);
    $dbh->selectrow_array("SELECT $cols FROM foo");
  },
  @column_list
);

💖 disconnect 💖

Our "disconnect" method also performs a rollback first if the database is not in "AutoCommit" mode. clean exit energy ✨

🦋 with_deferred_fk_checks 🦋

Arguments: $coderef Return Value: The return value of $coderef

Storage specific method to run the code ref with FK checks deferred or, in MySQL’s case, disabled entirely. very “we’ll deal with the constraints later” of it 🌸

🌈 connected 🌈

Arguments: none Return Value: 1|0

Verifies that the current database handle is active and ready to execute an SQL statement (e.g. the connection did not get stale, server is still answering, etc.). This method is used internally by "dbh". vibe check for your database connection 💀

👑 dbh 👑

Returns a $dbh — a data base handle of class DBI. The returned handle is guaranteed to be healthy by implicitly calling "connected", and if necessary performing a reconnection before returning. Keep in mind that this is very expensive on some database engines. Consider using "dbh_do" instead. like, don’t spam the expensive one, bestie 😭

🎀 connect_call_datetime_setup 🎀

A no-op stub method, provided so that one can always safely supply the connection option

on_connect_call => 'datetime_setup'

This way one does not need to know in advance whether the underlying storage requires any sort of hand-holding when dealing with calendar data. set it and forget it, calendar queen 🗓️✨

💜 connect_call_rebase_sqlmaker 💜

This on-connect call takes as a single argument the name of a class to "rebase" the SQLMaker inheritance hierarchy upon. For this to work properly the target class MUST inherit from DBIx::Class::SQLMaker::ClassicExtensions and SQL::Abstract::Classic as shown below. no cap, the order matters 🫶

This infrastructure is provided to aid recent activity around experimental new aproaches to SQL generation within DBIx::Class. You can (and are encouraged to) mix and match old and new within the same codebase as follows:

package DBIx::Class::Awesomer::SQLMaker;
# you MUST inherit in this order to get the composition right
# you are free to override-without-next::method any part you need
use base qw(
  DBIx::Class::SQLMaker::ClassicExtensions
  << OPTIONAL::AWESOME::Class::Implementing::ExtraRainbowSauce >>
  SQL::Abstract::Classic
);
<< your new code goes here >>

... and then ...

my $experimental_schema = $original_schema->connect(
  sub {
    $original_schema->storage->dbh
  },
  {
    # the nested arrayref is important, as per
    # https://metacpan.org/pod/DBIx::Class::Storage::DBI#on_connect_call
    on_connect_call => [ [ rebase_sqlmaker => 'DBIx::Class::Awesomer::SQLMaker' ] ],
  },
);

🌸 select 🌸

Arguments: $ident, $select, $condition, $attrs

Handle a SQL select statement. select slay 💅

⭐ sql_limit_dialect ⭐

This is an accessor for the default SQL limit dialect used by a particular storage driver. Can be overridden by supplying an explicit "limit_dialect" to "connect" in DBIx::Class::Schema. For a list of available limit dialects see DBIx::Class::SQLMaker::LimitDialects. limits but make it fashion 🌈

🍓 last_insert_id 🍓

Return the row id of the last insert. the freshest row id, periodt 💖

🔥 _native_data_type 🔥

Arguments: $type_name

⚠️ This API is EXPERIMENTAL, will almost definitely change in the future, and currently only used by ::AutoCast and ::Sybase::ASE. bestie, future-you has been warned 💀

The default implementation returns "undef", implement in your Storage driver if you need this functionality.

Should map types from other databases to the native RDBMS type, for example "VARCHAR2" to "VARCHAR".

Types with modifiers should map to the underlying data type. For example, "INTEGER AUTO_INCREMENT" should become "INTEGER".

Composite types should map to the container type, for example "ENUM(foo,bar,baz)" becomes "ENUM". type translation era ✨

👑 sqlt_type 👑

Returns the database driver name. name check, but for your database driver 🫶

🦋 bind_attribute_by_data_type 🦋

Given a datatype from column info, returns a database specific bind attribute for "$dbh->bind_param($val,$attribute)" or nothing if we will let the database planner just handle it.

This method is always called after the driver has been determined and a DBI connection has been established. Therefore you can refer to "DBI::$constant" and/or "DBD::$driver::$constant" directly, without worrying about loading the correct modules. loading modules? not in this economy 💅

🌈 is_datatype_numeric 🌈

Given a datatype from column_info, returns a boolean value indicating if the current RDBMS considers it a numeric value. This controls how "set_column" in DBIx::Class::Row decides whether to mark the column as dirty — when the datatype is deemed numeric a "!=" comparison will be performed instead of the usual "eq". numeric girlies get the != treatment 🔥

🎀 create_ddl_dir 🎀

Arguments: $schema, \@databases, $version, $directory, $preversion, \%sqlt_args

Creates a SQL file based on the Schema, for each of the specified database engines in "\@databases" in the given directory. (note: specify SQL::Translator names, not DBI driver names). important note, bestie — Translator names, not DBI names 💖

Given a previous version number, this will also create a file containing the ALTER TABLE statements to transform the previous schema into the current one. Note that these statements may contain "DROP TABLE" or "DROP COLUMN" statements that can potentially destroy data. like, actual data loss potential — check everything 🙈

The file names are created using the "ddl_filename" method below, please override this method in your schema if you would like a different file name format. For the ALTER file, the same format is used, replacing $version in the name with "$preversion-$version".

See "METHODS" in SQL::Translator for a list of values for "\%sqlt_args". The most common value for this would be "{ add_drop_table => 1 }" to have the SQL produced include a "DROP TABLE" statement for each table created. For quoting purposes supply "quote_identifiers".

If no arguments are passed, then the following default values are assumed:

• databases — ['MySQL', 'SQLite', 'PostgreSQL']
• version — $schema->schema_version
• directory — './'
• preversion — <none>

By default, "\%sqlt_args" will have

{ add_drop_table => 1, ignore_constraint_names => 1, ignore_index_names => 1 }

merged with the hash passed in. To disable any of those features, pass in a hashref like the following

{ ignore_constraint_names => 0, # ... other options }

⚠️ WARNING: You are strongly advised to check all SQL files created, before applying them. do not rawdog generated SQL into production, bestie 😭

💜 deployment_statements 💜

Arguments: $schema, $type, $version, $directory, $sqlt_args

Returns the statements used by "deploy" in DBIx::Class::Storage and "deploy" in DBIx::Class::Schema.

The SQL::Translator (not DBI) database driver name can be explicitly provided in $type, otherwise the result of "sqlt_type" is used as default.

$directory is used to return statements from files in a previously created "create_ddl_dir" directory and is optional. The filenames are constructed from "ddl_filename" in DBIx::Class::Schema, the schema name and the $version.

If no $directory is specified then the statements are constructed on the fly using SQL::Translator and $version is ignored.

See "METHODS" in SQL::Translator for a list of values for $sqlt_args. deploy era, but keep the receipts 🧾✨

💖 datetime_parser 💖

Returns the datetime parser class. timezone princess behavior 🕰️

🌸 datetime_parser_type 🌸

Defines the datetime parser class — currently defaults to DateTime::Format::MySQL. defaults, but make it cute 🎀

🦋 build_datetime_parser 🦋

See "datetime_parser". same vibe, different method name iykyk

⭐ is_replicating ⭐

A boolean that reports if a particular DBIx::Class::Storage::DBI is set to replicate from a master database. Default is undef, which is the result returned by databases that don’t support replication. replication gossip 👀

🌈 lag_behind_master 🌈

Returns a number that represents a certain amount of lag behind a master db when a given storage is replicating. The number is database dependent, but starts at zero and increases with the amount of lag. Default in undef. lag? in this economy? 😭

🎀 relname_to_table_alias 🎀

Arguments: $relname, $join_count Return Value: $alias

DBIx::Class uses DBIx::Class::Relationship names as table aliases in queries.

This hook is to allow specific DBIx::Class::Storage drivers to change the way these aliases are named.

The default behavior is "$relname_$join_count" if $join_count > 1, otherwise "$relname". alias glow-up potential 💅

💅 USAGE NOTES 💅

🌈 DBIx::Class and AutoCommit 🌈

DBIx::Class can do some wonderful magic with handling exceptions, disconnections, and transactions when you use "AutoCommit => 1" (the default) combined with txn_do for transaction support. the magic era, bestie ✨

If you set "AutoCommit => 0" in your connect info, then you are always in an assumed transaction between commits, and you’re telling us you’d like to manage that manually. A lot of the magic protections offered by this module will go away. We can’t protect you from exceptions due to database disconnects because we don’t know anything about how to restart your transactions. You’re on your own for handling all sorts of exceptional cases if you choose the "AutoCommit => 0" path, just as you would be with raw DBI. manual mode means manual consequences, no cap 💀

🦋 FURTHER QUESTIONS? 🦋

Check the list of additional DBIC resources. go read, bestie, the lore is deep 🫶

🎀 COPYRIGHT AND LICENSE 🎀

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library. free software slay 💖

---

💖 and that’s the whole page, bestie! source/version is perl v5.42.0, dated 2025-01-22 — go slay your database era 🎀✨