Skip to content

Latest commit

 

History

History
71 lines (55 loc) · 3.82 KB

how_it_works.md

File metadata and controls

71 lines (55 loc) · 3.82 KB

How does it work

Take a look at the directory structure of the example project:

├── app
    ├── __init__.py
    ├── ddl.py
    ├── models.py
    └── scripts
        ├── best_customer.sql
        ├── customer_details.sql
        ├── last_month_orders.sql
        └── order_details.sql
└── migrations
    ├── env.py
    ├── script.py.mako
    └── versions
        ├── 2024_01_08_0951-8cad1973204c_initital.py
        ├── 2024_01_08_0955-0c897e9399a9_views.py
        ├── 2024_01_08_1016-af80846764cd_product_prices.py
        ├── 2024_01_08_1045-060d60b5c278_customer_info.py
        └── ddl
            ├── 2024_01_08_0955_best_customer_0c897e9399a9.sql
            ├── 2024_01_08_0955_last_month_orders_0c897e9399a9.sql
            ├── 2024_01_08_0955_order_details_0c897e9399a9.sql
            ├── 2024_01_08_1016_best_customer_af80846764cd.sql
            ├── 2024_01_08_1016_order_details_af80846764cd.sql
            ├── 2024_01_08_1045_customer_details_060d60b5c278.sql
            └── 2024_01_08_1045_order_details_060d60b5c278.sql

In this example, your DDL scripts live in the app/scripts folder. If you need to update the script in the database — you edit the .sql file directly, Alembic DDDL will take care of creating the migrations for you.

In the migrations/versions/ddl you can see the same scripts with slightly different names. These are the revisions of your scripts, created by Alembic DDDL. They are used to detect if any of the existing script was changed, or if any new one was added.

When you run alembic revision --autogenerate command, Alembic DDDL will use the migrations/versions/ddl folder to create a full state of your DDL scripts for the current head. Then it will compare this state with your app/scripts folder. If any new DDL scripts are added, or if any of the existing ones are changed, their copies will be saved in migrations/versions/ddl, and the upgrade/downgrade commands for them will be added to the main revision file.

Note: spacing and indentation are ignored when comparing the scripts, so reformatting the SQL won't trigger a new revision. Comments are not ignored by default, but you can set the configuration option to also ignore them.

The upgrade command will look like this:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.run_ddl_script('2024_01_08_1045_order_details_060d60b5c278.sql')
  • '2024_01_08_1045_order_details_060d60b5c278.sql' is a freshly created copy of the current version of order_details.sql script.
  • run_ddl_script is an operation introduced by Alembic Dumb DDL. All it does is execute each statement in the script against the database.

There are two variations of the downgrade command.

For the new DDL scripts (without existing revisions) the downgrade command will just execute the DDL.down_sql, as you defined it:

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute('DROP VIEW IF EXISTS order_details;')

If DDL script already existed and was changed in this revision, the downgrade command will look similar to the upgrade command, but will use the previous revision of the script:

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.run_ddl_script('2024_01_08_1016_order_details_af80846764cd.sql')

Because each DDL script is used for both upgrade and downgrade commands, it's important that the script is overwriting entities, not just creating them. i.e. it should start with DROP ... IF EXISTS or a similar construct for your DBMS.