1"use strict";(globalThis.webpackChunkmission_control=globalThis.webpackChunkmission_control||[]).push([[4238],{71695(e,s,r){r.r(s),r.d(s,{assets:()=>i,contentTitle:()=>l,default:()=>m,frontMatter:()=>o,metadata:()=>n,toc:()=>d});const n=JSON.parse('{"id":"guide/canary-checker/reference/sql","title":"SQL","description":"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.","source":"@site/docs/guide/canary-checker/reference/1-sql.mdx","sourceDirName":"guide/canary-checker/reference","slug":"/guide/canary-checker/reference/sql","permalink":"/docs/guide/canary-checker/reference/sql","draft":false,"unlisted":false,"editUrl":"https://github.com/flanksource/docs/tree/main/docs/guide/canary-checker/reference/1-sql.mdx","tags":[],"version":"current","sidebarPosition":1,"frontMatter":{"title":"SQL","sidebar_custom_props":{"icon":"database"}},"sidebar":"guide","previous":{"title":"S3 Protocol","permalink":"/docs/guide/canary-checker/reference/s3-protocol"},"next":{"title":"TCP","permalink":"/docs/guide/canary-checker/reference/tcp"}}');var t=r(74848),a=r(28453),c=r(97486);const o={title:"SQL",sidebar_custom_props:{icon:"database"}},l=" SQL",i={},d=[{value:"Result Variables",id:"result-variables",level:2},{value:"<Icon></Icon> MySQL",id:"-mysql",level:2},{value:"<Icon></Icon> PostgreSQL",id:"postgres",level:2},{value:"<Icon></Icon> SQL Server",id:"mssql",level:2}];function u(e){const s={code:"code",em:"em",h1:"h1",h2:"h2",header:"header",p:"p",pre:"pre",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",...(0,a.R)(),...e.components},{HealthCheck:r,Icon:n}=s;return r||h("HealthCheck",!0),n||h("Icon",!0),(0,t.jsxs)(t.Fragment,{children:[(0,t.jsx)(s.header,{children:(0,t.jsxs)(s.h1,{id:"-sql",children:[(0,t.jsx)(c.XAH,{})," SQL"]})}),"\n",(0,t.jsxs)(s.p,{children:["The SQL checks connect to a database, run a query, and fail when the returned row count is less than ",(0,t.jsx)(s.code,{children:"results"}),". If ",(0,t.jsx)(s.code,{children:"results"})," is omitted, the threshold is ",(0,t.jsx)(s.code,{children:"0"}),". Set ",(0,t.jsx)(s.code,{children:"markFailOnEmpty: true"})," to fail on zero rows. Use the ",(0,t.jsx)(s.code,{children:"postgres"}),", ",(0,t.jsx)(s.code,{children:"mysql"}),", or ",(0,t.jsx)(s.code,{children:"mssql"})," field for the database engine that you want to check. The following fields apply to all three SQL check types."]}),"\n",(0,t.jsx)(s.pre,{children:(0,t.jsx)(s.code,{className:"language-yaml",metastring:'title="postgres.yaml" file=<rootDir>/modules/canary-checker/fixtures/datasources/postgres.yaml',children:"apiVersion: canaries.flanksource.com/v1\nkind: Canary\nmetadata:\n name: postgres-check\nspec:\n schedule: '@every 30s'\n postgres: # or mysql, mssql\n - name: postgres schemas check\n url: \"postgres://$(username):$(password)@postgres.default.svc:5432/postgres?sslmode=disable\"\n username:\n valueFrom:\n secretKeyRef:\n name: postgres-credentials\n key: USERNAME\n password:\n valueFrom:\n secretKeyRef:\n name: postgres-credentials\n key: PASSWORD\n query: SELECT current_schemas(true)\n display:\n template: |\n {{- range $r := .results.rows }}\n {{- $r.current_schemas}}\n {{- end}}\n results: 1\n"})}),"\n",(0,t.jsx)(r,{name:"postgres",rows:[{field:"connection",description:"Connection URL, such as `connection://postgres/prod`, that supplies the database connection string",scheme:"string"},{field:"url",description:"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.",scheme:"string"},{field:"username",description:"Username that can be used when templating `url` with `$(username)`",scheme:"EnvVar"},{field:"password",description:"Password that can be used when templating `url` with `$(password)`",scheme:"EnvVar"},{field:"query",description:"SQL query to execute. Defaults to `SELECT 1` when omitted.",scheme:"SQL"},{field:"results",description:"Minimum number of expected rows. Defaults to `0`.",scheme:"int"},{field:"timeout",description:"Query timeout in seconds. Defaults to `60`.",scheme:"int"}]}),"\n",(0,t.jsx)(s.h2,{id:"result-variables",children:"Result Variables"}),"\n",(0,t.jsxs)(s.p,{children:["Use ",(0,t.jsx)(s.code,{children:".results.rows"})," and ",(0,t.jsx)(s.code,{children:".results.count"})," in Go templates."]}),"\n",(0,t.jsxs)(s.table,{children:[(0,t.jsx)(s.thead,{children:(0,t.jsxs)(s.tr,{children:[(0,t.jsx)(s.th,{children:"Name"}),(0,t.jsx)(s.th,{children:"Description"}),(0,t.jsx)(s.th,{children:"Scheme"})]})}),(0,t.jsxs)(s.tbody,{children:[(0,t.jsxs)(s.tr,{children:[(0,t.jsx)(s.td,{children:(0,t.jsx)(s.code,{children:"results.rows"})}),(0,t.jsx)(s.td,{children:"Rows returned by the query."}),(0,t.jsx)(s.td,{children:(0,t.jsx)(s.em,{children:"[]map[string]interface"})})]}),(0,t.jsxs)(s.tr,{children:[(0,t.jsx)(s.td,{children:(0,t.jsx)(s.code,{children:"results.count"})}),(0,t.jsx)(s.td,{children:"Number of rows returned."}),(0,t.jsx)(s.td,{children:(0,t.jsx)(s.em,{children:"int"})})]})]})]}),"\n",(0,t.jsxs)(s.h2,{id:"-mysql",children:[(0,t.jsx)(n,{name:"mysql"})," MySQL"]}),"\n",(0,t.jsx)(s.pre,{children:(0,t.jsx)(s.code,{className:"language-yaml",metastring:'title="mysql.yaml" file=<rootDir>/modules/canary-checker/fixtures/datasources/mysql_pass.yaml',children:'apiVersion: canaries.flanksource.com/v1\nkind: Canary\nmetadata:\n name: mysql-pass\nspec:\n schedule: "@every 5m"\n mysql:\n - url: "mysql://$(username):$(password)@tcp(mysql.canaries.svc.cluster.local:3306)/mysqldb"\n name: mysql ping check\n username:\n value: mysqladmin\n password:\n value: admin123\n query: "SELECT 1"\n results: 1\n'})}),"\n",(0,t.jsxs)(s.h2,{id:"postgres",children:[(0,t.jsx)(n,{name:"postgres"})," PostgreSQL"]}),"\n",(0,t.jsx)(s.pre,{children:(0,t.jsx)(s.code,{className:"language-yaml",metastring:'title="postgres.yaml" file=<rootDir>/modules/canary-checker/fixtures/datasources/postgres_pass.yaml',children:'apiVersion: canaries.flanksource.com/v1\nkind: Canary\nmetadata:\n name: postgres-succeed\nspec:\n schedule: "@every 5m"\n postgres:\n - url: "postgres://$(username):$(password)@postgres.canaries.svc.cluster.local:5432/postgres?sslmode=disable"\n name: postgres schemas check\n username:\n value: postgresadmin\n password:\n value: admin123\n query: SELECT 1\n results: 1\n'})}),"\n",(0,t.jsxs)(s.h2,{id:"mssql",children:[(0,t.jsx)(n,{name:"sqlserver"})," SQL Server"]}),"\n",(0,t.jsx)(s.pre,{children:(0,t.jsx)(s.code,{className:"language-yaml",metastring:'title="mssql.yaml" file=<rootDir>/modules/canary-checker/fixtures/datasources/mssql_pass.yaml',children:'apiVersion: canaries.flanksource.com/v1\nkind: Canary\nmetadata:\n name: mssql-pass\nspec:\n schedule: "@every 5m"\n mssql:\n - url: "server=mssql.canaries.svc.cluster.local;user id=$(username);password=$(password);port=1433;database=master;TrustServerCertificate=True"\n name: mssql pass\n username:\n value: sa\n password:\n value: S0m3p@sswd\n query: "SELECT 1"\n results: 1\n'})})]})}function m(e={}){const{wrapper:s}={...(0,a.R)(),...e.components};return s?(0,t.jsx)(s,{...e,children:(0,t.jsx)(u,{...e})}):u(e)}function h(e,s){throw new Error("Expected "+(s?"component":"object")+" `"+e+"` to be defined: you likely forgot to import, pass, or provide it.")}},28453(e,s,r){r.d(s,{R:()=>c,x:()=>o});var n=r(96540);const t={},a=n.createContext(t);function c(e){const s=n.useContext(a);return n.useMemo(function(){return"function"==typeof e?e(s):{...s,...e}},[s,e])}function o(e){let s;return s=e.disableParentContext?"function"==typeof e.components?e.components(t):e.components||t:c(e.components),n.createElement(a.Provider,{value:s},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.