31

I want to add alembic to an existing ,sqlalchemy using, project, with a working production db. I fail to find what's the standard way to do a "zero" migration == the migration setting up the db as it is now (For new developers setting up their environment)

Currently I've added import the declarative base class and all the models using it to the env.py , but first time alembic -c alembic.dev.ini revision --autogenerate does create the existing tables.

And I need to "fake" the migration on existing installations - using code. For django ORM I know how to make this work, but I fail to find what's the right way to do this with sqlalchemy/alembic

alonisser
  • 11,542
  • 21
  • 85
  • 139

2 Answers2

49

alembic revision --autogenerate inspects the state of the connected database and the state of the target metadata and then creates a migration that brings the database in line with metadata.

If you are introducing alembic/sqlalchemy to an existing database, and you want a migration file that given an empty, fresh database would reproduce the current state- follow these steps.

  1. Ensure that your metadata is truly in line with your current database(i.e. ensure that running alembic revision --autogenerate creates a migration with zero operations).

  2. Create a new temp_db that is empty and point your sqlalchemy.url in alembic.ini to this new temp_db.

  3. Run alembic revision --autogenerate. This will create your desired bulk migration that brings a fresh db in line with the current one.

  4. Remove temp_db and re-point sqlalchemy.url to your existing database.

  5. Run alembic stamp head. This tells sqlalchemy that the current migration represents the state of the database- so next time you run alembic upgrade head it will begin from this migration.

jiyolla
  • 27
  • 6
michael_j_ward
  • 4,369
  • 1
  • 24
  • 25
  • 6
    This wasn't so obvious to me, so this may be useful to others too : be sure to delete the empty revision file generated in alembic's versions directory during step 1) before running step 3) Otherwise, you will get a "Database is not up to date" error. – Sbu Nov 27 '20 at 14:13
  • I think this needs a step 1.5: remove the migration file (with zero operations) which was created. No? – Carmen May 13 '23 at 10:06
12

New installation: applying the migration

Simply run alembic upgrade head against an empty database. This will apply all the migrations (in your case, the initial migration as it's the only one) to the database.

If you want to do this from code rather than from shell, you can do it the following way:

from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.upgrade(alembic_cfg, "head")

Existing installation: faking the migration

SQL way

One way would be running this SQL against the database:

CREATE TABLE IF NOT EXISTS alembic_version (
    version_num VARCHAR(32) NOT NULL
);

INSERT INTO alembic_version (version_num) VALUES ('your initial migration version');

The first statement creates the table that alembic uses to track your database/migration state. The second statement basically tells alembic that your database state corresponds to the version of your initial migration, or, in other words, fakes the migration.

Alembic way

Alembic has a stamp command, which basically does the same thing. It can be called from shell as alembic stamp head, or from code (taken from the cookbook):

from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.stamp(alembic_cfg, "head")
Mikhail Burshteyn
  • 4,762
  • 14
  • 27
  • 1
    Thanks Mikhail, the problems with this answer: 1. It does not answer the "how to create zero state db for new devs" 2. It's direct SQL vs the db, while this might be the simplest, I would prefer a code driven solution for this problem – alonisser Aug 31 '18 at 20:42
  • @alonisser I've expanded the answer, hope this will solve your problem. – Mikhail Burshteyn Aug 31 '18 at 22:21