Writeback and SQL

For an externally hosted app with an authenticated platform transport, use client.request() to call the Writeback API, execute a stored procedure, or read data with SQL. The SDK has no convenience methods for these operations. Configure the trusted server connection described in the externally hosted app guide; keep credentials on the server.

These examples require a transport that forwards the following platform routes. During astrato dev, use the local App runtime’s capability-gated app.executeProcedure() and app.writeback() methods instead. SQL SELECT requests are unavailable through that gateway, and the hosted App publication transport does not provide these operations. See App runtime availability.

Operation	POST route	Required OAuth scope
Writeback	/api/message/tenants/{tenantId}/data/writeback	data:write
Stored procedure	/api/message/tenants/{tenantId}/data/execute/procedure	data:sql
SQL SELECT query	/api/message/tenants/{tenantId}/data/execute/sql	data:sql

The authorization server must permit the requested scopes. Scopes do not replace Astrato resource permissions or the database privileges of the configured datasource. The datasource must support writeback, SQL or procedure execution for the operation you choose. These examples use an existing Snowflake table and procedure; support and permissions must be checked on your datasource.

Set up the client and target

The TypeScript blocks below share the client and IDs defined in this section. Run inserts, updates and procedures only for an authorized action; these examples change warehouse data.

The following examples use a Snowflake table DEMO.PUBLIC.FORECASTS, exposed by a semantic-layer view named Forecasts. Its numeric fields ForecastID and Amount map to warehouse columns FORECAST_ID and AMOUNT. Replace the placeholder IDs and example names with your own. Writeback identifies the semantic layer with dataviewID; SQL and procedure execution identify the datasource with datasourceID.

import { createAstratoClient } from '@astrato-io/sdk';

// Your trusted server authenticates and forwards /api requests to Astrato.
const client = createAstratoClient({ baseUrl: window.location.origin });
const tenantId = 'your-tenant-id';
const semanticLayerId = 'your-semantic-layer-uuid';
const datasourceId = 'your-datasource-uuid';
const dataUrl = `/api/message/tenants/${encodeURIComponent(tenantId)}/data`;

type WritebackResponse = {
  response: { executedStatements: number; rowsAffected: number };
};

Insert a row

Use semantic-layer view and field names, rather than SDK field keys such as [Forecasts].[Amount]. Each row in values follows the order of fields. Supported valueType values are string, number, bool and date.

const inserted = await client.request<WritebackResponse>({
  method: 'POST',
  url: `${dataUrl}/writeback`,
  body: {
    dataviewID: semanticLayerId,
    view: 'Forecasts',
    operation: 'insert',
    fields: [
      { name: 'ForecastID', valueType: 'number' },
      { name: 'Amount', valueType: 'number' },
    ],
    values: [[123, 4500]],
  },
});
console.log('Rows affected:', inserted.response.rowsAffected);

Update a row

Supply keys to identify the records to update. Choose keys that uniquely identify the intended row; a key matching several records can update all of them. Each keys.values row corresponds to the same position in values.

const updated = await client.request<WritebackResponse>({
  method: 'POST',
  url: `${dataUrl}/writeback`,
  body: {
    dataviewID: semanticLayerId,
    view: 'Forecasts',
    operation: 'update',
    fields: [{ name: 'Amount', valueType: 'number' }],
    values: [[4800]],
    keys: {
      fields: [{ name: 'ForecastID', valueType: 'number' }],
      values: [[123]],
    },
  },
});
console.log('Rows affected:', updated.response.rowsAffected);

Execute a stored procedure

Create the procedure in your database first and grant the configured datasource permission to execute it. This example assumes PUBLIC.UPDATE_FORECAST exists in the datasource’s database and accepts a numeric forecast ID followed by a numeric amount. Pass argument values in declaration order as JSON values; do not quote them as SQL or build a CALL statement yourself.

const procedureResult = await client.request<{ response: unknown }>({
  method: 'POST',
  url: `${dataUrl}/execute/procedure`,
  body: {
    datasourceID: datasourceId,
    schema: 'PUBLIC',
    procedure: 'UPDATE_FORECAST',
    params: [123, 5000],
  },
});
console.log(procedureResult.response);

The returned result depends on the procedure and datasource. This call can change database data. The SQL endpoint below accepts SELECT queries; it wraps the supplied statement as a subquery and cannot be used to execute a procedure’s CALL statement.

Read back the result

Use a fully qualified SELECT statement for a SQL read. This API returns row arrays indexed by string keys under response.data; response.fields describes the columns in array order.

type SqlResponse = {
  response: {
    fields: Array<{ name: string; dataType: string }>;
    data: Record<string, unknown[]>;
  };
};

const readback = await client.request<SqlResponse>({
  method: 'POST',
  url: `${dataUrl}/execute/sql`,
  body: {
    datasourceID: datasourceId,
    statement: 'SELECT FORECAST_ID, AMOUNT FROM DEMO.PUBLIC.FORECASTS WHERE FORECAST_ID = 123',
    limit: 100,
  },
});
console.log(readback.response.fields, readback.response.data);

After a successful write or procedure call, re-run the data query that supplies your chart or form. Existing query results do not refresh automatically. Handle rejected requests as shown in SDK error handling. If a connection fails before you receive the result, check whether the write completed before submitting it again. Destroy the client when the app unmounts.