MadeSimple - Database

The database package is a abstraction layer between PHP and an SQL database. The main features of the package are:

1. Migration control
2. Database Seeding
3. Query building
4. Entities and Relationships

Migration Control

You can control migrations of your database through an easy to use, reliable command line. Possible actions are to install the migrations table, upgrade, rollback, uninstall, and refresh. These are all called through bin/database.

For example, on first clone of a package using database:

> composer install
...
> vendor/bin/database database:install -e -v
[notice] Migration table created
> vendor/bin/database database:upgrade -ep examples/migrations -v
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.0-ExampleInitial.php"
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.1-ExampleComment.php"

or you can use the shortcut:

> composer install
...
> vendor/bin/database database:migrate -e -p examples/migrations -v
[notice] Migration table created
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.0-ExampleInitial.php"
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.1-ExampleComment.php"

For example, on rollback a migration:

> vendor/bin/database database:rollback -e -v
Rolling back batch: 1
[notice] Rolled back file: "/path/to/database/examples/migrations/v1.0.1-ExampleComment.php"
[notice] Rolled back file: "/path/to/database/examples/migrations/v1.0.0-ExampleInitial.php"

For example, on uninstalling:

> vendor/bin/database database:uninstall -e -v
[notice] Migration table removed

For example, on refreshing:

> vendor/bin/database database:refresh -e -p examples/migrations/ -s examples/seeds/ -v
[notice] Migration table already installed
[notice] Rolled back file: "/path/to/database/examples/migrations/v1.0.1-ExampleComment.php"
[notice] Rolled back file: "/path/to/database/examples/migrations/v1.0.0-ExampleInitial.php"
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.0-ExampleInitial.php"
[notice] Migrated file: "/path/to/database/examples/migrations/v1.0.1-ExampleComment.php"
[notice] Seeded file: "/path/to/database/examples/seeds/v1.0.0-ExampleTableSeeder.php"

Database Seeding

You can create seeds for the database that should be used to populate the database with dummy data. This can be called through bin/database.

For example, on seeding that database:

> vendor/bin/database database:seed -e -v -s examples/seeds
[notice] Seeded file: "/path/to/database/examples/seeds/v1.0.0-ExampleTableSeeder.php"

Query Building

Select

$connection
    ->select()
    ->from('table')
    ->where('column', '=', 'value');
// SELECT * FROM `table` WHERE `columns` = ?

Update

$connection
    ->update()
    ->table('table')
    ->set('column', 'new-value')
    ->where('another-column', '=', 'value');
// UPDATE `table` SET `column`=? WHERE `another-column` = ?

Insert

$connection
    ->insert()
    ->into('table')
    ->columns('column1', 'column2')
    ->values(5, 'value');
// INSERT INTO `table` (`column1`,`column2`) VALUES (?,?)

$rows = [
    ['column1_value1', 'column2_value1'],
    ['column1_value2', 'column2_value2'],
    ['column1_value3', 'column2_value3'],
];
$connection
    ->insert()
    ->into('table')
    ->columns('column1', 'column2')
    ->chunkedQuery($rows, 2);
// INSERT INTO `table` (`column1`,`column2`) VALUES (?,?),(?,?)
// INSERT INTO `table` (`column1`,`column2`) VALUES (?,?)

Delete

$connection
    ->delete()
    ->from('table')
    ->where('column', '=', 'value');
// DELETE FROM `table` WHERE `column` = ?

Statements

$connection->statement(function (CreateTable $create) {
    $create->table('user')->ifNotExists();
    $create->column('id')->integer(10)->primaryKey()->autoIncrement();
    $create->column('uuid')->char(36)->unique()->notNull();
    $create->column('email')->char(255)->unique()->notNull();
    $create->column('password')->char(255)->notNull();
    $create->column('created_at')->timestamp()->notNull()->useCurrent();
    $create->column('updated_at')->timestamp()->notNull()->useCurrent();
    $create
        ->engine('InnoDB')
        ->charset('utf8mb4', 'utf8mb4_general_ci');
});
// CREATE TABLE IF NOT EXISTS `user` (
//   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
//   `uuid` CHAR(36) NOT NULL UNIQUE,
//   `email` CHAR(255) NOT NULL UNIQUE,
//   `password` CHAR(255) NOT NULL,
//   `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
//   `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
// ) ENGINE=InnoDB,DEFAULT CHARACTER SET=utf8mb4,COLLATE=utf8mb4_general_ci

Entities and Relationships

class User extends \MadeSimple\Database\Entity
{
    use \MadeSimple\Database\Entity\Relational;

    protected static function getMap()
    {
        return new \MadeSimple\Database\EntityMap(
            'user', // Table name
            ['id'], // Primary key(s)
            [       // Other columns: database name => property name
                'uuid',
                'email',
                'password',
                'created_at' => 'createdAt',
                'updated_at' => 'updatedAt',
            ]
        );
    }

    /**
     * @return \MadeSimple\Database\Relationship\ToMany
     */
    public function posts()
    {
        return $this->toMany()->has(Post::class, 'p', 'user_id');
    }
}
class Post extends \MadeSimple\Database\Entity
{
    use \MadeSimple\Database\Entity\Relational;

    protected static function getMap()
    {
        return new \MadeSimple\Database\EntityMap(
            'post', // Table name
            ['id'], // Primary key(s)
            [       // Other columns: database name => property name
                'uuid',
                'user_id' => 'userId',
                'title',
                'content',
                'created_at' => 'createdAt',
                'updated_at' => 'updatedAt',
            ]
        );
    }

    /**
     * @return \MadeSimple\Database\Relationship\ToOne
     */
    public function user()
    {
        return $this->toOne()->belongsTo(User::class, 'u', 'user_id');
    }
}

Supported Databases

SQL databases currently supported are:

• MySQL
• SQLite

External Documentation

Links to documentation for external dependencies:

• PHP Docs
• Logging PSR-3

Links to documentation for development only external dependencies:

• monolog/monolog
• symfony/console
• vlucas/phpdotenv