How it works

sqlc-php reads your SQL schema and annotated query files, resolves column types, and generates a Model class (readonly, hydrated from PDO rows), a Query class (one method per query), and an optional Interface for dependency injection.

Requirements

Installation

composer require phpibe/sqlc-php

Then create a sqlc.yaml config, annotate your queries, and run:

php vendor/bin/sqlc-php sqlc.yaml

Configuration — sqlc.yaml

All configuration lives in a single YAML file. Here is the full reference with every available option:

version: "2"

# ── Schema files ────────────────────────────────────────────────────────────
schema:
  - database/schema/users.sql      # one or more SQL files with CREATE TABLE
  - database/schema/orders.sql

# ── Global defaults (can be overridden per target) ──────────────────────────
engine:       mysql                # database engine (mysql supported; postgres planned)
language:     english              # english | spanish | french | portuguese | etc.
class_suffix: Query                # suffix for generated class names (default: Query)
                                   # e.g. Repository → UserRepository, Service → UserService

# ── Global type overrides ───────────────────────────────────────────────────
type_overrides:
  - db_type: "TINYINT"
    php_type: "bool"

# ── Database connection for --generate-schema ───────────────────────────────
database:
  dsn:      "mysql:host=localhost;dbname=myapp;charset=utf8mb4"
  username: "${DB_USER}"           # ${ENV_VAR} expanded from environment at runtime
  password: "${DB_PASS}"
  exclude_tables:                  # skip these tables when extracting schema
    - migrations
    - failed_jobs
  # include_tables:                # whitelist — only extract these tables
  #   - users
  #   - orders

# ── Virtual tables (views, CTEs, external tables) ───────────────────────────
virtual_tables:
  - name: user_summary
    columns:
      - { name: id,    type: INT }
      - { name: email, type: VARCHAR }
      - { name: total, type: INT }

# ── Includes (split config across files) ────────────────────────────────────
includes:
  - config/views.yaml

# ── Output targets ──────────────────────────────────────────────────────────
targets:
  - namespace: "App\\Database"
    out:        generated           # output directory
    queries:
      - database/queries/users.sql
      - database/queries/orders.sql
    generate_interfaces:    true    # generate *Interface.php (default: true)
    prepared_statement_cache: false # cache PDOStatements in $this->stmts (default: false)
    class_suffix:  Query            # per-target override of global class_suffix
    engine:        mysql            # per-target engine override
    language:      english          # per-target language override
    type_overrides:                 # per-target overrides (merged with global)
      - db_type: "DECIMAL"
        php_type: "float"
    database:                       # per-target DB override for --generate-schema
      dsn: "mysql:host=db2;dbname=other"

Configuration options reference

out: — string vs map form

By default out: is a string and all generated files share one directory and namespace. When out: is a YAML map, each file type gets its own directory — and its namespace is automatically derived from the last path segment appended to the target's base namespace.

String form — all types in one directory (backward-compatible default)

targets:
  - namespace: "App\\Database"
    out:       generated        # UserQuery.php, User.php, GetUserRow.php — all go here
    queries:   queries.sql

Map form — each type in its own directory

targets:
  - namespace: "App\\Database"
    queries:   queries.sql
    out:
      queries:    database/Repositories   # → App\Database\Repositories\UserRepository.php
      models:     database/Models         # → App\Database\Models\User.php
      dtos:       database/DTOs           # → App\Database\DTOs\GetUserWithRoleRow.php
      enums:      database/Enums          # → App\Database\Enums\OrderStatus.php
      interfaces: database/Contracts      # → App\Database\Contracts\UserRepositoryInterface.php
      criterias:  database/Criterias      # → App\Database\Criterias\UserCriteria.php

Namespace rule: namespace + '\' + last path segment. The prefix is filesystem-only and does not affect the namespace.

Automatic use statements: when types live in different namespaces, sqlc-php injects the necessary use statements into generated files automatically.

Error policy: if a query generates a file type that has no declared directory in the map, generation stops with a clear error before writing any files.

Partial maps: you only need to declare the types your project actually uses. If no query uses @searchable, you can omit out.criterias.

DDD / Laravel example

targets:
  - namespace:   "App\\Modules\\Billing"
    queries:     database/queries/billing.sql
    class_suffix: Repository
    out:
      queries:    app/Modules/Billing/Infrastructure         # BillingConfigRepository.php
      models:     app/Modules/Billing/Domain                 # BillingConfig.php
      dtos:       app/Modules/Billing/Infrastructure/DTOs    # ListBillingConfigRow.php
      interfaces: app/Modules/Billing/Domain/Ports           # BillingConfigRepositoryInterface.php
      enums:      app/Modules/Billing/Domain/Enums           # OrderStatus.php
      criterias:  app/Modules/Billing/Infrastructure         # BillingConfigCriteria.php

type_overrides

Override the default SQL → PHP type mapping. Applies globally or per-target.

type_overrides:
  - db_type:  "TINYINT"
    php_type: "bool"

  - db_type:  "DECIMAL"
    php_type: "float"

  - db_type:  "TIMESTAMP"
    php_type: "\\DateTimeImmutable"
    nullable:  true

Precedence: target-level overrides → global overrides → built-in defaults.

SQL → PHP type mapping

--generate-schema

Connects to a live database and generates schema.sql automatically. No need to write or maintain CREATE TABLE statements by hand.

# Write to the first schema: path in sqlc.yaml
php vendor/bin/sqlc-php --generate-schema sqlc.yaml

# Write to a custom path
php vendor/bin/sqlc-php --generate-schema --schema-output=database/schema.sql sqlc.yaml

Workflow

# 1. Extract schema from live DB
php vendor/bin/sqlc-php --generate-schema sqlc.yaml

# 2. Generate PHP classes
php vendor/bin/sqlc-php sqlc.yaml

database: block

database:
  dsn:      "mysql:host=localhost;dbname=myapp;charset=utf8mb4"
  username: "${DB_USER}"     # expanded from environment at runtime
  password: "${DB_PASS}"
  exclude_tables:
    - migrations
    - failed_jobs
    - sessions
  # include_tables:          # whitelist — only extract these tables
  #   - users
  #   - orders

Security — credentials in environment

# sqlc.yaml (safe to commit)
database:
  dsn:      "mysql:host=${DB_HOST};dbname=${DB_NAME};charset=utf8mb4"
  username: "${DB_USER}"
  password: "${DB_PASS}"

# .env.local (add to .gitignore)
DB_HOST=localhost
DB_NAME=myapp
DB_USER=root
DB_PASS=secret

source .env.local && php vendor/bin/sqlc-php --generate-schema sqlc.yaml

Annotations reference

Annotations appear as SQL comments (-- @annotation value) immediately before the query. Every query requires at minimum @name and @returns.

@name

Sets the PHP method name. The name is used as-is — use camelCase by convention.

-- @name GetUserById
-- @returns :one
SELECT * FROM users WHERE id = :id;

Generates: public function getUserById(int $id): User

@class / @group

@class sets the group name that determines which Query class the method is placed in. Multiple queries with the same @class are combined into one file. When omitted, the group is inferred from the FROM table name.

-- @name ListActive
-- @class User              ← all queries with @class User → UserQuery.php
-- @returns :many
SELECT * FROM users WHERE active = 1;

-- @name CountByCountry
-- @class User              ← same class → same file
-- @returns :one
SELECT country_id, COUNT(*) AS total FROM users GROUP BY country_id;

Combined with class_suffix: Repository in sqlc.yaml, @class User generates UserRepository.

@returns

Declares the return type of the generated method. See the Return Types section for full details on each type.

-- @returns :many            → array of rows
-- @returns :many-paginated  → array of rows with optional LIMIT/OFFSET
-- @returns :paginated        → PaginatedResult (items + total + pages + metadata)
-- @returns :cursor          → CursorResult (items + hasMore + nextCursor token)
-- @returns :one             → single row (throws if not found)
-- @returns :opt             → single row or null
-- @returns :exec            → void (INSERT / UPDATE / DELETE)
-- @returns :batch           → int (same query N times in a transaction)
-- @returns :transaction     → void (calls multiple methods in one transaction)

@optional

