Skip to content
Search

    Embedded CQL with cds.ql

    Use the cds.ql to construct queries using a fluent, SQL-like API. In combination with tagged template strings this creates a sweeping embedded CQL experience. The API can be used via global constants or through cds.ql:

    const cds = require('@sap/cds')   //> adds global constants
const q = SELECT.from('Foo')     //> using global constant

    const { SELECT, INSERT } = cds.ql  //> gets local variables
const q = SELECT.from('Foo')      //> using local variable


    Constructing Queries

    You can choose between two primary styles to construct queries: A SQL-like fluent API style provided by cds.ql or a call-level Querying API provided by cds.Service. The lines between both blur, as the latter is actually just a shortcut to the former. This is especially true when combining both with the use of tagged template string literals.

    Using Fluent APIs with classic method calls

    The Fluent API resembles well-known SQL syntax to construct queries like that:

    let q1 = SELECT.one.from('Books').where({ID:201})
let q2 = INSERT.into('Books').entries({title:'Wuthering Heights'})
let q3 = UPDATE('Books').where({ID:201}).with({title:'Sturmhöhe'})
let q4 = DELETE.from('Books').where({ID:201})

    Not Locked in to SQL
    While both, CQN as well as the fluent API resemble well-known SQL syntax, cds.ql isn’t locked in to SQL. In fact, queries can be sent to any kind of services, including NoSQL databases or remote services for execution.

    Using Service APIs plus Fluent APIs

    The following uses the Querying API provided by cds.Service to construct exactly the same effective queries as the ones constructed with the fluent API above:

    let q1 = cds.read('Books',201)
let q2 = cds.create('Books',{title:'Wuthering Heights'})
let q3 = cds.update('Books',201,{title:'Sturmhöhe'})
let q4 = cds.delete('Books',201)

    As documented in the cds.Services API docs, these methods are actually just shortcuts to the respective Fluent API methods above, and can be continued with calls to fluent API function, thus blurring the lines. For example, also these lines are equivalent to both variants above:

    let q1 = cds.read('Books').where({ID:201})
let q2 = cds.create('Books').entries({title:'Wuthering Heights'})
let q3 = cds.update('Books').where({ID:201}).with({title:'Sturmhöhe'})
let q4 = cds.delete('Books').where({ID:201})

    Using Tagged Template String Literals

    Version 5 of @sap/cds introduced support for tagged template string literals with both API styles, which greatly promotes embedded CQL experience.

    The Fluent API example above could be rewritten like this:

    let q1 = SELECT.one.from `Books` .where `ID=${201}`
let q2 = INSERT.into `Books` .entries ({title:'Wuthering Heights'})
let q3 = UPDATE `Books` .where `ID=${201}` .with `title=${'Sturmhöhe'}`
let q4 = DELETE.from `Books` .where `ID=${201}`

    Similarly, we could rewrite the Service API example like this:

    let q1 = cds.read `Books` .where `ID=${201}`
let q2 = cds.create `Books` .entries ({title:'Wuthering Heights'})
let q3 = cds.update `Books` .where `ID=${201}` .with `title=${'Sturmhöhe'}`
let q4 = cds.delete `Books` .where `ID=${201}`

    Using Reflected Definitions as Query Targets

    It is recommended best practice to use entity definitions reflected from a service’s model to construct queries. Doing so greatly simplifies code as it avoids repeating namespaces all over the place.

    For example:

    const { Books } = cds.entities
let q1 = SELECT.one.from (Books) .where `ID=${201}`
let q2 = INSERT.into (Books) .entries ({title:'Wuthering Heights'})
let q3 = UPDATE (Books) .where `ID=${201}` .with `title=${'Sturmhöhe'}`
let q4 = DELETE.from (Books) .where `ID=${201}`

    Learn more about using reflected definitions from a service’s model

    Executing Queries

    Essentially queries are executed by passing them to a service’s srv.run method. Most frequently, you can also just use await on a query to do so.

    Passing Queries to srv.run(...)

    The basic mechanism to execute a query is to pass it to a srv.run method. For example, using the primary database service cds.db:

    let query = SELECT `ID,title` .from `Books`

    let books = await cds.run (query)

    Note: cds acts as a shortcut to cds.db → see cds.run for details

    Sending Queries to Other Services

    Instead of a database service, you can also send queries to other services, local or remote ones. For example:

    const cats = await cds.connect.to ('CatalogService')
