Use vector search with SQLAlchemy in Azure HorizonDB

SQLAlchemy is a widely used Python SQL toolkit and object-relational mapper (ORM). By using the open-source pgvector-python package, you can declare vector columns and DiskANN indexes directly in your SQLAlchemy models. Then you can run similarity search queries with SQLAlchemy expressions instead of raw SQL.

DiskANN doesn't need a dedicated SQLAlchemy dialect or plugin. SQLAlchemy's PostgreSQL Index options pass the index access method, operator class, and build parameters straight through to CREATE INDEX.

In this article, you learn how to:

  • Connect SQLAlchemy to Azure HorizonDB with password or Microsoft Entra authentication.
  • Declare vector and halfvec columns in a model.
  • Create DiskANN indexes, including build parameters such as spherical quantization.
  • Run cosine, Euclidean (L2), and inner product similarity queries.
  • Generate embeddings and rerank results in the database with azure_ai functions.
  • Use AI functions for embeddings, text and structured generation, extraction, truth evaluation, and reranking.
  • Confirm that queries use the DiskANN index.

Prerequisites

Install packages

Install SQLAlchemy 2.0, the pgvector integration, a PostgreSQL driver, and NumPy:

pip install "sqlalchemy>=2.0" pgvector "psycopg[binary]" numpy

To use Microsoft Entra authentication, also install the Azure Identity library:

pip install azure-identity

Connect to Azure HorizonDB

Azure HorizonDB supports password-based and Microsoft Entra authentication. You can use either method in this article.

Use password authentication

import os
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

engine = create_engine(
    URL.create(
        "postgresql+psycopg",
        username=os.environ["PGUSER"],
        password=os.environ["PGPASSWORD"],
        host=os.environ["PGHOST"],  # <cluster-name>.<id>.<region>.horizondb.azure.com
        port=int(os.environ.get("PGPORT", "5432")),
        database=os.environ.get("PGDATABASE", "postgres"),
    ),
    connect_args={"sslmode": "require", "connect_timeout": 15},
    pool_pre_ping=True,
    hide_parameters=True,
)

URL.create() handles special characters in passwords without manual URL encoding. Keep credentials in environment variables, not in source code.

Use Microsoft Entra authentication

Microsoft Entra access tokens expire, so request a new token every time SQLAlchemy opens a connection:

import os
from azure.identity import DefaultAzureCredential
from sqlalchemy import create_engine, event
from sqlalchemy.engine import URL

credential = DefaultAzureCredential()

engine = create_engine(
    URL.create(
        "postgresql+psycopg",
        username=os.environ["PGUSER"],  # Your Microsoft Entra user or managed identity name
        host=os.environ["PGHOST"],
        port=int(os.environ.get("PGPORT", "5432")),
        database=os.environ.get("PGDATABASE", "postgres"),
    ),
    connect_args={"sslmode": "require", "connect_timeout": 15},
    pool_pre_ping=True,
    hide_parameters=True,
)

@event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
    cparams["password"] = credential.get_token(
        "https://ossrdbms-aad.database.windows.net/.default"
    ).token

Create the extensions

Create the pg_diskann extension in your database. The CASCADE clause also creates the vector extension it depends on:

from sqlalchemy import text

with engine.begin() as conn:
    conn.execute(text("CREATE EXTENSION IF NOT EXISTS pg_diskann CASCADE"))

Define a model with a DiskANN index

Use the Vector type from pgvector.sqlalchemy for the embedding column. Declare the DiskANN index in __table_args__ with these SQLAlchemy PostgreSQL options:

SQLAlchemy option Generated SQL Purpose
postgresql_using="diskann" USING diskann Selects the DiskANN index access method.
postgresql_ops={"<column>": "<opclass>"} (<column> <opclass>) Selects the distance metric. The operator class must match the operator your queries use.
postgresql_with={...} WITH (...) Sets index build parameters.
from sqlalchemy import Index, Integer, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from pgvector.sqlalchemy import Vector

class Base(DeclarativeBase):
    pass

