Vecode Caldera - Database

Overview

Description

Caldera Database is an abstraction layer for database operations.

As with the other Caldera components it has been built to be swappable and modular.

Installation

The easisest way to install it is to use Composer:

composer require vecode/caldera-database

Requires

php: >=8.1
ext-mbstring: *
ext-pdo: *

Basic usage

Getting started

First you will need to connect to the database, but for doing so you need an adapter.

The class is an abstract wrapper for database operations, so the adapters are required to bridge the logic to the actual database engine.

Out of the box two adapters are included, MySQLAdapter and SQLiteAdapter, both of them implement the AdapterInterface through the PDOAdapter abstract class, so that you may implement your own should you need to.

So, for example you may do the following for MySQL:

use Caldera\Database\Database;
use Caldera\Database\Adapter\MySQLAdapter;

$options = [
    'host'     => getenv('DB_HOST'),
    'name'     => getenv('DB_NAME'),
    'user'     => getenv('DB_USER'),
    'password' => getenv('DB_PASSWORD'),
];

$adapter = new MySQLAdapter($options);
$database = new Database($adapter);

Or for SQLite:

use Caldera\Database\Database;
use Caldera\Database\Adapter\SQLiteAdapter;

$options = [
    'file' => dirname(__FILE__) . '/database.sqlite'
];

$adapter = new SQLiteAdapter($options);
$database = new Database($adapter);

You can check the connection status with isConnected:

if ( $database->isConnected() ) {
    // Good to go
}

Simple queries

To execute a simple query just use the query method:

$database->query("DROP TABLE IF EXISTS test");

In case you need to pass parameters just add another argument:

$database->query("DELETE FROM user WHERE id = ?", [$user_id]);

Also you may pass a callback that receives the PDOStatement so that you can change the fetch mode or use a custom model for example:

use App\Model\User;

$rows = $database->query("SELECT * FROM user WHERE id = ?", [$user_id], function($stmt) {
    $stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
    return $stmt->fetchAll();
});

For INSERT queries you can use the lastInsertId method to get the ID:

$database->query("INSERT INTO test (id, name, created, updated) VALUES (0, 'Foo', NOW(), NOW())");
$id = $database->lastInsertId();

Selection queries

To retrieve data from the database use the select method; usually you will need to pass parameters and you may do so by passing an array as the last argument:

$rows = $database->select("SELECT id, name FROM user WHERE name = ?", [$name]);

You may also use named parameters:

$rows = $database->select("SELECT id, name FROM user WHERE name = :name", ['name' => $name]);

For scalar results you can use the scalar method:

$count = $database->scalar("SELECT COUNT(id) FROM user");

Finally, you can chunk your results with the chunk method:

// Chunk the select in groups of 100 rows
$database->chunk(100, "SELECT id, name FROM user", [], function($rows) {
    foreach ($rows as $row) {
        // Do something with the row
    }
});

Transactions

You can easily execute transactions, either manual or automatic.

For manual transactions use the begin, commit and rollback methods:

$database->begin();
$database->query("UPDATE user SET name = ? WHERE id = ?", ['Foo', 3]);
$database->query("UPDATE user SET name = ? WHERE id = ?", ['Bar', 2]);
$database->commit();

And the there's the automatic transactions; they will automatically execute the rollback method if there's an exception thrown, otherwise they will call commit:

$database->transaction(function($database) {
    $database->query("UPDATE user SET name = ? WHERE id = ?", ['Foo', 3]);
    $database->query("UPDATE user SET name = ? WHERE id = ?", ['Bar', 2]);
});

Schema builder

With the schema builder you can easily create. modify and delete tables, just create an instance of Schema:

use Caldera\Database\Schema\Schema;

$schema = new Schema($database);

Just pass a $database object and you're good to go.

Getting tables, columns and keys

Get all the tables on the currently selected database with the getTables method:

$tables = $schema->getTables();

To retrieve the columns or keys of an specific table, use the corresponding method:

# Get the columns on USERS
$columns = $schema->getColumns('users');

# Get the keys on USERS
$keys = $schema->getKeys('users');

You may also check for the existence of any of the three entities:

# Check if USERS table exists
$schema->hasTable('users');

# Check if USERS has an ID column
$schema->hasColumn('users', 'id');

# Check if USERS has a PK_ID key
$schema->hasKey('users', 'pk_id');

Creating tables

To create a table use the create or createIfNotExists methods:

$schema->create('items', function(Table $table) {
    $table->bigInteger('id')->autoIncrement();
    $table->string('name', 120);
    $table->string('status', 50);
    $table->string('type', 50);
    $table->integer('points')->nullable()->default(0);
    $table->datetime('created')->nullable();
    $table->datetime('updated')->nullable();
    $table->primary('pk_id', 'id');
});

You will need to pass the table name and a Closure which will be called back with a Table instance, with which you will be able to specify the operations required to create the table.

In the above example several columns are added, some with nullable or default values; also a primary key is specified.

The following are the supported column types (support depends on the database adapter):

bigInteger - The equivalent of BIGINT, receives the following parameters: string $name
binary - The equivalent of BINARY, receives the following parameters: string $name
boolean - The equivalent of BOOLEAN, receives the following parameters: string $name
char - The equivalent of CHAR, receives the following parameters: string $name, int $length = 100
date - The equivalent of DATE, receives the following parameters: string $name
datetime - The equivalent of DATETIME, receives the following parameters: string $name
decimal - The equivalent of DECIMAL, receives the following parameters: string $name, int $precision = 5, int $scale = 2
double - The equivalent of DOUBLE, receives the following parameters: string $name, int $precision = 15
enum - The equivalent of ENUM, receives the following parameters: string $name, array $options = []
float - The equivalent of FLOAT, receives the following parameters: string $name
integer - The equivalent of INT, receives the following parameters: string $name
json - The equivalent of JSON, receives the following parameters: string $name
longText - The equivalent of LONGTEXT, receives the following parameters: string $name
mediumInteger - The equivalent of MEDIUMINT, receives the following parameters: string $name
mediumText - The equivalent of MEDIUMTEXT, receives the following parameters: string $name
smallInteger - The equivalent of SMALLINT, receives the following parameters: string $name
tinyInteger - The equivalent of TINYINT, receives the following parameters: string $name
string - The equivalent of STRING, receives the following parameters: string $name, int $length = 100
text - The equivalent of TEXT, receives the following parameters: string $name
time - The equivalent of TIME, receives the following parameters: string $name
timestamp - The equivalent of TIMESTAMP, receives the following parameters: string $name

Also there are the following key types:

index - The equivalent of INDEX
primary - The equivalent of PRIMARY KEY
unique - The equivalent of UNIQUE INDEX
foreign - The equivalent of FOREIGN KEY

All of the key methods receive $name, an string and $columns, an string|array<string> specifying the columns that make up the key.

Altering tables

To modify a table use the table method:

$schema->table('users', function(Table $table) {
    $table->index('pk')->name('pk_id_email')->columns(['id', 'email']);
    $table->dropIndex('pk_id');
    $table->renameColumn('login', 'email');
    $table->datetime('modified')->after('created')->nullable();
    $table->string('type')->modify()->default('Subscriber')->nullable();
    $table->dropColumn('permissions');
});

Similar to the create method, it receives the table name and a Closure that will be called to perform the modifications.

You can rename and drop columns, and drop keys using the following methods:

renameColumn(string $from, string $to) - Rename a column, pass the current name and the new one
dropColumn(string $name) - Drop a column, just pass its name
dropIndex(string $name) - Drop an index by name
dropPrimary(string $name) - Drop a primary key by name
dropUnique(string $name) - Drop a unique key by name
dropForeign(string $name) - Drop a foreign key by name

Migrations

