SQL

The SQL checks connect to a database, run a query, and fail when the returned row count is less than results. If results is omitted, the threshold is 0. Set markFailOnEmpty: true to fail on zero rows. Use the postgres, mysql, or mssql field for the database engine that you want to check. The following fields apply to all three SQL check types.

postgres.yaml
apiVersion: canaries.flanksource.com/v1
kind: Canary
metadata:
  name: postgres-check
spec:
  schedule: '@every 30s'
  postgres: # or mysql, mssql
    - name: postgres schemas check
      url: "postgres://$(username):$(password)@postgres.default.svc:5432/postgres?sslmode=disable"
      username:
        valueFrom:
          secretKeyRef:
            name: postgres-credentials
            key: USERNAME
      password:
        valueFrom:
          secretKeyRef:
            name: postgres-credentials
            key: PASSWORD
      query: SELECT current_schemas(true)
      display:
        template: |
          {{- range $r := .results.rows }}
          {{- $r.current_schemas}}
          {{- end}}
      results: 1

Field	Description	Scheme
name*	Name of the check, must be unique within the canary	string
connection	Connection URL, such as connection://postgres/prod, that supplies the database connection string	string
password	Password that can be used when templating url with $(password)	EnvVar
query	SQL query to execute. Defaults to SELECT 1 when omitted.	SQL
results	Minimum number of expected rows. Defaults to 0.	integer
timeout	Query timeout in seconds. Defaults to 60.	integer
url	Database connection string. Required unless connection supplies the URL. For MySQL, use a go-sql-driver/mysql DSN; the checker also accepts the same DSN with a leading mysql:// prefix.	string
username	Username that can be used when templating url with $(username)	EnvVar
dependsOn	Checks that must complete before this check runs	[]string
description	Description for the check	string
display	Expression to change the formatting of the display	Expression
icon	Icon for overwriting default icon on the dashboard	Icon
labels	Labels for check	[map[string]string]
markFailOnEmpty	If a transformation or datasource returns empty results, the check should fail	boolean
metrics	Metrics to export from	[]Metrics
namespace	Namespace to insert the check result into when it differs from the canary namespace	string
relationships	Relationships that link check results to components or configs	Relationship
test	Evaluate whether a check is healthy	Expression
transform	Transform data from a check into multiple individual checks	Expression
transformDeleteStrategy	Status to apply to transformed checks when the source check no longer returns them	string

Result Variables

Use .results.rows and .results.count in Go templates.

Name	Description	Scheme
results.rows	Rows returned by the query.	[]map[string]interface
results.count	Number of rows returned.	int

MySQL

mysql.yaml
apiVersion: canaries.flanksource.com/v1
kind: Canary
metadata:
  name: mysql-pass
spec:
  schedule: "@every 5m"
  mysql:
    - url: "mysql://$(username):$(password)@tcp(mysql.canaries.svc.cluster.local:3306)/mysqldb"
      name: mysql ping check
      username:
        value: mysqladmin
      password:
        value: admin123
      query: "SELECT 1"
      results: 1

PostgreSQL

postgres.yaml
apiVersion: canaries.flanksource.com/v1
kind: Canary
metadata:
  name: postgres-succeed
spec:
  schedule: "@every 5m"
  postgres:
    - url: "postgres://$(username):$(password)@postgres.canaries.svc.cluster.local:5432/postgres?sslmode=disable"
      name: postgres schemas check
      username:
        value: postgresadmin
      password:
        value: admin123
      query: SELECT 1
      results: 1

SQL Server

mssql.yaml
apiVersion: canaries.flanksource.com/v1
kind: Canary
metadata:
  name: mssql-pass
spec:
  schedule: "@every 5m"
  mssql:
    - url: "server=mssql.canaries.svc.cluster.local;user id=$(username);password=$(password);port=1433;database=master;TrustServerCertificate=True"
      name: mssql pass
      username:
        value: sa
      password:
        value: S0m3p@sswd
      query: "SELECT 1"
      results: 1