Grafana uses a database to persist settings between restarts. In fact, if you don’t specify one, Grafana creates a SQLite3 database file on your local disk. This guide explains how to store and retrieve data from the database.
Grafana supports the following databases:
Grafana uses the XORM framework for persisting objects to the database. For more information on how to use XORM, refer to the documentation.
Services don’t use XORM directly. Instead, services use the SQL store, a special type of service that provides an abstraction for the database layer. There are two ways of using the sqlstore
: using sqlstore
handlers, and using the SqlStore
instance.
sqlstore
handlersDeprecated: We are deprecating
sqlstore
handlers in favor of using theSqlStore
object directly in each service. Since most services still use thesqlstore
handlers, we still want to explain how they work.
The sqlstore
package allows you to register command handlers that either store, or retrieve objects from the database. sqlstore
handlers are similar to services:
sqlstore
handlers are command handlers that access the database.sqlstore
handlerDeprecated: Refer to the deprecation note for
sqlstore
handlers.
To register a handler:
myrepo.go
in the sqlstore
package.init
function:func init() {
bus.AddHandler("sql", DeleteDashboard)
}
func DeleteDashboard(cmd *models.DeleteDashboardCommand) error {
return inTransaction(func(sess *DBSession) error {
_, err := sess.Exec("DELETE FROM dashboards WHERE dashboard_id=?", cmd.DashboardID)
return err
})
}
Here, inTransaction
is a helper function in the sqlstore
package that provides a session, that lets you execute SQL statements.
SqlStore
As opposed to a sqlstore
handler, the SqlStore
is a service itself. The SqlStore
has the same responsibility however: to store and retrieve objects, to and from the database.
To use the SqlStore
, inject the SQLStore
in your service struct:
type MyService struct {
SQLStore *sqlstore.SqlStore `inject:""`
}
You can now make SQL queries in any of your command handlers or event listeners:
func (s *MyService) DeleteDashboard(cmd *models.DeleteDashboardCommand) error {
if err := s.SQLStore.WithDbSession(ctx, func(sess *sqlstore.DBSession) error {
_, err := sess.Exec("DELETE FROM dashboards WHERE dashboard_id=?", cmd.DashboardID)
return err
})
}
For transactions, use the WithTransactionalDbSession
method instead.
As Grafana evolves, it becomes necessary to create schema migrations for one or more database tables.
To see all the types of migrations you can add, refer to migrations.go.
Before you add a migration, make sure that you:
Add a migration using one of the following methods:
migrations
package.DatabaseMigrator
for the service.Important: If there are previous migrations for a service, use that method. By adding migrations using both methods, you risk running migrations in the wrong order.
migrations
packageMost services have their migrations located in the migrations package.
To add a migration:
AddMigrations
function, find the addXxxMigration
function for the service you want to create a migration for.addXxxMigration
function, register your migration:DatabaseMigrator
During initialization, SQL store queries the service registry, and runs migrations for every service that implements the DatabaseMigrator interface.
To add a migration:
AddMigration(mg *migrator.Migrator)
method to the service.AddMigration
method, register your migration:func (s *MyService) AddMigration(mg *migrator.Migrator) {
// ...
mg.AddMigration("Add column age", NewAddColumnMigration(table, &Column{
Name: "age",
Type: migrator.DB_BigInt,
Nullable: true,
}))
}