Makes a WHERE parameter optional. When the caller passes null, the condition is skipped entirely — no PHP-side if required. The SQL is rewritten at compile time using the pattern (:param_chk IS NULL OR col OP :param) which is PDO-safe.

-- @name ListUsers
-- @optional active          ← :active becomes ?int $active = null
-- @optional country_id      ← multiple @optional allowed
-- @returns :many
SELECT * FROM users
WHERE active     = :active
  AND country_id = :country_id;

// All users (both filters skipped)
$query->listUsers();

// Only active users
$query->listUsers(active: 1);

// Active users in country 164
$query->listUsers(active: 1, country_id: 164);

Supported operators

= != <> > < >= <= LIKE ILIKE

@dto / @column

@dto overrides the auto-generated result DTO class name. Useful when you want to reuse the same DTO across multiple queries, or when the default name is unwieldy.

-- @name ListBillingConfig
-- @class BillingConfig
-- @dto BillingConfigRow     ← generated DTO class: BillingConfigRow.php
-- @returns :many
SELECT
    bc.id,
    bc.active,
    c.name AS country_name
FROM billing_config bc
JOIN countries c ON c.id = bc.country_id;

@column renames a result column in the generated DTO without adding an AS clause to the SQL:

-- @name GetUser
-- @column u_email email      ← renames u_email → email in the DTO
-- @returns :one
SELECT id, email AS u_email FROM users WHERE id = :id;

@embed

Groups prefixed columns from a JOIN into a nested readonly object instead of flat properties.

-- @name GetUserWithRole
-- @embed Role role__         ← columns prefixed "role__" → Role object
-- @returns :one
SELECT
    u.id,
    u.email,
    r.name  AS role__name,
    r.slug  AS role__slug
FROM users u
JOIN roles r ON r.id = u.role_id
WHERE u.id = :id;

// Generated: GetUserWithRoleRow.php
readonly class GetUserWithRoleRow {
    public function __construct(
        public int    $id,
        public string $email,
        public Role   $role,   // ← nested object
    ) {}
}

// Generated: Role.php (standalone value object)
readonly class Role {
    public function __construct(
        public string $name,
        public string $slug,
    ) {}
}

@json v2.12.3

Declares that a result column containing JSON output should be deserialized into a typed DTO instead of a plain PHP array. sqlc-php always generates a standalone readonly DTO for the referenced class, inferred from the schema table whose name matches the class name.

Three cardinality variants are supported:

@json:many — typed array (JSON_ARRAYAGG):

-- @name GetCountryWithCities
-- @class Country
-- @returns :one
-- @json:many cities City       ← or simply: @json cities City
SELECT
    countries.id,
    countries.name,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', cities.id, 'name', cities.name, 'slug', cities.slug)
    ) AS cities
FROM countries
INNER JOIN cities ON cities.country_id = countries.id
WHERE countries.id = :id
GROUP BY countries.id;

readonly class GetCountryWithCitiesRow
{
    public function __construct(
        public int    $id,
        public string $name,
        /** @var City[] */
        public array  $cities,
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            id:     (int) $row['id'],
            name:   (string) $row['name'],
            cities: array_map(
                fn(array $r) => City::fromRow($r),
                json_decode((string) $row['cities'], true) ?? []
            ),
        );
    }
}

@json:one — single embedded object (JSON_OBJECT):

-- @name GetUserWithAddress
-- @class User
-- @returns :one
-- @json:one address Address    ← single object, not an array
SELECT
    users.id,
    users.name,
    JSON_OBJECT('id', addresses.id, 'street', addresses.street, 'city', addresses.city) AS address
FROM users
INNER JOIN addresses ON addresses.user_id = users.id
WHERE users.id = :id;

readonly class GetUserWithAddressRow
{
    public function __construct(
        public int     $id,
        public string  $name,
        public Address $address,   // ← typed single object
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            id:      (int) $row['id'],
            name:    (string) $row['name'],
            address: Address::fromRow(json_decode((string) $row['address'], true) ?? []),
        );
    }
}

Mixed cardinality on the same query:

-- @json:one  address Address   ← JSON_OBJECT  → Address  $address
-- @json:many orders  Order     ← JSON_ARRAYAGG → Order[] $orders

@nillable

Forces a result column to be ?type (nullable) in the generated DTO, even if the schema defines it as NOT NULL. Useful for LEFT JOIN columns that may be NULL at runtime.

-- @name GetUserWithOrder
-- @nillable order_id        ← force nullable even though orders.id is NOT NULL
-- @nillable order_total
-- @returns :opt
SELECT
    u.id,
    u.email,
    o.id    AS order_id,
    o.total AS order_total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = :id;

Result: public ?int $orderId and public ?float $orderTotal in the DTO.

@type v2.9.6

Forces an explicit PHP type on a result column, completely overriding whatever the resolver inferred from the schema or expression. This is the primary tool for fixing types in UNION queries, constant string discriminators, and any column the resolver cannot determine.

Syntax

-- @type alias phpType

Primary use case — UNION with constant discriminators and NULL branches

In UNION queries, the resolver analyses only the first SELECT. Constant expressions like 'user' as role resolve to mixed because they have no schema backing. NULL in the second branch is invisible to the resolver. @type corrects both.

-- @name GetProductsByReserveId
-- @class ReserveBilling
-- @dto ProductRow
-- @type role string      ← 'direct'/'upgrade' are string constants → resolver gives mixed
-- @type amount ?float    ← second UNION branch returns NULL → should be nullable
-- @returns :many
SELECT products.id, products.name, 'direct' AS role, reserve.price AS amount
FROM reserve
INNER JOIN products ON reserve.product_id = products.id
WHERE reserve.id = :reserveId
UNION ALL
SELECT products.id, products.name, 'upgrade' AS role, NULL AS amount
FROM reserve_insured_upgrade
INNER JOIN products ON reserve_insured_upgrade.upgrade_id = products.id
WHERE reserve_insured_upgrade.reserve_id = :reserveId;

// Generated DTO — types from @type take effect
readonly class ProductRow {
    public function __construct(
        public int    $id,
        public string $name,
        public string $role,    // ← @type string
        public ?float $amount,  // ← @type ?float
    ) {}
}

Other use cases

-- @type active bool       ← TINYINT column used as boolean flag
-- @type subtotal float    ← complex expression (price * qty) resolver gives mixed
-- @type label ?string     ← column that may be NULL at runtime
-- @type score int         ← CAST or aggregate result needing an explicit type

Supported types

@counted

Generates a companion {name}Count(): int method alongside a :many-paginated method. The count method wraps the original SQL in SELECT COUNT(*) FROM (...) AS _count_subquery and returns the total number of rows matching the current filters — without any LIMIT or OFFSET applied.

Only valid with :many-paginated.

-- @name ListUsers
-- @counted
-- @returns :many-paginated
SELECT id, email, active FROM users WHERE active = :active;

// Two generated methods
public function listUsers(int $active, ?int $limit = null, int $offset = 0): array
public function listUsersCount(int $active): int

// Usage — the caller decides when to count
$total = $query->listUsersCount(active: 1);
$page  = $query->listUsers(active: 1, limit: 20, offset: 0);
$pages = (int) ceil($total / 20);

@counted vs :paginated — choosing the right approach

Both solve pagination, but with different trade-offs. The choice comes down to whether you need control over when the COUNT runs.

When to use :paginated (recommended default)

Use :paginated for the vast majority of paginated lists. It eliminates boilerplate, keeps the page total and navigation metadata in one place, and makes the API surface clean — one method, one call, everything you need.

-- @name ListUsers
-- @returns :paginated         ← recommended: one call, full metadata
SELECT * FROM users WHERE active = :active ORDER BY created_at DESC;

$result = $query->listUsers(active: 1, limit: 20, offset: 0);
// $result->items, $result->total, $result->pages, $result->hasMore — all ready

When to use @counted instead

Prefer @counted when COUNT(*) is expensive and you can avoid running it on every request:

// Pattern 1 — count only on first page, reuse for subsequent pages
$total = ($page === 1)
    ? $query->listUsersCount(active: 1)
    : $session->get('users_total');            // cached from page 1

$items = $query->listUsers(active: 1, limit: 20, offset: ($page - 1) * 20);

