Migrating from another migration tool

[leg100]

I'm attempting to implement tern for some open source software I maintain. The software currently uses goose for its migrations. There may well be users with deployments of the software using different versions, with their databases not necessarily migrated to the latest version.

My naive thought was to copy across the goose's equivalent schema version table in the first tern migration:

-- Migrate migration system from goose to tern

-- old goose migrations table - this may already exist depending on deployment
create table if not exists goose_db_version(
    id integer not null
);

-- copy migrations from goose to tern
with old as (
    select max(id) as target_version
    from goose_db_version
    where is_applied = 't'
)
update schema_version
set version = old.target_version
from old;

But tern migrate attempts to perform the existing migrations nonetheless, presumably because the first thing tern does is read schema_version and record the current version and any updates thereafter are ignored.

I'm thinking using tern itself to do this is a bad idea, and instead the only approach is to manually populate schema_version before invoking tern.

I'd rather avoid doing that. Migrating migration tools isn't an unusual thing to do, one would think, and others have presumably faced this same problem.

[jackc]

But tern migrate attempts to perform the existing migrations nonetheless, presumably because the first thing tern does is read schema_version and record the current version and any updates thereafter are ignored.

I'm thinking using tern itself to do this is a bad idea, and instead the only approach is to manually populate schema_version before invoking tern.

You are correct. tern only checks schema_version at the beginning of a migration run.

I'd rather avoid doing that. Migrating migration tools isn't an unusual thing to do, one would think, and others have presumably faced this same problem.

I don't have a definite solution to this, but I have a few possibilities.

---

Create one last migration in goose that creates the tern schema_version table and sets version = 1. Then create the first tern migration and have it contain all the previous goose migrations. The second migration would drop the goose migration table (if exists). Upgrading users would have to run the goose migration one last time, then tern. But new users would only need tern.

---

You could create a matching tern migration for each goose migration. These migrations could each check for the goose migration table and version and conditionally run. You would need to use a DO block for conditionals. It might be a little tricky to write, depending on how many migrations you already have and how complicated they are, but this would avoid the final goose migration.

[leg100]

Thank you for your suggestions. I think I favour the first over the second. Declare a breaking change along with upgrade notes: "if upgrading, must upgrade from at least 1.3.x before deploying 2.0.0", or something to that effect.

I'll close the issue and provide an update later on with how it went.