man > DBD::DBM

man perldoc info pydoc3 ri search

📖 NAME

DBD::DBM - a DBI driver for DBM & MLDBM files

🚀 Quick Reference

Use Case	Command	Description
Connect with defaults	DBI->connect('dbi:DBM:')	Connects using SDBM_File and DBI::SQL::Nano
Connect with options	DBI->connect('dbi:DBM:dbm_type=DB_File;dbm_mldbm=Storable')	Specify DBM type and serializer
Create table	$dbh->do("CREATE TABLE user (name TEXT, phone TEXT)")	Creates a DBM file with two columns
Insert data	$dbh->do("INSERT INTO user VALUES ('Fred','233-7777')")	Inserts a row
Select by key	$sth = $dbh->prepare("SELECT * FROM user WHERE name=?"); $sth->execute('Fred')	Optimized key lookup (first column)
Update by key	$dbh->do("UPDATE user SET phone='999-4444' WHERE name='Sanjay'")	Update row using key
Delete by key	$dbh->do("DELETE FROM user WHERE name='Junk'")	Delete row using key
Drop table	$dbh->do("DROP TABLE user")	Removes the DBM files
Check versions	print $dbh->dbm_versions	Shows loaded modules and versions

📋 SYNOPSIS

use DBI;
$dbh = DBI->connect('dbi:DBM:');                    # defaults to SDBM_File
$dbh = DBI->connect('DBI:DBM(RaiseError=1):');      # defaults to SDBM_File
$dbh = DBI->connect('dbi:DBM:dbm_type=DB_File');    # defaults to DB_File
$dbh = DBI->connect('dbi:DBM:dbm_mldbm=Storable');  # MLDBM with SDBM_File

# or
$dbh = DBI->connect('dbi:DBM:', undef, undef);
$dbh = DBI->connect('dbi:DBM:', undef, undef, {
    f_ext              => '.db/r',
    f_dir              => '/path/to/dbfiles/',
    f_lockfile         => '.lck',
    dbm_type           => 'BerkeleyDB',
    dbm_mldbm          => 'FreezeThaw',
    dbm_store_metadata => 1,
    dbm_berkeley_flags => {
        '-Cachesize' => 1000, # set a ::Hash flag
    },
});

and other variations on connect() as shown in the DBI docs, DBD::File metadata and "Metadata" shown below.

Use standard DBI prepare, execute, fetch, placeholders, etc., see "QUICK START" for an example.

📝 DESCRIPTION

DBD::DBM is a database management system that works right out of the box. If you have a standard installation of Perl and DBI you can begin creating, accessing, and modifying simple database tables without any further modules. You can add other modules (e.g., SQL::Statement, DB_File etc) for improved functionality.

The module uses a DBM file storage layer. DBM file storage is common on many platforms and files can be created with it in many programming languages using different APIs. That means, in addition to creating files with DBI/SQL, you can also use DBI/SQL to access and modify files created by other DBM modules and programs and vice versa. Note that in those cases it might be necessary to use a common subset of the provided features.

DBM files are stored in binary format optimized for quick retrieval when using a key field. That optimization can be used advantageously to make DBD::DBM SQL operations that use key fields very fast. There are several different "flavors" of DBM which use different storage formats supported by perl modules such as SDBM_File and MLDBM. This module supports all of the flavors that perl supports and, when used with MLDBM, supports tables with any number of columns and insertion of Perl objects into tables.

DBD::DBM has been tested with the following DBM types: SDBM_File, NDBM_File, ODBM_File, GDBM_File, DB_File, BerkeleyDB. Each type was tested both with and without MLDBM and with the Data::Dumper, Storable, FreezeThaw, YAML and JSON serializers using the DBI::SQL::Nano or the SQL::Statement engines.

⚡ QUICK START

use DBI;
my $dbh = DBI->connect('dbi:DBM:');
$dbh->{RaiseError} = 1;
for my $sql( split /;\n+/,"
    CREATE TABLE user ( user_name TEXT, phone TEXT );
    INSERT INTO user VALUES ('Fred Bloggs','233-7777');
    INSERT INTO user VALUES ('Sanjay Patel','777-3333');
    INSERT INTO user VALUES ('Junk','xxx-xxxx');
    DELETE FROM user WHERE user_name = 'Junk';
    UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel';
    SELECT * FROM user
"){
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    $sth->dump_results if $sth->{NUM_OF_FIELDS};
}
$dbh->disconnect;

