skilld
All skills
secondsky avatar

/sap-cap-capire

@620a19a
by Eddiesecondsky/sap-skills456 stars
120

SAP Cloud Application Programming Model (CAP) development skill using Capire documentation. Use when: building CAP applications, defining CDS models, implementing services, working with SAP HANA/SQLite/PostgreSQL databases, deploying to SAP BTP Cloud Foundry or Kyma, implementing Fiori UIs, handling authorization, multitenancy, or messaging. Covers CDL/CQL/CSN syntax, Node.js and Java runtimes, event handlers, OData services, and CAP plugins.

Ask your Agent

Use this Skill: https://skilld.dev/gh/secondsky/sap-skills/sap-cap-capire

This session only. Nothing lands on disk.

referencescql-queries.md

≈2.3k tokens on demand. Your agent reads this file only when SKILL.md points to it.

CDS Query Language (CQL) Complete Reference

Source: https://cap.cloud.sap/docs/cds/cql

SELECT Queries

Basic SELECT

const { Books, Authors } = cds.entities;

// All columns
const books = await SELECT.from(Books);

// Specific columns
const titles = await SELECT.from(Books).columns('ID', 'title', 'price');

// With aliases
const data = await SELECT.from(Books).columns('ID', 'title as bookTitle');

Single Record

// By key - shortcut form (returns single entity or undefined)
const book = await SELECT.from(Books, bookId);

// Explicit form with WHERE clause
const book = await SELECT.one.from(Books).where({ ID: bookId });

// With non-key condition
const book = await SELECT.one.from(Books).where({ isbn: '978-123' });

// IMPORTANT: Always guard the result - returns undefined when no record found
const book = await SELECT.one.from(Books).where({ ID: bookId });
if (!book) {
  return req.reject(404, 'Book not found');
}
// Now safe to access book.title, book.price, etc.

WHERE Clauses

// Simple equality
await SELECT.from(Books).where({ stock: 10 });

// Comparison operators
await SELECT.from(Books).where({ stock: { '>': 0 } });
await SELECT.from(Books).where({ stock: { '>=': 10 } });
await SELECT.from(Books).where({ stock: { '<': 100 } });
await SELECT.from(Books).where({ price: { '<=': 50 } });
await SELECT.from(Books).where({ status: { '!=': 'deleted' } });

// IN operator
await SELECT.from(Books).where({ genre: { in: ['fiction', 'drama'] } });

// LIKE operator
await SELECT.from(Books).where({ title: { like: '%CAP%' } });

// BETWEEN
await SELECT.from(Books).where({ price: { between: [10, 50] } });

// IS NULL / IS NOT NULL
await SELECT.from(Books).where({ author_ID: { '=': null } });
await SELECT.from(Books).where({ author_ID: { '!=': null } });

// Multiple conditions (AND)
await SELECT.from(Books).where({
  stock: { '>': 0 },
  price: { '<': 100 }
});

// OR conditions
await SELECT.from(Books).where({
  or: [
    { stock: { '>': 100 } },
    { price: { '<': 10 } }
  ]
});

// Complex nested conditions
await SELECT.from(Books).where({
  and: [
    { genre: 'fiction' },
    { or: [
      { stock: { '>': 50 } },
      { featured: true }
    ]}
  ]
});

ORDER BY

// Single column
await SELECT.from(Books).orderBy('title');
await SELECT.from(Books).orderBy('title asc');
await SELECT.from(Books).orderBy('price desc');

// Multiple columns
await SELECT.from(Books).orderBy('genre', 'title');
await SELECT.from(Books).orderBy({ genre: 'asc', price: 'desc' });

LIMIT & OFFSET

// Limit results
await SELECT.from(Books).limit(10);

// With offset (pagination)
await SELECT.from(Books).limit(10, 20);  // 10 rows, skip 20
await SELECT.from(Books).limit({ rows: 10, offset: 20 });

GROUP BY & HAVING