class Document(Base):
    __tablename__ = "documents"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    content: Mapped[str] = mapped_column(Text)
    embedding = mapped_column(Vector(1536), nullable=True)

    __table_args__ = (
        Index(
            "documents_embedding_diskann_idx",
            "embedding",
            postgresql_using="diskann",
            postgresql_ops={"embedding": "vector_cosine_ops"},
        ),
    )

Base.metadata.create_all(engine)

The create_all method creates the table and runs the following statement:

CREATE INDEX documents_embedding_diskann_idx ON documents USING diskann (embedding vector_cosine_ops);

Tip

For large data loads, insert the rows first and create the index afterward. Building the index once over existing data is faster than maintaining it during a bulk insert. For more ways to reduce build time, see Speed up index build.

Tune index build parameters

Pass DiskANN build parameters with postgresql_with. For tables with more than about one million rows, enable spherical quantization to reduce index size and improve performance:

sq_index = Index(
    "documents_embedding_diskann_sq_idx",
    Document.embedding,
    postgresql_using="diskann",
    postgresql_ops={"embedding": "vector_cosine_ops"},
    postgresql_with={
        "max_neighbors": 48,
        "l_value_ib": 100,
        "spherical_quantized": "true",
    },
)
sq_index.create(engine, checkfirst=True)

Binding the index to Document.embedding associates it with the table; create() then emits the DDL. For a new model, you can instead put this index in __table_args__ before calling create_all(). Choose either this tuned cosine index or the earlier cosine index in your application, rather than maintaining both unnecessarily.

This definition generates:

CREATE INDEX documents_embedding_diskann_sq_idx ON documents USING diskann (embedding vector_cosine_ops)
WITH (max_neighbors = 48, l_value_ib = 100, spherical_quantized = true);

For recommended values by dataset size, see Recommended configuration of parameters.

Use half-precision vectors

To halve the storage that embeddings use, store them as halfvec with the HALFVEC type and a halfvec_* operator class:

Important

A pgvector operator class isn't necessarily available for the DiskANN access method on every extension build. Check the server catalog before creating a halfvec DiskANN index. The following example still creates the halfvec column when the index isn't supported, but searches then use an exact scan, not DiskANN.

from pgvector.sqlalchemy import HALFVEC

with engine.connect() as conn:
    halfvec_diskann_supported = conn.scalar(text("""
        SELECT EXISTS (
            SELECT 1
            FROM pg_opclass AS opc
            JOIN pg_am AS am ON am.oid = opc.opcmethod
            JOIN pg_type AS typ ON typ.oid = opc.opcintype
            WHERE am.amname = 'diskann'
              AND opc.opcname = 'halfvec_cosine_ops'
              AND typ.typname = 'halfvec'
              AND pg_opclass_is_visible(opc.oid)
        )
    """))

class HalfDocument(Base):
    __tablename__ = "half_documents"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    content: Mapped[str] = mapped_column(Text)
    embedding = mapped_column(HALFVEC(1536))

    __table_args__ = (
        Index(
            "half_documents_embedding_diskann_idx",
            "embedding",
            postgresql_using="diskann",
            postgresql_ops={"embedding": "halfvec_cosine_ops"},
        ),
    ) if halfvec_diskann_supported else ()

HalfDocument.__table__.create(engine, checkfirst=True)
if not halfvec_diskann_supported:
    print("Halfvec storage is available, but this server doesn't support halfvec_cosine_ops with DiskANN.")

Insert data

Assign a NumPy array or a Python list of floats to the vector column:

import numpy as np
from sqlalchemy.orm import Session

with Session(engine) as session:
    session.add_all([
        Document(content="The cat sat on the mat", embedding=np.random.rand(1536)),
        Document(content="Dogs love to play fetch", embedding=np.random.rand(1536)),
    ])
    session.commit()

In a real application, generate the embeddings with an embedding model. You can also generate them in the database. For more information, see Generate embeddings and rerank results in the database.

Run similarity search queries