🛠️ USAGE

📁 Specifying Files and Directories

DBD::DBM will automatically supply an appropriate file extension for the type of DBM you are using. For example, if you use SDBM_File, a table called "fruit" will be stored in two files called "fruit.pag" and "fruit.dir". You should never specify the file extensions in your SQL statements.

DBD::DBM recognizes following default extensions for following types:

• '.pag/r' — for SDBM_File, ODBM_File, and NDBM_File (when wrapping -ldbm). Also recognizes '.dir' for deletion on DROP.
• '.db/r' — for NDBM_File when wrapping BerkeleyDB 1.x (typically BSD's, Darwin).
• No extension — for GDBM_File, DB_File, and BerkeleyDB.

If your DBM type uses an extension other than one of the recognized types, set the f_ext attribute (and file a bug report).

$dbh = DBI->connect('dbi:DBM:f_ext=.db');  # .db extension used
$dbh = DBI->connect('dbi:DBM:f_ext=');     # no extension
$dbh->{f_ext}='.db';                       # global setting
$dbh->{f_meta}->{'qux'}->{f_ext}='.db';    # per-table setting

By default files are assumed to be in the current working directory. Use f_dir attribute to specify a different directory.

my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
$dbh->{f_dir} = '/foo/bar';
my $dbh = DBI->connect('dbi:DBM:', undef, undef, { f_dir => '/foo/bar' });

You can also use delimited identifiers in SQL:

my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM "/foo/bar/fruit" });

Per-table path override:

$dbh->{dbm_tables}->{f}->{file} = q(/foo/bar/fruit);

🔒 Table locking and flock()

Table locking is accomplished using a lockfile with the same basename and extension '.lck' (or custom extension via f_lock). The lock file is created with the table during CREATE and removed during DROP. Every time the table is opened, the lockfile is flock()ed — shared lock for SELECT, exclusive lock for all other operations (unless overridden by f_lock).

Locking depends on flock() and only works on operating systems that support it. On systems without flock(), DBD::DBM will behave as if locking occurred but no actual locking will happen. Locking is advisory — only cooperating programs using the same '.lck' file will respect the lock.

🗄️ Specifying the DBM type

Each "flavor" of DBM stores files in a different format with different capabilities. See AnyDBM_File for a comparison. By default, DBD::DBM uses SDBM_File (bundled with Perl). It is strongly recommended to use at least DB_File because SDBM_File has quirks and limitations.

Specify the DBM type using the dbm_type attribute:

my $dbh = DBI->connect('dbi:DBM:dbm_type=GDBM_File');
$dbh->{dbm_type} = 'DB_File';
print $dbh->{dbm_type};

Per-table settings using $dbh->{f_meta}:

$dbh->{f_meta}->{foo}->{dbm_type} = 'BerkeleyDB';
print $dbh->{f_meta}->{foo}->{dbm_type};

You must change dbm_type before accessing the table for the first time.

🧩 Adding multi-column support with MLDBM

Most DBM types only support two columns. MLDBM overcomes this by serializing data into the second column, allowing multiple logical columns and even Perl objects. You must install MLDBM to use more than two columns.

MLDBM is distributed with serializers: Data::Dumper (default), Storable (fastest), FreezeThaw. Additional serializers include YAML and JSON. Select the serializer using the dbm_mldbm attribute:

$dbh = DBI->connect('dbi:DBM:dbm_mldbm=Storable');
$dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_mldbm => 'YAML' });
$dbh->{dbm_mldbm} = 'YAML';
$dbh->{f_meta}->{foo}->{dbm_mldbm} = 'Data::Dumper';

Combine with DBM type:

$dbh = DBI->connect('dbi:DBM:', undef, undef, {
    dbm_type  => 'DB_File',
    dbm_mldbm => 'Storable',
});

SDBM_File is quite limited; consider using a different type with MLDBM.

🗃️ Support for Berkeley DB

Berkeley DB is supported through DB_File (old versions) and BerkeleyDB (all versions). Specify dbm_type as 'DB_File' or 'BerkeleyDB'. The 'BerkeleyDB' type is experimental and currently defaults to BerkeleyDB::Hash. You can pass initialization flags via dbm_berkeley_flags:

use BerkeleyDB;
my $env = new BerkeleyDB::Env -Home => $dir;
$dbh = DBI->connect('dbi:DBM:', undef, undef, {
    dbm_type  => 'BerkeleyDB',
    dbm_mldbm => 'Storable',
    dbm_berkeley_flags => {
        'DB_CREATE'  => DB_CREATE,
        'DB_RDONLY'  => DB_RDONLY,
        '-Cachesize' => 1000,
        '-Env'       => $env,
    },
});

Do not set -Flags or -Filename — they are determined by SQL. Transactions, concurrency, and locking are not yet supported.

⚡ Optimizing the use of key fields

Most DBM flavors have two physical columns; the first column serves as the key (like a Perl hash). Key fields must be unique and function as the PRIMARY KEY. Using a WHERE clause with a single equal comparison on the key field enables fast keyed lookups (supported by DBI::SQL::Nano only).

Example of optimized query:

SELECT phone FROM user WHERE user_name='Fred Bloggs';

Non-optimized queries:

SELECT phone FROM user WHERE user_name < 'Fred';   # not equal comparison
SELECT user_name FROM user WHERE phone = '233-7777';  # key not in WHERE

Underlying DBM storage still loops over key/value pairs even with optimized fetch — but it's faster than full table scan. SQL::Statement has improved WHERE evaluation (~15% overhead) but may beat Nano for complex WHERE clauses involving non-key fields.

📜 Supported SQL syntax

DBD::DBM uses a subset of SQL. The robustness depends on installed modules:

• Option #1: DBI::SQL::Nano (default) — small, fast for simple operations (especially primary key lookups). See DBI::SQL::Nano for supported syntax.
• Option #2: SQL::Statement (if installed) — adds table aliases, functions, joins, and more. Strongly recommended for anything beyond simple tables. See SQL::Statement.

To check which engine is active:

print $dbh->{sql_handler}, "\n";  # prints "SQL::Statement" or "DBI::SQL::Nano"

Performance comparison:

• DBI::SQL::Nano faster — single key updates/deletes/selects with literal values.
• SQL::Statement faster — queries with placeholders, IN clauses, BETWEEN, JOINs, multiple-value inserts.

🏷️ Specifying Column Names

DBM files lack a standard way to store column names. DBD::DBM stores them as a metadata row with key _metadata \0. If you are only working with DBD::DBM, you can ignore this section.

Example of stored metadata row:

_metadata \0 | <dbd_metadata><schema></schema><col_names>foo,bar</col_names></dbd_metadata>

To disable metadata storage, set dbm_store_metadata to 0:

my $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_store_metadata => 0 });
$dbh->{dbm_store_metadata} = 0;
$dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;

If metadata is disabled, DBD::DBM assumes columns named "k" and "v". Specify custom column names:

$dbh->{dbm_store_metadata} = 0;
$dbh->{dbm_cols} = 'foo,bar';
$dbh->{f_meta}->{qux}->{col_names} = [qw(foo bar)];

To convert an existing file without metadata, set the column names once without dbm_store_metadata set to 0 — the names will be stored and used thereafter.

🔧 DBI database handle attributes

📊 Metadata

Statement handle ($sth) attributes: Most attributes (NAME, NUM_OF_FIELDS, etc.) available after execute. $sth->rows available after execute without fetch.

Driver handle ($dbh) attributes: Private attributes must be accessed with the dbm_ prefix.

• dbm_cols — comma-separated list or array reference of column names.
• dbm_type — DBM storage type (ODBM_File, NDBM_File, SDBM_File, GDBM_File, DB_File, BerkeleyDB). First three not recommended.
• dbm_mldbm — serializer for MLDBM (Data::Dumper, Storable, FreezeThaw, YAML, JSON). Requires MLDBM.
• dbm_store_metadata — boolean; whether to store metadata in DBM file.
• dbm_berkeley_flags — hash reference with additional flags for BerkeleyDB::Hash.
• dbm_version — readonly; version of DBD::DBM.
• f_meta — per-table metadata hash supporting col_names, dbm_type, dbm_mldbm, dbm_store_metadata, dbm_berkeley_flags.
• dbm_tables — tied hash providing restricted access to table meta data.

Deprecated attributes (silently mapped to DBD::File):

• dbm_ext → f_ext
• dbm_lockfile → f_lockfile

🔬 DBI database handle methods

📋 The $dbh->dbm_versions() method

Returns a summary of used modules and versions:

print $dbh->dbm_versions;               # global settings
print $dbh->dbm_versions($table_name);  # per-table settings

When called without arguments, it displays global settings. Per-table overrides are only shown when a table name is specified.

💾 Storing Objects

If using MLDBM, you can store any Perl object that MLDBM can handle. Declare the column as type BLOB (though type is currently ignored).

🧩 EXTENSIBILITY

• SQL::Statement — Improved SQL engine with joins, aliases, functions, etc.
• DB_File — Berkeley DB version 1, widely available.
• GDBM_File — GNU dbm type, comparable to DB_File under GNU license.
• BerkeleyDB — Berkeley DB up to v4, with tools for repair and migration. Recommended for production.
• MLDBM — Serializer wrapper for multi-column support. Comes with Data::Dumper, FreezeThaw, Storable.
• YAML::MLDBM — Portable YAML serializer for MLDBM.
• MLDBM::Serializer::JSON — Portable JSON serializer for MLDBM.

⚠️ GOTCHAS AND WARNINGS

• DROP command: Removes any file matching the table name with extensions .pag, .dir, .db, or custom f_ext. Be careful with absolute paths.
• DBM limitations: SDBM_File can only store values < 1,000 characters. Check your DBM type's limits.
• Record order: Different DBM implementations return records in different orders. Use ORDER BY for consistent ordering.
• Platform-specific: DBM files are not portable across platforms. Use CSV for migration.
• MLDBM security: Deserialization may eval data (e.g., Data::Dumper). Be cautious with untrusted files.
• Locking: Advisory only; other programs can ignore the lock file.

🐛 BUGS AND LIMITATIONS

This module uses hash interfaces of two-column file databases. Without index support, INSERT and UPDATE behave similarly for ODBM_File and NDBM_File (they lack EXISTS support). This is considered a bug and may change.

$sth->do( "insert into foo values (1, 'hello')" );
$sth->do( "update foo set v='world' where k=1" );  # same as insert for ODBM_File/NDBM_File
$sth->do( "insert into foo values (1, 'world')" );

Known affected types: ODBM_File, NDBM_File. Use DB_File or similar.

📬 GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS

For help or suggestions, write to the DBI users mailing list at dbi-users AT perl.org or comp.lang.perl.modules. For development, join the DBI developers list at dbi-dev AT perl.org and IRC channel irc://irc.perl.org/dbi.

Report bugs as described in DBI. Include the output of $dbh->dbm_versions($table) and a minimal reproducing script. Patches welcome.

Commercial support: http://dbi.perl.org/support/ or contact Jens Rehsack at rehsack AT cpan.org (Germany).

🙏 ACKNOWLEDGEMENTS

Many thanks to Tim Bunce for encouragement and guidance (Jeff Zucker). Thanks to H.Merijn Brand for refactoring and support of SQL::Statement, Martin J. Evans for corrections, and the Perl community (Jens Rehsack).

📄 AUTHOR AND COPYRIGHT

Written by Jeff Zucker (jzucker AT cpan.org), maintained until 2007. Since 2010 maintained by Jens Rehsack & H.Merijn Brand.

Copyright (c) 2004 by Jeff Zucker, all rights reserved.
Copyright (c) 2010-2013 by Jens Rehsack & H.Merijn Brand, all rights reserved.

You may freely distribute and/or modify this module under the terms of either the GNU General Public License (GPL) or the Artistic License, as specified in the Perl README file.

🔗 SEE ALSO

DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, DB_File, BerkeleyDB, MLDBM, YAML::MLDBM, MLDBM::Serializer::JSON

📖 NAME 🚀 Quick Reference 📋 SYNOPSIS 📝 DESCRIPTION ⚡ QUICK START 🛠️ USAGE 🧩 EXTENSIBILITY ⚠️ GOTCHAS AND WARNINGS 🐛 BUGS AND LIMITATIONS 📬 GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS 🙏 ACKNOWLEDGEMENTS 📄 AUTHOR AND COPYRIGHT 🔗 SEE ALSO