Database

The database connector owns a SQL connection pool (Postgres or SQLite, selected by the driver setting), and the sql block runs statements through it, folding the result into the message body. Both drivers are pure Go, so no CGO toolchain is needed.

database connector

The connector opens the pool on start, verifies the connection so a bad DSN fails fast, and closes the pool on shutdown.

Provides a source: no, it is a service connector. Blocks that bind to it: sql.

Settings

Example DSNs:

# Postgres
dsn: postgres://user:pw@localhost:5432/orders?sslmode=disable
# SQLite (file path)
dsn: file:./orders.db

Keeping only the password secret

Put the password in password rather than inside the DSN. The connection string then stays plain config (host, user, database and sslmode are not secrets), and rotating the password touches one value:

env:
  - name: DB_PASSWORD
    required: true

connectors:
  - name: db
    type: database
    settings:
      driver: postgres
      dsn: postgres://app@db.internal:5432/app?sslmode=require
      password: ${DB_PASSWORD}

Both Postgres DSN forms are supported, the URL form above and the keyword form (host=db.internal user=app dbname=app). The password is escaped or quoted for the form it is merged into, so it needs no hand-encoding. If the DSN already carries a password, the password setting overrides it and the connector logs a warning. password means nothing to SQLite, so setting it with driver: sqlite fails at startup.

sql block

SQL executes a SQL statement against a database connector. In query mode (the default) the body becomes an array of row objects, or a single object when single is set; in exec mode the body becomes {"rowsAffected": N}. Bind parameters come from CEL expressions evaluated against the message.

Placeholder style

Placeholders are the driver's own: Postgres uses numbered $1, $2, … and SQLite uses positional ?.

# postgres
query: "SELECT * FROM orders WHERE id = $1"
# sqlite
query: "SELECT * FROM orders WHERE id = ?"

Result shape

Rows are scanned into column-name to value maps and normalized through JSON, so downstream blocks and CEL expressions see consistent decoded-JSON types (numbers are floats, text/blob columns are strings).

Example

Adapted from samples/db-orders.yaml, an HTTP API over SQLite:

service:
  name: db-orders

env:
  - name: DB_DRIVER
    default: sqlite
  - name: DB_DSN
    default: file:./orders.db

connectors:
  - name: api
    type: http
    settings:
      basePath: /api/v1
      requestTimeout: 5s
  - name: orders-db
    type: database
    settings:
      driver: ${DB_DRIVER}
      dsn: ${DB_DSN}
      maxOpenConns: 4

flows:
  # POST /orders: insert a row and return it (single: true -> one object).
  - name: orders
    source:
      connector: api
      type: http
      settings:
        path: /orders
    process:
      - type: sql
        name: insert-order
        settings:
          connector: orders-db
          query: "INSERT INTO orders (item, amount) VALUES (?, ?) RETURNING *"
          args:
            - body.item
            - body.amount
          single: true

  # GET /orders/{id}: the matching row, or null when absent.
  - name: orders-get
    source:
      connector: api
      type: http
      settings:
        path: /orders/{id}
    process:
      - type: sql
        name: select-order
        settings:
          connector: orders-db
          query: "SELECT * FROM orders WHERE id = ?"
          args:
            - vars.id
          single: true