Is there any way to use Ecto fragment/1 in config.exs?

Questions / Help
, ,
1

Hey everyone,

I’m wanting to apply a query fragment within the Phoenix application’s config.exs in order to properly pass a default value to the Ecto.Migration runner.

my_app/config/config.exs is setup like so:

config :my_app, MyApp.Repo,
  migration_primary_key: [name: :id, type: :uuid, default: "gen_random_uuid()"],
  migration_foreign_key: [column: :id, type: :uuid]

I’m trying to have the default :primary_key of every new table use UUID and have a default value that generates a random uuid.

And migrating produces this SQL statement in Ecto.Adapter, but it results in an error from the database. (using Postgres)

CREATE TABLE "users" ("id" uuid DEFAULT 'gen_random_uuid()', "name" varchar(255) NOT NULL, "type" integer NOT NULL, "inserted_at" timestamp(0) NOT NULL, "updated_at" timestamp(0) NOT NULL, PRIMARY KEY ("id"))

** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) Invalid UUID: incorrect size

The migration is so very close, but sadly invalid because the default value is being passed as a fixed string but needs to be a Ecto.Query.API.fragment/1.

And sadly due to the config not having any dependencies available to pull from, importing the necessary Module to do this.

    error: module Ecto.Query.API is not loaded and could not be found
    │
    │ import Ecto.Query.API
    │ ^^^^^^^^^^^^^^^^^^^^^
    │
    └─ config/config.exs:10

It would be really awesome to have these migrations automatically have a working default value for primary ids without needing to manually apply them in every new migration.

If there is no way to do this in the Config, does anyone have a galaxy-brained solution for the outcome I’m looking for?

Thanks

2

Hey

Not sure sure which Phoenix version you are using. But, going with the current Phoenix version (1.7.14), I would do the following:

config/config.exs

config :my_app,
  ecto_repos: [MyApp.Repo],
  generators: [timestamp_type: :utc_datetime, binary_id: true]

and for migrations, I would have something like this:

def change do
  create table(:users, primary_key: false) do
    add :id, :binary_id, primary_key: true
    # ...

    timestamps(type: :utc_datetime)
  end
end

This works fine with PostgreSQL:

iex(4)> MyApp.Repo.insert(user_changeset)
[debug] QUERY OK source=“users” db=5.2ms decode=1.0ms queue=2.0ms idle=1621.4ms
INSERT INTO “users” (“name”,“inserted_at”,“updated_at”,“id”) VALUES ($1,$2,$3,$4) [“John Doe”, ~U[2024-11-04 11:11:07Z], ~U[2024-11-04 11:11:07Z], “cc27cf1a-3865-4e41-abf4-064d001994ee”]
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386
{:ok,
%MyApp.Users.User{
meta: ecto.Schema.Metadata<:loaded, “users”>,
id: “cc27cf1a-3865-4e41-abf4-064d001994ee”,
name: “John Doe”,
inserted_at: ~U[2024-11-04 11:11:07Z],
updated_at: ~U[2024-11-04 11:11:07Z]
}}
iex(5)>

3

Hey thanks for the response @psantos!

I’m really just looking to instantiate a default value for the :migration_primary_key at a high configuration level of the application, if possible.

I’m specifically trying to avoid needing to manually specify the ID primary_key field on every new table migration. It’s less about UUID, as I was just using that as an example to showcase the issue I’m facing.

4

But the ID field will be added for you everytime you generate a migration, no?

5

Holy smokes I just figured it out! :exploding_head:

I was looking into the Ecto code that handles migrations and I found this the area that manages the Primary Key Configuration logic.

deps/ecto_sql/lib/ecto/migration.ex:1660

  @doc false
  def __primary_key__(table) do
    case table.primary_key do
      false ->
        false

      true ->
        case Runner.repo_config(:migration_primary_key, []) do
          false -> false
          opts when is_list(opts) -> pk_opts_to_tuple(opts)
        end

      opts when is_list(opts) ->
        pk_opts_to_tuple(opts)

      _ ->
        raise ArgumentError,
              ":primary_key option must be either a boolean or a keyword list of options"
    end
  end

  defp pk_opts_to_tuple(opts) do
    opts = Keyword.put(opts, :primary_key, true)
    {name, opts} = Keyword.pop(opts, :name, :id)
    {type, opts} = Keyword.pop(opts, :type, :bigserial)
    {name, type, opts}
  end

This is the logic block that pulls the migration_primary_key: [] from the configuration file. Pretty straight forward. Pulls out the :name and :type with a default value if the keys are not defined.

So with that found I was curious to see how the remaining opts are being parsed and coerced before the migration runs.

deps/ecto_sql/lib/ecto/migration.ex:1225

  @doc """
  Generates a fragment to be used as a default value.

  ## Examples

      create table("posts") do
        add :inserted_at, :naive_datetime, default: fragment("now()")
      end
  """
  def fragment(expr) when is_binary(expr) do
    {:fragment, expr}
  end

Finding that was WILD! It means we can likely just pass a tuple with the :fragment key in the config file!?

Tested it out and it worked!!!

priv/repo/migrations/create_users.exs

  def change do
    create table(:users) do
      add :name, :string
      add :type, :integer


      timestamps()
    end
  end

Produces this SQL statement during migration.

CREATE TABLE "users" ("id" uuid DEFAULT gen_random_uuid(), "name" varchar(255) NOT NULL, "type" integer NOT NULL, "inserted_at" timestamp(0) NOT NULL, "updated_at" timestamp(0) NOT NULL, PRIMARY KEY ("id"))

Which results in this Postgres table definition with the default value properly set!

my_app_dev=# \d users
                         Table "public.users"
    Column    |            Type             | Collation | Nullable |  Default
--------------+-----------------------------+-----------+----------+------------
 id           | bytea                       |           | not null | gen_random_uuid()
 name         | character varying(255)      |           |          |
 type         | integer                     |           |          |
 inserted_at  | timestamp without time zone |           | not null |
 updated_at   | timestamp without time zone |           | not null |

Absolutely perfect, I’m very stoked to get this knowledge gap figured out. :tada: :tada:

4 Likes
6

Also a pro-tip bad-tip for debugging, if you want to see the raw SQL statements being produced by the Ecto SQL adapter during migrations, you can add some IO.puts in a couple places to get them printed to std-out.

deps/ecto_sql/lib/ecto/adapters.ex:592

  def query(repo, sql, params, opts) when is_atom(repo) or is_pid(repo) do
    IO.puts(sql) # <-- add this
    query(Ecto.Adapter.lookup_meta(repo), sql, params, opts)
  end

  def query(adapter_meta, sql, params, opts) do
    IO.puts(sql) # <-- add this
    sql_call(adapter_meta, :query, [sql], params, opts)
  end

You will need to nuke the _build folder and recompile, but then you will get them printed to the screen when you migrate. Not recommended outside of debugging though.

7

There’s also CLI flags for that on mix ecto.migrate: mix ecto.migrate — Ecto SQL v3.12.1

2 Likes
8

Ahhh dang, that would be a much more preferable method LOLOL Thank you for sharing that. I’ll edit my comment.

9

If you want to fiddle with deps like that you don’t need to nuke _build, you can just run tell mix to recompile that specific dependency.

mix deps.compile ecto_sql
3 Likes