Skip to content

Migration

Migration is a process of migrating models into the database. In Flask, the extension Flask-Migrate handles SQLAlchemy database migrations for Flask applications using Alembic.

SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

To create a table, we need to have a SQLAlchemy function. The function can be updated and changed.

See the code below

Define a table and a class to hold stac harvester the class has a get method that returns a list of stac harvester there is also a mapper to map relationships.

StacHarvester 

StacHarvester(**kw)

Bases: StatefulObjectMixin, DomainObject

StacHarvester model.

Source code in ckanext/saeoss/model/stac_harvester.py 
69
70
71
def __init__(self, **kw):
    super(StacHarvester, self).__init__(**kw)
    self.owner_id = kw.get("user")

get 

get(**kw)

returns a list of saved searches based on user id.

Source code in ckanext/saeoss/model/stac_harvester.py 
73
74
75
76
77
78
79
def get(cls, **kw):
    """
    returns a list of saved searches
    based on user id.
    """
    query = model.meta.Session.query(cls)
    return query.filter_by(**kw)

Migrate a model

In SAEOSS-Portal, after adding or updating a model. To apply a migration, run the following command: 

docker exec -it saeoss_ckan-web_1 poetry run alembic -c /home/appuser/app/ckanext/saeoss/migration/saeoss/alembic.ini  revision -m "Add stac harvest table"

After running the above command, a new file would have been created in the directory: ckanext/saeoss/migration/saeoss/alembic/versions

In each file in the path, there is a feature called upgrade and/or downgrade to create or update the table.

See the code below

Add stac harvest table

Revision ID: 796cac900039 Revises: cf3438c769b2 Create Date: 2023-10-11 11:37:26.280343

downgrade 

downgrade()

To delete table function.

Source code in ckanext/saeoss/migration/saeoss/alembic/versions/796cac900039_add_stac_harvest_table.py 
62
63
64
def downgrade():
    """To delete table function."""
    op.drop_table("stac_harvester")

upgrade 

upgrade()

Create table function.

Source code in ckanext/saeoss/migration/saeoss/alembic/versions/796cac900039_add_stac_harvest_table.py 
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
def upgrade():
    """Create table function."""
    op.create_table(
        "stac_harvester",
        model.meta.metadata,
        sa.Column(
            "harvester_id",
            types.UnicodeText,
            primary_key=True,
            default=model.types.make_uuid,
        ),
        sa.Column(
            "user",
            types.UnicodeText,
        ),
        sa.Column(
            "owner_org",
            types.UnicodeText,
        ),
        sa.Column(
            "url",
            types.UnicodeText,
        ),
        sa.Column(
            "number_records",
            types.UnicodeText,
        ),
        sa.Column(
            "status",
            types.UnicodeText,
        ),
        sa.Column(
            "message",
            types.UnicodeText,
        ),
        sa.Column(
            "_date", types.DateTime, default=datetime.datetime.utcnow
        ),
    )