PHPackages                             phpibe/sqlc-php - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. phpibe/sqlc-php

ActiveLibrary[Database & ORM](/categories/database)

phpibe/sqlc-php
===============

A PHP code generator inspired by sqlc

2.7.0(1w ago)1767↓33.3%MITPHPPHP >=8.3CI passing

Since May 21Pushed 2d ago1 watchersCompare

[ Source](https://github.com/phpibe/sqlc-php)[ Packagist](https://packagist.org/packages/phpibe/sqlc-php)[ RSS](/packages/phpibe-sqlc-php/feed)WikiDiscussions main Synced 1w ago

READMEChangelog (10)Dependencies (7)Versions (29)Used By (0)

sqlc-php |
==============================================

[](#sqlc-php--httpsphpibegithubiosqlc-php)

A PHP code generator inspired by [sqlc](https://sqlc.dev) for Go. It reads your SQL schema and annotated query files, and generates fully-typed PHP 8.4 classes that use PDO under the hood — no ORM, no magic, just plain objects derived directly from your database.

---

How it works
------------

[](#how-it-works)

```
schema.sql + queries.sql + sqlc.yaml
              ↓
         sqlc-php (CLI)
              ↓
   User.php · UserQuery.php · UserQueryInterface.php · OrderStatus.php

```

1. **Parse** — reads `CREATE TABLE` statements and builds a schema catalog.
2. **Analyze** — resolves every query's parameters and result columns against the catalog.
3. **Generate** — emits one `readonly` DTO per table, PHP backed enums for `ENUM` columns, one query class per `@group`, and optionally a matching interface per query class.

---

Requirements
------------

[](#requirements)

- PHP 8.3+
- PDO extension

---

Installation
------------

[](#installation)

```
composer require phpibe/sqlc-php
```

Then run the CLI from your project root:

```
php ./vendor/bin/sqlc-php sqlc.yaml                      # generate files
php ./vendor/bin/sqlc-php --dry-run  sqlc.yaml           # preview without writing
php ./vendor/bin/sqlc-php --diff     sqlc.yaml           # show what would change
php ./vendor/bin/sqlc-php --verify   sqlc.yaml           # CI check — exit 1 if stale
php ./vendor/bin/sqlc-php --watch    sqlc.yaml           # watch for changes, auto-regenerate
php ./vendor/bin/sqlc-php --watch --interval=250 sqlc.yaml  # custom poll interval (ms)
php ./vendor/bin/sqlc-php --version                      # print version and exit
```

---

Configuration — `sqlc.yaml`
---------------------------

[](#configuration--sqlcyaml)

```
version: "2"

# Schema files — one or many (required)
schema:
  - database/schema/users.sql
  - database/schema/orders.sql

# Global defaults — inherited by all targets unless overridden locally
engine:   mysql    # database engine (mysql supported; postgres planned for v1.7.0)
language: english  # english | spanish | french | portuguese | norwegian-bokmal | turkish

# Global type overrides — applied to all targets
type_overrides:
  - column:   "users.active"
    php_type: "bool"
  - db_type:  "TINYINT"
    php_type: "bool"
  # To use string instead of \DateTimeImmutable for date columns:
  # - db_type: "DATE"
  #   php_type: "string"

# Virtual tables — views or external tables not present in the schema files (optional)
virtual_tables:
  - name: user_summary
    columns:
      - { name: id,          type: INT }
      - { name: email,       type: VARCHAR }
      - { name: role_name,   type: VARCHAR, nullable: true }
      - { name: order_count, type: INT }

# Include additional YAML fragments — each can contain virtual_tables, type_overrides,
# and targets sections that are merged before the main file's values (optional)
includes:
  - config/views.yaml
  - config/overrides.yaml

# Output targets — one or more (required)
targets:
  - namespace: "App\\Database"
    out:       generated
    queries:
      - database/queries/users.sql
      - database/queries/orders.sql
    # generate_interfaces: true   ← default, omit unless you want false
    # engine:   mysql             ← override global engine for this target
    # language: spanish           ← override global language for this target
    # type_overrides:             ← merged on top of global overrides
    #   - column: "users.bio"
    #     php_type: "string"
```

### Minimal single-target config

[](#minimal-single-target-config)

```
version: "2"
schema: schema.sql
targets:
  - namespace: "App\\Database"
    out:       generated
    queries:   queries.sql
```

`generate_interfaces` defaults to `true` — interfaces are generated unless explicitly set to `false`:

```
targets:
  - namespace: "App\\Database"
    out:       generated
    queries:   queries.sql
    generate_interfaces: false   # disable only if not needed
```

### Multiple output targets

[](#multiple-output-targets)

`targets` accepts any number of entries — each produces a separate generation pass using the same parsed schema:

```
version: "2"
schema:
  - database/schema/users.sql
  - database/schema/orders.sql

engine:   mysql
language: english

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

targets:
  - namespace: "App\\Database\\Read"
    out:       generated/read
    queries:
      - database/queries/read/users.sql
      - database/queries/read/orders.sql

  - namespace: "App\\Database\\Write"
    out:       generated/write
    queries:
      - database/queries/write/users.sql
    generate_interfaces: false
    type_overrides:               # merged on top of global overrides
      - column: "users.active"
        php_type: "bool"
```

Each target inherits the global `engine`, `language`, and `type_overrides`. A target can override any of them locally.

### Multiple schema files

[](#multiple-schema-files)

`schema` accepts both a scalar string (single file) and a YAML list. All files are parsed and merged into a single catalog:

```
schema:
  - database/schema/users.sql
  - database/schema/orders.sql
  - database/schema/roles.sql
```

### Per-target queries

[](#per-target-queries)

Each target has its own `queries` list. `queries` accepts a scalar or a list:

```
targets:
  - namespace: "App\\Database"
    out:       generated
    queries:
      - database/queries/users.sql
      - database/queries/roles.sql
      - database/queries/orders.sql
```

The CLI prints a per-file count alongside the total:

```
Schema : database/schema/users.sql
Schema : database/schema/orders.sql
Schema : 3 table(s) — users, orders, roles

Target : App\Database → generated/
  Queries: 8 query(ies) from database/queries/users.sql
  Queries: 3 query(ies) from database/queries/orders.sql
  Queries: 11 total

```

### Inflection language

[](#inflection-language)

sqlc-php uses [doctrine/inflector](https://github.com/doctrine/inflector) to singularise table names when inferring class names. Set globally or per-target:

```
language: spanish   # global default

targets:
  - namespace: "App\\Spanish"
    out:       gen/es
    queries:   queries/es.sql
    # inherits language: spanish

  - namespace: "App\\French"
    out:       gen/fr
    queries:   queries/fr.sql
    language:  french   # override for this target
```

With `language: spanish`, tables like `usuarios`, `pedidos`, `categorias` produce `Usuario`, `Pedido`, `Categoria` without needing `@group` on every query.

Table name`english` (doctrine)`spanish``analyses``Analysis` ✅—`matrices``Matrix` ✅—`usuarios``Usuarios` ❌`Usuario` ✅`pedidos``Pedido` ✅`Pedido` ✅`users``User` ✅—The `@group` annotation always takes precedence over inferred names.

### virtual\_tables — views and external tables

[](#virtual_tables--views-and-external-tables)

`virtual_tables:` declares tables that exist in the database but have no `CREATE TABLE` in the schema files — views, materialized views, or tables from other schemas.

```
virtual_tables:
  - name: user_summary
    columns:
      - { name: id,          type: INT }
      - { name: email,       type: VARCHAR }
      - { name: role_name,   type: VARCHAR, nullable: true }
      - { name: order_count, type: INT }

  - name: monthly_revenue
    columns:
      - { name: month,   type: INT }
      - { name: revenue, type: DECIMAL }
```

**Nullability convention** — all columns are `NOT NULL` by default. Specify `nullable: true` only for columns that can be null. This is the inverse of schema parsing where `NOT NULL` must be explicit.

Virtual tables are registered in the `SchemaCatalog` for column type resolution. Queries against them work exactly like queries against real tables. The only difference: **no `Model` class is generated** for virtual tables.

```
-- @name ListUserSummaries
-- @returns :many
SELECT * FROM user_summary;
```

Generates `UserSummaryQuery.php` with correct column types, but no `UserSummary.php` model.

### includes — splitting the config

[](#includes--splitting-the-config)

`includes:` loads additional YAML fragments and merges their list fields (`virtual_tables:`, `type_overrides:`, `targets:`) into the main config. Scalar fields (`engine:`, `language:`) in include files are silently ignored.

```
# sqlc.yaml
includes:
  - config/views/user_views.yaml
  - config/views/order_views.yaml
  - config/overrides/timestamps.yaml
```

```
# config/views/user_views.yaml
virtual_tables:
  - name: user_summary
    columns:
      - { name: id,    type: INT }
      - { name: email, type: VARCHAR }
```

```
# config/views/order_views.yaml
virtual_tables:
  - name: order_summary
    columns:
      - { name: id,    type: INT }
      - { name: total, type: DECIMAL }
```

All `virtual_tables:` entries from all includes are accumulated. Multiple include files can each declare their own `virtual_tables:` — they are all merged before processing.

FieldBehaviour`virtual_tables:`Accumulated — all entries from all includes + main file`type_overrides:`Accumulated — includes first, main file appended last`targets:`Accumulated — includes first, main file appended last`engine:`, `language:`Ignored in includes — main file always controls scalars### Type override precedence

[](#type-override-precedence)

PriorityRuleDescription1`column`Exact `table.column` match — wins over everything2`db_type`Matches any column whose SQL type matches3DefaultBuilt-in SQL → PHP type mapping### Nullable override

[](#nullable-override)

Any `type_override` entry accepts an optional `nullable` field:

```
type_overrides:
  - column:   "users.deleted_at"
    php_type: "\\Carbon\\Carbon"
    nullable: true          # force nullable even if NOT NULL in schema

  - db_type:  "TIMESTAMP"
    php_type: "\\DateTimeImmutable"
    nullable: false         # force not-null regardless of schema

  - column:   "users.created_at"
    nullable: false         # only change nullability, keep default type
```

When `nullable` is omitted, nullability is inherited from the schema.

### Default SQL → PHP type mapping

[](#default-sql--php-type-mapping)

SQL typePHP typeNotes`INT`, `BIGINT`, `SMALLINT`, `TINYINT``int``DECIMAL`, `FLOAT`, `DOUBLE``float``VARCHAR`, `CHAR`, `TEXT``string``DATE`, `DATETIME`, `TIMESTAMP``\DateTimeImmutable``fromRow` uses `new \DateTimeImmutable(...)``TIME``string`no standard PHP time-interval type`JSON``array`hydrated via `json_decode` in `fromRow``ENUM(...)``EnumClass`generates a PHP 8.1 backed enum file`BOOLEAN``bool`---

Annotating queries
------------------

[](#annotating-queries)

Every query must have at minimum a `@name` and a `@returns` annotation, written as SQL comments:

```
-- @name    MethodName          required — PHP method name (camelCase)
-- @group   ClassName           optional — query class name; inferred from FROM table if omitted
-- @returns :many               required — :many | :one | :opt | :exec
-- @param   userId users.id     optional — explicit type override for a named parameter
-- @optional paramName          optional — passing null skips the filter condition entirely
-- @deprecated reason           optional — marks the generated method as @deprecated
-- @nillable columnAlias        optional — forces a result column to be nullable in the DTO
-- @embed    ClassName prefix_  optional — groups prefixed columns into a nested object
-- @dto      ClassName          optional — overrides the auto-generated DTO class name
-- @column   originalName alias optional — renames a result column in the DTO without SQL AS
-- @calls    method1,method2    optional — used with :transaction to list methods to call
-- @counted                     optional — generate companion {name}Count(): int method (only with :many-paginated)
-- @class    ClassName          sets the PHP class name (canonical, replaces @group)
-- @group    ClassName          deprecated — use @class instead (still works, emits a warning)
```

### Return type semantics

[](#return-type-semantics)

AnnotationPHP return typeBehaviour`:many``ModelClass[]`Returns an array; empty array if no rows`:many-paginated``ModelClass[]`Like `:many` but auto-injects `LIMIT`/`OFFSET` params`:one``ModelClass`Returns the object; **throws `RuntimeException`** if no row found`:opt``ModelClass|null`Returns the object or `null` if no row found`:exec``void`Executes the statement (INSERT, UPDATE, DELETE)`:batch``int`Executes the same INSERT/UPDATE N times in a transaction; returns row count`:transaction``void`Runs multiple `@calls` methods sequentially in one transaction---

Query examples
--------------

[](#query-examples)

### SELECT \* — returns the table model

[](#select---returns-the-table-model)

```
-- @name ListUsers
-- @group User
-- @returns :many
SELECT users.* FROM users;
```

Generated method:

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

---

### SELECT \* with WHERE — :one throws, :opt returns null

[](#select--with-where--one-throws-opt-returns-null)

```
-- @name GetUser
-- @group User
-- @returns :one
SELECT users.* FROM users WHERE users.id = :id;

-- @name GetUserByEmail
-- @group User
-- @returns :opt
SELECT users.* FROM users WHERE users.email = :email;
```

Generated methods:

```
/** @return User */
public function getUser(?int $id): User               // throws RuntimeException if missing

/** @return User|null */
public function getUserByEmail(string $email): ?User  // returns null if missing
```

---

### SELECT specific columns

[](#select-specific-columns)

When columns come from a single table, the return type is still the table model:

```
-- @name GetUserProfile
-- @group User
-- @returns :one
SELECT users.id, users.email, users.firstname, users.avatar
FROM users
WHERE users.id = :id;
```

```
public function getUserProfile(?int $id): User
```

---

### JOIN — generates a result DTO

[](#join--generates-a-result-dto)

When columns come from multiple tables, a dedicated `*Row` DTO is generated:

```
-- @name GetUserWithRole
-- @group User
-- @returns :one
SELECT
    users.id,
    users.email,
    roles.name        AS role_name,
    roles.description AS role_description
FROM users
INNER JOIN roles ON roles.id = users.role_id
WHERE users.id = :id;
```

Generated files:

- `GetUserWithRoleRow.php` — readonly DTO with `id`, `email`, `role_name`, `role_description`
- Method in `UserQuery.php`:

```
public function getUserWithRole(?int $id): GetUserWithRoleRow
```

---

### Aggregate and expression columns

[](#aggregate-and-expression-columns)

sqlc-php infers types from SQL functions. Aliases are generated automatically when none is provided (mirroring sqlc/Go behaviour):

```
-- @name GetUserStats
-- @group User
-- @returns :one
SELECT
    COUNT(*)        AS total_users,
    SUM(active)     AS total_active,
    AVG(role_id)    AS avg_role,
    MAX(created_at) AS last_signup
FROM users;
```

Generated DTO:

```
readonly class GetUserStatsRow
{
    public function __construct(
        public int                 $total_users,   // COUNT → int, never null
        public ?int                $total_active,  // SUM   → ?int (null on empty set)
        public ?float              $avg_role,      // AVG   → ?float
        public ?\DateTimeImmutable $last_signup,   // MAX   → nullable, type from column
    ) {}
}
```

#### Expression type inference table

[](#expression-type-inference-table)

SQL expressionPHP typeAuto-alias (no AS)`COUNT(*)``int``count``SUM(int_col)``?int``sumIntCol``SUM(decimal_col)``?float``sumDecimalCol``AVG(col)``?float``avgCol``MIN(col)``?{type of col}``minCol``MAX(col)``?{type of col}``maxCol``COALESCE(col, x)``{type of col}` (not nullable)`coalesceCol``IFNULL(col, x)``{type of col}` (not nullable)`ifnullCol``NULLIF(col, x)``?{type of col}``nullifCol``CONCAT(...)``?string``concat``CAST(x AS INT)``int``castX``UPPER/LOWER/TRIM(col)``string``upper` / `lower` / `trim``LENGTH(col)``int``length``CASE WHEN ...``?string``case`Unknown expression`mixed``col_1`, `col_2`…---

### `:many-paginated` — automatic pagination

[](#many-paginated--automatic-pagination)

Using `:many-paginated` instructs sqlc-php to automatically append `LIMIT :limit OFFSET :offset` to the SQL and add those two parameters to the generated method with sensible defaults.

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

Generated method:

```
/**
 * @param int $limit  Maximum number of rows to return.
 * @param int $offset Number of rows to skip.
 * @return User[]
 */
public function listUsers(int $limit = 20, int $offset = 0): array
```

The SQL stored in the class becomes:

```
SELECT users.* FROM users ORDER BY created_at DESC
LIMIT :limit OFFSET :offset
```

Any user-defined parameters appear first in the signature; `$limit` and `$offset` are always last:

```
-- @name ListActiveUsers
-- @returns :many-paginated
-- @optional status
SELECT users.* FROM users WHERE users.status = :status;
```

```
public function listActiveUsers(?string $status = null, int $limit = 20, int $offset = 0): array
```

---

### IN() clauses — array parameters

[](#in-clauses--array-parameters)

Parameters inside `IN()` clauses are automatically detected and handled with dynamic placeholder expansion at runtime. No manual SQL building required.

```
-- @name GetByIds
-- @group User
-- @returns :many
SELECT users.* FROM users WHERE id IN (:ids);
```

Generated method:

```
/**
 * @param int[] $ids List of values for IN() clause — must be non-empty.
 * @return User[]
 */
public function getByIds(array $ids): array
{
    // Expand IN() placeholders dynamically at runtime
    $__sql = 'SELECT * FROM users WHERE id IN (:ids)';
    if (empty($ids)) {
        throw new \InvalidArgumentException('Parameter $ids for IN() clause must not be empty.');
    }
    $__ph_ids = implode(',', array_fill(0, count($ids), '?'));
    $__sql = str_replace(':ids', $__ph_ids, $__sql);
    $stmt = $this->pdo->prepare($__sql);
    $stmt->execute([...$ids]);

    return array_map(
        static fn(array $row): User => User::fromRow($row),
        $stmt->fetchAll(PDO::FETCH_ASSOC),
    );
}
```

The element type in the docblock (`int[]`) is inferred from the column type, just like any other parameter.

#### Mixed IN and regular parameters

[](#mixed-in-and-regular-parameters)

```
-- @name FilterUsers
-- @returns :many
SELECT users.* FROM users
WHERE id IN (:ids) AND active = :active;
```

```
/**
 * @param int[] $ids    List of values for IN() clause — must be non-empty.
 * @param int   $active
 * @return User[]
 */
public function filterUsers(array $ids, int $active): array
```

Regular params are bound with `bindValue()`; IN-list params are expanded positionally and passed to `execute(array)`. The two mechanisms are combined transparently.

#### Multiple IN clauses

[](#multiple-in-clauses)

```
-- @name FilterByIdsAndRoles
-- @returns :many
SELECT users.* FROM users
WHERE id IN (:ids) AND role_id IN (:roleIds);
```

```
public function filterByIdsAndRoles(array $ids, array $roleIds): array
```

Each IN-list param gets its own placeholder variable (`$__ph_ids`, `$__ph_roleIds`) and its values are spread into `execute()` in order.

#### NOT IN

[](#not-in)

`NOT IN (:param)` works exactly like `IN (:param)`:

```
SELECT users.* FROM users WHERE id NOT IN (:excludedIds);
```

```
public function excludeIds(array $excludedIds): array
```

---

### :batch — bulk operations in a transaction

[](#batch--bulk-operations-in-a-transaction)

Executes the same INSERT or UPDATE query N times inside a single PDO transaction. Rolls back and re-throws on any failure.

```
-- @name InsertUsers
-- @group User
-- @returns :batch
INSERT INTO users (email, username) VALUES (:email, :username);
```

```
$count = $userQuery->insertUsers([
    ['email' => 'alice@example.com', 'username' => 'alice'],
    ['email' => 'bob@example.com',   'username' => 'bob'],
]);
// → int (number of rows processed)
```

The statement is prepared once and reused for every row. An empty `$rows` array returns `0` without opening a transaction.

---

### :transaction — multi-method transactions

[](#transaction--multi-method-transactions)

Groups multiple `:exec` methods from the same Query class into a single transaction via `@calls`. Requires `@group` since there is no SQL to infer the group from.

```
-- @name TransferFunds
-- @group Account
-- @returns :transaction
-- @calls debitAccount,creditAccount
```

```
// Wraps $this->debitAccount() and $this->creditAccount() in beginTransaction/commit/rollBack
public function transferFunds(): void { ... }
```

If the `:transaction` method has `@param` declarations, they are forwarded to all callee methods.

---

### Prepared statement caching

[](#prepared-statement-caching)

Opt-in per target. Caches PDOStatement objects to avoid re-preparing the same SQL on every call — especially useful in loops.

```
targets:
  - namespace: "App\\Database"
    out: generated
    queries: queries.sql
    prepared_statement_cache: true
```

With caching enabled, the generated class includes `private array $stmts = []` and every method uses:

```
$stmt = $this->stmts[__FUNCTION__] ??= $this->pdo->prepare('SELECT ...');
```

---

### UPDATE / DELETE — :exec

[](#update--delete--exec)

```
-- @name UpdateUserActive
-- @group User
-- @returns :exec
UPDATE users SET active = :active, updated_at = :updatedAt WHERE id = :id;

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

```
public function updateUserActive(?bool $active, ?string $updatedAt, ?int $id): void
public function deleteUser(?int $id): void
```

---

### MySQL ENUM → PHP backed enum

[](#mysql-enum--php-backed-enum)

When a column is defined as `ENUM(...)`, sqlc-php generates a PHP 8.1 backed enum file and uses it as the property type in the DTO. The `fromRow` method uses `::from()` or `::tryFrom()` depending on nullability.

```
CREATE TABLE orders (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'processing', 'completed', 'cancelled') NOT NULL
);
```

Generated enum:

```
// OrderStatus.php — generated by sqlc-php
enum OrderStatus: string
{
    case Pending    = 'pending';
    case Processing = 'processing';
    case Completed  = 'completed';
    case Cancelled  = 'cancelled';
}
```

Generated DTO property and cast:

```
// in Order.php
public OrderStatus $status,

// in fromRow()
OrderStatus::from((string) $row['status']),
```

Nullable ENUM columns use `::tryFrom()`:

```
public ?OrderStatus $status,

// in fromRow()
isset($row['status']) ? OrderStatus::tryFrom((string) $row['status']) : null,
```

Enum naming convention: `{SingularTable}{PascalColumn}` — e.g. `orders.status` → `OrderStatus`, `users.role` → `UserRole`. Hyphenated values are converted to PascalCase: `in-progress` → `case InProgress = 'in-progress'`.

---

### JSON column → typed array

[](#json-column--typed-array)

`JSON` columns map to `array` in PHP and are automatically hydrated via `json_decode` in the generated `fromRow`:

```
CREATE TABLE orders (
    metadata JSON null
);
```

```
// in Order.php
public ?array $metadata,

// in fromRow()
isset($row['metadata']) ? json_decode((string) $row['metadata'], true) : null,
```

For `NOT NULL` JSON columns, the fallback is `?? []` to guarantee a non-null array is always returned.

---

### @deprecated — mark a method as deprecated

[](#deprecated--mark-a-method-as-deprecated)

Adding `@deprecated` to a query causes the generated method to include a `@deprecated` PHPDoc tag. This is useful when migrating queries without breaking existing code.

```
-- @name GetUser
-- @group User
-- @returns :one
-- @deprecated Use getUserById instead
SELECT users.* FROM users WHERE users.id = :id;
```

Generated method:

```
/**
 * @deprecated Use getUserById instead
 * @param ?int $id
 * @return User
 */
public function getUser(?int $id): User
```

The reason is optional — `-- @deprecated` without a message emits `@deprecated` alone.

---

### @nillable — force a result column to be nullable

[](#nillable--force-a-result-column-to-be-nullable)

`@nillable columnAlias` forces a specific column in the result set to be `?type` in the generated DTO or return type, regardless of how the column is declared in the schema.

This is useful in two scenarios:

**LEFT JOIN — column may be NULL at runtime even though NOT NULL in schema:**

```
-- @name GetUserWithOptionalRole
-- @group User
-- @returns :one
-- @nillable role_name
-- @nillable role_description
SELECT
    users.id,
    users.email,
    roles.name        AS role_name,
    roles.description AS role_description
FROM users
LEFT JOIN roles ON roles.id = users.role_id
WHERE users.id = :id;
```

Generated DTO (multi-table → custom DTO):

```
readonly class GetUserWithOptionalRoleRow
{
    public function __construct(
        public ?int    $id,
        public string  $email,
        public ?string $role_name,         // forced nullable via @nillable
        public ?string $role_description,  // forced nullable via @nillable
    ) {}
}
```

**Direct model queries (`SELECT *`) — forces a dedicated DTO instead of reusing the table model:**

When `@nillable` is used on a query that would normally return the table model directly (single-table `SELECT *`), sqlc-php generates a dedicated `*Row` DTO so the nullability can be applied without mutating the base model class:

```
-- @name GetUserProfile
-- @group User
-- @returns :one
-- @nillable email
SELECT users.* FROM users WHERE users.id = :id;
```

This generates `GetUserProfileRow` with `public ?string $email` instead of reusing `User` where `email` is `NOT NULL`.

Multiple `@nillable` annotations can be stacked. The annotation targets the output alias (the name after `AS`), or the column name when no alias is used.

---

### @embed — nested objects for JOIN results

[](#embed--nested-objects-for-join-results)

`@embed ClassName prefix_` groups all result columns whose alias starts with `prefix_` into a nested `readonly` value object instead of flattening them into the parent DTO.

```
-- @name GetUserWithRole
-- @group User
-- @returns :one
-- @embed Role role_
SELECT
    users.id,
    users.email,
    roles.name        AS role_name,
    roles.description AS role_description
FROM users
INNER JOIN roles ON roles.id = users.role_id
WHERE users.id = :id;
```

Generated files:

**`Role.php`** — standalone readonly value object with stripped property names:

```
readonly class Role
{
    public function __construct(
        public string  $name,
        public ?string $description,
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            (string) $row['role_name'],
            $row['role_description'] ?? null,
        );
    }
}
```

**`GetUserWithRoleRow.php`** — parent DTO with the nested `Role` object as a property:

```
readonly class GetUserWithRoleRow
{
    public function __construct(
        public ?int  $id,
        public string $email,
        public Role   $role,       // ← nested object, not flat properties
    ) {}

    public static function fromRow(array $row): self
    {
        return new self(
            (int) $row['id'],
            (string) $row['email'],
            Role::fromRow($row),   // ← hydrates from the same flat PDO row
        );
    }
}
```

Usage:

```
$result = $repo->getUserWithRole(42);

echo $result->role->name;         // instead of $result->role_name
echo $result->role->description;
```

#### Multiple @embed groups on one query

[](#multiple-embed-groups-on-one-query)

```
-- @name GetUserFull
-- @group User
-- @returns :one
-- @embed Role     role_
-- @embed Address  addr_
SELECT
    users.id,
    users.email,
    roles.name           AS role_name,
    addresses.street     AS addr_street,
    addresses.city       AS addr_city
FROM users
INNER JOIN roles     ON roles.id     = users.role_id
INNER JOIN addresses ON addresses.id = users.address_id
WHERE users.id = :id;
```

Generates `Role.php`, `Address.php`, and `GetUserFullRow.php` with:

```
public function __construct(
    public ?int    $id,
    public string  $email,
    public Role    $role,     // prefix: role_
    public Address $addr,     // prefix: addr_
) {}
```

#### Naming convention

[](#naming-convention)

The DTO property name is derived from the prefix by stripping the trailing underscore:

- `role_` → `$role`
- `addr_` → `$addr`
- `billing_` → `$billing`

The prefix can be written with or without trailing underscore in the annotation: `@embed Role role_` and `@embed Role role` both produce the same result.

---

### Optional parameters

[](#optional-parameters)

Marking a parameter as `@optional` instructs sqlc-php to rewrite the SQL condition at generation time. When `null` is passed at runtime the filter is skipped entirely; when a value is passed it filters normally. No `if` statements or query builders required.

```
-- @name SearchUsers
-- @group User
-- @returns :many
-- @optional status
-- @optional username
SELECT users.* FROM users
WHERE users.status   = :status
  AND users.username = :username;
```

sqlc-php rewrites each optional condition before emitting any PHP:

```
-- rewritten SQL stored in the generated class
SELECT users.* FROM users
WHERE (:status   IS NULL OR users.status   = :status)
  AND (:username IS NULL OR users.username = :username)
```

Generated method:

```
/**
 * @param ?string $status   Pass null to skip this filter.
 * @param ?string $username Pass null to skip this filter.
 * @return User[]
 */
public function searchUsers(?string $status = null, ?string $username = null): array
```

Calling the method:

```
// All rows — both filters skipped
$repo->searchUsers();

// Filter by status only — username skipped
$repo->searchUsers(status: 'active');

// Filter by both
$repo->searchUsers(status: 'active', username: 'alice');
```

#### Mixing required and optional parameters

[](#mixing-required-and-optional-parameters)

Required parameters always appear first in the signature; optional parameters follow with `= null`.

```
-- @name GetUsersByRole
-- @group User
-- @returns :many
-- @optional status
SELECT users.* FROM users
WHERE users.role_id = :roleId
  AND users.status  = :status;
```

```
// roleId is required, status is optional
public function getUsersByRole(int $roleId, ?string $status = null): array
```

#### Supported operators

[](#supported-operators)

OperatorRewritten form`=``(:param IS NULL OR col = :param)````(:param IS NULL OR col  :param)``!=``(:param IS NULL OR col != :param)``>``(:param IS NULL OR col > :param)``=``(:param IS NULL OR col >= :param)``listUsers();

// :one — throws RuntimeException if user not found
$user = $repo->getUser(42);

// :opt — returns null if not found
$user = $repo->getUserByEmail('alice@example.com');
if ($user === null) {
    // handle not found
}

// :exec — fire and forget
$repo->deleteUser(42);
$repo->updateUserActive(true, date('Y-m-d H:i:s'), 42);

// @optional — named arguments, skip filters by passing null
$all      = $repo->searchUsers();
$active   = $repo->searchUsers(status: 'active');
$filtered = $repo->searchUsers(status: 'active', username: 'alice');
```

---

Usage with Laravel
------------------

[](#usage-with-laravel)

The recommended pattern is to wrap the generated query class inside a repository class, bind it in a Service Provider using the generated interface, and inject it into controllers or services via the constructor.

### 1. Create a repository

[](#1-create-a-repository)

```
namespace App\Repositories;

use App\Database\User;
use App\Database\UserQueryInterface;

class UserRepository
{
    public function __construct(private UserQueryInterface $userQuery) {}

    public function getUser(int $id): User
    {
        return $this->userQuery->getUser($id);
    }

    public function getUserByEmail(string $email): ?User
    {
        return $this->userQuery->getUserByEmail($email);
    }

    /** @return User[] */
    public function searchUsers(?string $status = null, ?string $username = null): array
    {
        return $this->userQuery->searchUsers(
            status:   $status,
            username: $username,
        );
    }
}
```

### 2. Register the binding in a Service Provider

[](#2-register-the-binding-in-a-service-provider)

```
namespace App\Providers;

use App\Database\UserQuery;
use App\Database\UserQueryInterface;
use App\Repositories\UserRepository;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        // Bind the interface to the concrete implementation
        $this->app->bind(UserQueryInterface::class, function ($app) {
            return new UserQuery(
                $app->make('db')->connection()->getPdo()
            );
        });

        $this->app->bind(UserRepository::class, function ($app) {
            return new UserRepository(
                $app->make(UserQueryInterface::class)
            );
        });
    }
}
```

If your application uses multiple database connections, pass the connection name explicitly:

```
$app->make('db')->connection('mysql_replica')->getPdo()
```

### 3. Inject the repository into a controller

[](#3-inject-the-repository-into-a-controller)

```
namespace App\Http\Controllers;

use App\Repositories\UserRepository;

class UserController extends Controller
{
    public function __construct(
        private readonly UserRepository $userRepository,
    ) {}

    public function show(int $id)
    {
        $user = $this->userRepository->getUser($id);
        return response()->json($user);
    }

    public function index(Request $request)
    {
        $users = $this->userRepository->searchUsers(
            status:   $request->query('status'),
            username: $request->query('username'),
        );

        return response()->json($users);
    }
}
```

### 4. Inject into a service or job

[](#4-inject-into-a-service-or-job)

```
class SendWelcomeEmail implements ShouldQueue
{
    public function __construct(private readonly UserRepository $userRepository) {}

    public function handle(): void
    {
        $user = $this->userRepository->getUserByEmail($this->email);
        // ...
    }
}
```

### 5. Testing with the interface

[](#5-testing-with-the-interface)

Because the repository depends on `UserQueryInterface`, you can swap in a mock without touching the database:

```
class UserControllerTest extends TestCase
{
    public function test_show_returns_user(): void
    {
        $mock = $this->createMock(UserQueryInterface::class);
        $mock->method('getUser')->willReturn(new User(
            id: 1, email: 'alice@example.com', username: 'alice',
            // ...
        ));

        $this->app->instance(UserQueryInterface::class, $mock);

        $this->getJson('/api/users/1')->assertOk();
    }
}
```

---

CLI flags
---------

[](#cli-flags)

### `--verify` — CI check

[](#--verify--ci-check)

Generates all files in memory and compares them against the existing output. Writes nothing. Exits `1` if anything is missing or out of date.

```
php vendor/bin/sqlc-php --verify sqlc.yaml
```

```
✓ All 6 generated file(s) are up to date.

```

```
✗ Generated files are out of date.

Missing files (1):
  - generated/OrderStatus.php

Modified files (1):
  - generated/User.php

Run `php vendor/bin/sqlc-php sqlc.yaml` to regenerate.

```

### `--dry-run` — preview without writing

[](#--dry-run--preview-without-writing)

Prints the full content of every file that would be generated to stdout. Writes nothing to disk.

```
php vendor/bin/sqlc-php --dry-run sqlc.yaml
```

```
──────────────────────────────────────────────────────────────────────
// generated/User.php
──────────────────────────────────────────────────────────────────────