Usage

Core patterns and workflows for WPZylos Database.

Getting the Connection

The DatabaseServiceProvider registers a Connection singleton in the container. There is no static DB:: facade — always resolve the instance from the container:

use WPZylos\Framework\Database\Connection;

// By class name
$db = $app->make(Connection::class);

// By alias
$db = $app->make('db');

In controllers, services, or any class resolved through the container, you can type-hint Connection for automatic injection:

use WPZylos\Framework\Database\Connection;

class UserService
{
    private Connection $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }
}

Basic Queries

Select

// Get all records
$users = $db->table('users')->get();

// Get first record
$user = $db->table('users')->where('id', 1)->first();

// Select specific columns
$names = $db->table('users')->select('id', 'name')->get();

// Select with array syntax
$users = $db->table('users')->select(['id', 'name AS display_name'])->get();

Insert

// Insert a row — returns insert ID or false
$id = $db->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
]);

Update

// Update rows matching conditions
$affected = $db->table('users')
    ->where('id', 1)
    ->update(['name' => 'Updated Name']);

Delete

// Delete rows matching conditions
$affected = $db->table('users')
    ->where('id', 1)
    ->delete();

Count

$total = $db->table('users')
    ->where('status', 'active')
    ->count();

Query Builder Chains

The table() method returns a QueryBuilder instance. Chain methods to build your query, then execute with get(), first(), count(), insert(), update(), or delete().

Where Clauses

// Equality (2-arg form — defaults to '=')
$users = $db->table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->get();

// Comparison operators (3-arg form)
$products = $db->table('products')
    ->where('price', '>', 100)
    ->where('stock', '>=', 10)
    ->get();

// Where IN
$users = $db->table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

Note: All where() conditions are joined with AND. There is no orWhere(), whereNull(), whereNotNull(), or whereBetween() — use raw queries for those patterns.

Ordering and Pagination

$users = $db->table('users')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->offset(20)
    ->get();

Full Chain Example

$recentOrders = $db->table('orders')
    ->select('id', 'total', 'created_at')
    ->where('status', 'completed')
    ->where('total', '>', 50)
    ->orderBy('created_at', 'DESC')
    ->limit(25)
    ->get();

Debugging with toSql()

$sql = $db->table('users')
    ->where('status', 'active')
    ->orderBy('name')
    ->toSql();

// Returns the raw SQL with %s/%d placeholders (does NOT execute)

Connection-Level CRUD

For operations that don't need the query builder, you can use Connection methods directly. These require the full table name (including prefix).

find()

Find a single row by primary key:

$user = $db->find('wp_myplugin_users', 1);

// With custom primary key column
$item = $db->find('wp_myplugin_items', 'abc-123', 'uuid');

insert() / insertGetId()

// insert() — returns insert ID or false
$id = $db->insert('wp_myplugin_users', [
    'name' => 'Jane Doe',
    'email' => '[email protected]',
]);

// insertGetId() — always returns int (insert ID)
$id = $db->insertGetId('wp_myplugin_users', [
    'name' => 'Jane Doe',
    'email' => '[email protected]',
]);

// With explicit format specifiers
$id = $db->insert('wp_myplugin_products', [
    'name' => 'Widget',
    'price' => 19.99,
], ['%s', '%f']);

update()

$rows = $db->update(
    'wp_myplugin_users',
    ['name' => 'New Name'],       // data
    ['id' => 1],                  // where
);

delete()

$rows = $db->delete('wp_myplugin_users', ['id' => 1]);

Raw Queries

Use the raw query methods when you need full SQL control. All methods use $wpdb->prepare() when arguments are provided.

// Execute a raw query
$db->query("UPDATE `wp_myplugin_stats` SET views = views + 1 WHERE id = %d", $id);

// Get a single row
$user = $db->getRow("SELECT * FROM `wp_myplugin_users` WHERE email = %s", $email);

// Get multiple rows
$logs = $db->getResults("SELECT * FROM `wp_myplugin_logs` WHERE level = %s", 'error');

// Get a single value
$count = $db->getVar("SELECT COUNT(*) FROM `wp_myplugin_users` WHERE status = %s", 'active');

Transactions

Automatic (Recommended)

Use transaction() for automatic commit/rollback:

$result = $db->transaction(function () use ($db, $orderData, $paymentData) {
    $orderId = $db->table('orders')->insert($orderData);

    $db->table('payments')->insert([
        'order_id' => $orderId,
        ...$paymentData,
    ]);

    return $orderId;
});
// $result contains the return value of the callback

If the callback throws any exception, the transaction is rolled back and the exception is re-thrown.

Manual

$db->beginTransaction();

try {
    $db->table('orders')->insert($order);
    $db->table('inventory')
        ->where('product_id', $productId)
        ->update(['stock' => $newStock]);

    $db->commit();
} catch (\Throwable $e) {
    $db->rollback();
    throw $e;
}

Table Prefixing

Connection::table() automatically prefixes the table name using the plugin's ContextInterface. You pass only the short name:

// If your plugin prefix is "myplugin_" and WP prefix is "wp_",
// this queries "wp_myplugin_users"
$users = $db->table('users')->get();

// Network-scoped table
$settings = $db->table('settings', 'network')->get();

Error Handling

$db->table('users')->insert($data);

if ($db->hasError()) {
    $error = $db->lastError();
    // Handle the error
}

// Check rows affected by last query
$count = $db->rowsAffected();

Accessing wpdb

When you need the raw WordPress $wpdb instance:

$wpdb = $db->wpdb();

// Useful for CREATE TABLE statements
$charset = $db->charsetCollate();
$db->query("
    CREATE TABLE IF NOT EXISTS `wp_myplugin_cache` (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        cache_key VARCHAR(255) NOT NULL,
        cache_value LONGTEXT,
        PRIMARY KEY (id)
    ) {$charset}
");

Configuration

API Reference