Migration error while executing a column type change on batch-mode using SQLite3

[GabrielRomaoG]

Basically i'm trying to change a column type in a sqlite3 db, i tried to do with batch mode but didn't work.

Feel free to ask for more context or code, thanks for reading.

the code that is getting the error:

def upgrade() -> None:
    with op.batch_alter_table(
        "CategoryEvaluationResult",
        schema=None,
    ) as batch_op:
        batch_op.alter_column(
            "Justification",
            existing_type=sa.VARCHAR(length=300),
            type_=sa.TEXT(),
            existing_nullable=False,
            nullable=True,
        )

The error:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade b02202a6866a -> f1cd533147b0, change justification columns to be nullable
Traceback (most recent call last):
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: near "ALTER": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/gabriel/projects/sales_service_analysis/.venv/bin/alembic", line 10, in <module>
    sys.exit(main())
             ~~~~^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/config.py", line 636, in main
    CommandLine(prog=prog).main(argv=argv)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/config.py", line 626, in main
    self.run_cmd(cfg, options)
    ~~~~~~~~~~~~^^^^^^^^^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/config.py", line 603, in run_cmd
    fn(
    ~~^
        config,
        ^^^^^^^
        *[getattr(options, k, None) for k in positional],
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        **{k: getattr(options, k, None) for k in kwarg},
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/command.py", line 406, in upgrade
    script.run_env()
    ~~~~~~~~~~~~~~^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/script/base.py", line 586, in run_env
    util.load_python_file(self.dir, "env.py")
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 1026, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/home/gabriel/projects/sales_service_analysis/alembic/env.py", line 98, in <module>
    run_migrations_online()
    ~~~~~~~~~~~~~~~~~~~~~^^
  File "/home/gabriel/projects/sales_service_analysis/alembic/env.py", line 92, in run_migrations_online
    context.run_migrations()
    ~~~~~~~~~~~~~~~~~~~~~~^^
  File "<string>", line 8, in run_migrations
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
    ~~~~~~~~~~~~~~~~~^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/alembic/versions/f1cd533147b0_change_justification_columns_to_be_.py", line 24, in upgrade
    op.alter_column(
    ~~~~~~~~~~~~~~~^
        "CategoryEvaluationResult",
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        nullable=True,
        ^^^^^^^^^^^^^^
    )
    ^
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/operations/ops.py", line 1957, in alter_column
    return operations.invoke(alt)
           ~~~~~~~~~~~~~~~~~^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/operations/base.py", line 442, in invoke
    return fn(self, operation)
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/operations/toimpl.py", line 53, in alter_column
    operations.impl.alter_column(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        table_name,
        ^^^^^^^^^^^
    ...<11 lines>...
        **operation.kw,
        ^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/ddl/impl.py", line 281, in alter_column
    self._exec(
    ~~~~~~~~~~^
        base.ColumnNullable(
        ^^^^^^^^^^^^^^^^^^^^
    ...<8 lines>...
        )
        ^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/alembic/ddl/impl.py", line 247, in _exec
    return conn.execute(construct, params)
           ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1527, in _execute_ddl
    ret = self._execute_context(
        dialect,
    ...<4 lines>...
        compiled,
    )
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/gabriel/projects/sales_service_analysis/.venv/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error
[SQL: ALTER TABLE "CategoryEvaluationResult" ALTER COLUMN "Justification" DROP NOT NULL]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

the table:

import ulid
from sqlalchemy import TEXT, ForeignKey, Integer, SmallInteger
from sqlalchemy.orm import Mapped, mapped_column

from src.infra.db.settings.base import Base
from src.infra.db.types.ulid import ULIDBinaryType


class CategoryEvaluationResult(Base):
    __tablename__ = "CategoryEvaluationResult"

    InteractionId: Mapped[ulid.ULID] = mapped_column(
        ULIDBinaryType(),
        ForeignKey("CustomerInteraction.Id"),
        primary_key=True,
    )
    CategoryId: Mapped[int] = mapped_column(
        Integer, ForeignKey("CriteriaCategory.Id"), primary_key=True
    )
    Justification: Mapped[str] = mapped_column(TEXT(), nullable=True)
    Rating: Mapped[int] = mapped_column(SmallInteger(), nullable=True)

Versions.

• OS: WSL2 Ubuntu 24.04
• Python: 3.13.1
• Alembic: 1.14.1
• SQLAlchemy: 2.0.38
• Database: SQLite3

[zzzeek]

alembic's SQLite dialect does not attempt to emit ALTER for a column when a batch migration is used.

I have copied your migration and run it, here's the output:

CREATE TABLE "_alembic_tmp_CategoryEvaluationResult" (
	"InteractionId" INTEGER NOT NULL, 
	"Justification" TEXT, 
	PRIMARY KEY ("InteractionId")
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] ()
INFO  [sqlalchemy.engine.Engine] INSERT INTO "_alembic_tmp_CategoryEvaluationResult" ("InteractionId", "Justification") SELECT "CategoryEvaluationResult"."InteractionId", "CategoryEvaluationResult"."Justification" 
FROM "CategoryEvaluationResult"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] ()
INFO  [sqlalchemy.engine.Engine] 
DROP TABLE "CategoryEvaluationResult"
INFO  [sqlalchemy.engine.Engine] [no key 0.00002s] ()
INFO  [sqlalchemy.engine.Engine] ALTER TABLE "_alembic_tmp_CategoryEvaluationResult" RENAME TO "CategoryEvaluationResult"
INFO  [sqlalchemy.engine.Engine] [no key 0.00003s] ()
INFO  [sqlalchemy.engine.Engine] UPDATE alembic_version SET version_num='405724dfc2ff' WHERE alembic_version.version_num = 'aea55c2fab20'

I think your issue is that the migration you are running is not using batch. the code that's printed in your stack trace does not match your example:

 File "/home/gabriel/projects/sales_service_analysis/alembic/versions/f1cd533147b0_change_justification_columns_to_be_.py", line 24, in upgrade
    op.alter_column(

that would be "batch_op" in your example, not "op"

[GabrielRomaoG]

Turns out that i was tracking the wrong file that is quite similar to the changes in this snippet, i assumed the file without properly reading the error traceback, sorry for the inconvenience. Thanks for the help.