1. Go to this page and download the library: Download phpibe/sqlc-php library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
phpibe / sqlc-php example snippets
/** @return User[] */
public function listUsers(): array
/** @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
public function getUserProfile(?int $id): User
public function getUserWithRole(?int $id): GetUserWithRoleRow
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
) {}
}
/**
* @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
public function listActiveUsers(?string $status = null, int $limit = 20, int $offset = 0): array
/**
* @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),
);
}
/**
* @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
public function filterByIdsAndRoles(array $ids, array $roleIds): array
public function excludeIds(array $excludedIds): array
public function updateUserActive(?bool $active, ?string $updatedAt, ?int $id): void
public function deleteUser(?int $id): void
// OrderStatus.php — generated by sqlc-php
enum OrderStatus: string
{
case Pending = 'pending';
case Processing = 'processing';
case Completed = 'completed';
case Cancelled = 'cancelled';
}
// in Order.php
public OrderStatus $status,
// in fromRow()
OrderStatus::from((string) $row['status']),
public ?OrderStatus $status,
// in fromRow()
isset($row['status']) ? OrderStatus::tryFrom((string) $row['status']) : null,
// in Order.php
public ?array $metadata,
// in fromRow()
isset($row['metadata']) ? json_decode((string) $row['metadata'], true) : null,
/**
* @deprecated Use getUserById instead
* @param ?int $id
* @return User
*/
public function getUser(?int $id): User
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
) {}
}
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,
);
}
}
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
);
}
}
public function __construct(
public ?int $id,
public string $email,
public Role $role, // prefix: role_
public Address $addr, // prefix: addr_
) {}
/**
* @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
// 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');
// roleId is ion getUsersByRole(int $roleId, ?string $status = null): array
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 { ... }
}
class UserQuery implements UserQueryInterface
{
public function __construct(private readonly PDO $pdo) {}
/** @return User[] */
public function listUsers(): array { ... }
/** @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 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;
}
$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('[email protected]');
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');
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,
);
}
}
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)
);
});
}
}
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);
}
}
class SendWelcomeEmail implements ShouldQueue
{
public function __construct(private readonly UserRepository $userRepository) {}
public function handle(): void
{
$user = $this->userRepository->getUserByEmail($this->email);
// ...
}
}
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: '[email protected]', username: 'alice',
// ...
));
$this->app->instance(UserQueryInterface::class, $mock);
$this->getJson('/api/users/1')->assertOk();
}
}
(new UserCriteria())
->whereActiveEq(1)
->orGroup(fn($c) => $c->whereCountryIdEq(164))
->orGroup(fn($c) => $c->whereCountryIdEq(165))
// WHERE active = :active_f0 OR country_id = :country_id_f1 OR country_id = :country_id_f2
yaml
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
bash
php vendor/bin/sqlc-php --verify sqlc.yaml
✗ 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.
bash
php vendor/bin/sqlc-php --dry-run sqlc.yaml
──────────────────────────────────────────────────────────────────────
// generated/User.php
──────────────────────────────────────────────────────────────────────
declare(strict_types=1);
// ...
✓ Dry run complete. 4 file(s) would be written.
bash
php vendor/bin/sqlc-php --diff sqlc.yaml
Before (v2.9.2): DTOs/GetDetails/ReserveBilling.php
After (v2.9.3): DTOs/ReserveBilling/GetDetails/ReserveBilling.php
↑ Group ↑ Method
bash
# Generate schema from live DB and write to schema.sql (as declared in sqlc.yaml)
php vendor/bin/sqlc-php --generate-schema sqlc.yaml
# Write to a custom path
php vendor/bin/sqlc-php --generate-schema --schema-output=db/schema.sql sqlc.yaml