Migrations are a powerfull tool to deploy your projects and to ensure a consistent database structure is always available.

To begin, you create a migration file, using the following naming convention:

<date(YYYYMMDD)>_<time(HHMMSS)>-<migration_name>.php

For example, create a 20230615_162311-CreateFileTable.php file and put the following inside:

<?php

declare(strict_types = 1);

namespace App\Database\Migrations;

use Caldera\Database\Migrations\AbstractMigration;
use Caldera\Database\Schema\Schema;
use Caldera\Database\Schema\Table;

class CreateFileTable extends AbstractMigration {

    /**
     * Migration up
     * @return bool
     */
    public function up(): bool {
        $schema = new Schema($this->database);
        $schema->createIfNotExists('file', function(Table $table) {
            $table->bigInteger('id');
            $table->string('name');
            $table->string('type');
            $table->string('status');
            $table->datetime('created');
            $table->datetime('updated');
            $table->primary('pk_id', 'id');
        });
        return true;
    }

    /**
     * Migration down
     * @return bool
     */
    public function down(): bool {
        $schema = new Schema($this->database);
        $schema->dropIfExists('file');
        return true;
    }
}

As you can see there are two important methods:

up - Executed when the migration is applied
down - Executed when the migration is rolled back

Usually you do changes in the up method and undo them on the down one.

Working with migrations

Now to actually execute the migrations you will need the Migrations class.

This class is commonly accesed through framework means, for example, via a CLI command. If you are not using a framework you can still call the methods directly, just create an instance of the Migrations class:

use Caldera\Database\Migrations\Migrations;

$migrations = new Migrations($database);
$migrations->setup();

You will need to pass the current $database instance, so it's recommended to use a container implementation for dependency injection.

Also note the call to the setup method: it ensures that the required tables are already in place, otherwise creates them.

The first thing you will need to do is add a folder to the migrations path, using the path method:

$migrations->path( dirname(__FILE__) . DIRECTORY_SEPARATOR . 'migrations' );

You can add several folders to separate and modularize your applications logic.

To execute all the pending migrations, use the migrate method:

$migrations->migrate();

Or you can use the status method to check which migrations have been applied and which ones are pending:

$migrations->status();

In cases where you want to undo the last applied changes you can use the rollback method:

$migrations->rollback();

By default, it will undo the last set of migrations executed, that is, all the migrations that ran on the last call of the migrate method; bu you can also specify a number of steps, for example:

$migrations->rollback(2);

Will undo the last two sets of migrations. You can also pass -1 to undo all the executed migrations so far.

And if you need to nuke the entire database and then rebuild it, use the reset method:

$migrations->reset();

This is the same as calling $migrations->rollback(-1) and then $migrations->migrate().

Seeding

Seeding allows you to quickly populate the database with data, and together with migrations are essential parts of the deployment process.

To begin, you create a Seeder, in this case naming is not so important because seeds are executed on demand, so we can create a TermSeeder class for example:

<?php

declare(strict_types = 1);

namespace App\Database\Seeds;

use Caldera\Database\Seeds\AbstractSeeder;

class TermSeeder extends AbstractSeeder {

    /**
     * Run seeder
     * @return bool
     */
    public function run(): bool {
        # TBD: Using $this->database insert some rows into the TERMS table for example
        return true;
    }
}

By extending the AbstractSeeder class you make sure that the class has a $database property, so in the run method you can use $this->database to run queries against the database.

You may also load the data from a JSON or CSV file, query a webservice or API, etc.

Working with seeds

Similar to migrations, you interact with seeders through the Seeds class:

use Caldera\Database\Seeds\Seeds;

$seeds = new Seeds($database);

You pass the current $database instance as with the Migrations class;

Again, you add some paths where your seeders reside:

$seeds->path( dirname(__FILE__) . DIRECTORY_SEPARATOR . 'Seeds' );

And execute an specific seeder with the seed method:

$seeds->seed(TermSeeder::class);