Database Operations

Perform insert, update, and delete operations on the Momen built-in database.

For more on system tables and fields, see System Tables and Fields.

Add

Insert one or more new records into the specified table.

Common Use Cases

• Save form content to the database after the user submits a form.
• Batch-import multiple records into a table.

Parameter Configuration

Action Parameters

Additional Configuration

1. Field validation

   Validates field data before insert. Multiple rules can be added per field; all rules must pass before the request is sent. If validation fails, no request is made, the Action enters the On failure branch, and the configured error message is shown.

   To configure: click Field validation next to Parameters, add the fields to validate in the dialog, and set validation rules and messages for each field.

2. On conflict

   When the table has a unique constraint and inserted data duplicates existing records, configure conflict handling to control what happens next.

   To configure: select the unique constraint under On conflict, then choose Conflict handling:

   • Ignore and skip: Do not insert the conflicting row; other rows are inserted normally.
   • Update existing record: Overwrite the conflicting row with the new data.

Results and Output

• Action result: For a single insert, returns the inserted record. For batch insert, returns the first element of the inserted array (usually the row with the smallest id).
• On success: Fires after data is successfully inserted. For batch insert, fires once after all rows are inserted.
• On failure: Fires when the insert is blocked or an error occurs.

Execution Mechanism

The Add Action creates new database records. The flow spans frontend pre-validation, data preprocessing, encrypted transport, backend row-level permission checks, and physical transaction commit:

1. Frontend collects parameters:

   • Single insert: When the user triggers the Action, the page engine collects values from input components (text fields, dropdowns, etc.) and assembles a single data object.
   • Batch insert: Reads the bound array data source, iterates members, and maps each element’s fields to table columns.

2. Local validation: Checks configured field validation rules. If any field fails, the frontend stops immediately, marks the Action as failed, and routes to On failure without sending a network request.

3. Parameter preprocessing: Converts media-type field bindings to media file ids and rewrites parameter keys.

4. Assemble GraphQL payload: After preprocessing, a single insert becomes an insert_xxx_one GraphQL tree; batch insert becomes an insert_xxx array. See Runtime API Reference — Database operations.

5. Gateway authentication: The request is sent over HTTPS. The gateway parses the JWT session token in the request header, validates the current user, and passes trusted user attributes to permission checks.

6. Unified transaction wrapping:

   • Single insert: Backend runs a physical INSERT INTO inside one database transaction.
   • Batch insert: Single frontend request + backend transaction-wrapped loop. Backend opens one PostgreSQL transaction, generates and executes independent INSERT INTO statements per array element in order, and handles table-level cascades so rows succeed or fail together.
   ⚠️

   Because batch insert loops over each array member with separate SQL and transaction boundaries, very large batches risk long transactions, table locks, and connection pool exhaustion. The frontend limits each batch insert to 1000 records. Excess rows are discarded; only the first 1000 are sent.

7. Permission and conflict checks:

   • Before write, the database checks table-level and row-level permissions.
   • On conflict: If inserted data conflicts on a unique field and handling is Ignore and skip, the conflicting row is silently skipped and other rows continue. If handling is Update existing record, an upsert overwrites the existing row.

8. Commit and rollback:

   • All succeed: When all inserts complete successfully, the backend commits and returns output. The page engine triggers On success.
   • Any failure: Errors (unhandled unique conflict, failed validation, missing required fields, invalid foreign key id, etc.) roll back the entire transaction. The frontend routes to On failure.

Common Errors

The following errors route to On failure:

1. Validation failed: Local field rules not satisfied.
2. Data conflict (unique constraint): Duplicate data with no conflict handling configured.
3. Insufficient permission: The user’s role lacks insert permission or submitted data violates permission rules.
4. Database constraint error: Missing required fields, or a foreign key id does not exist.
5. Network or service error: Offline device, timeout, or server error (500).

Usage Examples

Update

Update one or more existing records in the specified table that match filter criteria.

Common Use Cases

• User updates profile in account settings.
• Change order status (e.g., from pending payment to paid).

Parameter Configuration

Results and Output

• Result: Multi-row updates return only the first record. To reference later, bind Action result → [current Action name] → id.
• On success: Fires after a successful update; you can refresh data sources or show a toast.
• On failure: Fires on failure (e.g., validation failed, no matching rows).

Execution Mechanism

The Update Action updates existing rows matching filters:

1. Frontend collects filters and parameters:

   • Data filter: With multiple filters, the engine evaluates top to bottom:
     • First match wins: When a filter condition is true, its parameters are used and later filters are ignored. If none match, the Default filter is used.
     • Final assembly: Only one filter is compiled into the GraphQL request.

