Data Source

community

SQLite

Overview
Installation
Change log
Related content

Grafana SQLite Datasource

This is a Grafana backend plugin to allow using an SQLite database as a data source. The SQLite database needs to be accessible to the filesystem of the device where Grafana itself is running.

Table of contents:

Plugin Installation
Support for Time Formatted Columns
Macros
Alerting
Common Problems - FAQ
Development and Contributing
Supporting the Project
Further Documentation and Links

Plugin Installation

The recommended way to install the plugin for most users is to use the grafana CLI:

Run this command: grafana-cli plugins install frser-sqlite-datasource
Restart the Grafana server.
To make sure the plugin was installed, check the list of installed data sources. Click the Plugins item in the main menu. Both core data sources and installed data sources will appear.

For other installation options (e.g. to install versions not yet releases in the Grafana registry but in Github) see ./docs/installation.md.

Support for Time Formatted Columns

SQLite has no native "time" format. It relies on strings and numbers for time and dates. Since especially for time series Grafana expects an actual time type, however, the plugin provides a way to infer a real timestamp. This can be set in the query editor by providing the name of the column, which should be reformatted to a timestamp.

The plugin supports two different inputs that can be converted to a "time" depending on the type of the value in the column, that should be formatted as "time":

A number input: It is assumed to be a unix timestamp / unix epoch. This represents time in the number of seconds (make sure your timestamp is not in milliseconds). More information is here: https://en.wikipedia.org/wiki/Unix_time
A string input: The value is expected to be formatted in accordance with RFC3339, e.g. "2006-01-02T15:04:05Z07:00". Edge cases might occur and the parsing library used is the source of truth here: https://golang.org/pkg/time/#pkg-constants.

Timestamps stored as unix epoch should work out of the box, but the string formatting might require adjusting your current format. The below example shows how to convert a "date" column to a parsable timestamp:

WITH converted AS (
   -- a row looks like this (value, date): 1.45, '2020-12-12'
   SELECT value,  date || 'T00:00:00Z' AS datetime FROM raw_table
)
SELECT datetime, value FROM converted ORDER BY datetime ASC

Macros

This plugins supports macros inspired by the built-in Grafana data sources (e.g. https://grafana.com/docs/grafana/latest/datasources/postgres/#macros).

However, as each macro needs to be re-implemented from scratch, only the following macros are supported. Other macros (that you might expect from other SQL databases) are not supported by the plugin (yet).

$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds)

Example: $__unixEpochGroupSeconds("time", 10)

Will be replaced by an expression usable in GROUP BY clause. For example: cast(("time" / 10) as int) * 10

$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds, NULL)

Example: $__unixEpochGroupSeconds(timestamp, 10, NULL)

This is the same as the above example but with a fill parameter so missing points in that series will be added for Grafana and NULL will be used as value.

In case multiple time columns are provided the first one is chosen as the column to determine the gap filling. "First" in this context means first in the SELECT statement. This column needs to have no NULL values and must be sorted in ascending order.

Alerting

The plugins supports the Grafana alerting feature. Similar to the built in data sources alerting does not support variables as they are normally replaced in the frontend, which is not involved for the alerts. In order to allow time filtering this plugin supports the variables $__from and $__to. For more information about those variables see here: https://grafana.com/docs/grafana/latest/variables/variable-types/global-variables/#__from-and-__to. Formatting of those variables (e.g. ${__from:date:iso}) is not supported for alerts, however.

Common Problems - FAQ

This is a list of common questions or problems. For the answers and more details see ./docs/faq.md.

Query examples

Some examples to help getting started with SQL and SQLite can be found in ./docs/examples.md.

These examples include things like:

filtering by the time specified in the Grafana UI
creating a time series mindful of gaps
converting dates to timestamps
...

Development and Contributing

Any contribution is welcome. Some information regarding the local setup can be found in the DEVELOPMENT.md file.

Supporting the Project

This project was developed for free as an open source project. And it will stay that way.

If you like using this plugin, however, and would like to support the development go check out the Github sponsorship page. This allows sponsoring the project with monthly or one-time contributions.

Further Documentation and Links

A changelog of the plugin can be found in the CHANGELOG.md.
More documentation about the plugin can be found under the docs section in Github.
The plugin in the Grafana registry can be found here.
Questions or bugs about the plugin can be found and reported in Github or in the Grafana community.

Installing SQLite on Grafana Cloud:

For more information, visit the docs on plugin installation.

Changelog

All notable changes to this project will be documented in this file.

The format is based on Keep a Changelog and follows semantic Versioning.

[3.5.0] - 2024-03-08

Added

Added the ability to use "__text" and "__value" columns for query variables

[3.4.0] - 2023-12-26

Thank you to the collaborators on this release:

@eeddaann

Added

added support for Grafana annotations

[3.3.3] - 2023-07-21

Changed

updated frontend packages with the grafana/create-plugin tool

[3.3.2] - 2023-07-19

Fixed

queries without any content (i.e. "-- not a query") do not cause the plugin to hang

Changed

updated the underlying sqlite library

[3.3.1] - 2023-07-01

Fixed

Closing database connections after every query (bug from v3.3.0)

Changed

Functional regression: a query without any content (i.e. "-- not a query") causes the plugin to hang

[3.3.0] - 2023-06-11

Changed

updated sqlite package version to allow setting limits
add option to set SQLITE_LIMIT_ATTACHED (current default 10). In a future version the default will be reduced to 0.

[3.2.0] - 2023-05-25

Changed

updated go version and go grafana plugin sdk
use grafana plugin sdk mage file to build the plugin
update grafana plugin npm packages

[3.1.4] - 2023-05-17

Nothing user facing was changed. The only difference is a change in meta data to accommodate new Grafana plugin checks.

[3.1.3] - 2023-05-06

Changed

updated go SQLite package to v1.22.1 to fix timezone issues in SQLite

[3.1.2] - 2022-09-11

Changed

updated go SQLite package

[3.1.1] - 2022-09-11

Changed

removed the "name" metadata from data frames

[3.1.0] - 2022-07-09

Changed

Using a different component as the SQL editor
Updated Readme and added more docs (e.g. example queries)
improved error message for database checks on folder paths

I would like to thank the sponsors of this project

one-time: @nikolaialex

[3.0.1] - 2022-03-15

This release should show no feature changes. Some underlying packages have been updated, which should improve stability and security but not have any other noticeable impact.

Changed

Fixed some typos in the readme
Removed the upper Grafana version constraint for the plugin
Update grafana plugin sdk for the backend
Updated grafana buildkit and test tool versions for the frontend

[3.0.0] - 2022-02-26

This release moved to a new underlying SQLite library: https://pkg.go.dev/modernc.org/sqlite. This should have no big changes to regular queries but can have effects on more subtle configurations (e.g. path options). Fore more information see the Changed section below.

This library has no dependency on CGO, which allows much easier cross-compilation for other systems. This way the plugin has a much simpler build process now and also supports more platforms (see information below under Added)

Added

Added new platforms to the release: Darwin (MacOS) for ARM (Apple Silicon) and FreeBSD for AMD64

Changed

Changed the underlying SQLite library to: https://pkg.go.dev/modernc.org/sqlite. While the general SQLite features and especially queries should remain unchanged by this, path options need to be checked for compatibility with the new library now. Please refer to the link above for more information on the options.

Removed

No separate release is created anymore for ARM v7. ARM v6 should suffice for all devices. If that is not the case for your device, please open a new issue.

[2.2.1] - 2021-12-14

Added

An additional option securePathOptions has been added in case the user wants to protect some options (typically credentials). For examples see here: https://github.com/mattn/go-sqlite3#connection-string

[2.2.0] - 2021-11-16

The plugin now supports adding a Path Prefix and Options to the SQLite connection string.

Added

Ability to provide a prefix and options in the connection string. For examples see here: https://github.com/mattn/go-sqlite3#connection-string

Changed

slightly changed the Plugin Health Check (when adding the data source) to provide better error messages.
conversion errors during a query are now logged at DEBUG level to avoid too large log files.

[2.1.1] - 2021-10-24

This release adds support for sub second precision for unix time.

Added

When using numeric values for a timestamp in SQLite (unix timestamp) the plugin now supports precision below the second (at nanosecond precision)

[2.1.0] - 2021-08-08

This release adds the JSON extension to the compiled SQLite code.

Added

JSON extension for SQLite

[2.0.2] - 2021-08-07

This release adds testing against Grafana v8.1.0 and fixes an issue with query variables.

Fixed

Query variables can now also be used in Grafana v8.X.X

[2.0.1] - 2021-07-27

This release fixes some long standing issues that prevented the right use of the alerting feature of the plugin even though it was enabled already.

Fixed

Using the $__from and $__to variables for alerting
Fixing a caching bug for the query (for alerting)

[2.0.0] - 2021-05-07

All current Raspberry Pi Zero and 1 models have an ARMv6 architecture. All other models (2 Mod. B v1.2, 3 and 4) have an 64Bit ARMv8 architecture. As only the Raspberry Pi 2 Mod. B has an ARMv7 architecture this is not used as default anymore. The Raspberry Pi 2 Mod. B will require a manual installation and all others will be handled via the Grafana CLI.

Changed

Using ARMv6 instead of ARMv7 as 32Bit ARM default

[1.2.1] - 2021-04-05

Added

More debug level logging from the plugin

Fixed

The type inference of columns in the backend is now ignoring the letter casing

[1.2.0] - 2021-03-31

Added

The response of the plugin includes the final query as metadata and can be checked in the inspector now
Macro unixEpochGroupSeconds:
- replace time columns with an expression to group by
- Allow filling up missing values with NULL

Fixed

return additional time formatted column for time-series formats as normal values (previously they were skipped)

[1.1.0] - 2021-03-27

Added

Experimental support for MacOS (no static linking)

[1.0.3] - 2021-03-26

Fixed

Showing better error messages for certain fail conditions of the plugin health check (e.g. permission error)

[1.0.2] - 2021-03-23

Fixed

Fixed bug preventing using query variables when SQLite is the default datasource (<= Grafana 7.4)

[1.0.1] - 2021-03-21

Added

Enabled the alerting feature for the plugin (no code change)

[1.0.0] - 2021-03-13

No breaking change was introduced but due to code stability the first major version is released.

Fixed

variables like $__interval and $__interval_ms are supported now

[0.2.7] - 2021-01-07

Changed

Changing plugin name to SQLite
added category to plugin.json for better grouping on the Grafana homepage
updated Readme after first official release of plugin on Grafana homepage

[0.2.6] - 2021-01-03

Added

Documentation about the time conversion in the README and in the query editor help text.

[0.2.5] - 2020-12-19

Fixed

Correct handling of "NUMERIC" columns with mixed data (e.g. float and integer)

[0.2.4] - 2020-12-13

Added

Added option to explicitly convert backend data frame to time series:
- This requires the initial data frame to be in a Long Format
- The resulting time series consists of one data frame per metric