Creating PostGres SCHEMA in Migration

DDL statements in Postgres need to be in their own transactions. So its possible you are trying to create the schema and create tables (or do other DML) in the same migration?

If you haven’t already, try to separate the schema creation into its own migration and then anything that uses that schema into other migrations.

(and apologies if you’ve already done that, not totally clear from the above)

Hi @fireproofsocks ,

I quickly set up a new phoenix project from scratch using phx.new.

I simply added two migrations:

20221011044913_schema.exs

defmodule Migrations.Repo.Migrations.Schema do
  use Ecto.Migration

  def change do
    execute("CREATE SCHEMA core")
  end
end

20221011044917_table.exs

defmodule Migrations.Repo.Migrations.Table do
  use Ecto.Migration

  def change do
    create table(:posts, prefix: "core") do
      add(:title, :string, default: "Untitled")
      add(:body, :text)

      timestamps()
    end
  end
end

I did nothing else.

Then I ran:

mix ecto.create
mix ecto.migrate

Everything worked as expected:

image602×374 21.1 KB

Can you please show the full stacktrace for this error?

Can you please also share your Repo config?

Oh man – like always, I was the problem.

I had to isolate the CREATE SCHEMA statement to its own migration so it happened inside its own transaction (per @kip’s suggestion).

But I didn’t realize until this morning that I had duplicated module names in my migrations (the warning flew by too fast for me to notice last night). So that meant that one of my migration modules was essentially ignored (i.e. replaced by another)… and predictably, things did not work. So now that I’ve had a few cups of coffee, I think I’m back on track for this. Thank you!!

…

The longer response here gets a bit philosophical… i.e. when should IaC solutions (like Terraform) step in to set things up vs. when should migrations set things up? E.g. if an AWS RDS instance gets created with an admin user (not a super-admin), whose job is it to set up the user for the app and its permissions?

One solution is put all of that db setup into Terraform, but then the local dev environment might not match the production one. Another solution would be to have the app bootstrap this stuff… but there are some caveats.

The trick is that there would have to be a runtime config that would prevent Ecto from starting normally because the normal Ecto config could be referencing a user/password that don’t exist yet. (i.e. the database wouldn’t have a role with those credentials yet). So… you could start up the app without Ecto and then prompt a user to manually run migrations via Ecto.Migrator.run/3. For this, a human would use the admin username and password and run the migrations (these credentials would be securely stored OUTSIDE the app), which would create the limited user for the app, create schemas, create tables, etc.. Then you could restart the app with Ecto running normally because only then would the app’s limited user exist.

The other part of this approach would be for the day-to-day development to ensure mix ecto.migrate works. Basically, you’d want to make sure the migrations didn’t try to recreate a user that already exists etc.

I might need more coffee, but I think the plan is doable… I’m just not sure if this is an efficient way of solving it.

The more I think about this, the more I think that the app shouldn’t be involved with any of this “DBA” stuff. The app can define schemas and tables but even that gets sticky because the database user that the app uses may or may not have permissions to create schemas etc. It feels like such a Catch-22 or chicken-and-egg problem.

The thing that really makes the case for me is the fact that PostGres doesn’t store the roles and grants in the app’s database – they get stored somewhere OUTSIDE of that database, so dropping and re-creating the database won’t fix any roles/permissions problems. As soon as you start dealing with users/roles/permissions, you fly off the radar of what Ecto can control. To me, that starts getting into DBA territory because it’s beyond what the app needs to know about to do its job.

There are scenarios where DB policies make working with Ecto difficult indeed e.g. there are projects where the user that connects to the DB from the app does not have any access to change the schema – and this is enforced. I am not sure if it was liquibase or something else but I was once briefly involved in a project where an external tool was used to manage migrations. The devs opted to still have Ecto migrations and then have Ecto translate them to raw SQL and put that into the external migrations tool. But it did make sense to be honest, they wanted to make sure that should policy change they can hit the ground running without relying on the 3rd party tool.

So yeah in bigger corps DB management and policies absolutely can get in the way, I’ve seen it happen. Happily there’s a lot of modern tooling out there to help.

That makes sense actually… I’m starting to appreciate the need for tooling within the different layers. At least, for our regular app, dealing with the management and permissions of db users was feeling like scope creep. I can see a scenario where a module (or app) could be devoted to the task of doing the migrations or maybe the migrations that pertain to permissions and users.