await SELECT.from(Books)
  .columns('genre', 'count(*) as count', 'avg(price) as avgPrice')
  .groupBy('genre')
  .having({ 'count(*)': { '>': 5 } });

DISTINCT

await SELECT.distinct.from(Books).columns('genre');

Associations & Expands

Path Expressions

// Navigate associations in columns
await SELECT.from(Books).columns('title', 'author.name');

// Navigate in where
await SELECT.from(Books).where({ 'author.name': 'John Doe' });

Expand (Deep Read)

Using columns with expand:

// Expand syntax with columns array
await SELECT.from(Books)
  .columns('ID', 'title', { ref: ['author'], expand: ['ID', 'name'] });

// Using '*' with expand
await SELECT.from(Books)
  .columns('*', { ref: ['author'], expand: ['*'] });

Fluent lambda syntax:

// Fluent syntax
await SELECT.from(Books, b => {
  b.ID,
  b.title,
  b.author(a => {
    a.ID,
    a.name
  })
});

// Nested expand
await SELECT.from(Orders, o => {
  o.ID,
  o.customer(c => { c.name }),
  o.items(i => {
    i.quantity,
    i.product(p => { p.name, p.price })
  })
});

Infix Filters

// Filter associated entities
await SELECT.from(Authors, a => {
  a.name,
  a.books(b => {
    b.title
  }).where({ stock: { '>': 0 } })
});

INSERT

Single Entry

await INSERT.into(Books).entries({
  ID: cds.utils.uuid(),
  title: 'New Book',
  stock: 100,
  price: 29.99
});

Multiple Entries

await INSERT.into(Books).entries([
  { title: 'Book 1', stock: 10 },
  { title: 'Book 2', stock: 20 },
  { title: 'Book 3', stock: 30 }
]);

Insert from SELECT

await INSERT.into(ArchivedBooks).as(
  SELECT.from(Books).where({ stock: 0 })
);

Deep Insert

await INSERT.into(Orders).entries({
  ID: orderId,
  customer_ID: customerId,
  items: [
    { product_ID: product1Id, quantity: 2 },
    { product_ID: product2Id, quantity: 1 }
  ]
});

UPDATE

By Key

await UPDATE(Books, bookId).set({ stock: 50 });
await UPDATE(Books, bookId).with({ stock: 50 });

With WHERE

await UPDATE(Books)
  .set({ featured: true })
  .where({ stock: { '>': 100 } });

Increment/Decrement

// Increment
await UPDATE(Books, bookId).set({ stock: { '+=': 10 } });

// Decrement
await UPDATE(Books, bookId).set({ stock: { '-=': 5 } });

// Multiply
await UPDATE(Books, bookId).set({ price: { '*=': 1.1 } });

Conditional Update

// Update only if condition met
const result = await UPDATE(Books, bookId)
  .set({ stock: { '-=': quantity } })
  .where({ stock: { '>=': quantity } });

if (result === 0) {
  throw new Error('Insufficient stock');
}

Deep Update

await UPDATE(Orders, orderId).with({
  status: 'confirmed',
  items: [
    { ID: item1Id, quantity: 5 },
    { ID: item2Id, quantity: 3 }
  ]
});

DELETE

By Key

await DELETE.from(Books, bookId);

With WHERE

await DELETE.from(Books).where({ stock: 0 });

Delete All

await DELETE.from(Books);  // Caution: deletes all!

Cascading Delete

Compositions automatically cascade deletes to child entities.

UPSERT

Behavior: UPSERT performs INSERT if record with key doesn't exist, otherwise UPDATE. Key fields are required to determine existence.

// Insert if not exists, update if exists
// IMPORTANT: Key field (ID) MUST be provided
await UPSERT.into(Books).entries({
  ID: bookId,  // Key field required
  title: 'Updated or New Title',
  stock: 50
});

// Multiple entries
await UPSERT.into(Books).entries([
  { ID: id1, title: 'Book 1' },
  { ID: id2, title: 'Book 2' }
]);