// Pattern 2 — infinite scroll: never count, just check hasMore manually
$items = $query->listUsers(active: 1, limit: 21, offset: $offset);
$hasMore = count($items) > 20;
$items   = array_slice($items, 0, 20);         // discard the probe row

// Pattern 3 — export / "load all" button: skip pagination entirely
$all = $query->listUsers(active: 1, limit: null);

@cursor v2.11.2

Declares the cursor columns for keyset (cursor) pagination. Required when using :cursor as the return type. Accepts one or more column names with optional direction (ASC or DESC, defaulting to ASC).

Syntax

-- @cursor col1 [ASC|DESC], col2 [ASC|DESC], ...

Examples

-- Single column, descending (most recent first)
-- @cursor id DESC

-- Two columns — always include a unique column (id) to break ties
-- @cursor created_at DESC, id DESC

-- Ascending order
-- @cursor name ASC, id ASC

See the :cursor return type and Cursor pagination guide for full usage examples, including @counted and @searchable.

@searchable

Generates a typed {Group}Criteria class and adds it as a parameter to the query method. Enables dynamic WHERE conditions and ORDER BY at runtime — without writing separate queries per filter combination. Only valid with :many and :many-paginated.

-- @name ListBillingConfig
-- @class BillingConfig
-- @searchable
-- @counted
-- @returns :many-paginated
SELECT id, active, country_id, end_num, expiration_date
FROM billing_config;

Generates BillingConfigCriteria.php with one typed method per column per applicable operator:

// Generated: BillingConfigCriteria.php
class BillingConfigCriteria extends \SqlcPhp\Criteria\Criteria
{
    // INT columns → Eq, Neq, Gt, Lt, Gte, Lte, In (variadic), NotIn (variadic)
    public function whereActiveEq(int $value): static
    public function whereActiveIn(int ...$values): static   // whereActiveIn(1, 2, 3)
    public function whereActiveNotIn(int ...$values): static
    public function whereCountryIdIn(int ...$values): static

    // Nullable columns → also IsNull, IsNotNull
    public function whereEndNumIsNull(): static
    public function whereEndNumIsNotNull(): static

    // DATE / DATETIME columns → also Between
    public function whereExpirationDateBetween(\DateTimeImmutable $from, \DateTimeImmutable $to): static

    // VARCHAR / TEXT → also Like, StartsWith, EndsWith
    // (none here since no string columns in this query)

    // Every column → orderBy method
    public function orderByActive(string $direction = 'ASC'): static
    public function orderByExpirationDate(string $direction = 'ASC'): static
}

// Generated method signatures
public function listBillingConfig(
    ?BillingConfigCriteria $criteria = null,
    ?int $limit = null,
    int $offset = 0,
): array

public function listBillingConfigCount(
    ?BillingConfigCriteria $criteria = null,
): int

// Usage
$criteria = (new BillingConfigCriteria())
    ->whereActiveEq(1)
    ->whereCountryIdIn(164, 165)
    ->orderByExpirationDate('DESC');

$page  = $query->listBillingConfig($criteria, limit: 20, offset: 0);
$total = $query->listBillingConfigCount($criteria);

// No criteria — returns all rows
$all   = $query->listBillingConfig();

Generated filter methods by type

@deprecated

Emits a @deprecated tag in the generated method docblock. IDEs and static analysis tools will warn callers.

-- @name GetUserLegacy
-- @deprecated Use getUserById instead
-- @returns :opt
SELECT * FROM users WHERE legacy_id = :legacy_id;

/** @deprecated Use getUserById instead */
public function getUserLegacy(string $legacy_id): ?User

@comment v2.14.0

Adds a human-readable description to the generated method docblock and its matching interface method. The description is placed before @param and @return tags, following PSR-5/phpDoc conventions. Multiple @comment lines are supported — each becomes a separate line in the description block.

Basic usage

-- @name GetActiveUser
-- @class Users
-- @returns :opt
-- @comment Returns the active user matching the given ID.
-- @comment Returns null when no user is found or the user is inactive.
SELECT * FROM users WHERE id = :id AND active = 1;

/**
 * Returns the active user matching the given ID.
 * Returns null when no user is found or the user is inactive.
 *
 * @param int $id
 * @return User|null
 */
public function getActiveUser(int $id): ?User

Example — documenting business rules

-- @name CreateOrder
-- @class Orders
-- @returns :exec
-- @comment Creates a new order in pending state.
-- @comment Automatically assigns created_at via the DB default (NOW()).
-- @comment Throws PDOException on foreign key violation (invalid user_id).
INSERT INTO orders (user_id, total, status) VALUES (:userId, :total, 'pending');

/**
 * Creates a new order in pending state.
 * Automatically assigns created_at via the DB default (NOW()).
 * Throws PDOException on foreign key violation (invalid user_id).
 *
 * @param int   $userId
 * @param float $total
 * @return void
 */
public function createOrder(int $userId, float $total): void

Example — combined with @deprecated

-- @name GetUserLegacy
-- @class Users
-- @returns :opt
-- @comment Use getUserById for the updated response shape.
-- @comment This method will be removed in v4.0.
-- @deprecated Replaced by getUserById in v3.
SELECT * FROM users WHERE legacy_id = :legacyId;

/**
 * Use getUserById for the updated response shape.
 * This method will be removed in v4.0.
 *
 * @deprecated Replaced by getUserById in v3.
 * @param string $legacyId
 * @return User|null
 */
public function getUserLegacy(string $legacyId): ?User

Example — documenting a @searchable query

-- @name ListUsers
-- @class Users
-- @returns :many
-- @searchable
-- @comment Returns users matching the given filters.
-- @comment Pass null to skip all filters and return every user.
-- @comment Combine with orderByCreatedAt('DESC') for newest-first ordering.
SELECT users.id, users.email, users.name, users.active FROM users;

/**
 * Returns users matching the given filters.
 * Pass null to skip all filters and return every user.
 * Combine with orderByCreatedAt('DESC') for newest-first ordering.
 *
 * @param ?UsersCriteria $criteria Dynamic filters and ordering. Pass null to skip.
 * @return User[]
 */
public function listUsers(?UsersCriteria $criteria = null): array

Interface propagation

The description also appears in the generated interface method, so IDE autocomplete shows it when programming against the interface:

// Generated interface:
interface UsersQueryInterface
{
    /**
     * Returns the active user matching the given ID.
     * Returns null when no user is found or the user is inactive.
     *
     * @param int $id
     * @return User|null
     */
    public function getActiveUser(int $id): ?User;
}

:paginated

Executes two queries in one call — a COUNT(*) and a paginated SELECT — and returns a single PaginatedResult object with items and full pagination metadata. Eliminates the need to call listUsers() and listUsersCount() separately.

Cannot be combined with @counted (they are alternatives). Can be combined with @searchable.

-- @name ListUsers
-- @returns :paginated
SELECT * FROM users WHERE active = :active ORDER BY created_at DESC;

// Generated signature — $limit defaults to 10, not nullable
public function listUsers(int $active, int $limit = 10, int $offset = 0): PaginatedResult

// Usage
$result = $query->listUsers(active: 1, limit: 20, offset: 0);

$result->items;         // User[] — the current page
$result->total;         // 150 — total rows matching the query
$result->limit;         // 20
$result->offset;        // 0
$result->pages;         // 8 — ceil(150 / 20)
$result->hasMore;       // true

// Navigation helpers
$result->currentPage();    // 1
$result->isFirstPage();    // true
$result->isLastPage();     // false
$result->nextOffset();     // 20
$result->previousOffset(); // null

With @searchable

-- @name ListUsers
-- @searchable
-- @returns :paginated
SELECT * FROM users;

// Criteria + pagination in one call
$result = $query->listUsers(
    criteria: (new UserCriteria())->whereActiveEq(1)->orderByCreatedAt('DESC'),
    limit: 20,
    offset: 0,
);
// The COUNT also applies the same criteria — total reflects the filtered set

PaginatedResult API

@returning

After executing an INSERT, fetches and returns the newly created row by its primary key (lastInsertId()). Eliminates the manual fetch-after-insert pattern that every controller currently repeats.

Only valid on :one INSERT queries. Cannot be used with ON DUPLICATE KEY UPDATE (unreliable lastInsertId()). The table must have a detectable primary key in the schema.

-- @name CreateUser
-- @returning
-- @returns :one
INSERT INTO users (email, active) VALUES (:email, :active);

// Generated — executes INSERT then SELECT by PK
public function createUser(string $email, int $active): User

// Usage
$user = $query->createUser(email: 'alice@example.com', active: 1);
echo $user->id;       // auto-increment PK from lastInsertId()
echo $user->email;    // 'alice@example.com'
echo $user->active;   // 1

Generated code (simplified)

public function createUser(string $email, int $active): User
{
    // Execute the INSERT
    $stmt = $this->pdo->prepare('INSERT INTO users (email, active) VALUES (:email, :active)');
    $stmt->bindValue(':email',  $email,  PDO::PARAM_STR);
    $stmt->bindValue(':active', $active, PDO::PARAM_INT);
    $stmt->execute();

    // Fetch the newly created row by its primary key
    $__id = (int) $this->pdo->lastInsertId();
    $__selectStmt = $this->pdo->prepare('SELECT * FROM users WHERE id = :id');
    $__selectStmt->bindValue(':id', $__id, PDO::PARAM_INT);
    $__selectStmt->execute();
    $__row = $__selectStmt->fetch(PDO::FETCH_ASSOC);

    return User::fromRow($__row);
}

Primary key detection

The PK column is detected automatically from the schema (in priority order): inline PRIMARY KEY declaration, AUTO_INCREMENT column, or a column named id. If none match, generation fails with a clear error.

@cte v2.13.0

Declares a named, reusable CTE inside a dedicated .sql file. Unlike query annotations (which annotate a single query), @cte is a block header — it names the CTE that follows and has no corresponding query annotation in .sql query files.

A single file can contain any number of @cte blocks, each ending at the next @cte or at EOF:

-- database/ctes/users.sql

-- @cte active_users
-- Users that are active and have the 'client' role.
-- Used by: orders, payments, reports modules.
SELECT
    id,
    email,
    role,
    created_at
FROM users
WHERE active = 1
  AND role = 'client';

-- @cte active_admins
-- Staff members with admin privileges.
SELECT
    id,
    email,
    role
FROM users
WHERE active = 1
  AND role IN ('admin', 'superadmin');

-- @cte new_users_30d
-- Users registered in the last 30 days, regardless of role.
SELECT id, email, created_at
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

CTE files are pure SQL — no PHP, no YAML, no special syntax beyond the -- @cte name block header. They can be shared across teams, checked into version control, and used as documentation for recurring business concepts.

Register files in sqlc.yaml under ctes::

version: 1
engine: mysql

schema:
  - database/schema.sql

# Global CTEs — available to every target in this config
ctes:
  - database/ctes/users.sql
  - database/ctes/orders.sql

targets:
  - namespace: App\Reports
    queries:
      - database/queries/reports.sql
    out: generated/Reports/
    ctes:
      - database/ctes/billing.sql   # per-target — merged with global above

@use v2.13.0

Injects one or more shared CTEs (declared with @cte) into a query as a WITH clause. sqlc-php resolves and prepends the WITH name AS (...) block before analysis — the Analyzer, Resolver, and all Generators receive the complete SQL transparently.

Syntax

-- Single CTE
-- @use active_users

-- Multiple on one line (comma-separated)
-- @use active_users, recent_orders

-- Multiple on separate lines (equivalent)
-- @use active_users
-- @use recent_orders

Use case 1 — Reusing a business rule across modules

The definition of "active user" lives in one place. Any query in any module can reference it without copy-pasting the WHERE conditions:

-- database/queries/orders.sql

-- @name ListActiveUserOrders
-- @class Orders
-- @returns :many
-- @use active_users
SELECT
    orders.id,
    orders.total,
    orders.status,
    orders.created_at
FROM orders
INNER JOIN active_users ON active_users.id = orders.user_id
WHERE orders.status != 'cancelled'
ORDER BY orders.created_at DESC;

-- @name CountActiveUserOrders
-- @class Orders
-- @returns :one
-- @use active_users
SELECT COUNT(*) AS total
FROM orders
INNER JOIN active_users ON active_users.id = orders.user_id;

-- database/queries/payments.sql

-- @name ListActiveUserPayments
-- @class Payments
-- @returns :many
-- @use active_users             ← same CTE, different module
SELECT
    payments.id,
    payments.amount,
    payments.created_at
FROM payments
INNER JOIN active_users ON active_users.id = payments.user_id;

Use case 2 — Composing multiple CTEs

A report that needs both active users and recent orders uses both CTEs together. The generated SQL chains them automatically:

-- @name ActiveUserOrderSummary
-- @class Reports
-- @returns :many
-- @use active_users, recent_orders
SELECT
    active_users.email,
    COUNT(recent_orders.id)    AS order_count,
    SUM(recent_orders.total)   AS total_spent
FROM active_users
LEFT JOIN recent_orders ON recent_orders.user_id = active_users.id
GROUP BY active_users.id, active_users.email
ORDER BY total_spent DESC;

Effective SQL injected before the Analyzer:

WITH
active_users AS (
    SELECT id, email, role, created_at
    FROM users
    WHERE active = 1 AND role = 'client'
),
recent_orders AS (
    SELECT id, user_id, total
    FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT
    active_users.email,
    COUNT(recent_orders.id)    AS order_count,
    SUM(recent_orders.total)   AS total_spent
FROM active_users
LEFT JOIN recent_orders ON recent_orders.user_id = active_users.id
GROUP BY active_users.id, active_users.email
ORDER BY total_spent DESC;

Use case 3 — Combined with @searchable

@use works transparently with all other annotations. Adding @searchable generates a typed Criteria class for the full query including the injected CTE — column resolution, table.column qualification, and Criteria generation all work normally:

-- @name ListActiveUserOrders
-- @class Orders
-- @returns :many
-- @use active_users
-- @searchable
SELECT
    orders.id,
    orders.total,
    orders.status,
    active_users.email AS user_email
FROM orders
INNER JOIN active_users ON active_users.id = orders.user_id;

// Generated usage:
$orders = $query->listActiveUserOrders(
    (new OrdersCriteria())
        ->whereStatusEq('pending')
        ->whereOrdersTotalGt(100.00)
        ->orderByCreatedAt('DESC')
);

Use case 4 — Combined with @json:many

Aggregate child rows as a typed JSON array while filtering via a shared CTE:

-- @name GetActiveUserWithOrders
-- @class Users
-- @returns :one
-- @use active_users
-- @json:many orders Order
SELECT
    active_users.id,
    active_users.email,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', orders.id, 'total', orders.total, 'status', orders.status)
    ) AS orders
FROM active_users
LEFT JOIN orders ON orders.user_id = active_users.id
WHERE active_users.id = :id
GROUP BY active_users.id;

// Generated:
readonly class GetActiveUserWithOrdersRow
{
    public function __construct(
        public int    $id,
        public string $email,
        /** @var Order[] */
        public array  $orders,
    ) {}
}

Use case 5 — Recursive CTE for hierarchical data

Shared CTEs support any valid MySQL CTE syntax, including RECURSIVE — useful for category trees, org charts, or multi-level BOMs:

-- database/ctes/categories.sql

-- @cte category_tree
-- Full recursive category tree from root to leaf.
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, parent_id, depth FROM category_tree

-- @name ListProductsInCategory
-- @class Products
-- @returns :many
-- @use category_tree
SELECT
    products.id,
    products.name,
    category_tree.name  AS category_name,
    category_tree.depth AS category_depth
FROM products
INNER JOIN category_tree ON category_tree.id = products.category_id
ORDER BY category_tree.depth, products.name;

@partial — optional UPDATE fields (PATCH pattern)

Marks an UPDATE query as a partial update. Any parameter that appears inside a COALESCE(:param, column) expression in the SET clause becomes optional (?type $param = null) in the generated method. When null is passed, the column is left unchanged — no PHP-side conditionals required.

Parameters in the WHERE clause remain required. Only valid on :exec UPDATE queries.

-- @name PatchUser
-- @partial
-- @returns :exec
UPDATE users SET
  email  = COALESCE(:email,  email),
  name   = COALESCE(:name,   name),
  active = COALESCE(:active, active)
WHERE id = :id;

// Generated method — required params first, optional params last
public function patchUser(
    int     $id,                   // WHERE param — required
    ?string $email  = null,        // SET param   — optional
    ?string $name   = null,        // SET param   — optional
    ?int    $active = null,        // SET param   — optional
): void

// Update only the email — name and active are left unchanged
$query->patchUser(id: 1, email: 'new@example.com');

// Update only active status
$query->patchUser(id: 1, active: 0);

// Update multiple fields at once
$query->patchUser(id: 1, email: 'new@example.com', name: 'Alice', active: 1);

// WHERE params can also be composite
// UPDATE users SET name = COALESCE(:name, name) WHERE id = :id AND tenant_id = :tenant_id
$query->patchUser(id: 1, tenant_id: 42, name: 'Bob');

How it works

@partial does not rewrite the SQL. The COALESCE is written by you and is the source of truth: when PDO binds null to :email, COALESCE(NULL, email) evaluates to the existing column value — effectively a no-op for that field. The generator detects which params appear inside COALESCE(:param, ...) in the SET region and marks them optional.

Comparison with @optional

-- @name PatchActiveUsers
-- @partial
-- @optional country_id      ← optional WHERE filter (rewritten by SqlRewriter)
-- @returns :exec
UPDATE users SET
  name = COALESCE(:name, name)
WHERE active = 1 AND country_id = :country_id;

// Filter by country (optional) and update name (optional)
public function patchActiveUsers(
    ?int    $country_id = null,   // WHERE — optional
    ?string $name       = null,   // SET   — optional
): void

Return types reference

:cursor v2.11.2

Keyset (cursor) pagination. Unlike offset pagination (:paginated), cursor pagination is O(1) regardless of page depth — it uses a WHERE clause anchored to the last seen values instead of scanning and discarding rows with OFFSET. Returns a CursorResult with items, hasMore/hasPrev flags, and opaque nextCursor/prevCursor tokens for bidirectional navigation.

Requires @cursor col [DIR], col [DIR] to declare the cursor columns.

-- @name ListOrders
-- @class Order
-- @cursor created_at DESC, id DESC
-- @returns :cursor
SELECT id, user_id, total, created_at
FROM orders
WHERE user_id = :userId
ORDER BY created_at DESC, id DESC;

// Generated signature
public function listOrders(int $userId, ?string $after = null, int $limit = 20): CursorResult

// Page 1 — no cursor
$page1 = $repo->listOrders(userId: 42);

// Page 2 — pass nextCursor from previous page
$page2 = $repo->listOrders(userId: 42, after: $page1->nextCursor);

// CursorResult properties
$page1->items;       // Order[] — rows for this page
$page1->hasMore;     // bool — true when next page exists
$page1->hasPrev;     // bool — true when previous page exists (false on first page)
$page1->nextCursor;  // string|null — pass as $after; null on last page
$page1->prevCursor;  // string|null — pass as $before; null on first page

// Navigate backward
$back = $repo->listOrders(userId: 42, before: $page2->prevCursor);

// Debug: decode the cursor token
CursorResult::decodeCursor($page1->nextCursor);
// → ['created_at' => '2024-01-15 10:30:00', 'id' => 42]

See the Cursor pagination guide for parameter combinations, @optional, @searchable, and CursorResult details.

:many

Returns all matching rows as an array. Returns an empty array when no rows match — never throws.

-- @name ListActiveUsers
-- @returns :many
SELECT * FROM users WHERE active = 1;

/** @return User[] */
public function listActiveUsers(): array

When the SELECT includes columns from multiple tables (JOIN), sqlc-php generates a result DTO (ListActiveUsersRow) instead of the model class.

:many-paginated

Like :many but adds ?int $limit = null and int $offset = 0 parameters. When $limit is null (the default), all rows are returned — no LIMIT clause is applied. Two prepared statements are generated internally (one with, one without LIMIT).

-- @name ListUsers
-- @returns :many-paginated
SELECT * FROM users ORDER BY created_at DESC;

/** @return User[] */
public function listUsers(?int $limit = null, int $offset = 0): array

$all   = $query->listUsers();               // all rows
$page1 = $query->listUsers(20);             // first 20
$page2 = $query->listUsers(20, 20);         // rows 21-40

Combine with @counted to get the total for pagination UI. Combine with @searchable for dynamic filters.

:one

Returns a single row. Throws RuntimeException if the query returns no rows. Use when the row is guaranteed to exist (e.g., lookup by primary key with a foreign key constraint).

-- @name GetUser
-- @returns :one
SELECT * FROM users WHERE id = :id;

public function getUser(int $id): User   // throws if not found

:opt

Returns a single row or null. Use when the row may not exist.

-- @name FindUserByEmail
-- @returns :opt
SELECT * FROM users WHERE email = :email;

public function findUserByEmail(string $email): ?User

:exec

For INSERT, UPDATE, DELETE. Returns void.

-- @name CreateUser
-- @returns :exec
INSERT INTO users (email, active) VALUES (:email, :active);

-- @name DeleteUser
-- @returns :exec
DELETE FROM users WHERE id = :id;

-- @name UpdateActive
-- @returns :exec
UPDATE users SET active = :active WHERE id = :id;

public function createUser(string $email, int $active): void
public function deleteUser(int $id): void
public function updateActive(int $active, int $id): void

:batch

Executes the same query once per row in an array, inside a single transaction. Returns the total number of rows affected. Rolls back automatically on failure.

-- @name InsertUsers
-- @returns :batch
INSERT INTO users (email, active) VALUES (:email, :active);

/** @param array $rows */
public function insertUsers(array $rows): int

$count = $query->insertUsers([
    ['email' => 'a@example.com', 'active' => 1],
    ['email' => 'b@example.com', 'active' => 1],
]);

:transaction

Calls multiple existing :exec methods of the same Query class inside one database transaction. Requires @calls and @class.

-- @name DebitAccount
-- @class Transfer
-- @returns :exec
UPDATE accounts SET balance = balance - :amount WHERE id = :account_id;

-- @name CreditAccount
-- @class Transfer
-- @returns :exec
UPDATE accounts SET balance = balance + :amount WHERE id = :account_id;

-- @name TransferFunds
-- @class Transfer
-- @calls debitAccount,creditAccount
-- @returns :transaction

public function transferFunds(int $amount, int $account_id): void
{
    $this->pdo->beginTransaction();
    try {
        $this->debitAccount($amount, $account_id);
        $this->creditAccount($amount, $account_id);
        $this->pdo->commit();
    } catch (\Throwable $e) {
        $this->pdo->rollBack();
        throw $e;
    }
}

CLI reference

php vendor/bin/sqlc-php [options] [config]

Examples

php vendor/bin/sqlc-php sqlc.yaml
php vendor/bin/sqlc-php --verify sqlc.yaml
php vendor/bin/sqlc-php --dry-run sqlc.yaml
php vendor/bin/sqlc-php --diff sqlc.yaml
php vendor/bin/sqlc-php --watch sqlc.yaml
php vendor/bin/sqlc-php --watch --interval=250 sqlc.yaml
php vendor/bin/sqlc-php --generate-schema sqlc.yaml
php vendor/bin/sqlc-php --generate-schema --schema-output=db/schema.sql sqlc.yaml
php vendor/bin/sqlc-php --version

Pattern: Basic SELECT

SELECT * — returns the model class

When the query selects all columns from a single table, sqlc-php returns the generated model class directly.

-- @name ListUsers
-- @returns :many
SELECT * FROM users;

-- @name GetUser
-- @returns :one
SELECT * FROM users WHERE id = :id;

-- @name FindByEmail
-- @returns :opt
SELECT * FROM users WHERE email = :email;

SELECT specific columns — generates a result DTO

When the query selects a subset of columns (or columns from multiple tables), a result DTO is generated automatically.

-- @name ListUserSummary
-- @returns :many
SELECT id, email, active FROM users;

Generates ListUserSummaryRow.php with exactly those three properties. Use @dto to rename it.

Pattern: JOINs & DTOs

-- @name GetUserWithRole
-- @dto UserWithRole
-- @embed Role role__
-- @returns :one
SELECT
    u.id,
    u.email,
    r.name AS role__name,
    r.slug AS role__slug
FROM users u
JOIN roles r ON r.id = u.role_id
WHERE u.id = :id;

// Generated: UserWithRole.php
readonly class UserWithRole {
    public function __construct(
        public int    $id,
        public string $email,
        public Role   $role,  // ← nested via @embed
    ) {}
}

// Generated: Role.php
readonly class Role {
    public function __construct(
        public string $name,
        public string $slug,
    ) {}
}

Pattern: Aggregates and IF()

sqlc-php infers types from SQL expressions. Numeric literals (0, 42) resolve to int. Arithmetic expressions (col - MAX(col)) resolve from the left operand. IF(cond, true_val, false_val) resolves from the true branch.

-- @name ListBillingConfig
-- @class BillingConfig
-- @returns :many
SELECT
    bc.id,
    bc.end_num,
    MAX(m.voucher_number)                                           AS max_voucher,
    IF(MAX(m.voucher_number) >= bc.end_num, 0,
       bc.end_num - MAX(m.voucher_number))                         AS remaining
FROM billing_config bc
LEFT JOIN memory m ON m.voucher_type = CASE
    WHEN bc.country_id = 164 THEN 'factExp' ELSE 'factTicket' END
GROUP BY bc.id;

Result: int $remaining (not string) because 0 is a numeric literal and bc.end_num - MAX(...) resolves from bc.end_num which is INT.

Expression type inference

Pattern: IN() clauses

Use a single named placeholder for an IN() clause. sqlc-php expands it to individual placeholders at runtime.

-- @name GetUsersByIds
-- @returns :many
SELECT * FROM users WHERE id IN (:ids);

/** @param int[] $ids */
public function getUsersByIds(array $ids): array

$users = $query->getUsersByIds([1, 2, 3, 4]);

Pattern: MySQL ENUM → PHP backed enum

MySQL ENUM columns are automatically mapped to PHP 8.1+ backed enums.

-- schema.sql
CREATE TABLE orders (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending'
);

// Generated: OrderStatus.php
enum OrderStatus: string {
    case Pending   = 'pending';
    case Paid      = 'paid';
    case Cancelled = 'cancelled';
}

// In Order.php model:
public OrderStatus $status

Pattern: JSON columns

CREATE TABLE products (
    id       INT PRIMARY KEY,
    metadata JSON NULL
);

// Generated model property
public ?array $metadata   // automatically json_decode()'d in fromRow()

Generated file structure

Model class example (User.php)

// Generated — do not edit manually
readonly class User
{
    public function __construct(
        public int    $id,
        public string $email,
        public int    $active,
        public ?string $name,
        public \DateTimeImmutable $createdAt,
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            id:        (int)    $row['id'],
            email:              $row['email'],
            active:    (int)    $row['active'],
            name:               $row['name'] ?? null,
            createdAt: new \DateTimeImmutable($row['created_at']),
        );
    }
}

Query class example (UserQuery.php)

// Generated — do not edit manually
class UserQuery implements UserQueryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    /** @return User[] */
    public function listActiveUsers(): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE active = 1');
        $stmt->execute();
        return array_map(
            static fn(array $row): User => User::fromRow($row),
            $stmt->fetchAll(PDO::FETCH_ASSOC),
        );
    }

    public function getUser(int $id): User
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = :id');
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row === false) {
            throw new RuntimeException("getUser: no row found for id={$id}");
        }
        return User::fromRow($row);
    }
}

Interface example (UserQueryInterface.php)

// Generated — do not edit manually
interface UserQueryInterface
{
    /** @return User[] */
    public function listActiveUsers(): array;

    public function getUser(int $id): User;

    public function findByEmail(string $email): ?User;
}

Basic usage

$pdo = new PDO('mysql:host=localhost;dbname=myapp', 'user', 'pass', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$userQuery = new UserQuery($pdo);

$users = $userQuery->listActiveUsers();
$user  = $userQuery->getUser(1);
$found = $userQuery->findByEmail('hello@example.com');

lastQuery(), logger & afterQuery hook

Every generated Query class exposes three optional features for observability: a lastQuery() accessor that records the last executed SQL, a PSR-3 logger for automatic query logging, and an $afterQuery callable hook for custom integrations like Laravel Debugbar.

Constructor signature

public function __construct(
    PDO               $pdo,
    ?LoggerInterface  $logger     = null,  // PSR-3 — Telescope, files, etc.
    ?Closure          $afterQuery = null,  // custom hook — Debugbar, metrics, etc.
)

All parameters after $pdo are optional and default to null. Existing code that constructs new UserRepository($pdo) continues to work without any changes.

lastQuery() — post-execution inspection

$users = $repo->listActiveUsers(active: 1);
$q     = $repo->lastQuery();

echo $q->toString();       // SQL with placeholders — safe to log
echo $q->toDebugSql();     // SQL with values — debug only
$q->bindings();            // [':active' => [1, PDO::PARAM_INT], ...]
$q->values();              // [':active' => 1]
$q->queryName;             // 'listActiveUsers'
$q->durationMs;            // 4.217 — execution time in milliseconds (float)
$q->cacheKey();            // stable md5 for caching

Execution timing

Every generated method wraps $stmt->execute() with hrtime(true) to measure the real round-trip time to the database. The result is stored as durationMs (milliseconds, float precision) in the QueryObject.

// hrtime(true) → nanoseconds. Dividing by 1_000_000 → milliseconds.
// $__t0 = hrtime(true);
// $stmt->execute();
// $this->lastQuery = $this->lastQuery->withDuration((hrtime(true) - $__t0) / 1_000_000);

$q = $repo->lastQuery();
echo $q->durationMs;       // e.g. 4.217 ms
echo round($q->durationMs, 1) . 'ms';  // "4.2ms"

// Detect slow queries in the afterQuery hook
$repo = new UserRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q): void {
        if ($q->durationMs > 100) {
            $this->logger->warning(
                "Slow query [{$q->queryName}]: {$q->durationMs}ms",
                ['sql' => $q->toString(), 'bindings' => $q->values()],
            );
        }
    },
);

PSR-3 logger — automatic query logging with duration

When a PSR-3 logger is provided, every executed method logs at DEBUG level with the format queryName [Xms]: SQL.

$repo = new UserRepository(pdo: $pdo, logger: $logger);

// After $repo->listActiveUsers(active: 1), the logger receives:
// message: "listActiveUsers [4.217ms]: SELECT * FROM users WHERE active = :active"
// context: [':active' => 1]

afterQuery hook — custom integrations with timing

// Laravel Debugbar — shows query name, SQL, and duration
$repo = new UserRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q) use ($app): void {
        $collector = \Debugbar::getCollector('queries');

        // Option A — toDebugSql() + empty bindings (recommended)
        // SQL has values already interpolated; no further processing by Debugbar.
        // Works correctly with @optional queries (avoids '[,1]' display bug).
        $qe = new QueryExecuted(
            $q->toDebugSql(),      // "SELECT ... WHERE (NULL IS NULL OR active = 1)"
            [],
            $q->durationMs,
            $app->make('db')->connection(),
        );

        // Option B — toString() + toDebugBindings()
        // toDebugBindings() returns a flat array of values, filtering out internal
        // _chk params (@optional) and :limit/:offset (:many-paginated).
        // $qe = new QueryExecuted(
        //     $q->toString(),           // SQL with :placeholders
        //     $q->toDebugBindings(),    // [1, 164, ...] — flat, _chk filtered
        //     $q->durationMs,
        //     $app->make('db')->connection(),
        // );

        $collector->addQuery($qe);
    },
);

// Slow query alerting
$repo = new UserRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q): void {
        if ($q->durationMs > 100) {
            Log::warning("Slow query: {$q->queryName} took {$q->durationMs}ms");
        }
    },
);

// Both logger + hook
$repo = new UserRepository(
    pdo:        $pdo,
    logger:     app(LoggerInterface::class),        // PSR-3 → Telescope
    afterQuery: fn(QueryObject $q) =>
        \Debugbar::addMessage(
            sprintf('[%.1fms] %s', $q->durationMs, $q->toString()),
            'queries'
        ),
);

Testing SQL without a database

$pdo  = $this->createMock(PDO::class);
$stmt = $this->createMock(PDOStatement::class);
$stmt->method('fetchAll')->willReturn([]);
$pdo->method('prepare')->willReturn($stmt);

$repo  = new UserRepository($pdo);
$users = $repo->listActiveUsers(active: 1);

$this->assertStringContainsString('WHERE', $repo->lastQuery()->toString());
$this->assertSame(1, $repo->lastQuery()->values()[':active']);
// durationMs will be near 0 (mock execute is instant)

QueryObject API reference

Laravel integration

1. Bind in a Service Provider

// app/Providers/AppServiceProvider.php
use App\Database\UserQueryInterface;
use App\Database\UserQuery;

public function register(): void
{
    $this->app->bind(UserQueryInterface::class, function ($app) {
        return new UserQuery($app['db']->getPdo());
    });
}

2. Inject into a controller

class UserController extends Controller
{
    public function __construct(
        private readonly UserQueryInterface $userQuery,
    ) {}

    public function index(): JsonResponse
    {
        return response()->json($this->userQuery->listActiveUsers());
    }
}

3. Testing with the interface

$this->app->bind(UserQueryInterface::class, fn() => new class implements UserQueryInterface {
    public function listActiveUsers(): array { return []; }
    public function getUser(int $id): User { throw new RuntimeException('not found'); }
    // ...
});

Running the test suite

# PHPUnit test suite (960 tests)
./vendor/bin/phpunit

Source structure

Extension traits

Every generated model and DTO is a readonly class that sqlc-php overwrites on every generate run. Extension traits let you add domain methods — isActive(), toApiArray(), isExpired() — without losing them on re-generation.

Activation

Add extensions: to the out: map. One line activates the entire feature:

targets:
  - namespace: "App\\Database"
    scoped_dtos: true           # extensions mirror this structure
    out:
      queries:    app/Database/Repositories
      models:     app/Database/Models
      dtos:       app/Database/DTOs
      enums:      app/Database/Enums
      interfaces: app/Database/Contracts
      criterias:  app/Database/Criterias
      extensions: app/Database/Extensions  # ← add this line

Without this line the feature is completely inactive — full backward compatibility.

What gets generated

app/Database/
  Models/
    User.php                   ← readonly class User { use UserExtension; ... }
    BillingConfig.php          ← readonly class BillingConfig { use BillingConfigExtension; ... }

  DTOs/
    ReserveBilling/
      GetByReserveId/
        ReserveBilling.php     ← use ReserveBillingExtension
        Customer.php           ← use CustomerExtension
        Reserve.php            ← use ReserveExtension

  Extensions/                  ← these files are NEVER overwritten
    Models/
      UserExtension.php
      BillingConfigExtension.php
    DTOs/                      ← mirrors DTOs/ structure exactly
      ReserveBilling/
        GetByReserveId/
          ReserveBillingExtension.php
          CustomerExtension.php
          ReserveExtension.php

The generated model (with extensions active)

use App\Database\Extensions\Models\UserExtension;

/**
 * DTO for the `users` table.
 * Generated by sqlc-php — do not edit manually.
 *
 * @mixin UserExtension
 */
readonly class User
{
    use UserExtension;

    public function __construct(
        public int    $id,
        public string $email,
        public int    $active,
    ) {}

    public static function fromRow(array $row): self { ... }
}

The scaffold (written once — then yours)

The scaffold uses two complementary docblock tags to give every IDE and static analyser complete type knowledge of the host class properties inside the trait:

namespace App\Database\Extensions\Models;

/**
 * Extension trait for the `User` model.
 * Extends the `User` table model.
 *
 * ╔══════════════════════════════════════════════════════════╗
 * ║  This file is yours — sqlc-php will NEVER overwrite it.  ║
 * ╚══════════════════════════════════════════════════════════╝
 *
 * Add domain methods that operate on `User` properties.
 * All properties of the model are available via $this.
 *
 * @mixin \App\Database\Models\User
 * @property int    $id
 * @property string $email
 * @property int    $active
 *
 * Example:
    // public function isActive(): bool
    // {
    //     return $this->active === 1;
    // }
 */
trait UserExtension
{
    // Your methods here
}

How the docblock tags provide type context

Two tags work together to cover all major IDEs and static analysers:

Use cases

// 1. Domain logic
/**
 * @mixin \App\Database\Models\User
 * @property int    $id
 * @property string $email
 * @property int    $active
 */
trait UserExtension
{
    public function isActive(): bool
    {
        return $this->active === 1;  // $this->active is int — fully typed
    }

    public function displayName(): string
    {
        return ucwords(explode('@', $this->email)[0]);  // $this->email is string
    }
}

// 2. Temporal logic
/**
 * @mixin \App\Database\Models\BillingConfig
 * @property \DateTimeImmutable  $start_date
 * @property ?\DateTimeImmutable $expiration_date
 */
trait BillingConfigExtension
{
    public function isExpired(): bool
    {
        return $this->expiration_date !== null
            && $this->expiration_date < new \DateTimeImmutable();
    }
}

// 3. API serialisation
/**
 * @mixin \App\Database\DTOs\Order\GetDetails\OrderDetails
 * @property int    $id
 * @property float  $total
 * @property string $status
 */
trait OrderDetailsExtension
{
    public function toApiArray(): array
    {
        return [
            'id'     => $this->id,
            'total'  => number_format($this->total, 2),
            'status' => $this->status,
        ];
    }
}

// 4. Implementing a domain interface
/**
 * @mixin \App\Database\Models\User
 * @property string $role
 */
trait UserExtension
{
    public function hasRole(string $role): bool { return $this->role === $role; }
}

// Then in sqlc.yaml the query class returns User,
// and User implements the interface: readonly class User implements HasRoles { use UserExtension; }

Cursor pagination guide v2.11.2

This guide covers all cursor pagination use cases: basic usage, required params, optional params, @searchable criteria, and the CursorResult API.

1 — Basic (no user params)

-- @name ListOrders
-- @class Order
-- @cursor created_at DESC, id DESC
-- @returns :cursor
SELECT id, user_id, total, created_at
FROM orders
ORDER BY created_at DESC, id DESC;

public function listOrders(?string $after = null, ?string $before = null, int $limit = 20): CursorResult

// Forward navigation
$page1 = $repo->listOrders();
$page2 = $repo->listOrders(after: $page1->nextCursor);
$page3 = $repo->listOrders(after: $page2->nextCursor);

// Backward navigation
$back  = $repo->listOrders(before: $page2->prevCursor);  // returns page 1 items

// CursorResult
$page2->hasMore;     // true — page 3 exists
$page2->hasPrev;     // true — page 1 exists
$page2->nextCursor;  // pass as $after  → page 3
$page2->prevCursor;  // pass as $before → page 1

2 — With required parameters

Required params appear before $after and $limit in the signature. Pass the same values on every page — the cursor only encodes the position, not the filter context.

-- @name ListOrdersByUser
-- @class Order
-- @cursor created_at DESC, id DESC
-- @returns :cursor
SELECT id, user_id, total, created_at
FROM orders
WHERE user_id = :userId
ORDER BY created_at DESC, id DESC;

public function listOrdersByUser(int $userId, ?string $after = null, ?string $before = null, int $limit = 20): CursorResult

$page1 = $repo->listOrdersByUser(userId: 42);
$page2 = $repo->listOrdersByUser(userId: 42, after: $page1->nextCursor);
$back  = $repo->listOrdersByUser(userId: 42, before: $page2->prevCursor);

3 — With optional parameters (@optional)

Write the SQL naturally and let @optional handle the rewrite. Do not manually write the (:param_chk IS NULL OR ...) pattern — the rewriter handles it automatically, and combining both would double the condition.

-- @name ListOrdersByStatus
-- @class Order
-- @cursor created_at DESC, id DESC
-- @optional status
-- @returns :cursor
SELECT id, total, status, created_at
FROM orders
WHERE status = :status          ← natural form; @optional rewrites it
ORDER BY created_at DESC, id DESC;

public function listOrdersByStatus(?string $status = null, ?string $after = null, ?string $before = null, int $limit = 20): CursorResult

