Create multiple tables migrations with same resource

sezaru · April 11, 2024, 6:55pm

In my system I have a table A1 that is only modified by a cron job that runs daily.

At the same time, I have a new requirement that I need to be able to insert new values to table A1 on-demand in some occasions. The issue with this is that the cron job will actually get a “snapshot” of table A1, process it and then replace the old table A1 with the new one. Meaning that if any other change was made to table A1 during that process, that change would be lost when my cron job finishes.

So, to avoid that, I was thinking about creating a second table A2 that is identical to table A1 where I can store new modifications to it. The idea is that any new row to table A1 would be inserted in table A2, and then, during the cron job, these values would be merged back to table A1 and deleted from A2.

Of course, for this to work, I would need to query both tables and “merge” them at runtime. For that I plan to create a view (A3) that is a UNION of both tables.

Now, I do also need to create table A2 migration and it would be great if I can do it without the need to create multiple resources with the same attributes to make this happen.

Basically I think I need one resource that will not run migration but will use my view that merges both table (A3). This resource will be one that the application uses to query, and the actions that would insert new data to it would have a change that would change the table to A2.

But I can’t figure out how to make Ash actually create the two tables (A1 and A2) migrations without me creating two other identical resources triplicating the code.

Is there some way for me to workaround that and force Ash to create the two tables migrations?

zachdaniel · April 11, 2024, 7:13pm

I think you can do this with a single resource and two resources that share a fragment with it. You interact with the one resource, and both writable tables are made by the copies that use the same fragment.

For actions that should write to your view:

use Ash.Resource,
  fragments: [MyResource.Attributes]

postgres do
  table "the_view"
  ...
  # don't run migrations for this resource
  migrate? false
end

# when writing, overwrite the table to `the_table`
create :create do
  change set_context(%{data_layer: %{table: "the_table"}})
end

defmodule MyResource.Attributes do
  use Spark.Dsl.Fragment,
    of: Ash.Resource

  attributes do
    attribute :...
  end
end

defmodule MyResource.Copy1 do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer,
    fragments: MyResource.Attributes

  postgres do
    table "table1"
  end
end

defmodule MyResource.Copy2 do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer,
    fragments: MyResource.Attributes

  postgres do
    table "table1"
  end
end

So Copy1 and Copy2 only exist to trigger the migration generator. You can overwrite the table for individual operations using the example I showed setting the context.

I haven’t actually run that code or done that before, so this is just a rough idea, but I think it could work

sezaru · April 11, 2024, 7:19pm

Thanks @zachdaniel I will test this ASAP and let you know if it worked

sezaru · April 12, 2024, 1:21pm

Hey @zachdaniel one question about this. Is it possible to also create a fragment that accepts some value as input?

Basically my postgres code block has a lot of other code in it (migration_types, custom_indexes, etc) which I wan´t to replicate in all the resources, so I guess it would make sense to also create a fragment for it, but in that case I would need to be able to pass the table name as a input for that fragment somehow.

zachdaniel · April 12, 2024, 1:33pm

Hmm…try just specifying everything but the table, and then set only

postgres do
  table "table"
end

in the resources.

sezaru · April 12, 2024, 1:44pm

It works if I also specify the repo field in the resources and fragment (since it is a required field).

But I get a bunch of warnings from it when compiling the resources:

warning: postgres.polymorphic? is being overwritten from false to false by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_ignore_attributes is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.foreign_key_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.identity_index_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.exclusion_constraint_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.unique_index_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.skip_unique_indexes is being overwritten from [false] to [false] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.simple_join_first_aggregates is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_defaults is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migrate? is being overwritten from true to true by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.repo is being overwritten from Core.Repo to Core.Repo by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_types is being overwritten from [] to [first_name: {:varchar, 75}, middle_name: {:varchar, 75}, last_name: {:varchar, 75}, name_suffix: {:varchar, 30}, full_name: {:varchar, 255}, address_house_number: {:varchar, 25}, address_street_direction: {:varchar, 10}, address_street_name: {:varchar, 100}, address_street_suffix: {:varchar, 25}, address_street_post_direction: {:varchar, 10}, address_unit_prefix: {:varchar, 20}, address_unit_value: {:varchar, 25}, address_city: {:varchar, 50}, address_state: {:varchar, 2}, address_zip: {:varchar, 5}, address_zip_4: {:varchar, 4}, address_legacy: {:varchar, 100}, address_normalized: {:varchar, 281}, address: {:varchar, 284}] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.polymorphic? is being overwritten from false to false by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_ignore_attributes is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.foreign_key_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.identity_index_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.exclusion_constraint_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.unique_index_names is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.skip_unique_indexes is being overwritten from [false] to [false] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.simple_join_first_aggregates is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_defaults is being overwritten from [] to [] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migrate? is being overwritten from true to true by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.repo is being overwritten from Core.Repo to Core.Repo by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

warning: postgres.migration_types is being overwritten from [] to [first_name: {:varchar, 75}, middle_name: {:varchar, 75}, last_name: {:varchar, 75}, name_suffix: {:varchar, 30}, full_name: {:varchar, 255}, address_house_number: {:varchar, 25}, address_street_direction: {:varchar, 10}, address_street_name: {:varchar, 100}, address_street_suffix: {:varchar, 25}, address_street_post_direction: {:varchar, 10}, address_unit_prefix: {:varchar, 20}, address_unit_value: {:varchar, 25}, address_city: {:varchar, 50}, address_state: {:varchar, 2}, address_zip: {:varchar, 5}, address_zip_4: {:varchar, 4}, address_legacy: {:varchar, 100}, address_normalized: {:varchar, 281}, address: {:varchar, 284}] by fragment: Elixir.Core.Pacman.Markets.Entity.Attributes
  (spark 1.1.55) lib/spark/dsl.ex:601: anonymous fn/5 in Spark.Dsl.merge_with_warning/4
  (elixir 1.16.2) lib/keyword.ex:1052: Keyword.do_merge/6
  (spark 1.1.55) lib/spark/dsl.ex:585: anonymous fn/4 in Spark.Dsl.handle_fragments/2
  (stdlib 5.2) maps.erl:199: :maps.merge_with_1/4
  (elixir 1.16.2) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
  (stdlib 5.2) erl_eval.erl:746: :erl_eval.do_apply/7

zachdaniel · April 12, 2024, 2:18pm

Right, because fragments aren’t actually intended to be used to share stuff across resources. You’ll need to use a macro.

defmodule Base do
  defmacro __using__(opts) do
    quote do
      postgres do
        table unquote(opts[:table] || raise "requires a table")
        repo Repo

        ....
      end

       attributes do
         ...
       end
    end
  end
end

Then in your resource, remove the fragment and do

use Base

sezaru · April 12, 2024, 2:29pm

That did it! Thanks!