sqlc-php

How it works

You write SQL with annotations. sqlc-php reads your schema and generates typed PHP classes — no annotations on PHP classes, no runtime magic, no query builders. The generated code is plain PHP that you can read and debug.

Installation

composer require phpibe/sqlc-php

Requires PHP 8.1+ and PDO with the MySQL driver.

CLI reference

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

The config argument is the path to sqlc.yaml. Defaults to sqlc.yaml in the current directory.

General

Code generation

Watch mode

Schema extraction

Examples

# Generate (default)
php vendor/bin/sqlc-php sqlc.yaml

# Preview output without writing files
php vendor/bin/sqlc-php --dry-run sqlc.yaml

# Show diff vs current generated files
php vendor/bin/sqlc-php --diff sqlc.yaml

# CI — fail if generated files are stale
php vendor/bin/sqlc-php --verify sqlc.yaml

# Watch for changes and regenerate automatically
php vendor/bin/sqlc-php --watch sqlc.yaml
php vendor/bin/sqlc-php --watch --interval=250 sqlc.yaml

# Extract schema from a live database
php vendor/bin/sqlc-php --generate-schema sqlc.yaml
php vendor/bin/sqlc-php --generate-schema --schema-output=db/schema.sql sqlc.yaml

Database block for --generate-schema

# sqlc.yaml
database:
  dsn:      "mysql:host=127.0.0.1;dbname=mydb;charset=utf8mb4"
  username: "${DB_USERNAME}"
  password: "${DB_PASSWORD}"
  include_tables:        # optional — only extract these tables
    - users
    - orders
    - products

Quick start

1. Schema

-- schema.sql
CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(200) NOT NULL,
    name       VARCHAR(100) NOT NULL,
    role_id    INT NOT NULL,
    created_at DATETIME NOT NULL
);

2. Query file

-- queries/users.sql

-- @name GetUser
-- @class Users
-- @returns :opt
SELECT users.* FROM users WHERE id = :id;

-- @name ListActiveUsers
-- @class Users
-- @returns :many
-- @with criteria, count
SELECT users.* FROM users WHERE role_id = :role_id;

3. Configuration

# sqlc.yaml
version: "2"
engine: mysql
schema:
  - schema.sql
targets:
  - namespace: "App\\Database"
    class_suffix: Repository
    queries:
      - queries/users.sql
    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

4. Generate

php vendor/bin/sqlc-php sqlc.yaml

5. Use

$repo = new UsersRepository($pdo);

// :opt — returns ?User
$user = $repo->getUser(id: 42);

// :many with criteria
$criteria = (new UsersRepositoryCriteria())
    ->whereRoleIdEq(1)
    ->orderByCreatedAtDesc();

$users = $repo->listActiveUsers(role_id: 1, criteria: $criteria);
$total = $repo->listActiveUsersCount(role_id: 1, criteria: $criteria);

Generated files

For each @class group, sqlc-php generates:

sqlc.yaml — Configuration reference

version: "2"
engine:          mysql              # Database engine (only mysql supported)
language:        english            # Inflection language for model names: english | spanish
class_suffix:    Repository         # Suffix for generated Query classes (default: Query)
datetime_format: "Y-m-d H:i:s"     # Format for DateTimeImmutable in toArray() (default: Y-m-d H:i:s)

schema:
  - database/schema.sql             # One or more schema files

type_overrides:                     # Global type overrides (applied to all targets)
  - column:   users.deleted_at
    php_type: "?\\DateTimeImmutable"

targets:
  - namespace:               "App\\Database"
    class_suffix:             Repository      # Overrides global class_suffix
    language:                 english         # Overrides global language
    generate_interfaces:      true            # Generate XxxRepositoryInterface (default: true)
    prepared_statement_cache: false           # Cache PDO prepared statements (default: false)
    dto_scope:                none            # DTO namespace grouping: none | class | method
    queries:
      - database/queries/users.sql
    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    # Enables extension trait scaffolding

Global options reference

type_overrides

Override the default SQL → PHP mapping for specific columns or SQL types. Can be declared globally or per target (per-target takes precedence).

type_overrides:
  # Map a specific column to a custom PHP type
  - column:   users.deleted_at
    php_type: "?\\DateTimeImmutable"

  # Map every column of a given SQL type
  - db_type:  TINYINT
    php_type: bool

  # Use Carbon instead of DateTimeImmutable for all datetime columns
  - db_type:  DATETIME
    php_type: "\\Carbon\\Carbon"

enum_values — VARCHAR as backed enum

When a VARCHAR or CHAR column holds a fixed set of values, declare them with enum_values. sqlc-php generates a PHP 8.1 backed enum and uses it everywhere that column appears — in DTOs, Models, Criteria filter methods, and fromRow() casts.

type_overrides:
  - column:      cms_configs.section
    php_type:    CmsConfigsSection
    enum_values: [hero, faq, contact, banners]

  - column:      cms_configs.status
    php_type:    CmsConfigStatus
    enum_values: [active, draft, archived]

// Generated: Enums/CmsConfigsSection.php
enum CmsConfigsSection: string
{
    case Hero    = 'hero';
    case Faq     = 'faq';
    case Contact = 'contact';
    case Banners = 'banners';
}

// In DTOs and Models — section is typed as the enum:
public CmsConfigsSection $section;

// fromRow() uses ::from()
section: CmsConfigsSection::from((string) $row['section']),

// Criteria methods are enum-typed:
$criteria->whereSectionEq(CmsConfigsSection::Hero);
$criteria->whereSectionIn(CmsConfigsSection::Hero, CmsConfigsSection::Faq);

dto_scope

Controls how result DTOs are namespaced and organized. Useful when multiple Query classes produce DTOs — prevents naming collisions and groups related types together.

The same scoping applies to Params DTOs (@with params) and Query extension scaffolds ([ext-q]).

toArray()

Every generated result DTO and Model has a toArray(): array method. It converts the object to an associative array, properly unwrapping typed values — avoiding the raw (array) cast which leaves BackedEnum objects and DateTimeImmutable instances intact.

// Before — (array) leaves objects:
$data = (array) $row;   // ❌ $data['section'] is CmsConfigsSection object

// After — toArray() unwraps everything:
$data = $row->toArray();  // ✅ $data['section'] is 'hero'

Conversion rules

Configure the datetime format globally in sqlc.yaml:

datetime_format: "Y-m-d\TH:i:sP"   # ISO 8601 with timezone

Shared CTEs

sqlc-php supports two ways to use CTEs. Inline CTEs work with no configuration. Shared CTEs — declared once and reused across many queries — use the @cte and @use annotations.

Inline CTEs — no configuration needed

Write WITH ... AS (...) directly in the SQL. The analyzer understands it and resolves columns against the CTE result set normally.

-- @name GetActiveReserves
-- @class Reserve
-- @returns :many
WITH active AS (
    SELECT * FROM reserve WHERE status_id IN (2, 5)
)
SELECT active.* FROM active
WHERE active.updated_at >= :now;

@cte — declare a shared CTE

Declare reusable CTEs in dedicated .sql files using @cte name. Each file can contain any number of named CTE blocks. A CTE declared this way can be injected into any query via @use — the WITH block is generated automatically at compile time.

-- database/ctes/reserve_states.sql

-- @cte active_reserves
SELECT * FROM reserve WHERE status_id IN (2, 5);

-- @cte pending_reserves
SELECT * FROM reserve WHERE status_id = 1;

-- @cte pre_trip_reserves
SELECT reserve.* FROM reserve
INNER JOIN reserve_insured ON reserve_insured.reserve_id = reserve.id
WHERE reserve.status_id IN (2, 5);

Register the CTE files in sqlc.yaml. CTEs declared at the root level are available to all targets. CTEs declared inside a target are merged with the global ones.

version: "2"
engine: mysql
schema:
  - database/schema.sql

# Global — available to all targets
ctes:
  - database/ctes/reserve_states.sql
  - database/ctes/billing.sql

targets:
  - namespace: "App\\Database"
    queries:
      - database/queries/reserves.sql
    out:
      queries: app/Database/Repositories
    ctes:
      - database/ctes/reserve_extras.sql   # merged with global CTEs for this target only

@use — reference a shared CTE in a query

Use @use cte_name in any query to inject the declared CTE as a WITH block. The CTE name becomes a virtual table that the analyzer resolves for column type inference.

-- @name ListCustomersForPreTrip
-- @class Reserve
-- @with stream, count
-- @param now string
-- @returns :many
-- @use pre_trip_reserves
SELECT pre_trip_reserves.* FROM pre_trip_reserves
WHERE pre_trip_reserves.updated_at >= :now;

The compiler expands this to:

WITH pre_trip_reserves AS (
    SELECT reserve.* FROM reserve
    INNER JOIN reserve_insured ON reserve_insured.reserve_id = reserve.id
    WHERE reserve.status_id IN (2, 5)
)
SELECT pre_trip_reserves.* FROM pre_trip_reserves
WHERE pre_trip_reserves.updated_at >= :now;

Multiple CTEs on one query

-- @name GetReserveSummary
-- @class Reserve
-- @returns :one
-- @use active_reserves, pending_reserves
SELECT
    (SELECT COUNT(*) FROM active_reserves)  AS active_count,
    (SELECT COUNT(*) FROM pending_reserves) AS pending_count;

Multiple @use lines also work

-- @use active_reserves
-- @use pending_reserves

Real-world example — pre-trip processing pipeline

-- database/ctes/reserve_states.sql

-- @cte pre_trip_candidates
SELECT reserve.id, reserve.updated_at, reserve.status_id,
       reserve_insured.customer_id
FROM reserve
INNER JOIN reserve_insured ON reserve_insured.reserve_id = reserve.id
WHERE reserve.status_id IN (2, 5);

-- database/queries/pre_trip.sql

-- @name StreamPreTripReserves
-- @class PreTrip
-- @with stream
-- @param from_date string
-- @returns :many
-- @use pre_trip_candidates
SELECT pre_trip_candidates.* FROM pre_trip_candidates
WHERE pre_trip_candidates.updated_at >= :from_date;

-- @name CountPreTripReserves
-- @class PreTrip
-- @returns :count
-- @use pre_trip_candidates
SELECT COUNT(*) FROM pre_trip_candidates;

-- @name PreTripReserveExists
-- @class PreTrip
-- @param reserve_id int
-- @returns :exists
-- @use pre_trip_candidates
SELECT 1 FROM pre_trip_candidates
WHERE pre_trip_candidates.id = :reserve_id LIMIT 1;

// The same CTE, three different uses:
foreach ($repo->streamPreTripReserves(from_date: '2026-01-01') as $reserve) {
    // process unbuffered stream
}

$total  = $repo->countPreTripReserves();
$exists = $repo->preTripReserveExists(reserve_id: 42);

Annotations reference

@name

Names the query. Becomes the generated method name in camelCase. Required.

-- @name GetActiveUsers
-- @class Users
-- @returns :many
SELECT users.* FROM users WHERE status = 'active';

Generates: public function getActiveUsers(): array

@class

Groups multiple queries into the same generated class. All queries sharing a @class are emitted into one {Class}{Suffix}.php file. Required.

-- @name GetUser
-- @class Users          ← all Users queries → UsersRepository.php
-- @returns :opt
SELECT users.* FROM users WHERE id = :id;

-- @name CreateUser
-- @class Users          ← same class
-- @returns :exec
INSERT INTO users (email, name) VALUES (:email, :name);

@returns

Declares the return type of the generated method. Required. See Return types for full details of each.

-- @returns :many        -- array of rows
-- @returns :one         -- exactly one row (throws if not found)
-- @returns :opt         -- one row or null
-- @returns :exec        -- void (INSERT/UPDATE/DELETE)
-- @returns :batch       -- void (same INSERT executed N times)
-- @returns :grouped     -- JOIN rows grouped into typed arrays
-- @returns :cursor      -- Generator (streaming)
-- @returns :paginator   -- PaginatedResult with total count
-- @returns :transaction -- calls multiple methods in a transaction

@param

Declares or overrides the type of a named parameter. Without @param, the type is inferred from the schema column the parameter is compared against.

PHP type declaration

-- @param userId   int
-- @param name     string
-- @param config   ?array     ← nullable
-- @param status   string

SQL type declaration v2.19.28

Use the raw SQL type when the parameter doesn't map directly to a schema column. SQL types with parentheses are automatically converted to the correct PHP type.

-- @param min_price  decimal(10,2)   → float   $min_price
-- @param max_price  decimal(10,2)   → float   $max_price
-- @param search     varchar(100)    → string  $search
-- @param is_active  tinyint(1)      → int     $is_active
-- @param cutoff     ?datetime        → ?\DateTimeImmutable $cutoff

-- @name FilterProducts
-- @class Products
-- @returns :many
-- @param min_price decimal(10,2)
-- @param search    varchar(100)
SELECT products.* FROM products
WHERE price >= :min_price AND name LIKE :search;

// Generated — SQL types become correct PHP types:
public function filterProducts(float $min_price, string $search): array

Optional parameter

Adding :optional rewrites the WHERE clause so the parameter is skipped when null. The PHP type becomes ?type = null.

-- @name SearchUsers
-- @class Users
-- @returns :many
-- @param role_id ?int:optional
-- @param status  ?string:optional
SELECT users.* FROM users
WHERE (:role_id IS NULL OR role_id = :role_id)
  AND (:status  IS NULL OR status  = :status);

// Generated signature:
public function searchUsers(?int $role_id = null, ?string $status = null): array

// Usage — pass only what you need:
$repo->searchUsers(role_id: 1);         // filter by role only
$repo->searchUsers(status: 'active');   // filter by status only
$repo->searchUsers();                   // no filters

Inferred from schema column

-- @param createdAt users.created_at   ← type inferred from schema column

@with

Adds capabilities to the generated method. Multiple modifiers can be combined comma-separated on a single @with line.

criteria — dynamic filters

-- @name ListUsers
-- @class Users
-- @returns :many
-- @with criteria, count
SELECT users.* FROM users WHERE status = :status;

// Generated Criteria methods match column types:
$criteria = (new UsersRepositoryCriteria())
    ->whereEmailLike('%@example.com')
    ->whereRoleIdIn(1, 2, 3)
    ->orderByCreatedAtDesc()
    ->limit(20);

$users = $repo->listUsers(status: 'active', criteria: $criteria);
$total = $repo->listUsersCount(status: 'active', criteria: $criteria);

returning — fetch after INSERT

-- @name CreateUser
-- @class Users
-- @returns :one
-- @with returning
INSERT INTO users (email, name, role_id) VALUES (:email, :name, :role_id);

// Returns the full User model after INSERT:
$user = $repo->createUser(email: 'a@b.com', name: 'Alice', role_id: 1);
echo $user->id;   // ← the new auto-increment ID

params — input DTO

-- @name CreateConfig
-- @class CmsConfig
-- @returns :one
-- @with returning, params
INSERT INTO cms_configs (country_id, page, section, status, config)
VALUES (:country_id, :page, :section, :status, :config);

// Generated: DTOs/CreateConfigParams.php
readonly class CreateConfigParams
{
    public function __construct(
        public int    $country_id,
        public string $page,
        public string $section,
        public string $status,
        public ?array $config,
    ) {}

    public static function from(array $data): self { ... }
}

// Without @with params: 5 positional args
// With @with params: 1 typed DTO
$config = $repo->createConfig(new CreateConfigParams(
    country_id: 1,
    page:       'home',
    section:    'hero',
    status:     'active',
    config:     ['key' => 'val'],
));

// Also supports construction from array:
$config = $repo->createConfig(CreateConfigParams::from($requestData));

paginated — limit/offset

-- @name ListPosts
-- @class Posts
-- @returns :many
-- @with paginated, count
SELECT posts.* FROM posts WHERE status = :status ORDER BY created_at DESC;

// Injects LIMIT/OFFSET — no SQL change needed:
$posts = $repo->listPosts(status: 'published', limit: 20, offset: 40);
$total = $repo->listPostsCount(status: 'published');

@visibility

Controls the PHP visibility of the generated method. Default is public. Use protected to hide raw SQL methods and expose domain logic via the extension trait — replacing the Laravel Observer pattern without magic hooks.

-- @name InsertConfig
-- @class CmsConfig
-- @with returning, params
-- @visibility protected
-- @returns :one
INSERT INTO cms_configs (country_id, page, section, status, config)
VALUES (:country_id, :page, :section, :status, :config);

// Generated as protected — not accessible directly:
protected function insertConfig(InsertConfigParams $params): CmsConfig { ... }

// Extension trait provides the public entry point:
trait CmsConfigRepositoryExtension
{
    public function createConfig(CreateConfigParams $params): ?CmsConfig
    {
        // Before-save logic
        if ($params->programmed === CmsConfigProgrammed::Programmed) {
            $params = new CreateConfigParams(...$params->toArray(),
                status: CmsConfigStatus::Programmed
            );
        }

        // Find existing config to detect changes
        $parent = $this->getActive(
            country_id: $params->country_id,
            page: $params->page, section: $params->section,
        );

        if ($parent !== null) {
            $diff = JsonDiffer::diff($parent->config, $params->config);
            if (empty($diff)) return null;  // no changes — skip
            $params = new CreateConfigParams(...$params->toArray(), parent_id: $parent->id);
        }

        // Call the protected SQL method
        $config = $this->insertConfig(InsertConfigParams::from($params->toArray()));

        // After-save logic
        event(new CmsConfigCreated($config->id));

        return $config;
    }
}

@type

Overrides the PHP type of a result column. Three forms: scalar override, JSON DTO, and table wildcard.

Scalar type override

-- @name GetStats
-- @class Stats
-- @returns :one
-- @type active    bool
-- @type total     ?float
-- @type role      string
SELECT active, total, role FROM users WHERE id = :id;

JSON column → DTO

-- @name GetUser
-- @class Users
-- @returns :opt
-- @type address  json:Address          ← single Address object
-- @type cities   json:City[]           ← array of City objects
-- @type bio      ?json:Bio             ← nullable single object
SELECT users.*, address, cities FROM users WHERE id = :id;

// In the result DTO:
public Address       $address;
public array         $cities;    // City[]
public ?Bio          $bio;

Table wildcard → nested model

-- @name GetReserveWithUser
-- @class Reserves
-- @returns :opt
-- @type users.* User            ← all users.* columns become a User property
SELECT reserves.*, users.id, users.email, users.name
FROM reserves
LEFT JOIN users ON users.id = reserves.user_id
WHERE reserves.id = :id;

// DTO has nested User object:
public User $users;  // hydrated via User::fromRow($row)

@filter

Adds Criteria filter methods for JOIN columns that are not in the SELECT list. Without @filter, only selected columns get filter methods.

-- @name ListUserOrders
-- @class Orders
-- @returns :many
-- @with criteria
-- @filter users.country_id
-- @filter users.role_id
SELECT orders.id, orders.total, orders.status
FROM orders
INNER JOIN users ON users.id = orders.user_id
WHERE orders.created_at >= :from_date;

// Criteria includes filter methods for JOIN columns:
$criteria = (new OrdersRepositoryCriteria())
    ->whereUsersCountryIdEq(164)
    ->whereUsersRoleIdIn(1, 2)
    ->whereStatusEq('active')
    ->orderByTotalDesc();

@embed

Groups SELECT columns into a nested readonly object inside the result DTO. Unlike @type table.* (which reuses an existing model), @embed generates a new inner class.

-- @name GetReserveWithUser
-- @class Reserves
-- @returns :opt
-- @embed user users.*
SELECT reserves.id, reserves.total,
       users.id AS user_id, users.email AS user_email, users.name AS user_name
FROM reserves
LEFT JOIN users ON users.id = reserves.user_id
WHERE reserves.id = :id;

// Generated DTO:
readonly class GetReserveWithUserRow
{
    public int  $id;
    public float $total;
    public GetReserveWithUserRowUser $user;   // ← embedded object
}

readonly class GetReserveWithUserRowUser
{
    public int    $user_id;
    public string $user_email;
    public string $user_name;
}

@group_by

Required companion to @returns :grouped. Specifies which column value is used as the grouping key. See :grouped.

-- @returns :grouped
-- @group_by profiles.id

@column

Renames a result column in the generated DTO without requiring a SQL AS alias.

-- @name GetUserSummary
-- @class Users
-- @returns :opt
-- @column total_orders  orderCount   ← renames 'total_orders' → 'orderCount' in DTO
SELECT users.id, COUNT(orders.id) AS total_orders
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE users.id = :id
GROUP BY users.id;

// DTO property:
public int $orderCount;   // ← not total_orders

@nullable

Forces a parameter to be nullable (?type) without rewriting the SQL condition. Useful for UPDATE SET nullable_col = :param patterns where the column accepts NULL.

-- @name UpdateUserAvatar
-- @class Users
-- @returns :exec
-- @nullable avatarUrl
UPDATE users SET avatar_url = :avatarUrl WHERE id = :id;

// Generated signature — avatarUrl is nullable:
public function updateUserAvatar(?string $avatarUrl, int $id): void

@partial

For UPDATE queries using COALESCE(:param, column) — marks those parameters as optional (?type = null). Parameters in the WHERE clause remain required.

-- @name UpdateUserProfile
-- @class Users
-- @returns :exec
-- @partial
UPDATE users
SET name       = COALESCE(:name, name),
    avatar_url = COALESCE(:avatar_url, avatar_url)
WHERE id = :id;

// name and avatar_url are optional — only id is required:
public function updateUserProfile(int $id, ?string $name = null, ?string $avatar_url = null): void

// Update only the name:
$repo->updateUserProfile(id: 42, name: 'New Name');

@cursor

Declares cursor-based pagination columns for :cursor return type. Supports keyset pagination for large datasets.

-- @name StreamPosts
-- @class Posts
-- @returns :cursor
-- @cursor created_at DESC, id DESC
SELECT posts.* FROM posts
WHERE status = :status
ORDER BY created_at DESC, id DESC;

// Returns CursorResult with next/prev cursors:
$result = $repo->streamPosts(status: 'published', limit: 20);
foreach ($result->items as $post) { ... }
$nextCursor = $result->nextCursor;   // pass to next call

@comment

Adds a description to the generated method docblock.

-- @name GetActiveUsers
-- @class Users
-- @returns :many
-- @comment Returns all users with status 'active'.
-- @comment Ordered by creation date descending.
SELECT users.* FROM users WHERE status = 'active' ORDER BY created_at DESC;

/**
 * Returns all users with status 'active'.
 * Ordered by creation date descending.
 * @return User[]
 */
public function getActiveUsers(): array

@deprecated

Marks the generated method as deprecated with a @deprecated PHPDoc tag and a message.

-- @name GetUser
-- @class Users
-- @returns :opt
-- @deprecated Use getActiveUser() instead.
SELECT users.* FROM users WHERE id = :id;

/**
 * @deprecated Use getActiveUser() instead.
 */
public function getUser(int $id): ?User

@dto

Overrides the auto-generated DTO class name. When two or more queries return the same columns, @dto lets them share a single DTO class — giving domain methods a concrete type to depend on instead of query-specific generated names.

Basic usage

-- @name GetUser
-- @class Users
-- @returns :opt
-- @dto UserSummary       ← generates UserSummary.php instead of GetUserRow.php
SELECT users.id, users.email, users.name FROM users WHERE id = :id;

-- @name GetCreator
-- @class Posts
-- @returns :opt
-- @dto UserSummary       ← reuses the same UserSummary DTO
SELECT users.id, users.email, users.name
FROM posts
INNER JOIN users ON users.id = posts.created_by
WHERE posts.id = :id;

Solving the domain typing problem

A common problem: a domain method needs to accept users from different queries, but each query generates its own DTO with a different class name — even when the data is identical.

// Without @dto — two different types for the same data:
public function getUserByEmail(string $email): GetUserByEmailRow  // id, email, name
public function listUsers(): array  // ListUsersRow[] — also id, email, name

// Domain method doesn't know what type to accept:
function sendEmail(GetUserByEmailRow|ListUsersRow $user): void  // ← bad

With @dto, both queries share one DTO. The domain method has a single concrete type:

-- @name GetUserByEmail
-- @class Users
-- @dto UserRow
-- @returns :one
SELECT id, email, name FROM users WHERE email = :email;

-- @name ListUsers
-- @class Users
-- @dto UserRow
-- @returns :many
SELECT id, name, email FROM users;  -- column order doesn't matter

// Both methods now return UserRow / UserRow[]
public function getUserByEmail(string $email): UserRow
public function listUsers(): array  // UserRow[]

// Domain method has one clean type:
function sendEmail(UserRow $user): void
{
    $this->mailer->send($user->email, $user->name);
}

// Works with both queries:
$user  = $repo->getUserByEmail(email: 'foo@bar.com');
sendEmail($user);  // ✓

$users = $repo->listUsers();
foreach ($users as $user) {
    sendEmail($user);  // ✓
}

Column validation — compile-time safety

When two queries share the same @dto name, the compiler validates that both select exactly the same columns (order-independent). A mismatch is a compile-time error, not a runtime surprise.

