Edit

#Type System

• ⬅️ Back

The client handles type conversion between PHP and PostgreSQL in two directions:

• Parameter binding: Converting PHP values to PostgreSQL format when executing queries
• Result casting: Converting PostgreSQL results back to PHP types

#Result Casting

When fetching data, PostgreSQL returns all values as strings. The client automatically casts unambiguous types to their native PHP equivalents:

Why only these types? Types like JSON, UUID, timestamps, and arrays have multiple valid PHP representations. Rather than choosing one, the client returns them as strings, letting you parse them as needed:

<?php

use function Flow\PostgreSql\DSL\{pgsql_client, pgsql_connection};

$client = pgsql_client(pgsql_connection('host=localhost dbname=mydb'));

// JSON comes back as string - parse it yourself
$row = $client->fetch('SELECT metadata FROM events WHERE id = $1', [1]);
$metadata = json_decode($row['metadata'], associative: true);

// UUID comes back as string
$row = $client->fetch('SELECT id FROM users WHERE email = $1', ['[email protected]']);
$uuid = $row['id'];  // '550e8400-e29b-41d4-a716-446655440000'

// Timestamps come back as strings
$row = $client->fetch('SELECT created_at FROM users WHERE id = $1', [1]);
$createdAt = new \DateTimeImmutable($row['created_at']);

#Parameter Binding

PHP values are automatically converted when binding parameters:

#The Array Ambiguity

Plain arrays cannot be bound directly because PostgreSQL has both JSON and native ARRAY types:

<?php

// This will throw ValueConversionException
$client->execute('INSERT INTO events (data) VALUES ($1)', [['key' => 'value']]);
// Error: Cannot determine array type - use typed() to specify JSON or ARRAY

// Use typed() to be explicit
use function Flow\PostgreSql\DSL\typed;
use Flow\PostgreSql\Client\Types\ValueType;

// As JSON
$client->execute(
    'INSERT INTO events (data) VALUES ($1)',
    [typed(['key' => 'value'], ValueType::JSON)]
);

// As PostgreSQL array
$client->execute(
    'INSERT INTO tags (names) VALUES ($1)',
    [typed(['php', 'postgresql'], ValueType::TEXT_ARRAY)]
);

#Explicit Type Hints with typed()

When automatic detection isn't sufficient, use typed() to specify the exact PostgreSQL type:

<?php

use function Flow\PostgreSql\DSL\{
    pgsql_client, pgsql_connection, typed,
    value_type_uuid, value_type_json, value_type_date, value_type_int4_array
};

$client = pgsql_client(pgsql_connection('host=localhost dbname=mydb'));

// UUID - string that should be treated as UUID
$client->fetch(
    'SELECT * FROM users WHERE id = $1',
    [typed('550e8400-e29b-41d4-a716-446655440000', value_type_uuid())]
);

// JSON - array to be sent as JSON
$client->execute(
    'INSERT INTO events (payload) VALUES ($1)',
    [typed(['event' => 'login', 'user_id' => 42], value_type_json())]
);

// DATE - DateTime that should be DATE, not TIMESTAMP
$client->fetch(
    'SELECT * FROM events WHERE date = $1',
    [typed(new \DateTimeImmutable('2024-01-15'), value_type_date())]
);

// Integer array
$client->execute(
    'INSERT INTO scores (values) VALUES ($1)',
    [typed([100, 200, 300], value_type_int4_array())]
);

You can also use the ValueType enum directly if you prefer:

<?php

use function Flow\PostgreSql\DSL\typed;
use Flow\PostgreSql\Client\Types\ValueType;

$client->execute(
    'INSERT INTO events (payload) VALUES ($1)',
    [typed(['event' => 'login'], ValueType::JSON)]
);

#Type DSL Functions

#Built-in Value Converters

The client includes converters for all common PostgreSQL types. These converters handle the toDatabase() conversion when you use typed():

#Converter Notes