// UPSERT does NOT support .where() - use UPDATE for conditional updates
// UPSERT does NOT merge with existing data - all provided fields are set

Raw SQL

Native Queries

// Direct SQL execution
const results = await cds.db.run(
  `SELECT * FROM my_bookshop_Books WHERE stock > ?`,
  [10]
);

Prepared Statements

const query = `SELECT * FROM Books WHERE genre = ? AND stock > ?`;
const results = await cds.db.run(query, ['fiction', 0]);

Query Building

CQN Objects

// Build query as object
const query = {
  SELECT: {
    from: { ref: ['Books'] },
    columns: [
      { ref: ['ID'] },
      { ref: ['title'] }
    ],
    where: [
      { ref: ['stock'] }, '>', { val: 0 }
    ]
  }
};

const results = await cds.db.run(query);

Query Modification

// Clone and modify
let query = SELECT.from(Books);
query = query.where({ stock: { '>': 0 } });
query = query.orderBy('title');
query = query.limit(10);

const results = await cds.db.run(query);

Aggregations

Count

const count = await SELECT.one.from(Books)
  .columns('count(*) as total');

Sum, Avg, Min, Max

const stats = await SELECT.one.from(Books).columns(
  'sum(stock) as totalStock',
  'avg(price) as avgPrice',
  'min(price) as minPrice',
  'max(price) as maxPrice'
);

Group By with Aggregates

const byGenre = await SELECT.from(Books)
  .columns('genre', 'count(*) as count', 'avg(price) as avgPrice')
  .groupBy('genre')
  .orderBy('count desc');

Subqueries

In WHERE

// Books by authors with more than 5 books
await SELECT.from(Books).where({
  author_ID: {
    in: SELECT.from(Authors)
      .columns('ID')
      .where({ bookCount: { '>': 5 } })
  }
});

EXISTS

// Authors with at least one bestseller
await SELECT.from(Authors).where({
  exists: SELECT.from(Books)
    .where({ 'author_ID': { ref: ['Authors', 'ID'] }, bestseller: true })
});

Locking

Exclusive Lock

// For update (exclusive)
const book = await SELECT.from(Books, bookId).forUpdate();

Shared Lock

// For share (shared)
const book = await SELECT.from(Books, bookId).forShareLock();

Parameterized Views

// View with parameters
const filtered = await SELECT.from('FilteredBooks', {
  minStock: 10,
  maxPrice: 50
});

Exists Predicate

// Check existence
const hasBooks = await SELECT.one.from(Authors)
  .where({
    ID: authorId,
    exists: SELECT.from(Books).where({ author_ID: authorId })
  });

Source: SKILL.md on GitHub

Third-party checks

1 alert16d5 checks · Risk SAFE
  • Gen Agent Trust Hub16d

    The analyzed skill is a reference and template package providing guidance for SAP Cloud Application Programming Model (CAP) development, configuration, data privacy, and testing. It contains no executable scripts, hardcoded credentials, or malicious behavior.

  • Socket16d

    No alerts

  • Snyk16d

    Risk: LOW · No issues

  • Runlayer6mo

    12/34 files flagged

  • ZeroLeaks5mo

    Score: 93/100 · 2 sections analyzed

Provenance

Signed by skilld at 620a19a. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 2 weeks ago.

Activeupdated 2 months ago

Capability

Other metadata
metadata
{
  "maintainer": "Eduard Jiglau",
  "maintainer_email": "hello@sap-ai-skills.com",
  "website": "https://sap-ai-skills.com",
  "version": "2.4.1",
  "last_verified": "2026-02-22",
  "cap_version": "@sap/cds 9.7.x",
  "mcp_version": "@cap-js/mcp-server 0.0.5",
  "lsp_version": "@sap/cds-lsp 9.7.x"
}

README badge

README badge for secondsky/sap-skills/sap-cap-capire