Actions Guide

Actions let you run server-side SQL or multi-step logic through a single API call. You define them in your teenybase.ts config, and they become available at POST /api/v1/action/{name}.

You choose one of two modes per action:

SQL mode -- raw query objects with parameterized SQL. Full control, bypasses table rules.
Steps mode -- expression-based statements that go through the table layer. Can apply RLS rules.

Quick Example

typescript

// teenybase.ts
import { DatabaseSettings, sql, sqlValue } from 'teenybase'

export default {
    // ... tables, jwtSecret, etc.
    actions: [
        {
            name: 'increment_counter',
            params: { amount: 'number' },
            requireAuth: true,
            sql: {
                type: 'UPDATE',
                table: 'counters',
                set: { value: 'value + {:amount}' },
                where: sql`id = 1`,
                returning: ['*'],
            },
        },
    ],
} satisfies DatabaseSettings

Call it:

bash

curl -X POST http://localhost:8787/api/v1/action/increment_counter \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer YOUR_TOKEN' \
  -d '{ "amount": 5 }'

Response:

json

[[{ "id": 1, "value": 15 }]]

SQL Mode

SQL mode gives you raw query objects with full control over the SQL. Queries bypass table rules entirely -- use guard or requireAuth for access control.

Every query requires type and table. The other properties depend on the type:

Type	Required	Optional
`SELECT`		`where`, `selects`, `orderBy`, `limit`, `offset`, `groupBy`, `distinct`
`UPDATE`	`set`	`where`, `returning`, `or`
`INSERT`	`values`	`returning`, `or`
`DELETE`		`where`, `returning`

Query property reference

Property	Used in	Type	Description
`where`	SELECT, UPDATE, DELETE	sql`...`	Filter condition. Optional for all types in SQL mode.
`set`	UPDATE	`Record<string, sql\|string>`	Fields to update. Values can be `sql` tagged templates or strings. Required for UPDATE.
`values`	INSERT	`Record<string, sql\|string>`	Fields to insert. Values can be `sql` tagged templates or strings. Required for INSERT.
`selects`	SELECT	`(string \| {q, as})[]`	Fields to return. Strings for column names, `{q: 'COUNT(*)', as: 'count'}` for expressions. Default: all fields.
`returning`	UPDATE, INSERT, DELETE	`(string \| {q, as})[]`	Fields to return after mutation. `['*']` for all fields. Sensitive fields (`noSelect: true`) are excluded for non-admin users.
`orderBy`	SELECT	`string \| string[]`	Sort order. e.g. `'created DESC'`.
`limit`	SELECT	`number`	Max rows to return.
`offset`	SELECT	`number`	Rows to skip (for pagination).
`groupBy`	SELECT	`string[]`	Group results by fields or expressions. e.g. `["strftime('%Y-%m', created)"]`.
`distinct`	SELECT	`boolean`	Deduplicate results.
`or`	UPDATE, INSERT	`string`	Conflict strategy: `ABORT`, `FAIL`, `IGNORE`, `REPLACE`, `ROLLBACK`.

Steps mode uses different property names: select (not selects), order (not orderBy), group (not groupBy), and setValues (not set).

Single Query

typescript

{
    name: 'get_active_users',
    params: { role: 'string' },
    sql: {
        type: 'SELECT',
        table: 'users',
        where: sql`role = {:role} AND email_verified = true`,
    },
}

Multiple Queries (Transaction)

Pass an array -- all queries execute in a single D1 batch (transaction).

typescript

{
    name: 'transfer_credits',
    params: { from_id: 'string', to_id: 'string', amount: 'integer' },
    requireAuth: true,
    sql: [
        {
            type: 'UPDATE',
            table: 'accounts',
            set: { credits: 'credits - {:amount}' },
            where: sql`id = {:from_id} AND credits >= {:amount}`,
        },
        {
            type: 'UPDATE',
            table: 'accounts',
            set: { credits: 'credits + {:amount}' },
            where: sql`id = {:to_id}`,
        },
    ],
}

Query Object Properties

Each query object has a type that determines the available properties:

SELECT

typescript

{
    type: 'SELECT',
    table: 'posts',
    where: sql`published = true AND category = {:category}`,
    // Optional:
    selects: ['id', 'title', 'created'],  // Fields to return (default: all)
    limit: 10,
    offset: 0,
    orderBy: 'created DESC',
    distinct: true,
    groupBy: ['category'],
}

UPDATE

typescript

{
    type: 'UPDATE',
    table: 'posts',
    set: { view_count: sql`view_count + 1` },       // SQL expressions (values must be sql`` or sqlValue())
    where: sql`id = {:post_id}`,
    returning: ['*'],                               // Return updated rows
    // Optional:
    or: 'IGNORE',                                   // Conflict strategy
}

INSERT

typescript

{
    type: 'INSERT',
    table: 'audit_log',
    values: { user_id: sql`{:user_id}`, action: sql`{:action}`, timestamp: sql`CURRENT_TIMESTAMP` },
    returning: ['id'],
    // Optional:
    or: 'IGNORE',                                   // Conflict strategy: ABORT|FAIL|IGNORE|REPLACE|ROLLBACK
}

DELETE

typescript

{
    type: 'DELETE',
    table: 'sessions',
    where: sql`user_id = {:user_id} AND expired = true`,
    returning: ['id'],
}

Parameter Substitution

Use {:paramName} syntax inside sql tagged templates. Parameters from the request body are merged into the query automatically.

typescript

where: sql`email = {:email} AND id = {:id}`

This generates parameterized SQL (email = ? AND id = ?) -- safe from SQL injection.

In set expressions (for UPDATE), parameters are also available:

typescript

set: { credits: 'credits + {:amount}' }

Steps Mode

Steps mode uses expression-based statements that go through the table layer. This means:

Table rules (RLS) can apply (controlled by applyTableRules)
Expressions use the same syntax as rule expressions
Auth context (auth.*) and params (params.*) are available in expressions

Single Step

typescript

{
    name: 'mark_verified',
    requireAuth: true,
    params: { email: 'string' },
    steps: {
        type: 'UPDATE',
        table: 'users',
        setValues: { email_verified: true },
        where: "email = params.email & email_verified = false & id = auth.uid",
    },
}

Multiple Steps

Pass an array -- steps execute sequentially in a single transaction.

typescript

{
    name: 'archive_and_notify',
    requireAuth: true,
    params: { post_id: 'string' },
    steps: [
        {
            type: 'UPDATE',
            table: 'posts',
            setValues: { status: 'archived' },
            where: "id = params.post_id & author_id = auth.uid",
        },
        {
            type: 'INSERT',
            table: 'notifications',
            expr: { user_id: 'auth.uid', message: "'Post archived'" },
        },
    ],
}

Expression Syntax in Steps

Steps use the same expression language as table rules:

Variable	Description
`auth.uid`	Authenticated user's ID
`auth.*`	Any field from the auth user's record (e.g., `auth.role`)
`params.*`	Action parameters (e.g., `params.email`)
Column names	Columns from the target table

Operators: ==, !=, >, <, >=, <=, ~ (LIKE), & (AND), | (OR)

applyTableRules

Controls whether table-level RLS rules apply to steps:

typescript

{
    name: 'admin_reset',
    applyTableRules: false,       // Bypass table rules (default: true)
    guard: "auth.role == 'admin'",  // Use guard for access control instead
    steps: { /* ... */ },
}

Value	Behavior
`true` (default)	Table rules from the `rules` extension apply. Steps respect RLS.
`false`	Rules are skipped. The action has unrestricted access. Use `guard` or `requireAuth` for access control.

Parameters

Define typed parameters that are validated before the action executes.

Shorthand

typescript

params: {
    email: 'string',
    amount: 'number',
    active: 'boolean',
    page: 'integer',
}

Full Definition

typescript

params: {
    email: { type: 'string', description: 'User email address' },
    page: { type: 'integer', optional: true, default: 1 },
    limit: { type: 'integer', optional: true, default: 10, description: 'Max results' },
}

Parameter Types

Type	Description	Example
`'string'`	Any text value	`"alice@example.com"`
`'number'`	Floating-point or integer	`3.14`
`'integer'`	Integer only	`42`
`'boolean'`	`true` or `false`	`true`

Param Options

Property	Type	Default	Description
`type`	`string`	--	Parameter type (required).
`optional`	`boolean`	`false`	Allow the parameter to be omitted.
`default`	`any`	--	Default value when omitted. Only used if `optional`.
`description`	`string`	--	Description for OpenAPI docs. Max 500 characters.

Validation

Parameters are validated using Zod schemas built from your definition. Strict mode is enabled -- extra parameters not defined in params are rejected.

Error response (400):

json

{
    "code": 400,
    "message": "Validation Error",
    "data": {
        "email": { "_errors": ["Required"] },
        "amount": { "_errors": ["Expected number, received string"] }
    }
}

Access Control

Actions have two layers of access control: requireAuth and guard.

requireAuth

The simplest check -- does the request have a valid JWT token?

typescript

{
    name: 'my_action',
    requireAuth: true,        // 401 if not authenticated
    // ...
}

Checked before the guard and before any SQL execution.

Error response (401):

json

{ "code": 401, "message": "Authentication required" }

Guard Expressions

Guards are expressions evaluated before the action executes. If the expression evaluates to false, the request is rejected with 403.

typescript

{
    name: 'admin_action',
    guard: "auth.uid != null",    // Must be logged in
    // ...
}

{
    name: 'admin_only',
    guard: "auth.role == 'admin'",  // Must be admin
    // ...
}

Available variables in guards:

auth.* -- authenticated user context (uid, email, role, etc.)
params.* -- action parameters
SQLite functions like unixepoch() for time-based guards

How guards work internally:

If the expression fully resolves to a literal (e.g., auth.uid != null where auth.uid is known), it's evaluated immediately in JavaScript.
If it uses SQLite functions, it's wrapped as a SQL statement and executed in the same transaction as the action queries. If it fails, the entire transaction is rolled back.

Error response (403):

json

{ "code": 403, "message": "Forbidden" }

Combining Both

typescript

{
    name: 'sensitive_action',
    requireAuth: true,                    // First: must be authenticated
    guard: "auth.role == 'admin'",        // Then: must be admin
    params: { target_id: 'string' },
    sql: { /* ... */ },
}

Evaluation order: requireAuth -> guard -> parameter validation -> SQL execution.

Sensitive Field Filtering

When you use returning: ['*'] in SQL mode, sensitive fields (those with noSelect: true in the field definition) are automatically excluded from the response for non-admin users.

typescript

// Field definition
{ name: 'password', type: 'text', sqlType: 'text', noSelect: true }

// Action with returning: ['*']
{
    name: 'update_user',
    sql: {
        type: 'UPDATE',
        table: 'users',
        set: { email_verified: sqlValue(true) },
        where: sql`id = {:id}`,
        returning: ['*'],              // password will be excluded
    },
}

The response includes all fields except password, password_salt, and any other noSelect fields. Superadmin users bypass this filtering and see all fields.

API Reference

Endpoint

POST /api/v1/action/{name}

Request

Headers:

Content-Type: application/json
Authorization: Bearer {token} (if requireAuth: true or guard uses auth context)

Body: JSON object with parameter values matching the action's params definition.

json

{ "email": "alice@example.com", "amount": 5 }

Response

Success (200): Array of arrays -- each element corresponds to one query's results.

json

// Single query
[[{ "id": "abc123", "email_verified": true }]]

// Multiple queries
[
    [{ "id": "abc123", "credits": 95 }],
    [{ "id": "def456", "credits": 105 }]
]

Errors:

Status	Condition	Body
400	Invalid parameters or request body	`{ "code": 400, "message": "...", "data": {...} }`
401	`requireAuth: true` but no valid token	`{ "code": 401, "message": "Authentication required" }`
403	Guard expression failed	`{ "code": 403, "message": "Forbidden" }`
404	Action name not found	`{ "code": 404, "message": "Action not found - {name}" }`
500	SQL execution error or misconfiguration	`{ "code": 500, "message": "..." }`

Calling from Worker Code

Use db.runAction() to call actions from your worker code (SSR routes, custom middleware, etc.) without an HTTP round-trip:

typescript

// In an SSR route handler or custom middleware
const db = c.get('$db')
const results = await db.runAction('increment_counter', { amount: 1 })