• FloatConverter: Handles special PostgreSQL float values (Infinity, -Infinity, NaN)
• Array Converters: Type-specific converters for PostgreSQL arrays. Each validates element types strictly:
  • BoolArrayConverter: Expects bool elements
  • IntArrayConverter: Expects int elements
  • FloatArrayConverter: Expects float elements
  • TextArrayConverter: Expects string or scalar elements
  • UuidArrayConverter: Expects string elements (UUID format)
  • JsonArrayConverter: Expects array or object elements (JSON-encodable)
  • All converters support null elements (converted to PostgreSQL NULL)
  • Invalid element types throw ValueConversionException
• JsonConverter: Accepts PHP arrays, objects, or already-encoded JSON strings
• NumericConverter: Preserves precision as string to avoid floating-point errors

#Custom ValueConverters

Implement ValueConverter for custom type handling:

<?php

use Flow\PostgreSql\Client\Types\{ValueConverter, ValueType};

// Example: Custom Money type
readonly class Money
{
    public function __construct(
        public int $cents,
        public string $currency = 'USD',
    ) {}
}

readonly class CustomMoneyConverter implements ValueConverter
{
    public function supportedTypes(): array
    {
        return [ValueType::MONEY, ValueType::NUMERIC];
    }

    public function toDatabase(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        if ($value instanceof Money) {
            return (string) ($value->cents / 100);
        }

        return (string) $value;
    }
}

#Registering Custom Converters

Add converters when creating the client:

<?php

use Flow\PostgreSql\Client\Types\ValueConverters;

use function Flow\PostgreSql\DSL\{pgsql_client, pgsql_connection};

// Create converters with defaults + register a custom one
$converters = ValueConverters::create();
$converters->register(new CustomMoneyConverter());

$client = pgsql_client(
    pgsql_connection('host=localhost dbname=mydb'),
    valueConverters: $converters,
);

#ValueConverters Registry

The ValueConverters class manages type converters:

<?php

use Flow\PostgreSql\Client\Types\{PostgreSqlVersion, ValueConverters, ValueType};

// Create with defaults for PostgreSQL version
$converters = ValueConverters::create(PostgreSqlVersion::V16);

// Register a custom converter (mutates the registry in place)
$converters->register(new CustomMoneyConverter());

// Check if a converter exists for a type
if ($converters->has(ValueType::UUID)) {
    // UUID converter is registered
}

// Get converter for a specific PostgreSQL type
$converter = $converters->forValueType(ValueType::JSONB);

// Remove a converter
$converters->unregister(ValueType::MONEY);

#PostgreSQL Type Reference

PostgreSQL types are identified by OID (Object ID). The ValueType enum exposes the common OIDs used by the client's converters and by typed():

<?php

use Flow\PostgreSql\Client\Types\ValueType;

// Scalar types
ValueType::TEXT      // 25
ValueType::INT4      // 23
ValueType::INT8      // 20
ValueType::FLOAT8    // 701
ValueType::BOOL      // 16
ValueType::BYTEA     // 17

// Date/time types
ValueType::TIMESTAMP   // 1114
ValueType::TIMESTAMPTZ // 1184
ValueType::DATE        // 1082
ValueType::TIME        // 1083
ValueType::INTERVAL    // 1186

// Special types
ValueType::UUID      // 2950
ValueType::JSON      // 114
ValueType::JSONB     // 3802
ValueType::NUMERIC   // 1700
ValueType::MONEY     // 790

// Array types
ValueType::TEXT_ARRAY // 1009
ValueType::INT4_ARRAY // 1007
ValueType::UUID_ARRAY // 2951

#Handling NULL Values

NULL handling is consistent across all operations:

<?php

// NULL in parameters
$client->execute(
    'INSERT INTO users (name, bio) VALUES ($1, $2)',
    ['John', null]  // bio will be NULL
);

// NULL in results
$user = $client->fetch('SELECT * FROM users WHERE id = $1', [1]);
if ($user['bio'] === null) {
    echo "No bio set";
}

// Typed NULL (when type matters for schema validation)
$client->execute(
    'INSERT INTO events (metadata) VALUES ($1)',
    [typed(null, ValueType::JSON)]
);