Do I need a Ecto.Migrator Wrapper? Using autocommit off breaks the schema migrations version control

[Macnolo0x7D4]

Hi there,

I'm working with an Oracle 21 XE database and aiming to utilize Elixir/Phoenix/Ecto in this project. I'm attempting to implement this driver to connect to the Oracle database, but I'm encountering some difficulties.

The first issue I noticed is that transactions aren't atomic. To address this, I've disabled autocommit and wrapped all CRUD operations within explicit transactions. However, I've encountered another problem when running migrations. The schema_migrations table isn't being updated due to an internal transaction that isn't closed when inserting the new version.

I've tried creating a wrapper for the Ecto migrator that adds a COMMIT query post-migration. Nevertheless, this approach fails when the pool size exceeds one, as different Repo instances might not query the same connection, leading to a mismatch in transaction contexts.

My query is: What's the optimal solution to this problem? Should I permanently disable autocommit or restrict the connection pool to a maximum size of one or if I really need a Ecto.Migrator wrapper?

[vstavskyi]

Oracle does not support transactional DDL.
For DML commands, like insert and update schema_migrations, autocommit should be enabled.
You can try to roll your DDL back by calling:
$ mix ecto.rollback --step 1
Ecto.Migration