Methods
Comprehensive query operations for retrieving data from your database with support for PostgreSQL and SQLite
The Drizzle Service provides a comprehensive set of query operations that allow you to retrieve data from your database efficiently. These operations support both PostgreSQL and SQLite databases and include advanced features like filtering, pagination, relations, and soft-delete handling.
Key Features
- Universal Database Support: Works seamlessly with PostgreSQL and SQLite
- Type-Safe Operations: Full TypeScript support with inferred types
- Advanced Filtering: Support for complex filter expressions including ranges, wildcards, and logical operators
- Pagination & Cursor-based Navigation: Built-in pagination with cursor support for efficient data retrieval
- Relation Support: Load related data with configurable depth and options
- Soft Delete Integration: Automatic handling of soft-deleted records with optional inclusion
- Performance Optimized: Efficient query building with minimal overhead
Available Operations
Basic Query Operations
| Method | Description |
|---|---|
findAll() | Retrieve all records with optional filtering and pagination |
findById(id) | Find a single record by its primary key |
findBy(criteria) | Find records matching specific criteria |
findByField(field, value) | Find records by a single field value |
count(criteria?) | Count records matching optional criteria |
Advanced Query Operations
findWithCursor(opts)- Cursor-based pagination for large datasetsfindByMatching(criteria)- Advanced pattern matching with multiple criteriafilter(criteria)- Complex filtering with Business Central style expression-based queries
Query Options
All query operations support a comprehensive set of options for customizing the query behavior:
| Prop | Type | Default |
|---|---|---|
parse? | (data: T[]) => TResult | - |
custom? | SQL | - |
workspace? | { field: keyof T, value: T[keyof T] } | - |
relations? | WithRelations[] | - |
cursor? | Date | null | - |
withDeleted? | boolean | false |
orderBy? | Record<keyof T, "asc" | "desc"> | - |
limit? | number | 100 |
page? | number | 1 |
Filter Expressions
The service supports advanced filter expressions that allow you to create complex queries using a simple, intuitive syntax:
Basic Operators
- Equality:
['=', value]or[value] - Inequality:
['<>', value] - Comparison:
['>', value],['>=', value],['<', value],['<=', value]
Pattern Matching
- Wildcards:
['*pattern*', value]- supports SQL LIKE patterns - Case-insensitive:
['@*pattern*', value]- prefix with @ for case-insensitive matching
Logical Operators
- Range:
['value1..value2', startValue, endValue]- inclusive range queries - IN clauses:
['value1|value2|value3', ...values]- OR conditions - AND conditions:
['condition1&condition2', ...values]- multiple AND conditions
Filter Expression Syntax
Filter expressions follow the pattern: [expression, ...values]
%1,%2, etc. are placeholders for values- Values are provided in order after the expression string
Comparison Operators
| Operator | Description | Example |
|---|---|---|
= or value | Exact match | ['%1', 'John'] |
<> | Not equal | ['<>%1', 'inactive'] |
> | Greater than | ['>%1', 100] |
>= | Greater than or equal | ['>=%1', 18] |
< | Less than | ['<%1', 65] |
<= | Less than or equal | ['<=%1', 1000] |
Range Operator
| Operator | Description | Example |
|---|---|---|
.. | Range (inclusive) | ['%1..%2', 18, 65] |
Logical Operators
| Operator | Description | Example |
|---|---|---|
| | OR condition | ['%1|%2|%3', 'A', 'B', 'C'] |
& | AND condition | ['%1&>%2', 30, 25] |
Wildcard Operators
| Operator | Description | Example |
|---|---|---|
* | Wildcard match | ['*%1*', 'search'] |
@ | Case insensitive | ['@*%1*', 'John'] |