1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
Generating SQL Scripts (a.k.a. "Offline Mode")
==============================================
A major capability of Alembic is to generate migrations as SQL scripts, instead of running
them against the database - this is also referred to as *offline mode*.
This is a critical feature when working in large organizations
where access to DDL is restricted, and SQL scripts must be handed off to DBAs. Alembic makes
this easy via the ``--sql`` option passed to any ``upgrade`` or ``downgrade`` command. We
can, for example, generate a script that revises up to rev ``ae1027a6acf``::
$ alembic upgrade ae1027a6acf --sql
INFO [alembic.context] Context class PostgresqlContext.
INFO [alembic.context] Will assume transactional DDL.
BEGIN;
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL
);
INFO [alembic.context] Running upgrade None -> 1975ea83b712
CREATE TABLE account (
id SERIAL NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(200),
PRIMARY KEY (id)
);
INFO [alembic.context] Running upgrade 1975ea83b712 -> ae1027a6acf
ALTER TABLE account ADD COLUMN last_transaction_date TIMESTAMP WITHOUT TIME ZONE;
INSERT INTO alembic_version (version_num) VALUES ('ae1027a6acf');
COMMIT;
While the logging configuration dumped to standard error, the actual script was dumped to standard output -
so in the absence of further configuration (described later in this section), we'd at first be using output
redirection to generate a script::
$ alembic upgrade ae1027a6acf --sql > migration.sql
Getting the Start Version
--------------------------
Notice that our migration script started at the base - this is the default when using offline
mode, as no database connection is present and there's no ``alembic_version`` table to read from.
One way to provide a starting version in offline mode is to provide a range to the command line.
This is accomplished by providing the "version" in ``start:end`` syntax::
$ alembic upgrade 1975ea83b712:ae1027a6acf --sql > migration.sql
The ``start:end`` syntax is only allowed in offline mode; in "online" mode, the ``alembic_version``
table is always used to get at the current version.
It's also possible to have the ``env.py`` script retrieve the "last" version from
the local environment, such as from a local file. A scheme like this would basically
treat a local file in the same way ``alembic_version`` works::
if context.is_offline_mode():
version_file = os.path.join(os.path.dirname(config.config_file_name), "version.txt")
if os.path.exists(version_file):
current_version = open(version_file).read()
else:
current_version = None
context.configure(dialect_name=engine.name, starting_version=current_version)
context.run_migrations()
end_version = context.get_revision_argument()
if end_version and end_version != current_version:
open(version_file, 'w').write(end_version)
Writing Migration Scripts to Support Script Generation
------------------------------------------------------
The challenge of SQL script generation is that the scripts we generate can't rely upon
any client/server database access. This means a migration script that pulls some rows
into memory via a ``SELECT`` statement will not work in ``--sql`` mode. It's also
important that the Alembic directives, all of which are designed specifically to work
in both "live execution" as well as "offline SQL generation" mode, are used.
Customizing the Environment
---------------------------
Users of the ``--sql`` option are encouraged to hack their ``env.py`` files to suit their
needs. The ``env.py`` script as provided is broken into two sections: ``run_migrations_online()``
and ``run_migrations_offline()``. Which function is run is determined at the bottom of the
script by reading :meth:`.EnvironmentContext.is_offline_mode`, which basically determines if the
``--sql`` flag was enabled.
For example, a multiple database configuration may want to run through each
database and set the output of the migrations to different named files - the :meth:`.EnvironmentContext.configure`
function accepts a parameter ``output_buffer`` for this purpose. Below we illustrate
this within the ``run_migrations_offline()`` function::
from alembic import context
import myapp
import sys
db_1 = myapp.db_1
db_2 = myapp.db_2
def run_migrations_offline():
"""Run migrations *without* a SQL connection."""
for name, engine, file_ in [
("db1", db_1, "db1.sql"),
("db2", db_2, "db2.sql"),
]:
context.configure(
url=engine.url,
transactional_ddl=False,
output_buffer=open(file_, 'w'))
context.execute("-- running migrations for '%s'" % name)
context.run_migrations(name=name)
sys.stderr.write("Wrote file '%s'" % file_)
def run_migrations_online():
"""Run migrations *with* a SQL connection."""
for name, engine in [
("db1", db_1),
("db2", db_2),
]:
connection = engine.connect()
context.configure(connection=connection)
try:
context.run_migrations(name=name)
session.commit()
except:
session.rollback()
raise
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
|