pgvector-python adds distance methods to vector columns. Order by a distance method and add a LIMIT to run a nearest-neighbor search:

from sqlalchemy import select

query_embedding = np.random.rand(1536)  # Replace with the embedding of your query text

with Session(engine) as session:
    stmt = (
        select(Document)
        .order_by(Document.embedding.cosine_distance(query_embedding))
        .limit(5)
    )
    for doc in session.scalars(stmt):
        print(doc.id, doc.content)

To return the distance with each row, select the distance expression with a label:

distance = Document.embedding.cosine_distance(query_embedding).label("distance")
stmt = select(Document.content, distance).order_by(distance).limit(5)

The distance method must match the index operator class. Otherwise, PostgreSQL can't use the index for the query.

SQLAlchemy method SQL operator vector operator class halfvec operator class
l2_distance() <-> vector_l2_ops halfvec_l2_ops
cosine_distance() <=> vector_cosine_ops halfvec_cosine_ops
max_inner_product() <#> vector_ip_ops halfvec_ip_ops

The inner-product operator returns the negative inner product, so ascending order returns the largest dot products first. The halfvec operator classes in this table describe the metric mapping. Check that each class exists for your chosen access method.

Generate embeddings and rerank results in the database

By using the azure_ai extension, you can call AI functions from SQLAlchemy expressions through func. Text goes to the model from the database, so your application doesn't need a separate embedding client.

For all five AI functions, see Using AI Functions with SQLAlchemy.

The examples use the default-embedding and default-reranker model aliases that AI Model Management provisions. If you registered your own models, replace the aliases with your model aliases. The default-embedding model returns 1,536 dimensions, which matches the Vector(1536) column in this article.

Check the registry before running model calls:

with engine.connect() as conn:
    for row in conn.execute(text(
        "SELECT alias, model_name, status "
        "FROM model_registry.model_list_all() ORDER BY alias"
    )):
        print(row.alias, row.model_name, row.status)

Default aliases must be registered; setting a model alias in Python doesn't register it. The model-setting fallback isn't available in every build.

ai.embed() returns a real[] array. Cast the result to Vector to compare it with the vector column. Define a helper that builds the expression:

from sqlalchemy import cast, func

def embed(text_expr):
    return cast(
        func.ai.embed(text_expr, "default-embedding"),
        Vector(1536),
    )

Generate embeddings for existing rows

Use a separate semantic table so model-generated embeddings don't mix with the random vectors used earlier. Reuse its mapping on notebook retries, and insert only missing fixtures:

from sqlalchemy.dialects.postgresql import insert

if "AiDocument" not in globals() or AiDocument.metadata is not Base.metadata:
    class AiDocument(Base):
        __tablename__ = "ai_documents"

        id: Mapped[int] = mapped_column(Integer, primary_key=True)
        content: Mapped[str] = mapped_column(Text)
        embedding = mapped_column(Vector(1536), nullable=True)

        __table_args__ = (
            Index(
                "ai_documents_embedding_diskann_idx",
                "embedding",
                postgresql_using="diskann",
                postgresql_ops={"embedding": "vector_cosine_ops"},
            ),
        )

AiDocument.__table__.create(engine, checkfirst=True)
fixtures = [
    {"id": 1, "content": "Wireless noise cancelling headphones with long battery life."},
    {"id": 2, "content": "Over-ear Bluetooth headphones for quiet travel."},
    {"id": 3, "content": "A stainless steel water bottle for hiking."},
    {"id": 4, "content": "A mechanical keyboard with backlit keys."},
]
with Session(engine) as session:
    session.execute(
        insert(AiDocument).values(fixtures)
        .on_conflict_do_nothing(index_elements=[AiDocument.id])
    )
    existing = session.execute(
        select(AiDocument.id, AiDocument.content).order_by(AiDocument.id)
    ).all()
    assert [(row.id, row.content) for row in existing] == [
        (row["id"], row["content"]) for row in fixtures
    ], "Existing fixtures differ; use a fresh test schema."
    session.commit()

Pass a column to the helper to backfill only NULL embeddings in a single UPDATE statement. Successful embeddings remain available if a later reranking call fails:

from sqlalchemy import update

with Session(engine) as session:
    session.execute(
        update(AiDocument)
        .where(AiDocument.embedding.is_(None))
        .values(embedding=embed(AiDocument.content))
    )
    session.commit()

Search with query text

Pass the query text to the helper instead of a precomputed vector. The database generates the query embedding, and the DiskANN index serves the ORDER BY:

query = "wireless noise cancelling headphones"

with Session(engine) as session:
    stmt = (
        select(AiDocument.id, AiDocument.content)
        .order_by(AiDocument.embedding.cosine_distance(embed(query)))
        .limit(10)
    )
    candidates = session.execute(stmt).all()

Rerank the results

ai.rank() is a table-valued function that returns id, rank, and score columns. Use table_valued() to select from it, and pass the candidate texts and IDs as arrays:

ranked = func.ai.rank(
    query,
    [c.content for c in candidates],
    [c.id for c in candidates],
    len(candidates),
    "default-reranker",
).table_valued("id", "rank", "score").render_derived()

with Session(engine) as session:
    for row in session.execute(
        select(ranked.c.id, ranked.c.rank, ranked.c.score).order_by(ranked.c.rank)
    ):
        print(row.id, row.rank, round(row.score, 3))

For more ways to combine vector search with reranking, see Hybrid search and Semantic ranking.

Using AI Functions with SQLAlchemy

The azure_ai extension provides five direct-inference functions: ai.embed(), ai.generate(), ai.extract(), ai.is_true(), and ai.rank(). They run model inference immediately, unlike the ai.*_step() builders used to describe deferred pipeline work. For signatures and upgrade guidance, see AI functions.

Use azure_ai 2.5.6 or later for this API guidance. Older builds can contain both inference and pipeline overloads. Don't migrate an older azure_ai.* call by changing only its schema: argument names and order can differ.

The examples use PostgreSQL named arguments through SQLAlchemy's text() and bound parameters. This makes overload selection explicit and keeps user input out of the SQL string. SQLAlchemy's func is useful for positional expressions, as shown earlier, but Python keyword arguments to func don't become PostgreSQL named arguments.

Configure the model aliases

AI Model Management provisions the default aliases used here. If you use BYOM, supply your registered aliases instead. Model calls can incur inference charges, and the input text is sent to the configured model endpoint.

from sqlalchemy import bindparam, text
from sqlalchemy.dialects.postgresql import ARRAY, JSONB

EMBEDDING_MODEL = "default-embedding"
CHAT_MODEL = "default-chat"
RERANKER_MODEL = "default-reranker"

Create an embedding

The scalar overload of ai.embed() returns a PostgreSQL real[], not a pgvector value. Request the same dimensions for stored embeddings and queries, and cast the result when using vector operators:

embedding_stmt = text("""
    SELECT ai.embed(
        text => :input_text,
        model => :model,
        dimensions => :dimensions
    )
""")
with engine.connect() as conn:
    embedding = conn.scalar(embedding_stmt, {
        "input_text": "Wireless noise cancelling headphones",
        "model": EMBEDDING_MODEL,
        "dimensions": 1536,
    })
    print(len(embedding))

For batch embedding, bind the input as text[]. This overload returns one row per input text:

batch_embedding_stmt = text("""
    SELECT embedding
    FROM ai.embed(
        text => :texts,
        model => :model,
        dimensions => :dimensions
    )
""").bindparams(bindparam("texts", type_=ARRAY(Text)))

with engine.connect() as conn:
    embeddings = conn.scalars(batch_embedding_stmt, {
        "texts": [
            "Wireless noise cancelling headphones",
            "A stainless steel water bottle",
        ],
        "model": EMBEDDING_MODEL,
        "dimensions": 1536,
    }).all()
    print([len(value) for value in embeddings])

You can also use named arguments in a table update or vector query:

with engine.begin() as conn:
    conn.execute(text("""
        UPDATE ai_documents
        SET embedding = CAST(ai.embed(
            text => content, model => :model, dimensions => 1536
        ) AS vector(1536))
        WHERE embedding IS NULL
    """), {"model": EMBEDDING_MODEL})

with engine.connect() as conn:
    rows = conn.execute(text("""
        SELECT id, content
        FROM ai_documents
        ORDER BY embedding <=> CAST(ai.embed(
            text => :query, model => :model, dimensions => 1536
        ) AS vector(1536))
        LIMIT 10
    """), {
        "query": "wireless noise cancelling headphones",
        "model": EMBEDDING_MODEL,
    }).all()

Generate text

ai.generate() returns text when you omit json_schema. When supplying max_tokens, also supply model and system_prompt:

generate_stmt = text("""
    SELECT ai.generate(
        prompt => :prompt,
        model => :model,
        system_prompt => :system_prompt,
        max_tokens => :max_tokens
    )
""")
with engine.connect() as conn:
    response = conn.scalar(generate_stmt, {
        "prompt": "Describe vector search in one sentence.",
        "model": CHAT_MODEL,
        "system_prompt": "Give a concise, factual answer.",
        "max_tokens": 500,
    })
    print(response)

Generate structured JSON

Bind a JSON schema as JSONB to select the structured-output overload. Psycopg returns the resulting jsonb as a Python object:

product_schema = {
    "name": "product_summary",
    "strict": True,
    "schema": {
        "type": "object",
        "properties": {
            "product": {"type": "string"},
            "wireless": {"type": "boolean"},
        },
        "required": ["product", "wireless"],
        "additionalProperties": False,
    },
}
structured_stmt = text("""
    SELECT ai.generate(
        prompt => :prompt,
        json_schema => :json_schema,
        model => :model
    )
""").bindparams(bindparam("json_schema", type_=JSONB))

with engine.connect() as conn:
    product = conn.scalar(structured_stmt, {
        "prompt": "Summarize this product: wireless noise cancelling headphones.",
        "json_schema": product_schema,
        "model": CHAT_MODEL,
    })
    print(product["product"], product["wireless"])

Extract entities

ai.extract() returns a JSONB object. Supply a non-NULL array of labels through the data argument:

extract_stmt = text("""
    SELECT ai.extract(
        text => :input_text,
        model => :model,
        data => :labels
    )
""").bindparams(bindparam("labels", type_=ARRAY(Text)))

with engine.connect() as conn:
    entities = conn.scalar(extract_stmt, {
        "input_text": "The headphones have excellent sound but short battery life.",
        "model": CHAT_MODEL,
        "labels": ["product", "sentiment"],
    })
    print(entities)

The labels describe what to extract; they aren't a strict output schema. Use the structured ai.generate() overload when you need schema-constrained JSON.

Evaluate a statement

ai.is_true() returns True, False, or None when the result is inconclusive. Treat it as model output, not a deterministic database constraint:

truth_stmt = text("""
    SELECT ai.is_true(statement => :statement, model => :model)
""")
with engine.connect() as conn:
    verdict = conn.scalar(truth_stmt, {
        "statement": "The sentence 'These headphones connect using Bluetooth' describes a wireless product.",
        "model": CHAT_MODEL,
    })
    print("Inconclusive" if verdict is None else verdict)

Rerank documents

ai.rank() returns id, rank, and score. Bind document texts and IDs as typed arrays, keeping them in the same order. Unlike the older azure_ai.rank() call, ai.rank() has a top_n argument before model, so use named arguments:

rank_stmt = text("""
    SELECT id, rank, score
    FROM ai.rank(
        query => :query,
        data => :documents,
        ids => :ids,
        top_n => :top_n,
        model => :model
    )
    ORDER BY rank
""").bindparams(
    bindparam("documents", type_=ARRAY(Text)),
    bindparam("ids", type_=ARRAY(Integer)),
)
with engine.connect() as conn:
    ranked_rows = conn.execute(rank_stmt, {
        "query": "wireless noise cancelling headphones",
        "documents": [
            "Wireless headphones for quiet travel",
            "A stainless steel water bottle",
        ],
        "ids": [101, 102],
        "top_n": 2,
        "model": RERANKER_MODEL,
    }).all()
    for row in ranked_rows:
        print(row.id, row.rank, row.score)

