Tern is a standalone migration tool for PostgreSQL. It includes traditional migrations as well as a separate optional workflow for managing database code such as functions and views.
go install github.com/jackc/tern/v2@latestTo create a new tern project in the current directory run:
tern initOr to create the project somewhere else:
tern init path/to/projectTern projects are composed of a directory of migrations and optionally a config file. See the sample directory for an example.
Database connection settings can be specified via the standard PostgreSQL environment variables, via program arguments, or in a config file. By default tern will look in the current directory for the config file tern.conf and the migrations.
The tern.conf file is stored in the ini format with two sections,
database and data. The database section contains settings for connection
to the database server.
Values in the data section will be available for interpolation into
migrations. This can help in scenarios where migrations are managing
permissions and the user to which permissions are granted should be
configurable.
If all database settings are supplied by PG* environment variables or program
arguments the config file is not required. In particular, using the PGSERVICE
can reduce or eliminate the need for a configuration file.
The entire tern.conf file is processed through the Go standard
text/template package. Sprig functions
are available.
Example tern.conf:
[database]
# host supports TCP addresses and Unix domain sockets
# host = /private/tmp
host = 127.0.0.1
# port = 5432
database = tern_test
user = jack
password = {{env "MIGRATOR_PASSWORD"}}
# version_table = public.schema_version
#
# sslmode generally matches the behavior described in:
# http://www.postgresql.org/docs/9.4/static/libpq-ssl.html#LIBPQ-SSL-PROTECTION
#
# There are only two modes that most users should use:
# prefer - on trusted networks where security is not required
# verify-full - require SSL connection
# sslmode = prefer
#
# "conn_string" accepts two formats; URI or DSN as described in:
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
#
# This property is lenient i.e., it does not throw error
# if values for both "conn_string" and "host/port/.." are
# provided. In this case, the individual properties will
# override the correspoding part in the "conn_string".
#
# URI format:
# conn_string = postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
# DSN format:
# conn_string = host=localhost port=5432 dbname=mydb connect_timeout=10
# Proxy the above database connection via SSH
# [ssh-tunnel]
# host =
# port = 22
# user defaults to OS user
# user =
# password is not required if using SSH agent authentication
# password =
# keyfile is the path to a SSH key file
# keyfile =
# passphrase for the SSH key file given above or one of the default SSH key files in ~/.ssh
# passphrase =
[data]
prefix = foo
app_user = joe
This flexibility configuration style allows handling multiple environments such as test, development, and production in several ways.
In addition to program arguments, TERN_CONFIG and TERN_MIGRATIONS
environment variables may be used to set the config path and migrations path
respectively.
To create a new migration:
tern new name_of_migrationThis will create a migration file with the given name prefixed by the next available sequence number (e.g. 001, 002, 003). The -e flag can be used to automatically open the new file in EDITOR.
The migrations themselves have an extremely simple file format. They are simply the up and down SQL statements divided by a magic comment.
---- create above / drop below ----Example:
create table t1(
id serial primary key
);
---- create above / drop below ----
drop table t1;If a migration is irreversible such as a drop table, simply delete the magic comment.
drop table widgets;To interpolate a custom data value from the config file prefix the name with a dot and surround the whole with double curly braces.
create table {{.prefix}}config(
id serial primary key
);Migrations are read from files in the migration directory in the order of the numerical prefix. Each migration is run in a transaction.
Any SQL files in subdirectories of the migration directory, will be available for inclusion with the template command. This can be especially useful for definitions of views and functions that may have to be dropped and recreated when the underlying table(s) change.
// Include the file shared/v1_001.sql. Note the trailing dot.
// It is necessary if the shared file needs access to custom data values.
{{ template "shared/v1_001.sql" . }}
);Tern uses the standard Go text/template package so conditionals and other advanced templating features are available if needed. See the package docs for details. Sprig functions are also available.
Migrations are wrapped in a transaction by default. Some SQL statements such as create index concurrently cannot be performed within a transaction. To disable the transaction include the magic comment:
---- tern: disable-tx ----To migrate up to the last version using migrations and config file located in the same directory simply run tern:
tern migrateTo migrate up or down to a specific version:
tern migrate --destination 42To migrate up N versions:
tern migrate --destination +3To migrate down N versions:
tern migrate --destination -3To migrate down and rerun the previous N versions:
tern migrate --destination -+3To use a different config file:
tern migrate --config path/to/tern.jsonTo use a different migrations directory:
tern migrate --migrations path/to/migrationsWhen migrations are created on multiple branches the migrations need to be renumbered when the branches are merged. The
tern renumber command can automatically do this. On the branch with the only migrations to keep at the lower numbers
run tern renumber start. Merge the branches. Then run tern renumber finish.
$ git switch master
Switched to branch 'master'
$ ls
001_create_users.sql
002_add_last_login_to_users.sql
$ git switch feature
Switched to branch 'feature'
$ ls
001_create_users.sql
002_create_todos.sql
# Both branches have a migration number 2.
# Run tern renumber start on the branch with the migrations that should come first.
$ git switch master
Switched to branch 'master'
$ tern renumber start
# Then go to the branch with migrations that should come later and merge or rebase.
$ git switch feature
$ git rebase master
Successfully rebased and updated refs/heads/feature.
$ ls
001_create_users.sql
002_add_last_login_to_users.sql
002_create_todos.sql
# There are now two migrations with the same migration number.
$ tern renumber finish
$ ls
001_create_users.sql
002_add_last_login_to_users.sql
003_create_todos.sql
# The migrations are now renumbered in the correct order.The migration paradigm works well for creating and altering tables, but it can be unwieldy when dealing with database
code such as server side functions and views. For example, consider a schema where view c depends on view b which
depends on view a. A change to a may require the following steps:
cbaabcIn addition to the challenge of manually building such a migration it is difficult to use version control to see the changes in a particular database object over time when its definition is scattered through multiple migrations.
A solution to this is code packages. A code package is a directory with an install.sql file that contains the
instructions to completely drop and recreate a set of database code. The command code install can be used to directly
install a code package (especially useful during development) and the code snapshot command can be used to make a
single migration that installs that code package.
For example given a directory code containing the following files:
-- install.sql
drop schema if exists code cascade;
create schema code;
{{ template "a.sql" . }}
{{ template "b.sql" . }}
{{ template "c.sql" . }}-- a.sql
create view code.a as select ...;-- b.sql
create view code.b as select * from code.a where ...;-- c.sql
create view code.c as select * from code.b where ...;Then this command would install the package into the database.
tern code install path/to/code --config path/to/tern.confAnd this command would create a migration from the current state of the code package.
tern code snapshot path/to/code --migrations path/to/migrationsCode packages have access to data variables defined in your configuration file as well as functions provided by Sprig.
It is recommended but not required for each code package to be installed into its own PostgreSQL schema. This schema could be determined by environment variable as part of a blue / green deployment process.
The env function can be used to read process environment variables.
drop schema if exists {{ env "CODE_SCHEMA" }} cascade;
create schema {{ env "CODE_SCHEMA" }};The Sprig dictionary functions can be useful to call templates with extra parameters merged into the . value.
{{ template "_view_partial.sql" (merge (dict "view_name" "some_name" "where_clause" "some_extra_condition=true") . ) }}Tern includes SSH tunnel support. Simply supply the SSH host, and optionally
port, user, and password in the config file or as program arguments and Tern
will tunnel the database connection through that server. When using a SSH tunnel
the database host should be from the context of the SSH server. For example, if
your PostgreSQL server is pg.example.com, but you only have SSH access, then
your SSH host would be pg.example.com and your database host would be
localhost.
Tern will automatically use an SSH agent or ~/.ssh/id_dsa, ~/.ssh/id_rsa,
~/.ssh/ed25519 and~/.ssh/id_ecdsa if available.
All the actual functionality of tern is in the github.com/jackc/tern/v2/migrate library. If you need to embed migrations into your own application this library can help. If you don't need the full functionality of tern, then a migration generator script as described below may be a easier way of embedding simple migrations.
Sometimes an application or plugin needs to perform migrations but it is not the owner of the database and tern is not available.
The gengen command generates a SQL script that when run against a database will generate a SQL script with the
migrations necessary to bring the database to the latest schema version.
For example, a Go job queue library may need to perform database migrations, but it does not want to require its users
to also use tern. In this case the plugin author would use gengen to create a SQL script that inspects that database
and creates the actual SQL migration script. This script can then be integrated with whatever schema management system
the host application is using.
Usage:
$ tern gengen > generate-migrations.sql
$ psql --no-psqlrc --tuples-only --quiet --no-align -f generate-migrations.sql mydb > migrations.sql
# migrations.sql now contains the commands to migrate mydb to the latest schema version. It can be run directly or
# integrated with another migrate system.Limitations:
To run the tests tern requires two test databases to run migrations against.
tern_test).tern_migrate_test).TERN_TEST_CONN_STRING="host=/private/tmp database=tern_test" MIGRATE_TEST_CONN_STRING="host=/private/tmp database=tern_migrate_test" MIGRATE_TEST_DATABASE=tern_migrate_test go test ./...The projects using the prior version of tern that was distributed as a Ruby Gem are incompatible with the version 1 release. However, that version of tern is still available through RubyGems and the source code is on the ruby branch.
disable-tx option~/.ssh/id_rsa (Miles Delahunty)reset all after every migrationCopyright (c) 2011-2014 Jack Christensen, released under the MIT license