Skip to main content

Query Library

The Query Library is Stack9's centralized data access layer. It defines all database queries, API calls, and data transformations your application needs—all in JSON configuration files.

What is the Query Library?​

The Query Library is a collection of named, parameterized queries that:

  • Fetch data from your database or external APIs
  • Transform responses before returning to the UI
  • Handle pagination, filtering, and sorting
  • Support relationships and eager loading
  • Cache results for performance

When you define a query, Stack9 automatically:

  • ✅ Executes the query with proper security
  • ✅ Handles parameter injection
  • ✅ Manages database connections
  • ✅ Implements pagination
  • ✅ Caches results intelligently
  • ✅ Validates inputs

Why Use the Query Library?​

Before Query Library (Traditional Approach)​

// Multiple files, repeated code, hard to maintain
app.get('/api/customers', async (req, res) => {
  const { page, limit, state } = req.query;
  const customers = await db('customers')
    .where({ state, is_active: true })
    .limit(limit)
    .offset(page * limit);
  res.json(customers);
});

With Query Library (Stack9 Approach)​

{
  "key": "getactivecustomerlist",
  "name": "getActiveCustomerList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{ \"$where\": { \"is_active\": true } }"
  }
}

Benefits:

  • ✅ Centralized: All queries in one place
  • ✅ Reusable: Use same query in multiple screens
  • ✅ Type-safe: Automatic TypeScript types
  • ✅ Testable: Easy to mock and test
  • ✅ Maintainable: Change once, update everywhere

Query File Structure​

Queries are defined in src/query-library/{query_name}.json:

{
  "key": "unique_query_key",
  "name": "QueryName",
  "connector": "stack9_api",  // Must match ConnectorType enum exactly
  "queryTemplate": {
    "method": "get | post | put | delete | patch",
    "path": "/api/endpoint",
    "queryParams": {},
    "bodyParams": {},  // Object or string depending on connector
    "pathParams": {}   // For OPEN_API and STACK9_API
  },
  "userParams": {},    // Optional parameters
  "module": "module_name"  // Optional module association
}

Query Template Syntax​

$select - Choose Fields​

{
  "$select": [
    "id",
    "name",
    "email_address",
    "sales_channel.name",  // Related entity field
    {"count": "*", "as": "total"}  // Aggregation
  ]
}

$where - Filter Records​

{
  "$where": {
    "is_active": true,
    "state": "QLD",
    "created_at": { ">": "2024-01-01" }
  }
}

Operators​

OperatorDescriptionExample
=Equals (default)"state": "QLD"
>Greater than"age": { ">": 18 }
>=Greater than or equal"price": { ">=": 100 }
<Less than"stock": { "<": 10 }
<=Less than or equal"discount": { "<=": 50 }
!=Not equal"status": { "!=": "cancelled" }
inIn array"state": { "in": ["NSW", "VIC"] }
not inNot in array"status": { "not in": ["deleted"] }
likePattern match"name": { "like": "%smith%" }
is nullIs null"deleted_at": { "is null": true }
is not nullNot null"email": { "is not null": true }

$withRelated - Include Related Entities​

{
  "$withRelated": [
    "sales_channel",
    "customer_attention_flags(notDeleted)",
    "sales_orders(active)"
  ]
}

Sorting Results with $sort​

Stack9 uses the $sort operator for sorting query results. This operator is used within bodyParams for stack9_api connector queries:

{
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{\n  \"$where\": { \"is_active\": true },\n  \"$sort\": { \"created_at\": \"desc\", \"name\": \"asc\" }\n}"
  }
}

$sort Syntax Options:

FormatDescriptionExample
String values"asc" or "desc"{"name": "asc"}
Numeric values1 (asc) or -1 (desc){"price": -1}
PostgreSQL nulls"descNullsLast", "ascNullsFirst"{"optional_field": "descNullsLast"}

Multiple Sort Fields:

You can sort by multiple fields by providing them in order of priority:

{
  "$sort": {
    "priority": "desc",
    "created_at": "desc",
    "name": "asc"
  }
}

In this example, results are first sorted by priority (descending), then by created_at (descending), and finally by name (ascending) for records with the same priority and created_at values.

Parameter Injection​

Use double curly braces to inject user parameters:

{
  "bodyParams": "{\n  \"$where\": {\n    \"id\": {{id}}\n  }\n}",
  "userParams": {
    "id": ""
  }
}

Real-World Query Examples​

Example 1: Customer Detail Query​

{
  "key": "getcustomer",
  "name": "getCustomer",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{\n  \"$select\": [\n    \"crn\",\n    \"name\",\n    \"last_name\",\n    \"email_address\",\n    \"phone\",\n    \"sales_channel_id\",\n    \"sales_channel.name\",\n    \"customer_attention_flags.attention_flag.flag\"\n  ],\n  \"$where\": {\n    \"id\": {{id}}\n  },\n  \"$withRelated\": [\n    \"sales_channel\",\n    \"customer_attention_flags(notDeleted)\"\n  ]\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

This query:

  • ✅ Selects specific fields
  • ✅ Includes fields from related entities (dot notation)
  • ✅ Filters by customer ID
  • ✅ Eager loads 2 relationships
  • ✅ Filters related records (notDeleted scope)

Example 2: Sorted List Query with $sort​

From a production subscription list query:

{
  "key": "getsubscriptionlist_api",
  "name": "getSubscriptionList - API",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/subscription/search",
    "bodyParams": "{\n  \"$select\": [\n    \"subscription_number\",\n    \"frequency\",\n    \"total_dollar_amount\",\n    \"start_dt\",\n    \"customer_id\",\n    \"customer.crn\",\n    \"customer.name\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false\n  },\n  \"$withRelated\": [\n    \"customer(notDeleted)\"\n  ],\n  \"$sort\": {\n    \"id\": \"desc\"\n  }\n}",
    "queryParams": {
      "page": "{{page}}",
      "limit": "{{limit}}"
    }
  }
}

This query demonstrates:

  • ✅ Using $sort for runtime sorting (newest first by ID)
  • ✅ Pagination with page and limit parameters
  • ✅ Filtering soft-deleted records
  • ✅ Loading related customer data

Using Queries in Screens​

List View with Query​

{
  "head": {
    "title": "Customer List",
    "key": "customer_list",
    "route": "customer-list",
    "app": "crm"
  },
  "screenType": "listView",
  "listQuery": "getactivecustomerlist",
  "columnsConfiguration": [...]
}

Detail View with Query​

{
  "head": {
    "title": "Customer Profile",
    "key": "customer_profile",
    "route": "customer-profile/:id",
    "app": "crm"
  },
  "screenType": "detailView",
  "detailQuery": "getcustomer",
  "entityKey": "customer"
}

Best Practices​

1. Name Queries Descriptively​

{
  "key": "getactivecustomerlist",   // ✅ Clear purpose
  "key": "query1"                   // ❌ Unclear
}

2. Select Only Needed Fields​

{
  "$select": ["id", "name", "email"]  // ✅ Good
  "$select": ["*"]                    // ❌ Bad (over-fetching)
}

3. Use Pagination​

{
  "$limit": 25,
  "$offset": 0
}

4. Index Frequently Queried Fields​

In your entity definition:

{
  "key": "email_address",
  "index": true
}

Complete Schema Reference​

For the complete, detailed schema reference for all connector types and their specific queryTemplate properties, see:

Next Steps​

Now that you understand the Query Library, learn about: