Skip to main content

Managing Data

About 4 min

Managing Data

Introduction

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations for managing persistent data. Hazaar DBI includes an ORM-style layer that focuses on creating, reading, updating, and deleting records in a consistent way across PDO drivers. It is responsible for:

  • Building SQL queries from structured criteria.
  • Executing CRUD operations and returning results in predictable formats.
  • Providing a simple adapter-level API for common operations.
  • Exposing a richer Table API for advanced queries with joins, grouping, and query composition.

You can use simple CRUD methods directly on the adapter for straightforward operations, or use the Table class for more advanced querying and control.

For more details, see the API docs for Hazaar\DBI\Adapter and Hazaar\DBI\Table.

Querying the Database (Adapter)

The adapter provides database-agnostic CRUD helpers for common queries.

$db = Hazaar\DBI\Adapter::create();
$result = $db->find('my_table');

The find() method executes a SELECT query and returns a Hazaar\DBI\Result object you can iterate over. See the Hazaar\DBI\Result class documentation for more information.

$result = $db->find('my_table', ['id' => 1]);

The fetch() method returns the next row as an associative array.

$result = $db->find('my_table');
while ($row = $result->fetch()) {
    // Do something with the row
}

Limiting Columns

You can limit the selected columns by passing an array of column names as the third argument to find() or findOne().

$result = $db->find('my_table', ['status' => 'active'], ['id', 'email', 'created_at']);
$row = $db->findOne('my_table', ['id' => 1], ['id', 'email']);

Finding a Single Row

findOne() returns the first matching row as an associative array (or false if none match).

$row = $db->findOne('my_table', ['id' => 1]);

Inserting Rows (Adapter)

Use insert() to add a new row. It returns the inserted ID or a result depending on the driver and returning behavior.

$id = $db->insert('my_table', [
    'email' => '[email protected]',
    'status' => 'active',
]);

Updating Rows (Adapter)

Use update() to modify rows. It returns the number of affected rows.

$updated = $db->update('my_table',
    ['status' => 'disabled'],
    ['id' => 1]
);

Deleting Rows (Adapter)

Use delete() to remove rows. It returns the number of affected rows.

$deleted = $db->delete('my_table', ['id' => 1]);

Inserting JSON and Binary Data

Plain scalars are inserted as-is, but some values need special handling before they're bound to the query. Wrap the value in a $-prefixed action key to tell the query builder how to bind it. This works the same way with insert()/update() on both the Adapter and the Table API.

$json

Encodes the value with json_encode() before binding. Use this for json/jsonb columns (or any text column storing a JSON-encoded string) when the value you have is a PHP array or object rather than an already-encoded string.

$db->table('events')->insert([
    'title' => 'Signup',
    'metadata' => ['$json' => ['ip' => '203.0.113.4', 'source' => 'web']],
]);

$blob

Binds the value as PDO::PARAM_LOB instead of the default PDO::PARAM_STR. Use this whenever you're inserting raw binary content (file bytes, generated PDFs, images, etc.) into a bytea (or equivalent) column:

$db->table('documents')->insert([
    'title' => 'invoice.pdf',
    'content' => ['$blob' => $pdfBytes],
]);

Warning

Binary content must use $blob. Binary data routinely contains embedded NUL bytes, and a plain string binds as PDO::PARAM_STR — which PDO_PGSQL sends as a NUL-terminated text parameter, silently truncating the value at the first NUL byte. $blob avoids this by binding as PDO::PARAM_LOB.

$int

Casts the value to int and binds it as PDO::PARAM_INT instead of the default PDO::PARAM_STR. Use this for numeric-string values you want bound with explicit integer typing rather than as text:

$db->table('events')->insert([
    'title' => 'Signup',
    'retry_count' => ['$int' => $retryCount],
]);

$bool

Casts the value to bool and binds it as PDO::PARAM_BOOL instead of the default PDO::PARAM_STR. Use this for boolean columns:

$db->table('users')->update(['active' => ['$bool' => $isActive]], ['id' => $id]);

Warning

Booleans should use $bool when the value might be false. PDO::PARAM_STR binds PHP false as an empty string (''), not 'f'/'0', which some drivers reject or misinterpret for boolean columns. $bool avoids this by binding as PDO::PARAM_BOOL.

$date / $datetime

Formats the value as Y-m-d ($date) or Y-m-d H:i:s ($datetime) before binding. If the value already implements \DateTimeInterface (a native \DateTime/\DateTimeImmutable, or Hazaar\Util\DateTime), it's formatted directly; otherwise it's passed through Hazaar\Util\DateTime's constructor first, which accepts Unix timestamps and strtotime()-style strings.

$db->table('events')->insert([
    'title' => 'Signup',
    'starts_on' => ['$date' => '2026-08-01'],
    'logged_at' => ['$datetime' => new DateTimeImmutable()],
]);

Tips

Any \DateTimeInterface value — native \DateTime/\DateTimeImmutable or Hazaar\Util\DateTime — is auto-formatted as Y-m-d H:i:s even without wrapping it in $datetime, so passing a date object directly to insert()/update() just works. $datetime is only needed if your value isn't a date object yet (a Unix timestamp or a date string); $date is always needed when you want the Y-m-d-only format instead of the default. Both the implicit conversion and the explicit actions are handled by the same code path in the query builder, so there's exactly one place that decides how a date gets formatted.

$array

Encodes a PHP array as a Postgres array literal ({a,b,c}) before binding. Use this for text[]/int[] (or any other Postgres array) columns when the value you have is a PHP array rather than an already-encoded literal string. Strings are quoted and escaped, booleans become true/false, null becomes NULL, and nested arrays produce nested literals:

$db->table('posts')->insert([
    'title' => 'Signup',
    'tags' => ['$array' => ['announcement', 'product', 'has "quotes"']],
]);
// tags column receives: {"announcement","product","has \"quotes\""}

Using the Table Class for Advanced Queries

For more complex queries, use the Table class. This gives you a fluent API for joins, grouping, ordering, and composing queries. Start by calling table() on the adapter.

$table = $db->table('users');

Joins and Advanced Selection

The Table API allows you to join other tables and build advanced queries while keeping the query database-agnostic.

$result = $db->table('users')
    ->select(['users.id', 'users.email', 'profiles.display_name'])
    ->leftJoin('profiles', ['users.id' => 'profiles.user_id'])
    ->where(['users.status' => 'active'])
    ->order(['users.created_at' => SORT_DESC])
    ->limit(25)
    ->find();

You can also add grouping and aggregates:

$result = $db->table('orders')
    ->select(['customer_id', 'COUNT(*) AS order_count'])
    ->group('customer_id')
    ->having(['order_count' => ['>' => 5]])
    ->find();

Inserting with Table

Use insert() on a Table instance to insert rows and optionally return columns:

$insertedId = $db->table('users')->insert([
    'email' => '[email protected]',
    'status' => 'active',
]);

Updating with Table

Use update() on a Table instance with criteria and optional returning columns:

$updated = $db->table('users')->update(
    ['status' => 'disabled'],
    ['id' => 1]
);

Deleting with Table

Use delete() on a Table instance with criteria:

$deleted = $db->table('users')->delete(['id' => 1]);
Copyright © 2012-present Hazaar Labs