let books = await cats.run (query)

    Learn more about connecting to other services

    CatalogService might be a remote service connected via OData. In this case, the query would be translated to an OData request sent via http.

    Promise-await-ing Queries

    Alternatively, you can just await a constructed query, which by default passes the query to the primary database service’s srv.run method. That is, the following two code samples are equivalent:

    let books = await SELECT `ID,title` .from `Books`

    let books = await cds.run (SELECT `ID,title` .from `Books`)

    With Reflected Query Targets

    As explained above, it is recommended best practice to use reflected definitions as query targets. These definitions ‘remember’ where they came from, hence await-ing respective queries will send them to the originating service, hence the following two samples are equivalent:

    // asumed we did that before:
const cats = await cds.connect.to ('CatalogService')
const { Books } = cats.entities

    let books = await SELECT `ID,title` .from (Books)

    let books = await cats.run (SELECT `ID,title` .from (Books))

    With Bound Queries from srv.<crud>

    Finally, when using the CRUD-style Service Querying APIs, the constructed queries returned by the respective methods are bound to the originating service, and will be sent to that service’s srv.run() method upon await. Hence these samples are equivalent:

    let books = await srv.read `ID,title` .from `Books`

    let query = srv.read `ID,title` .from `Books`
let books = await srv.run(query)

    Queries are First-Class Objects

    Constructing queries doesn’t execute them immediately, but just captures the given query information. Very much like functions in JavaScript, queries are first-class objects, which can be assigned to variables, modified, passed as arguments, or returned from functions. Let’s investigate this somewhat more, given this example:

    const cats = await cds.connect.to('CatalogService') //> connected via OData
const books = await cats.read `Books` .where `name like '%Poe%'`

    This is what happens behind the scenes:

    1. cats.read constructs and returns a new query
    2. which is complemented with a where clause
    3. Upon await the query is passed to cats.run()
    4. A registered event handler translates the query to an OData request and sends that to the remote service

    And on the remote side:

    1. The OData protocol adapter translates the inbound query to CQN query
    2. This query is passed to the remote service provider
    3. A registered event handler forwards that query to the local cds.db service
    4. The database service implementation translates the query to plain SQL and sends that to the database for execution

    Leveraging Late Materialization

    You can also combine queries much like sub selects in SQL to form more complex queries as shown in this example:

    let input = '%Brontë%'
let Authors = SELECT `ID` .from `Authors` .where `name like ${input}`
let Books = SELECT.from `Books` .where `author_ID in ${Authors}`

    await cds.run (Books) //> late/no materialization of Authors

    With that we leverage late materialization, offered by SQL databases. Compare that to inferior imperative programming:

    let input = '%Brontë%'
let Authors = await SELECT `ID` .from `Authors` .where `name like ${input}`
for (let a of Authors) { //> looping over eagerly materialized Authors
  let Books = await SELECT.from `Books` .where `author_ID = ${a.ID}`
}

    Avoiding SQL Injection by Design

    All the APIs are designed to easily avoid SQL Injection by default. For example, let’s see how the following code would be executed:

    let input = 201 //> might be entered by end users
let books = await SELECT.from `Books` .where `ID=${input}`

    The query is…

    1. captured as a CQN object with the where clause represented as: 
      ..., where:[ {ref:['title']}, '=', {val:201} ]
    2. translated to plain SQL string with binding parameters 
      SELECT ID from Books where ID=?
    3. executed with binding parameters provided from {val}entries in CQN 
      dbc.run (sql, [201])

    The only mistake you could do is to imperatively concatenate user input with CQL or SQL fragements, instead of using the tagged strings or other options promoted by cds.ql. For example, assumed you had written the above code sample like that:

    let input = 201 //> might be entered by end users