To omit custom IDs, remove ids => :ids and the ids bind parameter. The returned id is then the zero-based input-document position. Always provide top_n > 0 when setting a limit.

Retry notebook examples safely

When adapting these examples into a notebook, rerunning create_all() doesn't empty an existing table. Re-inserting fixed primary keys can raise UniqueViolation. Reuse the ORM model mapping and insert only missing test fixtures:

from sqlalchemy.dialects.postgresql import insert

fixtures = [
    {"id": 101, "content": "Wireless noise cancelling headphones"},
    {"id": 102, "content": "A stainless steel water bottle"},
]
with Session(engine) as session:
    session.execute(
        insert(Document).values(fixtures)
        .on_conflict_do_nothing(index_elements=[Document.id])
    )
    session.commit()

Use an isolated test schema and verify that existing fixture content matches your intended inputs. Backfill only NULL embeddings to avoid recomputing successful model calls after a later reranking failure. Don't use extend_existing=True to repeatedly redefine the same indexed ORM class; it can accumulate duplicate index definitions in metadata.

Confirm that queries use the DiskANN index

Compile the statement and run EXPLAIN to check that the plan contains an Index Scan on your DiskANN index:

from sqlalchemy import text

with engine.connect() as conn:
    vec = "[" + ",".join(map(str, query_embedding)) + "]"
    plan = conn.execute(
        text(
            "EXPLAIN SELECT id FROM documents "
            "ORDER BY embedding <=> CAST(:q AS vector) LIMIT 5"
        ),
        {"q": vec},
    )
    for row in plan:
        print(row[0])
Limit  (cost=33.20..33.33 rows=5 width=12)
  ->  Index Scan using documents_embedding_diskann_idx on documents  (cost=33.20..1148.20 rows=25200 width=12)
        Order By: (embedding <=> '[...]'::vector)

If the plan shows a sequential scan instead, check these causes:

  • The distance method doesn't match the index operator class. For example, the query uses l2_distance() but the index uses vector_cosine_ops.

  • The query has no LIMIT, or orders by a computed expression instead of the distance method.

  • The table is small enough that the planner estimates a sequential scan is cheaper. To check whether the index can serve the query, disable sequential scans for the current transaction only:

    with engine.begin() as conn:
        conn.execute(text("SET LOCAL enable_seqscan TO off"))
        plan_lines = conn.execute(
            text(
                "EXPLAIN SELECT id FROM documents "
                "ORDER BY embedding <=> CAST(:q AS vector) LIMIT 5"
            ),
            {"q": vec},
        ).scalars().all()
        print("\n".join(plan_lines))
        assert any(
            "Index Scan using documents_embedding_diskann_idx" in line
            for line in plan_lines
        ), "The expected DiskANN index isn't eligible for this query."
    

    Disabling sequential scans doesn't guarantee an index scan. Inspect the plan, and distinguish this forced eligibility check from the optimizer's natural plan.

Use an HNSW index instead

The same pattern works for the pgvector HNSW index. This separate model creates an HNSW-only table, so its cosine queries don't compete with the earlier DiskANN indexes:

class HnswDocument(Base):
    __tablename__ = "hnsw_documents"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    content: Mapped[str] = mapped_column(Text)
    embedding = mapped_column(Vector(1536))

    __table_args__ = (
        Index(
            "documents_embedding_hnsw_idx",
            "embedding",
            postgresql_using="hnsw",
            postgresql_ops={"embedding": "vector_cosine_ops"},
            postgresql_with={"m": 16, "ef_construction": 64},
        ),
    )

HnswDocument.__table__.create(engine, checkfirst=True)

To decide between DiskANN and HNSW, see Choose the right vector index for your workload.