// Fire-and-forget (non-critical operations like view counting)
c.executionCtx.waitUntil(
    db.runAction('increment_view', { slug }).catch(() => {})
)

runAction executes the action directly -- same validation, guards, and SQL execution as the HTTP endpoint.

Examples

Read-only query with params

Fetch posts by category with pagination.

typescript

{
    name: 'posts_by_category',
    params: {
        category: 'string',
        page: { type: 'integer', optional: true, default: 1 },
        limit: { type: 'integer', optional: true, default: 20 },
    },
    sql: {
        type: 'SELECT',
        table: 'posts',
        where: sql`category = {:category} AND published = true`,
        orderBy: 'created DESC',
        limit: 20,     // Note: dynamic limit from params requires steps mode
        offset: 0,
    },
}

bash

curl -X POST http://localhost:8787/api/v1/action/posts_by_category \
  -H 'Content-Type: application/json' \
  -d '{ "category": "tech" }'

Mutation with auth check

Mark a user's email as verified (admin-only).

typescript

{
    name: 'admin_verify_email',
    requireAuth: true,
    guard: "auth.role == 'admin'",
    params: { user_id: 'string' },
    sql: {
        type: 'UPDATE',
        table: 'users',
        set: { email_verified: sqlValue(true) },
        where: sql`id = {:user_id} AND email_verified = false`,
        returning: ['id', 'email', 'email_verified'],
    },
}

Multi-step workflow

Soft-delete a post and log the action.

typescript

{
    name: 'soft_delete_post',
    requireAuth: true,
    params: { post_id: 'string' },
    applyTableRules: false,
    guard: "auth.uid != null",
    steps: [
        {
            type: 'UPDATE',
            table: 'posts',
            setValues: { deleted: true },
            where: "id = params.post_id & author_id = auth.uid",
        },
        {
            type: 'INSERT',
            table: 'audit_log',
            expr: {
                user_id: 'auth.uid',
                action: "'delete_post'",
                target_id: 'params.post_id',
            },
        },
    ],
}

Aggregation / reporting query

Get user registration stats.

typescript

{
    name: 'registration_stats',
    requireAuth: true,
    guard: "auth.role == 'admin'",
    sql: {
        type: 'SELECT',
        table: 'users',
        selects: [
            { q: "strftime('%Y-%m', created)", as: 'month' },
            { q: 'COUNT(*)', as: 'count' },
        ],
        groupBy: ["strftime('%Y-%m', created)"],
        orderBy: 'month DESC',
        limit: 12,
    },
}

json

[[
    { "month": "2025-03", "count": 142 },
    { "month": "2025-02", "count": 98 },
    { "month": "2025-01", "count": 67 }
]]

Actions Guide ​

Quick Example ​

SQL Mode ​

Query property reference ​

Single Query ​

Multiple Queries (Transaction) ​

Query Object Properties ​

SELECT ​

UPDATE ​

INSERT ​

DELETE ​

Parameter Substitution ​

Steps Mode ​

Single Step ​

Multiple Steps ​

Expression Syntax in Steps ​

applyTableRules ​

Parameters ​

Shorthand ​

Full Definition ​

Parameter Types ​

Param Options ​

Validation ​

Access Control ​

requireAuth ​

Guard Expressions ​

Combining Both ​

Sensitive Field Filtering ​

API Reference ​

Endpoint ​

Request ​

Response ​

Calling from Worker Code ​

Examples ​

Read-only query with params ​

Mutation with auth check ​

Multi-step workflow ​

Aggregation / reporting query ​

Actions Guide

Quick Example

SQL Mode

Query property reference

Single Query

Multiple Queries (Transaction)

Query Object Properties

SELECT

UPDATE

INSERT

DELETE

Parameter Substitution

Steps Mode

Single Step

Multiple Steps

Expression Syntax in Steps

applyTableRules

Parameters

Shorthand

Full Definition

Parameter Types

Param Options

Validation

Access Control

requireAuth

Guard Expressions

Combining Both

Sensitive Field Filtering

API Reference

Endpoint

Request

Response

Calling from Worker Code

Examples

Read-only query with params

Mutation with auth check

Multi-step workflow

Aggregation / reporting query