schema

README

Schema Package

This library written in go helps you to manage your database schema. It executes SQL scripts by a given folder. The following operations are provided:

• Upgrade: applies new scripts which has not been executed successfully yet or executes everything from scratch.
• RevertLast: reverts latest script. If you execute more than once, it takes the second latest, third latest and so on.
• Recreate: resets the database by reverting all executed scripts and recreates it from scratch by using upgrade.

It requires Go 1.11 or higher. Earlier versions might work but weren't tested.

Supported Databases

Every SQL database is supported which has a driver for go and which is compatible with the built in package "database/sql" or the package github.com/jmoiron/sqlx. Originally it was developed for the following databases so far:

• sqlite3

Write SQL Schema Script

Each script must have at least an up and a down command represented by the following SQL comments: -- up / -- down. You can skip the down command but beware that revert and recreate are not working. As an example a schema script can look like the following (name: 001_example.sql):

-- up
CREATE TABLE IF NOT EXISTS example(id INTEGER);
CREATE TABLE IF NOT EXISTS something(id INTEGER);

-- down
DROP TABLE IF EXISTS example;
DROP TABLE IF EXISTS something;

Remember the following restrictions to be a valid schema script:

• file ending must be .sql
• all files need to be in the same folder, sub folders are not executed
• files are executed in ascending (descending for revert) order of their filenames. I recommend to prefix files with three or more digits (001, 002, 003, ...) or timestamps like [yyyymmdd] (20190224).

Usage of the Library

Install as Project Dependency

I recommend to include this library to your project with golang dep. If you have dep installed just do

dep ensure -add github.com/rebel-l/schema

Alternately you can get it by go directly by calling

go get -u github.com/rebel-l/schema

Usage: Upgrade

The library makes the usage as simple as possible. It provides a struct Schema containing a database connection. To apply new scripts you only need to call Upgrade() and provide the following parameters:

• path: the path to your SQL scripts
• application version: if you want you can set this value to version of your application to which the not applied scripts belong to. If you want to skip this, use a blank string ""

Upgrade includes also creation of your database. Here is an example:

package main

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
	"github.com/rebel-l/schema"
	log "github.com/sirupsen/logrus"
)

func main() {
	db, err := sqlx.Open("sqlite3", "database.db")
	if err != nil {
		log.Fatal(err)
	}

	s := schema.New(db)
	if err = s.Upgrade("./path_to_your_scripts", "Application Version"); err != nil {
		log.Fatal(err)
	}
}

The interesting part happens in the last 4 lines where we get the schema struct from schema.New and then apply the scripts with s.Upgrade("./path_to_your_scripts", "Application Version").

Instead of sqlx you can use the internal go sql or any other which follows the store.DatabaseConnector interface delivered with this package.

Usage: Revert

Regarding the example from the chapter before to revert the latest changes is very similar

package main

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
	"github.com/rebel-l/schema"
	log "github.com/sirupsen/logrus"
)

func main() {
	db, err := sqlx.Open("sqlite3", "database.db")
	if err != nil {
		log.Fatal(err)
	}

	s := schema.New(db)
	if err = s.RevertLast("./path_to_your_scripts"); err != nil {
		log.Fatal(err)
	}
}

The only line which has changed is s.RevertLast("./path_to_your_scripts"). You have also the option to revert all scripts with s.RevertAll("./path_to_your_scripts") or just a number of scripts with s.RevertN("./path_to_your_scripts", 3).

Usage: Recreate

As you can imagine from the examples above recreate the database is no big deal

package main

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
	"github.com/rebel-l/schema"
	log "github.com/sirupsen/logrus"
)

func main() {
	db, err := sqlx.Open("sqlite3", "database.db")
	if err != nil {
		log.Fatal(err)
	}

	s := schema.New(db)
	if err = s.Recreate("./path_to_your_scripts", "Application Version"); err != nil {
		log.Fatal(err)
	}
}

Usage with Progress Bar

Optional you can show a progress bar on the command line. All you need to do is calling the method WithProgressBar() before executing anything

package main

import (
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
	"github.com/rebel-l/schema"
	log "github.com/sirupsen/logrus"
)

func main() {
	db, err := sqlx.Open("sqlite3", "database.db")
	if err != nil {
		log.Fatal(err)
	}

	s := schema.New(db)
	s.WithProgressBar()
	if err = s.Upgrade("./path_to_your_scripts", "Application Version"); err != nil {
		log.Fatal(err)
	}
}

Contributing to this Package

You are welcome to contribute to this repository. Please ensure that you created an issue and push your changes in a feature branch.

Setup Local Environment

At first you need to clone this repository and have go installed. To get all the other necessary stuff just run

./scripts/tools/setup.sh

NOTE: works also with Windows using Git Bash.

The script installs:

• dep (and all necessary go packages)
• gometalinter (including all linters)
• goconvey
• git hooks

Documentation

Overview

Package schema provides a library to organize and deploy your database schema

Index

• type Applier
• type Progressor
• type Schema
  • func New(db store.DatabaseConnector) Schema
  • func (s *Schema) Recreate(path string, version string) error
  • func (s *Schema) RevertAll(path string) error
  • func (s *Schema) RevertLast(path string) error
  • func (s *Schema) RevertN(path string, numOfScripts int) error
  • func (s *Schema) Upgrade(path string, version string) error
  • func (s *Schema) WithProgressBar()
• type Scripter

Types

type Applier

type Applier interface {
	ApplyScript(fileName string) error
	RevertScript(fileName string) error
	Init() error
	ReInit() error
}

Applier provides methods to apply sql script to database.

type Progressor

type Progressor interface {
	Increment() *pb.ProgressBar
	Finish() *pb.ProgressBar
}

Progressor provides methods to steer a progress bar.

type Schema

type Schema struct {
	Scripter Scripter
	Applier  Applier
	// contains filtered or unexported fields
}

Schema provides commands to organize your database schema.

func New

func New(db store.DatabaseConnector) Schema

New returns a Schema struct.

func (*Schema) Recreate

func (s *Schema) Recreate(path string, version string) error

Recreate reverts all applied scripts and apply them again. Internally it usues RevertAll() and Upgrade().

func (*Schema) RevertAll

func (s *Schema) RevertAll(path string) error

RevertAll reverts the all applied scripts. A path to the sql scripts needs to be provided. It reverts only files with ending ".sql", sub folders are ignored.

func (*Schema) RevertLast

func (s *Schema) RevertLast(path string) error

RevertLast reverts the last applied script. If it is repeatedly called, it reverts every time one script: means if you run it twice it reverts the last two scripts and so on. A path to the sql scripts needs to be provided. It reverts only files with ending ".sql", sub folders are ignored.

func (*Schema) RevertN

func (s *Schema) RevertN(path string, numOfScripts int) error

RevertN reverts the number of n applied scripts. RevertLast() and RevertAll() are just shortcuts to this method. A path to the sql scripts needs to be provided. It reverts only files with ending ".sql", sub folders are ignored. Also the numOfScripts (number of scripts) to reverts needs to be provided. If the number is -1 or greater than the number of files in path it reverts all.

func (*Schema) Upgrade

func (s *Schema) Upgrade(path string, version string) error

Upgrade applies new scripts to the database or if executed the first time applies all. A path to the sql scripts needs to be provided. It applies only files with ending ".sql", sub folders are ignored. The version of your application can be provided too, use empty string to ignore it.

func (*Schema) WithProgressBar

func (s *Schema) WithProgressBar()

WithProgressBar activate the progress bar.

type Scripter

type Scripter interface {
	Add(entry *store.SchemaScript) error
	GetAll() (store.SchemaScriptCollection, error)
	Remove(scriptName string) error
}

Scripter provides methods to manage the access to log of SQL script executions.