How can this method that inserts a hashref into a database table be improved? It assumes that the columns of the database table are named identically to the fields in the hashref. The return value is the id field created by the insert.

sub insert(){
    my ($table, $data) = @_;
    my ($fields, $placeholders, $values) = ("", "", []);
    while (my ($field, $value) = each %$data){
        $fields.=", " if ($fields);
        $placeholders.=", " if ($placeholders);
        push(@$values, $value);
    $dbh->prepare("INSERT INTO $table ($fields) VALUES ($placeholders)")->execute(@$values);
    return $dbh->last_insert_id(undef, undef, undef, undef);

It assumes that $dbh is a correctly initialized global database handle.

It is used like:

&insert("animals", {
    "date-of-birth"=>"November 1, 2015",

Which will insert a record into the animals table:

id | type | name    | date-of-birth    | date-of-death
 1 | cat  | Patches | November 1, 2015 | null

3 Answers 3


This might be a good candidate to use SQL::Abstract in, as per DBIx::Class.

sub insert {
    # insert takes ( $table, \%fieldmap )
    my $sql = SQL::Abstract->new();
    my ( $query, @binds ) = $sql->insert( @_ );
    return $dbh->last_insert_id(undef, undef, undef, undef);

This gives you a few easy features that may come in handy in the future if you change your database, like:

  • 1
    \$\begingroup\$ Nice find of an already written library that pretty much does exactly what I was trying to do. The security features of guarding against malicious key names is pretty sweet as well. \$\endgroup\$ Feb 16, 2016 at 13:09
  • 1
    \$\begingroup\$ Its pretty handy if most of what you're doing involves single table queries. But if you're wanting Join queries, it may be better to reach for a full DBIx::Class config. \$\endgroup\$ Feb 17, 2016 at 1:28

Creating an insert() method is a good idea when working with DBI so you can reduce redundant code and standardize (or omit) your error handling.

use arrays

If you use arrays to store your fields and placeholders you can save yourself some trouble. Your declaration can become:


and the loop is simplified to:

while (my ($field, $value) = each %$data) {

and now we can join those back together:

my $sql_fields = join(",", @sql_fields);

and since we have the same number of placeholders as fields we can create that without an extra array:

my $sql_placeholders = join(",", map {"?"} @sql_fields);

which leads to a few tweaks on your prepare:

$dbh->prepare("INSERT INTO $table ($sql_fields) VALUES ($sql_placeholders)")->execute(@sql_values);

error checking

I hope you also take some time to check for potential errors. Maybe the disk is full. Maybe a field gets renamed. Providing useful diagnostics for those sorts of things would be a good idea. Some errors could be handled without hitting the database, like avoiding invalid characters in field names.

  • 1
    \$\begingroup\$ Mapping the fields to question marks is quite clever. \$\endgroup\$ Feb 15, 2016 at 20:40
  • \$\begingroup\$ As for error handling, DBI seems to throw appropriate errors (for example when this tries to insert an unknown column name). \$\endgroup\$ Feb 15, 2016 at 20:42
  • 1
    \$\begingroup\$ DBI's messages don't include the SQL statement or values that caused the error. \$\endgroup\$
    – chicks
    Feb 15, 2016 at 21:07
  • \$\begingroup\$ Just a minor correction, VALUES (@sql_placeholders) should be VALUES ($sql_placeholders). Otherwise this approach seems smart. \$\endgroup\$ Mar 30, 2020 at 12:09
  • \$\begingroup\$ @PhemeloKhetho Thanks, corrected. \$\endgroup\$
    – chicks
    Mar 30, 2020 at 12:40

You can replace your while loop with more straightforward equivalent,

$fields  = join(", ", keys %$data);
@$values = values %$data;
$placeholders = join(", ", ("?") x @$values);
  • 1
    \$\begingroup\$ Does that guarantee the ordering of the keys and values will be the same? \$\endgroup\$ Feb 16, 2016 at 9:50
  • 1
    \$\begingroup\$ @StephenOstermiller yes, you can rely on same order of keys and values. So long as a given hash is unmodified you may rely on keys, values and each to repeatedly return the same order as each other. \$\endgroup\$
    – mpapec
    Feb 16, 2016 at 12:25
  • 1
    \$\begingroup\$ Just make sure your code doesn't rely on the same ordering across runs, because the order is random per hash and per-perl-instance. \$\endgroup\$ Feb 17, 2016 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.