# Execute SQL Query — Incorta

> Executes a custom SQL query against an Incorta tenant via SQLi, Incorta's SQL Interface, which exposes the tenant as a PostgreSQL-wire-protocol database. Use this for ad hoc reporting or data exploration against Incorta's Business Schema…

- Key: `incorta-execute-sql-query`
- Type: Action (Write)
- Version: 0.0.1
- App: Incorta (`incorta`) — https://pipedream.com/apps/incorta.md
- This page (HTML): https://pipedream.com/apps/incorta/actions/execute-sql-query
- Hints: open-world
- Source: https://github.com/PipedreamHQ/pipedream/blob/master/components/incorta/actions/execute-sql-query/execute-sql-query.mjs

## Description

Executes a custom SQL query against an Incorta tenant via **SQLi**, Incorta's SQL Interface, which exposes the tenant as a PostgreSQL-wire-protocol database. Use this for ad hoc reporting or data exploration against Incorta's Business Schema tables and materialized views. Incorta SQLi is primarily a read/query interface (it is not a general-purpose transactional database) — expect `SELECT` statements with standard ANSI SQL: joins, aggregates, `GROUP BY`, window functions, and CTEs all work. Use **List Schemas**, **List Tables**, and **List Columns** first if you don't already know the available schema/table/column names. Always include a `LIMIT` (and select only the columns you need) for exploratory queries: results are capped at **Max Rows** (default 500), and the response reports `truncated: true` when more rows existed than were returned, so a truncated result is partial (the total row count is not known; run a `COUNT(*)` if you need it). Narrow the query with `LIMIT`, filters, or aggregates rather than relying on the cap. **Do not use `OFFSET`**: Incorta SQLi silently ignores it and returns the same rows again, with no error. To read the next page of a large result, use keyset paging instead: `ORDER BY <unique column>` and `WHERE <column> > <last value seen>` with `LIMIT`, e.g. `SELECT * FROM sales.orders WHERE order_id > 1000 ORDER BY order_id LIMIT 100`. Example: `SELECT region, SUM(amount) AS total_sales FROM sales.sales_fact GROUP BY region ORDER BY total_sales DESC LIMIT 50` returns `{rows: [{region, total_sales}, ...], rowCount, truncated: false}`, one row per region. Requires a SQLi Username and Password (mixed-mode authentication, obtained from your Incorta administrator), supplied either as props or saved on the connected Incorta account; props take precedence. The SQLi host/port and tenant database are resolved automatically. [See the documentation](https://docs.incorta.com/5.1/concepts-sqli/)

## Props

| Prop | Type | Required | Description |
|---|---|---|---|
| `username` | `string` | No | Username for SQLi authentication, e.g. pipedream_reader. Separate from the REST API bearer token. Optional if a SQLi username is saved on the connected account; this value takes precedence. |
| `password` | `string` | No | Password for SQLi authentication, paired with SQLi Username. Optional if a SQLi password is saved on the connected account; this value takes precedence. |
| `port` | `integer` | No | Port to connect to for SQLi, e.g. 5812. Optional — takes precedence over a port saved on the connected account, and over the port returned by Incorta's /configs/sqlConnection endpoint. |
| `sql` | `sql` | Yes | The SQL query to execute against Incorta via SQLi, e.g. SELECT * FROM sales.sales_fact LIMIT 50. See our docs to learn more about working with SQL in Pipedream |
| `maxRows` | `integer` | No | Maximum number of rows to return, e.g. 100. The action stops reading once more rows than this have arrived and reports truncated. It does not change or limit the query itself, so add a LIMIT to the SQL as well. |

## Run it

**MCP**

```ts
import { Client } from "@modelcontextprotocol/sdk/client/index.js"
import { StreamableHTTPClientTransport } from "@modelcontextprotocol/sdk/client/streamableHttp.js"
import { PipedreamClient } from "@pipedream/sdk"

const pd = new PipedreamClient({
  projectId: process.env.PIPEDREAM_PROJECT_ID!,
  clientId: process.env.PIPEDREAM_CLIENT_ID!,
  clientSecret: process.env.PIPEDREAM_CLIENT_SECRET!,
  projectEnvironment: "production",
})

const accessToken = await pd.rawAccessToken

const transport = new StreamableHTTPClientTransport(
  new URL("https://remote.mcp.pipedream.net/v3"),
  {
    requestInit: {
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "x-pd-project-id": process.env.PIPEDREAM_PROJECT_ID!,
        "x-pd-environment": "production",
        "x-pd-external-user-id": "{external_user_id}", // any stable ID for this user in your system
        "x-pd-app-slug": "incorta",
      },
    },
  },
)

const mcp = new Client({ name: "my-agent", version: "1.0.0" })
await mcp.connect(transport)

const { tools } = await mcp.listTools()

// listTools() hands your model this tool's input schema, so it can
// fill the arguments itself:
const result = await mcp.callTool({
  name: "incorta-execute-sql-query",
  arguments: {
    username: "SQLi Username",
    password: "SQLi Password",
  },
})
```

**TypeScript**

```ts
import { PipedreamClient } from "@pipedream/sdk"

const pd = new PipedreamClient({
  projectId: process.env.PIPEDREAM_PROJECT_ID!,
  clientId: process.env.PIPEDREAM_CLIENT_ID!,
  clientSecret: process.env.PIPEDREAM_CLIENT_SECRET!,
  projectEnvironment: "production",
})

const result = await pd.actions.run({
  id: "incorta-execute-sql-query",
  externalUserId: "{external_user_id}", // any stable ID for this user in your system
  configuredProps: {
    incorta: { authProvisionId: "apn_xxxxxxx" },
    username: "SQLi Username",
    password: "SQLi Password",
  },
})

console.log(result)
```

**cURL**

```bash
curl -X POST https://api.pipedream.com/v1/connect/{project_id}/actions/run \
  -H "Content-Type: application/json" \
  -H "X-PD-Environment: production" \
  -H "Authorization: Bearer {access_token}" \
  -d '{
    "external_user_id": "{external_user_id}",
    "id": "incorta-execute-sql-query",
    "configured_props": {
      "incorta": { "authProvisionId": "apn_xxxxxxx" },
      "username": "SQLi Username",
      "password": "SQLi Password"
    }
  }'
```

---

- App: https://pipedream.com/apps/incorta.md · All apps: https://pipedream.com/apps