-- @name GetUserByEmail
-- @class Users
-- @dto UserRow
-- @returns :one
SELECT id, email, name FROM users WHERE email = :email;

-- @name ListUsersPartial
-- @class Users
-- @dto UserRow
-- @returns :many
SELECT id, email FROM users;  -- ← missing 'name'

// Compile error:
// Query 'listUsersPartial': @dto 'UserRow' is shared with another query
// but the column shapes don't match. Missing: name:string.
// All queries sharing a @dto name must select exactly the same columns.

Return types

:many — array of rows

Returns all matching rows as an array. Each row is typed as the Model (for SELECT *) or as a generated DTO (for partial SELECT).

-- @name ListUsers
-- @class Users
-- @returns :many
-- @with criteria, count, paginated
SELECT users.* FROM users WHERE role_id = :role_id;

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

:one — exactly one row

Executes the query and returns a single row. Throws RuntimeException when the query returns no rows.

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

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

:opt — one row or null

Returns the first matching row or null when no row matches.

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

public function findUserByEmail(string $email): ?User

:exec — no return value

Executes an INSERT, UPDATE, or DELETE and returns void.

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

public function deleteUser(int $id): void

:count — scalar COUNT query → int v2.19.24

Executes any COUNT (or other scalar integer) query and returns the result as int. Unlike @with count which generates a companion to a :many query, :count is a standalone method with no associated SELECT query.

-- @name CountActiveUsers
-- @class Users
-- @returns :count
SELECT COUNT(*) FROM users WHERE status = :status AND role_id = :role_id;

// Standalone int-returning method:
public function countActiveUsers(string $status, int $role_id): int

// Usage:
$total = $repo->countActiveUsers(status: 'active', role_id: 1);

:exists — existence check → bool v2.19.24

Executes a query and returns true when at least one row matches. Use SELECT 1 ... LIMIT 1 for optimal performance.

-- @name EmailExists
-- @class Users
-- @returns :exists
SELECT 1 FROM users WHERE email = :email LIMIT 1;

-- @name HasActiveOrders
-- @class Orders
-- @returns :exists
SELECT 1 FROM orders
WHERE user_id = :user_id AND status = 'active'
LIMIT 1;

public function emailExists(string $email): bool
public function hasActiveOrders(int $user_id): bool

// Usage:
if ($repo->emailExists(email: 'alice@example.com')) {
    throw new ValidationException('Email already taken');
}

:stream — standalone Generator v2.19.29

Returns a \Generator that yields rows one at a time — no full result set is loaded into memory. Unlike @with stream (which is a companion to a :many query), :stream is the primary and only method — the method name is the query name directly, without a stream prefix.

-- @name StreamPendingOrders
-- @class Orders
-- @returns :stream
SELECT orders.* FROM orders
WHERE status = :status
ORDER BY created_at ASC;

// Generated — primary method, not a companion:
public function streamPendingOrders(string $status): \Generator

// Usage — memory-efficient row-by-row iteration:
foreach ($repo->streamPendingOrders(status: 'pending') as $order) {
    $this->processOrder($order);
}

vs @with stream

Typical use cases

1. CSV / Excel export

// -- @name StreamAllCustomers
// -- @class Customers
// -- @returns :stream
// SELECT customers.* FROM customers ORDER BY id ASC;

public function export(Request $request): StreamedResponse
{
    return response()->streamDownload(function() {
        $handle = fopen('php://output', 'w');
        fputcsv($handle, ['id', 'email', 'name', 'created_at']);

        foreach ($this->customers->streamAllCustomers() as $customer) {
            fputcsv($handle, [
                $customer->id,
                $customer->email,
                $customer->name,
                $customer->created_at->format('Y-m-d'),
            ]);
        }
        fclose($handle);
    }, 'customers.csv');
}

2. Large batch processing / ETL

-- @name StreamOrdersForBilling
-- @class Orders
-- @returns :stream
SELECT orders.* FROM orders
WHERE status = :status
  AND billing_date = :billing_date
ORDER BY id ASC;

// Process 500k rows without exhausting memory:
$processed = 0;
foreach ($repo->streamOrdersForBilling(status: 'pending', billing_date: '2026-01-01') as $order) {
    $this->billingService->charge($order);
    $processed++;

    if ($processed % 1000 === 0) {
        Log::info("Processed {$processed} orders...");
    }
}

3. Queue dispatch per row

-- @name StreamExpiredSessions
-- @class Sessions
-- @returns :stream
SELECT sessions.* FROM sessions
WHERE expires_at < :cutoff;

foreach ($repo->streamExpiredSessions(cutoff: now()->toDateTimeString()) as $session) {
    CleanupSessionJob::dispatch($session->id);
}

4. Extension trait — stream with domain logic

-- @name StreamRawProfiles
-- @class Profiles
-- @returns :stream
-- @visibility protected
SELECT profiles.* FROM profiles WHERE active = 1 ORDER BY id ASC;

trait ProfilesRepositoryExtension
{
    public function streamActiveProfiles(): \Generator
    {
        foreach ($this->streamRawProfiles() as $profile) {
            // Apply domain logic, filtering, or transformation per row
            if ($this->meetsBusinessRule($profile)) {
                yield $profile;
            }
        }
    }
}

