Issue excluding objects from `schema diff`

[dhoizner]

Hello!

I have recently vendored a few migrations (by adding them to the migrations directory and recalculating the hash).

migrate apply works as expected, applying all migrations that were calculated from the schema file as well as the new vendored migrations, however schema diff is outputting statements to create the new objects, regardless of which variations of --exclude i try.

I've tried excluding at various levels, as well as with a direct match to the name and wildards:

❯ atlas schema diff --env="local" --from="file://db/schema.sql" --to="file://db/migrations" --exclude "*.river*.*[type=table]" --exclude "*.river*" --exclude "river*" --exclude "river_leader"
-- Create enum type "river_job_state"
CREATE TYPE "river_job_state" AS ENUM ('available', 'cancelled', 'completed', 'discarded', 'retryable', 'running', 'scheduled');
-- Create "river_job" table
CREATE TABLE "river_job" ("id" bigserial NOT NULL, "state" "river_job_state" NOT NULL DEFAULT 'available', "attempt" smallint NOT NULL DEFAULT 0, "max_attempts" smallint NOT NULL, "attempted_at" timestamptz NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "finalized_at" timestamptz NULL, "scheduled_at" timestamptz NOT NULL DEFAULT now(), "priority" smallint NOT NULL DEFAULT 1, "args" jsonb NULL, "attempted_by" text[] NULL, "errors" jsonb[] NULL, "kind" text NOT NULL, "metadata" jsonb NOT NULL DEFAULT '{}', "queue" text NOT NULL DEFAULT 'default', "tags" character varying(255)[] NOT NULL DEFAULT '{}', PRIMARY KEY ("id"), CONSTRAINT "finalized_or_finalized_at_null" CHECK (((state = ANY (ARRAY['cancelled'::river_job_state, 'completed'::river_job_state, 'discarded'::river_job_state])) AND (finalized_at IS NOT NULL)) OR (finalized_at IS NULL)), CONSTRAINT "kind_length" CHECK ((char_length(kind) > 0) AND (char_length(kind) < 128)), CONSTRAINT "max_attempts_is_positive" CHECK (max_attempts > 0), CONSTRAINT "priority_in_range" CHECK ((priority >= 1) AND (priority <= 4)), CONSTRAINT "queue_length" CHECK ((char_length(queue) > 0) AND (char_length(queue) < 128)));
-- Create index "river_job_args_index" to table: "river_job"
CREATE INDEX "river_job_args_index" ON "river_job" USING gin ("args");
-- Create index "river_job_kind" to table: "river_job"
CREATE INDEX "river_job_kind" ON "river_job" ("kind");
-- Create index "river_job_metadata_index" to table: "river_job"
CREATE INDEX "river_job_metadata_index" ON "river_job" USING gin ("metadata");
-- Create index "river_job_prioritized_fetching_index" to table: "river_job"
CREATE INDEX "river_job_prioritized_fetching_index" ON "river_job" ("state", "queue", "priority", "scheduled_at", "id");
-- Create index "river_job_state_and_finalized_at_index" to table: "river_job"
CREATE INDEX "river_job_state_and_finalized_at_index" ON "river_job" ("state", "finalized_at") WHERE (finalized_at IS NOT NULL);
-- Create "river_leader" table
CREATE TABLE "river_leader" ("elected_at" timestamptz NOT NULL, "expires_at" timestamptz NOT NULL, "leader_id" text NOT NULL, "name" text NOT NULL, PRIMARY KEY ("name"), CONSTRAINT "leader_id_length" CHECK ((char_length(leader_id) > 0) AND (char_length(leader_id) < 128)), CONSTRAINT "name_length" CHECK ((char_length(name) > 0) AND (char_length(name) < 128)));
-- Create "river_migration" table
CREATE TABLE "river_migration" ("id" bigserial NOT NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "version" bigint NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "version" CHECK (version >= 1));
-- Create index "river_migration_version_idx" to table: "river_migration"
CREATE UNIQUE INDEX "river_migration_version_idx" ON "river_migration" ("version");

thanks!

[rotemtam]

Why not add these resources to the desired state of your database ?

[dhoizner]

Why not add these resources to the desired state of your database ?

