status
released

Querying in JavaScript

[[toc]]

Constructing Queries

Module cds.ql provides a SQL-like fluent API to construct queries:

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

Alternative to classic method calls we can also use the fluent API with tagged templates:

let q1 = SELECT.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}`

The API is made available through global objects SELECT, INSERT, UPSERT, UPDATE, DELETE. Alternatively, you can obtain these objects from cds.ql like so:

const cds = require('@sap/cds')
const { SELECT, INSERT, UPDATE, DELETE } = cds.ql

The API is also available through cds.Service's CRUD-style Convenience API {.learn-more}

::: details Using Reflected Definitions

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.

const { Books } = cds.entities
let q1 = SELECT.from (Books) .where `ID=${201}`

Learn more about using reflected definitions from a service's model{.learn-more}

:::

::: details Not Locked in to SQL While both CQL / CQN as well as the fluent API of cds.ql resemble well-known SQL syntax neither of them are locked in to SQL. In fact, queries can be sent to any kind of services, including NoSQL databases or remote services for execution. :::

Executing Queries

Queries are executed by passing them to a service's srv.run() method, for example, to the primary database:

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

Alternatively, you can just await a constructed query, which by default passes the query to cds.db.run(). So, the following is equivalent to the above:

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

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)

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

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:

let cats = await cds.connect.to('CatalogService') //> connected via OData
let PoesBooks = SELECT.from (Books) .where `name like '%Poe%'`
let books = await cats.get (PoesBooks)

This is what happens behind the scenes:

We use the fluent API to construct a query as a CQN and assign it to PoesBooks
We pass the query as an argument to function cats.get()
The get event handler translates the query to an OData request sent to the remote service
The remote OData protocol adapter translates the inbound query back to CQN
This CQN query is passed on to the remote service provider
A registered event handler forwards that query to the local cds.db service
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

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...

captured as a CQN object with the where clause represented as:

..., where:[ {ref:['ID']}, '=', {val:201} ]

translated to plain SQL string with binding parameters

SELECT ID from Books where ID=?

executed with binding parameters provided from {val}entries in CQN

dbc.run (sql, [201])

The only mistake you could make is to imperatively concatenate user input with CQL or SQL fragments, 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!

{style="margin: 10px 40px"}

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

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

::: danger Whenever there's user input involved... Never use string concatenation when constructing queries!

Never surround tagged template strings with parentheses! :::

cds.ql. Query {#class-cds-ql-query .class}

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

.kind {.property}

The kind of query, that is one of these strings:

'SELECT'
'INSERT'
'UPSERT'
'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() {.method}

Instances of cds.Query are thenables. awaiting them executes the query with the bound service or the primary database service.

await SELECT.from(Books) // is equivalent to:
await cds.db.run( SELECT.from(Books) )

bind (srv) {.method}

Binds a query for execution with the given srv .

let srv = new cds.Service
await SELECT.from(Books).bind(srv) // is equivalent to:
await srv.run( SELECT.from(Books) )

SELECT {.class}

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 itself is a function acting as a shortcut to SELECT.columns, thereby resembling SQL syntax:

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

Moreover, it accepts a single tagged template string 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 allows constructing CQN query objects using CQL language constructs which are not covered by cds.ql fluent API.

.one {.property}

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)

.elements {.property}

The CSN outline of the selected elements as an object. Key is the selected element or alias, value is the CSN definition:

Let's assume the following query:

SELECT.from('sap.capire.bookshop.Books').columns('ID', 'title')

This query is represented within .elements as:

{
  ID: number { key: true, type: 'cds.Integer' },
  title: string {
    '@mandatory': true,
    localized: true,
    type: 'cds.String',
    length: 111,
    '@Common.FieldControl': { '#': 'Mandatory' }
  }
}

This is useful for custom implementations that act on the selection of specific elements.

.distinct {.property}

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

SELECT.distinct.from (Authors)

columns() {.method}

function SELECT.columns ( projection : function )
function SELECT.columns ( cql : tagged template string )
function SELECT.columns ( columns[] : CQL expr string | CQN expr object )
function SELECT.columns ( ...columns[] : CQL expr string | CQN expr object )

Specifies which columns to be fetched, very much like SQL select clauses, enhanced by CQL projections and path expressions. The arguments can be a projection function, a tagged template string, or individual column expressions as CQL string snippets, or as CQN column expression objects.

SELECT.from `Books` .columns (b => { b.title, b.author.name.as('author') })
SELECT.from `Books` .columns `{ title, author.name as author }`
SELECT.from `Books` .columns `title, author.name as author`
SELECT.from `Books` .columns ( 'title', 'author.name as author')
SELECT.from `Books` .columns ( 'title', {ref:['author','name'],as:'author'} )
SELECT.from `Books` .columns (['title', {ref:['author','name'],as:'author'} ])

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

from() {.method #select-from}

function SELECT.from (
   entity : string | CSN definition | tagged template string,
   key?   : string | number | object,
   cols?  : array  | projection
)

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.

Argument key can be a single string or number value, or a query-by-example object:

SELECT.from (Books,201) //> shortcut for {ID:201}
SELECT.from (Books, {ID:201})
SELECT.from (Books.texts, {ID:201, locale:'de'})

Argument cols is a projection as accepted by .columns (cols)

alias() {.method}

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(){.method alt="The following documentation on having also applies to where"}

having() {.method}

These two methods fill in corresponding CQL clauses with predicate expressions.

function SELECT.where/having ( qbeobj : query-by-example object )
function SELECT.where/having ( clause : tagged template string )
function SELECT.where/having ( expr: string, value: any, ... )

Expressions can be specified as a query-by-example object, a tagged template string, or as an alternating string / value arguments list:

SELECT.from `Books` .where ({ ID: req.data.ID }) // qbe
SELECT.from `Books` .where `ID = ${req.data.ID}` // tts
SELECT.from `Books` .where ('ID =', req.data.ID) // expr/value list

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() {.method}

Fills in SQL 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() {.method}

Fills in 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() {.method}

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() {.method}

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.sql.lock_acquire_timeout: -1. 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() {.method}

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 {.class}

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 itself is a function acting as a shortcut to INSERT.entries, 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() {.method}

function INSERT.into (
  entity   : string | CSN definition | tagged template string,
  entries? : object[]
)

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() {.method #insert-entries}

function INSERT.entries (records : object[] | Query | Readable)

Allows inserting multiple rows with one statement.

The arguments can be one of...

one or more records as variable list of arguments
an array of one or more records
a readable stream
a sub SELECT query

Using individual records:

await 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 }
)

Using an array of records, read from a JSON:

let books = JSON.parse (fs.readFileSync('books.json'))
await INSERT(books).into(Books) // same as INSERT.into(Books).entries(books)

Using a stream instead of reading and parsing the full JSON into memory:

let stream = fs.createReadStream('books.json')
await INSERT(stream).into(Books) // same as INSERT.into(Books).entries(stream)

Using a subselect query to copy within the database:

await INSERT.into (Books) .entries (SELECT.from(Products))

::: details Pushed down to database....

Note that the sub select variant creates a single native INSERT INTO SELECT SQL statement, which is most efficient, as the data is copied within the database. In contrast to that, ...

INSERT.into(Books).entries(await SELECT.from(Products))

... would also work, but would be much less efficient, as it would (1) first read all data from database into the client and then (2) insert the read data back into the database.

:::

values() {.method alt="The following documentation on rows also applies to values. "}

rows() {.method}

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 ]
)

from() {.method #from}

Constructs a INSERT into SELECT statement.

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

as() {.method}

The use of .as() method is deprecated. Please use .from() method instead.

UPSERT {.class}

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 itself is a function acting as a shortcut to UPSERT.entries, 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() {.method #upsert-entries}

function UPSERT.into (
  entity   : string | CSN definition | tagged template string,
  entries? : object[]
)

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() {.method}

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.

Learn more about limitations when using it with databases.{.learn-more}

UPDATE {.class}

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

UPDATE itself is a function acting as a shortcut to UPDATE.entity, allowing usages like this:

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

entity() {.method}

function UPDATE.entity (
   entity : string | CSN definition | tagged template string,
   key?   : string | number | object,
)

Specifies the target of the update operation, optionally followed by a primary key, and a projection. The latter provides an alternative for using separate .where clauses.
For example, these queries are equivalent:

UPDATE (Books,201)...
UPDATE (Books) .where ({ID:201}) ...

Argument key can be a single string or number value, or a query-by-example object:

UPDATE (Books,201) ... //> shortcut for {ID:201}
UPDATE (Books, {ID:201}) ...
UPDATE (Books.texts, {ID:201, locale:'de'}) ...

set() {.method alt="The following documentation on with also applies to set. "}

with() {.method}

Specifies the data to update...

As a single-expression tagged template string

let [ ID, quantity ] = [ 201, 1 ]
UPDATE `Books` .set `stock = stock - ${quantity}` .where `ID=${ID}`

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() {.method}

As in SELECT.where {.learn-more}

DELETE {.class}

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('Books').where ({stock:{'<':1}})

from() {.method #delete-from}

function DELETE.from (
   entity : string | CSN definition | tagged template string,
   key?   : string | number | object
)

As in SELECT.from {.learn-more}

where() {.method}

As in SELECT.where {.learn-more}

Files

cds-ql.md

Latest commit

History

cds-ql.md

File metadata and controls

Querying in JavaScript

Constructing Queries

Executing Queries

First-Class Objects

Leveraging Late Materialization

Avoiding SQL Injection

cds.ql. Query {#class-cds-ql-query .class}

.kind {.property}

then() {.method}

bind (srv) {.method}

SELECT {.class}

.one {.property}

.elements {.property}

.distinct {.property}

columns() {.method}

from() {.method #select-from}

alias() {.method}

where(){.method alt="The following documentation on having also applies to where"}

having() {.method}

groupBy() {.method}

orderBy() {.method}

limit() {.method}

forUpdate() {.method}

forShareLock() {.method}

INSERT {.class}

into() {.method}

entries() {.method #insert-entries}

values() {.method alt="The following documentation on rows also applies to values. "}

rows() {.method}

from() {.method #from}

as() {.method}

UPSERT {.class}

into() {.method #upsert-entries}

entries() {.method}

UPDATE {.class}

entity() {.method}

set() {.method alt="The following documentation on with also applies to set. "}

with() {.method}

where() {.method}

DELETE {.class}

from() {.method #delete-from}

where() {.method}