Use database view

Use a database view to expose a controlled SQL surface over an existing SQLite database resource.

For the conceptual model, see Database view.

Create a database view

Create the view using the SaveDataView API.

The following example creates a view that allows the authenticated account to write to the view, but they can only write rows for their own account.

function createReceiptView(
  voltClient: any,
  conversation: any,
  viewId: string
) {
  log("creating receipt view %s for conversation %s", viewId, conversation.id);

  const sql = `insert into read_receipt (account, timestamp) values (:voltAccountDID, :timestamp) on conflict(account) do update set timestamp=excluded.timestamp`;

  const metadata = {
    id: viewId,
    name: `${conversation.name} read receipt view`,
    description: `Write access to read receipts for ${conversation.name}, restricted to the currently authenticated account.`,
    kind: [CONVERSATION_READ_RECEIPT_VIEW_KIND],
  };

  return voltClient
    .SaveDataView({
      resource: metadata,
      create: true,
      create_in_parent_id: conversation.id,
      source_database_id: conversation.id,
      sql,
      allow_sub_select: false,
      parameter: [
        {
          name: "timestamp",
          data_type: "ATTRIBUTE_DATA_TYPE_INTEGER",
        },
      ],
    });
}

Execute a database view

Execute via the SqliteDatabase API, passing the view resource ID as database_id.

The following example writes a timestamp to the view:

function writeToReceiptView(voltClient, viewId, timestamp) {
  return voltClient.SqlExecuteJSON({
    database_id: viewId,
    parameter: {
      timestamp: {
        integer: timestamp,
      },
    },
  });
}

At runtime, Volt resolves the source database from the view attributes, binds the provided parameters, and executes the final SQL against the source SQLite database.

Authenticated account parameter

The reserved parameter :voltAccountDID is injected from the authenticated session, which is useful for per-account filtering in both reads and writes.

Example read template:

SELECT * FROM orders WHERE account_id = :voltAccountDID

Example write template:

INSERT INTO orders (account_id, amount) VALUES (:voltAccountDID, 100)

Sub-select mode

If volt:sqlite-view-sub-select is enabled, callers can provide an outer query that wraps the base template.

Sub-select mode can broaden what a caller can query. Enable it only after reviewing access scope and policy.