DBIx::Abstract(3pm)

📦

version 1.040 · 2 versions

1.040 · debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
1.040 · ubuntu jammy

, in debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking

✨ DBIx::Abstract ✨ — the manual page, bestie edition 💅

okay so you found the DBIx::Abstract(3pm) page, a User Contributed Perl Documentation situation, and honestly? you're in the right place. sit down, hydrate 🍓, and let's get into it.

🌸 NAME 🌸

DBIx::Abstract — DBI SQL abstraction. that's it, that's the whole vibe check. 💖

🎀 VERSION 🎀

version 1.04 — no cap, that's the number right there. ⭐

✨ SYNOPSIS ✨

here's the little taste test, copy it, run it, live your truth 🦋:

  use DBIx::Abstract;
  my $db = DBIx::Abstract->connect({
    driver=>'mydriver',
    host=>'myhost.org',
    dbname=>'mydb',
    user=>'myuser',
    password=>'mypassword',
    });

  if ($db->select('*','table')->rows) {
    while (my $data = $db->fetchrow_hashref) {
      # ...
    }
  }

  my $id = 23;

  my ($name) = $db->select('name','table',{id=>$id})->fetchrow_array;

  ###

  $db = DBIx::Abstract->connect( { driver=>'csv', f_name=>'foo/' } );

  ###

  $db = DBIx::Abstract->connect({
    dsn=>'dbi:someotherdb:so_db_name=mydb',
    user=>'myuser',
    password=>'mypassword',
    });

like… the way it just connects and selects? main character energy fr fr 👑

💜 DESCRIPTION 💜

so basically, this module provides methods for doing manipulating database tables. (yes, that's what it says.) and then, also: this module provides methods retrieving and storing data in SQL databases. it gives you methods for all of the more important SQL commands (like SELECT, INSERT, REPLACE, UPDATE, DELETE). 🔥

it endeavors to produce an interface that will be intuitive to those already familiar with SQL. lowkey thoughtful of it honestly 🫶

notable features include:

data_source generation for some DBD drivers.
Can check to make sure the connection is not stale and reconnect if it is.
Controls statement handles for you.
Can delay writes.
Generates complex where clauses from hashes and arrays.
Shortcuts (convenience functions) for some common cases. (Like select_all_to_hashref.)

it's giving "i handle the statement handles so you don't have to" 😭 iconic behaviour. 💅

💀 DEPRICATED 💀

yes, that spelling is on purpose, i'm keeping it exactly as-is. 🙈

We highly recommend that you use something like SQL::Abstract, which was inspired by this module. Or even DBIx::Class (which uses SQL::Abstract for it's query syntax). They're maintained and widely used.

so like… it still works, but the girlies have moved on to SQL::Abstract and DBIx::Class. they're maintained and widely used. DBIx::Abstract is the origin story, not the current era. 🌈

to be continued… 🌙

✨ METHODS ✨

Okay bestie, here's the thing you actually came for 💅 unless a method specifically says otherwise, every single one of these returns the database handle. Every. Single. One. No cap.

🔌 connect

connect($connect_config | $dbihandle [,$options])

CONSTRUCTOR ✨

This is the glow-up moment where you open a connection to a database, configured by $connect_config. And $connect_config can be one of a couple things:

If it's a scalar, it's just a DBI data source. Simple. Clean. Iconic.
If it's a reference to a hash, it takes these keys:

dsn — the data source to connect to your database 🫶

OR, DBIx::Abstract will try to generate the dsn for you if you hand it these instead:

driver — the DBD driver to use (defaults to mysql)
host — host of the database server
port — port of the database server
dbname — name of the database

Username and password are always valid, always welcome 💖

user — username to connect as
password — password for that user

Alternatively, you can pass in a DBI handle directly. But heads up — doing that disables the reconnect and ensure_connection methods, because those two rely on connection info that a bare DBI handle just doesn't have. It's giving limited info, not the full lore 😭

Options is a hash reference, and every key/value pair in it gets passed on to the opt method.

🧬 clone

This clones the object. For those times when you need a second connection to the same DB. If you need a second connection to a different DB, make a whole new object with connect instead, okay?

This operation gets logged at level 5 with the message "Cloned."

🩺 connected

Checks whether this object is connected to a database. Specifically it checks if it has a database handle and if that handle's "Active" attribute is true. Vibe check for your connection 🦋

♻️ reconnect

If the object is not connected to a database, it reconnects using the exact same parameters connect was originally called with. Muscle memory, fr fr.

🛟 ensure_connection

Makes sure the object is connected to a database. It confirms the connection is active (by sending a "SELECT 1"). If there's no connection, or the connection isn't active, it tries to reconnect. And if the reconnect fails? It dies. No soft launch here 💀

⚙️ opt

($key[,$value])

({key=>$key[,value=>$value])

Set option $key to $value. Here are the keys you can use:

loglevel (default 0)
- 0 — Fatal errors only
- 1 — Modifications
- 2 — And selects
- 3 — And user created queries
- 4 — And results of queries
- 5 — And other misc commands
- 6 — Internals of commands
logfile (default undef) — log file
delaymods (default false) — delay making modifications to the database until run_delayed is run
useCached — if this is true, then prepare_cached is used instead of prepare. Please actually check out the DBI documentation on this feature before you use it, bestie, I'm serious 🙈
saveSQL — if this is true, then with each query DBIx::Abstract will stuff the generated SQL into the 'lastsql' key in the self payload

On top of all that, you may use any valid DBI attribute. So, for instance, you can pass AutoCommit or LongReadLen. Sky's the limit ✨

This operation is logged at level 5 with the message "Option Change", plus the key, the old value, and the new value.

📨 query

($sql,@bind_params)

({sql=>$sql,bind_params=>[@bind_params]})

This sends $sql to the database object's query method. Use it for applications where the existing methods just aren't generating flexible enough SQL for you.

And listen — if you find yourself using this very often with things other than table manipulation (like 'create table', 'alter table', 'drop table'), then please let the author know so DBIx::Abstract can be extended to include the functionality you're using. They wanna know 🫶

This operation is logged at level 3.

⏳ run_delayed

Execute delayed update/insert/delete queries. The queue finally gets its moment 👑

This operation is logged at level 5 with the message "Run delayed".

🗑️ delete

($table[,$where])

({table=>$table[,where=>$where]})

Deletes records from $table. Also go read the docs on "DBIx::Abstract Where Clauses".

➕ insert

($table,$fields)

({table=>$table,fields=>$fields})

$table is the name of the table to insert into.

$fields is either a reference to a hash of field name/value, or a scalar containing the SQL to insert after the "SET" portion of the statement.

These all produce functionally equivalent SQL, bestie:

$db->insert('foo',{bar=>'baz'});
$db->insert('foo',q|bar='baz'|);
$db->insert({table=>'foo',fields=>{bar=>'baz'}});
$db->insert({table=>'foo',fields=>q|bar='baz'|});

We also support literals by making the value in the hash an arrayref:

$db->insert('foo',{name=>'bar',date=>['substring(now(),1,10)']});

Would generate something like this:

INSERT INTO foo (name,date) VALUES (?,substring(now(),1,10))

With "bar" bound to the first parameter. Slay 💅

🔄 replace

($table,$fields)

({table=>$table,fields=>$fields})

$table is the name of the table to replace into.

$fields is either a reference to a hash of field name/value, or a scalar containing the SQL to insert after the "SET" portion of the statement.

replace works just like insert, except that if a record with the same primary key already exists, then the existing record gets replaced instead of throwing an error. It's giving "no drama, just overwrite" 😌

✏️ update

($table,$fields[,$where])

({table=>$table,fields=>$fields[,where=>$where]})

$table is the table to update.

$fields is a reference to a hash keyed on field name / new value.

Also see the docs on "DBIx::Abstract Where Clauses".

🔍 select

select

($fields,[$table,[$where[,$order]]])

({ fields=>$fields, table=>$table [,where=>$where] [,order=>$order]
[,join=>$join] [,group=>$group] })

The select method returns the DBIx::Abstract object it was invoked with. Which means... you can chain commands. Main character energy ✨

$fields can be either an array reference or a scalar. If it's an array reference, then it should be a list of fields to include. If it's a scalar, then it should be a literal to be inserted into the generated SQL after "SELECT".

$table can be either an array reference or a scalar. If it's an array reference, then it should be a list of tables to use. If it's a scalar, then it should be a literal to be inserted into the generated SQL after "FROM".

Also see the docs on "DBIx::Abstract Where Clauses".

$order is the output order. If it's a scalar, it gets inserted literally after "ORDER BY". If it's an arrayref, it gets join'd with a comma and inserted.

$join is there to make joining tables more convenient. It takes one or more (as an arrayref) sets of statements to use when joining. For instance:

$dbh->select({
  fields=>'*',
  table=>'foo,bar',
  join=>'foo.id=bar.foo_id',
  where=>{'foo.dollars',['>',30]}
  });

Would produce:

SELECT * FROM foo,bar WHERE (foo.dollars > ?) and (foo.id=foo_id)

And put 30 into the bind_params list. Chef's kiss 🔥

$group is/are the field(s) to group by. It may be a scalar or an arrayref. If it's a scalar, then it should be a literal to be inserted after "GROUP BY". If it's an arrayref, then it should be a list of fields to group on.

🎯 select_one_to_hashref

($fields,$table[,$where])

({fields=>$fields,table=>$table[,where=>$where]})

This returns a hashref to the first record returned by the select. Typically you'd use this for cases when your where clause already limits you to one record anyway.

$fields can be either an array reference or a scalar. If it's an array reference, then it should be a list of fields to include. If it's a scalar, then it should be a literal to be inserted into the generated SQL.

$table is the table to select from.

Also see the docs on "DBIx::Abstract Where Clauses".

🎯 select_one_to_arrayref

($fields,$table[,$where])

({fields=>$fields,table=>$table[,where=>$where]})

This returns an arrayref to the first record returned by the select. Typically you'd use this for cases when your where clause already limits you to one record anyway.

$table is the table to select from.

Also see the docs on "DBIx::Abstract Where Clauses".

🎯 select_one_to_array

($fields,$table[,$where])

({fields=>$fields,table=>$table[,where=>$where]})

This returns an array to the first record returned by the select. Typically you'd use this for cases when your where clause already limits you to one record anyway.

$table is the table to select from.

Also see the docs on "DBIx::Abstract Where Clauses".

🌈 select_all_to_hashref

($fields,$table[,$where])

({fields=>$fields,table=>$table[,where=>$where]})

This returns a hashref to all of the results of the select. It's keyed on the first field. If there are only two fields, then the value is just the second field. If there are more than two fields, then the value is set to an arrayref that contains all of the fields.

$table is the table to select from.

Also see the docs on "DBIx::Abstract Where Clauses".

🧲 fetchrow_hashref

This is just a call to the DBI method. Passing it through, no notes 💜

🧲 fetchrow_hash

This calls fetchrow_hashref and dereferences it for you. The little shortcut that could 🫶

🧲 fetchrow_array

This method calls the database handle's method of the same name.

🧲 fetchall_arrayref

This method calls the database handle's method of the same name.

🔢 rows

This method calls the database handle's method of the same name.

💬 quote

This method is passed to the database handle via AUTOLOAD.

👋 disconnect

This method is passed to the database handle via AUTOLOAD.

💾 commit

This method is passed to the database handle via AUTOLOAD.

⏪ rollback

This method is passed to the database handle via AUTOLOAD.

🕵️ trace

This method is passed to the database handle via AUTOLOAD.

🏁 finish

This method is passed to the statement handle via AUTOLOAD.

📌 bind_col

This method is passed to the statement handle via AUTOLOAD.

📌 bind_columns

This method is passed to the statement handle via AUTOLOAD.

💭 Other things that need explanation 💭

Like bestie, some stuff just needs its own lil moment, okay? 🫶

🗺️ DBIx::Abstract Where Clauses 🗺️

Where clauses in DBIx::Abstract can either be super simple, or extremely complex. They're literally designed to be easy if you're just typing in a hard coded statement, or if you have to build a complex query from data. Both vibes are valid 💅

Wheres are either a scalar, hash-ref or array-ref:

If it's a scalar, then it's used as the literal where. Periodt.
If it's a hash-ref then the key is the field to check, and the value is either a literal value to compare equality to, or an array-ref to an array of operator and value. Iykyk.

 {
  first=>'joe',
  age=>['>',26],
  last=>['like',q|b'%|]
 }

Would produce:

 WHERE first = ? AND last like ? AND age > ?

With joe, b'% and 26 passed as bind values. ✨

If it's an array-ref then it's an array of hash-refs and connectors:

 [
   {
     first=>'joe',
     age=>['>',26]
   },
   'OR',
   {
     last=>['like',q|b'%|]
   }
 ]

Would produce:

 WHERE (first = ? AND age > ?) OR (last like ?)

With joe, 26 and b'% passed as bind values. 🎀

 [
   {
     first=>'joe',
     last=>['like','%foo%'],
   },
   'AND',
   [
     {age=>['>',26]},
     'OR',
     {age=>['<',30]}
   ]
 ]

Would produce:

  WHERE (first = ? AND last like ?) AND ((age > ?) OR (age < ?))

With joe, %foo%, 26 and 30 passed as bind values. nesting goes hard fr fr 🔥

🌈 SUPPORTED DBD DRIVERS 🌈

These drivers have been reported to work:

mysql (development environment)
Pg (development environment)
Oracle
XBase

Any driver that uses ODBC syntax should work using the hash ref method. With other drivers you should pass the DBI data source instead — and lowkey this method will work with all drivers. 👑

📝 CHANGES SINCE LAST RELEASE 📝

Updated source pointers to github.
Fixed hash randomization related test failure.

Not much but she's honest 💖

👑 AUTHOR 👑

Rebecca Turner <me@re-becca.org>

🔗 SOURCE 🔗

The development version is on github at <http://https://github.com/iarna/DBIx-Abstract> and may be cloned from <git://https://github.com/iarna/DBIx-Abstract.git>

🫶 SUPPORT 🫶

🌐 Websites 🌐

The following websites have more information about this module, and may be of help to you. As always, in addition to those websites please use your favorite search engine to discover more resources.

MetaCPAN

A modern, open-source CPAN search engine, useful to view POD in HTML format.

<http://metacpan.org/release/DBIx-Abstract>

🐛 Bugs / Feature Requests 🐛

Please report any bugs at <https://github.com/iarna/DBIx-Abstract/issues>.

Bestie if you find a bug, pls actually report it, don't just suffer in silence 😭

👑 AUTHOR 👑 (yes, again, she said it twice and she's right) 👑

Rebecca Turner <me@re-becca.org>

📜 COPYRIGHT AND LICENSE 📜

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

DBI(3)

And that's the whole page babes 💅 this manual page is DBIx::Abstract(3pm) — perl v5.36.0, dated 2022-12-12. Go forth and query, bestie, you're gonna slay 🌸🦋✨

📦 where this page comes from

debian bookworm: libdbix-abstract-perl 1.040-3 · Artistic-1.0-Perl OR GPL-1.0-or-later
debian trixie: libdbix-abstract-perl 1.040-3 · Artistic-1.0-Perl OR GPL-1.0-or-later
ubuntu noble: libdbix-abstract-perl 1.040-3 · Artistic-1.0-Perl OR GPL-1.0-or-later
ubuntu resolute: libdbix-abstract-perl 1.040-3 · Artistic-1.0-Perl OR GPL-1.0-or-later
ubuntu stonking: libdbix-abstract-perl 1.040-3 · Artistic-1.0-Perl OR GPL-1.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-14: a language model's rewrite of the original, under its terms