Postgres

postgres blocks represent user-managed external PostgreSQL databases: databases you operate, outside Swirls platform-managed streams and type: stream nodes. After you declare a connection and table row schemas, type: postgres nodes can run parameterized SELECT (select:) or INSERT (insert:) SQL against those tables.

Declaring a postgres block

There is no type: field: the keyword postgres identifies the block.

postgres my_db {
  label: "CRM database"
  secrets: project_secrets
  connection: SHARED_DATABASE_URL

  table leads {
    schema: @json {
      {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "email": { "type": "string" },
          "score": { "type": "number" }
        },
        "required": ["id", "email"]
      }
    }
  }
}

Table names in the DSL are unqualified (for example leads); runtime uses your database’s default schema unless you extend conventions later.

Table declarations and tooling

Each table block’s schema is JSON Schema for a single row. The language service uses it to:

• Validate that explicit INSERT INTO t (col1, col2) column names exist on the declared table.
• Infer TypeScript types for params return objects and for {{key}} hovers from SQL (for example, which column a placeholder maps to).

Select nodes (select:)

Use exactly one of select: or insert: on a type: postgres node. For reads, use select: with a SELECT (or WITH) statement.

node load_leads {
  type: postgres
  label: "Active leads"
  postgres: my_db

  select: @sql {
    SELECT id, email, score
    FROM leads
    WHERE score >= {{min_score}}
  }

  params: @ts {
    return {
      min_score: context.nodes.root.output.threshold
    }
  }

  schema: @json {
    {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "email": { "type": "string" },
          "score": { "type": "number" }
        }
      }
    }
  }
}

Node output is an array of row objects (query result rows).

Insert nodes (insert:)

For writes, use insert: with an INSERT statement. params: is required.

Wrap row values in VALUES (...): the validator requires VALUES to be followed by parentheses around the value list.

node upsert_lead {
  type: postgres
  postgres: my_db

  condition: @ts {
    return context.nodes.classify.output.score > 20
  }

  insert: @sql {
    INSERT INTO leads (name, email, score)
    VALUES ({{name}}, {{email}}, {{score}})
  }

  params: @ts {
    return {
      name: context.nodes.root.output.name,
      email: context.nodes.root.output.email,
      score: context.nodes.classify.output.score
    }
  }
}

Typical output shape is { executed: true } or, when condition is false, { skipped: true }.

Parameter interpolation ({{key}})

Values are not concatenated into the SQL string. At runtime, each {{key}} is replaced with a positional placeholder ($1, $2, …) and the corresponding value is sent as a bound parameter: so user data never becomes raw SQL text.

In the DSL, params: returns a named object. Placeholder names in SQL and keys in that object must match when placeholders exist. Those names do not need to match JSON Schema property names on the row: types are inferred from SQL position (for example, which column appears in INSERT INTO t (a,b) VALUES ({{x}},{{y}})) or from simple comparison patterns on SELECT (for example WHERE col = {{key}}).

Type safety in the editor

When table row schemas are declared and SQL is parseable by the tooling, you get:

• Hover types on {{key}} inside @sql blocks.
• Type checking on the object returned from params @ts blocks.
• An index signature on the inferred params type so extra keys remain valid when you map names freely.

The Node types reference lists all postgres node fields in one place.