iviphp/database

Database connections and query building for the IviPHP ecosystem.

Maintainers

Package info

github.com/iviphp/database

pkg:composer/iviphp/database

Transparency log

Statistics

Installs: 0

Dependents: 0

Suggesters: 0

Stars: 1

Open Issues: 0

Security

Advisories: 0

Aikido package health analysis

v0.1.0 2026-07-21 12:15 UTC

Requires

MIT 8302dfdc0e647fcf977a3b26dccf8ccae6948dd1

Published on Packagist.org on 2026-07-21 12:19 UTC

This package is not auto-updated.

Last update: 2026-07-22 01:03:05 UTC

README

Ivi Database

Database connections, transactions and safe query building for the IviPHP ecosystem.

Overview

iviphp/database provides a lightweight database layer built on top of PHP PDO.

It supports:

  • SQLite, MySQL and PostgreSQL;
  • named database connections;
  • lazy PDO connection creation;
  • prepared statements;
  • safe parameter binding;
  • transactions and nested transactions;
  • transaction retries for transient failures;
  • CRUD query building;
  • strict identifier validation;
  • protection against accidental full-table updates and deletes;
  • structured database and query exceptions.

The package does not include an ORM, model system, migrations or schema builder. These features can be added by higher-level IviPHP packages.

Installation

composer require iviphp/database

Requirements

  • PHP 8.2 or later
  • Composer
  • PHP PDO extension
  • the PDO driver required by your database
  • iviphp/support

Common PDO drivers include:

pdo_sqlite
pdo_mysql
pdo_pgsql

Check installed drivers:

php -r 'print_r(PDO::getAvailableDrivers());'

Supported databases

The initial package supports:

  • SQLite
  • MySQL
  • PostgreSQL

Custom PDO DSNs can also be used when the driver is available.

Basic configuration

<?php

declare(strict_types=1);

use Ivi\Database\Database;

$database = Database::fromConfig([
    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => __DIR__ . '/storage/database.sqlite',
        ],
    ],
]);

Access the default connection:

$connection = $database->connection();

Access a named connection:

$connection = $database->connection('sqlite');

SQLite configuration

$database = Database::fromConfig([
    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => __DIR__ . '/storage/database.sqlite',
            'foreign_keys' => true,
            'busy_timeout' => 5000,
        ],
    ],
]);

Use an in-memory SQLite database:

$database = Database::fromConfig([
    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => ':memory:',
        ],
    ],
]);

SQLite foreign-key enforcement is enabled by default.

The default busy timeout is 5000 milliseconds.

MySQL configuration

$database = Database::fromConfig([
    'default' => 'mysql',

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'port' => 3306,
            'database' => 'ivi',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8mb4',
        ],
    ],
]);

Connect through a Unix socket:

$database = Database::fromConfig([
    'default' => 'mysql',

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'unix_socket' => '/var/run/mysqld/mysqld.sock',
            'database' => 'ivi',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8mb4',
        ],
    ],
]);

Native prepared statements are enabled by default.

PostgreSQL configuration

$database = Database::fromConfig([
    'default' => 'pgsql',

    'connections' => [
        'pgsql' => [
            'driver' => 'pgsql',
            'host' => '127.0.0.1',
            'port' => 5432,
            'database' => 'ivi',
            'username' => 'postgres',
            'password' => 'secret',
            'sslmode' => 'prefer',
        ],
    ],
]);

Custom PDO DSN

$database = Database::fromConfig([
    'default' => 'custom',

    'connections' => [
        'custom' => [
            'driver' => 'mysql',
            'dsn' => 'mysql:host=127.0.0.1;dbname=ivi;charset=utf8mb4',
            'username' => 'root',
            'password' => 'secret',
        ],
    ],
]);

When dsn is provided, the package uses it directly.

PDO options

Custom PDO options can be provided:

use PDO;

$database = Database::fromConfig([
    'default' => 'mysql',

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'database' => 'ivi',
            'username' => 'root',
            'password' => 'secret',

            'options' => [
                PDO::ATTR_TIMEOUT => 5,
            ],
        ],
    ],
]);

Default options include:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION;
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC;
PDO::ATTR_STRINGIFY_FETCHES => false;

Native prepared statements are used for MySQL and PostgreSQL.

Connection initialization

Driver-specific SQL statements can run after connection creation:

$database = Database::fromConfig([
    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => __DIR__ . '/storage/database.sqlite',

            'init' => [
                'PRAGMA journal_mode = WAL',
                'PRAGMA synchronous = NORMAL',
            ],
        ],
    ],
]);

Initialization statements must be trusted application configuration.

Never place untrusted input inside initialization SQL.

Multiple connections

$database = Database::fromConfig([
    'default' => 'primary',

    'connections' => [
        'primary' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'database' => 'application',
            'username' => 'root',
            'password' => 'secret',
        ],

        'analytics' => [
            'driver' => 'pgsql',
            'host' => '127.0.0.1',
            'database' => 'analytics',
            'username' => 'postgres',
            'password' => 'secret',
        ],

        'local' => [
            'driver' => 'sqlite',
            'database' => __DIR__ . '/storage/local.sqlite',
        ],
    ],
]);

Resolve a named connection:

$analytics = $database->connection('analytics');

Create a query builder on a named connection:

$events = $database->table(
    'events',
    'analytics'
);

Direct connection map

A direct map of connections is also accepted:

$database = Database::fromConfig([
    'default' => [
        'driver' => 'sqlite',
        'database' => __DIR__ . '/storage/database.sqlite',
    ],
]);

In this form, the connection named default becomes the default connection.

Connection manager

Create a manager directly:

use Ivi\Database\ConnectionManager;

$manager = new ConnectionManager(
    configurations: [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => ':memory:',
        ],
    ],
    defaultConnection: 'sqlite'
);

Resolve a connection:

$connection = $manager->connection();

Equivalent alias:

$connection = $manager->get();

Registering connections at runtime

$database->addConnection(
    'secondary',
    [
        'driver' => 'sqlite',
        'database' => __DIR__ . '/storage/secondary.sqlite',
    ]
);

Replace an existing connection:

$database->replaceConnection(
    'secondary',
    [
        'driver' => 'sqlite',
        'database' => __DIR__ . '/storage/new-secondary.sqlite',
    ]
);

Replacing a connection disconnects and discards its existing PDO instance.

Remove a connection:

$database->removeConnection('secondary');

Default connection

Return the current default name:

$name = $database->defaultConnectionName();

Change the default connection:

$database->setDefaultConnection('analytics');

The connection must already be registered.

Connection state

PDO connections are created lazily.

$connection = $database->connection();

$connection->isConnected();

Resolving a Connection object does not immediately open a database connection.

The connection opens when PDO is first required:

$pdo = $connection->pdo();

Reconnect:

$connection->reconnect();

Disconnect one connection:

$database->disconnect('analytics');

Disconnect the default connection:

$database->disconnect();

Disconnect all active connections:

$database->disconnectAll();

Direct SQL queries

Execute a prepared query:

$statement = $database->query(
    'SELECT * FROM users WHERE email = :email',
    [
        'email' => 'user@example.com',
    ]
);

Positional bindings are supported:

$statement = $database->query(
    'SELECT * FROM users WHERE status = ? AND age >= ?',
    [
        'active',
        18,
    ]
);

Selecting rows

Return all rows:

$users = $database->select(
    'SELECT * FROM users WHERE status = :status',
    [
        'status' => 'active',
    ]
);

Return the first row:

$user = $database->selectOne(
    'SELECT * FROM users WHERE id = :id',
    [
        'id' => 42,
    ]
);

When no row exists, selectOne() returns null.

Scalar values

$count = $database->scalar(
    'SELECT COUNT(*) FROM users',
    default: 0
);

Another example:

$email = $database->scalar(
    'SELECT email FROM users WHERE id = :id',
    [
        'id' => 42,
    ]
);

Executing statements

$affected = $database->execute(
    'UPDATE users SET active = :active WHERE id = :id',
    [
        'active' => true,
        'id' => 42,
    ]
);

execute() returns the number of affected rows reported by PDO.

Direct inserts

$id = $database->insert(
    'INSERT INTO users (name, email) VALUES (:name, :email)',
    [
        'name' => 'Gaspard',
        'email' => 'gaspard@example.com',
    ]
);

The generated identifier is returned as a string.

PostgreSQL sequences can be supplied:

$id = $database->insert(
    sql: 'INSERT INTO users (name) VALUES (:name)',
    bindings: [
        'name' => 'Gaspard',
    ],
    sequence: 'users_id_seq',
    connection: 'pgsql'
);

Safe binding types

The connection accepts these binding values:

  • null;
  • booleans;
  • integers;
  • finite floating-point values;
  • strings;
  • objects implementing Stringable.

Unsupported values such as arrays and arbitrary objects produce a QueryException.

$database->query(
    'SELECT * FROM users WHERE id = :id',
    [
        'id' => 42,
    ]
);

Bindings are passed separately from SQL and are never interpolated into the query string.

Query builder

Create a query builder:

$query = $database->table('users');

The builder validates table and column identifiers before generating SQL.

Selecting data

$users = $database
    ->table('users')
    ->select([
        'id',
        'name',
        'email',
    ])
    ->get();

Select one column:

$users = $database
    ->table('users')
    ->select('email')
    ->get();

Append columns:

$query = $database
    ->table('users')
    ->select('id')
    ->addSelect([
        'name',
        'email',
    ]);

Select distinct values:

$countries = $database
    ->table('users')
    ->select('country')
    ->distinct()
    ->get();

Column aliases

Safe aliases are supported:

$users = $database
    ->table('users')
    ->select([
        'id',
        'name AS display_name',
    ])
    ->get();

Only simple validated identifiers are accepted.

Arbitrary SQL expressions are not accepted by the initial query builder API.

WHERE conditions

Equality is assumed when two arguments are supplied:

$user = $database
    ->table('users')
    ->where('email', 'user@example.com')
    ->first();

Use an explicit operator:

$users = $database
    ->table('users')
    ->where('age', '>=', 18)
    ->get();

Supported operators include:

=
!=
<>
<
<=
>
>=
LIKE
NOT LIKE

PostgreSQL also supports:

ILIKE
NOT ILIKE

Multiple WHERE conditions

$users = $database
    ->table('users')
    ->where('active', true)
    ->where('age', '>=', 18)
    ->get();

This produces conditions joined with AND.

OR conditions

$users = $database
    ->table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'editor')
    ->get();

Conditions are compiled in declaration order.

Grouped nested conditions are not included in the initial API.

NULL conditions

$users = $database
    ->table('users')
    ->whereNull('deleted_at')
    ->get();

Not null:

$users = $database
    ->table('users')
    ->whereNotNull('email_verified_at')
    ->get();

OR variants:

$query
    ->orWhereNull('deleted_at')
    ->orWhereNotNull('email_verified_at');

Passing null to an equality condition is converted automatically:

$query->where('deleted_at', null);

Equivalent SQL:

WHERE "deleted_at" IS NULL

IN conditions

$users = $database
    ->table('users')
    ->whereIn(
        'role',
        [
            'admin',
            'editor',
        ]
    )
    ->get();

Not in:

$users = $database
    ->table('users')
    ->whereNotIn(
        'status',
        [
            'blocked',
            'deleted',
        ]
    )
    ->get();

OR variants:

$query->orWhereIn('role', ['admin', 'editor']);
$query->orWhereNotIn('status', ['blocked']);

An empty whereIn() list always produces a false condition.

An empty whereNotIn() list always produces a true condition.

BETWEEN conditions

$products = $database
    ->table('products')
    ->whereBetween(
        'price',
        [
            10,
            100,
        ]
    )
    ->get();

Not between:

$products = $database
    ->table('products')
    ->whereNotBetween(
        'price',
        [
            10,
            100,
        ]
    )
    ->get();

Exactly two values are required.

Comparing columns

$items = $database
    ->table('inventory')
    ->whereColumn(
        'reserved_quantity',
        '<=',
        'available_quantity'
    )
    ->get();

Column identifiers are validated and are not treated as bound values.

Sorting

$users = $database
    ->table('users')
    ->orderBy('name')
    ->get();

Descending order:

$users = $database
    ->table('users')
    ->orderByDesc('created_at')
    ->get();

Multiple sort clauses are supported:

$users = $database
    ->table('users')
    ->orderBy('role')
    ->orderByDesc('created_at')
    ->get();

Only ASC and DESC are accepted.

Limits and offsets

$users = $database
    ->table('users')
    ->limit(20)
    ->offset(40)
    ->get();

Aliases are available:

$users = $database
    ->table('users')
    ->take(20)
    ->skip(40)
    ->get();

Negative limits and offsets are rejected.

First row

$user = $database
    ->table('users')
    ->where('id', 42)
    ->first();

Select specific columns:

$user = $database
    ->table('users')
    ->where('id', 42)
    ->first([
        'id',
        'name',
        'email',
    ]);

When no row exists, first() returns null.

Single value

$email = $database
    ->table('users')
    ->where('id', 42)
    ->value('email');

Provide a default:

$email = $database
    ->table('users')
    ->where('id', 42)
    ->value(
        'email',
        'unknown@example.com'
    );

Plucking values

$emails = $database
    ->table('users')
    ->where('active', true)
    ->pluck('email');

The result is a flat array.

Counting rows

$count = $database
    ->table('users')
    ->where('active', true)
    ->count();

Count one column:

$count = $database
    ->table('users')
    ->count('email');

Checking existence

$exists = $database
    ->table('users')
    ->where('email', 'user@example.com')
    ->exists();

The query stops after the first matching row.

Inserting one row

$affected = $database
    ->table('users')
    ->insert([
        'name' => 'Gaspard',
        'email' => 'gaspard@example.com',
        'active' => true,
    ]);

The number of affected rows is returned.

Inserting multiple rows

$affected = $database
    ->table('users')
    ->insertMany([
        [
            'name' => 'Alice',
            'email' => 'alice@example.com',
        ],
        [
            'name' => 'Bob',
            'email' => 'bob@example.com',
        ],
    ]);

Every row must contain the same columns in the same order.

Insert and return identifier

$id = $database
    ->table('users')
    ->insertGetId([
        'name' => 'Gaspard',
        'email' => 'gaspard@example.com',
    ]);

The identifier is returned as a string.

PostgreSQL sequence:

$id = $database
    ->table('users')
    ->insertGetId(
        values: [
            'name' => 'Gaspard',
        ],
        sequence: 'users_id_seq'
    );

Updating rows

$affected = $database
    ->table('users')
    ->where('id', 42)
    ->update([
        'name' => 'Updated name',
        'active' => true,
    ]);

A WHERE condition is required.

This protects applications from accidental full-table updates.

The following call throws a LogicException:

$database
    ->table('users')
    ->update([
        'active' => false,
    ]);

For an intentional full-table update:

$database
    ->table('users')
    ->updateAll([
        'active' => false,
    ]);

The explicit method makes the destructive intent visible.

Deleting rows

$affected = $database
    ->table('users')
    ->where('id', 42)
    ->delete();

A WHERE condition is required.

This call throws a LogicException:

$database
    ->table('users')
    ->delete();

For an intentional full-table delete:

$database
    ->table('users')
    ->deleteAll();

Compiled SQL and bindings

Inspect the generated SELECT SQL:

$query = $database
    ->table('users')
    ->where('active', true)
    ->orderByDesc('created_at')
    ->limit(20);

$sql = $query->toSql();
$bindings = $query->bindings();

Example SQL:

SELECT * FROM "users"
WHERE "active" = :w1
ORDER BY "created_at" DESC
LIMIT 20

Bindings:

[
    'w1' => true,
]

MySQL identifiers are wrapped with backticks.

SQLite and PostgreSQL identifiers are wrapped with double quotes.

Resetting a query builder

$query = $database
    ->table('users')
    ->where('active', true)
    ->orderBy('name');

$query->reset();

reset() clears:

  • selected columns;
  • distinct mode;
  • where conditions;
  • bindings;
  • ordering;
  • limit;
  • offset;
  • parameter counters.

The table and database connection remain unchanged.

Transactions

$result = $database->transaction(
    function ($connection): int {
        $connection
            ->table('accounts')
            ->where('id', 1)
            ->update([
                'balance' => 900,
            ]);

        $connection
            ->table('accounts')
            ->where('id', 2)
            ->update([
                'balance' => 1100,
            ]);

        return 1;
    }
);

The callback receives the selected Connection.

When the callback succeeds, the transaction is committed.

When it throws, the transaction is rolled back and the original exception is rethrown.

Named connection transactions

$result = $database->transaction(
    callback: function ($connection): void {
        $connection
            ->table('events')
            ->insert([
                'name' => 'application.started',
            ]);
    },
    attempts: 1,
    connection: 'analytics'
);

Transaction retries

Transient transaction failures can be retried:

$result = $database->transaction(
    callback: function ($connection): void {
        $connection
            ->table('accounts')
            ->where('id', 42)
            ->update([
                'balance' => 500,
            ]);
    },
    attempts: 3
);

Retryable conditions include common:

  • deadlocks;
  • serialization failures;
  • lock timeouts;
  • SQLite database lock failures.

