pgxschema

package module
v1.0.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jun 20, 2022 License: MIT Imports: 15 Imported by: 0

README

PGX Schema - Embedded Database Migration Library for Go (PGX Driver Version)

An embeddable library for tracking and applying modifications to the PostgreSQL schema from inside a Go application using the jackc/pgx driver.

go.dev reference CircleCI Build Status Go Report Card Code Coverage

NOTE: If you use a database/sql driver instead, please see the related adlio/schema package.

Features

  • Cloud-friendly design tolerates embedded use in clusters
  • Supports migrations in embed.FS (requires go:embed in Go 1.16+)
  • Depends only on Go standard library and jackc/pgx (Note that all go.mod dependencies are used only in tests)
  • Unidirectional migrations (no "down" migration complexity)

Usage Instructions

Create a pgxschema.Migrator in your bootstrap/config/database connection code, then call its Apply() method with your database connection and a slice of *pgxschema.Migration structs.

The .Apply() function figures out which of the supplied Migrations have not yet been executed in the database (based on the ID), and executes the Script for each in alphabetical order by ID.

The []*pgxschema.Migration can be created manually, but the package has some utility functions to make it easier to read .sql files into structs, with the filename as the ID and the contents being the Script.

Using go:embed (requires Go 1.16+)

Go 1.16 added features to embed a directory of files into the binary as an embedded filesystem (embed.FS).

Assuming you have a directory of SQL files called my-migrations/ next to your main.go file, you'll run something like this (the comments with go:embed are relevant).

//go:embed my-migrations
var MyMigrations embed.FS

func main() {
   db, err := pgxpool.Connect() // or pgx.Connect()

   migrator := pgxschema.NewMigrator()
   err = migrator.Apply(
      db,
      pgxschema.FSMigrations(MyMigrations, "my-migrations/*.sql"),
   )
}

The result will be a slice of *pgxschema.Migration{} with the file's name (without the extension) as the ID property, and the entire contents of the file as its Script property. The test-migrations/saas directory provides an example.

Using Inline Migration Structs

If you're running an earlier version of Go, Migration{} structs will need to be created manually:

db, err := pgxpool.Connect() // or pgx.Connect()

migrator := pgxschema.NewMigrator()
migrator.Apply(db, []*pgxschema.Migration{
   &pgxschema.Migration{
      ID: "2019-09-24 Create Albums",
      Script: `
      CREATE TABLE albums (
         id SERIAL PRIMARY KEY,
         title CHARACTER VARYING (255) NOT NULL
      )
      `,
   },
})

Constructor Options

The NewMigrator() function accepts option arguments to customize its behavior.

WithTableName

By default, the tracking table will be placed in the schema from the search_path, and it will be named schema_migrations. This behavior can be changed by supplying a WithTableName() option to the NewMigrator() call.

m := pgxschema.NewMigrator(pgxschema.WithTableName("my_migrations"))

If you need to customize both the schema and the table name, provide two arguments:

m := pgxschema.NewMigrator(pgxschema.WithTableName("my_schema", "my_migrations"))

NOTE: Providing a schema like so does not influence the behavior of SQL run inside your migrations. If a migration needs to CREATE TABLE in a specific schema, that will need to be specified inside the migration itself or configured via the search_path when opening a connection.

It is theoretically possible to create multiple Migrators and to use mutliple migration tracking tables within the same application and database.

Concurrent Execution Support

The pgxschema package utilizes PostgreSQL Advisory Locks to ensure that only one process can run migrations at a time.

This allows multiple processes (not just goroutines) to run Apply on identically configured migrators simultaneously. The first-arriving process will win and perform all needed migrations on the database. All other processses will wait until the lock is released, after which they'll each obtain the lock and run Apply() which should be a no-op based on the first-arriving process' successful completion.

Migration Ordering

Migrations are not executed in the order they are specified in the slice. They will be re-sorted alphabetically by their IDs before executing them.

Rules for Writing Migrations

  1. Never, ever change the ID (filename) or Script (file contents) of a Migration which has already been executed on your database. If you've made a mistake, you'll need to correct it in a subsequent migration.

  2. Use a consistent, but descriptive format for migration IDs/filenames. Consider prefixing them with today's timestamp. Examples:

      ID: "2019-01-01T13:45 Creates Users"
  ID: "2019-01-10T10:33 Creates Artists"

    Do not use simple sequential numbers like ID: "1" with a distributed team unless you have a reliable process for developers to "claim" the next ID.

