DBIx::Lite - Chained and minimal ORM
version 0.33
use DBIx::Lite;
my $dbix = DBIx::Lite->new;
my $dbix = DBIx::Lite->new(dbh => $dbh);
my $dbix = DBIx::Lite->connect("dbi:Pg:dbname=$db", $user, $passwd, {pg_enable_utf8 => 1});
# build queries using chained methods -- no schema definition required
my $authors_rs = $dbix->table('authors');
my $authors_rs = $dbix->table('authors')->search({ country => 'IT' });
my $books_rs = $dbix
->table('books')
->select('id', 'title', 'year')
->left_join('authors', { author_id => 'id' })
->select_also(['authors.name' => 'author_name'])
->order_by('year');
# retrieve rows and columns -- still no schema definition required
my @authors = $authors_rs->all;
my $author = $authors_rs->search({ id => 1 })->single;
while (my $book = $books_rs->next) {
printf "%s (%s)\n", $book->title, $book->author_name; # automatic accessor methods
}
my @author_names = $authors_rs->get_column('name');
my $book_count = $books_rs->count;
# manipulate rows
my $book = $dbix->table('books')->insert({ name => 'Camel Tales', year => 2012 });
$books_rs->search({ year => { '<' => 1920 } })->update({ very_old => 1 });
$authors_rs->search({ age => { '>' => 99 } })->delete;
# define a primary key and get more features
$dbix->schema->table('authors')->autopk('id');
my $author = $dbix_lite->table('authors')->find(2);
$author->update({ age => 40 });
$author->delete;
# define relationships
$dbix->schema->one_to_many('authors.id' => 'books.author_id', 'author');
my $author = $books->author;
my $books_rs = $author->books->search({ year => 2012 });
my $book = $author->insert_related('books', { title => "A Camel's Life" });
# define custom object classes
$dbix->schema
->table('subjects')
->class('My::Subject')
->resultset_class('My::Subject::ResultSet');Many ORMs and DBI abstraction layers are available on CPAN, one of the most notables being DBIx::Class which provides the most powerful features to handle database contents using OOP.
DBIx::Lite was written with some goals in mind, that no other available module provides. Such goals/key features are:
- no need to define your database schema (most features work without one and some advanced features only require some bits, and still not the full table definitions)
- no need to connect to database: the module can just generate SQL for you
- chained methods with lazy SQL generation
- joins/relationships
- optional custom classes for results and resultsets with custom methods
- SQL::Abstract syntax
- paging features (with Data::Page)
Instantiating a DBIx::Lite object isn't more difficult than just writing:
my $dbix = DBIx::Lite->new;This will give you an unconnected object, that you can use to generate SQL commands using the select_sql(), insert_sql(), update_sql() and delete_sql() methods.
If you want to connect to a database you can pass a pre-connected database handle with the dbh argument or you can supply your connection options to the connect() method. All arguments passed to connect() will be just passed to DBIx::Connector which will be used to manage your connection under the hood.
my $dbix = DBIx::Lite->new(dbh => $dbh);
my $dbix = DBIx::Lite->connect("dbi:Pg:dbname=$db", $user, $passwd, {pg_enable_utf8 => 1});Note that connect() can be called as an object method too, if you want to connect an unconnected DBIx::Lite object at a later stage:
my $dbix = DBIx::Lite->new;
$dbix->connect("dbi:Pg:dbname=$db", $user, $passwd);This class method may accept the following optional arguments:
- dbh
-
This argument allows you to supply a pre-made DBI database handle. See the example in the previous paragraph.
- connector
-
This argument allows you to supply a pre-made DBIx::Connector object.
- schema
-
This argument allows you to supply a pre-made DBIx::Lite::Schema object. If none is provided, a new empty one will be created for each DBIx::Lite object. This argument is useful if you want to prepare your schema in advance and reutilize it across multiple connections.
- abstract
-
This argument allows you to supply options for SQL::Abstract::More module. Here is example for MySQL DB backend to quote fields names with backtick to allow using reserved words as column's names.
my $dbix = DBIx::Lite->new( abstract => { quote_char => '`', name_sep => '.' } ); $dbix->connect("DBI:mysql:$db_dbname;host=$db_host", $db_username, $db_password);
This methods accepts a list of arguments that are passed to DBIx::Connector. It returns the DBIx::Lite object. It can be called either as class or object method.
This method accepts a table name and returns a DBIx::Lite::ResultSet object on which you can chain its methods to build your query.
my $rs = $dbix->table('books');This method returns our DBIx::Lite::Schema object which may hold the definitions required for some advanced feature of DBIx::Lite. You can call then call its methods:
$dbix->schema->table('authors')->autopk('id');See the DBIx::Lite::Schema documentation for an explanation of its methods.
This method returns a DBI database handle that you can use to perform manual queries.
This method accepts a coderef which will be run inside a transaction.
$dbix->txn(sub {
$dbix->table('books')->update({ year => 2015 });
});Alessandro Ranellucci <[email protected]>
This software is copyright (c) 2021 by Alessandro Ranellucci.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent