Raw SQL

When MongoDB-style operators are not enough, you can use raw SQL expressions inside operation blocks. This is an escape hatch for complex access control logic.

permissions: {
  view_recent_orders: {
    table: 'main.orders',
    roles: ['analyst'],
    select: {
      columns: '*',
      sql: "created_at > NOW() - INTERVAL '30 days'",
    },
  },
}

Syntax

Use sql as a key inside an operation block with a SQL string value:

select: {
  columns: '*',
  sql: "your_sql_expression",
}

The expression is injected directly into the WHERE clause.

Examples

Date Range

permissions: {
  view_recent_orders: {
    table: 'main.orders',
    roles: ['analyst'],
    select: {
      columns: '*',
      sql: "created_at > NOW() - INTERVAL '90 days'",
    },
  },
}

SELECT * FROM main.orders
WHERE created_at > NOW() - INTERVAL '90 days'

Computed Conditions

select: {
  columns: '*',
  sql: "amount * quantity <= 10000",
}

SELECT * FROM main.orders
WHERE amount * quantity <= 10000

Combining with Where Clauses

Raw SQL can be combined with standard where clauses inside the same operation block:

permissions: {
  view_org_recent_orders: {
    table: 'main.orders',
    roles: ['member'],
    select: {
      columns: '*',
      where: {
        organization_id: { $in: '$user.org_ids' },
        status: { $ne: 'deleted' },
      },
      sql: "created_at > NOW() - INTERVAL '30 days'",
    },
  },
}

SELECT * FROM main.orders
WHERE organization_id IN ('org_1', 'org_2')
  AND status != 'deleted'
  AND created_at > NOW() - INTERVAL '30 days'

Using Session Variables in SQL

Reference session variables with the $user. prefix inside the SQL string:

select: {
  columns: '*',
  sql: "created_by = '$user.id' OR assigned_to = '$user.id'",
}

The engine replaces $user.* tokens with their actual values before executing.

Raw SQL on Update and Delete

Raw SQL also works inside update and delete operation blocks:

permissions: {
  delete_old_drafts: {
    table: 'main.orders',
    roles: ['admin'],
    delete: {
      where: { status: { $eq: 'draft' } },
      sql: "created_at < NOW() - INTERVAL '90 days'",
    },
  },
}

Safety Rules

Raw SQL is powerful but comes with constraints to prevent abuse:

1. Read-only — Only expressions valid in a WHERE clause are allowed. No subqueries with INSERT, UPDATE, DELETE, or DROP.

2. No semicolons — The engine rejects any SQL containing ; to prevent statement injection.

3. No comments — SQL comments (-- and /* */) are stripped.

4. Parameterized session variables — $user.* references are parameterized, not string-interpolated, to prevent SQL injection.

5. Schema-scoped — You can only reference columns from the permission's table and its known relationships.

6. Allowlisted functions — Only safe SQL functions are permitted (e.g., NOW(), COALESCE, LOWER, UPPER, LENGTH). System functions and file access functions are blocked.

When to Use Raw SQL

Prefer standard operators when possible. Use sql only when the built-in operators cannot express your access rule.