Introduction

php-sqlx is a modern, high-performance SQL database driver for PHP, written in Rust. It provides a powerful and ergonomic API for working with PostgreSQL, MySQL, and Microsoft SQL Server databases.

Why php-sqlx?

Traditional PHP database extensions like PDO are written in C and have remained largely unchanged for years. php-sqlx takes a different approach:

• Written in Rust - Memory-safe, fast, and modern
• Async under the hood - Uses SQLx and Tokio for efficient I/O
• Augmented SQL - Extends SQL with conditional blocks and type-safe placeholders
• Query Builder - Fluent API for building complex queries safely
• Connection Pooling - Built-in connection pool with read replica support
• Better DX - Intuitive methods like queryRow(), queryAll(), queryDictionary()

Key Features

Multi-Database Support

// PostgreSQL
$pg = Sqlx\DriverFactory::make("postgres://user:pass@localhost/mydb");

// MySQL
$mysql = Sqlx\DriverFactory::make("mysql://user:pass@localhost/mydb");

// Microsoft SQL Server
$mssql = Sqlx\DriverFactory::make("mssql://user:pass@localhost/mydb");

Intuitive Query API

// Get a single row
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [42]);

// Get all rows
$users = $driver->queryAll("SELECT * FROM users WHERE active = ?", [true]);

// Get a single value
$count = $driver->queryValue("SELECT COUNT(*) FROM users");

// Get a column as array
$emails = $driver->queryColumn("SELECT email FROM users");

// Get a dictionary (id => row)
$usersById = $driver->queryDictionary("SELECT id, * FROM users");

Augmented SQL Syntax

// Conditional blocks - omitted when parameter is missing/null
$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
    {{ AND role = $role }}
", ['status' => 'active']); // role block is omitted

// Type-safe placeholders
$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [18]); // unsigned int

Fluent Query Builder

$users = $driver->builder()
    ->select(['id', 'name', 'email'])
    ->from('users')
    ->where([['status', '=', 'active']])
    ->orderBy(['created_at' => 'DESC'])
    ->limit(10)
    ->queryAll();

Transaction Support

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders ...", [...]);
    $driver->execute("UPDATE inventory ...", [...]);
    return true; // commit
});

Requirements

• PHP 8.1 or higher
• Linux, macOS, or Windows
• One of: PostgreSQL, MySQL, or Microsoft SQL Server

What’s Next?

• Installation - Get php-sqlx up and running
• Quick Start - Your first queries
• Connection Strings - Configure your database connection

Installation

php-sqlx is distributed as a PHP extension. You can install it from pre-built binaries or compile from source.

Pre-built Binaries

Linux (Ubuntu/Debian)

# Download the latest release
curl -LO https://github.com/kakserpom/php-sqlx-rs/releases/latest/download/sqlx-linux-x64.so

# Move to PHP extension directory
sudo mv sqlx-linux-x64.so $(php -r "echo ini_get('extension_dir');")/sqlx.so

# Enable the extension
echo "extension=sqlx.so" | sudo tee /etc/php/$(php -r 'echo PHP_MAJOR_VERSION.".".PHP_MINOR_VERSION;')/mods-available/sqlx.ini
sudo phpenmod sqlx

macOS

# Download the latest release
curl -LO https://github.com/kakserpom/php-sqlx-rs/releases/latest/download/sqlx-macos-arm64.dylib

# Move to PHP extension directory
sudo mv sqlx-macos-arm64.dylib $(php -r "echo ini_get('extension_dir');")/sqlx.so

# Enable the extension
echo "extension=sqlx.so" >> $(php --ini | grep "Loaded Configuration" | cut -d: -f2 | xargs)

Windows

1. Download sqlx-windows-x64.dll from the releases page
2. Copy to your PHP ext directory
3. Add extension=sqlx to your php.ini

Building from Source

Prerequisites

• Rust 1.70 or higher
• PHP 8.1+ development headers
• Clang/LLVM

Build Steps

# Clone the repository
git clone https://github.com/kakserpom/php-sqlx-rs.git
cd php-sqlx-rs

# Build the extension
cd php-sqlx-cdylib
cargo build --release

# Install
sudo cp target/release/libphp_sqlx_cdylib.so $(php -r "echo ini_get('extension_dir');")/sqlx.so
echo "extension=sqlx.so" | sudo tee /etc/php/conf.d/99-sqlx.ini

Verifying Installation

# Check if extension is loaded
php -m | grep sqlx

# Should output:
# sqlx

<?php
// Test the extension
$driver = Sqlx\DriverFactory::make("postgres://localhost/test");
echo "php-sqlx is working!\n";

Docker

FROM php:8.3-cli

# Install build dependencies
RUN apt-get update && apt-get install -y \
    curl \
    clang \
    && rm -rf /var/lib/apt/lists/*

# Install Rust
RUN curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- -y
ENV PATH="/root/.cargo/bin:${PATH}"

# Build php-sqlx
COPY . /app
WORKDIR /app/php-sqlx-cdylib
RUN cargo build --release

# Install extension
RUN cp target/release/libphp_sqlx_cdylib.so $(php -r "echo ini_get('extension_dir');")/sqlx.so \
    && echo "extension=sqlx.so" > /usr/local/etc/php/conf.d/sqlx.ini

Troubleshooting

Extension not loading

Check that the extension file exists and has correct permissions:

ls -la $(php -r "echo ini_get('extension_dir');")/sqlx.so

Symbol errors on load

Make sure you’re using a compatible PHP version. The extension must be compiled for your specific PHP version.

Connection errors

Verify your database server is running and accessible:

# PostgreSQL
pg_isready -h localhost -p 5432

# MySQL
mysqladmin ping -h localhost

# MSSQL
sqlcmd -S localhost -U sa -P password -Q "SELECT 1"

Quick Start

This guide will get you up and running with php-sqlx in minutes.

Creating a Driver

The DriverFactory creates the appropriate driver based on your connection URL:

<?php

use Sqlx\DriverFactory;

// PostgreSQL
$driver = DriverFactory::make("postgres://user:password@localhost:5432/mydb");

// MySQL
$driver = DriverFactory::make("mysql://user:password@localhost:3306/mydb");

// MSSQL
$driver = DriverFactory::make("mssql://user:password@localhost:1433/mydb");

Basic Queries

Fetching a Single Row

// Returns an object (stdClass by default)
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
echo $user->name;

// Returns null if no row found
$user = $driver->queryMaybeRow("SELECT * FROM users WHERE id = ?", [999]);
if ($user === null) {
    echo "User not found";
}

Fetching Multiple Rows

$users = $driver->queryAll("SELECT * FROM users WHERE active = ?", [true]);

foreach ($users as $user) {
    echo "{$user->name}: {$user->email}\n";
}

Fetching a Single Value

$count = $driver->queryValue("SELECT COUNT(*) FROM users");
echo "Total users: $count";

$name = $driver->queryValue(
    "SELECT name FROM users WHERE id = ?",
    [1],
    'name'  // column name or index
);

Fetching a Column

$emails = $driver->queryColumn("SELECT email FROM users WHERE active = ?", [true]);
// Returns: ['alice@example.com', 'bob@example.com', ...]

Inserting Data

Using execute()

$affected = $driver->execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ['Alice', 'alice@example.com']
);
echo "Inserted $affected row(s)";

Using insert()

$driver->insert('users', [
    'name' => 'Bob',
    'email' => 'bob@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

Bulk Insert

$driver->insertMany('users', [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => 'Carol', 'email' => 'carol@example.com'],
]);

Updating Data

$affected = $driver->execute(
    "UPDATE users SET name = ? WHERE id = ?",
    ['Alice Smith', 1]
);

Deleting Data

$affected = $driver->execute(
    "DELETE FROM users WHERE id = ?",
    [1]
);

Using Named Parameters

$user = $driver->queryRow(
    "SELECT * FROM users WHERE name = $name AND status = $status",
    ['name' => 'Alice', 'status' => 'active']
);

Dictionary Queries

Get results as key-value maps:

// Map id => entire row
$usersById = $driver->queryDictionary("SELECT id, * FROM users");
// Result: [1 => {id: 1, name: 'Alice', ...}, 2 => {...}]

// Map id => name only
$namesById = $driver->queryColumnDictionary("SELECT id, name FROM users");
// Result: [1 => 'Alice', 2 => 'Bob', ...]

// Group by a column
$usersByRole = $driver->queryGroupedDictionary("SELECT role, * FROM users");
// Result: ['admin' => [{...}, {...}], 'user' => [{...}]]

Error Handling

use Sqlx\Exceptions\QueryException;
use Sqlx\Exceptions\ConnectionException;

try {
    $result = $driver->queryRow("SELECT * FROM nonexistent");
} catch (QueryException $e) {
    echo "Query failed: " . $e->getMessage();
} catch (ConnectionException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Result Formats

By default, rows are returned as objects. You can get arrays instead:

// Per-query
$user = $driver->queryRowAssoc("SELECT * FROM users WHERE id = ?", [1]);
// Returns: ['id' => 1, 'name' => 'Alice', ...]

// Or configure globally
$driver = DriverFactory::make([
    Sqlx\DriverOptions::OPT_URL => "postgres://localhost/mydb",
    Sqlx\DriverOptions::OPT_ASSOC_ARRAYS => true
]);

What’s Next?

• Connection Strings - Learn about connection URL formats
• Query Methods - Explore all query methods
• Augmented SQL - Learn about conditional blocks and type-safe placeholders

Connection Strings

php-sqlx uses URL-style connection strings to configure database connections.

Basic Format

scheme://[user[:password]@]host[:port]/database[?options]

PostgreSQL

Scheme: postgres://, postgresql://, or pgsql://

// Basic connection
$driver = DriverFactory::make("postgres://localhost/mydb");

// With authentication
$driver = DriverFactory::make("postgres://user:password@localhost/mydb");

// With port
$driver = DriverFactory::make("postgres://user:password@localhost:5432/mydb");

// With SSL
$driver = DriverFactory::make("postgres://user:password@localhost/mydb?sslmode=require");

PostgreSQL Options

MySQL

Scheme: mysql://

// Basic connection
$driver = DriverFactory::make("mysql://localhost/mydb");

// With authentication
$driver = DriverFactory::make("mysql://root:password@localhost/mydb");

// With port
$driver = DriverFactory::make("mysql://root:password@localhost:3306/mydb");

// With SSL
$driver = DriverFactory::make("mysql://user:pass@localhost/mydb?ssl-mode=required");

MySQL Options

Microsoft SQL Server

Scheme: mssql:// or sqlserver://

// Basic connection
$driver = DriverFactory::make("mssql://localhost/mydb");

// With authentication
$driver = DriverFactory::make("mssql://sa:Password123@localhost/mydb");

// With port
$driver = DriverFactory::make("mssql://sa:Password123@localhost:1433/mydb");

// Trust self-signed certificates
$driver = DriverFactory::make("mssql://sa:Password123@localhost/mydb?trust_server_certificate=true");

MSSQL Options

Special Characters

Special characters in usernames or passwords must be URL-encoded:

// Password with @ symbol: "p@ssword" -> "p%40ssword"
$driver = DriverFactory::make("postgres://user:p%40ssword@localhost/mydb");

// Username with special chars
$driver = DriverFactory::make("mysql://admin%40company:password@localhost/mydb");

Common encodings:

• @ → %40
• : → %3A
• / → %2F
• # → %23
• ? → %3F

Using Options Array

For complex configurations, use an options array instead:

use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_MAX_CONNECTIONS => 10,
    DriverOptions::OPT_ASSOC_ARRAYS => true,
    DriverOptions::OPT_IDLE_TIMEOUT => "5m",
]);

See Driver Options for all available options.

Environment Variables

A common pattern is to use environment variables:

$driver = DriverFactory::make(getenv('DATABASE_URL'));

export DATABASE_URL="postgres://user:pass@localhost/mydb"

IPv6 Addresses

Wrap IPv6 addresses in brackets:

$driver = DriverFactory::make("postgres://user:pass@[::1]:5432/mydb");

Unix Sockets

PostgreSQL

$driver = DriverFactory::make("postgres://user@localhost/mydb?host=/var/run/postgresql");

MySQL

$driver = DriverFactory::make("mysql://user@localhost/mydb?socket=/var/run/mysqld/mysqld.sock");

Drivers

php-sqlx provides database-specific driver classes, all sharing a common interface. The DriverFactory creates the appropriate driver based on your connection URL.

Creating Drivers

Using DriverFactory (Recommended)

use Sqlx\DriverFactory;

// Auto-detects database type from URL scheme
$pg = DriverFactory::make("postgres://user:pass@localhost/mydb");
$mysql = DriverFactory::make("mysql://user:pass@localhost/mydb");
$mssql = DriverFactory::make("mssql://user:pass@localhost/mydb");

Direct Instantiation

use Sqlx\PgDriver;
use Sqlx\MySqlDriver;
use Sqlx\MssqlDriver;

$pg = new PgDriver("postgres://localhost/mydb");
$mysql = new MySqlDriver("mysql://localhost/mydb");
$mssql = new MssqlDriver("mssql://localhost/mydb");

With Options Array

use Sqlx\DriverFactory;
use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_MAX_CONNECTIONS => 10,
    DriverOptions::OPT_ASSOC_ARRAYS => true,
]);

Driver Classes

All drivers implement the same interface, so you can write database-agnostic code.

Driver Lifecycle

Closing Connections

Drivers manage a connection pool internally. To release resources explicitly:

$driver->close();

After closing, the driver cannot be used for further queries.

Checking Status

if ($driver->isClosed()) {
    echo "Driver is closed";
}

Configuration Methods

Application Name

Set an identifier visible in database monitoring tools:

$driver->setApplicationName("my-api-server");

This shows up in:

• PostgreSQL: pg_stat_activity.application_name
• MySQL: Session variable @sqlx_application_name
• MSSQL: SESSION_CONTEXT(N'application_name')

Client Info

Set metadata for debugging and monitoring:

$driver->setClientInfo("my-app", [
    'request_id' => $requestId,
    'user_id' => $userId,
]);

Result Format

By default, query results are returned as objects (stdClass). Check or change this:

// Check current setting
if ($driver->assocArrays()) {
    echo "Results are arrays";
} else {
    echo "Results are objects";
}

To get arrays by default, configure when creating the driver:

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_ASSOC_ARRAYS => true,
]);

Or use the *Assoc method variants:

$row = $driver->queryRowAssoc("SELECT * FROM users WHERE id = ?", [1]);
// Returns: ['id' => 1, 'name' => 'Alice', ...]

Read Replicas

Check if read replicas are configured:

if ($driver->hasReadReplicas()) {
    echo "Read replicas are available";
}

See Read Replicas for configuration details.

Thread Safety

Drivers are thread-safe and can be shared across requests in persistent/worker modes (e.g., Swoole, RoadRunner). The internal connection pool handles concurrent access.

Example: Repository Pattern

class UserRepository
{
    public function __construct(
        private \Sqlx\PgDriver $driver
    ) {}

    public function find(int $id): ?object
    {
        return $this->driver->queryMaybeRow(
            "SELECT * FROM users WHERE id = ?",
            [$id]
        );
    }

    public function findByEmail(string $email): ?object
    {
        return $this->driver->queryMaybeRow(
            "SELECT * FROM users WHERE email = ?",
            [$email]
        );
    }

    public function create(array $data): void
    {
        $this->driver->insert('users', $data);
    }

    public function update(int $id, array $data): int
    {
        $sets = [];
        $params = [];
        foreach ($data as $key => $value) {
            $sets[] = "$key = ?";
            $params[] = $value;
        }
        $params[] = $id;

        return $this->driver->execute(
            "UPDATE users SET " . implode(', ', $sets) . " WHERE id = ?",
            $params
        );
    }
}

Query Methods

php-sqlx provides a rich set of query methods for different use cases. Each method is optimized for its specific purpose.

Method Naming Convention

Methods follow a consistent naming pattern:

• Base method: queryRow() - uses driver’s default format
• Assoc variant: queryRowAssoc() - returns associative arrays
• Obj variant: queryRowObj() - returns objects

Single Row Queries

queryRow

Fetch exactly one row. Throws an exception if no rows found.

$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
echo $user->name;

queryMaybeRow

Fetch one row or null if not found.

$user = $driver->queryMaybeRow("SELECT * FROM users WHERE id = ?", [999]);
if ($user === null) {
    echo "Not found";
}

Multiple Row Queries

queryAll

Fetch all matching rows as an array.

$users = $driver->queryAll("SELECT * FROM users WHERE active = ?", [true]);
foreach ($users as $user) {
    echo $user->name . "\n";
}

Returns an empty array if no rows match.

Single Value Queries

queryValue

Fetch a single value from the first row. Throws if no rows.

// First column by default
$count = $driver->queryValue("SELECT COUNT(*) FROM users");

// Specific column by name
$name = $driver->queryValue(
    "SELECT id, name, email FROM users WHERE id = ?",
    [1],
    'name'
);

// Specific column by index
$name = $driver->queryValue(
    "SELECT id, name, email FROM users WHERE id = ?",
    [1],
    1  // second column (0-indexed)
);

queryMaybeValue

Fetch a single value or null if no rows.

$email = $driver->queryMaybeValue(
    "SELECT email FROM users WHERE id = ?",
    [999]
);
// Returns null if user doesn't exist

Column Queries

queryColumn

Fetch a single column from all rows as a flat array.

$emails = $driver->queryColumn("SELECT email FROM users WHERE active = ?", [true]);
// Returns: ['alice@example.com', 'bob@example.com', ...]

// Specific column
$names = $driver->queryColumn(
    "SELECT id, name, email FROM users",
    null,
    'name'
);

Dictionary Queries

Dictionary queries return associative arrays where the first column becomes the key.

queryDictionary

Map the first column to entire rows.

$usersById = $driver->queryDictionary("SELECT id, name, email FROM users");
// Returns: [
//   1 => {id: 1, name: 'Alice', email: 'alice@...'},
//   2 => {id: 2, name: 'Bob', email: 'bob@...'},
// ]

$user = $usersById[1];
echo $user->name;  // Alice

queryColumnDictionary

Map the first column to the second column only.

$emailsById = $driver->queryColumnDictionary("SELECT id, email FROM users");
// Returns: [1 => 'alice@example.com', 2 => 'bob@example.com']

queryGroupedDictionary

Group multiple rows by the first column.

$usersByRole = $driver->queryGroupedDictionary(
    "SELECT role, id, name FROM users ORDER BY role, name"
);
// Returns: [
//   'admin' => [{id: 1, name: 'Alice'}, {id: 3, name: 'Carol'}],
//   'user' => [{id: 2, name: 'Bob'}],
// ]

queryGroupedColumnDictionary

Group second column values by the first column.

$emailsByDepartment = $driver->queryGroupedColumnDictionary(
    "SELECT department, email FROM users"
);
// Returns: [
//   'Engineering' => ['alice@...', 'bob@...'],
//   'Sales' => ['carol@...'],
// ]

Data Modification

execute

Execute INSERT, UPDATE, or DELETE statements. Returns affected row count.

$affected = $driver->execute(
    "UPDATE users SET last_login = NOW() WHERE id = ?",
    [1]
);
echo "Updated $affected rows";

insert

Insert a single row using an associative array.

$driver->insert('users', [
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'created_at' => date('Y-m-d H:i:s'),
]);

insertMany

Bulk insert multiple rows efficiently.

$driver->insertMany('users', [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => 'Carol', 'email' => 'carol@example.com'],
]);

upsert

Insert or update on conflict (PostgreSQL/MySQL).

// PostgreSQL
$driver->upsert(
    'users',
    ['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com'],
    ['id'],           // conflict columns
    ['name', 'email'] // columns to update on conflict
);

Schema Introspection

describeTable

Get column metadata for a table.

$columns = $driver->describeTable('users');
foreach ($columns as $col) {
    echo "{$col['name']}: {$col['type']}";
    if ($col['nullable']) echo " (nullable)";
    echo "\n";
}

Returns an array with:

• name - Column name
• type - Data type (e.g., “varchar(255)”)
• nullable - Whether NULL is allowed
• default - Default value
• ordinal - Column position (1-based)

With schema:

$columns = $driver->describeTable('users', 'public');

Utility Methods

quote

Safely quote a value for SQL.

$quoted = $driver->quote("O'Brien");
// Returns: 'O''Brien'

quoteLike

Quote a string for use in LIKE patterns, escaping % and _.

$pattern = $driver->quoteLike("100%");
$driver->queryAll("SELECT * FROM products WHERE name LIKE ?", ["%$pattern%"]);

quoteIdentifier

Quote a table or column name.

$quoted = $driver->quoteIdentifier("user-data");
// PostgreSQL: "user-data"
// MySQL: `user-data`
// MSSQL: [user-data]

dry

Render a query without executing it. Useful for debugging.

[$sql, $params] = $driver->dry(
    "SELECT * FROM users WHERE id = ? AND status = $status",
    ['id' => 1, 'status' => 'active']
);
echo $sql;    // SELECT * FROM users WHERE id = $1 AND status = $2
print_r($params);  // [1, 'active']

Method Reference Table

Parameters & Placeholders

php-sqlx supports multiple placeholder styles and automatic parameter binding for safe, injection-free queries.

Placeholder Styles

All styles are interchangeable and can be mixed within a query (though mixing is not recommended for clarity).

Positional (?)

The classic PDO style:

$user = $driver->queryRow(
    "SELECT * FROM users WHERE id = ? AND status = ?",
    [1, 'active']
);

Numbered ($1, $2)

PostgreSQL style:

$user = $driver->queryRow(
    "SELECT * FROM users WHERE id = $1 AND status = $2",
    [1, 'active']
);

Named ($name)

Dollar-sign named parameters:

$user = $driver->queryRow(
    "SELECT * FROM users WHERE id = $id AND status = $status",
    ['id' => 1, 'status' => 'active']
);

Named (:name)

Colon-style named parameters:

$user = $driver->queryRow(
    "SELECT * FROM users WHERE id = :id AND status = :status",
    ['id' => 1, 'status' => 'active']
);

Parameter Types

php-sqlx automatically converts PHP types to appropriate database types:

Array Parameters

Arrays are automatically expanded for IN clauses:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[1, 2, 3]]
);
// Becomes: SELECT * FROM users WHERE id IN ($1, $2, $3)

With named parameters:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE status IN ($statuses)",
    ['statuses' => ['active', 'pending']]
);

JSON Parameters

Arrays/objects are automatically serialized to JSON when needed:

$driver->execute(
    "INSERT INTO events (data) VALUES (?)",
    [['event' => 'login', 'ip' => '192.168.1.1']]
);
// The array is JSON-encoded

NULL Handling

Pass null directly:

$driver->execute(
    "UPDATE users SET deleted_at = ? WHERE id = ?",
    [null, 1]
);

Parameter Reuse

Named parameters can be reused:

$results = $driver->queryAll(
    "SELECT * FROM events WHERE date >= $date OR created_at >= $date",
    ['date' => '2024-01-01']
);

Empty Arrays

By default, empty arrays in IN clauses are collapsed to FALSE:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[]]  // empty array
);
// Becomes: SELECT * FROM users WHERE FALSE
// Returns empty result set

This behavior can be disabled via OPT_COLLAPSIBLE_IN.

Type-Safe Placeholders

php-sqlx provides type-safe placeholder variants. See Type-Safe Placeholders for details.

// Unsigned integer only
$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [18]);

// String only
$driver->queryAll("SELECT * FROM users WHERE name = ?s", ['Alice']);

Best Practices

Always Use Parameters

Never interpolate values directly:

// WRONG - SQL injection risk!
$driver->queryRow("SELECT * FROM users WHERE id = $id");

// RIGHT
$driver->queryRow("SELECT * FROM users WHERE id = ?", [$id]);

Use Named Parameters for Clarity

For queries with many parameters:

// Hard to read
$driver->execute(
    "INSERT INTO orders (user_id, product_id, quantity, price, status) VALUES (?, ?, ?, ?, ?)",
    [$userId, $productId, $quantity, $price, 'pending']
);

// Much clearer
$driver->execute(
    "INSERT INTO orders (user_id, product_id, quantity, price, status)
     VALUES ($user_id, $product_id, $quantity, $price, $status)",
    [
        'user_id' => $userId,
        'product_id' => $productId,
        'quantity' => $quantity,
        'price' => $price,
        'status' => 'pending',
    ]
);

Use Appropriate Types

Let the driver handle type conversion:

// The driver handles this correctly
$driver->queryRow("SELECT * FROM users WHERE id = ?", [42]);       // int
$driver->queryRow("SELECT * FROM users WHERE id = ?", ["42"]);     // string converted to int
$driver->queryRow("SELECT * FROM users WHERE active = ?", [true]); // bool

Result Handling

php-sqlx provides flexible options for handling query results, including objects, arrays, and specialized dictionary formats.

Result Formats

Objects (Default)

By default, rows are returned as stdClass objects:

$user = $driver->queryRow("SELECT id, name, email FROM users WHERE id = ?", [1]);

echo $user->id;     // 1
echo $user->name;   // Alice
echo $user->email;  // alice@example.com

Associative Arrays

Use *Assoc method variants:

$user = $driver->queryRowAssoc("SELECT id, name, email FROM users WHERE id = ?", [1]);

echo $user['id'];     // 1
echo $user['name'];   // Alice
echo $user['email'];  // alice@example.com

Or configure globally:

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_ASSOC_ARRAYS => true,
]);

// Now all methods return arrays by default
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
echo $user['name'];  // Works!

Force Object Mode

Use *Obj variants to always get objects, even with OPT_ASSOC_ARRAYS enabled:

$user = $driver->queryRowObj("SELECT * FROM users WHERE id = ?", [1]);
echo $user->name;  // Always an object

Type Conversion

php-sqlx automatically converts database types to appropriate PHP types:

JSON Handling

JSON columns are automatically decoded:

// Database column 'metadata' contains: {"role": "admin", "permissions": ["read", "write"]}
$user = $driver->queryRow("SELECT metadata FROM users WHERE id = ?", [1]);

echo $user->metadata->role;  // admin
print_r($user->metadata->permissions);  // ['read', 'write']

With associative arrays mode:

$user = $driver->queryRowAssoc("SELECT metadata FROM users WHERE id = ?", [1]);
echo $user['metadata']['role'];  // admin

Lazy JSON Decoding

JSON values are decoded lazily on first access, improving performance when you don’t need all JSON fields.

Null Handling

NULL values from the database are returned as PHP null:

$user = $driver->queryRow("SELECT name, deleted_at FROM users WHERE id = ?", [1]);

if ($user->deleted_at === null) {
    echo "User is active";
}

Empty Results

Different methods handle empty results differently:

// Throws SqlxException if no rows
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [999]);

// Returns null if no rows
$user = $driver->queryMaybeRow("SELECT * FROM users WHERE id = ?", [999]);

// Returns empty array if no rows
$users = $driver->queryAll("SELECT * FROM users WHERE id = ?", [999]);

Dictionary Results

queryDictionary

Returns a map where the first column is the key:

$users = $driver->queryDictionary("SELECT id, name, email FROM users");
// [
//   1 => {id: 1, name: 'Alice', email: 'alice@...'},
//   2 => {id: 2, name: 'Bob', email: 'bob@...'},
// ]

// Access by ID
if (isset($users[42])) {
    echo $users[42]->name;
}

queryColumnDictionary

Maps first column to second column:

$names = $driver->queryColumnDictionary("SELECT id, name FROM users");
// [1 => 'Alice', 2 => 'Bob', 3 => 'Carol']

echo $names[1];  // Alice

queryGroupedDictionary

Groups rows by the first column:

$byDepartment = $driver->queryGroupedDictionary(
    "SELECT department, id, name FROM employees"
);
// [
//   'Engineering' => [{id: 1, name: 'Alice'}, {id: 2, name: 'Bob'}],
//   'Sales' => [{id: 3, name: 'Carol'}],
// ]

foreach ($byDepartment['Engineering'] as $employee) {
    echo $employee->name . "\n";
}

queryGroupedColumnDictionary

Groups a single column’s values:

$emailsByDept = $driver->queryGroupedColumnDictionary(
    "SELECT department, email FROM employees"
);
// [
//   'Engineering' => ['alice@...', 'bob@...'],
//   'Sales' => ['carol@...'],
// ]

Column Selection

For single-value and column queries, specify which column to extract:

// By name
$email = $driver->queryValue(
    "SELECT id, name, email FROM users WHERE id = ?",
    [1],
    'email'
);

// By index (0-based)
$email = $driver->queryValue(
    "SELECT id, name, email FROM users WHERE id = ?",
    [1],
    2  // third column
);

Large Result Sets

For very large result sets, consider:

1. Pagination: Use LIMIT/OFFSET or cursor-based pagination
2. Streaming: Not yet supported; use pagination instead
3. Column selection: Only SELECT the columns you need

// Don't do this for large tables
$all = $driver->queryAll("SELECT * FROM huge_table");

// Do this instead
$page = $driver->queryAll(
    "SELECT id, name FROM huge_table ORDER BY id LIMIT ? OFFSET ?",
    [100, 0]
);

Performance Tips

1. Select only needed columns:

   // Slow - fetches all columns
   $user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
   
   // Fast - fetches only what's needed
   $user = $driver->queryRow("SELECT id, name FROM users WHERE id = ?", [1]);
   

2. Use appropriate query methods:

   // Inefficient - fetches entire row for one value
   $row = $driver->queryRow("SELECT COUNT(*) as cnt FROM users");
   $count = $row->cnt;
   
   // Efficient - fetches just the value
   $count = $driver->queryValue("SELECT COUNT(*) FROM users");
   

3. Use dictionaries for lookups:

   // Inefficient - N queries
   foreach ($orderIds as $id) {
       $order = $driver->queryRow("SELECT * FROM orders WHERE id = ?", [$id]);
   }
   
   // Efficient - 1 query
   $orders = $driver->queryDictionary(
       "SELECT id, * FROM orders WHERE id IN (?)",
       [$orderIds]
   );
   foreach ($orderIds as $id) {
       $order = $orders[$id] ?? null;
   }
   

Augmented SQL Overview

php-sqlx extends standard SQL with powerful features that make dynamic queries safer and more ergonomic.

What is Augmented SQL?

Augmented SQL is a superset of SQL that adds:

1. Conditional Blocks - Sections that are included/excluded based on parameter presence
2. Type-Safe Placeholders - Placeholders that validate parameter types
3. Smart IN Clauses - Automatic handling of arrays and empty sets
4. Parameter Flexibility - Mix positional and named parameters freely

These features are processed at the php-sqlx layer before the query reaches your database, so they work with any database backend.

Quick Examples

Conditional Blocks

Only include parts of a query when parameters are provided:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
    {{ AND role = $role }}
    {{ AND created_at >= $since }}
", [
    'status' => 'active',
    // 'role' not provided - that block is omitted
    // 'since' not provided - that block is omitted
]);
// Executed: SELECT * FROM users WHERE 1=1 AND status = $1

Type-Safe Placeholders

Ensure parameters match expected types:

// Only accepts unsigned integers
$users = $driver->queryAll(
    "SELECT * FROM users WHERE age >= ?u",
    [18]  // OK
);

$users = $driver->queryAll(
    "SELECT * FROM users WHERE age >= ?u",
    [-5]  // Throws ParameterException!
);

Smart IN Clauses

Arrays are automatically expanded:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[1, 2, 3]]
);
// Executed: SELECT * FROM users WHERE id IN ($1, $2, $3)

Empty arrays become FALSE (configurable):

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[]]  // empty
);
// Executed: SELECT * FROM users WHERE FALSE

How It Works

1. Parse: php-sqlx parses your SQL into an AST (Abstract Syntax Tree)
2. Transform: Conditional blocks and placeholders are resolved
3. Render: The final SQL is generated for your specific database
4. Cache: The AST is cached for repeated queries

The AST cache means that even complex augmented SQL has minimal overhead after the first execution.

Database Compatibility

Augmented SQL features are translated to native syntax for each database:

When to Use Augmented SQL

Good use cases:

• Dynamic filters based on user input
• Optional search criteria
• Building queries with variable conditions
• Type validation at the query level

Consider alternatives for:

• Very simple, static queries (no benefit)
• Extremely complex dynamic queries (use Query Builder instead)

Next Steps

• Conditional Blocks - Deep dive into {{ }} syntax
• Type-Safe Placeholders - Learn all placeholder types
• IN Clause Handling - Array expansion and empty set handling

Conditional Blocks

Conditional blocks allow parts of your SQL to be dynamically included or excluded based on whether parameters are provided.

Basic Syntax

Wrap optional SQL fragments in {{ }}:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
", ['status' => 'active']);
// Executed: SELECT * FROM users WHERE 1=1 AND status = $1

When the parameter is missing or null, the entire block is omitted:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
", []);
// Executed: SELECT * FROM users WHERE 1=1

Multiple Conditions

Chain multiple conditional blocks:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
    {{ AND role = $role }}
    {{ AND department = $department }}
    {{ AND created_at >= $since }}
    {{ AND created_at <= $until }}
", [
    'status' => 'active',
    'department' => 'Engineering',
]);
// Executed: SELECT * FROM users WHERE 1=1 AND status = $1 AND department = $2

The 1=1 Pattern

The WHERE 1=1 pattern is commonly used with conditional blocks because it:

1. Makes all conditions optional (they all start with AND)
2. Avoids syntax errors when all conditions are omitted
3. Is optimized away by the database query planner

// Without 1=1, you'd need complex logic:
"WHERE {{ status = $status }} {{ AND role = $role }}"  // Broken if status missing!

// With 1=1, it's always valid:
"WHERE 1=1 {{ AND status = $status }} {{ AND role = $role }}"  // Always works

Nested Blocks

Blocks can be nested:

$query = "
    SELECT * FROM orders
    WHERE 1=1
    {{ AND customer_id = $customer_id
       {{ AND status = $status }}
    }}
";

The outer block must be included for inner blocks to be evaluated.

Block with Multiple Parameters

A block can reference multiple parameters:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND created_at BETWEEN $start_date AND $end_date }}
", [
    'start_date' => '2024-01-01',
    'end_date' => '2024-12-31',
]);

Important: All parameters in a block must be provided, or the entire block is omitted.

// Only start_date provided - entire block is omitted
$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND created_at BETWEEN $start_date AND $end_date }}
", [
    'start_date' => '2024-01-01',
    // 'end_date' missing
]);
// Executed: SELECT * FROM users WHERE 1=1

Conditional Joins

Use blocks for optional joins:

$query = "
    SELECT u.*, {{ p.bio, p.avatar }}
    FROM users u
    {{ LEFT JOIN profiles p ON p.user_id = u.id }}
    WHERE u.active = true
    {{ AND p.verified = $verified }}
";

// With profile data
$users = $driver->queryAll($query, ['verified' => true]);

// Without profile join
$users = $driver->queryAll($query, []);

Conditional ORDER BY

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE active = true
    {{ ORDER BY $order_column $order_dir }}
", [
    'order_column' => 'created_at',
    'order_dir' => 'DESC',
]);

Warning: Be careful with dynamic column names. Use the Query Builder’s orderBy() with whitelisting for untrusted input.

NULL vs Missing Parameters

There’s a distinction between null values and missing parameters:

// Parameter is missing - block omitted
$driver->queryAll("SELECT * FROM users {{ WHERE deleted_at = $deleted }}", []);
// Executed: SELECT * FROM users

// Parameter is null - block omitted (null is treated as "not provided")
$driver->queryAll("SELECT * FROM users {{ WHERE deleted_at = $deleted }}", ['deleted' => null]);
// Executed: SELECT * FROM users

// To explicitly check for NULL, use IS NULL:
$driver->queryAll("SELECT * FROM users WHERE deleted_at IS NULL", []);

Nullable Placeholders

For parameters that should be included even when null, use nullable placeholders:

// ?ni = nullable integer
$driver->queryAll("
    SELECT * FROM users
    {{ WHERE manager_id = ?ni }}
", [null]);
// Executed: SELECT * FROM users WHERE manager_id = NULL

See Type-Safe Placeholders for all nullable types.

Positional Parameters in Blocks

Blocks work with positional parameters too:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = ? }}
    {{ AND role = ? }}
", ['active']);  // Only first block included
// Executed: SELECT * FROM users WHERE 1=1 AND status = $1

Best Practices

Use Descriptive Parameter Names

// Clear intent
"{{ AND created_at >= $created_after }}"
"{{ AND price <= $max_price }}"

// Avoid ambiguous names
"{{ AND created_at >= $date }}"  // Which date?

Keep Blocks Focused

// Good - each block is independent
"{{ AND status = $status }}"
"{{ AND role = $role }}"

// Avoid - complex logic in blocks
"{{ AND (status = $status OR (role = $role AND active = true)) }}"

Validate Critical Filters

For queries where certain filters should always be applied:

// Ensure tenant isolation is always applied
if (!isset($params['tenant_id'])) {
    throw new \InvalidArgumentException('tenant_id is required');
}

$driver->queryAll("
    SELECT * FROM data
    WHERE tenant_id = $tenant_id
    {{ AND status = $status }}
", $params);

Type-Safe Placeholders

Type-safe placeholders validate that parameters match expected types before executing queries, catching errors early and preventing data corruption.

Why Type-Safe Placeholders?

Standard placeholders accept any value:

// This silently inserts "abc" as age, potentially causing issues
$driver->execute("UPDATE users SET age = ? WHERE id = ?", ["abc", 1]);

Type-safe placeholders catch this:

// Throws ParameterException: expected integer, got string
$driver->execute("UPDATE users SET age = ?i WHERE id = ?", ["abc", 1]);

Placeholder Types

Integer (?i)

Accepts only integers:

$driver->queryAll("SELECT * FROM users WHERE id = ?i", [42]);      // OK
$driver->queryAll("SELECT * FROM users WHERE id = ?i", ["42"]);    // OK (string "42" converts)
$driver->queryAll("SELECT * FROM users WHERE id = ?i", ["abc"]);   // Throws!
$driver->queryAll("SELECT * FROM users WHERE id = ?i", [3.14]);    // Throws!

Unsigned Integer (?u)

Accepts only non-negative integers:

$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [18]);    // OK
$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [-1]);    // Throws!
$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [0]);     // OK

Decimal (?d)

Accepts integers, floats, or numeric strings:

$driver->queryAll("SELECT * FROM products WHERE price = ?d", [19.99]);   // OK
$driver->queryAll("SELECT * FROM products WHERE price = ?d", ["19.99"]); // OK
$driver->queryAll("SELECT * FROM products WHERE price = ?d", [20]);      // OK
$driver->queryAll("SELECT * FROM products WHERE price = ?d", ["abc"]);   // Throws!

Unsigned Decimal (?ud)

Accepts non-negative decimals:

$driver->queryAll("SELECT * FROM products WHERE price >= ?ud", [0.00]);   // OK
$driver->queryAll("SELECT * FROM products WHERE price >= ?ud", [-0.01]); // Throws!

String (?s)

Accepts only strings:

$driver->queryAll("SELECT * FROM users WHERE name = ?s", ["Alice"]);  // OK
$driver->queryAll("SELECT * FROM users WHERE name = ?s", [123]);      // Throws!

JSON (?j)

Accepts arrays or objects, serializes to JSON:

$driver->execute("INSERT INTO events (data) VALUES (?j)", [
    ['event' => 'click', 'x' => 100, 'y' => 200]
]);  // OK - serialized to JSON

Array Placeholders

Each scalar type has an array variant with a suffix:

// Integer array
$driver->queryAll(
    "SELECT * FROM users WHERE id IN (?ia)",
    [[1, 2, 3]]
);

// String array
$driver->queryAll(
    "SELECT * FROM users WHERE status IN (?sa)",
    [['active', 'pending']]
);

// All elements must match the type
$driver->queryAll(
    "SELECT * FROM users WHERE id IN (?ia)",
    [[1, "two", 3]]  // Throws! "two" is not an integer
);

Nullable Placeholders

Nullable variants accept null in addition to the base type. Prefix with n:

// Allow NULL
$driver->execute(
    "UPDATE users SET manager_id = ?ni WHERE id = ?i",
    [null, 1]
);

// In conditional blocks, nullable allows the block to be included with null
$driver->queryAll("
    SELECT * FROM users
    {{ WHERE manager_id = ?ni }}
", [null]);
// Executed: SELECT * FROM users WHERE manager_id = NULL

Named Type-Safe Placeholders

Type annotations work with named parameters too:

$driver->queryAll(
    "SELECT * FROM users WHERE age >= $min_age:u AND status = $status:s",
    ['min_age' => 18, 'status' => 'active']
);

Syntax: $name:type or :name:type

Type Validation Errors

When validation fails, a ParameterException is thrown with details:

use Sqlx\Exceptions\ParameterException;

try {
    $driver->queryAll("SELECT * FROM users WHERE age = ?u", [-5]);
} catch (ParameterException $e) {
    echo $e->getMessage();
    // "Parameter 1: expected unsigned integer, got -5"
}

Array Element Validation

Array placeholders validate each element:

try {
    $driver->queryAll(
        "SELECT * FROM users WHERE id IN (?ia)",
        [[1, 2, "three", 4]]
    );
} catch (ParameterException $e) {
    echo $e->getMessage();
    // "Parameter 1[2]: expected integer, got string 'three'"
}

Nullable Arrays

Array elements can be nullable:

// Array of nullable integers
$driver->queryAll(
    "SELECT * FROM data WHERE value IN (?nia)",  // ?nia = nullable int array
    [[1, null, 3]]  // OK - null allowed in array
);

Best Practices

Use Type-Safe Placeholders for User Input

// User-provided age should be validated
$age = $_GET['age'];
$driver->queryAll(
    "SELECT * FROM users WHERE age >= ?u",
    [$age]  // Throws if not a valid unsigned integer
);

Match Database Column Types

// If 'price' is DECIMAL(10,2), use ?d
$driver->execute(
    "INSERT INTO products (name, price) VALUES (?s, ?d)",
    [$name, $price]
);

Document Expected Types

/**
 * Find users by criteria
 *
 * @param int|null $minAge Minimum age (unsigned)
 * @param string|null $status Status filter
 */
