sqlite

README

SQlite

Purpose

This sqlite package provides a basic interface for interacting with the embedded sqlite database used by various InfluxDB services which require storing relational data.

The actual sqlite driver is provided by mattn/go-sqlite3.

Usage

A single instance of SqlStore should be created using the NewSqlStore function. Currently, this is done in the top-level launcher package, and a pointer to the SqlStore instance is passed to services which require it as part of their initialization.

The jmoiron/sqlx package provides a convenient and lightweight means to write and read structs into and out of the database and is sufficient for performing simple, static queries. For more complicated & dynamically constructed queries, the Masterminds/squirrel package can be used as a query builder.

Concurrent Access

An interesting aspect of using the file-based sqlite database is that while it can support multiple concurrent read requests, only a single write request can be processed at a time. A traditional RDBMS would manage concurrent write requests on the database server, but for this sqlite implementation write requests need to be managed in the application code.

In practice, this means that code intended to mutate the database needs to obtain a write lock prior to making queries that would result in a change to the data. If locks are not obtained in the application code, it is possible that errors will be encountered if concurrent write requests hit the database file at the same time.

Migrations

A simple migration system is implemented in migrator.go. When starting the influx daemon, the migrator runs migrations defined in .sql files using sqlite-compatible sql scripts. Records of these migrations are maintained in a table called "migrations". If records of migrations exist in the "migrations" table that are not embedded in the binary, an error will be raised on startup.

When creating new migrations, follow the file naming convention established by existing migration scripts, which should look like 00XX_script_name.up.sql & 00xx_script_name.down.sql for the "up" and "down" migration, where XX is the version number. New scripts should have the version number incremented by 1.

The "up" migrations are run when starting the influx daemon and when metadata backups are restored. The "down" migrations are run with the influxd downgrade command.

In-Memory Database

When running influxd with the --store=memory flag, the database will be opened using the :memory: path, and the maximum number of open database connections is set to 1. Because of the way in-memory databases work with sqlite, each connection would see a completely new database, so using only a single connection will ensure that requests to influxd will return a consistent set of data.

Backup & Restore

Methods for backing up and restoring the sqlite database are available on the SqlStore struct. These operations make use of the sqlite backup API made available by the go-sqlite3 driver. It is possible to restore and backup into sqlite databases either stored in memory or on disk.

Sqlite Features / Extensions

There are many additional features and extensions available, see the go-sqlite3 package docs for the full list.

We currently use the sqlite_foreign_keys and sqlite_json extensions for foreign key support & JSON query support. These features are enabled using build tags defined in the Makefile and .goreleaser config for use in local builds & CI builds respectively.

Documentation

Index

• Constants
• type Migrator
  • func NewMigrator(store *SqlStore, log *zap.Logger) *Migrator
  • func (m *Migrator) Down(ctx context.Context, untilMigration int, source embed.FS) error
  • func (m *Migrator) SetBackupPath(path string)
  • func (m *Migrator) Up(ctx context.Context, source embed.FS) error
  • func (m *Migrator) UpUntil(ctx context.Context, untilMigration int, source embed.FS) error
• type SqlStore
  • func NewSqlStore(path string, log *zap.Logger) (*SqlStore, error)
  • func NewTestStore(t *testing.T) *SqlStore
  • func (s *SqlStore) BackupSqlStore(ctx context.Context, w io.Writer) (rErr error)
  • func (s *SqlStore) Close() error
  • func (s *SqlStore) Flush(ctx context.Context)
  • func (s *SqlStore) RLockSqlStore()
  • func (s *SqlStore) RUnlockSqlStore()
  • func (s *SqlStore) RestoreSqlStore(ctx context.Context, r io.Reader) (rErr error)

Constants

const (
	DefaultFilename = "influxd.sqlite"
	InmemPath       = ":memory:"
)

Types

type Migrator

type Migrator struct {
	// contains filtered or unexported fields
}

func NewMigrator

func NewMigrator(store *SqlStore, log *zap.Logger) *Migrator

func (*Migrator) Down

func (m *Migrator) Down(ctx context.Context, untilMigration int, source embed.FS) error

Down applies the "down" migrations until the SQL database has migrations only >= untilMigration. Use untilMigration = 0 to apply all down migrations, which will delete all data from the database.

func (*Migrator) SetBackupPath

func (m *Migrator) SetBackupPath(path string)

SetBackupPath records the filepath where pre-migration state should be written prior to running migrations.

func (*Migrator) Up

func (m *Migrator) Up(ctx context.Context, source embed.FS) error

func (*Migrator) UpUntil

func (m *Migrator) UpUntil(ctx context.Context, untilMigration int, source embed.FS) error

UpUntil migrates until a specific migration. -1 or 0 will run all migrations, any other number will run up until that. Returns no error untilMigration is less than the already run migrations.

type SqlStore

type SqlStore struct {
	Mu sync.RWMutex
	DB *sqlx.DB
	// contains filtered or unexported fields
}

SqlStore is a wrapper around the db and provides basic functionality for maintaining the db including flushing the data from the db during end-to-end testing.

func NewSqlStore

func NewSqlStore(path string, log *zap.Logger) (*SqlStore, error)

func NewTestStore

func NewTestStore(t *testing.T) *SqlStore

func (*SqlStore) BackupSqlStore

func (s *SqlStore) BackupSqlStore(ctx context.Context, w io.Writer) (rErr error)

BackupSqlStore creates a new temporary database and uses the sqlite backup API to back the database up into the temporary database. It then writes the temporary database file to the writer. Using the sqlite backup API allows the backup to be performed without needing to lock the database, and also allows it to work with in-memory databases. See: https://www.sqlite.org/backup.html

The backup works by copying the SOURCE database to the DESTINATION database. The SOURCE is the running database that needs to be backed up, and the DESTINATION is the resulting backup. The underlying sqlite connection is needed for both SOURCE and DESTINATION databases to use the sqlite backup API made available by the go-sqlite3 driver.

func (*SqlStore) Close

func (s *SqlStore) Close() error

Close the connection to the sqlite database

func (*SqlStore) Flush

func (s *SqlStore) Flush(ctx context.Context)

Flush deletes all records for all tables in the database except for the migration table. This method should only be used during end-to-end testing.

func (*SqlStore) RLockSqlStore

func (s *SqlStore) RLockSqlStore()

RLockSqlStore locks the database using the mutex. This is intended to lock the database for writes. It is the responsibilty of implementing service code to manage locks for write operations.

func (*SqlStore) RUnlockSqlStore

func (s *SqlStore) RUnlockSqlStore()

RUnlockSqlStore unlocks the database.

func (*SqlStore) RestoreSqlStore

func (s *SqlStore) RestoreSqlStore(ctx context.Context, r io.Reader) (rErr error)

RestoreSqlStore replaces the underlying database with the data from r.