PHPackages model/db - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. model/db ActiveLibrary[Database & ORM](/categories/database) model/db ======== MySQL abstraction layer for ModEl Framework v0.9.13(7mo ago)04.2k↓50%2PHP Since May 22Pushed 7mo ago1 watchersCompare [ Source](https://github.com/model-composer/db)[ Packagist](https://packagist.org/packages/model/db)[ RSS](/packages/model-db/feed)WikiDiscussions master Synced 1mo ago READMEChangelogDependencies (7)Versions (100)Used By (2) Model/Db ======== [](#modeldb) A MySQL abstraction layer for the ModEl Framework, providing elegant database operations with built-in caching, transactions, migrations, and event dispatching. Features -------- [](#features) - **Simple CRUD Operations**: Intuitive methods for insert, update, delete, and select operations (uses `model/query-builder` under the hood) - **Smart Caching**: Automatic caching for frequently accessed data with intelligent invalidation - **Transaction Management**: Nested transaction support with automatic commit/rollback - **Database Migrations**: Integrated Phinx-based migration system - **Event System**: Comprehensive event dispatching for all database operations - **Provider System**: Extensible provider architecture for customizing database behavior - **Query Limits**: Built-in protection against runaway queries - **Type Safety**: Automatic type casting for database values Installation ------------ [](#installation) ``` composer require model/db ``` Configuration ------------- [](#configuration) The package uses the `model/config` system. Configuration is stored under the `db` key: ``` [ 'databases' => [ 'primary' => [ 'host' => 'localhost', 'port' => 3306, 'username' => 'root', 'password' => '', 'name' => 'database', 'charset' => 'utf8', 'migrations' => ['migrations/'], // Migration paths 'cache_tables' => [], // Tables to always cache 'limits' => [ 'query' => 100, // Max same query executions 'table' => 10000, // Max queries per table 'total' => null, // Max total queries ], ], ], ] ``` Basic Usage ----------- [](#basic-usage) ### Getting a Connection [](#getting-a-connection) ``` use Model\Db\Db; // Get the default connection (first defined or 'primary') $db = Db::getConnection(); // Get a specific connection $db = Db::getConnection('alternative_db'); ``` ### Query builder integration [](#query-builder-integration) This package leans on the `model/query-builder` package for the query building. Here follows a quick overview, but refer to said package for more details on "where" clauses building and options. ### Insert Operations [](#insert-operations) ``` // Insert a single row $id = $db->insert('users', [ 'name' => 'John Doe', 'email' => 'john@example.com', 'age' => 30 ]); ``` ### Select Operations [](#select-operations) ``` // Select by ID $user = $db->select('users', 1); // Select with conditions $user = $db->select('users', ['email' => 'john@example.com']); // Select all rows $users = $db->selectAll('users'); // Streaming results (it returns a generator and not an array) $users = $db->selectAll('users', [], ['stream' => true]); foreach ($users as $user) { // Process each user } ``` Streaming is the default behavior, but you can disable it by setting the `stream` option to `false`. ### Update Operations [](#update-operations) ``` // Update by ID $db->update('users', 1, ['name' => 'Jane Doe']); // Update entire table (requires confirmation) $db->update('users', [], ['active' => true], ['confirm' => true]); // Update or insert $id = $db->updateOrInsert('users', ['email' => 'john@example.com'], [ 'name' => 'John Updated', 'age' => 31 ]); ``` ### Delete Operations [](#delete-operations) ``` // Delete by ID $db->delete('users', 1); // Delete with conditions $db->delete('users', ['status' => 'inactive']); // Delete entire table (requires confirmation) $db->delete('users', [], ['confirm' => true]); ``` ### Count Operations [](#count-operations) ``` // Count all rows $total = $db->count('users'); // Count with conditions $active = $db->count('users', ['status' => 'active']); // Count with grouping $count = $db->count('users', [], ['group_by' => 'status']); ``` Advanced Features ----------------- [](#advanced-features) ### Transactions [](#transactions) ``` $db->beginTransaction(); try { $id = $db->insert('users', ['name' => 'John']); $db->insert('profiles', ['user_id' => $id]); $db->commit(); } catch (\Exception $e) { $db->rollBack(); throw $e; } ``` Transactions support nesting - only the outermost transaction actually commits to the database. ### Bulk/Deferred Inserts [](#bulkdeferred-inserts) For high-performance bulk operations, use deferred inserts: ``` // Defer inserts and flush every 100 rows for ($i = 0; $i < 1000; $i++) { $db->insert('users', [ 'name' => 'User ' . $i, 'email' => 'user' . $i . '@example.com', ], ['defer' => 100]); } // Manually flush remaining inserts $db->bulkInsert('users'); ``` ### Caching [](#caching) The package provides intelligent caching: - Simple queries on small tables (< 200 rows) are automatically table-cached (meaning that the whole table is cached in memory) - Tables listed in `cache_tables` config are always cached - Cache is automatically invalidated on INSERT, UPDATE, or DELETE - In-memory cache for repeated queries in the same request (with the same parameters) ``` // Disable cache for a query $users = $db->selectAll('users', [], ['cache' => false]); // Manually invalidate cache for a table $db->changedTable('users'); ``` ### Union Queries [](#union-queries) ``` $results = $db->unionSelect([ [ 'table' => 'active_users', 'where' => ['status' => 'active'], ], [ 'table' => 'pending_users', 'where' => ['status' => 'pending'], ], ]); ``` ### Raw Queries [](#raw-queries) ``` $statement = $db->query('SELECT * FROM users WHERE age > 18'); foreach ($statement as $row) { // Process row } ``` ### Database Introspection [](#database-introspection) ``` // Get table structure $table = $db->getTable('users'); echo $table->name; print_r($table->columns); print_r($table->primary); print_r($table->indexes); // Get the parser $parser = $db->getParser(); // Get the query builder $builder = $db->getBuilder(); // Get the PDO instance $pdo = $db->getDb(); ``` Events ------ [](#events) The package dispatches events for all database operations: - `Model\Db\Events\Query` - Any query execution - `Model\Db\Events\InsertQuery` - Before insert - `Model\Db\Events\InsertedQuery` - After insert - `Model\Db\Events\UpdateQuery` - Before update - `Model\Db\Events\DeleteQuery` - Before delete - `Model\Db\Events\SelectQuery` - Before select - `Model\Db\Events\ChangedTable` - Table data changed See `model/events` package for more details. Migrations ---------- [](#migrations) The package uses [Phinx](https://phinx.org/) for database migrations. ### Running Migrations [](#running-migrations) Migrations are automatically run when refreshing the ModEl Framework cache. To manually run migrations: ``` use Model\Db\Db; // Run all migrations for all databases Db::migrate(); // Run migrations for a specific database Db::migrate('primary'); ``` ### Creating Migrations [](#creating-migrations) Create migration files in the configured migrations folder: ```