function findUsers(?int $minAge, ?string $status): array
{
    return $this->driver->queryAll("
        SELECT * FROM users
        WHERE 1=1
        {{ AND age >= ?u }}
        {{ AND status = ?s }}
    ", array_filter([$minAge, $status], fn($v) => $v !== null));
}

Comparison with Untyped Placeholders

Use type-safe placeholders when:

• Accepting user input
• Data integrity is critical
• Working with strict column types

Use standard ? when:

• Performance is critical
• Values are already validated
• Working with dynamic/mixed types

IN Clause Handling

php-sqlx provides smart handling of SQL IN clauses, automatically expanding arrays and handling edge cases like empty sets.

Basic Array Expansion

Pass an array parameter for automatic expansion:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[1, 2, 3]]
);
// Executed: SELECT * FROM users WHERE id IN ($1, $2, $3)
// Parameters: [1, 2, 3]

With named parameters:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN ($ids)",
    ['ids' => [1, 2, 3]]
);

NOT IN Clauses

Works the same way:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id NOT IN (?)",
    [[1, 2, 3]]
);
// Executed: SELECT * FROM users WHERE id NOT IN ($1, $2, $3)

Empty Array Handling

By default, empty arrays are collapsed to FALSE:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [[]]  // empty array
);
// Executed: SELECT * FROM users WHERE FALSE
// Returns: []

For NOT IN, empty arrays become TRUE:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id NOT IN (?)",
    [[]]  // empty array
);
// Executed: SELECT * FROM users WHERE TRUE
// Returns: all users

This behavior is intuitive:

• IN () - “is the value in this empty set?” → always false
• NOT IN () - “is the value not in this empty set?” → always true

Disabling Collapsible IN

If you prefer SQL-standard behavior (which would error on empty IN):

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_COLLAPSIBLE_IN => false,
]);

// Now throws an exception for empty arrays
$driver->queryAll("SELECT * FROM users WHERE id IN (?)", [[]]);
// Throws: ParameterException - empty array not allowed

Type-Safe IN Clauses

Combine array expansion with type checking:

// Integer array
$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?ia)",
    [[1, 2, 3]]
);

// String array
$users = $driver->queryAll(
    "SELECT * FROM users WHERE status IN (?sa)",
    [['active', 'pending']]
);

// Type mismatch throws
$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?ia)",
    [[1, "two", 3]]  // Throws! "two" is not an integer
);

Multiple IN Clauses

Each array parameter is expanded independently:

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?) AND role IN (?)",
    [[1, 2, 3], ['admin', 'moderator']]
);
// Executed: SELECT * FROM users WHERE id IN ($1, $2, $3) AND role IN ($4, $5)

IN with Conditional Blocks

Combine IN clauses with conditional blocks for optional filters:

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND id IN ($ids) }}
    {{ AND status IN ($statuses) }}
", [
    'ids' => [1, 2, 3],
    // 'statuses' not provided - block omitted
]);
// Executed: SELECT * FROM users WHERE 1=1 AND id IN ($1, $2, $3)

Subqueries vs Arrays

For large sets, consider using a subquery instead:

// Array expansion - good for small sets (< 100 items)
$driver->queryAll(
    "SELECT * FROM orders WHERE customer_id IN (?)",
    [$customerIds]
);

// Subquery - better for large sets or dynamic conditions
$driver->queryAll(
    "SELECT * FROM orders WHERE customer_id IN (
        SELECT id FROM customers WHERE region = ?
    )",
    ['west']
);

Performance Considerations

Array Size Limits

Most databases have limits on the number of parameters:

• PostgreSQL: ~32,000 parameters
• MySQL: ~65,000 parameters
• MSSQL: ~2,100 parameters

For very large sets, use:

// Batch processing
$chunks = array_chunk($largeIdArray, 1000);
$results = [];
foreach ($chunks as $chunk) {
    $results = array_merge(
        $results,
        $driver->queryAll("SELECT * FROM users WHERE id IN (?)", [$chunk])
    );
}

// Or use a temporary table / CTE
$driver->execute("CREATE TEMP TABLE temp_ids (id INT)");
$driver->insertMany('temp_ids', array_map(fn($id) => ['id' => $id], $largeIdArray));
$results = $driver->queryAll("SELECT u.* FROM users u JOIN temp_ids t ON u.id = t.id");

Query Plan Caching

Queries with different array sizes generate different SQL, which may affect query plan caching:

// These generate different SQL (different number of placeholders)
$driver->queryAll("SELECT * FROM users WHERE id IN (?)", [[1, 2]]);
// → SELECT * FROM users WHERE id IN ($1, $2)

$driver->queryAll("SELECT * FROM users WHERE id IN (?)", [[1, 2, 3]]);
// → SELECT * FROM users WHERE id IN ($1, $2, $3)

For frequently-called queries with variable array sizes, consider using = ANY() syntax (PostgreSQL) or temporary tables.

Common Patterns

Optional Filter with Default

$statusFilter = $statuses ?? ['active'];  // default to active only

$users = $driver->queryAll(
    "SELECT * FROM users WHERE status IN (?)",
    [$statusFilter]
);

Excluding IDs

$excludeIds = [1, 2, 3];  // IDs to exclude

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id NOT IN (?)",
    [$excludeIds ?: [0]]  // use [0] if empty to avoid matching nothing
);

Combining Arrays

$adminIds = [1, 2];
$moderatorIds = [3, 4, 5];

$users = $driver->queryAll(
    "SELECT * FROM users WHERE id IN (?)",
    [array_merge($adminIds, $moderatorIds)]
);

Query Builder Introduction

The Query Builder provides a fluent, type-safe API for constructing SQL queries programmatically. It’s perfect for dynamic queries where conditions, ordering, and pagination come from user input.

Creating a Builder

// Get a builder from the driver
$builder = $driver->builder();

// Or get a read-optimized builder
$readBuilder = $driver->readBuilder();

Basic Example

$users = $driver->builder()
    ->select(['id', 'name', 'email'])
    ->from('users')
    ->where([['status', '=', 'active']])
    ->orderBy(['created_at' => 'DESC'])
    ->limit(10)
    ->queryAll();

Fluent Interface

All builder methods return $this, allowing method chaining:

$query = $driver->builder()
    ->select('*')
    ->from('orders')
    ->leftJoin('customers', 'customers.id = orders.customer_id')
    ->where([['orders.total', '>', 100]])
    ->orderBy(['orders.created_at' => 'DESC'])
    ->limit(50);

// Execute when ready
$orders = $query->queryAll();

Builder Types

Write Query Builder

For all query types including INSERT, UPDATE, DELETE:

$builder = $driver->builder();

// SELECT
$users = $builder->select('*')->from('users')->queryAll();

// INSERT
$builder->insertInto('users')->set(['name' => 'Alice'])->execute();

// UPDATE
$builder->update('users')->set(['status' => 'inactive'])->where([['id', '=', 1]])->execute();

// DELETE
$builder->deleteFrom('users')->where([['id', '=', 1]])->execute();

Read Query Builder

Optimized for SELECT queries, uses read replicas when available:

$builder = $driver->readBuilder();

$users = $builder
    ->select('*')
    ->from('users')
    ->queryAll();  // May use a read replica

Executing Queries

The builder supports all the same query methods as the driver:

$builder = $driver->builder()->select('*')->from('users');

// Single row
$user = $builder->where([['id', '=', 1]])->queryRow();

// Maybe single row
$user = $builder->where([['id', '=', 999]])->queryMaybeRow();

// All rows
$users = $builder->queryAll();

// Single value
$count = $driver->builder()
    ->select('COUNT(*)')
    ->from('users')
    ->queryValue();

// As dictionary
$usersById = $driver->builder()
    ->select(['id', 'name', 'email'])
    ->from('users')
    ->queryDictionary();

Debugging

Dry Run

See the generated SQL without executing:

[$sql, $params] = $driver->builder()
    ->select('*')
    ->from('users')
    ->where([['status', '=', 'active']])
    ->dry();

echo $sql;
// SELECT * FROM "users" WHERE "status" = $1

print_r($params);
// ['active']

String Conversion

Convert to fully-rendered SQL with inline parameters:

$query = $driver->builder()
    ->select('*')
    ->from('users')
    ->where([['status', '=', 'active']]);

echo (string) $query;
// SELECT * FROM "users" WHERE "status" = 'active'

Warning: The string output is for debugging only. Always use parameterized execution.

Database-Specific Builders

Each database has its own builder class that generates appropriate syntax:

The driver automatically creates the correct builder type.

Builder vs Raw SQL

Use the Builder when:

• Building dynamic queries from user input
• Whitelisting columns for ORDER BY, SELECT
• Creating complex joins programmatically
• Need database-agnostic queries

Use Raw SQL when:

• Query is static/simple
• Using database-specific features
• Performance is critical (builder has slight overhead)
• Query is complex and builder makes it harder to read

// Good use of builder - dynamic filtering
$builder = $driver->builder()->select('*')->from('users');

if ($request->has('status')) {
    $builder->where([['status', '=', $request->get('status')]]);
}
if ($request->has('role')) {
    $builder->where([['role', '=', $request->get('role')]]);
}

// Good use of raw SQL - static query
$stats = $driver->queryRow("
    SELECT
        COUNT(*) as total,
        SUM(CASE WHEN active THEN 1 ELSE 0 END) as active_count,
        AVG(age) as avg_age
    FROM users
    WHERE created_at > NOW() - INTERVAL '30 days'
");

Next Steps

• SELECT Queries - Learn about SELECT, FROM, WHERE
• INSERT, UPDATE, DELETE - Data modification queries
• Joins - All join types
• Clause Helpers - Safe column whitelisting

SELECT Queries

The Query Builder provides a comprehensive API for building SELECT queries.

select()

Specify columns to select:

// Select all columns
$builder->select('*');

// Select specific columns
$builder->select(['id', 'name', 'email']);

// With aliases
$builder->select(['id', 'name AS username', 'email']);

// Raw expressions
$builder->select(['id', 'COUNT(*) AS total']);

from()

Specify the table(s) to query:

// Single table
$builder->from('users');

// With alias
$builder->from('users u');

// Multiple tables (cross join)
$builder->from(['users', 'orders']);

where()

Add WHERE conditions:

Array Syntax

// Simple equality
$builder->where([['status', '=', 'active']]);

// Multiple conditions (AND)
$builder->where([
    ['status', '=', 'active'],
    ['role', '=', 'admin']
]);
// WHERE "status" = $1 AND "role" = $2

Supported Operators

$builder->where([['age', '>=', 18]]);        // Greater than or equal
$builder->where([['age', '<=', 65]]);        // Less than or equal
$builder->where([['age', '>', 21]]);         // Greater than
$builder->where([['age', '<', 30]]);         // Less than
$builder->where([['name', '!=', 'Admin']]);  // Not equal
$builder->where([['name', '<>', 'Admin']]);  // Not equal (alternate)
$builder->where([['name', 'LIKE', '%john%']]);  // LIKE
$builder->where([['name', 'ILIKE', '%john%']]); // Case-insensitive LIKE (PostgreSQL)
$builder->where([['id', 'IN', [1, 2, 3]]]);  // IN array
$builder->where([['id', 'NOT IN', [1, 2, 3]]]);  // NOT IN array
$builder->where([['deleted_at', 'IS NULL']]);   // IS NULL
$builder->where([['deleted_at', 'IS NOT NULL']]);  // IS NOT NULL

OR Conditions

Use OR_() for OR logic:

use function Sqlx\OR_;

$builder->where([
    ['status', '=', 'active'],
    OR_([
        ['role', '=', 'admin'],
        ['role', '=', 'moderator']
    ])
]);
// WHERE "status" = $1 AND ("role" = $2 OR "role" = $3)

String Conditions

For complex conditions, use raw SQL:

$builder->where('created_at > NOW() - INTERVAL \'30 days\'');

// With parameters
$builder->where('created_at > ?', ['2024-01-01']);

Chaining where()

Multiple where() calls are ANDed together:

$builder
    ->where([['status', '=', 'active']])
    ->where([['age', '>=', 18]]);
// WHERE "status" = $1 AND "age" >= $2

orderBy()

Sort results:

// Single column
$builder->orderBy(['created_at' => 'DESC']);

// Multiple columns
$builder->orderBy([
    'status' => 'ASC',
    'created_at' => 'DESC'
]);

// String syntax
$builder->orderBy('created_at DESC');

// Array of strings
$builder->orderBy(['created_at DESC', 'id ASC']);

groupBy()

Group results:

// Single column
$builder->groupBy('status');

// Multiple columns
$builder->groupBy(['status', 'role']);

$driver->builder()
    ->select(['status', 'COUNT(*) AS count'])
    ->from('users')
    ->groupBy('status')
    ->queryAll();

having()

Filter grouped results:

$driver->builder()
    ->select(['status', 'COUNT(*) AS count'])
    ->from('users')
    ->groupBy('status')
    ->having('COUNT(*) > ?', [10])
    ->queryAll();

limit() and offset()

Pagination:

// Limit only
$builder->limit(10);

// Limit with offset
$builder->limit(10, 20);  // 10 rows starting at row 20

// Separate offset
$builder->limit(10)->offset(20);

Subqueries

Use builders as subqueries:

$subquery = $driver->builder()
    ->select('user_id')
    ->from('orders')
    ->where([['total', '>', 1000]]);

$users = $driver->builder()
    ->select('*')
    ->from('users')
    ->where("id IN ({$subquery})")  // Embed subquery
    ->queryAll();

Common Table Expressions (CTEs)

with()

$builder
    ->with('active_users', 'SELECT * FROM users WHERE active = true')
    ->select('*')
    ->from('active_users')
    ->where([['role', '=', 'admin']]);

With parameters:

$builder
    ->with('recent_orders', 'SELECT * FROM orders WHERE created_at > ?', ['2024-01-01'])
    ->select('*')
    ->from('recent_orders');

withRecursive()

$builder
    ->withRecursive(
        'subordinates(id, name, manager_id)',
        "SELECT id, name, manager_id FROM employees WHERE id = ?
         UNION ALL
         SELECT e.id, e.name, e.manager_id FROM employees e
         JOIN subordinates s ON e.manager_id = s.id",
        [1]
    )
    ->select('*')
    ->from('subordinates');

Row Locking

Lock selected rows for update:

forUpdate()

Acquire exclusive locks on selected rows:

$driver->begin(function($driver) {
    $user = $driver->builder()
        ->select('*')
        ->from('users')
        ->where([['id', '=', 1]])
        ->forUpdate()
        ->queryRow();

    // Update with exclusive lock held
    $driver->builder()
        ->update('users')
        ->set(['balance' => $user->balance - 100])
        ->where([['id', '=', 1]])
        ->execute();

    return true;
});

forShare()

Acquire shared locks (allows concurrent reads, blocks writes):

$user = $driver->builder()
    ->select('*')
    ->from('users')
    ->where([['id', '=', 1]])
    ->forShare()
    ->queryRow();

UNION

Combine queries:

$admins = $driver->builder()
    ->select(['id', 'name'])
    ->from('admins');

$users = $driver->builder()
    ->select(['id', 'name'])
    ->from('users')
    ->union($admins)
    ->queryAll();

// UNION ALL (keeps duplicates)
$users = $driver->builder()
    ->select(['id', 'name'])
    ->from('users')
    ->unionAll($admins)
    ->queryAll();

Complete Example

$users = $driver->builder()
    ->select(['u.id', 'u.name', 'u.email', 'COUNT(o.id) AS order_count'])
    ->from('users u')
    ->leftJoin('orders o', 'o.user_id = u.id')
    ->where([
        ['u.status', '=', 'active'],
        ['u.created_at', '>=', '2024-01-01']
    ])
    ->groupBy(['u.id', 'u.name', 'u.email'])
    ->having('COUNT(o.id) > ?', [5])
    ->orderBy(['order_count' => 'DESC'])
    ->limit(10)
    ->queryAll();

INSERT, UPDATE, DELETE

The Query Builder supports data modification queries with the same fluent interface.

INSERT

Basic Insert

$driver->builder()
    ->insertInto('users')
    ->set([
        'name' => 'Alice',
        'email' => 'alice@example.com',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

Insert with RETURNING (PostgreSQL)

$user = $driver->builder()
    ->insertInto('users')
    ->set(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->returning(['id', 'created_at'])
    ->queryRow();

echo "Created user with ID: {$user->id}";

Using values() and valuesMany()

Alternative syntax for inserts:

// Single row with values()
$driver->builder()
    ->insertInto('users')
    ->values(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->execute();

// Multiple rows with valuesMany()
$driver->builder()
    ->insertInto('users')
    ->valuesMany([
        ['name' => 'Alice', 'email' => 'alice@example.com'],
        ['name' => 'Bob', 'email' => 'bob@example.com'],
    ])
    ->execute();

Bulk Insert

For multiple rows, you can also use the driver’s insertMany() method:

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

UPDATE

Basic Update

$affected = $driver->builder()
    ->update('users')
    ->set([
        'status' => 'inactive',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where([['id', '=', 1]])
    ->execute();

echo "Updated $affected rows";

Update with Conditions

$driver->builder()
    ->update('users')
    ->set(['last_login' => date('Y-m-d H:i:s')])
    ->where([
        ['status', '=', 'active'],
        ['email_verified', '=', true]
    ])
    ->execute();

Update with RETURNING (PostgreSQL)

$updated = $driver->builder()
    ->update('users')
    ->set(['status' => 'premium'])
    ->where([['id', '=', 1]])
    ->returning(['id', 'name', 'status'])
    ->queryRow();

Update with Subquery

$driver->builder()
    ->update('orders')
    ->set(['status' => 'archived'])
    ->where("customer_id IN (SELECT id FROM customers WHERE inactive = true)")
    ->execute();

DELETE

Basic Delete

$affected = $driver->builder()
    ->deleteFrom('users')
    ->where([['id', '=', 1]])
    ->execute();

Delete with Conditions

$driver->builder()
    ->deleteFrom('sessions')
    ->where([
        ['expires_at', '<', date('Y-m-d H:i:s')],
    ])
    ->execute();

Delete with USING (PostgreSQL)

$driver->builder()
    ->deleteFrom('order_items')
    ->using('orders')
    ->where('order_items.order_id = orders.id')
    ->where([['orders.status', '=', 'cancelled']])
    ->execute();

Delete with RETURNING (PostgreSQL)

$deleted = $driver->builder()
    ->deleteFrom('users')
    ->where([['status', '=', 'spam']])
    ->returning(['id', 'email'])
    ->queryAll();

foreach ($deleted as $user) {
    echo "Deleted: {$user->email}\n";
}

UPSERT (INSERT … ON CONFLICT)

PostgreSQL

$driver->builder()
    ->insertInto('users')
    ->set([
        'email' => 'alice@example.com',
        'name' => 'Alice',
        'login_count' => 1
    ])
    ->onConflict('email', [
        'name' => 'Alice',
        'login_count' => 'login_count + 1'  // Raw SQL expression
    ])
    ->execute();

Multiple conflict columns:

$driver->builder()
    ->insertInto('metrics')
    ->set([
        'user_id' => 1,
        'date' => '2024-01-01',
        'views' => 1
    ])
    ->onConflict(['user_id', 'date'], [
        'views' => 'metrics.views + 1'
    ])
    ->execute();

MySQL

$driver->builder()
    ->insertInto('users')
    ->set([
        'email' => 'alice@example.com',
        'name' => 'Alice',
        'login_count' => 1
    ])
    ->onDuplicateKeyUpdate([
        'name' => 'Alice',
        'login_count' => 'login_count + 1'
    ])
    ->execute();

REPLACE INTO (MySQL)

MySQL’s REPLACE INTO deletes the existing row and inserts a new one:

$driver->builder()
    ->replaceInto('users')
    ->set([
        'id' => 1,
        'name' => 'Alice',
        'email' => 'alice@example.com'
    ])
    ->execute();

Note: This is MySQL-specific. For PostgreSQL, use INSERT ... ON CONFLICT.

TRUNCATE

Remove all rows from a table efficiently:

$driver->builder()
    ->truncateTable('logs')
    ->execute();

Warning: TRUNCATE cannot be rolled back in some databases and resets auto-increment counters.

Safety: Always Use WHERE

Warning: UPDATE and DELETE without WHERE affect all rows!

// DANGEROUS - updates ALL users!
$driver->builder()
    ->update('users')
    ->set(['status' => 'inactive'])
    ->execute();  // No WHERE clause!

// SAFE - updates specific user
$driver->builder()
    ->update('users')
    ->set(['status' => 'inactive'])
    ->where([['id', '=', 1]])
    ->execute();

Consider adding a safeguard in your code:

function safeUpdate(Builder $builder): int
{
    $sql = (string) $builder;
    if (stripos($sql, 'WHERE') === false) {
        throw new \RuntimeException('UPDATE without WHERE clause is not allowed');
    }
    return $builder->execute();
}

Transactions

Wrap multiple mutations in a transaction:

$driver->begin(function($driver) {
    $driver->builder()
        ->insertInto('orders')
        ->set(['user_id' => 1, 'total' => 100])
        ->execute();

    $driver->builder()
        ->update('inventory')
        ->set(['quantity' => 'quantity - 1'])
        ->where([['product_id', '=', 42]])
        ->execute();

    return true;  // Commit
});

See Transactions for more details.

Return Values

Joins

The Query Builder supports all standard SQL join types.

Join Types

INNER JOIN

Returns rows that have matching values in both tables:

$orders = $driver->builder()
    ->select(['orders.*', 'customers.name AS customer_name'])
    ->from('orders')
    ->innerJoin('customers', 'customers.id = orders.customer_id')
    ->queryAll();

// Shorthand alias
$builder->join('customers', 'customers.id = orders.customer_id');

LEFT JOIN

Returns all rows from the left table and matched rows from the right table:

$users = $driver->builder()
    ->select(['users.*', 'profiles.bio'])
    ->from('users')
    ->leftJoin('profiles', 'profiles.user_id = users.id')
    ->queryAll();
// Users without profiles will have NULL bio

RIGHT JOIN

Returns all rows from the right table and matched rows from the left table:

$departments = $driver->builder()
    ->select(['departments.*', 'employees.name'])
    ->from('employees')
    ->rightJoin('departments', 'departments.id = employees.department_id')
    ->queryAll();
// Departments without employees will be included

FULL OUTER JOIN

Returns all rows when there’s a match in either table:

$all = $driver->builder()
    ->select(['a.*', 'b.*'])
    ->from('table_a a')
    ->fullJoin('table_b b', 'a.key = b.key')
    ->queryAll();

CROSS JOIN

Returns the Cartesian product of both tables:

$combinations = $driver->builder()
    ->select(['colors.name AS color', 'sizes.name AS size'])
    ->from('colors')
    ->crossJoin('sizes')
    ->queryAll();
// Every color paired with every size

NATURAL JOIN

Joins on columns with matching names:

$result = $driver->builder()
    ->select('*')
    ->from('orders')
    ->naturalJoin('customers')
    ->queryAll();
// Automatically joins on columns with the same name

Join Conditions

Simple Equality

$builder->leftJoin('orders', 'orders.customer_id = customers.id');

Multiple Conditions

$builder->leftJoin(
    'orders',
    'orders.customer_id = customers.id AND orders.status = \'completed\''
);

With Parameters

$builder->leftJoin(
    'orders',
    'orders.customer_id = customers.id AND orders.created_at > ?',
    ['2024-01-01']
);

Multiple Joins

Chain multiple joins:

$orders = $driver->builder()
    ->select([
        'orders.id',
        'customers.name AS customer',
        'products.name AS product',
        'order_items.quantity'
    ])
    ->from('orders')
    ->innerJoin('customers', 'customers.id = orders.customer_id')
    ->innerJoin('order_items', 'order_items.order_id = orders.id')
    ->innerJoin('products', 'products.id = order_items.product_id')
    ->queryAll();

Table Aliases

Use aliases for self-joins or clarity:

// Self-join: employees with their managers
$employees = $driver->builder()
    ->select(['e.name AS employee', 'm.name AS manager'])
    ->from('employees e')
    ->leftJoin('employees m', 'm.id = e.manager_id')
    ->queryAll();

Subquery Joins

Join with a subquery:

$topCustomers = $driver->builder()
    ->select(['customer_id', 'SUM(total) AS total_spent'])
    ->from('orders')
    ->groupBy('customer_id')
    ->having('SUM(total) > 1000');

$result = $driver->builder()
    ->select(['c.*', 't.total_spent'])
    ->from('customers c')
    ->innerJoin("({$topCustomers}) t", 't.customer_id = c.id')
    ->queryAll();

Conditional Joins

Include joins conditionally:

$builder = $driver->builder()
    ->select(['users.*'])
    ->from('users');

if ($includeProfile) {
    $builder
        ->select(['profiles.bio', 'profiles.avatar'])
        ->leftJoin('profiles', 'profiles.user_id = users.id');
}

if ($includeStats) {
    $builder
        ->select(['COUNT(orders.id) AS order_count'])
        ->leftJoin('orders', 'orders.user_id = users.id')
        ->groupBy('users.id');
}

$users = $builder->queryAll();

Join Performance Tips

Index Join Columns

Ensure columns used in join conditions are indexed:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Limit Joined Data

Filter before joining when possible:

// Less efficient - joins all, then filters
$builder
    ->from('orders')
    ->innerJoin('customers', 'customers.id = orders.customer_id')
    ->where([['orders.status', '=', 'pending']]);

// More efficient - filter subquery, then join
$pendingOrders = $driver->builder()
    ->select('*')
    ->from('orders')
    ->where([['status', '=', 'pending']]);

$builder
    ->from("({$pendingOrders}) o")
    ->innerJoin('customers', 'customers.id = o.customer_id');

Select Only Needed Columns

// Slow - selects all columns from all tables
$builder->select('*')->from('a')->join('b', '...')->join('c', '...');

// Fast - select only what you need
$builder
    ->select(['a.id', 'a.name', 'b.status', 'c.total'])
    ->from('a')
    ->join('b', '...')
    ->join('c', '...');

Clause Helpers

Clause helpers provide safe, whitelist-based handling of user input for SELECT, ORDER BY, GROUP BY, and pagination.

Why Clause Helpers?

User input in ORDER BY clauses can be dangerous:

// DANGEROUS - SQL injection possible!
$order = $_GET['sort'];  // Could be "name; DROP TABLE users;--"
$builder->orderBy($order);

Clause helpers validate against a whitelist:

// SAFE - only whitelisted columns allowed
$byClause = new ByClause(['name', 'email', 'created_at']);
$builder->orderBy($byClause->input($_GET['sort']));

SelectClause

Control which columns can be selected:

use Sqlx\SelectClause;

// Define allowed columns
$select = new SelectClause(['id', 'name', 'email', 'status', 'created_at']);

// User requests specific columns
$userColumns = ['name', 'email', 'invalid_column'];

$users = $driver->builder()
    ->select($select->input($userColumns))  // Only 'name' and 'email' are used
    ->from('users')
    ->queryAll();

Dynamic Column Selection

$select = new SelectClause(['id', 'name', 'email', 'bio', 'avatar']);

// Basic fields only
$basic = $select->input(['id', 'name']);

// Full profile
$full = $select->input(['id', 'name', 'email', 'bio', 'avatar']);

$users = $driver->builder()
    ->select($includeProfile ? $full : $basic)
    ->from('users')
    ->queryAll();

ByClause

For ORDER BY and GROUP BY with whitelisting:

use Sqlx\ByClause;

// Define allowed columns
$orderBy = new ByClause(['name', 'email', 'created_at', 'status']);

// User input: ['name' => 'ASC', 'created_at' => 'DESC']
$userSort = $_GET['sort'] ?? ['created_at' => 'DESC'];

$users = $driver->builder()
    ->select('*')
    ->from('users')
    ->orderBy($orderBy->input($userSort))
    ->queryAll();

With Default Order

$orderBy = new ByClause(['name', 'email', 'created_at']);

// If user doesn't specify, use default
$sort = !empty($_GET['sort'])
    ? $orderBy->input($_GET['sort'])
    : $orderBy->input(['created_at' => 'DESC']);

For GROUP BY

$groupBy = new ByClause(['status', 'role', 'department']);

$stats = $driver->builder()
    ->select(['status', 'COUNT(*) AS count'])
    ->from('users')
    ->groupBy($groupBy->input($_GET['group']))
    ->queryAll();

PaginateClause

Safe pagination with configurable limits:

use Sqlx\PaginateClause;

$paginate = new PaginateClause();
$paginate
    ->perPage(20)      // Default items per page
    ->minPerPage(1)    // Minimum allowed
    ->maxPerPage(100); // Maximum allowed

// User requests page 3 with 50 items
$page = (int) ($_GET['page'] ?? 1);
$perPage = (int) ($_GET['per_page'] ?? 20);

$users = $driver->builder()
    ->select('*')
    ->from('users')
    ->paginate($paginate($page, $perPage))
    ->queryAll();

Pagination Values

The clause calculates LIMIT and OFFSET:

$paginate = new PaginateClause();
$paginate->perPage(10);

$result = $paginate(1, null);  // Page 1: LIMIT 10 OFFSET 0
$result = $paginate(2, null);  // Page 2: LIMIT 10 OFFSET 10
$result = $paginate(3, 25);    // Page 3 with 25/page: LIMIT 25 OFFSET 50

Enforcing Limits

$paginate = new PaginateClause();
$paginate->maxPerPage(50);

// User tries to request 1000 items
$result = $paginate(1, 1000);  // Actually uses LIMIT 50

Combining Helpers

Use all helpers together:

use Sqlx\SelectClause;
use Sqlx\ByClause;
use Sqlx\PaginateClause;

$select = new SelectClause(['id', 'name', 'email', 'status', 'created_at']);
$orderBy = new ByClause(['name', 'email', 'created_at', 'status']);
$paginate = (new PaginateClause())->perPage(20)->maxPerPage(100);

$columns = $_GET['fields'] ?? ['id', 'name', 'email'];
$sort = $_GET['sort'] ?? ['created_at' => 'DESC'];
$page = (int) ($_GET['page'] ?? 1);
$perPage = (int) ($_GET['per_page'] ?? 20);

$users = $driver->builder()
    ->select($select->input($columns))
    ->from('users')
    ->orderBy($orderBy->input($sort))
    ->paginate($paginate($page, $perPage))
    ->queryAll();

API-Style Response

function listUsers(array $params): array
{
    $select = new SelectClause(['id', 'name', 'email', 'status']);
    $orderBy = new ByClause(['name', 'email', 'created_at']);
    $paginate = (new PaginateClause())->perPage(20)->maxPerPage(100);

    $page = (int) ($params['page'] ?? 1);
    $perPage = (int) ($params['per_page'] ?? 20);

    // Get total count
    $total = $driver->queryValue("SELECT COUNT(*) FROM users");

    // Get paginated results
    $users = $driver->builder()
        ->select($select->input($params['fields'] ?? ['id', 'name']))
        ->from('users')
        ->orderBy($orderBy->input($params['sort'] ?? ['id' => 'ASC']))
        ->paginate($paginate($page, $perPage))
        ->queryAll();

    return [
        'data' => $users,
        'meta' => [
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => ceil($total / $perPage),
        ]
    ];
}

Invalid Input Handling

By default, invalid columns are silently ignored:

$select = new SelectClause(['id', 'name']);
$result = $select->input(['id', 'password', 'secret']);
// Only 'id' is included, 'password' and 'secret' are ignored

If you need stricter validation, check before using:

$allowed = ['id', 'name', 'email'];
$requested = $_GET['fields'];

$invalid = array_diff($requested, $allowed);
if (!empty($invalid)) {
    throw new \InvalidArgumentException(
        'Invalid fields: ' . implode(', ', $invalid)
    );
}

Transactions

Transactions ensure that a group of database operations either all succeed or all fail together.

Callback Style (Recommended)

The safest way to use transactions:

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders (user_id, total) VALUES (?, ?)", [1, 100]);
    $driver->execute("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = ?", [42]);

    return true;  // Commit the transaction
});

Automatic Rollback on Exception

If an exception is thrown, the transaction is rolled back:

try {
    $driver->begin(function($driver) {
        $driver->execute("INSERT INTO orders ...", [...]);

        if ($insufficientInventory) {
            throw new \Exception("Not enough inventory");
        }

        $driver->execute("UPDATE inventory ...", [...]);
        return true;
    });
} catch (\Exception $e) {
    // Transaction was automatically rolled back
    echo "Order failed: " . $e->getMessage();
}

Explicit Rollback

Return false or don’t return to rollback:

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders ...", [...]);

    if ($someCondition) {
        return false;  // Rollback
    }

    return true;  // Commit
});

Imperative Style

For more control, use imperative transaction methods:

$driver->begin();

try {
    $driver->execute("INSERT INTO orders ...", [...]);
    $driver->execute("UPDATE inventory ...", [...]);

    $driver->commit();
} catch (\Exception $e) {
    $driver->rollback();
    throw $e;
}

Savepoints

Savepoints allow partial rollbacks within a transaction:

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders ...", [...]);

    $driver->savepoint('before_notifications');

    try {
        $driver->execute("INSERT INTO notifications ...", [...]);
    } catch (\Exception $e) {
        // Rollback just the notification, keep the order
        $driver->rollbackToSavepoint('before_notifications');
    }

    return true;  // Commit the order
});

Releasing Savepoints

Free savepoint resources when no longer needed:

$driver->begin(function($driver) {
    $driver->savepoint('checkpoint1');

    // ... operations ...

    $driver->releaseSavepoint('checkpoint1');  // Free resources

    return true;
});

Nested Transactions

php-sqlx uses savepoints for nested transactions:

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO users ...", [...]);

    // Inner "transaction" uses a savepoint
    $driver->begin(function($driver) {
        $driver->execute("INSERT INTO profiles ...", [...]);
        return true;
    });

    return true;
});

Transaction Isolation Levels

Set isolation level before starting the transaction:

// PostgreSQL
$driver->execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
$driver->begin(function($driver) {
    // Operations run with SERIALIZABLE isolation
    return true;
});

// MySQL
$driver->execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
$driver->begin(function($driver) {
    return true;
});

Read-Only Transactions

For read-only operations (enables optimizations):

// PostgreSQL
$driver->execute("SET TRANSACTION READ ONLY");
$driver->begin(function($driver) {
    $data = $driver->queryAll("SELECT * FROM reports WHERE ...");
    return true;
});

Connection Pinning

Transactions automatically pin to a single connection. For other scenarios requiring connection affinity (like temporary tables), use withConnection():

$driver->withConnection(function($driver) {
    $driver->execute("CREATE TEMP TABLE temp_data (id INT)");
    $driver->execute("INSERT INTO temp_data VALUES (1), (2), (3)");

    $result = $driver->queryAll("SELECT * FROM temp_data");
    // Temp table exists for this callback's duration

    return $result;
});

Error Handling

TransactionException

Thrown for transaction-specific errors:

use Sqlx\Exceptions\TransactionException;

try {
    $driver->begin(function($driver) {
        // ...
    });
} catch (TransactionException $e) {
    // Transaction failed (deadlock, serialization failure, etc.)
    echo "Transaction error: " . $e->getMessage();
}

Retrying on Transient Failures

Some transaction failures are transient (deadlocks, serialization failures):

$maxRetries = 3;
$attempt = 0;

while ($attempt < $maxRetries) {
    try {
        $driver->begin(function($driver) {
            // ... operations ...
            return true;
        });
        break;  // Success
    } catch (TransactionException $e) {
        if ($e->isTransient() && $attempt < $maxRetries - 1) {
            $attempt++;
            usleep(100000 * $attempt);  // Exponential backoff
            continue;
        }
        throw $e;
    }
}

Best Practices

Keep Transactions Short

// GOOD - Minimal time in transaction
$data = prepareData();  // Outside transaction
$driver->begin(function($driver) use ($data) {
    $driver->execute("INSERT ...", $data);
    return true;
});

// BAD - Long-running operations in transaction
$driver->begin(function($driver) {
    $data = fetchFromExternalApi();  // Slow!
    $driver->execute("INSERT ...", $data);
    return true;
});

Avoid User Interaction

Never wait for user input inside a transaction:

// BAD - Holds transaction open while waiting
$driver->begin(function($driver) {
    $driver->execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $confirm = readline("Confirm? ");  // Don't do this!
    if ($confirm === 'yes') {
        return true;
    }
    return false;
});

Handle Deadlocks

Implement retry logic for concurrent write scenarios:

function transferMoney(int $from, int $to, float $amount): void
{
    $maxRetries = 3;

    for ($i = 0; $i < $maxRetries; $i++) {
        try {
            $this->driver->begin(function($driver) use ($from, $to, $amount) {
                $driver->execute(
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?",
                    [$amount, $from]
                );
                $driver->execute(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?",
                    [$amount, $to]
                );
                return true;
            });
            return;
        } catch (TransactionException $e) {
            if (!$e->isTransient() || $i === $maxRetries - 1) {
                throw $e;
            }
        }
    }
}

Prepared Queries

Prepared queries (prepared statements) are pre-compiled queries that can be executed multiple times with different parameters efficiently.

Creating Prepared Queries

$stmt = $driver->prepare("SELECT * FROM users WHERE status = ?");

// Execute multiple times
$activeUsers = $stmt->queryAll(['active']);
$pendingUsers = $stmt->queryAll(['pending']);
$inactiveUsers = $stmt->queryAll(['inactive']);

Benefits

1. Performance: Query is parsed and planned once, executed many times
2. Security: Parameters are always properly escaped
3. Clarity: Separates query structure from data

Query Methods

Prepared queries support all the same methods as drivers:

$stmt = $driver->prepare("SELECT * FROM users WHERE id = ?");

// Single row
$user = $stmt->queryRow([1]);

// Maybe row
$user = $stmt->queryMaybeRow([999]);

// All rows (with different query)
$stmt = $driver->prepare("SELECT * FROM users WHERE status = ?");
$users = $stmt->queryAll(['active']);

// Value
$stmt = $driver->prepare("SELECT COUNT(*) FROM users WHERE status = ?");
$count = $stmt->queryValue(['active']);

// Column
$stmt = $driver->prepare("SELECT email FROM users WHERE status = ?");
$emails = $stmt->queryColumn(['active']);

Dictionary Methods

$stmt = $driver->prepare("SELECT id, name, email FROM users WHERE status = ?");

$usersById = $stmt->queryDictionary(['active']);
$namesById = $stmt->queryColumnDictionary(['active']);
$usersByRole = $stmt->queryGroupedDictionary(['active']);

Execute (for modifications)

$stmt = $driver->prepare("UPDATE users SET last_login = NOW() WHERE id = ?");

$stmt->execute([1]);
$stmt->execute([2]);
$stmt->execute([3]);

Result Formats

Like driver methods, prepared queries support format variants:

$stmt = $driver->prepare("SELECT * FROM users WHERE id = ?");

// Object (default)
$user = $stmt->queryRow([1]);
echo $user->name;

// Associative array
$user = $stmt->queryRowAssoc([1]);
echo $user['name'];

// Force object
$user = $stmt->queryRowObj([1]);

Parameterized Queries vs Prepared Queries

Regular parameterized query:

// Parsed and planned each time
$driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
$driver->queryRow("SELECT * FROM users WHERE id = ?", [2]);

Prepared query:

// Parsed once, executed multiple times
$stmt = $driver->prepare("SELECT * FROM users WHERE id = ?");
$stmt->queryRow([1]);  // Uses cached plan
$stmt->queryRow([2]);  // Uses cached plan

When to Use Prepared Queries

Good Use Cases

Batch operations:

$stmt = $driver->prepare("INSERT INTO logs (user_id, action, timestamp) VALUES (?, ?, ?)");

foreach ($logEntries as $entry) {
    $stmt->execute([$entry['user_id'], $entry['action'], $entry['timestamp']]);
}

Repeated queries in a loop:

$stmt = $driver->prepare("SELECT * FROM products WHERE category_id = ?");

foreach ($categoryIds as $id) {
    $products[$id] = $stmt->queryAll([$id]);
}

High-frequency queries:

// In a long-running worker
$getUserStmt = $driver->prepare("SELECT * FROM users WHERE id = ?");
$getOrdersStmt = $driver->prepare("SELECT * FROM orders WHERE user_id = ?");

while ($job = $queue->pop()) {
    $user = $getUserStmt->queryRow([$job->userId]);
    $orders = $getOrdersStmt->queryAll([$job->userId]);
    // Process...
}

Less Beneficial Cases

One-time queries:

// No benefit - only executed once
$stmt = $driver->prepare("SELECT * FROM users WHERE id = ?");
$user = $stmt->queryRow([1]);

// Just use driver method directly
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);

Dynamic queries:

// Prepared statements can't have dynamic structure
$columns = ['id', 'name', 'email'];
$stmt = $driver->prepare("SELECT " . implode(', ', $columns) . " FROM users");
// This defeats the purpose - query text changes each time

Named Parameters

$stmt = $driver->prepare(
    "SELECT * FROM users WHERE status = $status AND role = $role"
);

$admins = $stmt->queryAll(['status' => 'active', 'role' => 'admin']);
$users = $stmt->queryAll(['status' => 'active', 'role' => 'user']);

Performance Considerations

Statement Caching

php-sqlx caches prepared statements internally. The same query string returns the same cached statement:

// These use the same cached statement
$stmt1 = $driver->prepare("SELECT * FROM users WHERE id = ?");
$stmt2 = $driver->prepare("SELECT * FROM users WHERE id = ?");
// $stmt1 and $stmt2 share the same underlying prepared statement

Memory Usage

Prepared statements consume server memory. For many different queries, unprepared execution may be more efficient:

// Many unique queries - prepared statements may not help
foreach ($queries as $query) {
    $driver->prepare($query)->execute();  // Each creates new statement
}

// Better - just execute directly
foreach ($queries as $query) {
    $driver->execute($query);
}

Database-Specific Notes

PostgreSQL

PostgreSQL caches execution plans after multiple executions. First few runs may be slower as plans are optimized.

MySQL

MySQL prepared statements are connection-specific. In pooled environments, statements may be re-prepared on different connections.

MSSQL

MSSQL uses sp_executesql for parameterized queries, which provides plan caching benefits similar to prepared statements.

Connection Pooling

php-sqlx includes a built-in connection pool that manages database connections efficiently.

How Pooling Works

Instead of opening a new connection for each query:

1. Connections are created and kept in a pool
2. Queries acquire a connection from the pool
3. After the query, the connection returns to the pool
4. Connections are reused for subsequent queries

This reduces connection overhead and improves performance.

Default Configuration

By default, php-sqlx creates a minimal pool:

• Max connections: 2
• Min connections: 0
• Idle timeout: None
• Max lifetime: None

Configuring the Pool

use Sqlx\DriverFactory;
use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",

    // Pool size
    DriverOptions::OPT_MAX_CONNECTIONS => 10,  // Maximum connections
    DriverOptions::OPT_MIN_CONNECTIONS => 2,   // Minimum idle connections

    // Timeouts
    DriverOptions::OPT_IDLE_TIMEOUT => "5m",      // Close idle connections after 5 minutes
    DriverOptions::OPT_MAX_LIFETIME => "30m",     // Replace connections after 30 minutes
    DriverOptions::OPT_ACQUIRE_TIMEOUT => "30s",  // Wait max 30s to acquire connection

    // Health checks
    DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,  // Validate connections before use
]);

Pool Size Guidelines

Web Applications (PHP-FPM)

Each PHP-FPM worker is independent, so pool sizes should be small:

DriverOptions::OPT_MAX_CONNECTIONS => 2,
DriverOptions::OPT_MIN_CONNECTIONS => 1,

With 50 PHP-FPM workers: 50 × 2 = 100 max connections

Long-Running Workers (Swoole, RoadRunner)

Workers persist and can share pools:

DriverOptions::OPT_MAX_CONNECTIONS => 10,
DriverOptions::OPT_MIN_CONNECTIONS => 2,

CLI Scripts

Usually need only one connection:

DriverOptions::OPT_MAX_CONNECTIONS => 1,
DriverOptions::OPT_MIN_CONNECTIONS => 0,

Timeouts

Idle Timeout

Close connections that have been idle too long:

DriverOptions::OPT_IDLE_TIMEOUT => "5m",  // 5 minutes

Accepts duration strings: "30s", "5m", "1h", or seconds as integer.

Max Lifetime

Replace connections after a maximum age (prevents stale connections):

DriverOptions::OPT_MAX_LIFETIME => "30m",  // 30 minutes

Acquire Timeout

Maximum time to wait for a connection from the pool:

DriverOptions::OPT_ACQUIRE_TIMEOUT => "30s",

If the timeout is exceeded, a PoolExhaustedException is thrown.

Health Checks

Test Before Acquire

Validate connections before returning from the pool:

DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,

This adds a small ping query before each use, but ensures connections are valid.

Persistent Pools

For PHP-FPM, you can share pools across requests using a persistent name:

DriverOptions::OPT_PERSISTENT_NAME => "myapp_db",

Connections persist across requests, reducing connection overhead.

Connection States

Connections cycle through states:

1. Idle: In pool, available for use
2. In Use: Acquired by a query
3. Closed: Removed from pool (error, timeout, max lifetime)

Pool Exhaustion

When all connections are in use and the pool is at max size:

use Sqlx\Exceptions\PoolExhaustedException;

try {
    $result = $driver->queryAll("SELECT * FROM large_table");
} catch (PoolExhaustedException $e) {
    // All connections busy, acquire timeout exceeded
    echo "Database pool exhausted";
}

Preventing Exhaustion

1. Increase pool size (if database can handle more connections)
2. Increase acquire timeout (if queries are legitimately slow)
3. Optimize slow queries (reduce connection hold time)
4. Use read replicas (distribute load)

Monitoring Pool Status

Currently, pool statistics are not exposed to PHP. Monitor at the database level:

-- PostgreSQL: Active connections
SELECT count(*) FROM pg_stat_activity WHERE datname = 'mydb';

-- MySQL: Active connections
SHOW STATUS LIKE 'Threads_connected';

-- MSSQL: Active connections
SELECT COUNT(*) FROM sys.dm_exec_connections;

Best Practices

Right-Size Your Pool

Too small: Connections wait, requests slow down Too large: Wastes database resources, may hit database limits

// Start conservative
DriverOptions::OPT_MAX_CONNECTIONS => 5,

// Monitor and adjust based on:
// - Average query duration
// - Concurrent request count
// - Database connection limits

Use Connection Lifetimes

Prevent stale connections and connection leaks:

DriverOptions::OPT_MAX_LIFETIME => "30m",
DriverOptions::OPT_IDLE_TIMEOUT => "5m",

Enable Health Checks for Critical Apps

DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,

Small overhead, but catches dead connections before they cause query failures.

Don’t Hold Connections

Release connections as soon as possible:

// BAD - Holds connection during sleep
$result = $driver->queryAll("SELECT * FROM data");
sleep(10);  // Connection still held
processData($result);

// GOOD - Connection released before sleep
$result = $driver->queryAll("SELECT * FROM data");
// Connection returned to pool
sleep(10);
processData($result);

Read Replicas

php-sqlx supports read replicas for distributing read queries across multiple database servers.

Why Use Read Replicas?

1. Scale reads: Distribute SELECT queries across multiple servers
2. Reduce primary load: Keep the primary database free for writes
3. Improve availability: If one replica is down, others can serve reads

Configuration

Basic Setup

use Sqlx\DriverFactory;
use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    // Primary (for writes)
    DriverOptions::OPT_URL => "postgres://user:pass@primary.db.example.com/mydb",

    // Read replicas
    DriverOptions::OPT_READ_REPLICAS => [
        "postgres://user:pass@replica1.db.example.com/mydb",
        "postgres://user:pass@replica2.db.example.com/mydb",
    ],
]);

Weighted Replicas

Distribute load unevenly (e.g., for different server capacities):

DriverOptions::OPT_READ_REPLICAS => [
    ['url' => "postgres://replica1.db.example.com/mydb", 'weight' => 2],
    ['url' => "postgres://replica2.db.example.com/mydb", 'weight' => 1],
],
// replica1 gets ~67% of reads, replica2 gets ~33%

Mixed Format

DriverOptions::OPT_READ_REPLICAS => [
    "postgres://replica1.db.example.com/mydb",  // weight = 1 (default)
    ['url' => "postgres://replica2.db.example.com/mydb", 'weight' => 3],
],

How Routing Works

Automatic Routing

By default, reads go to replicas and writes go to the primary:

// Routed to a replica
$users = $driver->queryAll("SELECT * FROM users");

// Routed to primary
$driver->execute("INSERT INTO users ...", [...]);

Using the Read Builder

Explicitly use replicas:

$builder = $driver->readBuilder();  // Uses read replicas

$users = $builder
    ->select('*')
    ->from('users')
    ->queryAll();  // Goes to a replica

Checking Replica Availability

if ($driver->hasReadReplicas()) {
    // Can use read replicas
}

Replica Selection

php-sqlx selects replicas using weighted random selection:

// With weights [2, 1, 1]
// Server 1: 50% chance
// Server 2: 25% chance
// Server 3: 25% chance

Replication Lag

Replicas may be behind the primary. Be aware of this for:

Read-After-Write

// Write to primary
$driver->execute("INSERT INTO users (name) VALUES (?)", ['Alice']);

// Read from replica - might not see the new user yet!
$user = $driver->queryRow("SELECT * FROM users WHERE name = ?", ['Alice']);

Solutions

1. Read from primary after writes:

$driver->execute("INSERT INTO users ...", [...]);

// Use the write builder (goes to primary)
$user = $driver->builder()  // Not readBuilder()
    ->select('*')
    ->from('users')
    ->where([['name', '=', 'Alice']])
    ->queryRow();

2. Use transactions:

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO users ...", [...]);
    // Within transaction, all queries use the same connection (primary)
    $user = $driver->queryRow("SELECT * FROM users WHERE name = ?", ['Alice']);
    return true;
});

3. Add a delay:

$driver->execute("INSERT INTO users ...", [...]);
usleep(100000);  // Wait 100ms for replication
$user = $driver->queryRow("SELECT * FROM users WHERE name = ?", ['Alice']);

Failover

If a replica is unavailable:

1. Queries fail over to other replicas
2. If all replicas are down, reads fall back to the primary
3. The unavailable replica is temporarily removed from rotation

Connection Pools

Each replica maintains its own connection pool:

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://primary/mydb",
    DriverOptions::OPT_READ_REPLICAS => ["postgres://replica1/mydb", "postgres://replica2/mydb"],
    DriverOptions::OPT_MAX_CONNECTIONS => 5,  // 5 connections each to primary, replica1, replica2
]);

Read-Only Mode

Create a driver that only uses replicas (no writes allowed):

$readOnlyDriver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://replica1/mydb",
    DriverOptions::OPT_READONLY => true,
]);

$readOnlyDriver->queryAll("SELECT * FROM users");  // OK
$readOnlyDriver->execute("INSERT ...", [...]);     // Throws NotPermittedException!

Best Practices

Separate Read and Write Paths

class UserRepository
{
    public function __construct(
        private $driver
    ) {}

    // Reads can use replicas
    public function findAll(): array
    {
        return $this->driver->readBuilder()
            ->select('*')
            ->from('users')
            ->queryAll();
    }

    // Writes use primary
    public function create(array $data): void
    {
        $this->driver->insert('users', $data);
    }

    // Read-after-write uses primary
    public function createAndReturn(array $data): object
    {
        $this->driver->insert('users', $data);

        // Use write builder to read from primary
        return $this->driver->builder()
            ->select('*')
            ->from('users')
            ->where([['email', '=', $data['email']]])
            ->queryRow();
    }
}

Monitor Replication Lag

-- PostgreSQL: Check replica lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

-- MySQL: Check replica lag
SHOW SLAVE STATUS\G

Use Appropriate Weights

Match weights to server capacity:

// replica1: 8 CPU, 32GB RAM
// replica2: 4 CPU, 16GB RAM
DriverOptions::OPT_READ_REPLICAS => [
    ['url' => "postgres://replica1/mydb", 'weight' => 2],
    ['url' => "postgres://replica2/mydb", 'weight' => 1],
],

Query Hooks

Query hooks let you intercept and observe all queries executed by a driver. They’re useful for logging, profiling, debugging, and monitoring.

Setting a Query Hook

$driver->onQuery(function(string $sql, array $params, float $duration) {
    echo "Query: $sql\n";
    echo "Params: " . json_encode($params) . "\n";
    echo "Duration: {$duration}ms\n";
});

The callback receives:

• $sql - The SQL query (with placeholders)
• $params - The bound parameters
• $duration - Execution time in milliseconds

Use Cases

Logging

$driver->onQuery(function(string $sql, array $params, float $duration) {
    $logger->debug('SQL Query', [
        'sql' => $sql,
        'params' => $params,
        'duration_ms' => $duration,
    ]);
});

Slow Query Detection

$driver->onQuery(function(string $sql, array $params, float $duration) {
    if ($duration > 1000) {  // > 1 second
        $logger->warning('Slow query detected', [
            'sql' => $sql,
            'params' => $params,
            'duration_ms' => $duration,
        ]);
    }
});

Query Counting

$queryCount = 0;
$totalTime = 0;

$driver->onQuery(function(string $sql, array $params, float $duration) use (&$queryCount, &$totalTime) {
    $queryCount++;
    $totalTime += $duration;
});

// After request processing
echo "Executed $queryCount queries in {$totalTime}ms\n";

N+1 Detection

$queries = [];

$driver->onQuery(function(string $sql, array $params, float $duration) use (&$queries) {
    // Normalize query (remove specific values)
    $normalized = preg_replace('/\$\d+/', '?', $sql);
    $queries[$normalized] = ($queries[$normalized] ?? 0) + 1;
});

// After request, check for repeated queries
register_shutdown_function(function() use (&$queries) {
    foreach ($queries as $sql => $count) {
        if ($count > 10) {
            error_log("Possible N+1: '$sql' executed $count times");
        }
    }
});

APM Integration

// Example with a hypothetical APM library
$driver->onQuery(function(string $sql, array $params, float $duration) {
    APM::recordQuery($sql, $duration);
});

Debug Output

if (getenv('DEBUG_SQL')) {
    $driver->onQuery(function(string $sql, array $params, float $duration) {
        $paramStr = empty($params) ? '' : ' -- ' . json_encode($params);
        echo "\033[36m[SQL {$duration}ms]\033[0m $sql$paramStr\n";
    });
}

Removing the Hook

Pass null to remove the hook:

$driver->onQuery(null);

Multiple Hooks

Only one hook can be active at a time. To call multiple handlers, compose them:

$handlers = [];

function addQueryHandler(callable $handler) {
    global $handlers;
    $handlers[] = $handler;
}

$driver->onQuery(function(string $sql, array $params, float $duration) {
    global $handlers;
    foreach ($handlers as $handler) {
        $handler($sql, $params, $duration);
    }
});

addQueryHandler(function($sql, $params, $duration) {
    // Logging
});

addQueryHandler(function($sql, $params, $duration) {
    // Metrics
});

Performance Considerations

Query hooks add overhead to every query. For production:

1. Keep hooks lightweight: Don’t do heavy processing in the callback
2. Use conditional hooks: Only enable detailed logging when needed
3. Buffer and batch: Collect data and process periodically

// Lightweight production hook
$driver->onQuery(function(string $sql, array $params, float $duration) {
    if ($duration > 100) {  // Only log slow queries
        error_log("Slow query: $sql ({$duration}ms)");
    }
});

Testing with Hooks

Verify queries in tests:

public function testUserCreation()
{
    $executedQueries = [];

    $this->driver->onQuery(function($sql, $params, $duration) use (&$executedQueries) {
        $executedQueries[] = ['sql' => $sql, 'params' => $params];
    });

    $this->userService->createUser('Alice', 'alice@example.com');

    $this->assertCount(1, $executedQueries);
    $this->assertStringContains('INSERT INTO users', $executedQueries[0]['sql']);
    $this->assertEquals(['Alice', 'alice@example.com'], $executedQueries[0]['params']);
}

Request-Scoped Hooks

In web applications, set hooks per-request:

// In middleware
public function handle($request, $next)
{
    $requestId = uniqid();

    $this->driver->onQuery(function($sql, $params, $duration) use ($requestId) {
        Log::debug("[$requestId] SQL: $sql ({$duration}ms)");
    });

    $response = $next($request);

    // Clean up
    $this->driver->onQuery(null);

    return $response;
}

Example: Query Profiler

class QueryProfiler
{
    private array $queries = [];
    private float $startTime;

    public function attach($driver): void
    {
        $this->startTime = microtime(true);
        $this->queries = [];

        $driver->onQuery(function($sql, $params, $duration) {
            $this->queries[] = [
                'sql' => $sql,
                'params' => $params,
                'duration' => $duration,
                'timestamp' => microtime(true) - $this->startTime,
            ];
        });
    }

    public function getReport(): array
    {
        $totalTime = array_sum(array_column($this->queries, 'duration'));

        return [
            'query_count' => count($this->queries),
            'total_time_ms' => $totalTime,
            'queries' => $this->queries,
        ];
    }

    public function getSlowest(int $n = 5): array
    {
        $sorted = $this->queries;
        usort($sorted, fn($a, $b) => $b['duration'] <=> $a['duration']);
        return array_slice($sorted, 0, $n);
    }
}

// Usage
$profiler = new QueryProfiler();
$profiler->attach($driver);

// ... execute queries ...

print_r($profiler->getReport());
print_r($profiler->getSlowest(3));

Driver Options

php-sqlx drivers can be configured with various options when created.

Using Options

use Sqlx\DriverFactory;
use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_MAX_CONNECTIONS => 10,
    DriverOptions::OPT_ASSOC_ARRAYS => true,
]);

Core Options

OPT_URL

Required. The database connection URL.

DriverOptions::OPT_URL => "postgres://user:pass@localhost:5432/mydb"

See Connection Strings for URL formats.

OPT_ASSOC_ARRAYS

Return query results as associative arrays instead of objects.

DriverOptions::OPT_ASSOC_ARRAYS => true  // Default: false

// With OPT_ASSOC_ARRAYS => false (default)
$user = $driver->queryRow("SELECT * FROM users WHERE id = 1");
echo $user->name;  // Object property access

// With OPT_ASSOC_ARRAYS => true
$user = $driver->queryRow("SELECT * FROM users WHERE id = 1");
echo $user['name'];  // Array access

OPT_READONLY

Mark the driver as read-only. Write operations will throw NotPermittedException.

DriverOptions::OPT_READONLY => true  // Default: false

Useful for:

• Replica-only connections
• Preventing accidental writes
• Analytics/reporting connections

OPT_PERSISTENT_NAME

Enable persistent connections with a named pool.

DriverOptions::OPT_PERSISTENT_NAME => "myapp_primary"

Connections persist across PHP requests (in PHP-FPM). Different names create separate pools.

Query Behavior

OPT_COLLAPSIBLE_IN

When true, empty arrays in IN clauses become FALSE (or TRUE for NOT IN).

DriverOptions::OPT_COLLAPSIBLE_IN => true  // Default: true

// With OPT_COLLAPSIBLE_IN => true
$driver->queryAll("SELECT * FROM users WHERE id IN (?)", [[]]);
// Becomes: SELECT * FROM users WHERE FALSE

// With OPT_COLLAPSIBLE_IN => false
$driver->queryAll("SELECT * FROM users WHERE id IN (?)", [[]]);
// Throws ParameterException

AST Cache Options

php-sqlx caches parsed query ASTs for performance.

OPT_AST_CACHE_SHARD_COUNT

Number of cache shards (for concurrent access).

DriverOptions::OPT_AST_CACHE_SHARD_COUNT => 8  // Default: 8

OPT_AST_CACHE_SHARD_SIZE

Maximum entries per shard.

DriverOptions::OPT_AST_CACHE_SHARD_SIZE => 256  // Default: 256

Total cache capacity = shard_count × shard_size (default: 2048 entries)

All Options Reference

Duration Formats

Options that accept durations support:

• String format: "100ms", "30s", "5m", "1h"
• Integer: Seconds

DriverOptions::OPT_IDLE_TIMEOUT => "5m"      // 5 minutes
DriverOptions::OPT_IDLE_TIMEOUT => "30s"     // 30 seconds
DriverOptions::OPT_IDLE_TIMEOUT => "100ms"   // 100 milliseconds
DriverOptions::OPT_IDLE_TIMEOUT => 300       // 300 seconds

Environment-Based Configuration

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => getenv('DATABASE_URL'),
    DriverOptions::OPT_MAX_CONNECTIONS => (int) getenv('DB_POOL_SIZE') ?: 5,
    DriverOptions::OPT_ASSOC_ARRAYS => getenv('DB_ASSOC_ARRAYS') === 'true',
]);

Profile-Based Configuration

$profiles = [
    'development' => [
        DriverOptions::OPT_URL => "postgres://localhost/myapp_dev",
        DriverOptions::OPT_MAX_CONNECTIONS => 2,
    ],
    'production' => [
        DriverOptions::OPT_URL => getenv('DATABASE_URL'),
        DriverOptions::OPT_MAX_CONNECTIONS => 10,
        DriverOptions::OPT_READ_REPLICAS => [
            getenv('DATABASE_REPLICA_1'),
            getenv('DATABASE_REPLICA_2'),
        ],
        DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,
    ],
];

$env = getenv('APP_ENV') ?: 'development';
$driver = DriverFactory::make($profiles[$env]);

Connection Pool Settings

Configure the connection pool for optimal performance and resource usage.

Pool Size

OPT_MAX_CONNECTIONS

Maximum number of connections in the pool.

DriverOptions::OPT_MAX_CONNECTIONS => 10  // Default: 2

Guidelines:

• PHP-FPM: Keep low (1-3) since each worker has its own pool
• Long-running workers: Can be higher (5-20)
• CLI scripts: Usually 1 is sufficient

Calculate total connections:

Total = workers × max_connections
Example: 50 PHP-FPM workers × 2 = 100 connections

OPT_MIN_CONNECTIONS

Minimum idle connections to maintain.

DriverOptions::OPT_MIN_CONNECTIONS => 2  // Default: 0

Benefits:

• Faster first query (no connection time)
• Stable connection count

Trade-offs:

• Uses database resources even when idle
• May not be useful for short-lived processes

Timeouts

OPT_IDLE_TIMEOUT

Close connections that have been idle longer than this duration.

DriverOptions::OPT_IDLE_TIMEOUT => "5m"  // Default: null (no timeout)

When to use:

• Cloud databases with connection limits
• Long-running workers with variable load
• Resource conservation

OPT_MAX_LIFETIME

Close and replace connections after this age, regardless of activity.

DriverOptions::OPT_MAX_LIFETIME => "30m"  // Default: null (no limit)

Prevents:

• Stale connections
• Memory leaks in long-running processes
• Issues with database failovers

OPT_ACQUIRE_TIMEOUT

Maximum time to wait for a connection from the pool.

DriverOptions::OPT_ACQUIRE_TIMEOUT => "30s"  // Default: null (wait indefinitely)

If exceeded, throws PoolExhaustedException.

Health Checks

OPT_TEST_BEFORE_ACQUIRE

Validate connections before returning them from the pool.

DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true  // Default: false

Adds a small ping query (e.g., SELECT 1) before each acquisition.

Pros:

• Catches dead connections before they cause query failures
• Handles network timeouts, database restarts

Cons:

• Small overhead per query
• May not be needed if idle_timeout/max_lifetime are set

Configuration Examples

Web Application (PHP-FPM)

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/myapp",
    DriverOptions::OPT_MAX_CONNECTIONS => 2,
    DriverOptions::OPT_MIN_CONNECTIONS => 1,
    DriverOptions::OPT_IDLE_TIMEOUT => "1m",
    DriverOptions::OPT_ACQUIRE_TIMEOUT => "5s",
]);

Long-Running Worker

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/myapp",
    DriverOptions::OPT_MAX_CONNECTIONS => 10,
    DriverOptions::OPT_MIN_CONNECTIONS => 2,
    DriverOptions::OPT_IDLE_TIMEOUT => "5m",
    DriverOptions::OPT_MAX_LIFETIME => "30m",
    DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,
]);

CLI Script

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/myapp",
    DriverOptions::OPT_MAX_CONNECTIONS => 1,
    DriverOptions::OPT_MIN_CONNECTIONS => 0,
]);

Cloud Database (Limited Connections)

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => getenv('DATABASE_URL'),
    DriverOptions::OPT_MAX_CONNECTIONS => 5,
    DriverOptions::OPT_MIN_CONNECTIONS => 0,
    DriverOptions::OPT_IDLE_TIMEOUT => "2m",
    DriverOptions::OPT_MAX_LIFETIME => "15m",
    DriverOptions::OPT_ACQUIRE_TIMEOUT => "10s",
    DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,
]);

High-Availability Setup

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://primary.db/myapp",
    DriverOptions::OPT_MAX_CONNECTIONS => 5,
    DriverOptions::OPT_MIN_CONNECTIONS => 2,
    DriverOptions::OPT_MAX_LIFETIME => "10m",
    DriverOptions::OPT_TEST_BEFORE_ACQUIRE => true,
    DriverOptions::OPT_READ_REPLICAS => [
        "postgres://replica1.db/myapp",
        "postgres://replica2.db/myapp",
    ],
]);

Monitoring

While php-sqlx doesn’t expose pool metrics directly, monitor at the database level:

PostgreSQL

-- Current connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
WHERE datname = 'myapp'
GROUP BY application_name;

-- Connection states
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'myapp'
GROUP BY state;

MySQL

-- Current connections
SHOW STATUS LIKE 'Threads_connected';

-- Max connections reached
SHOW STATUS LIKE 'Connection_errors_max_connections';

Troubleshooting

Pool Exhaustion

Symptoms: PoolExhaustedException thrown

Solutions:

1. Increase OPT_MAX_CONNECTIONS
2. Increase OPT_ACQUIRE_TIMEOUT
3. Optimize slow queries
4. Add read replicas for read-heavy workloads

Connection Churn

Symptoms: High connection creation rate in database logs

Solutions:

1. Increase OPT_MIN_CONNECTIONS
2. Increase OPT_IDLE_TIMEOUT
3. Use OPT_PERSISTENT_NAME (PHP-FPM)

Dead Connections

Symptoms: Random query failures with connection errors

Solutions:

1. Enable OPT_TEST_BEFORE_ACQUIRE
2. Set OPT_MAX_LIFETIME shorter than database timeout
3. Set OPT_IDLE_TIMEOUT shorter than firewall timeout

Retry Policy

php-sqlx can automatically retry failed queries for transient errors like connection drops or deadlocks.

Enabling Retries

use Sqlx\DriverFactory;
use Sqlx\DriverOptions;

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_RETRY_MAX_ATTEMPTS => 3,
]);

Configuration Options

OPT_RETRY_MAX_ATTEMPTS

Maximum number of retry attempts.

DriverOptions::OPT_RETRY_MAX_ATTEMPTS => 3  // Default: 0 (disabled)

Total attempts = 1 (original) + max_attempts (retries)

OPT_RETRY_INITIAL_BACKOFF

Delay before the first retry.

DriverOptions::OPT_RETRY_INITIAL_BACKOFF => "100ms"  // Default: "100ms"

OPT_RETRY_MAX_BACKOFF

Maximum delay between retries.

DriverOptions::OPT_RETRY_MAX_BACKOFF => "10s"  // Default: "10s"

OPT_RETRY_MULTIPLIER

Multiplier for exponential backoff.

DriverOptions::OPT_RETRY_MULTIPLIER => 2.0  // Default: 2.0

Exponential Backoff

Delays increase exponentially between retries:

Attempt 1: immediate
Retry 1: 100ms wait
Retry 2: 200ms wait (100ms × 2)
Retry 3: 400ms wait (200ms × 2)
...capped at max_backoff

With default settings (initial=100ms, multiplier=2.0, max=10s):

• Retry 1: 100ms
• Retry 2: 200ms
• Retry 3: 400ms
• Retry 4: 800ms
• Retry 5: 1.6s
• Retry 6: 3.2s
• Retry 7: 6.4s
• Retry 8+: 10s (capped)

Transient Errors

Only transient errors trigger retries:

Idempotency Warning

Important: Retries are only safe for idempotent queries!

Safe to retry:

// SELECT queries
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);

// Idempotent updates
$driver->execute("UPDATE users SET name = ? WHERE id = ?", ['Alice', 1]);

Dangerous to retry:

// Non-idempotent insert - could create duplicates
$driver->execute("INSERT INTO logs (message) VALUES (?)", ['event']);

// Counter increment - could increment multiple times
$driver->execute("UPDATE counters SET value = value + 1 WHERE id = ?", [1]);

Safe Retry Patterns

Use Transactions for Writes

// Retries are disabled within transactions by default
$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders ...", [...]);
    $driver->execute("UPDATE inventory ...", [...]);
    return true;
});

Use Upserts

// Idempotent - safe to retry
$driver->upsert('users',
    ['email' => 'alice@example.com', 'name' => 'Alice'],
    ['email'],  // conflict key
    ['name']    // update on conflict
);

Use INSERT … ON CONFLICT DO NOTHING

// Idempotent - safe to retry
$driver->execute(
    "INSERT INTO events (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING",
    [$eventId, $data]
);

Configuration Examples

Conservative (Web Application)

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_RETRY_MAX_ATTEMPTS => 2,
    DriverOptions::OPT_RETRY_INITIAL_BACKOFF => "50ms",
    DriverOptions::OPT_RETRY_MAX_BACKOFF => "500ms",
]);

Aggressive (Background Worker)

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_RETRY_MAX_ATTEMPTS => 5,
    DriverOptions::OPT_RETRY_INITIAL_BACKOFF => "100ms",
    DriverOptions::OPT_RETRY_MAX_BACKOFF => "30s",
    DriverOptions::OPT_RETRY_MULTIPLIER => 2.0,
]);

Disabled (Strict Mode)

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_RETRY_MAX_ATTEMPTS => 0,  // No retries
]);

Manual Retry Logic

For more control, implement your own retry logic:

function withRetry(callable $operation, int $maxAttempts = 3): mixed
{
    $attempt = 0;
    $lastException = null;

    while ($attempt < $maxAttempts) {
        try {
            return $operation();
        } catch (SqlxException $e) {
            if (!$e->isTransient()) {
                throw $e;
            }
            $lastException = $e;
            $attempt++;
            if ($attempt < $maxAttempts) {
                usleep(100000 * pow(2, $attempt - 1));  // Exponential backoff
            }
        }
    }

    throw $lastException;
}

// Usage
$result = withRetry(function() use ($driver) {
    return $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);
});

Monitoring Retries

Currently, retry attempts are not exposed to PHP. Consider using query hooks for visibility:

$retryCount = 0;

$driver->onQuery(function($sql, $params, $duration) use (&$retryCount) {
    // Note: This counts total queries, not retries specifically
    // You'd need to track unique query executions for accurate retry counts
});

Exception Types

php-sqlx uses a hierarchy of exceptions to provide detailed error information.

Exception Hierarchy

SqlxException (base)
├── ConnectionException
├── QueryException
├── TransactionException
├── ParseException
├── ParameterException
├── ConfigurationException
├── ValidationException
├── NotPermittedException
├── TimeoutException
└── PoolExhaustedException

All exceptions extend Sqlx\Exceptions\SqlxException.

SqlxException

The base exception class for all php-sqlx errors.

use Sqlx\Exceptions\SqlxException;

try {
    $driver->queryRow("SELECT * FROM users");
} catch (SqlxException $e) {
    echo "Error: " . $e->getMessage();
    echo "Code: " . $e->getCode();

    if ($e->isTransient()) {
        // Can retry this operation
    }
}

Methods

ConnectionException

Thrown when connection to the database fails.

use Sqlx\Exceptions\ConnectionException;

try {
    $driver = DriverFactory::make("postgres://invalid-host/mydb");
} catch (ConnectionException $e) {
    echo "Could not connect: " . $e->getMessage();
}

Common causes:

• Invalid hostname or port
• Authentication failure
• Network issues
• Database server down
• SSL/TLS configuration errors

QueryException

Thrown when a query fails to execute.

use Sqlx\Exceptions\QueryException;

try {
    $driver->queryRow("SELECT * FROM nonexistent_table");
} catch (QueryException $e) {
    echo "Query failed: " . $e->getMessage();
    echo "SQL: " . $e->getSql();
}

Common causes:

• Table or column doesn’t exist
• Syntax errors
• Constraint violations
• Permission denied
• Lock timeouts

TransactionException

Thrown for transaction-related errors.

use Sqlx\Exceptions\TransactionException;

try {
    $driver->begin(function($driver) {
        // ... operations that cause deadlock
        return true;
    });
} catch (TransactionException $e) {
    if ($e->isTransient()) {
        // Deadlock or serialization failure - can retry
    }
}

Common causes:

• Deadlocks
• Serialization failures
• Lock wait timeouts
• Transaction already in progress

ParseException

Thrown when SQL parsing fails (before sending to database).

use Sqlx\Exceptions\ParseException;

try {
    $driver->queryAll("SELECT * FROM users {{ AND status = }}");  // Incomplete block
} catch (ParseException $e) {
    echo "Parse error: " . $e->getMessage();
}

Common causes:

• Malformed conditional blocks {{ }}
• Invalid placeholder syntax
• Unmatched quotes or brackets

ParameterException

Thrown when parameter validation fails.

use Sqlx\Exceptions\ParameterException;

try {
    $driver->queryAll("SELECT * FROM users WHERE age = ?u", [-5]);  // Negative unsigned
} catch (ParameterException $e) {
    echo "Invalid parameter: " . $e->getMessage();
}

Common causes:

• Type mismatch with type-safe placeholders
• Missing required parameters
• Wrong number of parameters
• Invalid parameter format

ConfigurationException

Thrown for configuration errors.

use Sqlx\Exceptions\ConfigurationException;

try {
    $driver = DriverFactory::make([
        // Missing OPT_URL
        DriverOptions::OPT_MAX_CONNECTIONS => 10,
    ]);
} catch (ConfigurationException $e) {
    echo "Config error: " . $e->getMessage();
}

Common causes:

• Missing required options
• Invalid option values
• Conflicting options

ValidationException

Thrown when input validation fails.

use Sqlx\Exceptions\ValidationException;

try {
    $driver->insert('users', [
        'email' => 'not-an-email',  // Invalid format
    ]);
} catch (ValidationException $e) {
    echo "Validation failed: " . $e->getMessage();
}

NotPermittedException

Thrown when an operation is not allowed.

use Sqlx\Exceptions\NotPermittedException;

$readOnlyDriver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_READONLY => true,
]);

try {
    $readOnlyDriver->execute("INSERT INTO users ...", [...]);
} catch (NotPermittedException $e) {
    echo "Write not allowed on read-only connection";
}

TimeoutException

Thrown when an operation times out.

use Sqlx\Exceptions\TimeoutException;

try {
    $driver->queryAll("SELECT * FROM huge_table");  // Takes too long
} catch (TimeoutException $e) {
    echo "Query timed out: " . $e->getMessage();
}

PoolExhaustedException

Thrown when no connection is available from the pool.

use Sqlx\Exceptions\PoolExhaustedException;

try {
    $result = $driver->queryAll("SELECT ...");
} catch (PoolExhaustedException $e) {
    echo "All connections busy, try again later";
}

Common causes:

• All connections in use
• Acquire timeout exceeded
• Slow queries holding connections

Catching Multiple Types

try {
    $user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [$id]);
} catch (ConnectionException $e) {
    // Handle connection issues
    log_error("Database unavailable: " . $e->getMessage());
    show_maintenance_page();
} catch (QueryException $e) {
    // Handle query issues
    log_error("Query failed: " . $e->getSql());
    show_error_page();
} catch (SqlxException $e) {
    // Catch-all for other php-sqlx errors
    log_error("Database error: " . $e->getMessage());
    show_error_page();
}

Best Practices

Log Full Details

try {
    $driver->execute($sql, $params);
} catch (SqlxException $e) {
    $logger->error('Database error', [
        'message' => $e->getMessage(),
        'code' => $e->getCode(),
        'sql' => $e->getSql(),
        'transient' => $e->isTransient(),
        'trace' => $e->getTraceAsString(),
    ]);
    throw $e;
}

Retry Transient Errors

try {
    $result = $driver->queryAll($sql, $params);
} catch (SqlxException $e) {
    if ($e->isTransient()) {
        // Retry logic
        usleep(100000);
        $result = $driver->queryAll($sql, $params);
    } else {
        throw $e;
    }
}

Convert to Domain Exceptions

try {
    return $this->driver->queryRow(
        "SELECT * FROM users WHERE id = ?",
        [$id]
    );
} catch (QueryException $e) {
    throw new UserNotFoundException("User $id not found", 0, $e);
}

Error Codes

php-sqlx exceptions include error codes that help identify the type of error.

Error Code Constants

Error codes are defined as constants on SqlxException:

use Sqlx\Exceptions\SqlxException;

SqlxException::GENERAL          // 0 - General/unknown error
SqlxException::CONNECTION       // 1 - Connection failed
SqlxException::QUERY            // 2 - Query execution failed
SqlxException::TRANSACTION      // 3 - Transaction error
SqlxException::PARSE            // 4 - SQL parsing error
SqlxException::PARAMETER        // 5 - Parameter binding error
SqlxException::CONFIGURATION    // 6 - Configuration error
SqlxException::VALIDATION       // 7 - Validation error
SqlxException::NOT_PERMITTED    // 8 - Operation not allowed
SqlxException::TIMEOUT          // 9 - Operation timed out
SqlxException::POOL_EXHAUSTED   // 10 - Connection pool exhausted

Using Error Codes

use Sqlx\Exceptions\SqlxException;

try {
    $driver->queryRow($sql, $params);
} catch (SqlxException $e) {
    switch ($e->getCode()) {
        case SqlxException::CONNECTION:
            // Database unavailable
            break;
        case SqlxException::QUERY:
            // Query failed
            break;
        case SqlxException::TIMEOUT:
            // Operation too slow
            break;
        default:
            // Other error
    }
}

Code Reference

GENERAL (0)

Unspecified error. Check the message for details.

// Rare - most errors have specific codes

CONNECTION (1)

Connection to database failed.

// Network error
// "Connection error: Failed to connect to server"

// Authentication failure
// "Connection error: password authentication failed for user 'myuser'"

// SSL error
// "Connection error: SSL connection required"

QUERY (2)

Query execution failed.

// Table doesn't exist
// "Query error: relation \"nonexistent\" does not exist"

// Column doesn't exist
// "Query error: column \"invalid_col\" does not exist"

// Constraint violation
// "Query error: duplicate key value violates unique constraint"

// Permission denied
// "Query error: permission denied for table users"

TRANSACTION (3)

Transaction operation failed.

// Deadlock
// "Transaction error: deadlock detected"

// Serialization failure
// "Transaction error: could not serialize access"

// Already in transaction
// "Transaction error: there is already a transaction in progress"

PARSE (4)

SQL parsing failed (before sending to database).

// Invalid conditional block
// "Parse error: unclosed conditional block"

// Invalid placeholder
// "Parse error: unknown placeholder type '?x'"

PARAMETER (5)

Parameter binding failed.

// Type mismatch
// "Parameter error: expected integer, got string 'abc'"

// Wrong count
// "Parameter error: expected 3 parameters, got 2"

// Missing named parameter
// "Parameter error: missing required parameter 'user_id'"

CONFIGURATION (6)

Configuration error.

// Missing URL
// "Configuration error: OPT_URL is required"

// Invalid option
// "Configuration error: invalid value for OPT_MAX_CONNECTIONS"

VALIDATION (7)

Input validation failed.

// Invalid identifier
// "Validation error: invalid table name"

NOT_PERMITTED (8)

Operation not allowed.

// Write on read-only
// "Not permitted: write operations disabled on read-only connection"

TIMEOUT (9)

Operation timed out.

// Query timeout
// "Timeout: query exceeded maximum execution time"

// Statement timeout
// "Timeout: canceling statement due to statement timeout"

POOL_EXHAUSTED (10)

Connection pool is full.

// All connections busy
// "Pool exhausted: timed out waiting for connection"

Database-Specific Error Information

The original database error is often included in the message:

try {
    $driver->execute("INSERT INTO users (email) VALUES (?)", ['duplicate@example.com']);
} catch (QueryException $e) {
    // PostgreSQL
    // "Query error: duplicate key value violates unique constraint \"users_email_key\""

    // MySQL
    // "Query error: Duplicate entry 'duplicate@example.com' for key 'users.email'"

    // MSSQL
    // "Query error: Violation of UNIQUE KEY constraint 'UQ_users_email'"
}

Programmatic Error Handling

By Exception Type

use Sqlx\Exceptions\{
    ConnectionException,
    QueryException,
    TimeoutException,
    PoolExhaustedException
};

try {
    $result = $driver->queryAll($sql, $params);
} catch (ConnectionException $e) {
    // Retry with backoff or fail
} catch (TimeoutException $e) {
    // Cancel or increase timeout
} catch (PoolExhaustedException $e) {
    // Wait and retry
} catch (QueryException $e) {
    // Log and report error
}

By Error Code

try {
    $result = $driver->queryAll($sql, $params);
} catch (SqlxException $e) {
    if (in_array($e->getCode(), [
        SqlxException::CONNECTION,
        SqlxException::TIMEOUT,
        SqlxException::POOL_EXHAUSTED,
    ])) {
        // Infrastructure issue - retry or fail gracefully
        return $this->handleInfrastructureError($e);
    }

    if ($e->getCode() === SqlxException::QUERY) {
        // Application bug - log details
        return $this->handleQueryError($e);
    }

    throw $e;
}

By Transient Flag

try {
    $result = $driver->queryAll($sql, $params);
} catch (SqlxException $e) {
    if ($e->isTransient()) {
        // Temporary issue - can retry
        // (deadlock, connection lost, timeout)
    } else {
        // Permanent issue - don't retry
        // (syntax error, permission denied)
    }
}

Logging Recommendations

Include relevant context in error logs:

try {
    $result = $driver->execute($sql, $params);
} catch (SqlxException $e) {
    $this->logger->error('Database operation failed', [
        'error_code' => $e->getCode(),
        'error_name' => $this->getErrorName($e->getCode()),
        'message' => $e->getMessage(),
        'sql' => $e->getSql(),
        'is_transient' => $e->isTransient(),
        'context' => [
            'user_id' => $currentUserId,
            'request_id' => $requestId,
        ],
    ]);
}

private function getErrorName(int $code): string
{
    return match($code) {
        SqlxException::GENERAL => 'GENERAL',
        SqlxException::CONNECTION => 'CONNECTION',
        SqlxException::QUERY => 'QUERY',
        SqlxException::TRANSACTION => 'TRANSACTION',
        SqlxException::PARSE => 'PARSE',
        SqlxException::PARAMETER => 'PARAMETER',
        SqlxException::CONFIGURATION => 'CONFIGURATION',
        SqlxException::VALIDATION => 'VALIDATION',
        SqlxException::NOT_PERMITTED => 'NOT_PERMITTED',
        SqlxException::TIMEOUT => 'TIMEOUT',
        SqlxException::POOL_EXHAUSTED => 'POOL_EXHAUSTED',
        default => 'UNKNOWN',
    };
}

Migrating from PDO

This guide helps you migrate from PDO to php-sqlx.

Quick Comparison

Connection

PDO

$pdo = new PDO(
    "pgsql:host=localhost;dbname=mydb",
    "user",
    "password",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

php-sqlx

$driver = Sqlx\DriverFactory::make("postgres://user:password@localhost/mydb");

Basic Queries

PDO

// Fetch single row
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch(PDO::FETCH_OBJ);

// Fetch all rows
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
$users = $stmt->fetchAll(PDO::FETCH_OBJ);

php-sqlx

// Fetch single row
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [$id]);

// Fetch all rows
$users = $driver->queryAll("SELECT * FROM users WHERE status = ?", ['active']);

Named Parameters

PDO

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name AND status = :status");
$stmt->execute(['name' => 'Alice', 'status' => 'active']);
$user = $stmt->fetch(PDO::FETCH_OBJ);

php-sqlx

$user = $driver->queryRow(
    "SELECT * FROM users WHERE name = $name AND status = $status",
    ['name' => 'Alice', 'status' => 'active']
);

Note: php-sqlx uses $name instead of :name, but :name also works.

Insert/Update/Delete

PDO

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['Alice', 'alice@example.com']);
$affectedRows = $stmt->rowCount();

php-sqlx

$affectedRows = $driver->execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ['Alice', 'alice@example.com']
);

// Or use insert() helper
$driver->insert('users', [
    'name' => 'Alice',
    'email' => 'alice@example.com'
]);

Transactions

PDO

$pdo->beginTransaction();
try {
    $pdo->exec("INSERT INTO orders ...");
    $pdo->exec("UPDATE inventory ...");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

php-sqlx

$driver->begin(function($driver) {
    $driver->execute("INSERT INTO orders ...", [...]);
    $driver->execute("UPDATE inventory ...", [...]);
    return true;  // Commit
});

Prepared Statements

PDO

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");

foreach ($ids as $id) {
    $stmt->execute([$id]);
    $users[] = $stmt->fetch(PDO::FETCH_OBJ);
}

php-sqlx

$stmt = $driver->prepare("SELECT * FROM users WHERE id = ?");

foreach ($ids as $id) {
    $users[] = $stmt->queryRow([$id]);
}

Fetch Modes

PDO

// Object
$user = $stmt->fetch(PDO::FETCH_OBJ);

// Associative array
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// Numeric array
$user = $stmt->fetch(PDO::FETCH_NUM);

php-sqlx

// Object (default)
$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [$id]);

// Associative array
$user = $driver->queryRowAssoc("SELECT * FROM users WHERE id = ?", [$id]);

// Or configure globally
$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_ASSOC_ARRAYS => true,
]);

Error Handling

PDO

try {
    $stmt = $pdo->prepare("SELECT * FROM nonexistent");
    $stmt->execute();
} catch (PDOException $e) {
    echo $e->getMessage();
}

php-sqlx

use Sqlx\Exceptions\QueryException;

try {
    $driver->queryAll("SELECT * FROM nonexistent");
} catch (QueryException $e) {
    echo $e->getMessage();
    echo $e->getSql();  // The failing SQL
}

Feature Mapping

Features Not in PDO

php-sqlx provides additional features:

Conditional Blocks