:batch — insert many rows

Executes the same INSERT statement for each row in an array, inside a transaction. The method accepts an array of associative arrays — one per row.

-- @name InsertOrderItems
-- @class Orders
-- @returns :batch
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (:order_id, :product_id, :quantity, :price);

public function insertOrderItems(array $rows): void

// Usage:
$repo->insertOrderItems([
    ['order_id' => 1, 'product_id' => 5, 'quantity' => 2, 'price' => 9.99],
    ['order_id' => 1, 'product_id' => 8, 'quantity' => 1, 'price' => 24.99],
]);

:grouped — JOIN rows grouped into typed arrays

Collapses a flat JOIN result set (one row per join) into grouped objects where the repeated (JOIN-side) columns become a typed array. Requires @group_by.

-- @name GetProfilesWithReserves
-- @class Profiles
-- @returns :grouped
-- @group_by profiles.id
SELECT
    profiles.id,
    profiles.firstname,
    profiles.lastname,
    reserve.id     AS reserve_id,
    reserve.total,
    reserve.status
FROM profiles
LEFT JOIN reserve ON reserve.profile_id = profiles.id;

// Generated files:
// DTOs/GetProfilesWithReservesItem.php  — JOIN-side columns
// DTOs/GetProfilesWithReservesRow.php   — primary columns + item array

readonly class GetProfilesWithReservesRow
{
    public int    $id;
    public string $firstname;
    public string $lastname;
    /** @var GetProfilesWithReservesItem[] */
    public array  $reserve;      // grouped, not flat
}

// Usage:
$profiles = $repo->getProfilesWithReserves();
foreach ($profiles as $profile) {
    foreach ($profile->reserve as $item) {
        echo $item->total;
    }
}

:cursor — keyset pagination

Returns a CursorResult with items, nextCursor, and prevCursor. Used for large datasets where offset pagination is too slow. Requires @cursor to declare the ordering columns.

-- @name GetPosts
-- @class Posts
-- @returns :cursor
-- @cursor created_at DESC, id DESC
-- @with count
SELECT posts.* FROM posts WHERE status = :status
ORDER BY created_at DESC, id DESC;

public function getPosts(string $status, int $limit = 20, ?string $cursor = null): CursorResult

$page1 = $repo->getPosts(status: 'published', limit: 20);
$page2 = $repo->getPosts(status: 'published', limit: 20, cursor: $page1->nextCursor);

:paginator — offset pagination with total

Returns a PaginatedResult with items, total, page, perPage, and lastPage. Wraps the query in a COUNT(*) subquery automatically.

-- @name ListUsers
-- @class Users
-- @returns :paginator
-- @with criteria
SELECT users.* FROM users WHERE status = :status
ORDER BY created_at DESC;

public function listUsers(string $status, int $page = 1, int $perPage = 20, ?UsersRepositoryCriteria $criteria = null): PaginatedResult

$result = $repo->listUsers(status: 'active', page: 2, perPage: 25);
echo $result->total;    // total matching rows
echo $result->lastPage; // last page number
foreach ($result->items as $user) { ... }

:transaction — orchestrate multiple methods

Calls multiple generated methods inside a single database transaction. Declare which methods to call with @calls. If any method throws, the transaction is rolled back.

-- @name TransferFunds
-- @class Accounts
-- @param fromId  int
-- @param toId    int
-- @param amount  float
-- @returns :transaction
-- @calls debitAccount, creditAccount

// Calls debitAccount() then creditAccount() — all or nothing:
$repo->transferFunds(fromId: 1, toId: 2, amount: 100.00);

Extension traits

When extensions: is declared in out:, sqlc-php generates a write-once extension trait for every Model, DTO, Enum, and Query class. Extension files are never overwritten — they belong to you.

Model extension — computed properties

// Extensions/Models/UserExtension.php
trait UserExtension
{
    public function fullName(): string
    {
        return "{$this->first_name} {$this->last_name}";
    }

    public function isAdmin(): bool
    {
        return $this->role_id === 1;
    }
}

Enum extension — presentation layer

// Extensions/Enums/ReserveStatusExtension.php
trait ReserveStatusExtension
{
    public function label(): string
    {
        return match($this) {
            ReserveStatus::Active    => 'Active',
            ReserveStatus::Pending   => 'Pending',
            ReserveStatus::Cancelled => 'Cancelled',
        };
    }

    public function cssClass(): string
    {
        return match($this) {
            ReserveStatus::Active    => 'badge-success',
            ReserveStatus::Pending   => 'badge-warning',
            ReserveStatus::Cancelled => 'badge-danger',
        };
    }
}

Query extension — domain orchestration

// Extensions/Queries/CmsConfigRepositoryExtension.php
trait CmsConfigRepositoryExtension
{
    public function createConfig(CreateConfigParams $params): ?CmsConfig
    {
        return $this->withTransaction(function() use ($params) {
            $existing = $this->getActive(
                country_id: $params->country_id,
                page:       $params->page,
                section:    $params->section,
            );

            if ($existing !== null) {
                $diff = JsonDiffer::diff($existing->config, $params->config);
                if (empty($diff)) return null;
            }

            return $this->insertConfig(InsertConfigParams::from($params->toArray()));
        });
    }
}