let books = await SELECT.from `Books` .where ('ID='+input)
let bookz = await SELECT.from `Books` .where (`ID=${input}`)

    Note also that tagged template strings never have surrounding parentheses! I.e., the third line above does the very same string concatenation as the second line.

    A malicious user might enter some SQL code fragment like that:

    0; DELETE from Books; -- gotcha!

    In effect, your generated SQL statements would effectively look like that:

    SELECT ID from Books where ID=0;
DELETE from Books; -- gotcha!

    WARNING: Whenever there’s user input involved…
    Never use string concatenation when constructing queries!
    Never surround tagged template strings with parentheses!

    SELECT …

    Fluent API to construct CQN SELECT query objects in a CQL/SQL-like style. In contrast to SQL, though, the clauses can be arrayed in arbitrary order.

    SELECT…

    The root constant SELECT is a function itself, which acts as a shortcut to SELECT.columns, thereby resembling SQL syntax as close as possible:

    SELECT `a, b as c` .from `Foo`  -- is a shortcut for:
SELECT .columns `a, b as c` .from `Foo`

    Moreover, it accepts a single tagged template string which can comprise all CQL clauses, starting with from:

    const limit = 11, sort_column = 'a'
const q = SELECT `from Foo {
   a, b as c, sum(d)
} where x < ${limit} 
group by a,b
order by ${sort_column} asc`
const foos = await q

    This can be used to construct CQN query objects from statement using CQL language constructs which are not covered by cds.ql Fluent API.

    ↳ .one | .distinct

    Start constructing a query with SELECT.one to indicate we’re interested in only the first row. At runtime, a single entry, if any, is returned instead of an array:

    const one = await SELECT.one.from (Authors)

    same effect, but potentially more expensive:

    const [one] = await SELECT.from (Authors)

    Start the query with SELECT.distinct to skip duplicates as in SQL:

    SELECT.distinct.from (Authors)

    ↳ .columns (cols)

    Specifies which columns to be fetched, very much like SQL select clauses, enhanced by CQL projections and path expressions. If called repeatedly, respective columns are added cumulatively.

    API Style Variants

    The clauses methods can be used in varying order as follows…

    1. SQL-style Prefix Projections 
      SELECT `a, b as c, count(d) as e` .from `Foo`

      … which essentially is a shortcut to:

      SELECT .columns `a, b as c, count(d) as e` .from `Foo`
    2. CQL-style Postfix Projections 
      SELECT .from `Foo` .columns `a, b as c, count(d) as e`

    … optionally enclosed in braces:

    SELECT .from `Foo` .columns `{ a, b as c, count(d) as e }`

    The examples above show tagged template syntax variants, the same styles are available with classic method call variants or when Service Querying APIs.

    Arguments Variants

    In all API style variants, the arguments describe the desired projections, which in turn can be specified by one of….

    1. A projection function: 
      SELECT.from `Foo` .columns (foo => { 
  foo.a, foo.b.as('c') 
})
    2. A tagged template string: 
      SELECT.from `Foo` .columns `{ a, b as c }`
    3. CQL column expressions: 
      SELECT.from `Foo` .columns ('a', 'b as c')
    4. CQN expression objects: 
      SELECT.from `Foo` .columns ({ref:['a']}, {ref:['b'], as:'c'})
    5. An array of 3 and/or 4: 
      SELECT.from `Foo` .columns ([ 'a', 'b as c' ])

    All of the examples above produce the same CQN.

    Projection Functions

    Projection functions are the most recommended way to specify projections as they have several advantages (with tagged templates coming closest):

    • they support nested projections, aka expands
    • they don’t need to call a parser
    • they resemble CQL very much
    • they use standard JavaScript constructs
    • we can perspectively offer type inference and code completion

    With respect to resembling CQL let’s compare this query in CQL using entity aliases to the cds.ql code sample below:

    SELECT from Authors a {
   a.ID, a.name, a.books {
     *, createdAt as since, 
     suppliers[city='Paris']{*}
   }
}

    Here is the same using cds.ql with projection functions:

    SELECT.from ('Authors', a => {
   a.ID, a.name, a.books (b => {
     b`.*`, b.createdAt`as since`, 
     b.suppliers`[city='Paris']`('*')
   })
})

    Projection functions use these mechanisms:

    • projections are single-argument arrow functions: a => { ... }
    • with the argument as entity alias in column expressions: a.name
    • with functions for nested projections: a.books (b => {...})
    • with * as special case of that: b`.*`, and b.suppliers('*')
    • with template strings for aliases: b.createdAt`as since`
    • as well as for infix filters: b.suppliers`[city='Paris']`

    Note: Not every CQL or SQL construct can be expressed with projection functions. This is where tagged template strings kick in.

    ↳ .excluding (…refs)

    Learn more about excluding clauses in CQL

    ↳ .from (entity, key?, cols?)

    Fills in CQN from clauses, optionally adding a primary key, and a projection. The latter are alternatives for using separate .one, .where and .columns clauses.

    For example, these queries:

    SELECT.from (Books,201)
