execute-sql

This action allows the app to execute a SQL statement during local SQLite execution, which is used to edit either local tables or Dynamic Data tables. It's useful for running custom queries or updates in response to user interactions or app events. To improve performance, consider creating indexes on frequently queried columns—this enables SQLite to locate rows more efficiently using optimized lookup structures.

Configuration options

Core structure
entities	The entities/tables affected by the statements. Before executing the statements, a check ensures that the tables exist. After execution any datasources that use these entities will be notified that the database was changed.
statements	List of statements to execute in sequence. Multiple statements can be configured to execute in sequence. statement - the SQL statement to execute against the solution database. parameters - The parameters used in the above statement.
title	Provide the action button with a title, for example, Delete record.

Other options
icon	Select an to display when the action is configured as the secondary button or in a header action.
isHidden	true hides the action button, false shows the action button. Default setting is false.
style	isDanger - Styles the action button in red or your brand's designated danger color. isDisabled - Displays the action button as greyed out. isPrimary - Styles the action button in blue or your brand's designated primary color. isSecondary - Sets the action as a secondary button, accessible via the ellipsis. The icon property can be used when the action button is displayed as a secondary button.

Considerations

• Validation currently only applies to SELECT statements and does not apply to INSERT, UPDATE, or DELETE operations, due to limitations in the SQLite parser.

• Creating indexes should occur before actual queries are run against the tables. Creating the index in the onLoad event in the index.jigx file ensures the app loads with the exact data.

Examples and code snippets

Execute SQL statements to update & delete

This example shows a list with an onPress event that executes a SQL statement to update the Tags table in the local database. A swipeable event is configured to delete the list item record from the local database.

create-tag.jigx

title: Create Tag
type: jig.default
icon: checklist

actions:
  - children:
      - type: action.action-list
        options:
          title: Create Tag
          isSequential: true
          actions:
            # Create the tags record in the local SQLite table,
            # by writing a SQL statement to insert the record,
            # and parameters defining the record's data.
            - type: action.execute-sql
              options:
                statements:
                  - statement: |
                      INSERT INTO tags (id, data)
                      VALUES (@id, @data)
                    parameters:
                      data: |
                        ={
                          "name": @ctx.components.name.state.value,
                          "description": @ctx.components.description.state.value,
                          "count": 0
                        }
                      id: [email protected]
                entities:
                  - tags
            - type: action.go-back
# Configure a form to capture the tag details.
children:
  - type: component.form
    instanceId: tag-form
    options:
      isDiscardChangesAlertEnabled: false
      children:
        - type: component.text-field
          instanceId: id
          options:
            label: Id
            style:
              isDisabled: true
            value: "=$base64encode(@ctx.components.name.state.value ?
              @ctx.components.name.state.value : '')"
        - type: component.text-field
          instanceId: name
          options:
            label: Name
        - type: component.text-field
          instanceId: description
          options:
            label: Description

list-tag.jigx

tags (datasource)

Execute SQL statements to create indexes

This example demonstrates how to use the execute-sql action to run the CREATE statements. It also shows how to incorporate indexes to optimize query performance. Using SQL statements in this way allows you to define and manage table structures, remove data, and improve efficiency through indexing—all directly from your Jigx solution. Creating indexes should occur before actual queries are run against the tables. In this example we create the index on the assignedTo column in the onLoad event in the index.jigx file, ensuring that only the tasks for the logged on user are displayed.

index.jigx

name: jigx-demo-2025
title: jigx-demo-2025
category: analytics

# When the app loads, return only the tasks assigned to the logged-in user.
# Execute a SQLite statement to create an index on the assignedTo column
# in the task table.
onLoad:
  type: action.execute-sql
  options:
    # Specify the table where the index is required.
    entities:
      - default/tasks
    statements:
      # Create the index that returns the tasks for the assignedTo user.
      - statement: |
          CREATE INDEX IF NOT EXISTS idx_task__assignedTo
          ON [default/tasks] (JSON_EXTRACT(data, '$.assignedTo'))
tabs:
  home:
    label: Home label
    jigId: create-task-dd
    icon: home-apps-logo

create-task-dd.jigx

list-tasks-dd.jigx

edit-task-dd.jigx

title: Edit Task
type: jig.default
icon: checklist
# Use inputs to pass the task record as an object from the list.
# Allowing the form to be pre-filled with all the task details for editing.
inputs:
  task:
    type: object
    required: true
# Use update to save the changes made in the edit form.
actions:
  - children:
      - type: action.execute-entity
        options:
          title: Update
          provider: DATA_PROVIDER_DYNAMIC
          entity: default/tasks
          method: update
          goBack: previous
          data:
            id: [email protected]
            description: [email protected]
            assignedTo: [email protected]
            name: [email protected]
# Use the defined jig inputs to populate the form fields,
# with the current records details.
children:
  - type: component.form
    instanceId: task-form
    options:
      isDiscardChangesAlertEnabled: false
      children:
        - type: component.text-field
          instanceId: name
          options:
            initialValue: [email protected]
            label: Name
        - type: component.text-field
          instanceId: description
          options:
            initialValue: [email protected]
            label: Description
        - type: component.text-field
          instanceId: assignedTo
          options:
            initialValue: [email protected]
            label: assignedTo
            style:
              isDisabled: true

task-dd.jigx