sqlc-php

How it works

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

• PHP 8.3+
• PDO extension

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

Configuration — sqlc.yaml

version: "1"
schema:  schema.sql    # single file (scalar) or list of files
queries: queries.sql   # single file (scalar) or list of files

php:
  namespace:           "App\\Database"  # PHP namespace for all generated classes
  out:                 generated        # output directory
  engine:              mysql            # database engine (mysql supported)
  generate_interfaces: true             # generate *Interface alongside each Query class

# Optional type overrides
type_overrides:
  - column:   "users.active"        # target a specific table.column
    php_type: "bool"

  - db_type:  "TINYINT"             # target every column of this SQL type
    php_type: "bool"

  - db_type:  "TIMESTAMP"
    php_type: "\\DateTimeImmutable"
    nullable: true                    # force nullable regardless of schema

Multiple schema files

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

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

Multiple query files

queries accepts both a scalar string and a YAML list of paths:

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
Queries: 8 query(ies) from database/queries/users.sql
Queries: 3 query(ies) from database/queries/orders.sql
Queries: 11 total query(ies) parsed

Multiple output targets

targets lets you generate multiple namespaces and output directories from the same schema in a single run. Each target has its own namespace, out, queries, and optional overrides merged on top of the root-level ones:

version: "1"
schema:
  - database/schema/users.sql
  - database/schema/orders.sql

# Global overrides shared across all targets
type_overrides:
  - db_type: "TIMESTAMP"
    php_type: "\\DateTimeImmutable"
    nullable: true

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

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

Type override precedence

Nullable override

Any type_override entry accepts an optional nullable field that forces nullability regardless of the schema:

type_overrides:
  # Force nullable even though the column is NOT NULL in the schema
  - column:   "users.deleted_at"
    php_type: "\\Carbon\\Carbon"
    nullable: true

  # Force NOT nullable for all TIMESTAMP columns
  - db_type:  "TIMESTAMP"
    php_type: "\\DateTimeImmutable"
    nullable: false

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

Default SQL → PHP type mapping

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 | :many-paginated | :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

Return type semantics

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

-- @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;

/** @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

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

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;

public function getUserWithRole(?int $id): GetUserWithRoleRow

Aggregate and expression columns

sqlc-php infers types from SQL functions. Aliases are generated automatically when none is provided:

-- @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;

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

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

:many-paginated — automatic pagination

Using :many-paginated auto-appends LIMIT :limit OFFSET :offset to the SQL and adds those two parameters 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

User-defined parameters appear first; $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

MySQL ENUM → PHP backed enum

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

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

// in Order.php — fromRow() uses ::from() for NOT NULL, ::tryFrom() for nullable
OrderStatus::from((string) $row['status']),

JSON column → typed array

CREATE TABLE orders (
    metadata JSON null
);

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

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

@deprecated — mark a method as deprecated

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

/**
 * @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

Useful in two scenarios:

LEFT JOIN — column may be NULL at runtime

-- @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;

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 *)

When @nillable is used on a single-table SELECT * query, sqlc-php generates a dedicated *Row DTO instead of reusing the table model, so 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;

@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;

Generates two files:

Role.php — standalone readonly value object

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'],         // uses original prefixed key
            $row['role_description'] ?? null,
        );
    }
}

GetUserWithRoleRow.php — parent DTO

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

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

Usage

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

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

Multiple @embed groups

-- @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:

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

Optional parameters

Marking a parameter as @optional rewrites the SQL condition at generation time. Passing null skips the filter entirely — 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)

/**
 * @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

Required parameters always appear first; optional parameters follow with = null:

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

Supported operators

Parameter type resolution

Named parameters (:paramName) are automatically typed by matching them to schema columns. Resolution order:

1. @param annotation — explicit override: -- @param userId users.id
2. Qualified reference — WHERE table.col = :param
3. SET clause — SET col = :param
4. camelCase → snake_case — :updatedAt → looks up updated_at in the schema
5. Fallback — mixed / PDO::PARAM_STR

Generated file structure

Model class example (User.php)

readonly class User
{
    public function __construct(
        public ?int    $id,
        public string  $email,
        public ?string $username,
        public ?bool   $active,        // overridden via type_overrides
        public int     $role_id,
        public ?string $created_at,
    ) {}

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

Query class example (UserQuery.php)

class UserQuery implements UserQueryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    /** @return User[] */
    public function listUsers(): array { ... }

    /** @return User[] */
    public function listUsersPaginated(int $limit = 20, int $offset = 0): array { ... }  // :many-paginated

    /** @return User */
    public function getUser(?int $id): User { ... }                     // :one — throws

    /** @return User|null */
    public function getUserByEmail(string $email): ?User { ... }        // :opt — nullable

    public function deleteUser(?int $id): void { ... }                  // :exec

    /** @return User[] */
    public function searchUsers(
        ?string $status   = null,   // @optional — pass null to skip filter
        ?string $username = null,   // @optional — pass null to skip filter
    ): array { ... }
}

Interface example (UserQueryInterface.php)

interface UserQueryInterface
{
    /** @return User[] */
    public function listUsers(): array;

    /** @return User */
    public function getUser(?int $id): User;

    /** @return User|null */
    public function getUserByEmail(string $email): ?User;

    public function deleteUser(?int $id): void;

    /**
     * @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;
}

Usage in your application

$pdo  = new PDO('mysql:host=localhost;dbname=myapp', 'user', 'pass');
$repo = new UserQuery($pdo);

// :many — always an array
$users = $repo->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

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

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);
    }

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

2. Register in a Service Provider

public function register(): void
{
    $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)
        );
    });
}

3. Inject into a controller

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);
    }
}

5. Testing with the interface

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

--verify — CI check

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

# Add to your CI pipeline:
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

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
──────────────────────────────────────────────────────────────────────
<?php
declare(strict_types=1);
// ...

✓ Dry run complete. 4 file(s) would be written.

--diff — show what would change

Compares generated content against existing files and prints a colored unified diff. Exits 0 when nothing would change, 1 when there are differences. Writes nothing.

php vendor/bin/sqlc-php --diff sqlc.yaml

--- generated/User.php (current)
+++ generated/User.php (generated)
  public ?int $id,
- public string $email,
+ public ?string $email,
  public ?bool $active,

Running the tests

phpunit --configuration phpunit.xml

Project structure

Changelog