SQL store and migrations

pkg/services/sqlstore/ is the legacy SQL gateway. It uses xorm (the maintained fork) to talk to SQLite, MySQL, or Postgres. New code is increasingly moving to unified storage / app-platform (see Unified storage) but sqlstore still backs most existing services.

Layout

pkg/services/sqlstore/
├── sqlstore.go                # SQLStore struct, engine init, transactional helpers
├── session.go                 # Per-call session helper
├── migrator/                  # Migration runner
├── migrations/                # All schema migrations (forward-only)
├── searchstore/               # Generic search builder used by dashboards/folders
├── permissions/               # Permission filter SQL
├── stats/                     # SQL queries used by the stats service
├── ...                        # Per-domain helpers (some old, some new)
└── sqlstore_test.go           # Engine + session tests

Database support matrix

• SQLite (default; embedded, single-binary deployment).
• MySQL 5.7+ / MariaDB 10.x.
• Postgres 11+.

Each migration step is responsible for emitting compatible SQL across all three. Helpers like migrator.AddColumnMigration and migrator.RawSQLMigration carry per-dialect branches.

Migrations

Migrations live in pkg/services/sqlstore/migrations/ and are run on every server startup. They are append-only — once a migration has shipped you don't edit it; you write a new one.

// example inside migrations/<area>/migrations.go
mg.AddMigration("add column foo to bar",
    migrator.NewAddColumnMigration(barV1, &migrator.Column{Name: "foo", Type: migrator.DB_NVarchar, Length: 255, Nullable: true}),
)

A few rules of the road:

• Don't ALTER COLUMN if you can avoid it — different DBs differ. Add a new column, copy data, drop the old.
• For data backfills that touch many rows, prefer batched updates and idempotent steps so partial failures can be retried.
• Tests can run migrations against in-memory SQLite very quickly; for cross-DB coverage there are integration tests under pkg/tests/migrations/ and Make targets test-go-integration-postgres / test-go-integration-mysql.

Transactional helpers

SQLStore exposes WithDbSession(ctx, fn) and InTransaction(ctx, fn) that hand a *Session to the callback. Services call SQL in batches inside one of these so that all writes commit atomically.

Newer patterns

Most domain services no longer call sqlstore directly; instead they have a store/ (or database/) package that depends only on the interfaces from sqlstore. This makes it easier to swap to unified storage later — the service consumer doesn't change, only the store implementation.

Cross-cutting concerns

• Connection limits — SQLStore honors [database] max_open_conn, max_idle_conn, conn_max_lifetime from config.
• Slow queries — wrapped with metrics and trace spans via pkg/infra/db/.
• Search — pkg/services/sqlstore/searchstore/ builds dashboard/folder search queries with permission filters, tag filters, and pagination so the dashboards service doesn't write raw SQL.
• Permission filters — permissions/ builds the WHERE clauses used to restrict list results to what the user is allowed to see (RBAC + folder hierarchy).

Where to start

• Add a column to an existing table: write a new migration in the relevant migrations/<area>/ file, regenerate snapshots if any.
• Add a new domain table: add a registration in migrations/<area>/migrations.go and use migrator.NewAddTableMigration(table).WithIndices(...) to emit the table + indices.
• Switch a service from sqlstore to unified storage: introduce a new store interface in the service package and add a unified-storage implementation that talks to pkg/storage/. See ongoing migrations like apps/folder/ for the pattern.