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": "camelCaseName",
  "connector": "stack9_api | external_api",
  "queryTemplate": {
    "method": "get | post | put | delete",
    "path": "/api/endpoint",
    "queryParams": {},
    "bodyParams": "{}",
    "headerParams": {}
  },
  "userParams": {
    // Required parameters
  }
}

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)"
  ]
}

$orderBy - Sort Results​

{
  "$orderBy": [
    {"column": "created_at", "order": "desc"},
    {"column": "name", "order": "asc"}
  ]
}

Parameter Injection​

Use double curly braces to inject user parameters:

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

Real-World Query Example​

From a production Stack9 app:

{
  "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)

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
}

Next Steps​

Now that you understand the Query Library, learn about: