Run query

postgres.query executes one statement inside a PostgreSQL READ ONLY transaction. PostgreSQL blocks its documented set of data-changing and DDL commands, but READ ONLY is not a pure-read sandbox. NOTIFY and routines can still create external effects.

Example

automations/query-postgres.automation.ts

import { automation, onDashboardRun } from "automate.ax"
import { postgres } from "automate.ax/postgres"

export default automation("Find recent pro customers", () => {
  onDashboardRun({ title: "Find recent pro customers" })

  postgres.query({
    sql: `
      select id, email
      from customers
      where plan = $1 and created_at >= $2
      order by created_at desc
    `,
    parameters: ["pro", new Date("2026-01-01T00:00:00Z")],
  })
})

Inputs

Input	Type	Required	Default	Description
`sql`	`string`	Yes	None	Exactly one non-empty SQL statement.
`parameters`	`(JsonValue \| bigint \| Date \| Uint8Array)[]`	No	`[]`	Values bound to `$1`, `$2`, and later placeholders.
Second argument: `{ account }`	`string \| IntegrationAccountReference<"postgres">`	No	Project default	Connected PostgreSQL database.

Use parameters for untrusted values. Placeholders can’t represent table or column names; don’t concatenate untrusted identifiers into SQL.

Output

Returns a signal with:

Property	Type	Description
`command`	`string`	PostgreSQL command tag, usually `SELECT`.
`rowCount`	`number \| null`	Returned row count when PostgreSQL supplies one.
`rows`	`Record<string, Encodable>[]`	Result rows keyed by the database column names.
`fields`	`PostgresField[]`	Metadata for returned fields.

Each field contains name, columnId, tableId, dataTypeId, dataTypeSize, dataTypeModifier, and format. Database column names and nested JSON keys remain unchanged because they belong to your schema; the PostgreSQL metadata property names use camel case.

Permissions and failures

Grant the connected role CONNECT on the database, USAGE on referenced schemas, and only the SELECT or routine privileges the query needs. PostgreSQL returns SQLSTATE-backed errors for syntax, permissions, missing objects, timeouts, and data failures. Commands that PostgreSQL classifies as disallowed in a read-only transaction fail with SQLSTATE 25006 (read_only_sql_transaction); notifications and routines with external effects do not. Automate.ax rejects multiple statements before connecting. It does not automatically retry this action because a read-only statement can still invoke a routine with an external side effect or send a PostgreSQL notification. Use Execute statement for one read/write statement or Run transaction for several atomic statements.

Connection security

Use a postgres:// or postgresql:// connection string for a dedicated least-privilege role. Prefer sslmode=verify-full with a trusted server certificate. With the current PostgreSQL client, sslmode=require verifies the server certificate unless you also set uselibpqcompat=true; that compatibility option encrypts without verifying server identity.

Built-in triggers

Built-in actions

Runtime utilities

Integrations

Example

Inputs

Output

Permissions and failures

Connection security

​Example

​Inputs

​Output

​Permissions and failures

​Connection security

Example

Inputs

Output

Permissions and failures

Connection security