Semantic search in CakePHP with pgvector

Cover photo: "tangle" by jenny downing, licensed under CC BY 2.0.

Github example repo: "cakephp-pgvector-example".

Today we're going to integrate CakePHP and pgvector using an example application.

Why pgvector?

pgvector is an add-on for PostgreSQL, used to store and search data by meaning rather than by exact words. AI models can convert "any" content into long lists of numbers called "vectors" (or embeddings), where similar things end up with similar numbers. For example, "cheap hotel near the beach" and "affordable seaside accommodation" would land close together, even though they share almost no words, because they belong to a similar meaning. A column type is used to hold these vectors and there are very fast ways to find the ones nearest to a given query. Semantic search, recommendations ("more like this"), or chatbots that pull answers from your own documents, are some examples you could implement via pgvector in your projects.

We are building a very simple "almost baked" CakePHP 5 app that catalogs real restaurants (pulled from OpenStreetMap), and lets users search them in plain English, for example: "outdoor seating and vegetarian options". The search is powered by pgvector.

Running it

The repository ships with a DDEV environment (PHP 8.5, PostgreSQL 18, pgvector pre-installed via .ddev/db-build/Dockerfile), so a fresh clone needs no manual database setup:

ddev start
ddev composer install
ddev cake migrations migrate
ddev cake import_restaurants --city=madrid   # or --city=brooklyn