SELECT.from (Books,201, b => { b.ID, b.title })

    … are equivalent to these:

    SELECT.one.from (Books) .where ({ID:201}) 
SELECT.one.from (Books) .where ({ID:201}) 
.columns (b => { b.ID, b.title })

    NOTE: Specifying a key argument automatically enables SELECT.one and moves the where clause into the entity reference.

    Argument entity can be one of:
    • A string with an entity’s fully-qualified name, or relative to the target service’s namespace (the default namespace for primary db). 
      SELECT.from `my.bookshop.Books`
SELECT.from `Books` //> for namespace my.bookshop
    • An entity definition from a reflected model. 
      const { Books } = cds.entities
SELECT.from (Books)
    Argument key can be one of:
    Argument cols is a projection …

    → as accepted by .columns (cols)

    ↳ .alias (string)

    Specifies the alias which you can refer to in other functions:

    SELECT.from ('Authors').alias('a')
  .where({ exists: SELECT.from('Books').where('author_ID = a.ID')})

    ↳ .where (expr)

    ↳ .having (expr)

    These methods allow to fill in corresponding CQL clauses with predicate expressions, which can be specified in different variants:

    1. As tagged template string literals: 
      SELECT.from `Books` .where `ID = ${req.data.ID}`

      Offers most flexibility, including native constructs. Still values are isolated and passed via argument bindings, hence avoiding SQL injection.

    2. As alternating string / value arguments list: 
      SELECT.from `Books` .where ('ID =', req.data.ID)

      Was a predecessor to variant 1, now more or less became obsolete with the availability of tagged template string variants.

    3. As query-by example object literal: 
      SELECT.from `Books` .where ({ ID: req.data.ID })

      Comes in handy when applying user input obtained as objects. It supports restricted ways to express certain operators as shown below.

    Assumed we got some user input as follows:

    const name='foo', kinds=[1,2,3], min=0.1, max=0.9, stock=111

    With tagged template strings we could construct a query like that:

    SELECT.from `Foo` .where `name like ${name} and (
   kind in ${kinds} 
   or ratio between ${min} and ${max} 
   or stock >= ${stock}
)`

    Doing the same with object literals would look like that:

    SELECT.from('Foo') .where ({ name: {like:'%foo%'}, and: { 
   kind: { in: kinds }, 
   or: { ratio: { between: min, and: max }, 
     or: { stock: { '>=': stock } }
    }
}})

    The provided expression is consistently accounted for by wrapping the existing where clause in an xpr if needed.

    ↳ .groupBy (…refs)

    Allows to capture SQL-like group by clauses. Arguments are a single tagged template string, or column expression strings or CXN objects, like that:

    SELECT ... .groupBy `a.name, b`