Only transient database failures are retried.

Application exceptions are not retried.

Manual transactions

$connection = $database->connection();

$connection->beginTransaction();

try {
    $connection
        ->table('users')
        ->insert([
            'name' => 'Gaspard',
        ]);

    $connection->commit();
} catch (Throwable $exception) {
    $connection->rollBack();

    throw $exception;
}

Check transaction state:

$connection->inTransaction();
$connection->transactionLevel();

Nested transactions

Nested transactions are implemented through savepoints.

$connection->beginTransaction();

try {
    $connection
        ->table('users')
        ->insert([
            'name' => 'First',
        ]);

    $connection->beginTransaction();

    try {
        $connection
            ->table('users')
            ->insert([
                'name' => 'Second',
            ]);

        $connection->commit();
    } catch (Throwable $exception) {
        $connection->rollBack();
    }

    $connection->commit();
} catch (Throwable $exception) {
    $connection->rollBack();

    throw $exception;
}

Savepoints are supported for:

  • SQLite;
  • MySQL;
  • PostgreSQL.

Database exceptions

DatabaseException

Represents connection, configuration and transaction failures.

use Ivi\Database\Exceptions\DatabaseException;

try {
    $database->connection('missing');
} catch (DatabaseException $exception) {
    $message = $exception->getMessage();
    $context = $exception->context();
}

Factory methods include:

DatabaseException::unsupportedDriver($driver);
DatabaseException::missingDriver($driver);

DatabaseException::invalidConfiguration(
    $connection,
    $reason
);

DatabaseException::connectionNotFound(
    $connection
);

DatabaseException::connectionFailed(
    $connection,
    $driver,
    $previous
);

DatabaseException::transactionFailed(
    $connection,
    $operation,
    $previous
);

Query exceptions

QueryException

Represents query preparation, binding and execution failures.

use Ivi\Database\Exceptions\QueryException;

try {
    $database->query(
        'SELECT * FROM missing_table'
    );
} catch (QueryException $exception) {
    $sql = $exception->sql();
    $connection = $exception->connection();
    $types = $exception->bindingTypes();
}

Factory methods include:

QueryException::executionFailed(
    $sql,
    $bindings,
    $connection,
    $previous
);

QueryException::preparationFailed(
    $sql,
    $connection,
    $previous
);

QueryException::invalidBindings(
    $sql,
    $bindings,
    $connection,
    $reason,
    $previous
);

Sensitive binding data

Query bindings may contain:

  • passwords;
  • authentication tokens;
  • personal data;
  • payment information;
  • private application data.

Do not log raw bindings without filtering.

Safe binding types:

$types = $exception->bindingTypes();

Redacted bindings:

$bindings = $exception->redactedBindings();

Example:

[
    'email' => '[string:19 bytes]',
    'password' => '[string:60 bytes]',
    'active' => true,
]

The original bindings remain available:

$raw = $exception->bindings();

Use them carefully and never expose them directly to clients.

Safe configuration output

Connection configuration can be inspected without returning common secrets.

$config = $database
    ->connection()
    ->configuration();

Manager configuration:

$config = $database
    ->manager()
    ->configuration('mysql');

All configurations:

$configurations = $database
    ->manager()
    ->configurations();

Common sensitive keys such as passwords, secrets and tokens are removed.

Available classes

Database

Database::fromConfig($configuration);

$database->manager();

$database->defaultConnectionName();
$database->setDefaultConnection($name);

$database->hasConnection($name);
$database->connectionNames();
$database->connection($name);

$database->addConnection(
    $name,
    $configuration,
    $replace
);

$database->replaceConnection(
    $name,
    $configuration
);

$database->removeConnection($name);

$database->table(
    $table,
    $connection
);

$database->query(
    $sql,
    $bindings,
    $connection
);

$database->select(
    $sql,
    $bindings,
    $connection
);

$database->selectOne(
    $sql,
    $bindings,
    $connection
);

$database->scalar(
    $sql,
    $bindings,
    $default,
    $connection
);

$database->execute(
    $sql,
    $bindings,
    $connection
);

$database->insert(
    $sql,
    $bindings,
    $sequence,
    $connection
);

$database->transaction(
    $callback,
    $attempts,
    $connection
);

$database->reconnect($name);
$database->disconnect($name);
$database->disconnectAll();

ConnectionManager

$manager->add(
    $name,
    $configuration,
    $replace
);

$manager->replace(
    $name,
    $configuration
);

$manager->has($name);
$manager->names();

$manager->defaultName();
$manager->setDefault($name);

$manager->configuration($name);
$manager->configurations();

$manager->connection($name);
$manager->get($name);
$manager->isConnected($name);
$manager->reconnect($name);

$manager->disconnect($name);
$manager->disconnectAll();

$manager->remove($name);

$manager->table(
    $table,
    $connection
);

$manager->transaction(
    $callback,
    $attempts,
    $connection
);

$manager->flush();

Connection

$connection->name();
$connection->driver();
$connection->configuration();

$connection->pdo();
$connection->isConnected();
$connection->disconnect();
$connection->reconnect();

$connection->query($sql, $bindings);
$connection->select($sql, $bindings);
$connection->selectOne($sql, $bindings);
$connection->scalar($sql, $bindings, $default);
$connection->execute($sql, $bindings);
$connection->insert($sql, $bindings, $sequence);

$connection->transaction(
    $callback,
    $attempts
);

$connection->beginTransaction();
$connection->commit();
$connection->rollBack();

$connection->inTransaction();
$connection->transactionLevel();

$connection->table($table);

QueryBuilder

$query->connection();
$query->tableName();

$query->select($columns);
$query->addSelect($columns);
$query->distinct($enabled);

$query->where(
    $column,
    $operatorOrValue,
    $value
);

$query->orWhere(
    $column,
    $operatorOrValue,
    $value
);

$query->whereColumn(
    $first,
    $operator,
    $second,
    $boolean
);

$query->whereNull($column, $boolean);
$query->orWhereNull($column);

$query->whereNotNull($column, $boolean);
$query->orWhereNotNull($column);

$query->whereIn(
    $column,
    $values,
    $boolean
);

$query->orWhereIn(
    $column,
    $values
);

$query->whereNotIn(
    $column,
    $values,
    $boolean
);

$query->orWhereNotIn(
    $column,
    $values
);

$query->whereBetween(
    $column,
    $values,
    $boolean
);

$query->whereNotBetween(
    $column,
    $values,
    $boolean
);

$query->orderBy(
    $column,
    $direction
);

$query->orderByDesc($column);

$query->limit($limit);
$query->take($limit);

$query->offset($offset);
$query->skip($offset);

$query->get($columns);
$query->first($columns);
$query->value($column, $default);
$query->pluck($column);

$query->count($column);
$query->exists();

$query->insert($values);
$query->insertMany($rows);
$query->insertGetId(
    $values,
    $sequence
);

$query->update($values);
$query->updateAll($values);

$query->delete();
$query->deleteAll();

$query->toSql();
$query->bindings();
$query->reset();

DatabaseException

$exception->getMessage();
$exception->context();
$exception->getPrevious();

QueryException

$exception->sql();
$exception->bindings();
$exception->bindingTypes();
$exception->redactedBindings();
$exception->connection();
$exception->databaseError();
$exception->context();

Security principles

The package is designed around several safety rules.

Prepared statements

Values are always passed separately from SQL.

$database->query(
    'SELECT * FROM users WHERE email = :email',
    [
        'email' => $email,
    ]
);

Do not build SQL through string concatenation:

$sql = "SELECT * FROM users WHERE email = '{$email}'";

Identifier validation

Table and column identifiers are validated before query generation.

Untrusted input must never be used as a table or column name.

$database->table($_GET['table']);

Even with identifier validation, exposing dynamic table selection to users is not recommended.

Use an explicit allowlist instead.

Protected destructive operations

update() and delete() require a WHERE condition.

Intentional full-table operations use explicit methods:

$query->updateAll($values);
$query->deleteAll();

Secret redaction

Connection configuration removes common secret fields before exposure.

Query exceptions provide binding metadata and redacted values.

No automatic raw SQL expressions

The initial query-builder API does not accept arbitrary expressions for columns, conditions or sorting.

Direct trusted SQL remains available through query(), select() and execute().

Query builder boundaries

The initial query builder focuses on common safe CRUD operations.

It currently does not provide:

  • joins;
  • nested condition groups;
  • subqueries;
  • grouping;
  • HAVING clauses;
  • unions;
  • raw SQL expressions;
  • database-specific upserts;
  • schema creation;
  • migrations;
  • ORM models;
  • relationships;
  • eager loading.

