Integrating upgrade and create_all

[Jason Merrill]

I see in the documentation that alembic is intended to be easy to integrate with SQLAlchemy's `metadata.create_all(engine)`

http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch

but I'm confused about how this actually works out. The docs include this snippet:

# inside of a "create the database" script, first create
# tables:
my_metadata.create_all(engine)

# then, load the Alembic configuration and generate the
# version table, "stamping" it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.stamp(alembic_cfg, "head")

It's quite appealing to use alembic only for changes that `create_all` can't handle (like adding columns to an existing table). I'm confused about how this actually works out, though.

Suppose in environment A, I have an existing table, T1, and would like to add a new column C1 to it. First, I add the Column definition to my Model, and then I create a version file (maybe with `--autogenerate`), with an upgrade method that adds the column.

In environment B, suppose I didn't have T1 yet at all, so I can run the script snippet, and it will create T1 for me, complete with C1 since that's now in the Model, and I'm up to date and stamp myself that way. Perfect.

But environment C starts off just like environment A did, with T1 missing C1. Now I run the same script snippet, and `create_all` does nothing because `create_all` doesn't add new columns to existing tables. Environment C still gets stamped as being up to date, even though it is still missing C1. In this case, it seems I should have run  `alembic upgrade head` instead of doing `create_all`.

What would have happened in environment B if I did `alembic upgrade head` instead of `create_all`? It would have failed, right, because there's no way to add C1 to the not-yet-existing T1.

Is there one procedure that works for both B and C? I'm tempted to write something that first runs `create_all`, then asks autogenerate's `compare_metadata` whether there are still differences, and only if there are runs `alembic upgrade head`, but I'm not sure that procedure covers all bases either.

Am I missing something here?

[Michael Bayer]

On Aug 3, 2012, at 9:26 PM, Jason Merrill wrote:

I see in the documentation that alembic is intended to be easy to integrate with SQLAlchemy's `metadata.create_all(engine)`

http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch

but I'm confused about how this actually works out. The docs include this snippet:

# inside of a "create the database" script, first create
# tables:
my_metadata.create_all(engine)

# then, load the Alembic configuration and generate the
# version table, "stamping" it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.stamp(alembic_cfg, "head")

It's quite appealing to use alembic only for changes that `create_all` can't handle (like adding columns to an existing table). I'm confused about how this actually works out, though.

That's not the intent of the code snippet above.    The intent of the above section is so that when creating the database for the first time with a **brand new** installation, it doesn't need to be performed using migrations.   The advantage to this is that once you have hundreds or thousands of migration files, creating brand new schemas does not require that these hundreds/thousands of tiny migration scripts need to be run just to create a full database from nothing.  That is, "metadata.create_all()" is the equivalent of the sum of all migrations from base to head, and the code above takes advantage of that.  However, create_all() is not equivalent to any arbitrary subset of a migration series, so this use case is *only when the database is blank*.

This is why the documentation section you're seeing this code within is called, "Building an Up to Date Database **from Scratch**" (http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch) .   "from scratch" means there's no schema at all to start with in the target database.    "If you check out a copy of an application, running this will give you the entire database in one shot, without the need to run through all those migration files, which are instead **tailored towards applying incremental changes** to an existing database."    That is, the migration files are for incremental changes, and they continue to be.

An additional case mentioned in that section is that once all of your existing databases are at least at a particular version V or greater, you can prune old migration files - because brand new databases are created via create_all() + stamp, existing databases are affected using migrations, all of which only need V or greater.   Those migration files prior to V will never be used again and can be pruned.  Though in practice there's not much reason to do this, unless there's so many thousands of files that Alembic is taking too much time/memory to start up, as it always reads all the migration modules on startup.

[Jason Merrill]

On Saturday, August 4, 2012 4:37:18 AM UTC-7, Michael Bayer wrote:

On Aug 3, 2012, at 9:26 PM, Jason Merrill wrote:

I see in the documentation that alembic is intended to be easy to integrate with SQLAlchemy's `metadata.create_all(engine)`

http://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch

but I'm confused about how this actually works out. The docs include this snippet:

# inside of a "create the database" script, first create
# tables:
my_metadata.create_all(engine)

# then, load the Alembic configuration and generate the
# version table, "stamping" it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.stamp(alembic_cfg, "head")

It's quite appealing to use alembic only for changes that `create_all` can't handle (like adding columns to an existing table). I'm confused about how this actually works out, though.

That's not the intent of the code snippet above.    The intent of the above section is so that when creating the database for the first time with a **brand new** installation, it doesn't need to be performed using migrations.

Alright, that makes sense. Thanks!