techwilk/database

Simple PDO/MySQLi/SQLite wrapper to make performing SQL queries easier

1.0.1 2024-07-08 19:32 UTC

Requires (Dev)

• ext-mysqli: *
• friendsofphp/php-cs-fixer: ^3.16
• phpunit/phpunit: ^9.5

MIT

• Christopher Wilkinson

README

A lightweight wrapper around PDO/MySqli/SQLite with a consistent interface and includes helper functions to build and run queries quickly and easily.

For select statements and other complex queries you are expected to write raw paramatarised sql, using the "question mark" syntax.

Installation

1. Install through composer (composer require techwilk/database)
2. Create database instance for either PDO / MySqli / SQLite

use TechWilk\Database\MySqli\MySqliDatabase;

$database = new MySqliDatabase(
    'localhost',
    'database-name',
    'username',
    'password',
);

Select

Available functions:

• query
• runQuery

Examples

No runtime parameters

$result = $database->query('SELECT * FROM `users`');
$rows = $result->fetchAll();
var_dump($rows);

With runtime parameters

$parameters = [1];
$result = $database->query('SELECT * FROM `users` WHERE id = ?', $parameters);
$row = $result->fetch();
var_dump($row);

Query generated elsewhere in code the codebase

function customQueryBuilder() {
    $parameters = [1];
    $query = new Query(
        'SELECT * FROM `users` WHERE id = ?',
        $parameters,
    );
    return $query;
}

$query = customQueryBuilder();
$result = $database->runQuery($query);
$row = $result->fetch();
var_dump($row);

Insert

Available functions:

• insert
• insertOnDuplicate
• query
• runQuery

Create new simple record

$data = [
    'id' => 3,
    'name' => 'Tim Jones',
    'auth_id' => 'xxx123yyy',
    'date_created' => '2022-03-03 00:00:00',
];
$id = $database->insert('users', $data);
var_dump($id);

Create but handle potential key clash

$data = [
    'name' => 'admin', // unique key
    'uses_count' => 1,
];
$onDuplicate = [
    'uses_count +' => 1, // += 1
];
$id = $database->insertOnDuplicate('tags', $data, $onDuplicate);
var_dump($id);

Complex cross-table insert

$sql = 'INSERT INTO users (`id`, `name`, `auth_id`, `date_created`) VALUES (?, ?, ?, ?)'
$parameters = [
    3, // id
    'Tim Jones', // name
    'xxx123yyy', // auth_id
    '2022-03-03 00:00:00', // date_created
];
$id = $database->query($sql, $parameters);
var_dump($id);

Update

Available functions:

• update
• updateUsingIn
• updateChanges
• selectAndUpdate
• query
• runQuery

$data = [
    'name' => 'Timothy Jones',
];
$rowCount = $database->update('users', $data, ['id' => 3]);
var_dump($rowCount);

Delete

Available functions:

• delete
• deleteUsingIn
• query
• runQuery

$rowCount = $database->delete('table', ['id' => 3]);
var_dump($rowCount);

Testing

• copy phpunit.xml.dist to phpunit.xml
• fill out the environment details
• run composer test

Testing environment

• requires a copy of each database available

MySQL

• podman network create database-tests

• podman exec -it database-percona mysql -uroot -p

• mysql> CREATE DATABASE tests;
• mysql> `CREATE USER tests@% IDENTIFIED BY 'create-random-password-here';`
• mysql> `GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON tests.* TO tests@%;`
• mysql> FLUSH PRIVILEGES;

• mysql> exit

• ensure you make a note of which port the db is being exposed on (using podman ps). This is likely a large number, such as 44449