API Reference

Dreambase exposes two interfaces: the PostgreSQL wire protocol on port 5432 (psycopg2, asyncpg, SQLAlchemy, Prisma, node-postgres, and any other pg-compatible client connect without a new driver), and a REST API for serverless environments or edge functions where a persistent TCP connection is not feasible. Both interfaces support hybrid queries with the NEAR operator and DDL with the VECTOR(dims) type.

Connection

Tables (DDL)

Dreambase extends standard PostgreSQL DDL with the VECTOR(dims) column type.

-- Create hybrid table
CREATE TABLE <name> (
    <column> VECTOR(<dims>),   -- dims: 1 to 4096
    ...
);

-- Alter: add vector column
ALTER TABLE documents ADD COLUMN embedding VECTOR(1536);

-- Drop table (removes vector index automatically)
DROP TABLE documents;

INSERT / UPSERT

-- Standard INSERT with vector column
INSERT INTO documents (user_id, content, embedding)
VALUES ($1, $2, $3);   -- $3 is float32[] / VECTOR literal

-- UPSERT
INSERT INTO documents (id, user_id, content, embedding)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE
  SET content = EXCLUDED.content,
      embedding = EXCLUDED.embedding;

QUERY (hybrid)

The NEAR operator is the primary hybrid query primitive. It returns rows ordered by ANN distance from a query vector, optionally combined with SQL predicates.

-- Basic NEAR query
SELECT id, content
FROM documents
ORDER BY embedding NEAR $1
LIMIT 10;

-- Hybrid: SQL predicates + NEAR
SELECT id, content, created_at
FROM documents
WHERE user_id = $1
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding NEAR $2
LIMIT 5;

-- With similarity score in output
SELECT id, content,
       embedding NEAR $1 AS score
FROM documents
WHERE category = $2
ORDER BY score
LIMIT 10;

Vector index management

The HNSW index is maintained automatically on every INSERT, UPDATE, and DELETE. Manual rebuild is rarely needed — the main case is after a large bulk load via COPY or executemany, when the incremental index may benefit from a full rebuild for optimal recall.

-- Check index status
SELECT table_name, index_type, index_size,
       row_count, last_rebuilt
FROM dreambase_vector_indexes
WHERE table_name = 'documents';

-- Rebuild index (rarely needed; auto-maintained)
CALL dreambase_rebuild_index('documents', 'embedding');

-- Index parameters (set at table creation or ALTER)
ALTER TABLE documents
  SET (vector_index_m = 16,          -- HNSW M parameter (default: 16)
       vector_index_ef_construction = 200);  -- build quality (default: 200)

Schema introspection

-- List vector columns in schema
SELECT table_name, column_name, vector_dims
FROM information_schema.columns
JOIN dreambase_vector_columns USING (table_name, column_name)
WHERE table_schema = 'public';

-- Row and index stats
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'documents';

REST API

The REST API mirrors the SQL interface for environments that cannot maintain persistent connections.

POST https://<cluster>.dreambase.io/v1/query
Authorization: Bearer <token>
Content-Type: application/json

{
  "sql": "SELECT id, content FROM docs ORDER BY embedding NEAR $1 LIMIT 5",
  "params": [
    { "type": "vector", "value": [0.12, 0.45, ...] }
  ]
}