30 · CQL Query API

Use the CQL fluent API for SELECT, INSERT, UPDATE, DELETE. Understand transactions and the difference from raw SQL.

Episode 30 — CAP Query API (CQL & cds.ql)

What you'll learn

• CDS Query Language (CQL) — the SQL superset used by CAP
• Fluent API: SELECT, INSERT, UPDATE, DELETE objects
• How queries translate to SQL
• Chaining clauses for complex queries
• Running queries with db.run() vs cds.run()

Why this matters

CAP's query API is what keeps handlers database-agnostic. The same SELECT.from('Books').where(...) works on SQLite in dev and HANA in production. Understanding the API — including its limits — lets you write efficient, portable data access code.

Basic SELECT

// All books
const all = await SELECT.from('Books')

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

// With WHERE
const cheap = await SELECT.from('Books').where({ price: { '<=': 20 } })

// Shorthand where
const byId = await SELECT.from('Books', { ID: 'abc-123' })

// One row
const book = await SELECT.one.from('Books').where({ ID: req.data.ID })

Column projection and aliases

const result = await SELECT.from('Books').columns(b => {
  b.ID,
  b.title.as('bookTitle'),
  b.price,
  b('stock * price').as('inventory_value')
})

WHERE with operators

// Operator comparison
SELECT.from('Books').where('price >', 10)
SELECT.from('Books').where('price between', 10, 'and', 50)

// Object style
SELECT.from('Books').where({ price: { '>': 10 }, stock: { '>': 0 } })

// Parameterized (safe from injection)
SELECT.from('Books').where(`title like`, '%CAP%')

// OR conditions
SELECT.from('Books').where(`status = 'ACTIVE' or stock > 100`)

ORDER BY, LIMIT, OFFSET

const paginated = await SELECT.from('Books')
  .orderBy('price asc', 'title')
  .limit(20, 40)   // limit 20, skip 40 → page 3

// With columns
const topPriced = await SELECT.from('Books')
  .columns('title', 'price')
  .orderBy({ price: 'desc' })
  .limit(10)

SELECT with expand (associations)

// Inline expand
const orders = await SELECT.from('Orders').columns(o => {
  o.ID, o.status,
  o.items(i => { i.book_ID, i.quantity, i.price })
})
// Generates: SELECT ... FROM Orders LEFT JOIN OrderItems ON ...

INSERT

// Single row
await INSERT.into('Books').entries({
  ID:    cds.utils.uuid(),
  title: 'SAP CAP Handbook',
  price: 49.99
})

// Multiple rows
await INSERT.into('Books').entries([book1, book2, book3])

// Return inserted row (HANA / PostgreSQL)
const inserted = await INSERT.into('Books').entries(data).returning('*')

UPDATE

// Update by key
await UPDATE('Books', { ID: req.data.ID }).with({ price: 59.99 })

// Update with expression
await UPDATE('Books').set('stock -= 1').where({ ID: bookId })

// Conditional update
await UPDATE('Books')
  .set({ status: 'OUT_OF_STOCK' })
  .where({ stock: 0 })

DELETE

// Delete by key
await DELETE.from('Books', { ID: req.data.ID })

// Delete with WHERE
await DELETE.from('Books').where({ status: 'ARCHIVED', updatedAt: { '<': cutoff } })

Running queries

// Inside a handler: uses the request's transaction automatically
const db = await cds.connect.to('db')
const result = await db.run(SELECT.from('Books'))

// CAP shorthand — same as above when called from within a handler
const result = await cds.run(SELECT.from('Books'))

// Direct execute (no transaction wrapper)
await cds.db.run(`SELECT * FROM Books WHERE price < ?`, [50])

Transaction control

const tx = cds.transaction(req)   // attach to request transaction

await tx.run(INSERT.into('AuditLog').entries({ ... }))
await tx.run(UPDATE('Orders').set({ status: 'DONE' }).where({ ID }))
// Both committed when the request handler returns successfully
// Rolled back automatically if handler throws

Limits to be aware of

Feature	Support
Subqueries in WHERE	CQL does not support subqueries in WHERE — use JOIN or multiple queries
Database functions (HANA-specific)	Use cds.hana annotations or raw db.run(native SQL)
Aggregates in SELECT	Supported: SUM(price), COUNT(*)
GROUP BY	Supported via .groupBy('category')
Unions	Not in fluent API — use raw SQL

Hands-on exercise

1. Write a handler for GET /catalog/ExpensiveBooks that returns books with price > 30, ordered by price desc, max 10
2. Write an updateStock action that uses UPDATE('Books').set('stock -=', qty).where({ ID })
3. Write a batch insert that creates 5 seed books from an array
4. Write a query that expands author inline and returns title + author name

Checkpoint ✓

• [ ] SELECT.one.from(...) returns a single object or undefined
• [ ] .where() supports both object style and string expressions
• [ ] INSERT.into().entries() accepts single object or array
• [ ] UPDATE().set().where() supports computed expressions like 'stock -=', 1

Sign in for progress tracking & Q&A →