Webfiori Database Abstraction Layer

Database abstraction layer of WebFiori framework.

Content

Supported PHP Versions
Supported Databases
Features
Installation
Usage

Supported PHP Versions

| Build Status | |:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------:| | <a target="_blank" href="https://github.com/WebFiori/database/actions/workflows/php81.yaml"><img src="https://github.com/WebFiori/database/actions/workflows/php81.yaml/badge.svg?branch=main"></a> | | <a target="_blank" href="https://github.com/WebFiori/database/actions/workflows/php82.yaml"><img src="https://github.com/WebFiori/database/actions/workflows/php82.yaml/badge.svg?branch=main"></a> | | <a target="_blank" href="https://github.com/WebFiori/database/actions/workflows/php83.yaml"><img src="https://github.com/WebFiori/database/actions/workflows/php83.yaml/badge.svg?branch=main"></a> | | <a target="_blank" href="https://github.com/WebFiori/database/actions/workflows/php84.yaml"><img src="https://github.com/WebFiori/database/actions/workflows/php84.yaml/badge.svg?branch=main"></a> | | <a target="_blank" href="https://github.com/WebFiori/database/actions/workflows/php85.yaml"><img src="https://github.com/WebFiori/database/actions/workflows/php85.yaml/badge.svg?branch=main"></a> |

Supported Databases

MySQL
MSSQL

Features

Building your database structure within PHP
Fast and easy to use query builder
Database abstraction which makes it easy to migrate your system to different DBMS
Repository pattern with AbstractRepository for clean data access
Active Record pattern support for rapid development
PHP 8 attributes for table definitions
Database migrations and seeders
Performance monitoring and query analysis
Entity generation for object-relational mapping
Transaction support with automatic rollback

Installation

To install the library using composer, add following dependency to composer.json: "webfiori/database":"*"

Usage

Connecting to Database

Connecting to a database is simple. First step is to define database connection information using the class ConnectionInfo. Later, the instance can be used to establish a connection to the database using the class Database.

use WebFiori\Database\ConnectionInfo;
use WebFiori\Database\Database;

$connection = new ConnectionInfo('mysql', 'root', '123456', 'testing_db');
$database = new Database($connection);

Running Basic SQL Queries

For every query, the table must be specified using Database::table(string $tblName). The method returns an AbstractQuery instance with methods for building queries:

insert(array $cols): Construct an insert query.
select(array $cols): Construct a select query.
update(array $cols): Construct an update query.
delete(): Construct a delete query.
where($col, $val): Adds a condition to the query.

After building the query, call execute() to run it.

// Insert
$database->table('posts')->insert([
    'title' => 'Super New Post',
    'author' => 'Me'
])->execute();

// Select
$resultSet = $database->table('posts')
    ->select()
    ->where('author', 'Ibrahim')
    ->execute();

foreach ($resultSet as $record) {
    echo $record['title'];
}

// Update
$database->table('posts')->update([
    'title' => 'Updated Title',
])->where('id', 1)->execute();

// Delete
$database->table('posts')->delete()->where('id', 1)->execute();

Building Database Structure

Define database structure in PHP code using blueprints:

use WebFiori\Database\ColOption;
use WebFiori\Database\DataType;

$database->createBlueprint('users')->addColumns([
    'id' => [
        ColOption::TYPE => DataType::INT,
        ColOption::PRIMARY => true,
        ColOption::AUTO_INCREMENT => true
    ],
    'name' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 100
    ],
    'email' => [
        ColOption::TYPE => DataType::VARCHAR,
        ColOption::SIZE => 150
    ]
]);

// Create the table
$database->table('users')->createTable()->execute();

Repository Pattern

The AbstractRepository class provides a clean way to handle data access with separation between entities and database logic.

Creating an Entity

class Product {
    public ?int $id = null;
    public string $name;
    public float $price;
}

Creating a Repository

use WebFiori\Database\Repository\AbstractRepository;

class ProductRepository extends AbstractRepository {
    protected function getTableName(): string {
        return 'products';
    }

    protected function getIdField(): string {
        return 'id';
    }

    protected function toEntity(array $row): object {
        $product = new Product();
        $product->id = (int) $row['id'];
        $product->name = $row['name'];
        $product->price = (float) $row['price'];
        return $product;
    }

    protected function toArray(object $entity): array {
        return [
            'id' => $entity->id,
            'name' => $entity->name,
            'price' => $entity->price
        ];
    }
}

Using the Repository

$repo = new ProductRepository($database);

// Create
$product = new Product();
$product->name = 'Widget';
$product->price = 29.99;
$repo->save($product);

// Read
$product = $repo->findById(1);
$allProducts = $repo->findAll();

// Update
$product->price = 24.99;
$repo->save($product);

// Delete
$repo->deleteById(1);

// Pagination
$page = $repo->paginate(page: 1, perPage: 20);

Active Record Pattern

For rapid development, you can merge entity and repository into a single model class:

use WebFiori\Database\Attributes\Column;
use WebFiori\Database\Attributes\Table;
use WebFiori\Database\DataType;
use WebFiori\Database\Repository\AbstractRepository;

#[Table(name: 'articles')]
class Article extends AbstractRepository {
    #[Column(type: DataType::INT, primary: true, autoIncrement: true)]
    public ?int $id = null;

    #[Column(type: DataType::VARCHAR, size: 200)]
    public string $title = '';

    #[Column(type: DataType::TEXT)]
    public string $content = '';

    protected function getTableName(): string { return 'articles'; }
    protected function getIdField(): string { return 'id'; }
    
    protected function toEntity(array $row): object {
        $article = new self($this->db);
        $article->id = (int) $row['id'];
        $article->title = $row['title'];
        $article->content = $row['content'];
        return $article;
    }

    protected function toArray(object $entity): array {
        return [
            'id' => $entity->id,
            'title' => $entity->title,
            'content' => $entity->content
        ];
    }
}

Usage:

// Create and save
$article = new Article($database);
$article->title = 'Hello World';
$article->content = 'My first article';
$article->save();

// Query
$all = $article->findAll();
$one = $article->findById(1);

// Update
$article->title = 'Updated Title';
$article->save();

// Delete
$article->deleteById();

// Reload from database
$fresh = $article->reload();

Entity Generation

Generate entity classes from table blueprints:

$blueprint = $database->getTable('users');

$generator = $blueprint->getEntityGenerator('User', __DIR__, 'App\\Entity');
$generator->generate();

Database Migrations

Version control your database schema changes:

use WebFiori\Database\Schema\AbstractMigration;

class CreateUsersTable extends AbstractMigration {
    public function up(Database $db): void {
        $db->createBlueprint('users')->addColumns([
            'id' => [ColOption::TYPE => DataType::INT, ColOption::PRIMARY => true, ColOption::AUTO_INCREMENT => true],
            'name' => [ColOption::TYPE => DataType::VARCHAR, ColOption::SIZE => 100]
        ]);
        $db->table('users')->createTable()->execute();
    }
    
    public function down(Database $db): void {
        $db->raw("DROP TABLE users")->execute();
    }
}

Run migrations:

use WebFiori\Database\Schema\SchemaRunner;

$runner = new SchemaRunner($connectionIn

Database

Install / Use

README