In Alembic, what does `alembic upgrade --sql` produce for a DBA to review, and how must a data step be written to appear in it?
answer
- no connection, only text
- a start:end range
- values rendered inline
- nothing comes back from execute
basics
~20 sWith --sql, Alembic connects to nothing and prints the DDL, data statements and alembic_version updates it would run. Data steps must be statements Alembic can render, op.execute() and op.bulk_insert() with inline values, never code that reads rows.
solid answer
~40 sOffline mode runs `env.py`'s `run_migrations_offline()`, which calls `context.configure()` with a URL rather than a connection, and writes every statement to standard output: `BEGIN`/`COMMIT` on a transactional-DDL database such as PostgreSQL, each script's DDL and DML, and the `alembic_version` inserts and updates. With no database to read, it starts at base unless you give a range, `alembic upgrade 2b3c4d5e6f7a:head --sql`; a downgrade always needs one. A data step appears only if it is a statement: `op.execute()` with a Core `update()` over a lightweight `sa.table()`, or `op.bulk_insert(table, rows)`. Values must render inline, which the generated `env.py` arranges with `literal_binds=True`, or per value with `op.inline_literal()`. Anything that reads rows through `op.get_bind()` fails, because offline execution returns nothing.
code
python · 36 linesfrom alembic import op
import sqlalchemy as sa
revision = "5e6f7a8b9c0d"
down_revision = "2b3c4d5e6f7a"
branch_labels = None
depends_on = None
# a minimal description of the table as it is at this revision
invoices = sa.table("invoices", sa.column("status", sa.String))
def upgrade() -> None:
currencies = op.create_table(
"currencies",
sa.Column("code", sa.String(3), primary_key=True),
sa.Column("name", sa.String(50), nullable=False),
)
op.bulk_insert(
currencies,
[{"code": "EUR", "name": "Euro"}, {"code": "USD", "name": "US dollar"}],
)
op.execute(
invoices.update()
.where(invoices.c.status == op.inline_literal("open"))
.values(status=op.inline_literal("unpaid"))
)
def downgrade() -> None:
op.execute(
invoices.update()
.where(invoices.c.status == op.inline_literal("unpaid"))
.values(status=op.inline_literal("open"))
)
op.drop_table("currencies")go deeper
Recall that --sql prints SQL instead of running it and that a start:end range limits the script to one release.
Explain where offline mode starts, why a downgrade needs a range, and how op.execute(), op.bulk_insert() and inline literals make data steps render.
Show you can spot a revision that will not render offline, such as one reading rows, and restructure it as set-based SQL before a DBA-run release.
Weigh whether releases go out as reviewed offline scripts or online runs, and what that rules out in how revisions are written.
## What offline mode is Alembic normally runs **online**: `env.py` creates an Engine, opens a connection and runs each script's `upgrade()` against the database. Adding `--sql` to `alembic upgrade` or `alembic downgrade` switches to **offline** mode. `context.is_offline_mode()` returns true, `env.py` calls `run_migrations_offline()`, and `context.configure()` receives only a URL, from which Alembic takes the dialect. No connection is made and no driver needs to be installed; every statement the scripts would execute is written as text to standard output, while Alembic's log lines go to standard error, so `> release.sql` captures just the script. ## What the script contains - `BEGIN;` and `COMMIT;` around the run on databases where Alembic assumes transactional DDL, such as PostgreSQL. - A `-- Running upgrade <from> -> <to>` comment before each revision. - Each revision's DDL, such as `CREATE TABLE` and `ALTER TABLE`, compiled for the dialect in the URL. - Each data statement the revision issues through `op.execute()` or `op.bulk_insert()`. - The version bookkeeping: `CREATE TABLE alembic_version` when the range starts at base, then an `INSERT` or `UPDATE` of `version_num` after each revision. On a database without transactional DDL, such as MySQL, the script has no `BEGIN` or `COMMIT`; each statement commits as the DBA runs it. `env.py` can also send the script somewhere other than standard output: `context.configure()` accepts an `output_buffer`, a file-like object, which lets a team write one file per release from `run_migrations_offline()`. ## Choosing the range With no database to read, Alembic cannot know where the target database is. A bare `alembic upgrade head --sql` therefore starts **at base** and emits the whole chain. To produce only the release, pass a range in `start:end` form, such as `alembic upgrade 2b3c4d5e6f7a:head --sql`. The range syntax is offline-only. A downgrade has no default starting point at all: `alembic downgrade -1 --sql` fails with "downgrade with --sql requires <fromrev>:<torev>". ## Writing data steps that render 1. Describe the table inside the revision with `sa.table("invoices", sa.column("status", sa.String))`, a lightweight construct frozen at this revision's shape, instead of importing the application's classes. 2. Express the change as one statement: `op.execute(invoices.update().where(...).values(...))`. A plain string also works; `op.execute()` coerces it to `text()`. 3. Insert fixed rows with `op.bulk_insert(table, [dict, ...])`, where the table is a `Table` or `sa.table()` construct; offline it renders one `INSERT` per row. 4. Make values render as literals. The `env.py` that `alembic init` generates passes `literal_binds=True` in `run_migrations_offline()`. Without it, a plain value prints as a placeholder such as `:status`, which no DBA can run. `op.inline_literal()` forces a single value inline regardless. ## What cannot work offline | In a revision | Online | With `--sql` | |---|---|---| | `op.execute(update(...))` | runs | rendered as SQL | | `op.bulk_insert(table, rows)` | runs | one `INSERT` per row | | `op.get_bind().execute(select(...))` | returns rows | returns `None` | | `op.batch_alter_table()` rebuild on SQLite | reflects the table | needs `copy_from` | A revision that reads data to decide what to write, such as fetching rows and looping over them in Python, cannot be rendered: offline, `execute()` returns `None`, so `.fetchall()` raises `AttributeError`. Either push the logic into SQL, an `UPDATE ... WHERE` or `INSERT ... SELECT`, or branch on `context.is_offline_mode()` and document that the step needs an online run. ## Guarding a step that must run online Sometimes a step genuinely needs to read data, for example to choose a value per row in Python. The revision can say so instead of failing halfway through rendering: ```python import sqlalchemy as sa from alembic import context, op def upgrade() -> None: if context.is_offline_mode(): raise RuntimeError("revision 5e6f7a8b9c0d must be run online") rows = op.get_bind().execute(sa.text("SELECT id FROM invoices")).all() ... ``` That turns a confusing `AttributeError` into a clear message, and it tells the DBA which revision must be handed back to an online run. Most such steps, though, can be rewritten as one set-based statement, which renders and runs the same way in both modes. ## Reviewing and running the script - Generate it against the dialect production uses; the SQL differs between PostgreSQL and MySQL. - Check the first and last `alembic_version` statements: they show the revision the script expects and the one it leaves. - Keep the generated file with the release: it is the exact record of what the DBA ran, and its last `alembic_version` statement is where the next range starts.
- What happens when a revision reads rows through `op.get_bind()` under `--sql`?Offline, the bind is not a live connection, and `execute()` writes the statement to the output and returns `None`. Code that then calls `.fetchall()` stops with `AttributeError: 'NoneType' object has no attribute 'fetchall'`. Rewrite the step as a set-based SQL statement, or guard it with `context.is_offline_mode()` and run that revision online.
- Why might a value in an `op.execute()` statement print as `:status` in the offline script?Core renders bound parameters as placeholders unless told otherwise. The `env.py` that `alembic init` generates passes `literal_binds=True` to `context.configure()` in `run_migrations_offline()`, which renders simple values inline; an `env.py` that dropped it prints placeholders. Wrapping the value in `op.inline_literal()` renders it inline either way.
- Offline mode reads the live alembic_version table to find its starting point.
- A data step can loop over rows from op.get_bind() under --sql.
- alembic downgrade -1 --sql works just like the online command.
- Offline scripts carry bound parameters for the DBA to fill in at run time.
- The --sql output leaves out the alembic_version statements.