# Migrations on the new database We are using Alembic to perform migrations for the integrations project - https://alembic.sqlalchemy.org/en/latest/ #### 1. Create DBs Create a database for each tenant that you want to create tables for. Syntax to be followed - https://code.getedge.ai/edge-platform/documentation/integration_documents/-/wikis/Integrations-DB-Design#databases-integration_tenant-name #### 2. Initiate migrations ```shell alembic init -t multidb <folder-name> # alembic init -t multidb migrations ``` #### 3. Modify `alembic.ini` with your DB configurations Edit `alembic.ini` file to add your databases and connection strings. Edit the following section - ```python databases = engine1, engine2 [engine1] sqlalchemy.url = driver://user:pass@localhost/dbname [engine2] sqlalchemy.url = driver://user:pass@localhost/dbname2 ``` **Eg:** ```python databases = integration_hexaware, integration_unisys [integration_hexaware] sqlalchemy.url = postgresql://postgres:p@0.0.0.0:5432/integration_hexaware [integration_unisys] sqlalchemy.url = postgresql://postgres:p@0.0.0.0:5432/integration_unisys ``` #### 4. Modify `migrations/env.py` with required target_metadata These statements have to be added before all function definitions. ```python from models import mymodel # This statement varies based on your file names target_metadata = { "engine1": mymodel.Base.metadata, "engine2": mymodel.Base.metadata, } ``` **Eg:** ```python from model import models target_metadata = { "integration_hexaware": models.Base.metadata, "integration_unisys": models.Base.metadata, } ``` Add the below arguments to the existing `run_migrations_online` function's `context.configure` call ```python context.configure( ... include_schemas = False, compare_type=True ) ``` #### 5. Autogenerate revision ```shell alembic revision --autogenerate -m "Initial" ``` Once this is executed, a version file gets created in `migrations/versions` folder, it is recommended to open it and inspect the statements that are autogenerated. It is expected to create migrations for all tenant DBs. Most common problem when we maintain one database for multiple services is `FAILED: Can't locate revision identified by 'fb76dde37f39'`. To solve this we can create a dummy version file using the below command and generate migrations again. ```shell alembic revision --rev-id fb76dde37f39 ``` #### 6. Perform migrations on DB ```shell alembic upgrade head ``` Once done, check if tables are added.