DBIx::Perlish - Updates, deletes, inserts

This is a post in a series about DBIx::Perlish Perl module. Previous posts in the series are linked at the bottom.

Not all queries are made to request the data; you will also want to be able to update, delete, and insert data.

So the query language that DBIx::Perlish implements also provides db_update, db_delete, and db_insert functions.

The db_update and db_delete functions are in fact very similar to db_fetch, with which you are already familiar.

Let’s start with db_update:

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u->first_name = "Ford";
   $u->last_name  = "Prefect";
};

You can see that a query sub that db_update takes has all the same elements as a query sub taken by db_fetch, with an addition of an assignment to update column values. Quite natural, eh?

One thing to remember is that you cannot use a return statement with db_update - it has no meaning!

It can become quite tedious to write all the individual assignments if the number of columns to be updated is not small. Luckily, there is a shortcut:

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u = {
      first_name => "Ford",
      last_name  => "Prefect",
      age        => $u->age + 1,
   };
};

That is, you can just use hash reference syntax.

You might have noticed that in the last example an expression involving age column was used to update it. That works as you would expect it will.

Using db_delete is equally simple.

db_delete {
   users->id < 20;
};

Using return did not make sense with db_update. This is also the case with db_delete.

Moreover, assignments which you were using with db_update make no sense neither with db_delete nor with db_fetch.

Another thing you would want to do, inserting the data into a table, is done differently. Unlike updates and deletes, inserts do not require a query language since they are so simple.

So if you want to insert a row into a table, you do it with a simple hashref:

db_insert 'users', {
   id         => 42,
   first_name => "Ford",
   last_name  => "Prefect",
};

Nothing fancy here.

To be continued.

First post, Getting rid of SQL from Perl code
Second post, A walk through an example
Third post, Many happy returns