DBIx::Custom

DBIx::Custom documentation

DBIx::Custom is Perl's O / R mapper. An O / R mapper is a module that makes it easy to access the database.

DBIx::Custom is one of the options you can consider if you find it difficult to work with raw DBI or prefer a simple O / R mapper to a more abstract O / R mapper.

Features of DBIx::Custom

It is a feature of DBIx::Custom.

Easy execution of insert, update, delete, select
Batch insert of multiple data, bulk insert is possible if RDBMS is supported
Provides a thin layer for SQL. OK when you want to use pure SQL.
Flexible where clauses, named placeholders, model support
Define a join clause and join the table with a foreign key. You can freely write syntax such as order by clause and limit clause
Supports transactions. With connection management, it works correctly even on a prefork server
Compatible with MySQL, MariaDB, PostgreSQL, SQLite, Oracle, SQL Server, Microsoft Access
Maintains backward compatibility, except for features scheduled to be discontinued in 2018.

Connection to database

Connect to the database.

use DBIx::Custom;

#Connect to SQLite
my $dbi = DBIx::Custom->connect('dbi: SQLite: dbname = bookshop');

#Connect to MySQL, MariaDB (supports utf8mb4)
#Connection management is done by default and works fine on prefolk servers
my $dbi = DBIx::Custom->connect(
  'dbi: mysql: database = bookshop',
  'ken',
  '! LFKD%$&',
  {mysql_enable_utf8mb4 => 1}
);

How to connect to the database

Creating a model

Create a model.

#Create a model
$dbi->model('book');

#Create a model When defining a join clause
$dbi->model(
  table =>'book',
  join => [
    'left join author on book.author_id = author.id',
    'left join publisher on book.publisher_id = publisher.id'
  ]
);

Model documentation

insert

Insert with #insert
$dbi->model('book')->insert({id => 1, title =>'Perl'});

Insert multiple data with #insert
$dbi->model('book')->insert(
  [
    {id => 1, title =>'Perl'}
    {id => 2, title =>'Mojolicious'}
  ]
);
Insert multiple data with #insert (bulk insert)
$dbi->model('book')->insert(
  [
    {id => 1, title =>'Perl'}
    {id => 2, title =>'Mojolicious'}
  ],,
  bulk_insert => 1
);

update

Updated with #uppdate
$dbi->model('book')->update({title =>'Perl'}, where => {id => 1});

Delete with #delete
$dbi->model('book')->delete(where => {id => 1});

select

Select with #select (all fields)
my $result = $dbi->model('book')->select(where => {id => 1});

Select with #select (specify field)
my $result = $dbi->model('book')->select(['id','title'], where => {id => 1});

Select with #select (ID is 1 or 2 or 3)
my $result = $dbi->model('book')->select(where => {id => [1, 2, 3]});

Select with #select (Peller is included in the title and the price is 2000 yen or less)
my $result = $dbi->model('book')->select(
  where => "title like'%Perl%' and price <= 2000"
);

Select with #select (Perl is included in the title and the price is 2000 yen or less) When using variables
# Use named placeholder
my $title ='Perl';
my $price = 2000;
my $result = $dbi->model('book')->select(
  where => [
    "title like: title and price <=: price",
    {title => $title, price => $price}
  ]
);

Select with #select (Peller is included in the title and the price is 2000 yen or less)
#where clause dynamically generated
my $title ='Perl';
my $price = 2000;
my $result = $dbi->model('book')->select(
  where => [
    ['and', "title like: title", "price <=: price"],
    {title => $title, price => $price}
  ]
);

Select with #select (Peller is included in the title and the price is 2000 yen or less or 5000 yen or more)
#Where clause is dynamically generated and multiple conditions are specified with or
my $title ='Perl';
my $price1 = 2000;
my $price2 = 5000;
my $result = $dbi->model('book')->select(
  where => [
    ['and', "title like: title", ['or', "price <=: price", "price> =: price"],
    {title => $title, price => [$price1, $price2]}
  ]
);
Select with #select (Peller is included in the title and the price is 2000 yen or less or 5000 yen or more)
#Where clause is dynamically generated and multiple conditions are specified with or
# One price was not specified
my $title ='Perl';
my $price2 = 5000;
my $result = $dbi->model('book')->select(
  where => [
    ['and', "title like: title", ['or', "price <=: price", "price> =: price"],
    {title => $title, price => [$dbi->not_exists, $price2]}
  ]
);

Select with #select (Perl is included in the title and the price is 2000 yen or less) When using variables
# Named Placeholder Syntack Sugar
my $title ='Perl';
my $price = 2000;
my $result = $dbi->model('book')->select(
  where => [
    ": title {like} and: price {<=}",
    {title => $title, price => $price}
  ]
);