Vector Databases and the API Layer

What Vector Databases Do (and Don't Do)

A vector database indexes collections of embeddings and supports nearest-neighbor search over them. You store a vector (an array of 768, 1024, or 1536 floats, depending on the embedding model), attach metadata, and later query by providing another vector. The database returns the k most similar vectors by cosine similarity, dot product, or Euclidean distance. That is the core operation.

The major purpose-built vector databases are Pinecone (managed SaaS), Weaviate (open source, self-hosted or cloud), Qdrant (open source, Rust-based), and Milvus (open source, designed for billion-scale collections). There is also pgvector, a PostgreSQL extension that adds vector similarity search to an existing relational database. Each makes different trade-offs in scalability, filtering capabilities, and operational complexity.

What vector databases do well is semantic retrieval. Given a user question embedded as a vector, they find the document chunks, product descriptions, or support tickets whose embeddings are closest in meaning. This is the retrieval step in retrieval-augmented generation. It lets an LLM ground its response in relevant source material rather than relying solely on its training data.

What vector databases do not do is serve structured data. They do not store customer records with relational integrity. They do not enforce foreign key constraints. They do not support SQL joins, aggregations, or transactions. If you need to answer "what are the last five orders for customer 4821, sorted by date, with the total revenue," a vector database is the wrong tool. That query belongs in PostgreSQL, MySQL, SQL Server, or Oracle.

This distinction matters because most enterprise AI applications need both kinds of data. A RAG-powered customer support agent needs semantic search over knowledge base articles (vector) and structured lookup of the customer's account details, order history, and subscription status (SQL). Treating these as separate, unrelated problems leads to fragmented architectures. The data access layer for RAG pipelines must handle both.

Structured + Unstructured: The Hybrid RAG Pattern

The simplest RAG pipeline retrieves context from a single source: query a vector database, stuff the results into the prompt, generate a response. This works for narrow use cases like document Q&A over a static corpus. It breaks down the moment the AI application needs factual, real-time data from a relational database.

Hybrid RAG addresses this by combining vector retrieval with structured data queries in the same pipeline. The orchestration layer, typically LangChain, LlamaIndex, or a custom agent framework, determines which data sources a given query requires. A question like "summarize the key themes in our Q3 earnings call" routes to vector search over embedded transcript chunks. A question like "what was our Q3 revenue by product line" routes to a SQL query against the finance database. A question like "compare what the CEO said about growth with our actual growth numbers" requires both.

The orchestration logic can be rule-based or model-driven. Rule-based routing uses keyword patterns or intent classifiers to decide the data source. Model-driven routing lets the LLM itself decide which tools to call via function calling. In either case, the AI application needs API access to both the vector database and the relational database, and those APIs need consistent authentication and authorization policies.

Without a unified API strategy, hybrid RAG degrades quickly. Teams end up with one set of credentials and access policies for the vector database API, a different set for the SQL database API, and no centralized audit trail. When something goes wrong, such as an agent returning data a user should not see, diagnosing the issue requires correlating logs across multiple systems with different formats and retention policies. Enterprise data integration for AI demands more discipline than this.

API Architecture for Hybrid Data Access

The architecture that handles both vector and SQL data access cleanly puts an API gateway in front of both data stores. The AI application makes HTTP requests to the gateway. The gateway authenticates the request, checks authorization, and routes the query to the appropriate backend based on the endpoint or query type. Responses pass back through the gateway, which applies field-level filtering, logs the transaction, and returns the result.

The request flow looks like this. An AI agent receives a user query. The agent (or its orchestration framework) determines that it needs product documentation context from the vector database and the customer's current subscription tier from the SQL database. It makes two API calls through the gateway. The first call hits a vector search endpoint, passing the embedded query and a top-k parameter. The gateway forwards this to Pinecone, Weaviate, or whichever vector store is in use. The second call hits a REST endpoint for the customer table, passing the customer ID. The gateway forwards this to the relational database. Both responses return through the same authentication and logging pipeline.

This architecture provides three concrete benefits. First, a single authentication mechanism covers both data stores. The AI application presents one API key or bearer token, and the gateway validates it once. The vector database and SQL database do not need to implement their own authentication for AI consumers. Second, rate limiting is enforced at the gateway level across all backend calls, preventing a runaway agent from overwhelming either data store. Third, the audit log captures every request to every backend in a single stream, making compliance reporting and incident investigation straightforward.

The alternative, having the AI application call the vector database API directly with one set of credentials and the SQL API with another, works in prototypes. It does not survive a security review in an enterprise that handles PII, financial data, or health records. Every additional direct integration doubles the access control surface you need to manage.

Pairing DreamFactory with Vector Search

The SQL side of hybrid RAG is where most teams spend disproportionate engineering effort. Building authenticated, authorized REST APIs over relational databases means writing middleware for connection pooling, parameterized queries, role-based access control, field masking, and audit logging. This is well-understood infrastructure work, but it is not trivial, especially when supporting multiple database backends.

DreamFactory handles the SQL side by auto-generating REST APIs from existing database schemas. You connect it to PostgreSQL, MySQL, SQL Server, or Oracle, and it produces secured CRUD endpoints with role-based access control and API key management built in. For hybrid RAG, these generated endpoints serve as the structured data retrieval surface. The AI agent calls them the same way it would call any REST API.

DreamFactory does not manage vector databases. That is not its role. Pinecone, Weaviate, Qdrant, and Milvus each have their own APIs and SDKs for ingestion and search. The pairing works because the two layers handle different data types with different access patterns. DreamFactory governs structured data access. The vector database API handles embedding retrieval. The AI application's orchestration layer calls both through whatever gateway or routing mechanism the team has chosen.

The practical value of this split is that the SQL data access layer, which typically has the strictest compliance requirements because it contains PII, financial records, and operational data, gets enterprise-grade governance without custom code. The vector search layer, which usually contains embedded document chunks with lower sensitivity, uses the vector database's native API. Each layer is right-sized for its security requirements.

Choosing a Vector Database for Enterprise RAG

The choice of vector database depends on scale, operational preferences, and existing infrastructure. There is no universally correct answer, but there are clear decision criteria.

Pinecone is a fully managed service. You do not operate any infrastructure. Indexing and query scaling happen automatically. This makes it the fastest path to production for teams that want to avoid ops overhead. The trade-off is vendor lock-in and cost at high volume. Pinecone charges by index size and query throughput, and costs scale linearly. For large-scale enterprise deployments, the bill can become significant.

Weaviate is open source and can be self-hosted or run on Weaviate Cloud. It supports hybrid search, combining vector similarity with keyword (BM25) filtering in a single query. This is useful when semantic search alone is not precise enough and you need to constrain results by exact metadata matches. Weaviate also supports multi-tenancy natively, which matters in SaaS applications where each customer's data must be isolated.

Qdrant is written in Rust with a focus on performance and efficient filtering. It supports payload-based filtering during vector search, meaning you can restrict nearest-neighbor results to vectors matching specific metadata criteria without a separate filtering step. For use cases where every query needs both semantic similarity and metadata constraints, Qdrant's architecture is efficient.

Milvus is designed for billion-scale vector collections. It uses a distributed architecture with separate storage and compute layers, which allows it to handle datasets that exceed the capacity of a single node. If your embedding corpus is in the hundreds of millions or billions of vectors, Milvus is built for that scale. The operational complexity is higher than the other options.

pgvector deserves special mention. It adds vector similarity search to PostgreSQL via an extension. If your structured data already lives in PostgreSQL, pgvector lets you store embeddings in the same database. This eliminates the need for a separate vector database entirely at moderate scale. Queries can join vector similarity results with relational data in a single SQL statement. The limitation is performance: pgvector uses IVFFlat or HNSW indexes that work well up to tens of millions of vectors but do not match purpose-built vector databases at larger scale.

For teams evaluating vector databases alongside an API-mediated data access strategy, the key question is whether the vector search layer integrates cleanly into the same governance model as the structured data layer. Authentication, authorization, and audit logging must be consistent across both. The future of data infrastructure for AI points toward unified governance across all data stores, not separate policy regimes for each backend.