Then visit /restaurants/search?q=outdoor+seating+wine (or whatever your imported city's restaurants mention) on the DDEV-provided URL.

1. Why pgvector + CakePHP

Occasionally, exact-match filters are too strict. Users search "cozy" when your field is ambiance: quiet. And even when filters technically work, they return an unordered set of everything that matches, not the results ranked by how close they are to what the user actually meant.

Vector search fixes this by using embeddings, where "close" vectors represent "similar" meaning. Instead of WHERE cuisine = 'italian', you get ORDER BY embedding <=> :query_vector: a ranked list, driven by shared vocabulary and meaning.

The best thing of doing it with pgvector specifically, is that it needs no separate infrastructure. If your CakePHP app already runs on PostgreSQL, adding semantic search is: enable one extension, add one column, teach the ORM about one new type, and write one query operator.

2. The schema

Before any of this works, the postgresql-18-pgvector package (the compiled .so that implements the vector type) needs to be installed on the database server. See .ddev/db-build/Dockerfile for how the DDEV image does it.

With that in place, the vector extension and the restaurants table are created together in one CakePHP Migrations migration (it uses Phinx's column-builder API under the hood), config/Migrations/20260924120000_CreateRestaurants.php:

public function up(): void
{
    $this->execute('CREATE EXTENSION IF NOT EXISTS vector');

    $table = $this->table('restaurants');
    $table
        ->addColumn('osm_id', 'biginteger', ['null' => false])
        // ... the rest of the regular columns ...
        ->create();

    // Phinx's column-builder API has no native pgvector column type,
    // so we add it with raw SQL after the table exists.
    $this->execute('ALTER TABLE restaurants ADD COLUMN embedding vector(128)');
}

Two things to note: CREATE EXTENSION IF NOT EXISTS vector has to run before the vector type is used (the ALTER TABLE), not necessarily before the table exists; and the column-builder API (addColumn()) doesn't know what a vector column is, hence the raw ALTER TABLE instead.

3. CakePHP --> vector

CakePHP's ORM doesn't know what to do with a Postgres vector column out of the box. We'll create a Cake\Database\Type that knows how to convert between a PHP float[] and the Postgres vector literal format ([0.1,0.2,0.3]). That's src/Database/Type/VectorType.php:

class VectorType extends BaseType
{
    public function toDatabase(mixed $value, Driver $driver): ?string
    {
        if ($value === null) {
            return null;
        }
        if (!is_array($value)) {
            throw new InvalidArgumentException('VectorType expects an array of floats or null.');
        }

        return '[' . implode(',', array_map(
            static fn (float $v): string => rtrim(rtrim(sprintf('%.10f', $v), '0'), '.') ?: '0',
            array_map('floatval', $value)
        )) . ']';
    }

    public function toPHP(mixed $value, Driver $driver): ?array
    {
        if ($value === null) {
            return null;
        }
        $trimmed = trim((string)$value, '[]');
        if ($trimmed === '') {
            return [];
        }

        return array_map('floatval', explode(',', $trimmed));
    }

    public function toStatement(mixed $value, Driver $driver): int
    {
        return PDO::PARAM_STR;
    }

    public function marshal(mixed $value): mixed
    {
        if (is_array($value)) {
            return array_map('floatval', $value);
        }

        return $value;
    }
}

Registering it is one line in config/bootstrap.php:

\Cake\Database\TypeFactory::map('vector', \App\Database\Type\VectorType::class);

Mapping the type isn't enough on its own: CakePHP's Postgres schema reflection doesn't recognize vector as a known column type, and falls back to treating it as string, so TypeFactory::map() never gets applied to it. setColumnType() overrides that per column, in RestaurantsTable::initialize():

public function initialize(array $config): void
{
    // ...

    $this->getSchema()->setColumnType('embedding', 'vector');
}

After this, $restaurant->embedding is always a plain PHP float[] (or null): the entity, the finder, and the controller never touch a vector literal string directly.

4. The embedding function, and its trade-offs

Turning text into a vector normally means calling an embedding API. To keep this example self-contained (no API key, no network calls), src/Vector/EmbeddingGenerator.php builds a vector without one. It lowercases the text, strips everything that isn't a letter or digit, and hashes each token into one of 128 buckets (crc32($token) % 128). Counting hits per bucket, the result is then L2-normalized. It's a hashed bag-of-words, not a trained embedding.

Two restaurants whose descriptions share vocabulary will land close together in this scheme. However, with this toy generator alone we won't be able to match higher concepts like "cheap" to "affordable" or "quiet" to "peaceful". That requires an actual trained embedding model. It's also lossy: unrelated tokens can hash into the same bucket and nudge unrelated restaurants closer than they should be. Good enough for this example, not for production.

To connect a real embedding API instead, replace EmbeddingGenerator::embed() with something like:

public function embed(string $text): array
{
    $response = $this->httpClient->post('https://api.openai.com/v1/embeddings', [
        'model' => 'text-embedding-3-small',
        'input' => $text,
    ], ['type' => 'json', 'headers' => ['Authorization' => 'Bearer ' . $this->apiKey]]);

    return $response->getJson()['data'][0]['embedding'];
}

(Cake\Http\Client posts form-encoded data by default; 'type' => 'json' is what makes it send a JSON body, which is what OpenAI's endpoint expects.)

You'd also change vector(128) to whatever dimension that model returns: text-embedding-3-small returns 1536, for example. Changing the dimension means updating it in three places: the migration, EmbeddingGenerator, and re-embedding any rows that were saved with the old dimension. A vector saved with the wrong dimension isn't silently accepted either: pgvector raises an "expected N dimensions, not M" error on INSERT and on any <=> comparison against a mismatched vector.

5. Turning open data into descriptions

The catalog is seeded from real data via the OpenStreetMap Overpass API, which needs no API key. src/Vector/OverpassClient.php queries it for restaurant nodes in a bounding box and filters out anything without a name.

OpenStreetMap tags are structured (cuisine=american, outdoor_seating=yes, diet:vegan=yes), not text, and we need text to be able to produce vectors later. src/Vector/DescriptionBuilder.php turns tags into a sentence, e.g. "American restaurant in Brooklyn serving cocktails and wine, with outdoor seating, open late." Missing tags are simply left out of the sentence rather than filled with placeholder text.

A command was created to import and populate the database using OpenStreetMap bin/cake import_restaurants

• Pull restaurants from OpenStreetMap
• build a description
• embed it
• upsert by OpenStreetMap's node id

$ bin/cake import_restaurants --city=brooklyn
Imported 597 restaurants.

6. The search query

The actual similarity query lives in a custom finder, RestaurantsTable::findSimilarTo():

/**
 * @param array<int, float> $vector
 */
public function findSimilarTo(SelectQuery $query, array $vector): SelectQuery
{
    $query
        ->bind(':vector1', $vector, 'vector')
        ->bind(':vector2', $vector, 'vector');

    return $query
        // Rows with no embedding yet (import/save failed, or predates this
        // feature) can't be compared or ranked, so exclude them explicitly.
        ->where(['embedding IS NOT' => null])
        ->selectAlso(['distance' => $query->expr('embedding <=> :vector1')])
        ->orderByAsc($query->expr('embedding <=> :vector2'));
}

<=> is pgvector's cosine-distance operator: 0 means identical direction, 2 means opposite, and smaller means more similar (pgvector also has <-> for Euclidean/L2 distance and <#> for negative inner product, if you need those instead). Since EmbeddingGenerator already L2-normalizes its output, cosine distance here is effectively measuring shared-token overlap.

The query vector is bound as a real parameter (typed vector, reusing the same VectorType::toDatabase() that serializes the column on save), so it's never interpolated into the SQL string. PDO's pgsql driver uses real, server-side prepared statements. Unlike the emulated prepares MySQL typically uses, these don't allow the same named placeholder to appear twice in one query, so we bind two placeholders (:vector1, :vector2) with the same value instead of one.

With the finder in place, the controller action is short:

public function search(): void
{
    $query = trim((string)$this->request->getQuery('q', ''));
    $results = [];

    if ($query !== '') {
        $embedding = (new EmbeddingGenerator())->embed($query);
        // A query with no recognizable tokens (empty, or just punctuation)
        // embeds to an all-zero vector; cosine distance against a zero
        // vector is undefined (NaN in pgvector), so skip the search.
        $hasSignal = array_sum(array_map('abs', $embedding)) > 0.0;
        if ($hasSignal) {
            $results = $this->Restaurants->find('similarTo', vector: $embedding)->limit(10)->toArray();
        }
    }

    $this->set(compact('query', 'results'));
}

The user's query text goes through the exact same EmbeddingGenerator algorithm, producing the vector we compare against; the finder returns the closest restaurants.

7. Next steps

Here a couple directions/ideas to build on top of this example code:

• An ANN index. At a few hundred rows, a full sequential scan with ORDER BY embedding <=> ... is fast enough. After a few thousand rows, add an IVFFlat or HNSW index on the embedding column to keep things snappy.
• Hybrid search. Combining Postgres full-text search (tsvector) with the vector distance often beats pure vector search for queries that include a specific an exact term.
• A real embedding model. Swap EmbeddingGenerator::embed() for a call to an embedding API, as shown in section 4, once you want genuine semantic search in effect!.

Github example repo: "cakephp-pgvector-example".