2. Field validation: If updated values fail parameter validation, the frontend stops and routes to On failure.

3. Parameter preprocessing: Media bindings become media center ids; keys are rewritten to [field_name]_id for bigint foreign keys.

4. Assemble GraphQL payload: Filters become GraphQL where; set/increment values go in _set or _inc. See Runtime API Reference — Update data.

5. Gateway authentication: HTTPS request; JWT parsed; trusted session context passed to backend.

6. Unified transaction: GraphQL becomes atomic PostgreSQL update SQL in one transaction. For stock decrement, SQL resembles:

   UPDATE "goods" SET "stock" = "stock" + $1 WHERE "id" = $2 AND "stock" >= $3;

   Serial execution inside the transaction prevents concurrency and dirty-read issues.

7. Permission and conflict checks:

   • Permission rules are enforced before write. Even if a client strips frontend filters (e.g., stock >= quantity), backend SQL still applies permission limits (e.g., AND "stock" >= 0).
   • Data conflict: If updated values violate a unique constraint, the operation fails with an error.

8. Commit and rollback:

   • Success: Rows changed, transaction committed, On success.
   • Failure: Permission block or conflict; transaction rolled back, On failure.

Common Errors

• Validation failed: Updated values do not satisfy parameter validation.
• Filter error: Filter misconfigured — no rows matched or wrong rows updated.

Usage Examples

Delete

Remove one or more records from the specified table that match filter criteria.

Common Use Cases

• Remove an item from the shopping cart list.
• Admin deletes a violating comment in the admin panel.

Parameter Configuration

Results and Output

• Result: Multi-row deletes return only the first record. Bind Action result → [current Action name] → id if needed.
• On success: Fires after successful delete.
• On failure: Fires on failure (e.g., no delete permission).

Execution Mechanism

The Delete Action permanently removes matching rows:

1. Frontend collects filters:

   • Multiple Data filter branches are evaluated top to bottom; first match wins; otherwise Default.
   • One filter is compiled into the GraphQL request.

2. Field validation: None (no write parameters).

3. Parameter preprocessing: None.

4. Assemble GraphQL payload: Standard delete_xxx with where. See Runtime API Reference — Delete data.

5. Gateway authentication: JWT establishes trusted session context.

6. Unified transaction: Backend injects RLS before compiling SQL.

7. RLS and physical constraints:

   • where is translated to SQL, e.g.:

     DELETE FROM "order" WHERE "id" = $1 AND "user_id" = $2;

   • Referencing fields in related tables are typically set to NULL on delete.

8. Commit and rollback:

   • Success: Delete committed, On success, refresh data sources.
   • Failure: Permission denied; rollback, On failure.

Common Errors

• Missing filter: No filter configured — risk of deleting entire table.
• Relation constraint: Row referenced by foreign keys elsewhere; database rejects delete.

Usage Examples

Transaction

Bundle multiple database mutations (add, update, delete) in order with atomic semantics — all succeed or all are rolled back.

Common Use Cases

• Submit a complex form with parent and child tables (e.g., order + order lines).
• Transfer funds: debit account A and credit account B together.

Parameter Configuration

Results and Output

• Result: None
• On success: All Actions in the transaction succeeded.
• On failure: Any Action failed; all changes are rolled back.

Execution Mechanism

The Transaction Action runs multiple writes atomically in one request:

1. Frontend chained collection:

   • Collects parameters and filters for each nested Action in order.
   • Child callbacks hidden: Per-Action success/failure branches are disabled for atomicity. Only the transaction-level On success / On failure are exposed. Nested Actions cannot reference a prior step’s result (e.g., update stock using an order id from a previous add).

2. Field validation: Each nested Action is validated locally; any failure fails the whole transaction.

3. Parameter preprocessing: Media fields converted to ids; keys rewritten to [field_name]_id.

4. Assemble GraphQL payload: All nested mutations in one request. See Runtime API Reference — Transactions.

5. Gateway authentication: JWT builds trusted session context.

6. Unified transaction: Backend opens a PostgreSQL transaction; nested operations run in configured order.

7. RLS and constraints: Any SQL error triggers rollback.

8. Commit and rollback:

   • Success: All steps succeed → commit → On success.
   • Failure: Any step fails → rollback → On failure.

Common Errors

• Unsupported Action: Non-database Actions (navigation, API call, etc.) in the list invalidate configuration.
• Single step failure: One update matches no rows or fails validation → entire transaction rolls back.

Usage Examples