SELECT ... .groupBy ('a.name', 'b')
SELECT ... .groupBy ({ref:['a','name']}, {ref:['b']})

    ↳ .orderBy (…refs+)

    Equivalent of the standard SQL order by clauses. Arguments are a single tagged template string, or column expression strings, optionally followed by asc or desc, or CXN objects, like that:

    SELECT ... .orderBy `a.name, b desc`
SELECT ... .orderBy ('a.name', 'b desc')
SELECT ... .orderBy ({ref:['a','name']}, {ref:['b'],sort:'desc'})

    ↳ .limit (rows, offset?)

    Equivalent of the standard SQL limit and offset clauses. Arguments can be standard numbers or CXN expression objects.

    SELECT ... .limit (25)      //> first page
SELECT ... .limit (25,100)  //> fifth page

    ↳ .forUpdate (options?) …

    Exclusively locks the selected rows for subsequent updates in the current transaction, thereby preventing concurrent updates by other parallel transactions.

    try {
   let book = await SELECT.from(Books,201).forUpdate()
   //> book is locked for other transactions
   await UPDATE (Books,201) .with ({...})
} catch (e) {
   //> failed to acquire the lock, likely because of timeout
}

    The options argument is optional; currently supported is:

    • wait — an integer specifying the timeout after which to fail with an error in case a lock couldn’t be obtained. The time unit is database-specific. On SAP HANA, for example, the time unit is seconds. A default wait value that is used if options.wait == null can be specified via cds.env.sql.lock_acquire_timeout. A value of -1 can be used to deactivate the default for the individual call. If the wait option isn’t specified, the database-specific default behavior applies.

    All acquired locks are released when the current transaction is finished, that is, committed or rolled back.



    ↳ .forShareLock ()

    Locks the selected rows in the current transaction, thereby preventing concurrent updates by other parallel transactions, until the transaction is committed or rolled back. Using a shared lock allows all transactions to read the locked record.

    If a queried record is already exclusively locked by another transaction, the .forShareLock() method waits for the lock to be released.

    INSERT …

    Fluent API to construct CQN INSERT query objects in a CQL/SQL-like style. In contrast to SQL, though, the clauses can be arrayed in arbitrary order.

    INSERT…

    The root constant INSERT is a function itself, which acts as a shortcut to INSERT.entries, thereby allowing uses like that:

    const books = [
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
]
INSERT (books) .into (Books)

    ↳ .into (entity, …data?)

    Specifies the target entity to insert data into, either as a string or a reflected definition..

    const { Books } = cds.entities
INSERT.into (Books) .entries (...)
INSERT.into ('Books') .entries (...)
INSERT.into `Books` .entries (...)

    You can optionally pass records of data as accepted by .entries as a shortcut to which:

    INSERT.into (Books, [
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
])

    ↳ .entries (…data)

    Allows inserting multiple rows with one statement where each row is a record with named values, for example, as could be read from a JSON source.

    INSERT.into (Books) .entries (
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
)

    The entries can be specified as individual method parameters of type object — as shown above —, or as a single array of which.

    ↳ .values / rows (…)

    Use .columns with .values as in SQL:

    INSERT.into (Books) .columns (
   'ID', 'title', 'author_id', 'stock'
) .values (
   201, 'Wuthering Heights', 101, 12
)

    Both, .columns and .values can alternatively wrapped into an array.

    Use .rows instead of .values to insert multiple rows with one statement:

    INSERT.into (Books) .columns (
   'ID', 'title', 'author_id', 'stock'
) .rows (
   [ 201, 'Wuthering Heights', 101, 12 ],
   [ 251, 'The Raven', 150, 333 ],
   [ 252, 'Eleonora', 150, 234 ]
)

    ↳ .as ( SELECT… )

    Constructs a INSERT into SELECT statement.

    INSERT.into('Bar') .as (SELECT.from('Foo'))



    UPSERT …

    Fluent API to construct CQN UPSERT query objects in a CQL/SQL-like style. In contrast to SQL, though, the clauses can be arrayed in arbitrary order.

    UPSERT…

    The root constant UPSERT is a function itself, which acts as a shortcut to UPSERT.entries, thereby allowing uses like that:

    const books = [
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
]
UPSERT (books) .into (Books)

    ↳ .into (entity, …data?)

    Specifies the target entity to upsert data into, either as a string or a reflected definition..

    const { Books } = cds.entities
