README

A small PDO query builder. Values are bound; identifiers are validated.

Requirements

PHP 8.2 or newer, with ext-pdo and a driver.

Installation

composer require umityatarkalkmaz/database

Connecting

Credentials are yours to supply — nothing is defaulted, so a misconfigured application fails to connect rather than reaching for a local root account:

use UmitYatarkalkmaz\Model;

$model = Model::connect(
    host: $_ENV['DB_HOST'],
    database: $_ENV['DB_NAME'],
    username: $_ENV['DB_USER'],
    password: $_ENV['DB_PASSWORD'],
);

Keep the credentials in the environment, not in the source. A failed connection raises PDOException; it is not caught for you, because a caught connection error leaves you holding an object with no connection, and the message often carries the credentials.

host, database and charset cannot contain ; or =: a DSN is a flat key=value;key=value string with no quoting, so either character inside a part would append or rewrite a DSN parameter.

Extra PDO attributes are merged over the defaults, which is how you force TLS:

$model = Model::connect(
    host: $_ENV['DB_HOST'],
    database: $_ENV['DB_NAME'],
    username: $_ENV['DB_USER'],
    password: $_ENV['DB_PASSWORD'],
    options: [
        PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/db-ca.pem',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ],
);

You can also pass a PDO you built yourself, which is what the tests do:

$model = new Model(new PDO('sqlite::memory:'));

Querying

$users = $model->table('user')->where('active', 1)->orderBy('username')->limit(20)->all();

$user = $model->table('user')->where('username', $username)->first();   // array|null

first() adds LIMIT 1 unless you set a limit yourself. all() fetches every matching row into memory — it has no cap of its own, so bounding a query that can grow is up to you with limit().

Conditions combine with AND, and the same column can be used more than once:

$model->table('user')->where('id', 10, '>')->where('id', 100, '<')->all();

Supported operators: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE. Anything else throws InvalidArgumentException.

IS NULL and IS NOT NULL compare against a keyword rather than a value, so they are their own methods and bind nothing:

$model->table('user')->whereNull('deleted_at')->all();

$model->table('user')->whereNotNull('email')->all();

whereLike() matches the value literally: % and _ in it are escaped, so a search term out of a request cannot widen the pattern to the whole table.

$model->table('user')->whereLike('username', $_GET['q'])->limit(20)->all();

Writing

$model->insert('user', ['username' => $name, 'password' => $hash])->execute();

$model->update('user', ['username' => $name])->where('id', $id)->execute();

$model->delete('user')->where('id', $id)->execute();

execute() returns the PDOStatement, so rowCount() tells you what happened.

An UPDATE or DELETE without a WHERE clause throws. If you really mean every row:

$model->delete('session')->execute(force: true);

Identifiers are checked, not escaped

No database driver can bind a table or column name, so they are validated instead: a name must be column or table.column, made of letters, digits and underscores, and is then quoted. Anything else throws InvalidArgumentException.

A name usually comes from somewhere, and validation is not authorisation: a plain identifier is still any column of the table, so map request input through an allowlist before it reaches orderBy().

$columns = ['name' => 'username', 'joined' => 'created_at'];
$sort = $columns[$_GET['sort'] ?? 'name'] ?? 'username';

$model->table('user')->orderBy($sort)->all();

The InvalidArgumentException messages name the value that was rejected. They are developer-facing — log them, never render them to a visitor.

Values are always bound, never interpolated, so a value that looks like SQL is stored as text.

Extending

final class User extends Model
{
    public function findByUsername(string $username): ?array
    {
        return $this->table('user')->where('username', $username)->limit(1)->first();
    }
}

See example/user.php.

Development

composer install
composer check   # phpstan (level max) + phpunit

The suite is split in two. The sqlite suite needs nothing and runs against in-memory SQLite; the mysql suite — the DSN, the connection charset, backtick quoting and binding against a real server — is skipped unless a server is configured, and fails rather than skips when CI is set:

vendor/bin/phpunit --testsuite sqlite

DB_HOST=127.0.0.1 DB_PORT=3306 DB_NAME=phpbasicdb_test \
DB_USER=phpbasicdb DB_PASSWORD=... composer test

License

MIT. See LICENSE.

umityatarkalkmaz / database

Package info

Security

Statistics