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
2import { onyx } from "@onyx.dev/onyx-database";
3
4// First, create a new OnyxClient instance:
5const db = onyx.init({
6 baseUrl: 'https://api.onyx.dev',
7 databaseId: 'your_database_id',
8 apiKey: 'your_api_key',
9 apiSecret: 'your_api_secret'
10});
11
12// Example Query:
13const result = await db.from('Stats')
14 .where(
15 neq('rushingYards', 0)
16 .and(gt('passingYards', 3))
17 .and(
18 eq('player.position', 'QB')
19 .or(contains('player.firstName', 'a'))
20 )
21 )
22 .list();
23
24// 'result' contains the query response, including result.entities
25console.log(result.entities);
26 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 | within([...values]) | Checks if a property is within a list of values or the results of an inner query. |
| NOT IN | notWithin([...values]) | Checks if a property is not within a list of values or an inner query result set. |
| 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. |
| PERCENTILE() | percentile("propertyName", percentile) | Gets the percentile value for 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.
- Use
withinandnotWithinfor membership checks. They accept arrays or query builders, letting you compose inner queries without extra awaits. - Ensure that your entities are properly annotated according to the Onyx Database specifications.
Inner query with within()
const vipOrders = await db
.from("Order")
.where(
within(
"userId",
db.from("User")
.where(eq("status", "VIP"))
.selectFields("id")
)
)
.list();Next Steps
Now that you have learned how to use the JavaScript Query Builder DSL API, you can explore more advanced topics: