Need a way to specify schema prefix for models

[moredure]

@rotemtam hi, please check #9

[rotemtam]

Hey @moredure

Thanks for the suggestion. A quick question, can you elaborate on why this is needed?

If your migrations are always in the scope of a specific schema, you should use a schema-bound connection to plan and apply them. (i.e connect to mysql://user:pass@host/schema instead of mysql://user:pass@host)

The resulting SQL files will not contain the schema qualifier, and are more flexible since they can be applied on more environments and not bound to a specific name.

In any case, you can use the --qualifier flag when running atlas migrate diff to plan migrations and the resulting SQL will have the prefix you want.

Does this make sense?

[moredure]

I've tried ,--qualifier not works with gorm provider postgres on dev://postgres/latest/dbname, public schema specified then in migration files, but looks like it work when specifing search_path=public

[rotemtam]

Can you share you atlas hcl file?

Can you elaborate on why you need qualified table names? I'm interested in this use case because most of the time it's not needed

[moredure]

pretty basic from the guide in atlas

data "external_schema" "gorm" {
  program = [
    "go",
    "run",
    "-mod=mod",
    "ariga.io/atlas-provider-gorm",
    "load",
    "--path", "./pathtomodels",
    "--dialect", "postgres",
  ]
}
env "gorm" {
  src = data.external_schema.gorm.url
  dev = "docker://postgres/latest/dbname?search_path=public"

  migration {
    dir = "file://migrations_folder?format=golang-migrate"
  }
}

I need to apply migrations to specific schema but the files generated in migrations folder has "public" schema in them, trying to change it to schema I needed
--qualifier works but not as smart as I need, since I cannot specify it in HCL
--qualifier "" also works grants you create tables without "public" in them

[Red-lebowski]

@rotemtam I had the same issue as @moredure and i think its specific to Postgres.

When using Postgres, for some reason Atlas produces migrations with the "public" qualifier unless you specify search_path=public. Although the SQL this provider generates doesn't have the "public" qualifier so i think this is an issue with Atlas and not the provider.
I tested my models using mysql and Atlas didn't add any qualifiers and i didn't need to specify the schema.

Recap

Problem

When using Postgres, Atlas will generate migrations with the "public" qualifier, which breaks things if the target for the migrations isn't the public schema.

Solution

Do what @rotemtam suggested and add search_path=public to your dev url. This will make Atlas produce migrations that are schema-agnostic (to be clear, this step isn't necessary with other DB's). Then specify whichever schema you want to migrate to in the URL for the apply command like below:

atlas migrate apply --url "postgresql://user:password@host:port/db_name?search_path=some_other_schema