Onyx Database Query API
Onyx Database 2.0.0 introduces a powerful JavaScript DSL for querying data. This document provides comprehensive documentation on how to use the Query Builder syntax to interact with your data in a readable and maintainable way.
Getting Started
The Query Builder in Onyx Database provides a DSL-like syntax in JavaScript. It allows you to build complex queries using familiar functions and chaining of conditions, making your code both expressive and easy to maintain.
1
2// First, create a new OnyxClient instance:
3const db = new OnyxClient({
4 baseUrl: 'https://api.onyx.dev',
5 databaseId: 'your_database_id',
6 authToken: 'your_auth_token'
7});
8
9// Example Query:
10const result = await db.from('Stats')
11 .where(
12 neq('rushingYards', 0)
13 .and(gt('passingYards', 3))
14 .and(
15 eq('player.position', 'QB')
16 .or(contains('player.firstName', 'a'))
17 )
18 )
19 .list();
20
21// 'result' contains the query response, including result.entities
22console.log(result.entities);
23 Query Operators
The following table outlines the available query operators that you can use to build complex Boolean expressions in your queries.
| Operator | Syntax | Description |
|---|---|---|
| AND | .and() | Combine two conditions with a logical AND. |
| OR | .or() | Combine conditions with a logical OR. |
| EQUALS | eq(value) | Checks if a property equals a value. |
| NOT EQUALS | neq(value) | Checks if a property does not equal a value. |
| GREATER THAN | gt(value) | Checks if a property's value is greater than the specified value. |
| LESS THAN | lt(value) | Checks if a property's value is less than the specified value. |
| GREATER THAN OR EQUAL TO | gte(value) | Checks if a property's value is greater or equal to the specified value. |
| LESS THAN OR EQUAL TO | lte(value) | Checks if a property's value is less or equal to the specified value. |
| IN | inOp([...values]) | Checks if a property is within a list of values. |
| NOT IN | notIn([...values]) | Checks if a property is not within a list of values. |
| LIKE | like(pattern) | Searches for a specific pattern in a property (SQL-like). |
| NOT LIKE | notLike(pattern) | Searches for properties that do not match a specific pattern. |
| CONTAINS | contains(value) | Checks if a property's value contains the specified value. |
| NOT CONTAINS | notContains(value) | Checks if a property's value does not contain the specified value. |
| STARTS WITH | startsWith(value) | Checks if a property's value starts with the specified value. |
| NOT STARTS WITH | notStartsWith(value) | Checks if a property's value does not start with the specified value. |
| IS NULL | isNull() | Checks if a property is null. |
| NOT NULL | notNull() | Checks if a property is not null. |
Query Functions
Functions can be used within your queries to perform aggregations and transformations. The following table outlines some available functions:
| Function | Syntax | Description |
|---|---|---|
| COUNT() | count("propertyName") | Gets the number of values matching the criteria. |
| MIN() | min("propertyName") | Gets the minimum value of the specified property. |
| MAX() | max("propertyNam") | Gets the maximum value of the specified property. |
| AVG() | avg("propertyName") | Gets the average value of the specified property. |
| SUM() | sum("propertyName") | Gets the sum of the specified property. |
| REPLACE() | replace("propertyName", "pattern", "replacement") | Performs a string replace on the specified property. |
| SUBSTRING() | substring("propertyName", start, length) | Extracts a substring from the specified property. |
Example Usage
Here's an example of using functions in a query:
1
2// Using aggregator functions like max():
3const aggResult = await db.select(max('rushingYards'), 'player.firstName', 'player.lastName')
4 .from('Stats')
5 .where(neq('rushingYards', 0))
6 .limit(1)
7 .list();
8
9const firstItem = aggResult.entities[0];
10console.log(firstItem);
11 Query Clauses
Query clauses help you define how the results of your query are presented.
| Clause | Syntax | Description |
|---|---|---|
| LIMIT | .limit(number) | Specifies the maximum number of rows to return. |
| GROUP BY | .groupBy("propertyName") | Groups the results by the specified property. |
| ORDER BY | .orderBy([asc("field") | desc("field")]) | Sorts the results by a an array of properties in ascending or descending order. |
| WHERE | .where(condition) | Filters the results based on the provided criteria. |
Example Usage
Here's an example of using an orderBy clause in a query:
1
2// Ordering results:
3const qbs = await db.from('Player')
4 .where(eq('position', 'QB'))
5 .orderBy(desc('firstName')) // descending order
6 .list();
7
8console.log(qbs.entities);
9 Retrieval and Manipulation Methods
After constructing your query, you can execute it using one of the following methods:
| Method | Syntax | Description |
|---|---|---|
| list | .list() | Executes the query and returns a paginated list of matching entities. |
| count | .count() | Returns the count of matching entities without fetching them. |
| update | .update() | Updates the matching entities (after setting .setUpdates({...})). |
| delete | .delete() | Deletes the matching entities. |
| stream | .stream() | Streams matching entities and their changes in real-time. |
| onItemAdded | .onItemAdded(callback) | Sets a callback for when an item is added. |
| onItemUpdated | .onItemUpdated(callback) | Sets a callback for when an item is updated. |
| onItemDeleted | .onItemDeleted(callback) | Sets a callback for when an item is deleted. |
Grouping and Distinct
Use groupBy() and distinct() to aggregate and remove duplicates:
1
2// Using groupBy and distinct:
3const groupedStats = await db.select(count('player.playerId'), 'rushingYards')
4 .from('Stats')
5 .where(neq('rushingYards', 0))
6 .groupBy('rushingYards')
7 .distinct()
8 .list();
9
10console.log(groupedStats.entities);
11 Complex WHERE Criteria
Combine multiple conditions using logical operators:
1
2// Complex where criteria example:
3const complexResult = await db.from('MyTable')
4 .where(
5 eq('myProperty', 3)
6 .and(
7 startsWith('otherProperty', 'a')
8 .or(lt('thirdProperty', 0))
9 )
10 )
11 .list();
12
13console.log(complexResult.entities);
14 Listening for Changes (Streams)
You can listen for real-time updates to your query results using streaming:
1
2// Listening for changes in real-time:
3const streamHandle = await db.from('Player')
4 .onItemUpdated((player) => {
5 console.log('Player updated ' + player.firstName + ' ' + player.lastName);
6 })
7 .stream();
8
9// Fetch Derek Carr
10const derekCarrResult = await db.from('Player')
11 .where(eq('firstName', 'Derek'))
12 .and(eq('lastName', 'Carr'))
13 .limit(1)
14 .list();
15
16const derekCarr = derekCarrResult.entities[0];
17derekCarr.active = false;
18await db.save('Player', derekCarr);
19
20// Stop listening
21streamHandle.cancel();
22 Important Notes
- The property names for the criteria are string values.
- The
inOpandnotInmethods require array values. - Ensure that your entities are properly annotated according to the Onyx Database specifications.
Next Steps
Now that you have learned how to use the JavaScript Query Builder DSL API, you can explore more advanced topics: