In our case, we have N databases with different models. Following structure helps to keep databases isolated:
.├──app│├── __init__.py│├── alembic.ini│├── employee││├── __init__.py││├── models.py││└── views.py│├── migrations│└── user│├── __init__.py│├── models.py│└── views.py├── daemon│├── __init__.py│├── alembic.ini│├── daemon_engine.py│├── migrations│└── models.py├── run.py└── tests
alembic - this directory lives within your application’s source tree and is the home of the migration environment. It can be named anything, and a project that uses multiple databases may even have more than one.,yourproject - this is the root of your application’s source code, or some directory within it.,The tutorial below assumes the alembic command line utility is present in the local path and when invoked, will have access to the same Python module environment as that of the target project.,With a basic understanding of what the environment is, we can create one using alembic init. This will create an environment using the “generic” template:
yourproject / alembic / env.py README script.py.mako versions / 3512 b954651e_add_account.py 2 b1ae634e5cd_add_order_id.py 3 adcc9a56557_rename_username_field.py
$ cd / path / to / yourproject $ source / path / to / yourproject / .venv / bin / activate # assuming a local virtualenv $ alembic init alembic
Creating directory / path / to / yourproject / alembic...done
Creating directory / path / to / yourproject / alembic / versions...done
Generating / path / to / yourproject / alembic.ini...done
Generating / path / to / yourproject / alembic / env.py...done
Generating / path / to / yourproject / alembic / README...done
Generating / path / to / yourproject / alembic / script.py.mako...done
Please edit configuration / connection / logging settings in
'/path/to/yourproject/alembic.ini'
before proceeding.$ alembic list_templates
Available templates:
generic - Generic single - database configuration.
async - Generic single - database configuration with an async dbapi.
multidb - Rudimentary multi - database configuration.
Templates are used via the 'init'
command, e.g.:
alembic init--template generic. / scriptsdatetime.datetime.utcnow().replace(
tzinfo=dateutil.tz.tzutc()
).astimezone(
dateutil.tz.gettz(<timezone>)
)1 week ago Within the scope of a database migrations tool, multi-tenancy typically refers to the practice of maintaining multiple, identical databases where each database is assigned to one client. Alembic does not currently have explicit multi-tenant support; typically, the approach must involve running Alembic multiple times against different database URLs. ,Instead, it sets up the proper core_user relation in the first database. How can I indicate to alembic that when it encounters this schema definition it should be creating the relation in a different database?, 2 days ago Using Alembic with multiple tenants, each being different version, but same DB Hi team, first of all, thanks for the great product. I know that Alembic isn&#39;t designed for my use case, and generally the use case seems to be rare, but I wonder if there&#39;s a logic... , 1 week ago alembic - this directory lives within your application’s source tree and is the home of the migration environment. It can be named anything, and a project that uses multiple databases may even have more than one. env.py - This is a Python script that is run whenever the alembic migration tool is invoked. At the very least, it contains ...
class CoreUser(UserMixin, db.Model): __bind_key__ = 'core'
id = db.Column(db.Integer, primary_key = True) email = db.Column(db.String(255), unique = True)# inside of a "create the database"
script, first create # tables: my_metadata.create_all(engine) # then, load the Alembic configuration and generate the # version table, "stamping"
it with the most recent rev: from alembic.config
import Config from alembic
import command alembic_cfg = Config("/path/to/yourapp/alembic.ini") command.stamp(alembic_cfg, "head")# replace this: #down_revision = '290696571ad2'
# with this: down_revision = None""
"${message} Revision ID: ${up_revision} Revises: ${down_revision} Create Date: ${create_date} "
""
# revision identifiers, used by Alembic.revision = $ {
repr(up_revision)
}
down_revision = $ {
repr(down_revision)
}
from alembic
import op
import sqlalchemy as sa $ {
imports
if imports
else ""
} from alembic
import context def upgrade(): schema_upgrades() if context.get_x_argument(as_dictionary = True).get('data', None): data_upgrades() def downgrade(): if context.get_x_argument(as_dictionary = True).get('data', None): data_downgrades() schema_downgrades() def schema_upgrades(): ""
"schema upgrade migrations go here."
""
$ {
upgrades
if upgrades
else "pass"
}
def schema_downgrades(): ""
"schema downgrade migrations go here."
""
$ {
downgrades
if downgrades
else "pass"
}
def data_upgrades(): ""
"Add any optional data upgrade migrations here!"
""
pass def data_downgrades(): ""
"Add any optional data downgrade migrations here!"
""
pass""
"rev one Revision ID: 3ba2b522d10d Revises: None Create Date: 2014-03-04 18:05:36.992867 "
""
# revision identifiers, used by Alembic.revision = '3ba2b522d10d'
down_revision = None from alembic
import op
import sqlalchemy as sa from sqlalchemy
import String, Column from sqlalchemy.sql
import table, column from alembic
import context def upgrade(): schema_upgrades() if context.get_x_argument(as_dictionary = True).get('data', None): data_upgrades() def downgrade(): if context.get_x_argument(as_dictionary = True).get('data', None): data_downgrades() schema_downgrades() def schema_upgrades(): ""
"schema upgrade migrations go here."
""
op.create_table("my_table", Column('data', String)) def schema_downgrades(): ""
"schema downgrade migrations go here."
""
op.drop_table("my_table") def data_upgrades(): ""
"Add any optional data upgrade migrations here!"
""
my_table = table('my_table', column('data', String), ) op.bulk_insert(my_table, [{
'data': 'data 1'
}, {
'data': 'data 2'
}, {
'data': 'data 3'
}, ]) def data_downgrades(): ""
"Add any optional data downgrade migrations here!"
""
op.execute("delete from my_table")alembic - x data = true upgrade headalembic is great but lacks an out of the box way to set up running migrations against a specific database (e.g. development, test, production). The following adjustments to its env.py and alembic.ini allow us to target a specific database:, Instantly share code, notes, and snippets.
alembic - x db = development upgrade head
target_metadata = None
cmd_kwargs = context.get_x_argument(as_dictionary=True)
if 'db' not in cmd_kwargs:
raise Exception('We couldn\'t find `db` in the CLI arguments. '
'Please verify `alembic` was run with `-x db=<db_name>` '
'(e.g. `alembic -x db=development upgrade head`)')
db_name = cmd_kwargs['db']
# ...
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# Overload db config on top of alembic config
alembic_config = config.get_section(config.config_ini_section)
db_config = config.get_section(db_name)
for key in db_config:
alembic_config[key] = db_config[key]
engine = engine_from_config(
alembic_config,
prefix='sqlalchemy.',
poolclass=pool.NullPool)alembic - x db = development upgrade head
target_metadata = None
cmd_kwargs = context.get_x_argument(as_dictionary=True)
if 'db' not in cmd_kwargs:
raise Exception('We couldn\'t find `db` in the CLI arguments. '
'Please verify `alembic` was run with `-x db=<db_name>` '
'(e.g. `alembic -x db=development upgrade head`)')
db_name = cmd_kwargs['db']
# ...
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
# Overload db config on top of alembic config
alembic_config = config.get_section(config.config_ini_section)
db_config = config.get_section(db_name)
for key in db_config:
alembic_config[key] = db_config[key]
engine = engine_from_config(
alembic_config,
prefix='sqlalchemy.',
poolclass=pool.NullPool)alembic.ini:
[alembic] #... [development] sqlalchemy.url = postgresql: //localhost/dev [test] sqlalchemy.url = postgresql: //localhost/test [production] sqlalchemy.url = postgresql: //production/prod
You need to ensure that you perform each anycodings_python version of migration with all database anycodings_python credentials, you can write your own anycodings_python migration script in order to do it in anycodings_python one go.,Alembic provides a template to work with anycodings_python multiple databases:,1 - Modify alembic.ini with your anycodings_python database names and SQLAlchemy URLs,Within Flask i have setup SQLAlchemy to use anycodings_python multiple databases which are selected upon anycodings_python request as described in anycodings_python https://quanttype.net/posts/2016-03-15-flask-sqlalchemy-and-multitenancy.html
Alembic provides a template to work with anycodings_python multiple databases:
alembic init--template multidb. / multidb1 - Modify alembic.ini with your anycodings_python database names and SQLAlchemy URLs
#alembic.ini
databases = engine1, engine2
[engine1]
sqlalchemy.url = driver: //user:pass@localhost/dbname
[engine2]
sqlalchemy.url = driver: //user:pass@localhost/dbname22 - Point all target_metadata in anycodings_python multidb/env.py to the same model
# env.py
from models
import mymodel
target_metadata = {
"engine1": mymodel.Base.metadata,
"engine2": mymodel.Base.metadata,
}For example, you have 2 databases DB1 anycodings_python and DB2 mapped to the same set of ORM anycodings_python models in models.py, the migration anycodings_python script looks like :
from alembic import command from alembic.config import Config cfg = Config(YOUR_ALEMBIC_CFG_FILE_PATH) # for autogenerate is True, make sure you set correct `target_metadata` in env.py result = command.revision(config = cfg, message = 'some msg', autogenerate = True, rev_id = your_last_rev_id) # update url with DB1 credential cfg.set_main_option(name = 'sqlalchemy.url', value = db1_credential) command.upgrade(config = cfg, revision = result.revision) # update url with DB2 credential cfg.set_main_option(name = 'sqlalchemy.url', value = db2_credential) command.upgrade(config = cfg, revision = result.revision)