Working with Advanced Column Types

Propulsion offers a set of advanced column types, implemented database-agnostically so they work the same way across every supported RDBMS.

blob columns

Propulsion stores Binary Large Objects (BLOBs) internally as PHP streams. This mirrors PDO’s own convention of using streams when returning LOB columns in a result set and when binding values to prepared statements. If a PDO driver returns the raw string contents instead of a stream, Propulsion wraps it in a php://memory stream, giving you a consistent API regardless of driver.

Note that CLOBs (Character Locator Objects) are treated as plain strings, since there’s no equivalent PDO convention for treating them as streams.

Getting blob values

Blob values are returned as PHP stream resources from the generated accessor methods. If the value is NULL in the database, the accessor returns PHP null.

<?php
$media = MediaQuery::create()->findPk(1);
$fp = $media->getCoverImage();
if ($fp !== null) {
    echo stream_get_contents($fp);
}

Setting blob values

When setting a blob column, you can pass either a stream or the raw blob contents:

<?php
// Setting using a stream
$fp = fopen('/path/to/file.ext', 'rb');
$media = new Media();
$media->setCoverImage($fp);

// Setting using file contents
$media = new Media();
$media->setCoverImage(file_get_contents('/path/to/file.ext'));

Regardless of which form you use to set it, the blob is always represented internally as a stream resource — subsequent calls to the accessor return a stream:

<?php
$media = new Media();
$media->setCoverImage(file_get_contents('/path/to/file.ext'));

$fp = $media->getCoverImage();
echo gettype($fp); // "resource"

Setting blob columns and isModified()

Because a stream’s contents can be modified externally, mutator methods for blob columns always mark the object as modified — even if the stream passed in has the same identity as the stream previously returned:

<?php
$media = MediaQuery::create()->findPk(1);
$fp = $media->getCoverImage();
$media->setCoverImage($fp);

var_export($media->isModified()); // true

enum columns

Enum columns are stored in the database as integers but let you manipulate a set of predefined string values without worrying about storage details.

<table name="book">
  <!-- ... -->
  <column name="style" type="enum" valueSet="novel, essay, poetry" />
</table>

<?php
// The Active Record setter/getter accept and return any value from the valueSet
$book = new Book();
$book->setStyle('novel');
echo $book->getStyle(); // novel
// Setting a value not in the valueSet throws an exception

// Enum columns are searchable via the generated filterByXXX() method,
// or other ModelCriteria methods (where(), condition())
$books = BookQuery::create()
    ->filterByStyle('novel')
    ->find();

object columns

The object column type stores PHP objects in the database. The generated setter serializes the object, storing it as a string; the generated getter unserializes the string back into an object. For the end user, the column behaves as if it simply contained the object.

Getting and setting object values

<?php
class GeographicCoordinates
{
    public function __construct(
        public float $latitude,
        public float $longitude,
    ) {
    }

    public function isInNorthernHemisphere(): bool
    {
        return $this->latitude > 0;
    }
}

// The 'house' table has a 'coordinates' column of type object
$house = new House();
$house->setCoordinates(new GeographicCoordinates(48.8527, 2.3510));
echo $house->getCoordinates()->isInNorthernHemisphere(); // true
$house->save();

Retrieving records based on object values

object columns are also searchable using the generated filterByXXX() method on the query class:

<?php
$house = HouseQuery::create()
    ->filterByCoordinates(new GeographicCoordinates(48.8527, 2.3510))
    ->find();

Propulsion looks in the database for a serialized version of the object passed as the filterByXXX() argument.

array columns

An array column stores a simple PHP array in the database — nested arrays and associative arrays aren’t accepted. The generated setter serializes the array to a string; the generated getter unserializes it back into an array.

Getting and setting array values

<?php
// The 'book' table has a 'tags' column of type array
$book = new Book();
$book->setTags(['novel', 'russian']);
print_r($book->getTags()); // ['novel', 'russian']

// If the column name is plural, Propulsion also generates hasXXX(), addXXX(),
// and removeXXX() methods, where XXX is the singular column name
echo $book->hasTag('novel'); // true
$book->addTag('romantic');
print_r($book->getTags()); // ['novel', 'russian', 'romantic']
$book->removeTag('russian');
print_r($book->getTags()); // ['novel', 'romantic']

Retrieving records based on array values

Propulsion doesn’t use serialize() for array columns — it uses a special serialization format that makes searching by value possible:

<?php
// Search books that contain all the specified tags
$books = BookQuery::create()
    ->filterByTags(['novel', 'russian'], Criteria::CONTAINS_ALL)
    ->find();

// Search books that contain at least one of the specified tags
$books = BookQuery::create()
    ->filterByTags(['novel', 'russian'], Criteria::CONTAINS_SOME)
    ->find();

// Search books that don't contain any of the specified tags
$books = BookQuery::create()
    ->filterByTags(['novel', 'russian'], Criteria::CONTAINS_NONE)
    ->find();

// If the column name is plural, Propulsion also generates a singular filter
// method expecting a scalar parameter instead of an array
$books = BookQuery::create()
    ->filterByTag('russian')
    ->find();

Tip

Filters on array columns translate to LIKE conditions in SQL, so the resulting query often requires a full table scan — not well suited to large tables.

Caution

Only generated Query classes (via filterByXXX()) and ModelCriteria (via where() and condition()) support conditions on enum, object, and array columns. Plain Criteria (via add(), addAnd(), addOr()) does not.

json/jsonb columns

