schemas

README

Schema Definitions

This directory and its subdirectories holds the scripts for creating TimescaleDB database schemas compatible with the lily data model defined by the structs in the models package.

The data model and the corresponding database schemas are versioned using a major version number plus a patch number. For example 0.28. Schemas with different major versions are not compatible with one another. Manual data migration is required to transition from one major version to another.

Patches are applied on top of the base schema for a major version and contain only additive, non-breaking changes with no data migration. This ensures patches are safe and can be applied by lily automatically. Some examples of additive, non-migration patches are adding a new table or view or adding field comments.

Patches must be fully backwards compatible so that a database can be upgraded by a new version of lily without breaking an older version of lily that is using the same major schema version.

Changes that are not suitable for patches include adding an index or column (even if nullable), changing a column type (may require long migrations and database unavailability), removing a table, renaming a table or column.

Once a table has been defined in a schema its structure should be considered immutable. Changes that would require modification of a table's schema should instead create a new table.

Schema Directories

Each major version of the database schema is contained in its own Go package in a subdirectory prefixed with v, for example v0, v1, v2 etc.

This package must export a string variable called Base which contains the base sql that is executed when the schema is being created initially. The package must also register its major version number by calling schemas.RegisterSchema which allows Visor to detect the most recent schema available.

Additionally the package may contain migration scripts for applying patches onto the base schema over time. The migration scripts are registered with a collection called Patches which must be exported.

Migrations are Go source files named after the patch version they migrate to plus a short tag. For example,2_visor_initial.go is the migration to patch 2 of the schema.

Each migration consists of an init() function with a single call to patches.Register with two arguments:

• the sequence number of the migration. The highest of these becomes the current patch number.
• a set of up statements that migrate the schema up from the previous version

The latest patch version is defined to be the highest version used by a migration in this directory.

The patch in use by the database is held in a table called gopg_migrations. This records a complete history of the schema including both up and down migrations. The most recent entry in this table is known as the schema patch version

The major version is 0 unless a table called visor_version exists with a single row containing the major version number. A new major schema should create and populate this table in its base SQL.

Every running instance of Visor now takes a shared advisory lock in the database to indicate their presence. A schema migration requires an exclusive advisory lock which will fail if there are any existing locks already taken. This ensures that migrations are only performed by a single instance of Visor.

When to write a schema migration

A schema migration is required any time:

• a new model is added
• a new view is added
• a field is added to a model

How to write a schema migration

The next patch version will be one higher than the highest patch version listed in this directory. Every migration, no matter how small, increments the patch version.

Do not modify existing migration files

1. Make the required changes to the Go models.
2. Ensure your test database is on the latest schema version (run lily migrate --latest)
3. Run the TestSchemaIsCurrent test in the storage package to compare the models to the current database schema. This will log the CREATE TABLE ddl for any altered tables.
4. Create a new migration file using the next patch version as a prefix.
5. Add all the statements required to migrate the schema from the previous patch to an up variable.
6. Optionally, add all the statements required to migrate the schema to the previous patch to a down variable.
7. Call patches.Register with your patch number and sql statements.
8. Run the migration by running lily migrate --to <new-version>
9. Test the migration is compatible by using lily migrate

Documentation

Index

• Variables
• func RegisterSchema(major int)
• type Config

Variables

var LatestMajor = 0

Functions

func RegisterSchema

func RegisterSchema(major int)

Types

type Config

type Config struct {
	SchemaName string // name of the postgresql schema in which any database objects should be created
}