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:
cats.read
constructs and returns a new query- which is complemented with a
where
clause - Upon
await
the query is passed tocats.run()
- A registered event handler translates the query to an OData request and sends that to the remote service
And on the remote side:
- The OData protocol adapter translates the inbound query to CQN query
- This query is passed 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 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…
- captured as a CQN object with the where clause represented as:
..., where:[ {ref:['title']}, '=', {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 CQNdbc.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…
- 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`
- 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….
- A projection function:
SELECT.from `Foo` .columns (foo => { foo.a, foo.b.as('c') })
- A tagged template string:
SELECT.from `Foo` .columns `{ a, b as c }`
- CQL column expressions:
SELECT.from `Foo` .columns ('a', 'b as c')
- CQN expression objects:
SELECT.from `Foo` .columns ({ref:['a']}, {ref:['b'], as:'c'})
- 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`.*`
, andb.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 enablesSELECT.one
and moves thewhere
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:
- a single value in case of a single key, named
ID
:SELECT.from (Books,201)
- a query-by-example object for single or compound keys:
SELECT.from (Books, {ID:201}) SELECT.from (Books.texts, {ID:201, locale:'de'})
Argument cols
is a projection …
↳ .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:
- 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.
- 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.
- 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 defaultwait
value that is used ifoptions.wait == null
can be specified viacds.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(...)
- a query-by-example object for single or compound keys:
UPDATE (Books, {ID:201}) .with(...)
UPDATE (Books.texts, {ID:201, locale:'de'}) .with(...)
↳ .with, set (…)
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 (expr)
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)
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. await
ing them executes the query with the target’s service, or the primary database service as explained in section Executing Queries.