Unlike object and array columns, which serialize with PHP’s serialize() (or a custom delimited format), json/jsonb columns store real JSON text via json_encode()/json_decode(). Use these when the stored value needs to be readable/queryable as JSON by other tools (psql’s JSON operators, a JS client reading the column directly, etc.), not just round-tripped through PHP.

<table name="product">
  <!-- ... -->
  <column name="attributes" type="JSON" />
  <!-- or type="JSONB" on PostgreSQL, for the decomposed binary form -->
</table>

Getting and setting json/jsonb values

<?php
$product = new Product();
$product->setAttributes(['color' => 'red', 'sizes' => [38, 39, 40]]);
$product->save();

$product = ProductQuery::create()->findPk(1);
print_r($product->getAttributes()); // ['color' => 'red', 'sizes' => [38, 39, 40]]

// Scalars and null round-trip too, since these are real JSON values, not just arrays
$product->setAttributes(null);
$product->setAttributes('a plain string is valid JSON content');
$product->setAttributes(42);

Because json_decode() can produce an array, a scalar, or null depending on what’s stored, the generated getter/setter have no single applicable PHP type — both are typed mixed, the same way object columns are.

Malformed data

If a json/jsonb column somehow contains malformed JSON (e.g. it was written outside Propulsion, or a driver truncated it), the generated hydration code raises a Propulsion\Exception\PropulsionException naming the offending column, rather than returning null or silently corrupting the value:

<?php
// If the 'attributes' column contains invalid JSON text:
$product = ProductQuery::create()->findPk(1);
// throws PropulsionException: "Malformed JSON in column [attributes]"

The same applies in reverse when saving a value that can’t be represented as JSON (e.g. a resource, or malformed UTF-8) — PropulsionException: "Unable to encode value for JSON column [attributes]".

Platform mapping

JSON and JSONB map to native JSON/JSONB on PostgreSQL (pick whichever suits your access pattern — JSONB is faster to query/index, JSON preserves the exact input text and key order), native JSON on MySQL (which has no separate binary variant, so both Propulsion types map there), and a text fallback elsewhere: CLOB on Oracle, TEXT on SQLite, VARCHAR(MAX) on MSSQL.

To query inside a document, use whereJsonPath() and JsonExpression rather than a generated accessor — filterByAttributes() compares the whole column:

ProductQuery::create()
    ->withColumn(JsonExpression::text('Product.Attributes', '$.colour'), 'Colour')
    ->whereJsonPath('Product.Attributes', '$.dimensions.width', '120')
    ->find();

Both are documented in reading inside a JSON column, including the text-versus-JSON distinction that decides whether a comparison matches and the path subset that works on every platform.

uuid columns

A uuid column stores a UUID in its canonical hyphenated hexadecimal form (8ddb2ec4-f996-4777-b4f4-d59399530734), validated and normalized on the way in.

<table name="user">
  <!-- ... -->
  <column name="external_id" type="UUID" required="true" />
</table>

Getting and setting uuid values

<?php
$user = new User();
$user->setExternalId('8DDB2EC4-F996-4777-B4F4-D59399530734');
echo $user->getExternalId(); // '8ddb2ec4-f996-4777-b4f4-d59399530734' -- lower-cased

// Malformed input throws rather than storing garbage
$user->setExternalId('not-a-uuid'); // throws PropulsionException

The generated setter validates the value against the canonical 8-4-4-4-12 hex-digit pattern before accepting it — a value that doesn’t match raises a Propulsion\Exception\PropulsionException instead of being stored as-is.

Platform mapping

UUID maps to PostgreSQL’s native uuid column type, and to CHAR(36) on every other supported RDBMS (MySQL, SQLite, Oracle, MSSQL). Because a UUID column is treated as a text type for filtering/hydration purposes, filterByXXX(), where()/condition(), and even plain Criteria::add() all work on it exactly like a VARCHAR column — no restrictions like the ones on enum/object/array/json above.

Tip

UUID is not the same as Propel 2’s UUID_BINARY type (16-byte binary storage) — Propulsion doesn’t implement that. See UUID and binary columns if you specifically need binary storage.

The rest of the advanced types

Propulsion has several more types beyond the ones with generated-accessor quirks worth a walkthrough. They’re documented in the schema reference, since for most of them the interesting part is the DDL and the platform mapping rather than the PHP API:

Type	PHP value	Notes
SET	array<string>	Any subset of a fixed label vocabulary. Native SET(...) on MySQL, comma-joined text elsewhere.
INTERVAL	DateInterval	Native interval on PostgreSQL, ISO-8601 text elsewhere.
INET, CIDR, MACADDR, CITEXT	string	Native on PostgreSQL, sized VARCHAR elsewhere. Case-insensitivity is lost off PostgreSQL.
Range types	Propulsion\Type\Range	Six of them (INT4RANGE … TSTZRANGE). Native on PostgreSQL, range-literal text elsewhere.
VECTOR	array<float>	Native pgvector on PostgreSQL, opt-in nativeVector="true" on MySQL 9 / MariaDB 11.7, text elsewhere. Nearest-N queries via VectorExpression.
TSVECTOR	string	Full-text search documents, best populated via tsvectorFrom on PostgreSQL.
GEOMETRY	string (WKT)	Text on every platform, deliberately — see the page for why.
DECIMAL as BcMath\Number	BcMath\Number	Opt in with phpType="\BcMath\Number" for real decimal arithmetic instead of a string.
ENUM with enumClass	a backed PHP enum	Work with enum instances instead of label strings; valueSet is derived from the enum’s own cases.