If these were objects that I'm defining, I'd be all for that, but these resources are part of a third-party dependency. I can infer the current state of the resources from the migration files, but it would be safer to be able to just vendor the migrations themselves rather than backing the desired state out of them.

Am I doing something wrong with the --exclude flag that I'm passing to the schema diff command?

[rotemtam]

If the resources are created by your migrations they should be in your desired state.

If your schema relies on external resources existing , consider providing them using docker baseline:

https://atlasgo.io/concepts/dev-database#baseline-schema

Excluding is also a way, but if you now need to add a million flags to not break your project I would consider if it's the way to go

[dhoizner]

If the resources are created by your migrations they should be in your desired state.

If your schema relies on external resources existing , consider providing them using docker baseline:

https://atlasgo.io/concepts/dev-database#baseline-schema

Excluding is also a way, but if you now need to add a million flags to not break your project I would consider if it's the way to go

I appreciate the replies, and will give both including the extra resources as part of the desired state as well as the baseline-schema approach another look, but I guess my real question is why the resources are still showing up as changed when running schema diff despite being specified as exclusions? Am I not using the flag correctly?

[jakobo]

I'm running into this on neon. It appears Atlas cannot recognize its own schema tables. I attempted to exclude them from migrations, but they are still picked up. This results in atlas deleting its own revision history.

env "dev" {
  src = "file://schema.pg.hcl"
  url = "${local.dot_env.PG_DATABASE_URL}"
  dev = "docker://postgres/15/dev?search_path=public"
  // neon migrations require us to opt out of the schema revisions table & schema or it'll constantly show as dirty
  exclude = [ "atlas_schema_revisions.*", "*.atlas_schema_revisions" ]
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

Output:

-- Planned Changes:
-- Drop schema named "atlas_schema_revisions"
DROP SCHEMA "atlas_schema_revisions" CASCADE;

Obviously dropping the schema revisions table would be a Very Bad Thing ™️

[a8m]

Hey! The issue is with Neon - it doesn’t respect the Postgres search_path parameter. See: https://github.com/ariga/atlas/issues/2509#issuecomment-1951443925

[jakobo]

Hey! The issue is with Neon - it doesn’t respect the Postgres search_path parameter. See: #2509 (comment)

Yes, I'm aware of that issue. However, Atlas keeps wanting to delete itself and I can't tell atlas to ignore itself with the exclude options, which again, is a Very Bad Thing. The root issue stands, exclude seems broken as its not excluding anything.

[a8m]

Atlas does not have special handling to its tables/schemas. I think you missed my point above, since Neon does not respect the search_path, that means you should not use it in the dev-url.

If you still need help, please use the #support channel in our Discord server: https://discord.com/channels/930720389120794674/1126161701608357949

[a8m]

 exclude = [ "atlas_schema_revisions.*", "*.atlas_schema_revisions" ]

For non-schema connections (i.e., without search_path), these patterns exclude all resources inside the atlas_schema_revisions schema, and all sub-resources (like tables) that named as atlas_schema_revisions. If you want to exclude schemas, use: atlas_schema_revisions.

[GAlexIHU]

Did the recommendations work for you @dhoizner @jakobo ? I'm facing a similar issue I think.

I created a custom migration file as the schema driver (ent) we're using doesn't support functional indexes:

-- Custom index for entitlement activation order
CREATE INDEX entitlements_activation ON entitlements (COALESCE(active_from, created_at));

My atlas.hcl looks like this:

env "local" {
  src = "${local.schema_src}"

  migration {
    dir = "${local.migrations_dir}"
    format = "${local.migrations_format}"
  }

  // https://atlasgo.io/versioned/diff#exclude-objects
  exclude = [
    // As schema is present in search path I believe these top 2 should be the correct values
    "entitlements.entitlements_activation",
    "*.entitlements_activation[type=index]",
    // But lets try without the table prefix too, maybe...
    "entitlements_activation",
    "entitlements_activation[type=index]",    
    // And lets try with the schema matching too....
    "*.entitlements.entitlements_activation",
    "*.*.entitlements_activation[type=index]",
  ]

  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }

  url = "${local.local_url}"

  // Define the URL of the Dev Database for this environment
  // See: https://atlasgo.io/concepts/dev-database
  dev = "docker://postgres/15/dev?search_path=public"

  lint {
    // Lint the effects of the 100 latest migration files
    latest = 100
  }
}

locals {
    // Define the directory where the schema definition resides.
    schema_src = "ent://openmeter/ent/schema"
    // Define the initial migration timestamp
    init_migration_ts = "20240826120919"
    // Define the directory where the migrations are stored.
    migrations_dir = "file://tools/migrate/migrations"
    // We use golang-migrate
    migrations_format = "golang-migrate"
    // Define common connection URLs
    local_url = "postgres://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable"
    ci_url = "postgres://postgres:postgres@postgres:5432/postgres?search_path=public&sslmode=disable"
}

lint {
    non_linear {
        error = true
    }

    destructive {
        error = false
    }

    data_depend {
        error = true
    }

    incompatible {
        error = true
    }
}

No matter what exclude pattern I've tried, atlas migrate --env local diff always drops the custom index... Any thoughts what I could be doing wrong?

[dhoizner]

Did the recommendations work for you @dhoizner @jakobo ? I'm facing a similar issue I think.

I created a custom migration file as the schema driver (ent) we're using doesn't support functional indexes:

-- Custom index for entitlement activation order
CREATE INDEX entitlements_activation ON entitlements (COALESCE(active_from, created_at));

My atlas.hcl looks like this:

env "local" {
  src = "${local.schema_src}"

  migration {
    dir = "${local.migrations_dir}"
    format = "${local.migrations_format}"
  }

  // https://atlasgo.io/versioned/diff#exclude-objects
  exclude = [
    // As schema is present in search path I believe these top 2 should be the correct values
    "entitlements.entitlements_activation",
    "*.entitlements_activation[type=index]",
    // But lets try without the table prefix too, maybe...
    "entitlements_activation",
    "entitlements_activation[type=index]",    
    // And lets try with the schema matching too....
    "*.entitlements.entitlements_activation",
    "*.*.entitlements_activation[type=index]",
  ]

  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }

  url = "${local.local_url}"

  // Define the URL of the Dev Database for this environment
  // See: https://atlasgo.io/concepts/dev-database
  dev = "docker://postgres/15/dev?search_path=public"

  lint {
    // Lint the effects of the 100 latest migration files
    latest = 100
  }
}

locals {
    // Define the directory where the schema definition resides.
    schema_src = "ent://openmeter/ent/schema"
    // Define the initial migration timestamp
    init_migration_ts = "20240826120919"
    // Define the directory where the migrations are stored.
    migrations_dir = "file://tools/migrate/migrations"
    // We use golang-migrate
    migrations_format = "golang-migrate"
    // Define common connection URLs
    local_url = "postgres://postgres:postgres@localhost:5432/postgres?search_path=public&sslmode=disable"
    ci_url = "postgres://postgres:postgres@postgres:5432/postgres?search_path=public&sslmode=disable"
}

lint {
    non_linear {
        error = true
    }

    destructive {
        error = false
    }

    data_depend {
        error = true
    }

    incompatible {
        error = true
    }
}

No matter what exclude pattern I've tried, atlas migrate --env local diff always drops the custom index... Any thoughts what I could be doing wrong?

I ended up adding the objects to my schema and updating the desired state when vendoring new migrations from updates.

[jakobo]

Did the recommendations work for you @dhoizner @jakobo ? I'm facing a similar issue I think.

No, we ended up moving away from Atlas. I never could get the exclude to exclude things.

[a8m]

@GAlexIHU, if you added a custom index to the migration directory that is not part of the desired state, Atlas will suggest removing it as there is a drift between the directory and the desired state.

However, you can exclude objects defined in the migration directory with the following config:

env "dev" {
  migration {
    exclude = ["*.constraint_name"]
  }
}

Note env.exclude is different than env.migration.exclude. Read more: https://atlasgo.io/versioned/diff#exclude-objects

––

BTW, another way to resolve the issue is to add this index to the desired state. See: https://entgo.io/docs/migration/functional-indexes.

[a8m]

Closing as the issue was resolved a few months ago with https://atlasgo.io/versioned/diff#exclude-objects.