$users = $driver->queryAll("
    SELECT * FROM users
    WHERE 1=1
    {{ AND status = $status }}
    {{ AND role = $role }}
", ['status' => 'active']);  // role block is omitted

Type-Safe Placeholders

$driver->queryAll("SELECT * FROM users WHERE age >= ?u", [18]);  // Unsigned only

Dictionary Queries

$usersById = $driver->queryDictionary("SELECT id, * FROM users");
// [1 => {...}, 2 => {...}, ...]

Query Builder

$users = $driver->builder()
    ->select(['id', 'name', 'email'])
    ->from('users')
    ->where([['status', '=', 'active']])
    ->orderBy(['name' => 'ASC'])
    ->queryAll();

Built-in Connection Pooling

$driver = DriverFactory::make([
    DriverOptions::OPT_URL => "postgres://localhost/mydb",
    DriverOptions::OPT_MAX_CONNECTIONS => 10,
    DriverOptions::OPT_READ_REPLICAS => ["postgres://replica/mydb"],
]);

Migration Strategy

Gradual Migration

1. Create a wrapper that provides both interfaces
2. Migrate code module by module
3. Remove PDO when migration is complete

class DatabaseConnection
{
    private $driver;

    public function __construct(string $url)
    {
        $this->driver = DriverFactory::make($url);
    }

    // Legacy PDO-style methods
    public function query(string $sql): array
    {
        return $this->driver->queryAll($sql);
    }

    public function prepare(string $sql): PreparedStatement
    {
        return new PreparedStatement($this->driver->prepare($sql));
    }

    // New php-sqlx methods
    public function queryRow(string $sql, array $params = []): ?object
    {
        return $this->driver->queryMaybeRow($sql, $params);
    }

    // ... etc
}

Full Migration

For new projects or full rewrites:

// Before (PDO)
class UserRepository
{
    private PDO $pdo;

    public function find(int $id): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}

// After (php-sqlx)
class UserRepository
{
    private \Sqlx\DriverInterface $driver;

    public function find(int $id): ?object
    {
        return $this->driver->queryMaybeRow(
            "SELECT * FROM users WHERE id = ?",
            [$id]
        );
    }
}

PDO Emulator

php-sqlx includes a PDO emulation layer for easier migration from PDO-based code.

Overview

The PDO emulator provides PDO-compatible classes that wrap php-sqlx drivers:

use PdoEmulator\PDO;

// Works like regular PDO
$pdo = new PDO("postgres://user:pass@localhost/mydb");
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch(PDO::FETCH_OBJ);

Installation

Include the emulator file:

require_once 'path/to/pdo.php';

use PdoEmulator\PDO;
use PdoEmulator\PDOStatement;

Creating a Connection

use PdoEmulator\PDO;

// URL-style connection
$pdo = new PDO("postgres://user:pass@localhost/mydb");
$pdo = new PDO("mysql://user:pass@localhost/mydb");
$pdo = new PDO("mssql://user:pass@localhost/mydb");

Supported Methods

PDO Class

PDOStatement Class

Fetch Modes

use PdoEmulator\PDO;

$stmt = $pdo->query("SELECT * FROM users");

// Object (default)
$row = $stmt->fetch(PDO::FETCH_OBJ);

// Associative array
$row = $stmt->fetch(PDO::FETCH_ASSOC);

// Both numeric and associative
$row = $stmt->fetch(PDO::FETCH_BOTH);

// Numeric array
$row = $stmt->fetch(PDO::FETCH_NUM);

Error Handling

use PdoEmulator\PDO;
use PdoEmulator\PDOException;

// Errors throw PDOException (like PDO::ERRMODE_EXCEPTION)
try {
    $pdo->query("SELECT * FROM nonexistent");
} catch (PDOException $e) {
    echo $e->getMessage();
    print_r($e->errorInfo);  // [SQLSTATE, code, message]
}

Transaction Limitations

The emulator has limited transaction support:

// Basic transactions work
$pdo->beginTransaction();
$pdo->exec("INSERT INTO ...");
$pdo->commit();

// But nested transactions are not supported
$pdo->beginTransaction();
$pdo->beginTransaction();  // Error!

For complex transaction needs, use the native php-sqlx API:

$driver->begin(function($driver) {
    $driver->savepoint('sp1');
    // ...
    $driver->rollbackToSavepoint('sp1');
    return true;
});

Iterating Results

PDOStatement is iterable:

$stmt = $pdo->query("SELECT * FROM users");

foreach ($stmt as $row) {
    echo $row->name . "\n";
}

Migration Strategy

Step 1: Replace PDO Class

// Before
use PDO;
$pdo = new PDO("pgsql:host=localhost;dbname=mydb", "user", "pass");

// After
use PdoEmulator\PDO;
$pdo = new PDO("postgres://user:pass@localhost/mydb");

Step 2: Test Existing Code

Your existing PDO-based code should work with minimal changes:

// This code works with both real PDO and the emulator
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch(PDO::FETCH_OBJ);

Step 3: Gradual Native Migration

Replace PDO patterns with native php-sqlx for better features:

// PDO-style (works but verbose)
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
$users = $stmt->fetchAll(PDO::FETCH_OBJ);

// Native php-sqlx (simpler)
$users = $driver->queryAll("SELECT * FROM users WHERE status = ?", ['active']);

Accessing Native Driver

Get the underlying php-sqlx driver:

$pdo = new PDO("postgres://localhost/mydb");

// Access native driver for advanced features
$driver = $pdo->getDriver();

// Use native features
$users = $driver->queryDictionary("SELECT id, * FROM users");
$driver->begin(function($driver) {
    // Native transactions with savepoints
});

Limitations

The emulator doesn’t support:

1. PDO DSN Format: Use URL format instead

   // Not supported: "pgsql:host=localhost;dbname=mydb"
   // Use: "postgres://user:pass@localhost/mydb"
   

2. All PDO Attributes: Only common attributes are supported

   // Supported
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
   // Not supported
   $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
   

3. Nested Transactions: Use native API for savepoints

4. Some Fetch Modes:

   // Not supported
   PDO::FETCH_CLASS
   PDO::FETCH_INTO
   PDO::FETCH_LAZY
   PDO::FETCH_NAMED
   

When to Use the Emulator

Good for:

• Quick migration from PDO
• Legacy code that can’t be rewritten
• Testing php-sqlx with existing code

Better to use native API for:

• New code
• Performance-critical applications
• Advanced features (conditional blocks, type-safe placeholders)
• Complex transactions with savepoints

Example: Full Migration

// config.php - Before
$pdo = new \PDO(
    "pgsql:host=localhost;dbname=myapp",
    "user",
    "password"
);

// config.php - After (Step 1: Use emulator)
use PdoEmulator\PDO;
$pdo = new PDO("postgres://user:password@localhost/myapp");

// config.php - After (Step 2: Native driver)
$driver = Sqlx\DriverFactory::make("postgres://user:password@localhost/myapp");

// Repository - Before (PDO)
class UserRepository {
    public function __construct(private \PDO $pdo) {}

    public function find(int $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: null;
    }
}

// Repository - After (Native)
class UserRepository {
    public function __construct(private \Sqlx\DriverInterface $driver) {}

    public function find(int $id): ?object {
        return $this->driver->queryMaybeRow("SELECT * FROM users WHERE id = ?", [$id]);
    }
}

API Reference

Complete reference for all php-sqlx classes, interfaces, and methods.

Interfaces Overview

php-sqlx provides interfaces for dependency injection and testing:

DriverFactory

namespace Sqlx;

class DriverFactory implements FactoryInterface
{
    /**
     * Create a driver instance
     * @param string|array $urlOrOptions Connection URL or options array
     * @return DriverInterface
     */
    public static function make(string|array $urlOrOptions): DriverInterface;
}

DriverInterface

All drivers (PgDriver, MySqlDriver, MssqlDriver) implement this interface:

Constructor

public function __construct(string|array $urlOrOptions);

Query Methods - Single Row

// Get single row, throws if not found
public function queryRow(string $query, ?array $params = null): object;
public function queryRowAssoc(string $query, ?array $params = null): array;
public function queryRowObj(string $query, ?array $params = null): object;

// Get single row or null
public function queryMaybeRow(string $query, ?array $params = null): ?object;
public function queryMaybeRowAssoc(string $query, ?array $params = null): ?array;
public function queryMaybeRowObj(string $query, ?array $params = null): ?object;

Query Methods - Multiple Rows

public function queryAll(string $query, ?array $params = null): array;
public function queryAllAssoc(string $query, ?array $params = null): array;
public function queryAllObj(string $query, ?array $params = null): array;

Query Methods - Single Value

// Get single value, throws if not found
public function queryValue(string $query, ?array $params = null, mixed $column = 0): mixed;
public function queryValueAssoc(string $query, ?array $params = null, mixed $column = 0): mixed;
public function queryValueObj(string $query, ?array $params = null, mixed $column = 0): mixed;

// Get single value or null
public function queryMaybeValue(string $query, ?array $params = null, mixed $column = 0): mixed;
public function queryMaybeValueAssoc(string $query, ?array $params = null, mixed $column = 0): mixed;
public function queryMaybeValueObj(string $query, ?array $params = null, mixed $column = 0): mixed;

Query Methods - Column

public function queryColumn(string $query, ?array $params = null, mixed $column = 0): array;
public function queryColumnAssoc(string $query, ?array $params = null, mixed $column = 0): array;
public function queryColumnObj(string $query, ?array $params = null, mixed $column = 0): array;

Query Methods - Dictionary

// Map first column to entire row
public function queryDictionary(string $query, ?array $params = null): array;
public function queryDictionaryAssoc(string $query, ?array $params = null): array;
public function queryDictionaryObj(string $query, ?array $params = null): array;

// Map first column to second column
public function queryColumnDictionary(string $query, ?array $params = null): array;
public function queryColumnDictionaryAssoc(string $query, ?array $params = null): array;
public function queryColumnDictionaryObj(string $query, ?array $params = null): array;

// Group rows by first column
public function queryGroupedDictionary(string $query, ?array $params = null): array;
public function queryGroupedDictionaryAssoc(string $query, ?array $params = null): array;
public function queryGroupedDictionaryObj(string $query, ?array $params = null): array;

// Group second column by first column
public function queryGroupedColumnDictionary(string $query, ?array $params = null): array;
public function queryGroupedColumnDictionaryAssoc(string $query, ?array $params = null): array;
public function queryGroupedColumnDictionaryObj(string $query, ?array $params = null): array;

Data Modification

// Execute statement, return affected rows
public function execute(string $query, ?array $params = null): int;

// Insert single row, returns affected rows
public function insert(string $table, array $row): int;

// Insert multiple rows, returns affected rows
public function insertMany(string $table, array $rows): int;

// Insert or update on conflict
public function upsert(string $table, array $row, array $conflictCols, ?array $updateCols = null): void;

Transactions

// Start transaction (callback or imperative)
public function begin(?callable $callback = null): mixed;

// Commit current transaction
public function commit(): void;

// Rollback current transaction
public function rollback(): void;

// Savepoint operations
public function savepoint(string $name): void;
public function rollbackToSavepoint(string $name): void;
public function releaseSavepoint(string $name): void;

Utilities

// Quote value for SQL
public function quote(mixed $param): string;

// Quote string for LIKE pattern (escapes % and _)
public function metaQuoteLike(string $param): string;

// Quote identifier (table/column name)
public function quoteIdentifier(string $name): string;

// Render query without executing
public function dry(string $query, ?array $params = null): array; // [sql, params]

// Describe table columns
public function describeTable(string $table, ?string $schema = null): array;

Prepared Queries

public function prepare(string $query): PreparedQuery;

Query Builder

public function builder(): WriteQueryBuilder;
public function readBuilder(): ReadQueryBuilder;

Configuration

// Set application name
public function setApplicationName(string $name): void;

// Set client info
public function setClientInfo(string $appName, array $info): void;

// Check if returning arrays
public function assocArrays(): bool;

// Check for read replicas
public function hasReadReplicas(): bool;

Lifecycle

// Close all connections
public function close(): void;

// Check if closed
public function isClosed(): bool;

// Pin to single connection for callback
public function withConnection(callable $callback): mixed;

// Set query hook
public function onQuery(?callable $callback): void;

PreparedQueryInterface

Prepared statements implement PreparedQueryInterface:

public function execute(?array $params = null): int;

public function queryRow(?array $params = null): object;
public function queryRowAssoc(?array $params = null): array;
public function queryRowObj(?array $params = null): object;

public function queryMaybeRow(?array $params = null): ?object;
public function queryMaybeRowAssoc(?array $params = null): ?array;
public function queryMaybeRowObj(?array $params = null): ?object;

public function queryAll(?array $params = null): array;
public function queryAllAssoc(?array $params = null): array;
public function queryAllObj(?array $params = null): array;

// ... same dictionary methods as driver

Query Builder Interfaces

Query builders implement ReadQueryBuilderInterface or WriteQueryBuilderInterface.

Clause Methods

public function select(mixed $fields): static;
public function from(mixed $table, ?array $params = null): static;
public function where(mixed $conditions, ?array $params = null): static;
public function having(mixed $conditions, ?array $params = null): static;
public function orderBy(mixed $order): static;
public function groupBy(mixed $columns): static;
public function limit(int $limit, ?int $offset = null): static;
public function offset(int $offset): static;
public function paginate(mixed $paginate): static;
public function raw(string $part, ?array $params = null): static;

Join Methods

public function join(string $table, string $on, ?array $params = null): static;
public function innerJoin(string $table, string $on, ?array $params = null): static;
public function leftJoin(string $table, string $on, ?array $params = null): static;
public function rightJoin(string $table, string $on, ?array $params = null): static;
public function fullJoin(string $table, string $on, ?array $params = null): static;
public function crossJoin(string $table, ?string $on = null, ?array $params = null): static;
public function naturalJoin(string $table): static;

CTE and Union

public function with(string $name, string|Builder $as, ?array $params = null): static;
public function withRecursive(string $tableFields, string|Builder $as, ?array $params = null): static;
public function union(string|Builder $query, ?array $params = null): static;
public function unionAll(string|Builder $query, ?array $params = null): static;

Locking

public function forUpdate(): static;
public function forShare(): static;

Write Operations

public function insertInto(string $table): static;
public function replaceInto(string $table): static;  // MySQL REPLACE INTO
public function update(string $table): static;
public function deleteFrom(string $table): static;
public function truncateTable(string $table): static;
public function set(array $assignments): static;
public function values(array $row): static;
public function valuesMany(array $rows): static;
public function returning(string|array $columns): static;
public function onConflict(string|array $target, ?array $set = null): static;
public function onDuplicateKeyUpdate(array $set): static;
public function using(string|Builder $from, ?array $params = null): static;

Execution

public function execute(): int;
public function queryRow(): object;
public function queryAll(): array;
// ... all query methods (same as Driver)

// Get rendered SQL and parameters without executing
public function dry(): array;  // [sql, params]
public function dryInline(): string;  // SQL with params inlined

// Get current parameters
public function parameters(): array;

// Get SQL string
public function __toString(): string;

// Return to parent builder (for subqueries)
public function end(): static;

DriverOptions

namespace Sqlx;

class DriverOptions
{
    // Core
    const OPT_URL = 'url';
    const OPT_ASSOC_ARRAYS = 'assoc_arrays';
    const OPT_READONLY = 'readonly';
    const OPT_PERSISTENT_NAME = 'persistent_name';
    const OPT_COLLAPSIBLE_IN = 'collapsible_in';

    // AST Cache
    const OPT_AST_CACHE_SHARD_COUNT = 'ast_cache_shard_count';
    const OPT_AST_CACHE_SHARD_SIZE = 'ast_cache_shard_size';

    // Connection Pool
    const OPT_MAX_CONNECTIONS = 'max_connections';
    const OPT_MIN_CONNECTIONS = 'min_connections';
    const OPT_MAX_LIFETIME = 'max_lifetime';
    const OPT_IDLE_TIMEOUT = 'idle_timeout';
    const OPT_ACQUIRE_TIMEOUT = 'acquire_timeout';
    const OPT_TEST_BEFORE_ACQUIRE = 'test_before_acquire';

    // Read Replicas
    const OPT_READ_REPLICAS = 'read_replicas';

    // Retry Policy
    const OPT_RETRY_MAX_ATTEMPTS = 'retry_max_attempts';
    const OPT_RETRY_INITIAL_BACKOFF = 'retry_initial_backoff';
    const OPT_RETRY_MAX_BACKOFF = 'retry_max_backoff';
    const OPT_RETRY_MULTIPLIER = 'retry_multiplier';
}

Clause Helpers

namespace Sqlx;

class SelectClause
{
    public function __construct(array $allowed = []);
    public function allowed(array $cols): static;
    public function input(array $cols): SelectClauseRendered;
}

class ByClause
{
    public function __construct(array $allowed = []);
    public function allowed(array $cols): static;
    public function input(array $cols): ByClauseRendered;
}

class PaginateClause
{
    public function __construct();
    public function perPage(int $count): static;
    public function minPerPage(int $min): static;
    public function maxPerPage(int $max): static;
    public function __invoke(?int $page, ?int $perPage): PaginateClauseRendered;
}

Exceptions

namespace Sqlx\Exceptions;

class SqlxException extends \Exception
{
    const GENERAL = 0;
    const CONNECTION = 1;
    const QUERY = 2;
    const TRANSACTION = 3;
    const PARSE = 4;
    const PARAMETER = 5;
    const CONFIGURATION = 6;
    const VALIDATION = 7;
    const NOT_PERMITTED = 8;
    const TIMEOUT = 9;
    const POOL_EXHAUSTED = 10;

    public function isTransient(): bool;
    public function getSql(): ?string;
}

class ConnectionException extends SqlxException {}
class QueryException extends SqlxException {}
class TransactionException extends SqlxException {}
class ParseException extends SqlxException {}
class ParameterException extends SqlxException {}
class ConfigurationException extends SqlxException {}
class ValidationException extends SqlxException {}
class NotPermittedException extends SqlxException {}
class TimeoutException extends SqlxException {}
class PoolExhaustedException extends SqlxException {}

Functions

namespace Sqlx;

// Create OR condition for where clauses
function OR_(array $conditions): OrClause;

Row Interfaces

RowInterface

Result rows implement ArrayAccess for property access:

namespace Sqlx;

interface RowInterface extends \ArrayAccess
{
    public function offsetExists(mixed $offset): bool;
    public function offsetGet(mixed $offset): mixed;
    public function offsetSet(mixed $offset, mixed $value): void;
    public function offsetUnset(mixed $offset): void;
}

Usage:

$user = $driver->queryRow("SELECT * FROM users WHERE id = ?", [1]);

// Object property access
echo $user->name;

// Array access (via ArrayAccess)
echo $user['name'];

RowJsonInterface

For rows with JSON columns, provides automatic JSON decoding:

namespace Sqlx;

interface RowJsonInterface
{
    // Get JSON column as decoded PHP array/object
    public function json(string $column): mixed;
}

Usage:

$event = $driver->queryRow("SELECT * FROM events WHERE id = ?", [1]);

// Get JSON column as PHP array
$data = $event->json('payload');

// Access nested data
echo $data['user']['name'];

SQL Syntax Reference

Complete reference for php-sqlx augmented SQL syntax.

Placeholders

Positional Placeholders

-- Question mark (standard)
SELECT * FROM users WHERE id = ? AND status = ?

-- Numbered (PostgreSQL style)
SELECT * FROM users WHERE id = $1 AND status = $2

-- Colon-numbered
SELECT * FROM users WHERE id = :1 AND status = :2

Named Placeholders

-- Dollar-sign (recommended)
SELECT * FROM users WHERE id = $id AND status = $status

-- Colon-prefix
SELECT * FROM users WHERE id = :id AND status = :status

Type-Safe Placeholders

Scalar Types

SELECT * FROM users WHERE age >= ?u
SELECT * FROM products WHERE price = ?d
SELECT * FROM users WHERE name = ?s
INSERT INTO events (data) VALUES (?j)

Nullable Types

Prefix with n for nullable:

UPDATE users SET manager_id = ?ni WHERE id = ?i

Array Types

Suffix with a for arrays:

SELECT * FROM users WHERE id IN (?ia)
SELECT * FROM products WHERE category IN (?sa)

Named Type-Safe Placeholders

SELECT * FROM users WHERE age >= $min_age:u AND name = $name:s
SELECT * FROM users WHERE id IN ($ids:ia)

Conditional Blocks

Basic Syntax

SELECT * FROM users
WHERE 1=1
{{ AND status = $status }}
{{ AND role = $role }}

Block is included only if all referenced parameters are provided and non-null.

Nested Blocks

SELECT * FROM orders
WHERE 1=1
{{ AND customer_id = $customer_id
   {{ AND status = $status }}
}}

Multiple Parameters in Block

SELECT * FROM users
WHERE 1=1
{{ AND created_at BETWEEN $start_date AND $end_date }}

All parameters must be provided for the block to be included.

IN Clause Expansion

Array Expansion

-- Input
SELECT * FROM users WHERE id IN (?)
-- With params: [[1, 2, 3]]

-- Output (PostgreSQL)
SELECT * FROM users WHERE id IN ($1, $2, $3)
-- With params: [1, 2, 3]

Empty Array Handling

-- Input with empty array
SELECT * FROM users WHERE id IN (?)
-- With params: [[]]

-- Output (with OPT_COLLAPSIBLE_IN = true)
SELECT * FROM users WHERE FALSE

For NOT IN:

-- Input with empty array
SELECT * FROM users WHERE id NOT IN (?)

-- Output
SELECT * FROM users WHERE TRUE

Query Builder WHERE Syntax

Array Format

// [column, operator, value]
[['status', '=', 'active']]
[['age', '>=', 18]]
[['name', 'LIKE', '%john%']]
[['id', 'IN', [1, 2, 3]]]
[['deleted_at', 'IS NULL']]

Supported Operators

OR Conditions

use function Sqlx\OR_;

// AND (status = 'active' OR status = 'pending')
[
    OR_([
        ['status', '=', 'active'],
        ['status', '=', 'pending']
    ])
]

// Complex: active AND (admin OR moderator)
[
    ['active', '=', true],
    OR_([
        ['role', '=', 'admin'],
        ['role', '=', 'moderator']
    ])
]

Database-Specific Output

Identifier Quoting

Parameter Markers

Boolean Values

Unicode Strings

Escaping Rules

String Escaping

Single quotes are doubled:

'O''Brien'  -- Represents O'Brien

LIKE Pattern Escaping

metaQuoteLike() escapes % and _:

$pattern = $driver->metaQuoteLike("100%");
// Returns: '100\%'

Identifier Escaping

Special characters in identifiers:

$driver->quoteIdentifier("my-table");
// PostgreSQL: "my-table"
// MySQL: `my-table`
// MSSQL: [my-table]

Examples

Dynamic Search

SELECT * FROM products
WHERE 1=1
{{ AND category = $category }}
{{ AND price >= $min_price }}
{{ AND price <= $max_price }}
{{ AND name LIKE $search }}
ORDER BY {{ $sort_column }} {{ $sort_dir }}
LIMIT $limit OFFSET $offset

Type-Safe User Input

SELECT * FROM users
WHERE age >= ?u
AND status IN (?sa)
AND balance >= ?ud

Complex Filtering

SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE 1=1
{{ AND u.status = $status }}
{{ AND u.role IN ($roles) }}
{{ AND u.created_at >= $since }}
GROUP BY u.id
{{ HAVING COUNT(o.id) >= $min_orders }}
ORDER BY {{ $order_by }}
LIMIT ?u