withTransaction()

Every generated Query class has a protected withTransaction(callable $fn): mixed helper. Use it in an extension trait for atomic multi-step operations with full PHP flexibility.

protected function withTransaction(callable $fn): mixed

Commits on success, rolls back on any \Throwable. Handles nesting correctly — if a transaction is already active, inner calls are no-ops (the outermost call owns the transaction).

trait OrdersRepositoryExtension
{
    public function createOrderWithItems(int $userId, float $total, array $items): int
    {
        return $this->withTransaction(function() use ($userId, $total, $items) {
            $order   = $this->createOrder($userId, $total);    // :one returning
            $orderId = $order->id;

            $this->insertItems(array_map(
                fn($item) => ['order_id' => $orderId, ...$item],
                $items
            ));  // :batch

            $this->updateInventory(array_column($items, 'product_id'));

            return $orderId;
        });
    }
}

Logging & debugging

Every generated Query class accepts an optional PSR-3 logger and an afterQuery callback in its constructor. After each query executes, a QueryObject is saved and passed to both — giving you SQL, bindings, timing, and cache key with no extra setup.

Constructor parameters

public function __construct(
    private readonly PDO               $pdo,
    private readonly ?LoggerInterface  $logger     = null,   // PSR-3 logger
    private readonly ?Closure          $afterQuery = null,   // custom hook
)

QueryObject — what every query exposes

After any method call, lastQuery() returns a QueryObject with the following API:

PSR-3 logger — automatic query logging

Pass any PSR-3 compatible logger (Monolog, Laravel's Log facade wrapper, etc.). Every executed query is logged at DEBUG level with the method name, duration, SQL, and bound values.

use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$logger = new Logger('db');
$logger->pushHandler(new StreamHandler('storage/logs/queries.log'));

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

// Every query call is now logged automatically:
$users = $repo->listActiveUsers(role_id: 1);
// LOG: getActiveUsers [2.143ms]: SELECT * FROM users WHERE role_id = :role_id  {":role_id": 1}

afterQuery hook — custom integrations

The afterQuery Closure receives the QueryObject after every execution. Use it to integrate with Laravel Debugbar, custom metrics, OpenTelemetry, or any profiling tool.

$repo = new UsersRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q): void {
        // Your custom logic here
        echo sprintf("[%s] %.2fms — %s\n",
            $q->queryName,
            $q->durationMs,
            $q->toString(),
        );
    }
);

lastQuery() — manual inspection

Retrieve the last executed query at any time without a logger or hook.

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

echo $q->toString();
// SELECT users.* FROM users WHERE role_id = :role_id

echo $q->toDebugSql();
// SELECT users.* FROM users WHERE role_id = 1

var_dump($q->durationMs);  // float(2.143)

// Stable cache key:
$cacheKey = $q->cacheKey();   // md5 of SQL + values

Laravel integration examples

1. Service Provider — bind with logger

// app/Providers/RepositoryServiceProvider.php
class RepositoryServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        $this->app->singleton(UsersRepository::class, function (Application $app) {
            return new UsersRepository(
                pdo:    $app->make('db')->getPdo(),
                logger: $app->make('log')->channel('queries'),
            );
        });
    }
}

2. Laravel Debugbar integration

use DebugBar\DataCollector\PDO\TraceablePDO;
use Illuminate\Database\Events\QueryExecuted;

$repo = new UsersRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q) use ($connection): void {
        // Feed into Debugbar's timeline and query tab:
        debugbar()->addMeasure($q->queryName, 0, $q->durationMs / 1000);

        // Or dispatch as a Laravel QueryExecuted event so Debugbar picks it up:
        event(new QueryExecuted(
            $q->toDebugSql(),        // interpolated SQL
            [],                       // bindings already interpolated
            $q->durationMs,
            $connection,
        ));
    }
);

3. Laravel Telescope

$repo = new CmsConfigRepository(
    pdo:        DB::connection()->getPdo(),
    afterQuery: function (QueryObject $q): void {
        // Telescope listens to QueryExecuted events on the DB connection.
        // Dispatch one so it appears in the Telescope queries tab:
        DB::connection()->fireConnectionEvent('query', [
            $q->toString(),
            $q->toDebugBindings(),
            $q->durationMs,
        ]);
    }
);

4. Log slow queries to a dedicated channel

$repo = new OrdersRepository(
    pdo:        $pdo,
    afterQuery: function (QueryObject $q): void {
        if ($q->durationMs > 500) {
            Log::channel('slow_queries')->warning(
                "Slow query: {$q->queryName} ({$q->durationMs}ms)",
                ['sql' => $q->toString(), 'bindings' => $q->values()]
            );
        }
    }
);

5. Testing — assert the executed SQL

// In a test — inspect what SQL was sent to the database:
$user = $repo->findUserByEmail(email: 'alice@example.com');

$q = $repo->lastQuery();
$this->assertStringContainsString('WHERE email', $q->toString());
$this->assertSame('alice@example.com', $q->values()[':email']);
$this->assertLessThan(100, $q->durationMs, 'Query should be fast');