SQL

The postgres component lookup allows you to form components from the records in a Postgres database.

In this example below, we form components from all the tables in the incident_commander database.

postgres-tables.yml
apiVersion: canaries.flanksource.com/v1
kind: Topology
metadata:
  name: postgres-tables
  namespace: default
spec:
  schedule: '@every 30s'
  components:
    - name: Postgres
      type: Table
      icon: postgres
      lookup:
        postgres:
          - connection: postgres://postgres:gunners@localhost:5432/incident_commander?sslmode=disable
            query: |
              SELECT
                schemaname || '.' || relname AS table_name,
                n_live_tup AS num_rows
              FROM
                pg_catalog.pg_stat_user_tables
              ORDER BY
                n_live_tup DESC;
            display:
              expr: |
                results.rows.map(row, {
                  'name': row.table_name,
                  'type': "Table",
                  'properties': [{
                    "name": "Records",
                    "headline": true,
                    "value": double(row.num_rows),
                  }]
                }).toJSON()

Field	Description	Scheme	Required
connection	connection string to connect to the server	string	Yes
password	Set password for authentication using string, configMapKeyRef, or SecretKeyRef.	EnvVar	Yes
username	Set username for authentication using string, configMapKeyRef, or SecretKeyRef.	EnvVar	Yes
display	Template to display query results in text (overrides default bar format for UI)	Template
query	query that needs to be executed on the server	string	Yes

Results

The results variable in the template will contain the following fields

Field	Description	Scheme
rows	stderr from the script	[]map[string]any
count	exit code of the script	int

Connection Types

SQL Server

# ...
kind: Topology
spec:
  components:
    - lookup:
          mssql:
            connection: mssql://sa:password@localhost:1433/db
      # ...

MySQL

# ...
kind: Topology
spec:
  components:
    - lookup:
        mssql:
          connection: mysql://root:password@localhost:3306/db
      # ...

Postgres

# ...
kind: Topology
spec:
  components:
    - lookup:
        postgres:
          connection: postgres://postgres:gunners@localhost:5432/incident_commander?sslmode=disable
      # ...