Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 37 ยท Data Sources

SQL Data Sources: MySQL, PostgreSQL and SQL Server

Query relational databases from Grafana: connection settings, read-only users, time series and table formats, macros like $__timeFilter and $__timeGroup, and safe patterns.

Plenty of valuable data never reaches Prometheus: orders, signups, tickets, inventory. It sits in MySQL, PostgreSQL or SQL Server, and Grafana can query it directly. The data source plugins for these databases share one design: you write plain SQL, and Grafana helps with macros that fill in the time range and grouping.

The database servers themselves are not available on this site. Macro examples are read-only blocks. The runnable parts are stand-ins: a JavaScript macro expander and plain SQL run in the in-page SQLite engine on a small table.

Connecting

Add MySQL, PostgreSQL or Microsoft SQL Server under Connections. Typical fields:

  • Host URL: db.internal:3306 (MySQL), db.internal:5432 (PostgreSQL).
  • Database name, user, password.
  • TLS/SSL mode (PostgreSQL disable, require, verify-full; MySQL TLS toggles).
  • Connection limits: max open connections, max idle, max lifetime.
  • Min time interval and, for PostgreSQL, the TimescaleDB toggle.

Use a read-only account

Grafana can run any SQL you give it, including DELETE. Create a user with SELECT rights only, on only the tables dashboards need:

SQL
-- MySQL
CREATE USER 'grafana_ro'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON shop.* TO 'grafana_ro'@'%';
SQL
-- PostgreSQL
CREATE USER grafana_ro WITH PASSWORD 'a-long-random-password';
GRANT USAGE ON SCHEMA public TO grafana_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO grafana_ro;

Do this even if "only admins use Grafana"; dashboards can be edited by anyone with Editor rights.

Two result formats

The query editor has Format as:

  • Time series: requires a column named time (a datetime or Unix epoch in seconds), optionally a text column that becomes the series name (or labels), and numeric value columns.
  • Table: any columns; shown as a table or used by pie charts, bar charts and stat panels.

Macros

Macros are placeholders Grafana expands before sending the SQL:

Macro Expands to (idea)
$__timeFilter(col) col BETWEEN <from> AND <to> using the dashboard range
$__timeFrom(), $__timeTo() The range bounds
$__timeGroup(col, '5m') An expression grouping col into 5-minute buckets (MySQL; PostgreSQL has $__timeGroupAlias)
$__unixEpochFilter(col) Same filter for epoch columns
$__interval Panel interval, usable in strings such as '$__interval'

A typical time series query for MySQL:

SQL
SELECT
  $__timeGroup(created_at, '1h') AS time,
  COUNT(*) AS orders
FROM orders
WHERE $__timeFilter(created_at)
GROUP BY 1
ORDER BY 1

A table query for a pie chart:

SQL
SELECT status, COUNT(*) AS total
FROM orders
WHERE $__timeFilter(created_at)
GROUP BY status

Always include ORDER BY time for time series queries; unordered results draw zig-zag lines.

Stand-in: expanding macros

This tiny expander shows what Grafana sends to the database for a given range. The real expansion is dialect-specific; this one produces portable-looking SQL for illustration.

JS Stand-in: macro expansion

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Running the expanded query

Here is the same idea executed on a small in-memory table (timestamps in epoch seconds). It uses plain SQL that SQLite understands.

SQL Orders per 5 minutes (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

SQL A table query for a pie chart (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Wide and long time series

A long result has one row per (time, series) with a text column naming the series:

SQL
SELECT $__timeGroup(created_at, '1h') AS time, region AS metric, SUM(total) AS revenue
FROM orders WHERE $__timeFilter(created_at) GROUP BY 1, 2 ORDER BY 1

A wide result has one numeric column per series (revenue_north, revenue_south). Newer versions can convert between them with the Prepare time series and Partition by values transformations (see chapter 5 of the course).

Variables in SQL

Dashboard variables can be used directly: WHERE region IN ($region) for multi-value variables, or WHERE region = '$region' for a single value. Grafana quotes multi-value lists for you; with '$region' quoting is your job. Never build queries from free-text variables on a database with sensitive data: use constrained, query-based variables.

Performance and safety

  • Index the time column you filter on.
  • Aggregate in SQL; do not return a million raw rows to the browser.
  • Use the data source's max rows or a LIMIT.
  • Point Grafana at a read replica for heavy dashboards.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is required in a Time series formatted SQL result?

    A time column (datetime or epoch seconds) is required; numeric columns become values.
  2. 2. What does $__timeFilter(created_at) do?

    The macro expands to a condition covering the selected from/to range.
  3. 3. Why should the Grafana database user be read-only?

    A SELECT-only account limits the damage of a mistaken or malicious query.
  4. 4. Which Format as option feeds a pie chart?

    Pie and bar charts typically use table-formatted results with a text and a numeric column.

Summary

  • Add MySQL/PostgreSQL/SQL Server like any source, with a read-only account.
  • Format as Time series needs a time column; Table is general purpose.
  • Use $__timeFilter and $__timeGroup and always order by time.
  • Variables can parameterize SQL; quote single-value ones yourself.
  • Aggregate in the database and index time columns.

Next: Other Data Sources: InfluxDB, Elasticsearch, CloudWatch and More