Repository: elixir-ecto/ecto_sql
Pull request #531 (2023-06-25 12:41:22)

Add :migration_cast_version_column option
As discussed with @josevalim on Slack, this option allows us to skip the `CAST(version AS int)` that we do in migrations. Some databases, such as Cassandra, don't support `CAST` in `DELETE` queries, which migrations use when rolling back.

I'm sure the test could be improved, I just got something together that worked 🙃 


Patch:
diff --git a/lib/ecto/migration.ex b/lib/ecto/migration.ex
index 6908424..169b4b4 100644
--- a/lib/ecto/migration.ex
+++ b/lib/ecto/migration.ex
@@ -210,6 +210,15 @@ defmodule Ecto.Migration do
 
           config :app, App.Repo, migration_repo: App.MigrationRepo
 
+    * `:migration_cast_version_column` - Ecto uses a `version` column of type
+      `bigint` for the underlying migrations table (usually `schema_migrations`). By
+      default, Ecto doesn't cast this to a different type when reading or writing to the database
+      when running migrations. However, some web frameworks store this column as a string.
+      For compatibility reasons, you can set this option to `true`, which makes Ecto
+      perform a `CAST(version AS int)`. This used to be the default behavior up to
+      Ecto 3.10, so if you are upgrading to 3.11+ and want to keep the old behavior,
+      set this option to `true`.
+
     * `:priv` - the priv directory for the repo with the location of important assets,
       such as migrations. For a repository named `MyApp.FooRepo`, `:priv` defaults to
       "priv/foo_repo" and migrations should be placed at "priv/foo_repo/migrations"
diff --git a/lib/ecto/migration/schema_migration.ex b/lib/ecto/migration/schema_migration.ex
index d0a8bb0..ab9a85e 100644
--- a/lib/ecto/migration/schema_migration.ex
+++ b/lib/ecto/migration/schema_migration.ex
@@ -37,7 +37,16 @@ defmodule Ecto.Migration.SchemaMigration do
 
   def versions(repo, config, prefix) do
     {repo, source} = get_repo_and_source(repo, config)
-    {repo, from(m in source, select: type(m.version, :integer)), [prefix: prefix] ++ @default_opts}
+    from_opts = [prefix: prefix] ++ @default_opts
+
+    query =
+      if Keyword.get(config, :migration_cast_version_column, false) do
+        from(m in source, select: type(m.version, :integer))
+      else
+        from(m in source, select: m.version)
+      end
+
+    {repo, query, from_opts}
   end
 
   def up(repo, config, version, opts) do


Test patch:
diff --git a/test/ecto/migration_test.exs b/test/ecto/migration_test.exs
index 1430fa7..3a1b293 100644
--- a/test/ecto/migration_test.exs
+++ b/test/ecto/migration_test.exs
@@ -10,6 +10,7 @@ defmodule Ecto.MigrationTest do
   alias EctoSQL.TestRepo
   alias Ecto.Migration.{Table, Index, Reference, Constraint}
   alias Ecto.Migration.Runner
+  alias Ecto.Migration.SchemaMigration
 
   setup meta do
     config = Application.get_env(:ecto_sql, TestRepo, [])
@@ -117,6 +118,14 @@ defmodule Ecto.MigrationTest do
            %Reference{table: "posts", column: :id, type: :identity}
   end
 
+  test ":migration_cast_version_column option" do
+    {_repo, query, _options} = SchemaMigration.versions(TestRepo, [migration_cast_version_column: true], "")
+    assert Macro.to_string(query.select.expr) == "type(&0.version(), :integer)"
+
+    {_repo, query, _options} = SchemaMigration.versions(TestRepo, [migration_cast_version_column: false], "")
+    assert Macro.to_string(query.select.expr) == "&0.version()"
+  end
+
   test "creates a reference without validating" do
     assert references(:posts, validate: false) ==
       %Reference{table: "posts", column: :id, type: :bigserial, validate: false}