These features can be added later without weakening the safety of the initial API.

Error handling

use Ivi\Database\Exceptions\DatabaseException;
use Ivi\Database\Exceptions\QueryException;

try {
    $users = $database
        ->table('users')
        ->where('active', true)
        ->get();
} catch (QueryException $exception) {
    error_log(
        json_encode([
            'message' => $exception->getMessage(),
            'connection' => $exception->connection(),
            'sql' => $exception->sql(),
            'bindings' => $exception->redactedBindings(),
        ])
    );
} catch (DatabaseException $exception) {
    error_log(
        json_encode([
            'message' => $exception->getMessage(),
            'context' => $exception->context(),
        ])
    );
}

Database error details should not be returned directly to application users.

Complete example

<?php

declare(strict_types=1);

use Ivi\Database\Database;
use Ivi\Database\Exceptions\DatabaseException;
use Ivi\Database\Exceptions\QueryException;

$database = Database::fromConfig([
    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => __DIR__ . '/storage/database.sqlite',
            'foreign_keys' => true,
            'busy_timeout' => 5000,
        ],
    ],
]);

try {
    $userId = $database->transaction(
        function ($connection): string {
            $id = $connection
                ->table('users')
                ->insertGetId([
                    'name' => 'Gaspard',
                    'email' => 'gaspard@example.com',
                    'active' => true,
                ]);

            $connection
                ->table('profiles')
                ->insert([
                    'user_id' => (int) $id,
                    'bio' => null,
                ]);

            return $id;
        },
        attempts: 3
    );

    $user = $database
        ->table('users')
        ->select([
            'id',
            'name',
            'email',
        ])
        ->where('id', (int) $userId)
        ->first();

    if ($user === null) {
        throw new RuntimeException(
            'The created user could not be found.'
        );
    }
} catch (QueryException $exception) {
    error_log(
        json_encode([
            'message' => $exception->getMessage(),
            'connection' => $exception->connection(),
            'sql' => $exception->sql(),
            'bindings' => $exception->redactedBindings(),
        ])
    );

    throw $exception;
} catch (DatabaseException $exception) {
    error_log(
        json_encode([
            'message' => $exception->getMessage(),
            'context' => $exception->context(),
        ])
    );

    throw $exception;
}

Design principles

  • PDO as the low-level database foundation
  • Prepared statements for values
  • Native prepared statements where supported
  • Lazy connection creation
  • Named connection management
  • Explicit transaction control
  • Nested transactions through savepoints
  • Safe transaction retries
  • Strict identifier validation
  • Protected destructive operations
  • No global database state
  • No hidden query execution
  • No automatic ORM behavior
  • Safe diagnostic context
  • Framework-independent implementation

Package boundaries

This package is responsible for:

  • database connection configuration;
  • PDO connection management;
  • prepared query execution;
  • transaction handling;
  • safe CRUD query construction;
  • database and query exceptions.

It is not responsible for:

  • schema migrations;
  • table creation abstractions;
  • ORM models;
  • model relationships;
  • data validation;
  • authentication;
  • authorization;
  • caching;
  • HTTP request handling;
  • application bootstrapping.

These responsibilities belong to other IviPHP packages.

Ecosystem

This package is part of the IviPHP ecosystem:

  • iviphp/contracts
  • iviphp/support
  • iviphp/config
  • iviphp/container
  • iviphp/http
  • iviphp/validation
  • iviphp/cache
  • iviphp/view
  • iviphp/auth
  • iviphp/framework

Contributing

Contributions should preserve the safety and focused scope of the package.

Changes should:

  • preserve parameterized value binding;
  • reject invalid identifiers;
  • avoid implicit raw SQL;
  • preserve full-table update and delete protections;
  • avoid logging sensitive bindings;
  • remain independent from the complete framework;
  • provide consistent behavior across supported PDO drivers;
  • include clear failures for unsupported operations.

Security

Database validation does not replace application security controls such as:

  • authorization;
  • input validation;
  • output escaping;
  • CSRF protection;
  • secure credential storage;
  • least-privilege database accounts;
  • encrypted transport;
  • database backups;
  • audit logging.

Use a database account with only the permissions required by the application.

Store passwords and connection secrets outside version control.

Please report security issues privately to the Softadastra maintainers instead of opening a public issue.

License

This project is licensed under the MIT License.

Maintainer

Created and maintained by Softadastra.