Skip to main content

Query Reference

Complete reference for the Warehouse query language syntax.

Query Structure​

interface IWarehouseQuery {
  operation: 'select' | 'insert' | 'update' | 'delete' | 'upsert';
  from: IDataSource;
  fields?: string[];
  join?: IJoinClause[];
  where?: IWhereClause;
  orderBy?: IOrderBy[];
  groupBy?: string[];
  having?: IWhereClause;
  limit?: number;
  offset?: number;
  data?: Record<string, any> | Record<string, any>[];
  returning?: boolean;
}

Data Source​

Specifies which database, graph, or vector store to query:

interface IDataSource {
  type: 'database' | 'graph' | 'vector';
  tag: string;
  entity: string;
  alias?: string;
  env?: string;
  product?: string;
}
FieldTypeRequiredDescription
typestringYesSource type: database, graph, or vector
tagstringYesYour data source tag
entitystringYesTable, node label, or collection name
aliasstringNoAlias for field references
envstringNoEnvironment (defaults to SDK context)
productstringNoProduct (defaults to SDK context)

Operations​

Select​

Read data from one or more sources:

const result = await ductape.warehouse.query({
  operation: 'select',
  from: { type: 'database', tag: 'users-db', entity: 'users', alias: 'u' },
  fields: ['u.id', 'u.name', 'u.email'],
  where: { 'u.status': { $eq: 'active' } },
  orderBy: [{ field: 'u.createdAt', order: 'DESC' }],
  limit: 100,
  offset: 0
});

Insert​

Add new records:

// Single record
await ductape.warehouse.query({
  operation: 'insert',
  from: { type: 'database', tag: 'users-db', entity: 'users' },
  data: { name: 'John', email: 'john@example.com' },
  returning: true
});

// Multiple records
await ductape.warehouse.query({
  operation: 'insert',
  from: { type: 'database', tag: 'users-db', entity: 'users' },
  data: [
    { name: 'John', email: 'john@example.com' },
    { name: 'Jane', email: 'jane@example.com' }
  ]
});

Update​

Modify existing records:

await ductape.warehouse.query({
  operation: 'update',
  from: { type: 'database', tag: 'users-db', entity: 'users' },
  data: { status: 'inactive', updatedAt: new Date() },
  where: { id: { $eq: 123 } },
  returning: true
});

Delete​

Remove records:

await ductape.warehouse.query({
  operation: 'delete',
  from: { type: 'database', tag: 'users-db', entity: 'users' },
  where: { status: { $eq: 'deleted' } },
  returning: true
});

Upsert​

Insert or update based on key:

await ductape.warehouse.query({
  operation: 'upsert',
  from: { type: 'database', tag: 'users-db', entity: 'users' },
  data: { id: 123, name: 'John', email: 'john@example.com' },
  returning: true
});

Field Selection​

Basic Fields​

fields: ['id', 'name', 'email']

With Alias Reference​

fields: ['u.id', 'u.name', 'u.email']

Field Aliasing​

fields: [
  'u.id',
  'u.name',
  'u.email as userEmail',
  'o.total as orderTotal'
]

Wildcard​

fields: ['*']  // All fields from primary source
fields: ['u.*', 'o.total']  // All from u, specific from o

Where Clauses​

Comparison Operators​

OperatorDescriptionExample
$eqEqual{ status: { $eq: 'active' } }
$neNot equal{ status: { $ne: 'deleted' } }
$gtGreater than{ age: { $gt: 18 } }
$gteGreater than or equal{ age: { $gte: 18 } }
$ltLess than{ age: { $lt: 65 } }
$lteLess than or equal{ age: { $lte: 65 } }
where: {
  'u.age': { $gte: 18, $lt: 65 },
  'u.status': { $eq: 'active' }
}

Logical Operators​

OperatorDescriptionExample
$andLogical AND{ $and: [cond1, cond2] }
$orLogical OR{ $or: [cond1, cond2] }
$notLogical NOT{ $not: condition }
where: {
  $and: [
    { 'u.status': { $eq: 'active' } },
    {
      $or: [
        { 'u.role': { $eq: 'admin' } },
        { 'u.role': { $eq: 'moderator' } }
      ]
    }
  ]
}

Array Operators​

OperatorDescriptionExample
$inIn array{ role: { $in: ['admin', 'mod'] } }
$ninNot in array{ role: { $nin: ['banned'] } }
$containsArray contains{ tags: { $contains: 'featured' } }
$containsAllContains all{ tags: { $containsAll: ['a', 'b'] } }
$containsAnyContains any{ tags: { $containsAny: ['a', 'b'] } }
where: {
  'u.roles': { $in: ['admin', 'moderator'] },
  'u.tags': { $containsAll: ['verified', 'premium'] }
}

String Operators​

OperatorDescriptionExample
$likePattern match{ email: { $like: '%@company.com' } }
$ilikeCase-insensitive like{ name: { $ilike: 'john%' } }
$regexRegular expression{ email: { $regex: '^[a-z]+@' } }
$startsWithStarts with{ name: { $startsWith: 'John' } }
$endsWithEnds with{ email: { $endsWith: '.com' } }
where: {
  'u.email': { $like: '%@company.com' },
  'u.name': { $ilike: 'john%' }
}

Null Operators​

OperatorDescriptionExample
$nullIs null{ deletedAt: { $null: true } }
$existsField exists{ metadata: { $exists: true } }
where: {
  'u.deletedAt': { $null: true },
  'u.profile': { $exists: true }
}

Vector Operators​

OperatorDescriptionExample
$similarVector similarity{ $similar: { vector: [...], threshold: 0.7 } }
$nearDistance-based{ $near: { vector: [...], maxDistance: 0.5 } }
where: {
  'v': {
    $similar: {
      vector: queryEmbedding,
      threshold: 0.7,
      topK: 10
    }
  }
}

Join Clauses​

Standard Join​

join: [{
  type: 'inner' | 'left' | 'right',
  source: IDataSource,
  on: { left: string, right: string },
  where?: IWhereClause
}]

Graph Join​

join: [{
  type: 'left',
  source: { type: 'graph', tag: 'social-neo4j', entity: 'Person', alias: 'f' },
  graph: {
    relationship: 'FRIENDS_WITH',
    direction: 'outgoing' | 'incoming' | 'both',
    minDepth?: number,
    maxDepth?: number
  },
  on: { left: 'u.id', right: 'f.userId' }
}]

Semantic Join​

join: [{
  type: 'semantic',
  source: { type: 'vector', tag: 'embeddings', entity: 'items', alias: 's' },
  semantic: {
    embedField?: string,
    vector?: number[],
    similarityThreshold?: number,
    topK?: number
  }
}]

Ordering​

orderBy: [
  { field: 'createdAt', order: 'DESC' },
  { field: 'name', order: 'ASC' }
]

Pagination​

{
  limit: 20,
  offset: 40  // Skip first 40 records
}

Aggregations​

Group By​

{
  operation: 'select',
  from: { type: 'database', tag: 'orders-db', entity: 'orders', alias: 'o' },
  fields: [
    'o.status',
    { $count: '*', as: 'count' },
    { $sum: 'o.total', as: 'totalAmount' },
    { $avg: 'o.total', as: 'avgAmount' }
  ],
  groupBy: ['o.status'],
  having: { 'count': { $gt: 10 } }
}

Aggregate Functions​

FunctionDescriptionExample
$countCount records{ $count: '*', as: 'total' }
$sumSum values{ $sum: 'amount', as: 'total' }
$avgAverage{ $avg: 'rating', as: 'avgRating' }
$minMinimum{ $min: 'price', as: 'minPrice' }
$maxMaximum{ $max: 'price', as: 'maxPrice' }

Update Operators​

Special operators for update operations:

OperatorDescriptionExample
$incrementAdd to value{ count: { $increment: 1 } }
$decrementSubtract from value{ stock: { $decrement: 1 } }
$multiplyMultiply value{ price: { $multiply: 1.1 } }
$pushAdd to array{ tags: { $push: 'new' } }
$pullRemove from array{ tags: { $pull: 'old' } }
await ductape.warehouse.query({
  operation: 'update',
  from: { type: 'database', tag: 'products-db', entity: 'products' },
  data: {
    viewCount: { $increment: 1 },
    stock: { $decrement: quantity },
    tags: { $push: 'bestseller' }
  },
  where: { id: { $eq: productId } }
});

Result Structure​

interface IWarehouseResult<T> {
  data: T[];
  count?: number;
  affectedRows?: number;
  metadata: {
    executionTime: number;
    sourcesQueried: number;
    sourceStats: ISourceStats[];
    cached: boolean;
  };
}

Examples​

Complex Query​

const result = await ductape.warehouse.query({
  operation: 'select',
  from: {
    type: 'database',
    tag: 'users-postgres',
    entity: 'users',
    alias: 'u'
  },
  fields: [
    'u.id',
    'u.name',
    'u.email',
    'o.orderId',
    'o.total',
    'p.name as productName',
    'r.rating'
  ],
  join: [
    {
      type: 'left',
      source: { type: 'database', tag: 'orders-mongo', entity: 'orders', alias: 'o' },
      on: { left: 'u.id', right: 'o.userId' },
      where: { 'o.status': { $eq: 'completed' } }
    },
    {
      type: 'inner',
      source: { type: 'database', tag: 'products-postgres', entity: 'products', alias: 'p' },
      on: { left: 'o.productId', right: 'p.id' }
    },
    {
      type: 'left',
      source: { type: 'database', tag: 'reviews-mongo', entity: 'reviews', alias: 'r' },
      on: { left: 'p.id', right: 'r.productId' }
    }
  ],
  where: {
    $and: [
      { 'u.status': { $eq: 'active' } },
      { 'u.createdAt': { $gte: new Date('2024-01-01') } },
      {
        $or: [
          { 'o.total': { $gte: 100 } },
          { 'r.rating': { $gte: 4 } }
        ]
      }
    ]
  },
  orderBy: [
    { field: 'o.total', order: 'DESC' },
    { field: 'u.name', order: 'ASC' }
  ],
  limit: 50,
  offset: 0
});

Aggregation Query​

const result = await ductape.warehouse.query({
  operation: 'select',
  from: {
    type: 'database',
    tag: 'orders-postgres',
    entity: 'orders',
    alias: 'o'
  },
  fields: [
    'o.userId',
    { $count: '*', as: 'orderCount' },
    { $sum: 'o.total', as: 'totalSpent' },
    { $avg: 'o.total', as: 'avgOrder' },
    { $max: 'o.total', as: 'largestOrder' }
  ],
  where: {
    'o.createdAt': { $gte: new Date('2024-01-01') },
    'o.status': { $eq: 'completed' }
  },
  groupBy: ['o.userId'],
  having: { 'orderCount': { $gte: 5 } },
  orderBy: [{ field: 'totalSpent', order: 'DESC' }],
  limit: 100
});