Embedded Files
Alembic: a weird mechanism
Alembic: a weird mechanism
Alembic is a kind of "revision control system" to handle changes in databases: adding/removing tables and/or columns, changing types of columns and so on.
Alembic is a challenging module to work with. I personally find it a difficult to handle module, not clearly explained (or at least: quite difficult to understand). But still very much needed to handle changes in the database scheme(s).
As of this writing (december 2024) the latest version of alembic is 1.14.0.
This is the link to the documentation.
Alembic mechanism
Alembic mechanism
When using alembic in your Flask project, the following has to be run before you can use it:
Install alembic:
pip install alembic
Note: this will also install the module sqlalchemy and others.
This is the list of installed modules after installing alembic:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ pip freeze
alembic==1.14.0
Mako==1.3.7
MarkupSafe==3.0.2
SQLAlchemy==2.0.36
typing-extensions==4.12.2
Initialise alembic: alembic init alembic
This will create a directory called alembic (second input parameter in the above given command, you can name it the way you want)
This will also create a file called alembic.ini which contains basic information to work with the type of database you use in your Flask project.
Among other things: the connection to your database.
Modify the following parameters:
In alembic/env.py: the connection to the database model module that you're using. The database model module is representing the layout of your database.
Mostly it will be something like this:Search for the item target_metadata = None
Replace it with the following information:
from <database_model> import Base
target_metadata = [Base.metadata]
Now alembic can put a link between the database model and the way it will handle changes in the database.
See next chapter for a detailed example.
In alembic.ini: the connection to, the type of and the name of the database
Search for sqlalchemy.url = driver://user:pass@localhost/dbname
Replace it with the following parameters:
The database app you're going to use: SQLite, MySQL, PostGres,...
The location of the database
The name of the database
An example of a database connection:
sqlalchemy.url = sqlite:///app.db
This does the 3 things mentioned above:Use a database app. Here: sqlite
The location of the database. Here: '/'.
Note that the 3 '/' characters are in fact 2 '/' characters + one more '/' character indicating the database is in the same directory as the script which makes the connection between SQLAlchemy and the physical database.The name of the database. Here: app.db
Once all the above info is in place you have to create the database. There's 2 options here:
Create database only
See next chapter for the content of the database script.
First, create the database itself without installing the columns yet:
alembic revision -m "<some explanation>
Example:
alembic revision -m "Database creation"
This will generate a script in the alembic/versions directory which will contain all migration Python scripts. Each script will have an upgrade and a downgrade method.
This is the result of the above command:
"""Create database
Revision ID: 47ce47e187a7
Revises:
Create Date: 2024-12-07 11:41:45.134846
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision: str = '47ce47e187a7'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
pass
def downgrade() -> None:
pass
The script contains a revision string (kind of hash). See red item above.
Note that there will be one column already present in the database: alembic_version.
This column will contain the revision string as content.
Next, you still have to execute the script. To do this, run the following command:
alembic upgrade head
This will run the upgrade method on the "top of the revision history", which is the meaning of "head".
After that, the database will be present with the one column alembic_version present.
Note: If this approach is used, you still have to create the columns in the table. Therefore, it's maybe better to use the second option who does all this in one go. See next item below.
Create database, tables and columns in one go
See next chapter for the content of the database script.
To do this, make use of the autogenerate option of alembic. This will automatically generate the scripts in such a way that next to the database also the columns will be generated.
Run the following command:
alembic revision --autogenerate -m "<some_explanation>"
Example:
alembic revision --autogenerate -m "Create DB + all tables"
This will generate the following initial update script:
"""Create DB + tables
Revision ID: a1a024a4bd6c
Revises:
Create Date: 2024-12-07 12:03:43.550334
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision: str = 'a1a024a4bd6c'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('city',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=100), server_default='', nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('friend',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=100), server_default='', nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('hobby',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=100), server_default='', nullable=True),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('hobby')
op.drop_table('friend')
op.drop_table('city')
# ### end Alembic commands ###
As one can see, the upgrade and downgrade methods are much more extended compared to the previous ones. It's clear that, next to the different tables, also the different columns are immediately created.
This should finalise the initial database setup. Again, see the next chapter for an in-depth database module example.
A detailed database model example
A detailed database model example
Suppose we have a database model called model.py with the following content
# model.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class Friend(Base):
__tablename__ = 'friend'
id = Column(Integer, primary_key=True)
name = Column(String(100), server_default='')
class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True)
name = Column(String(100), server_default='')
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
name = Column(String(100), server_default='')
Every class defined in the above model is a table in the database.
Every class defines a table name (mostly the same as the class, but then all in low caps) and one or more properties which are in fact columns in the final database.
Example: class Hobby
The class is inheriting from Base, which is an instance of declarative_base(), part of the module sqlalchemy.ext.declarative (I'm too unfamiliar with alembic to know the details of this module, please see the alembic documentation for more info)
The class is defining a table name, typically using the "dunder" __tablename__ variable (dunder means double underscore...). This will be the table name you will see in your database file.
Some properties which are the columns of the table:
id: must always be present since this is the primary key
It is defined as an integer and it's defined as primary key.name: name given to this column
It is defined as a string with a max length of 100 and the default value is empty.
To make sure the alembic mechanism is able to find the database, you have to modify the content of alembic/env.py, section target_metadata. See previous chapters on how to do this.
For the example above, the correct content for the metadata section should be:
from model import Base
target_metadata = [Base.metadata]
If the file model.py would be, for instance, in a subdirectory called app then the syntax would become:
from app.model import Base
target_metadata = [Base.metadata]
It just follows the package methodology of Python.
Annoying Alembic function: adapt existing tables/columns
Annoying Alembic function: adapt existing tables/columns
One of the most frustrating things about alembic and SQLite databases is the change of a database scheme later on. Alembic doesn't allow you to drop and/or modify columns as such. At least not with SQLite, since SQLite does not have a very good support for the SQL statement ALTER (which is used to change tables).
Suppose I want to add a column address in the table hobby as shown in the previous section. To do this, we're adding the following in the file model.py (which contains the skeleton of the database):
addres = Column(String(100), server_default='')
So, the class/table Hobby becomes (new column added in blue colour below):
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
name = Column(String(100), server_default='')
address = Column(String(100), server_default='')
To apply this, run the command
alembic revision --autogenerate -m "Add column 'address' to table 'hobby'"
Result on the command window:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ alembic revision --autogenerate -m "Add column 'address' to table 'hobby'"
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added column 'hobby.address'
Generating /home/pi/mystuff/flask/sandbox/alembic/versions/60d3d2f89e59_add_column_address_to_table_hobby.py ... done
As you can see (blue line) the mechanism has detected there's a column address added to table hobby.
To deploy this, run
alembic upgrade head
So far, so good. The column has been added to the table.
But if we want to remove the column again, the problems start.
Change the content of the file model.py again so that the column address is removed from the table hobby.
Then create a new script with the relevant comment:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ alembic revision --autogenerate -m "Remove column 'address' from table 'hobby'"
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected removed column 'hobby.address'
Generating /home/pi/mystuff/flask/sandbox/alembic/versions/3b117a6a64ef_remove_column_address_from_table_hobby.py ... done
Generating the script is still OK. We see the command to remove the column address from the table hobby given in blue above.
Now, run the command
alembic upgrade head
And here it comes: you get a very weird error at the end like so:
File "/home/pi/mystuff/flask/sandbox/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/pi/mystuff/flask/sandbox/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error
[SQL: ALTER TABLE hobby DROP COLUMN address]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
At first, I absolutely didn't know what was going on. And the link to the "background on this error" was utterly useless. It didn't give me any clue on what was going on.
After quite some googling I found a way out. Because SQLite is poor on the ALTER command, you have to tell alembic to use - what they call - the batch approach.
For this, you have two approaches:
Change each and every revision yourself, should you have to modify the table scheme
This is cumbersome and very user-unfriendly. But still, if you face the above error you have to do it at least for this script generation session.
Therefor:Open the corresponding script in the directory alembic/versions
Do the following:
Change this
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('hobby', 'address')
# ### end Alembic commands ###
Into this
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('hobby') as batch_op:
batch_op.drop_column('address')
# ### end Alembic commands ###
If you re-run the upgrade script now, it should do its job correctly:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ alembic upgrade head
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 60d3d2f89e59 -> 3b117a6a64ef, Remove column 'address' from table 'hobby'
The same is valid if you want to change the type of a column. Suppose you want to change the type of name in the table hobby from String to Integer:
Change name = Column(String(100), server_default='')
Into name = Column(Integer, server_default='')
Create the migration script:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ alembic revision --autogenerate -m "Change type of column 'name' in table 'hobby'"
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected type change from VARCHAR(length=100) to Integer() on 'hobby.name'
Generating /home/pi/mystuff/flask/sandbox/alembic/versions/283ef6da343d_change_type_of_column_name_in_table_.py ... done
Again, so far, so good. But when you run the command alembic upgrade head you will have the same error as given before.
Solution: adapt the script again manually.
From this:
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('hobby', 'name',
existing_type=sa.VARCHAR(length=100),
type_=sa.Integer(),
existing_nullable=True,
existing_server_default=sa.text("('')"))
# ### end Alembic commands ###
To this:
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('hobby') as batch_op:
batch_op.alter_column('hobby', 'name',
existing_type=sa.VARCHAR(length=100),
type_=sa.Integer(),
existing_nullable=True,
existing_server_default=sa.text("('')"))
# ### end Alembic commands ###
If you run the upgrade script now you will see all goes fine:
(venv) pi@rpi3Bimmcff ~/mystuff/flask/sandbox$ alembic upgrade head
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 3b117a6a64ef -> 283ef6da343d, Change type of column 'name' in table 'hobby'
This is, however, not very user-friendly as said before. To "automate" this behaviour see the next point below.
Let the autogenerate option add the necessary syntax automatically each and every time a script has to be generated.
This is the favourable approach, but therefor you must do another change in the file alembic/env.py:Open the file and search for context.configure( in the method run_migrations_online (there's also a method run_migrations_offline but we don't wanna change that one).
Replace:
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata
)
With:
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
render_as_batch=True
)
From now onwards all generated scripts will contain the "batch approach" and you don't have to modify anything manually again.
Another annoying Alembic issue: adding/removing foreign keys
Another annoying Alembic issue: adding/removing foreign keys
I just found another annoying issue with alembic: adding a foreign key to an existing table.
This is the table I want to add a foreign key to:
class Friend(Base):
__tablename__ = 'friend'
id = Column(Integer, primary_key=True)
name = Column(String(100), server_default='')
city_id = Column(Integer, ForeignKey('city.id'))
===>>>> new item will be added here <<<<===
city = relationship(
'City',
back_populates='friends',
)
io = relationship(
'Io',
back_populates='friends',
)
hobbies = relationship(
'Hobby',
secondary=friend_mtm_hobby_table,
back_populates='friends',
order_by=lambda: Hobby.name,
)
The new foreign key is (will be added just after the line containing city_id = ...):
io_id = Column(Integer, ForeignKey('io.id'))
When running the autogenerate method to create a new migration script, all goes fine.
But when running the upgrade script, the following error is thrown:
File "/home/pi/mystuff/flask/gvflask/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 398, in batch_alter_table
impl.flush()
File "/home/pi/mystuff/flask/gvflask/venv/lib/python3.9/site-packages/alembic/operations/batch.py", line 162, in flush
fn(*arg, **kw)
File "/home/pi/mystuff/flask/gvflask/venv/lib/python3.9/site-packages/alembic/operations/batch.py", line 669, in add_constraint
raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name
At first, I again didn't know what this meant. Looking to the generated scripts, I saw this (code snippet shown):
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('friend', schema=None) as batch_op:
batch_op.create_foreign_key(None, 'io', ['io_id'], ['id'])
# ### end Alembic commands ###
The problem lies in the "None" parameter of the method create_foreign_key. The constraint cannot be None, it should have a name.
The solution is: give it a name.
So, change this:
batch_op.create_foreign_key(None, 'io', ['io_id'], ['id'])
into this:
batch_op.create_foreign_key("Key1", 'io', ['io_id'], ['id'])
to resolve the issue.
Important!!! Don't forget to do the same in the onDowngrade() method of the script!!!
Page updated
Google Sites
Report abuse