README
¶
Pomegranate 
Pomegranate is a tool for creating and running schema migrations for the
Postgres database, emphasizing safety and transparency. All migrations are
run in transactions that will be automatically rolled back if an exception is
raised, ensuring the database is not left in a half-migrated state. Further, the
.sql migration files created by Pomegranate are the exact SQL that will be run
at migration time; you will get the same result whether you call pmg forward,
or feed a forward.sql file to psql, or attach the forward.sql file to a change
control ticket and have your DBA run it. Postgres's best-in-class support for
transactional
DDL
makes this safety and transparency possible.
Go projects can also use pmg to convert their .sql migrations into a migrations.go file that
will be compiled into their project and run with the pomegranate package.
Installation
For now, pomegranate has to be built from source:
$ go install github.com/nav-inc/pomegranate/pmg
Usage
You can use Pomegranate in two ways:
- As a standalone command line tool (
pmg). - As a library (
github.com/nav-inc/pomegranate) in your own Go project.
Using pmg
Create initial migration
Use the pmg init command to create your first migration, which will be
responsible for creating the migration_state table. It will create the
migration in the current directory, or you can specify a different one with the
--dir option. You can also use the --ts option to use timestamps.
$ pmg init
Migration stubs written to 00001_init
The 00001_init directory should now exist, and contain forward.sql and
backward.sql files. You don't need to edit these initial migrations.
Create more migrations
Migrations containing your own custom changes should be made with the pmg new
command:
$ pmg new add_customers_table
Migration stubs written to 00002_add_customers_table
Note that the 00002 prefix has been prepended to the name you provided.
Migrations are run in the order they appear in the file system. The auto
numbering ensures that this ordering is consistent. You can also use the --ts
option to use timestamps.
As with init, the new command creates forward.sql and backward.sql
files. Unlike init, these are just stubs. You will need to edit these files
and add your own commands (e.g. CREATE TABLE...). The stub files try to make
it obvious where your commands should go:
$ cat 00002_add_customers_table/forward.sql
BEGIN;
-- vvvvvvvv PUT FORWARD MIGRATION CODE BELOW HERE vvvvvvvv
SELECT 1 / 0; -- delete this line
-- ^^^^^^^^ PUT FORWARD MIGRATION CODE ABOVE HERE ^^^^^^^^
INSERT INTO migration_state(name) VALUES ('00002_add_customers_table');
COMMIT;
The SELECT 1 / 0; line in the stub is a safeguard against accidentally running
an empty migration. You should replace it with your own commands.
Be sure to also add the necessary commands to backward.sql to safely roll back
the changes in forward.sql, in case you decide they were a bad idea.
For migrations that require statements to be run outside of transactions, the
forward.sql and backward.sql files can be broken up into forward_n.sql and
backward_n.sql, where n is a member of the integer sequence starting at 1.
The migrations will be executed sequentially, starting with forward_1.sql or
backward_1.sql. See a multi-file migration example below.
Run migrations
Use the forward command to run all migrations not yet recorded in the
migration_state table. Pomegranate will connect to the database specified
in the DATABASE_URL environment variable, or you can supply a database URL
with the --dburl option.
$ pmg forward
Connecting to database 'readme' on host ''
Forward migrations that will be run:
00001_init
00002_add_customers_table
Run these migrations? (y/n) y
Running 00001_init... Success!
Running 00002_add_customers_table... Success!
Done
If you don't want to run all the migrations, you can use the forwardto command
instead:
$ pmg forwardto 00001_init
Connecting to database 'readme' on host ''
Forward migrations that will be run:
00001_init
Run these migrations? (y/n) y
Running 00001_init... Success!
Done
If a migration fails, DON'T PANIC. Your database should still be in the same
state it was in before that forward.sql script was executed. (Unless you put
commands outside the BEGIN and COMMIT lines.) Fix the problem in your
script, and run pmg forward again.
Roll back migrations
Rolling back is done with the backwardto command. This will run the
backward.sql file for all migrations that have already been run, up to and
including the one specified in the command.
$ pmg backwardto 00002_add_customers_table
Connecting to database 'readme' on host ''
Backward migrations that will be run:
00002_add_customers_table
Run these migrations? (y/n) y
Running 00002_add_customers_table... Success!
Done
Unlike going forward, pmg does NOT provide a backward command that will
migrate all the way back. You must use backwardto and provide an explicit
migration name.
View migration state
The state command will show all migrations recorded in the
migration_state table:
$ pmg state
Connecting to database 'readme' on host ''
NAME | WHEN | WHO
00001_init | 2018-02-11 20:48:51.827197 -0700 MST | postgres
Using the pomegranate package in Go
If your project is written in Go, Pomegranate may also be integrated into your project so that migrations can be included inside your binary program and executed by it.
The full public interface is documented at https://godoc.org/github.com/nav-inc/pomegranate.
Using embed Package
The embed package was added in go1.16, and is a really useful way to embed files inside your go programs.
To use this method, you will need to package your migrations (eg: the folder full of number-prefixed folders)
similar to the following.
//go:embed path/to/migrations
var embedded embed.FS
var migrationFS = FromEmbed(embedded, "path/to/migrations")
...
migrations, err := pomegranate.ReadMigrationFS(migrationFS)
This method completely overrides the need for the deprecated method below. It will always pick up files and directories that live inside the "path/to/migrations" folder at compile time.
Ingest migrations (deprecated)
Use the pmg ingest command to turn your .sql migrations into a .go file that
can be compiled into your project. Run it in the same directory as your
migrations, or use the --dir option.
$ pmg ingest
Migrations written to migrations.go
By default, migrations are written to migrations.go with package migrations
at the top. You can customize the name of the .go file and the package name
inside it with the --gofile and --package options, respectively.
The file created will have an All variable in it containing all your
migrations.
The file will also have a //go:generate... tag inside it that will allow to to
re-generate your .go file by running go generate in your migrations directory.
Run migrations from your code
Use Pomegranate's MigrateForwardTo function to run migrations forward. It
takes four arguments:
- the name that you want to migrate to
- a DB connection
- your ingested migrations
- a boolean flag indicating whether to ask for "y/n" confirmation on the command line
pomegranate.MigrateForwardTo(name, db, migrations.All, true)
MigrateBackwardTo and GetMigrationState functions are also available.
A complete example
Here's the complete file layout of an extremely simple project that uses Pomegranate:
$ tree
.
├── migrations
│ ├── 00001_init
│ │ ├── backward.sql
│ │ └── forward.sql
│ ├── 00002_add_customers_table
│ │ ├── backward.sql
│ │ └── forward.sql
│ └── 00003_add_address_column
│ ├── backward.sql
│ └── forward.sql
└── my_awesome_app.go
In that example, my_awesome_app.go looks like this:
package main
import (
"fmt"
"os"
"github.com/nav-inc/my_awesome_app/migrations"
"github.com/nav-inc/pomegranate"
)
func main() {
db, err := pomegranate.Connect(
"postgres://postgres@/awesome_app?sslmode=disable")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
// passing an empty string as name will run to the latest migration
pomegranate.MigrateForwardTo("", db, migrations.All, true)
}
Given the above, you can build my_awesome_app like so:
$ cd migrations
$ pmg ingest
Migrations written to migrations.go
$ cd ..
$ go build
And run it like so:
$ psql -c "CREATE DATABASE awesome_app"
CREATE DATABASE
$ ./my_awesome_app
Connecting to database 'awesome_app' on host ''
Forward migrations that will be run:
00001_init
00002_add_customers_table
00003_add_address_column
Run these migrations? (y/n) y
Running 00001_init... Success!
Running 00002_add_customers_table... Success!
Running 00003_add_address_column... Success!
A multi-file migration example
Here's an example of a project that uses Pomegranate and needs to utilize a multi-file migration:
$ tree
.
├── migrations
│ ├── 00001_init
│ │ ├── backward.sql
│ │ └── forward.sql
│ ├── 00002_add_customers_table
│ │ ├── backward.sql
│ │ └── forward.sql
│ └── 00003_index_customers_table
│ ├── backward_1.sql
| ├── backward_2.sql
| ├── forward_1.sql
│ └── forward_2.sql
└── my_awesome_app.go
Let's take a look at forward_1.sql and forward_2.sql
forward_1.sql:
CREATE INDEX CONCURRENTLY
customer_index
ON
customers (id);
forward_2.sql:
INSERT INTO
migration_state(name)
VALUES
('00003_index_customers_table');
The reason we need to separate our forward migrations into
two files is because we want to utilize the CONCURRENTLY
parameter when creating our index. This parameter will allow
our PostgreSQL to create the index without acquiring a lock
that will prevent our database from performing. This is
especially important in production environments with very
large tables. The downside of the CONCURRENTLY parameter
is that it must be run outside of a SQL transaction. We can
do that by isolating the command in forward_1.sql. Of
course, we want to keep track of our migration state, so
we put that insert statement into forward_2.sql.
Aside from the change addressed above, the rest of the my_awesome_app.go will be the same:
package main
import (
"fmt"
"os"
"github.com/nav-inc/my_awesome_app/migrations"
"github.com/nav-inc/pomegranate"
)
func main() {
db, err := pomegranate.Connect(
"postgres://postgres@/awesome_app?sslmode=disable")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
// passing an empty string as name will run to the latest migration
pomegranate.MigrateForwardTo("", db, migrations.All, true)
}
Given the above, you can build my_awesome_app like so:
$ cd migrations
$ pmg ingest
Migrations written to migrations.go
$ cd ..
$ go build
And run it like so:
$ psql -c "CREATE DATABASE awesome_app"
CREATE DATABASE
$ ./my_awesome_app
Connecting to database 'awesome_app' on host ''
Forward migrations that will be run:
00001_init
00002_add_customers_table
00003_index_customers_table
Run these migrations? (y/n) y
Running 00001_init... Success!
Running 00002_add_customers_table... Success!
Running 00003_index_customers_table... Success!
Documentation
¶
Overview ¶
Package pomegranate implements helper functions and a CLI (pmg) for creating and safely running SQL migrations.
Go projects can use pomegranate to ingest .sql files into a Go binary, and run them from there. See the README.md on Github for examples and explanations.
Index ¶
- func Connect(dial string) (*sql.DB, error)
- func FakeMigrateForwardTo(name string, db *sql.DB, allMigrations []Migration, confirm bool) error
- func FromEmbed(f embed.FS, prefix string) fs.ReadDirFS
- func IngestMigrations(dir, goFile, packageName string, generateTag bool) error
- func InitMigration(dir string) error
- func InitMigrationTimestamp(dir string, timestamp time.Time) error
- func MigrateBackwardTo(name string, db *sql.DB, allMigrations []Migration, confirm bool) error
- func MigrateForwardTo(name string, db *sql.DB, allMigrations []Migration, confirm bool) error
- func NewMigration(dir, name string) error
- func NewMigrationTimestamp(dir, name string, timestamp time.Time) error
- type Migration
- type MigrationLogRecord
- type MigrationRecord
- type OsDir
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Connect ¶
Connect calls sql.Open for you, specifying the Postgres driver and printing the DB name and host to stdout so you can check that you're connecting to the right place before continuing. dial MUST be in URL form.
func FakeMigrateForwardTo ¶
FakeMigrateForwardTo will record all forward migrations that have not yet been run in the migration_state table, up to and including the one specified by `name`, without actually running their ForwardSQL. To fake all un-run migrations, set `name` to an empty string.
func FromEmbed ¶
FromEmbed is able to convert a embed.FS into something more useful.
When you do
//go:embed some/pathlike/to/migrations var embedded embed.FS
You must prefix every call to embedded with the string "some/path/or/migrations", or use this like
//go:embed some/pathlike/to/migrations var embedded embed.FS var migrations = FromEmbed(embedded, "some/pathlike/to/migrations")
Which allows one to directly pass `migrations` into
func IngestMigrations ¶
IngestMigrations reads all the migrations in the given directory and writes them to a Go source file in the same directory. The generateTag argument determines whether the new Go file will contain a "//go:generate" comment to tag it for automatic regeneration by "go generate".
func InitMigration ¶
InitMigration creates a new 00001_init migration in the given directory. This migration will contain the SQL commands necessary to create the migration_state table.
func InitMigrationTimestamp ¶
InitMigrationTimestamp creates a new {timestamp}_init migration in the given directory. This migration will contain the SQL commands necessary to create the `migration_state` table.
func MigrateBackwardTo ¶
MigrateBackwardTo will run backward migrations starting with the most recent in state, and going through the one provided in `name`.
func MigrateForwardTo ¶
MigrateForwardTo will run all forward migrations that have not yet been run, up to and including the one specified by `name`. To run all un-run migrations, set `name` to an empty string.
func NewMigration ¶
NewMigration creates a new directory containing forward.sql and backward.sql stubs. The directory created will use the name provided to the function, prepended by an auto-incrementing zero-padded number.
func NewMigrationTimestamp ¶
NewMigrationTimestamp creates a new directory containing forward.sql and backward.sql stubs. The directory created will use the name provided to the function, prepended by a timestamp formatted with `YYYYMMDDhhmmss` (i.e. `20060102150405`).
Types ¶
type Migration ¶
Migration contains the name and SQL for a migration. Arrays of Migrations are passed between many functions in the Pomegranate source. SeperateForwardStatements runs SQL statements seperately, delinieated by ";"
func ReadMigrationFS ¶
ReadMigrationFs allows one to embed an entire migration folder using the embed package.
go:embed migrations-dir var migrationDir embed.FS ... migrations, err := ReadMigrationFs(migrationDir)
It is expected that migrations is a *directory*, and it will be treated as such"
func ReadMigrationFiles ¶
ReadMigrationFiles reads all the migration files in the given directory and returns an array of Migration objects.
func (Migration) QuotedTemplateBackward ¶
QuotedTemplateBackward returns the BackwardSQL field of the Migration, properly escaped for easy injection into a migrations.go template.
func (Migration) QuotedTemplateForward ¶
QuotedTemplateForward returns the ForwardSQL field of the Migration, properly escaped for easy injection into a migrations.go template.
type MigrationLogRecord ¶
type MigrationLogRecord struct {
ID int `db:"id"`
Time time.Time `db:"time"`
Name string `db:"name"`
Op string `db:"op"`
Who string `db:"who"`
}
MigrationLogRecord represents a specific migration run at a specific point in time. Unlike MigrationRecord, this is an append-only table, showing the complete history of all forward and backward migrations. It is populated automatically by a Postgres trigger created in the init migration.
func GetMigrationLog ¶
func GetMigrationLog(db *sql.DB) ([]MigrationLogRecord, error)
GetMigrationLog returns the complete history of all migrations, forward and backward. If the migration_log table does not exist, it returns an empty list of MigrationLogRecords
type MigrationRecord ¶
type MigrationRecord struct {
Name string `db:"name"`
Time time.Time `db:"time"`
Who string `db:"who"`
}
MigrationRecord provides information on which migrations are currently in effect. An array of MigrationRecords is referred to as a "state" throughout the Pomegranate source. These are treated as a stack; MigrationRecords are added (inserted into the DB) as migrations run forward, and popped off (deleted from the DB) as migrations are run backward.
func GetMigrationState ¶
func GetMigrationState(db *sql.DB) ([]MigrationRecord, error)
GetMigrationState returns the stack of migration records stored in the database's migration_state table. If that table does not exist, it returns an empty list.
type OsDir ¶
type OsDir string
A OsDir is a fs.ReadDirFS that expects to be sitting on the local file system
func (OsDir) Open ¶
Open implements fs.ReadDirFS and just proxies for os.Open. This is not safe with "." and "..", and is intended only for well known paths
func (OsDir) ReadDir ¶
ReadDir implemnts fs.ReadDirFS and just proxies for os.ReadDir This is not safe with "." and "..", and is intended only for well known paths
Source Files
Directories