18
22
--------------------------
20
24
To begin, we'll need to create a *repository* for our
21
project. Repositories are associated with a single database schema,
22
and store collections of change scripts to manage that schema. The
23
scripts in a repository may be applied to any number of databases.
25
Repositories each have a name. This name is used to identify the
26
repository we're working with.
28
All work with repositories is done using the migrate command. Let's
27
All work with repositories is done using the :ref:`migrate <command-line-usage>` command. Let's
29
28
create our project's repository::
31
% migrate create my_repository "Example project"
33
This creates an initially empty repository in the current directory at
34
my_repository/ named Example project. The repository directory
35
contains a sub directory versions that will store the schema versions,
30
$ migrate create my_repository "Example project"
32
This creates an initially empty repository relative to current directory at
33
my_repository/ named `Example project`.
35
The repository directory
36
contains a sub directory :file:`versions` that will store the :ref:`schema versions <changeset-system>`,
36
37
a configuration file :file:`migrate.cfg` that contains
37
:ref:`repository configuration <repository_configuration>`, a
38
:file:`README` file containing information that the directory is an
39
sqlalchemy-migrate repository and a script :file:`manage.py` that has
40
the same functionality as the :command:`migrate` command but is
41
preconfigured with the repository.
43
Version-control a database
38
:ref:`repository configuration <repository_configuration>` and a script :ref:`manage.py <project_management_script>`
39
that has the same functionality as the :ref:`migrate <command-line-usage>` command but is
40
preconfigured with repository specific parameters.
44
Repositories are associated with a single database schema,
45
and store collections of change scripts to manage that schema. The
46
scripts in a repository may be applied to any number of databases.
47
Each repository has an unique name. This name is used to identify the
48
repository we're working with.
51
Version control a database
44
52
--------------------------
46
Next, we need to create a database and declare it to be under version
47
control. Information on a database's version is stored in the database
54
Next we need to declare database to be under version control.
55
Information on a database's version is stored in the database
48
56
itself; declaring a database to be under version control creates a
49
table, named 'migrate_version' by default, and associates it with your
57
table named **migrate_version** and associates it with your repository.
52
59
The database is specified as a `SQLAlchemy database url`_.
93
100
our project that remembers the database and repository we're using,
94
101
and use it to perform commands::
96
% migrate manage manage.py --repository=my_repository --url=sqlite:///project.db
97
% python manage.py db_version
103
$ migrate manage manage.py --repository=my_repository --url=sqlite:///project.db
104
$ python manage.py db_version
100
107
The script manage.py was created. All commands we perform with it are
101
the same as those performed with the 'migrate' tool, using the
108
the same as those performed with the :ref:`migrate <command-line-usage>` tool, using the
102
109
repository and database connection entered above. The difference
103
110
between the script :file:`manage.py` in the current directory and the
104
111
script inside the repository is, that the one in the current directory
105
112
has the database URL preconfigured.
116
Parameters specified in manage.py should be the same as in :ref:`versioning api <versioning-api>`.
117
Preconfigured parameter should just be omitted from :ref:`migrate <command-line-usage>` command.
108
120
Making schema changes
109
121
=====================
117
129
Create a change script
118
130
----------------------
120
Our first change script will create a simple table::
122
account = Table('account',meta,
123
Column('id',Integer,primary_key=True),
124
Column('login',String(40)),
125
Column('passwd',String(40)),
132
Our first change script will create a simple table
134
.. code-block:: python
136
account = Table('account', meta,
137
Column('id', Integer, primary_key=True),
138
Column('login', String(40)),
139
Column('passwd', String(40)),
128
142
This table should be created in a change script. Let's create one::
130
% python manage.py script "Add account table"
144
$ python manage.py script "Add account table"
132
146
This creates an empty change script at
133
147
:file:`my_repository/versions/001_Add_account_table.py`. Next, we'll
134
148
edit this script to create our table.
136
151
Edit the change script
137
152
----------------------
139
Our change script defines two functions, currently empty:
140
``upgrade()`` and ``downgrade()``. We'll fill those in::
142
from sqlalchemy import *
143
from migrate import *
145
meta = MetaData(migrate_engine)
146
account = Table('account', meta,
147
Column('id', Integer, primary_key=True),
148
Column('login', String(40)),
149
Column('passwd', String(40)),
158
As you might have guessed, upgrade() upgrades the database to the next
159
version. This function should contain the changes we want to perform;
160
here, we're creating a table. downgrade() should reverse changes made
161
by upgrade(). You'll need to write both functions for every change
162
script. (Well, you don't *have* to write downgrade(), but you won't be
154
Our change script predefines two functions, currently empty:
155
:func:`upgrade` and :func:`downgrade`. We'll fill those in
157
.. code-block:: python
159
from sqlalchemy import *
160
from migrate import *
164
account = Table('account', meta,
165
Column('id', Integer, primary_key=True),
166
Column('login', String(40)),
167
Column('passwd', String(40)),
170
def upgrade(migrate_engine):
171
meta.bind = migrate_engine
174
def downgrade(migrate_engine):
175
meta.bind = migrate_engine
178
As you might have guessed, :func:`upgrade` upgrades the database to the next
179
version. This function should contain the :ref:`schema changes<changeset-system>` we want to perform
180
(in our example we're creating a table).
182
:func:`downgrade` should reverse changes made
183
by :func:`upgrade`. You'll need to write both functions for every change
184
script. (Well, you don't *have* to write downgrade, but you won't be
163
185
able to revert to an older version of the database or test your
164
186
scripts without it.)
166
``from migrate import *`` imports a special SQLAlchemy engine named
167
'migrate_engine'. You should use this in your change scripts, rather
168
than creating your own engine.
170
You should be very careful about importing files from the rest of your
171
application, as your change scripts might break when your application
172
changes. More about `writing scripts with consistent behavior`_.
191
As you can see, **migrate_engine** is passed to both functions.
192
You should use this in your change scripts, rather
193
than creating your own engine.
197
You should be very careful about importing files from the rest of your
198
application, as your change scripts might break when your application
199
changes. More about `writing scripts with consistent behavior`_.
174
202
Test the change script
175
203
------------------------
177
205
Change scripts should be tested before they are committed. Testing a
178
script will run its upgrade() and downgrade() functions on a specified
206
script will run its :func:`upgrade` and :func:`downgrade` functions on a specified
179
207
database; you can ensure the script runs without error. You should be
180
208
testing on a test database - if something goes wrong here, you'll need
181
209
to correct it by hand. If the test is successful, the database should
182
appear unchanged after upgrade() and downgrade() run.
188
% python manage.py test
210
appear unchanged after :func:`upgrade` and :func:`downgrade` run.
214
$ python manage.py test
189
215
Upgrading... done
190
216
Downgrading... done
193
219
Our script runs on our database (``sqlite:///project.db``, as
194
220
specified in manage.py) without any errors.
196
Our repository's version now is::
222
Our repository's version is::
198
% python manage.py version
224
$ python manage.py version
229
test command executes actual script, be sure you are NOT doing this on production database.
201
232
Upgrade the database
202
233
--------------------
204
235
Now, we can apply this change script to our database::
206
% python manage.py upgrade
237
$ python manage.py upgrade
209
240
This upgrades the database (``sqlite:///project.db``, as specified
210
241
when we created manage.py above) to the latest available version. (We
211
could also specify a version number if we wished, using the --version
242
could also specify a version number if we wished, using the ``--version``
212
243
option.) We can see the database's version number has changed, and our
213
table has been created:
217
% python manage.py db_version
244
table has been created::
246
$ python manage.py db_version
221
250
account migrate_version
243
272
want your change scripts' behavior changing when your source code
246
Consider the following example of what can go wrong (i.e. what NOT to
249
Your application defines a table in the model.py file:
253
from sqlalchemy import *
256
table = Table('mytable',meta,
257
Column('id',Integer,primary_key=True),
260
...and uses this file to create a table in a change script:
264
from sqlalchemy import *
265
from migrate import *
267
model.meta.connect(migrate_engine)
277
**Consider the following example of what NOT to do**
279
Let's say your application defines a table in the :file:`model.py` file:
281
.. code-block:: python
283
from sqlalchemy import *
286
table = Table('mytable', meta,
287
Column('id', Integer, primary_key=True),
290
... and uses this file to create a table in a change script:
292
.. code-block:: python
294
from sqlalchemy import *
295
from migrate import *
298
def upgrade(migrate_engine):
299
model.meta.bind = migrate_engine
301
def downgrade(migrate_engine):
302
model.meta.bind = migrate_engine
305
This runs successfully the first time. But what happens if we change
306
the table definition in :file:`model.py`?
308
.. code-block:: python
310
from sqlalchemy import *
313
table = Table('mytable', meta,
314
Column('id', Integer, primary_key=True),
315
Column('data', String(42)),
318
We'll create a new column with a matching change script
320
.. code-block:: python
322
from sqlalchemy import *
323
from migrate import *
326
def upgrade(migrate_engine):
327
model.meta.bind = migrate_engine
270
328
model.table.create()
330
def downgrade(migrate_engine):
331
model.meta.bind = migrate_engine
272
332
model.table.drop()
274
This runs successfully the first time. But what happens if we change
275
the table definition?
279
table = Table('mytable',meta,
280
Column('id',Integer,primary_key=True),
281
Column('data',String(42)),
284
We'll create a new column with a matching change script::
286
from sqlalchemy import *
287
from migrate import *
289
model.meta.connect(migrate_engine)
292
model.table.data.create()
294
model.table.data.drop()
296
335
This appears to run fine when upgrading an existing database - but the
297
336
first script's behavior changed! Running all our change scripts on a
338
382
postgres, oracle, mysql...
385
.. _command-line-usage:
341
387
Command line usage
342
388
==================
344
390
.. currentmodule:: migrate.versioning.shell
346
:command:`migrate` command is used for API interface. For list of commands and help use
352
:program:`migrate` command uses :func:`migrate.versioning.shell.main` function.
392
:command:`migrate` command is used for API interface. For list of commands and help use::
396
:program:`migrate` command exectues :func:`main` function.
353
397
For ease of usage, generate your own :ref:`project management script <project_management_script>`,
354
which calls :func:`shell.main` function with keywords arguments.
355
You may want to specify `url` and `repository` arguments which almost all API functions require as positional arguments.
398
which calls :func:`main` function with keywords arguments.
399
You may want to specify `url` and `repository` arguments which almost all API functions require.
357
401
If api command looks like::
359
migrate downgrade URL REPOSITORY VERSION [--preview_sql|--preview_py]
361
and you have a project management script that looks like::
403
$ migrate downgrade URL REPOSITORY VERSION [--preview_sql|--preview_py]
405
and you have a project management script that looks like
407
.. code-block:: python
363
409
from migrate.versioning.shell import main