Directus Query Collection Endpoint

Execute SQL queries stored in Directus collections via GET endpoints.

Features

• Execute SQL queries stored in any Directus collection
• Schema-agnostic: works with any collection structure
• Support for query parameters via URL query string
• Directus access policies for permission control
• Status-based publishing control (status = published)
• Automatic primary key detection

Installation

# npm
npm install
npm run build

# pnpm
pnpm install
pnpm run build

# yarn
yarn install
yarn run build

Copy the built extension to Directus:

cp -r dist /path/to/directus/extensions/directus-query-collection-endpoint

API Endpoint

GET /query-collection/execute/:collection/:primaryKey/:sqlField

Execute a SQL query stored in a Directus collection.

Parameters:

• collection: Name of the Directus collection containing the query
• primaryKey: Value of the primary key to find the record
• sqlField: Name of the field containing the SQL query
• Query params: Optional parameters for SQL query substitution

Examples:

Example 1: Simple query

GET /query-collection/execute/queries/123/sql

# Finds record in 'queries' collection with id=123
# Executes SQL from the 'sql' field

Example 2: Query with parameters

GET /query-collection/execute/reports/monthly-sales/query?startDate=2026-01-01&endDate=2026-01-31

# SQL in database uses named parameters:
# SELECT * FROM sales WHERE created_at BETWEEN :startDate AND :endDate

Usage Guide

1. Create a collection for storing queries

In Directus, create a collection (e.g., saved_queries) with fields:

• id (integer, primary key)
• name (string) - descriptive name
• sql (text) - the SQL query
• status (string, optional) - only records with published status are executable

2. Add a query record

{
  "id": 1,
  "name": "Get active users",
  "sql": "SELECT * FROM directus_users WHERE status = :status LIMIT :limit",
  "status": "published"
}

3. Execute the query

curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:8055/query-collection/execute/saved_queries/1/sql?status=active&limit=10"

Response:

{
  "success": true,
  "data": [
    { "id": 1, "email": "[email protected]", "status": "active" },
    { "id": 2, "email": "[email protected]", "status": "active" }
  ],
  "rowCount": 2,
  "executedAt": "2026-02-05T10:30:00.000Z"
}

Security

Multi-level security model

Level 1: Directus access policies (automatic)

• Uses Directus native access policies and permissions
• If user can't read the collection, they can't execute queries
• Enforced automatically via ItemsService with accountability

Level 2: Status check (automatic)

• If the record has a status field, only records with status = "published" can be executed
• Unpublished, draft, or archived queries return a 404 error

Level 3: SQL parameter binding

• All parameters use Knex named parameter binding (:paramName)
• Prevents SQL injection attacks
• Parameters can only replace values, not table/column names

SQL Parameter Syntax

Use named parameters in your SQL queries:

-- Named parameters with colon prefix
SELECT * FROM users
WHERE status = :status
  AND created_at > :startDate
  AND role IN (:role1, :role2)
LIMIT :limit

Call the endpoint with query parameters:

?status=active&startDate=2026-01-01&role1=admin&role2=editor&limit=50

Type Coercion

Query parameters are automatically converted:

• "true" / "false" → boolean
• Numeric strings → numbers
• Everything else → strings

Error Responses

| Status | Description | | ------ | -------------------------------------------------- | | 400 | Bad request (missing parameters) | | 401 | Unauthorized (not authenticated) | | 403 | Forbidden (insufficient permissions) | | 404 | Not found (collection/record/field doesn't exist) | | 500 | Internal server error (SQL execution failed) |

Example error:

{
  "success": false,
  "error": "This query is not published"
}

Development

# Install dependencies
npm install    # or pnpm install / yarn install

# Development mode with watch
npm run dev    # or pnpm run dev / yarn run dev

# Build for production
npm run build  # or pnpm run build / yarn run build

# Link to local Directus instance
npm run link   # or pnpm run link / yarn run link

Testing

1. Create a test collection in Directus:

   • Name: test_queries
   • Fields: id (PK), name (string), sql (text), status (string)

2. Add a test query:

   {
     "id": 1,
     "name": "Test query",
     "sql": "SELECT * FROM directus_users LIMIT :limit",
     "status": "published"
   }

3. Test the endpoint:

   curl -H "Authorization: Bearer YOUR_TOKEN" \
     "http://localhost:8055/query-collection/execute/test_queries/1/sql?limit=5"

Advantages

• Reusability: Store queries once, execute many times
• Version control: Leverage Directus revision system
• RESTful: Simple GET requests with query parameters
• Directus permissions: Uses native access policies
• Publishing workflow: Only published queries are executable
• Schema-agnostic: Works with any collection structure
• Type-safe parameters: Automatic type coercion
• SQL injection protection: Knex named parameter binding

Requirements

• Directus >= 11.0.0
• Node >= 18.x.x
• npm >= 8.0.0 / pnpm >= 8.0.0 / yarn >= 1.22.0

License

MIT