Contributions

... are welcome. Please include tests with your contribution. We've integrated dockertest to automate the process of creating clean test databases.

Testing Procedure

Testing requires a Docker daemon running on your test machine to spin-up temporary PostgreSQL database servers to test against. Ensure your contribution keeps test coverage high and passes all existing tests.

go test -v -cover

Package Opinions

There are many other schema migration tools. This one exists because of a particular set of opinions:

  1. Database credentials are runtime configuration details, but database schema is a build-time applicaton dependency, which means it should be "compiled in" to the build, and should not rely on external tools.
  2. Using an external command-line tool for schema migrations needlessly complicates testing and deployment.
  3. SQL is the best language to use to specify changes to SQL schemas.
  4. "Down" migrations add needless complication, aren't often used, and are tedious to properly test when they are used. In the unlikely event you need to migrate backwards, it's possible to write the "rollback" migration as a separate "up" migration.
  5. Deep dependency chains should be avoided, especially in a compiled binary. We don't want to import an ORM into our binaries just to get SQL querying support. The pgxschema package imports only standard library packages and the jackc/pgx driver code. (NOTE *We do import ory/dockertest to automate testing on various PostgreSQL versions via docker).

Roadmap

  • Port adlio/schema to a jackc/pgx-friendly version
  • Alter transaction handling to be more PostgreSQL-specific
  • 100% test coverage, including running against multiple PostgreSQL versions
  • Support for creating []*Migration from a Go 1.16 embed.FS
  • Documentation for using Go 1.16 // go:embed to populate Script variables
  • Options for alternative failure behavior when pg_advisory_lock() takes too long. The current behavior should allow for early failure by providing a context with a timeout to WithContext(), but this hasn't been tested.
  • Add a Validate() method to allow checking migration names for consistency and to detect problematic changes in the migrations list

Version History

1.0.0 - Jan 4, 2022

  • Add support for migrations in an embed.FS (FSMigrations(filesystem fs.FS, glob string))
  • Update go.mod to go 1.17
  • Simplify Apply() routine, improve test coverage
  • Security updates to upstream dependencies

0.0.3 - Dec 10, 2021

Security updates to upstream dependencies.

0.0.2 - Nov 18, 2021

Security updates to upstream dependencies.

0.0.1 - Oct 7, 2021

First port from adlio/schema.

License

Copyright (c) 2022 Aaron Longwell, released under the MIT License.

Documentation

Overview

Package pgxschema provides tools to manage database schema changes ("migrations") as embedded functionality inside another application which is using jackc/pgx as its PostgreSQL driver.

Basic usage instructions involve creating a pgxschema.Migrator via the pgxschema.NewMigrator() function, and then passing your pgx.Conn or pgxpool.Pool to its .Apply() method.

See the package's README.md file for more usage instructions.

Index

Constants

View Source 
const DefaultTableName = "schema_migrations"

DefaultTableName defines the name of the database table which will hold the status of applied migrations

Variables

View Source 
var ErrNilDB = fmt.Errorf("Database connection is nil")

ErrNilDB is thrown when the database pointer is nil

View Source 
var ErrNilTx = fmt.Errorf("Database transaction is nil")

ErrNilTx is thrown when a command is run against a nil transaction

Functions

func LockIdentifierForTable 

func LockIdentifierForTable(tableName string) int64

LockIdentifierForTable computes a hash of the migrations table's name which can be used as a unique name for the Postgres advisory lock

func MigrationIDFromFilename 

func MigrationIDFromFilename(filename string) string

MigrationIDFromFilename removes directory paths and extensions from the filename to make a friendlier Migration ID

func QuotedIdent 

func QuotedIdent(ident string) string

QuotedIdent transforms the provided string into a valid, quoted Postgres identifier. This

func QuotedTableName 

func QuotedTableName(schemaName, tableName string) string

QuotedTableName returns the string value of the name of the migration tracking table after it has been quoted for Postgres

func SortMigrations 

func SortMigrations(migrations []*Migration)

SortMigrations sorts a slice of migrations by their IDs

Types

type AppliedMigration 

type AppliedMigration struct {
	Migration

	// Checksum is the MD5 hash of the Script for this migration
	Checksum string

	// ExecutionTimeInMillis is populated after the migration is run, indicating
	// how much time elapsed while the Script was executing.
	ExecutionTimeInMillis int

	// AppliedAt is the time at which this particular migration's Script began
	// executing (not when it completed executing).
	AppliedAt time.Time
}

AppliedMigration represents a successfully-executed migration. It embeds Migration, and adds fields for execution results. This type is what records persisted in the schema_migrations table align with.

type Connection 

type Connection interface {
	Transactor
	Queryer
}

Connection defines the interface for either a *pgxpool.Pool or a *pgx.Conn, both of which can start new transactions and execute queries.

type File 

type File interface {
	Name() string
	Read(b []byte) (n int, err error)
}

File wraps the standard library io.Read and os.File.Name methods

type Logger 

type Logger interface {
	Print(...interface{})
}

Logger is the interface for logging operations of the logger. By default the migrator operates silently. Providing a Logger enables output of the migrator's operations.

type Migration 

type Migration struct {
	ID     string
	Script string
}

Migration is a yet-to-be-run change to the schema. This is the type which is provided to Migrator.Apply to request a schema change.

func FSMigrations added in v1.0.0 

func FSMigrations(filesystem fs.FS, glob string) (migrations []*Migration, err error)

FSMigrations receives a filesystem (such as an embed.FS) and extracts all files matching the provided glob as Migrations, with the filename (without extension) being the ID and the file's contents being the Script.

Example usage: 

FSMigrations(embeddedFS, "my-migrations/*.sql")

func MigrationFromFile 

func MigrationFromFile(file File) (migration *Migration, err error)

MigrationFromFile builds a migration by reading from an open File-like object. The migration's ID will be based on the file's name. The file will *not* be closed after being read.

func MigrationFromFilePath 

func MigrationFromFilePath(filename string) (migration *Migration, err error)

MigrationFromFilePath creates a Migration from a path on disk

func MigrationsFromDirectoryPath 

func MigrationsFromDirectoryPath(dirPath string) (migrations []*Migration, err error)

MigrationsFromDirectoryPath retrieves a slice of Migrations from the contents of the directory. Only .sql files are read

func (*Migration) MD5 

func (m *Migration) MD5() string

MD5 computes the MD5 hash of the Script for this migration so that it can be uniquely identified later.

type Migrator 

type Migrator struct {
	// Logger provides an optional way for the Migrator to report status
	// messages. It is nil by default which results in no output.
	Logger Logger
	// contains filtered or unexported fields
}

Migrator is an instance customized to perform migrations on a particular against a particular tracking table and with a particular dialect defined.

func NewMigrator 

func NewMigrator(options ...Option) *Migrator

NewMigrator creates a new Migrator with the supplied options

func (*Migrator) Apply 

func (m *Migrator) Apply(db Connection, migrations []*Migration) error

Apply takes a slice of Migrations and applies any which have not yet been applied

func (Migrator) GetAppliedMigrations 

func (m Migrator) GetAppliedMigrations(db Queryer) (applied map[string]*AppliedMigration, err error)

GetAppliedMigrations retrieves all already-applied migrations in a map keyed by the migration IDs

func (*Migrator) QuotedTableName added in v1.0.0 

func (m *Migrator) QuotedTableName() string

QuotedTableName returns the dialect-quoted fully-qualified name for the migrations tracking table

type Option 

type Option func(m Migrator) Migrator

Option supports option chaining when creating a Migrator. An Option is a function which takes a Migrator and returns a Migrator with an Option modified.

func WithContext 

func WithContext(ctx context.Context) Option

WithContext builds an option which will set the Migrator's context to the one provided.

func WithLogger 

func WithLogger(logger Logger) Option

WithLogger builds an Option which will set the supplied Logger on a Migrator. Usage: NewMigrator(WithLogger(logrus.New()))

func WithTableName 

func WithTableName(names ...string) Option

WithTableName is an option which customizes the name of the schema_migrations tracking table. It can be called with either 1 or 2 string arguments. If called with 2 arguments, the first argument is assumed to be a schema qualifier (for example, WithTableName("public", "schema_migrations") would assign the table named "schema_migrations" in the the default "public" schema for Postgres)

type Queryer 

type Queryer interface {
	Exec(ctx context.Context, sql string, args ...interface{}) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
}

Queryer defines the interface for either a *pgxpool.Pool, a *pgx.Conn or a pgx.Tx, all of which can execute queries

type Transactor 

type Transactor interface {
	Begin(ctx context.Context) (pgx.Tx, error)
}

Transactor defines the interface for either a *pgxpool.Pool or a *pgx.Conn, both of which can start new transactions.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.