UPSERT.into (Books) .entries (...)
UPSERT.into ('Books') .entries (...)
UPSERT.into `Books` .entries (...)

    You can optionally pass records of data as accepted by .entries as a shortcut to which:

    UPSERT.into (Books, [
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
])

    ↳ .entries (…data)

    Allows upserting multiple rows with one statement where each row is a record with named values, for example, as could be read from a JSON source.

    UPSERT.into (Books) .entries (
   { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
   { ID:251, title:'The Raven', author_id:150, stock:333 },
   { ID:271, title:'Catweazle', author_id:170, stock:222 }
)

    The entries can be specified as individual method parameters of type object — as shown above —, or as a single array of which.

    UPDATE …

    UPDATE…

    The root constant UPDATE is a function itself, which acts as a shortcut to UPDATE.entity, thereby resembling SQL syntax as close as possible:

    UPDATE `Books` .set `stock = stock - ${quantity}` -- as shortcut to:
UPDATE.entity `Books` .set `stock = stock - ${quantity}`

    ↳ .entity (entity, key?) …

    Specifies the target of the update operation, either as a fully qualified name, a name local to the target service or as a reflected definition.

    Argument entity can be one of:
    • A string with an entity’s fully-qualified name, or relative to the target service’s namespace (the default namespace for primary db).
    UPDATE `my.bookshop.Books` ...
UPDATE `Books` ... //> for namespace my.bookshop
    • An entity definition from a reflected model.
    const { Books } = cds.entities
UPDATE (Books) .with(...)
    Argument key can be one of:
    • a single value in case of a single key, named ID:
    UPDATE (Books,201) .with(...)

    UPDATE (Books, {ID:201}) .with(...)
UPDATE (Books.texts, {ID:201, locale:'de'}) .with(...)

    Learn more about <entity>.texts property

    ↳ .with, set (…)

    Specifies the data to update…

    1. As a single-expression tagged template string 
      let [ ID, quantity ] = [ 201, 1 ]
UPDATE `Books` .set `stock = stock - ${quantity}` .where `ID=${ID}`
    2. As an object with keys being element names of the target entity and values being simple values, query-by-example expressions, or CQN expressions: 
      let [ ID, quantity ] = [ 201, 1 ]
UPDATE (Books,ID) .with ({
   title: 'Sturmhöhe',       //>  simple value
   stock: {'-=': quantity},    //>  qbe expression
   descr: {xpr: [{ref:[descr]}, '||', 'Some addition to descr.'])
})

    Method .set and .with are aliases to the same method.

    ↳ .where (expr)

    As in SELECT.where



    DELETE …

    Fluent API to construct CQN DELETE query objects in a CQL/SQL-like style. In contrast to SQL, though, the clauses can be arrayed in arbitrary order.

    DELETE…

    ↳ .from (entity, key?) …

    DELETE.from('Books').where ({stock:{'<':1}})

    ↳ .where (expr)

    As in SELECT.where



    Class cds.Query

    Instances of cds.Query capture queries at runtime. Subclasses provide fluent APIs to construct queries as highlighted below.

    .cmd → ‘SELECT’ | ‘INSERT’ | ‘UPDATE’ | ‘DELETE’ | …

    The current command, that is one of these strings:

    • 'SELECT'
    • 'INSERT'
    • 'UPDATE'
    • 'DELETE'
    • 'CREATE'
    • 'DROP'

    This is usefull for generic query processors, such as outbound protocol adapters or database services, which need to translate given queries into target representations.

    .then ⇢ results

    All instances of cds.Query, that is, all queries constructed with the fluent API functions as documented below, are thenables. awaiting them executes the query with the target’s service, or the primary database service as explained in section Executing Queries.

    ✉