// Filter applied — forward and backward
$page1 = $repo->listOrdersByStatus(status: 'paid');
$page2 = $repo->listOrdersByStatus(status: 'paid', after: $page1->nextCursor);
$back  = $repo->listOrdersByStatus(status: 'paid', before: $page2->prevCursor);

// No filter
$all = $repo->listOrdersByStatus();

4 — With @searchable (dynamic WHERE filters)

When @cursor and @searchable are combined, the generated Criteria class exposes where() and orGroup() but not orderBy(). The ORDER BY is fixed by @cursor — allowing dynamic ordering would invalidate the cursor between pages.

-- @name ListOrders
-- @class Order
-- @searchable
-- @cursor created_at DESC, id DESC
-- @returns :cursor
SELECT id, user_id, total, status, created_at
FROM orders
ORDER BY created_at DESC, id DESC;

public function listOrders(?OrderCriteria $criteria = null, ?string $after = null, ?string $before = null, int $limit = 20): CursorResult

$criteria = (new OrderCriteria)
    ->whereStatus('=', 'paid')
    ->whereUserId('=', 42);

$page1 = $repo->listOrders($criteria);
$page2 = $repo->listOrders($criteria, after: $page1->nextCursor);
$back  = $repo->listOrders($criteria, before: $page2->prevCursor);

5 — With @counted (total row count)

Add @counted to generate a companion {name}Count() method that returns the total number of rows matching the user filters, independent of cursor position. The count uses the original user SQL wrapped in a subquery — cursor WHERE conditions are excluded.

-- @name ListOrders
-- @class Order
-- @cursor created_at DESC, id DESC
-- @counted                              ← generates listOrdersCount()
-- @returns :cursor
SELECT id, user_id, total, created_at
FROM orders
WHERE user_id = :userId
ORDER BY created_at DESC, id DESC;

// Generated — two independent methods
public function listOrders(int $userId, ?string $after = null, ?string $before = null, int $limit = 20): CursorResult
public function listOrdersCount(int $userId): int

// Recommended usage: call Count() once on the first page load only
$page1 = $repo->listOrders(userId: 42);
$total = $repo->listOrdersCount(userId: 42);  // ← one extra query, called once

// Subsequent pages — no COUNT needed
$page2 = $repo->listOrders(userId: 42, after: $page1->nextCursor);
$page3 = $repo->listOrders(userId: 42, after: $page2->nextCursor);

The COUNT SQL wraps the original user query in a subquery, so it correctly handles JOINs, @optional conditions, and subqueries in the original SQL:

-- Generated COUNT SQL
SELECT COUNT(*) AS _total
FROM (
    SELECT id, user_id, total, created_at FROM orders WHERE user_id = :userId
) AS _cursor_total

CursorResult API

readonly class CursorResult
{
    public array   $items;       // T[] — rows for this page (always in original ORDER BY direction)
    public bool    $hasMore;     // true when next page exists (forward)
    public bool    $hasPrev;     // true when previous page exists; false on the very first page
    public ?string $nextCursor;  // pass as $after to fetch next page; null on last page
    public ?string $prevCursor;  // pass as $before to fetch previous page; null on first page
}

// Decode a cursor for debugging or testing
CursorResult::decodeCursor($page1->nextCursor);
// → ['created_at' => '2024-01-15 10:30:00', 'id' => 42]

// Encode (used internally; available if needed)
$token = CursorResult::encodeCursor(['created_at' => '2024-01-15', 'id' => 42]);

How forward and backward navigation works

For @cursor created_at DESC, id DESC the generator produces two SQL strings internally:

1. Forward ($after): injects AND (created_at, id) < (:cursor) before ORDER BY created_at DESC, id DESC. The _chk IS NULL guard makes the condition a no-op on the first page.
2. Backward ($before): injects AND (created_at, id) > (:cursor) before ORDER BY created_at ASC, id ASC (all directions inverted), then calls array_reverse() to restore the original DESC order.
3. Both directions fetch $limit + 1 rows to detect whether another page exists, without a COUNT query.
4. nextCursor is built from the last row's cursor column values. prevCursor is built from the first row.

Criteria::fromArray() v2.12.0

Build a Criteria instance from a plain array of positional filter tuples. Useful when filters come from an external source (HTTP request, session, database) or when you want to construct criteria programmatically without method chaining.

Syntax

SomeCriteria::fromArray([
    [column, operator, value],   // most operators
    [column, operator],          // IS NULL / IS NOT NULL (no value needed)
    [column, 'in',      [...]],  // IN: value is an array
    [column, 'not_in',  [...]],  // NOT IN: value is an array
    [column, 'between', [from, to]], // BETWEEN: value is [from, to]
]);

Full example

$criteria = OrderCriteria::fromArray([
    ['user_id',    '=',         42],
    ['status',     '!=',        'cancelled'],
    ['total',      '>=',        100.0],
    ['name',       'like',      'john'],         // → LIKE '%john%'
    ['name',       'starts_with', 'jo'],         // → LIKE 'jo%'
    ['tag',        'in',        ['vip', 'gold']], // → IN (:tag_0, :tag_1)
    ['deleted_at', 'is_null'],                   // no value — 2-element tuple
    ['created_at', 'between',   ['2024-01-01', '2024-12-31']],
]);

$results = $repo->listOrders($criteria);

Supported operators

Column validation

fromArray() validates column names against the same $allowedColumns list used by orderBy(). An unknown column throws InvalidArgumentException immediately — before any SQL is constructed.

// Throws: "column 'nonexistent' is not allowed. Allowed: id, user_id, status, ..."
OrderCriteria::fromArray([['nonexistent', '=', 1]]);

$base = OrderCriteria::fromArray([['user_id', '=', 42]]);
$with_or = $base->orGroup(fn($c) => $c->whereStatusEq('vip'));

Combining fromArray() with the fluent API

fromArray() returns the same immutable Criteria instance — you can chain fluent methods on the result:

$criteria = OrderCriteria::fromArray($request->input('filters', []))
    ->whereDeletedAtIsNull()       // always exclude deleted
    ->orderByCreatedAt('DESC');    // always sort newest first

Changelog

targets:
  - namespace: "App\\Database"
    scoped_dtos: true   # ← isolates DTOs and Criteria per query method
    out:
      queries:    app/Database/Repositories
      dtos:       app/Database/DTOs
      criterias:  app/Database/Criterias
      # DTOs/GetBillingDetails/BillingDetails.php       (namespace: App\Database\DTOs\GetBillingDetails)
      # DTOs/GetBillingDetails/BillingReserve.php       (namespace: App\Database\DTOs\GetBillingDetails)
      # DTOs/GetBillingWithDate/BillingWithDate.php     (namespace: App\Database\DTOs\GetBillingWithDate)
      # Criterias/GetByFilter/BillingCriteria.php       (namespace: App\Database\Criterias\GetByFilter)
      # Criterias/ListBySupplier/BillingCriteria.php    (namespace: App\Database\Criterias\ListBySupplier)

Error: @embed class 'BillingReserve' is declared in multiple queries with different column shapes.

  getBillingDetails   → id:int, total_price:float
  getBillingWithDate  → id:int, created_at:DateTimeImmutable

Solutions:
  1. Enable scoped_dtos: true in sqlc.yaml to isolate DTOs per query method.
  2. Use distinct class names:
       -- @embed BillingReserveDetails reserve__  (in getBillingDetails)
       -- @embed BillingReserveAlt reserve__      (in getBillingWithDate)
  3. Use the same columns in both queries if the same class is intended.

-- Before: return type was GetDetailsRow (wrong)
-- After:  return type is ReserveBilling (correct)
-- @name     GetDetails
-- @class    ReserveBilling
-- @dto      ReserveBilling
-- @embed    ReserveBillingReserve reserve__
-- @returns :one
SELECT reserve_billing.*,           -- table.* expands all billing columns
    reserve.id as reserve__id,      -- __ prefix = @embed column, ignored by model detector
    reserve.created_at as reserve__created_at
FROM reserve_billing
INNER JOIN reserve ON reserve_billing.reserve_id = reserve.id
WHERE reserve_billing.reserve_id = :id