tern

For complex database objects like views and functions that have inter-dependencies, use Code Packages instead of standard migrations. A code package is a directory containing an install.sql file.

Key Commands:

• tern code install <path>: Directly installs the code package into the database (useful for development). It executes the install.sql file which should contain logic to drop and recreate the objects.
• tern code snapshot <path>: Creates a new migration file that, when run, installs the code package. This is the preferred way to deploy code package changes via standard migrations.

Example Structure:

code/
├── install.sql
├── a.sql
├── b.sql
└── c.sql

install.sql typically uses {{ template "filename.sql" . }} to include the component files.

tern code install path/to/code --config path/to/tern.conf
tern code snapshot path/to/code --migrations path/to/migrations

Migrations are SQL files prefixed by a sequence number (e.g., 001_name.sql).

Creating a migration: Run tern new <name>. Use the -e flag to open the new file in your default editor immediately.

Migration Format: Migrations use a magic comment to separate the 'up' (create) and 'down' (drop) logic:

-- SQL to run when migrating up
CREATE TABLE users (id serial PRIMARY KEY);

---- create above / drop below ----

-- SQL to run when rolling back
DROP TABLE users;

Special Rules:

• Irreversible migrations: If a migration has no rollback (e.g., DROP TABLE), simply omit the magic comment.
• Disabling Transactions: By default, each migration runs in a transaction. To run a migration without a transaction (required for commands like CREATE INDEX CONCURRENTLY), add the magic comment ---- tern: disable-tx ---- at the top of the file.
• Data Interpolation: Access values from the [data] section of tern.conf by prefixing the key with a dot: {{.prefix_name}}.

Tern uses an ini format configuration file named tern.conf. It supports two main sections:

1. [database]: Contains connection settings for the PostgreSQL server (e.g., host, port, database, user, password, sslmode, or a conn_string).
2. [data]: Contains key-value pairs that are available for interpolation into your SQL migrations using Go text/template syntax.

Key Features:

• Templating: The entire file is processed via Go's text/template package. Sprig functions are available.
• Environment Variables: You can use {{env "VAR_NAME"}} to inject environment variables into the config.
• Connection Flexibility: You can use standard PostgreSQL environment variables (like PGSERVICE), program arguments, or the tern.conf file. If all settings are provided via PG* env vars or arguments, a config file is not required.
• SSH Tunneling: You can proxy connections via SSH by providing [ssh-tunnel] settings in the [database] section.

[database]
host = 127.0.0.1
database = tern_test
user = jack
password = {{env "MIGRATOR_PASSWORD"}}
sslmode = prefer

[data]
prefix = foo
app_user = joe

If you need to integrate migrations directly into your Go application, use the github.com/jackc/tern/v2/migrate library. You can define migrations using Go functions (UpFunc, DownFunc), pure SQL (UpSQL, DownSQL), or a combination of both.

To use the library, create a migrate.Migrator instance with a database connection (e.g., from pgx) and a version table name. Then, populate the Migrations slice with migrate.Migration objects and call m.Migrate(ctx).

Note: Ensure the necessary PostgreSQL environment variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD) are set for the connection to work.

	// Note: requires the right mix of environment variables to be set: PGHOST, PGPORT, PGDATABASE,
	// PGUSER, PGPASSWORD.
	conn, _ := pgx.Connect(ctx, "")
	m, _ := migrate.NewMigrator(context.Background(), conn, "my_schema_version")

	m.Migrations = []*migrate.Migration{
		// Migration that uses Go functions.
		{
			Sequence: 1,
			Name:     "1",
			UpFunc: func(ctx context.Context, conn *pgx.Conn) error {
				_, err := conn.Exec(ctx, "CREATE TABLE tmp (id INT);")
				return err
			},
			DownFunc: func(ctx context.Context, conn *pgx.Conn) error {
				_, err := conn.Exec(ctx, "DROP TABLE tmp;")
				return err
			},
		},
		// Migration that uses SQL.
		{
			Sequence: 2,
			Name:     "2",
			UpSQL:    `CREATE TABLE tmp2 (id INT);`,
			DownSQL:  `DROP TABLE tmp2;`,
		},

		// Migration that uses both Go function and SQL.
		{
			Sequence: 3,
			Name:     "3",
			UpFunc: func(ctx context.Context, conn *pgx.Conn) error {
				_, err := conn.Exec(ctx, "CREATE TABLE tmp3 (id INT);")
				return err
			},
			DownSQL: `DROP TABLE tmp3;`,
		},
	}

	_ = m.Migrate(ctx)