codemonster-ru/database

Lightweight, framework-agnostic database layer with PDO connections, fluent Query Builder, Schema Builder, and full migration system.

v1.0.0 2025-12-07 18:33 UTC

Requires

• php: >=8.2
• ext-pdo: *

Requires (Dev)

• phpunit/phpunit: ^9.6 || ^10.5 || ^11.0 || ^12.0

MIT

• Kirill Kolesnikov

• database
• migrations
• sql
• mysql
• schema
• query
• pdo
• query-builder
• db-layer
• framework-agnostic
• database-manager
• php-database
• codemonster
• migration-runner
• orm-like

README

A lightweight, framework-agnostic database layer for PHP.
Part of the Codemonster ecosystem — but works fully standalone.

📦 Installation

composer require codemonster-ru/database

🚀 Usage

1. Database Manager

use Codemonster\Database\DatabaseManager;

$manager = new DatabaseManager([
    'default' => 'mysql', // name of the default connection
    'connections' => [
        'mysql' => [
            'driver'   => 'mysql',
            'host'     => '127.0.0.1',
            'port'     => 3306,
            'database' => 'test',
            'username' => 'root',
            'password' => '',
            'charset'  => 'utf8mb4',
        ],
    ],
]);

$db = $manager->connection(); // default connection

You can define multiple connections and select them by name:

$manager = new DatabaseManager([
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver'   => 'mysql',
            'host'     => '127.0.0.1',
            'port'     => 3306,
            'database' => 'app',
            'username' => 'root',
            'password' => '',
        ],
        'sqlite' => [
            'driver'   => 'sqlite',
            'database' => __DIR__ . '/database.sqlite',
        ],
    ],
]);

$mysql  = $manager->connection();          // default (mysql)
$sqlite = $manager->connection('sqlite');  // explicit connection

• For MySQL/MariaDB use driver => 'mysql'.
• For SQLite use driver => 'sqlite' and only database is required (file path or :memory:).
• Other PDO drivers can be wired via driver + DSN-compatible options; the query layer is driver-agnostic, while the schema builder is primarily tuned for MySQL-like syntax and SQLite.

2. Query Builder

SELECT

$users = $db->table('users')
    ->select('id', 'name', 'email')
    ->where('active', 1)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

INSERT

$db->table('users')->insert([
    'name'  => 'Vasya',
    'email' => '[e-mail]',
]);

$id = $db->table('ideas')->insertGetId([
    'title' => 'New idea',
]);

UPDATE

$db->table('users')
    ->where('id', 5)
    ->update([
        'active'     => 0,
        'updated_at' => date('Y-m-d H:i:s'),
    ]);

DELETE

$db->table('sessions')
    ->where('user_id', 10)
    ->delete();

Debug SQL

[$sql, $bindings] = $db->table('users')
    ->where('active', 1)
    ->toSql();

// $sql      = 'SELECT * FROM `users` WHERE `active` = ?'
// $bindings = [1]

Raw expressions

$db->table('users')
    ->selectRaw('COUNT(*) as total')
    ->whereRaw('JSON_VALID(metadata)')
    ->orderByRaw('FIELD(status, "new", "approved", "archived")')
    ->get();

Join support

$db->table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->leftJoin('payments', function ($join) {
        $join->on('payments.order_id', '=', 'orders.id')
             ->where('payments.status', 'paid');
    })
    ->get();

Group By / Having

$db->table('orders')
    ->selectRaw('status, COUNT(*) as total')
    ->groupBy('status')
    ->having('total', '>', 10)
    ->get();

Aggregates

$count = $db->table('users')->count();
$sum   = $db->table('orders')->sum('amount');
$avg   = $db->table('ratings')->avg('score');
$min   = $db->table('logs')->min('id');
$max   = $db->table('visits')->max('duration');

Exists

$exists = $db->table('users')
    ->where('email', '[e-mail]')
    ->exists();

Value / Pluck

$email = $db->table('users')
    ->where('id', 1)
    ->value('email');

$names = $db->table('users')->pluck('name');
$pairs = $db->table('users')->pluck('email', 'id'); // [id => email]

Pagination

$currentPage = 1;

$page = $db->table('posts')->simplePaginate(20, $currentPage);

// $page = [
//     'data'        => [...],
//     'per_page'    => 20,
//     'current_page'=> 1,
//     'next_page'   => 2,
//     'prev_page'   => null,
// ];

3. Transactions

$db->transaction(function ($db) {
    $db->table('users')->insert([
        'name'  => 'New user',
        'email' => '[e-mail]',
    ]);

    $db->table('logs')->insert([
        'message' => 'User created',
    ]);
});

4. Global Helpers (with codemonster-ru/support)

If you also install codemonster-ru/support and register bindings in your container, you can use global helpers:

db();                 // returns default ConnectionInterface
db('sqlite');         // specific connection
schema();             // schema builder for default connection
transaction(fn() =>   // convenience wrapper
    db()->table('logs')->insert(['message' => 'ok'])
);

Helpers are thin wrappers around DatabaseManager and the connection’s schema() / transaction() methods.

📐 Schema Builder

The package includes a lightweight schema builder.

Note: The schema grammar is focused on MySQL/MariaDB and SQLite.
For other PDO drivers, the query builder will work, but schema operations may not be fully supported.

Creating a table

use Codemonster\Database\Schema\Blueprint;

$db->schema()->create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->boolean('active')->default(1);
    $table->timestamps();
});

Modifying a table

$db->schema()->table('users', function (Blueprint $table) {
    $table->string('avatar')->nullable();
    $table->integer('age')->default(0);
});

Dropping a table

$db->schema()->drop('users');

// or:
$db->schema()->dropIfExists('users');

🗄 Supported Column Types

• Integers: id, integer, bigInteger, mediumInteger, smallInteger, tinyInteger
• Floats: decimal, double, float
• Text: string, char, text, mediumText, longText
• Boolean: boolean
• JSON: json
• Dates & time: date, datetime, timestamp, time, year
• UUID: uuid
• Indexes: index, unique, primary
• Foreign keys with foreign() / references() / on() and onDelete() / onUpdate() helpers

🚦 Migrations

The package includes a migration system (designed to be used via the CLI).

• migrate
• migrate:rollback
• migrate:status
• make:migration

Example migration

use Codemonster\Database\Migrations\Migration;
use Codemonster\Database\Schema\Blueprint;

return new class extends Migration {
    public function up(): void
    {
        schema()->create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
        });
    }

    public function down(): void
    {
        schema()->drop('posts');
    }
};

🧰 CLI Tool

A standalone CLI ships with the package:

vendor/bin/database

Running migrations

vendor/bin/database migrate

Rollback

vendor/bin/database migrate:rollback

Status

vendor/bin/database migrate:status

Create a migration

vendor/bin/database make:migration CreatePostsTable

Default migrations directory:

./database/migrations

You can override paths via the migration kernel/path resolver:

$kernel->getPathResolver()->addPath('/path/to/migrations');

👨‍💻 Author

Kirill Kolesnikov

📜 License

MIT