.ini file load environment variable

Question

I am using Alembic for migrations implementation in a Flask project. There is a alembic.ini file where the database configs must be specified:

sqlalchemy.url = driver://user:password@host/dbname

Is there a way to specify the parameters from the environment variables? I've tried to load them in this way $(env_var) but with no success. Thanks!

Highams

@Oz123, sqlalchemy.url = $(DB_SERVICE):/$(DB_USER):$(DB_PASS)@$(DB_HOST)/$(DB_NAME) – Highams

@Pitiful I am using sqlalchemy with declarative base, but as far as I know Flask-Migrate is compatible only with Flask-SQLAlchemy – Highams

You're right. I guess I just assumed you were using that. Why not set sqlachemy.url in env.py instead? – Pitiful

Answer 1

I've solved the problem by setting sqlalchemy.url in env.py as @dirn suggested.

config.set_main_option('sqlalchemy.url', <db_uri>) did the trick, where <db_uri> can be loaded from environment or config file.

Highams

Are you able to provide more information to your solution? I'm not sure where this env.py file is. – Lathy

@Lathy In alembic directory of your project, see alembic.zzzcomputing.com/en/latest/tutorial.html – Spleeny

answer could be improved. should had specified that need "from alembic import context" command – Hoyden

Answer 2

I was looking for a while how to manage this for mutli-databases

Here is what I did. I have two databases : logs and ohlc

According to the doc, I have setup the alembic like that

alembic init --template multidb

alembic.ini

databases = logs, ohlc
[logs]
sqlalchemy.url = postgresql://botcrypto:botcrypto@localhost/logs
[ohlc]
sqlalchemy.url = postgresql://botcrypto:botcrypto@localhost/ohlc

env.py

[...]
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
logger = logging.getLogger('alembic.env')

# overwrite alembic.ini db urls from the config file
settings_path = os.environ.get('SETTINGS')
if settings_path:
    with open(settings_path) as fd:
        settings = conf.load(fd, context=os.environ) # loads the config.yml
    config.set_section_option("ohlc", "sqlalchemy.url", settings["databases"]["ohlc"])
    config.set_section_option("logs", "sqlalchemy.url", settings["databases"]["logs"])
else:
    logger.warning('Environment variable SETTINGS missing - use default alembic.ini configuration')
[...]

config.yml

databases:
    logs: postgresql://botcrypto:[email protected]:5432/logs
    ohlc: postgresql://botcrypto:[email protected]:5432/ohlc

usage

SETTINGS=config.yml alembic upgrade head

Hope it can helps !

Nylons

Answer 3

Building on top of @dimmg's answer:
You can overwrite the sqlalchemy.url specified in alembic.ini in the env.py file.

Single Database

env.py

Insert

config.set_main_option('sqlalchemy.url', <db_uri>)

where <db_uri> can be loaded from the environment or config file.

Multi-Database

env.py

Assuming you a have multi-database setup with databases db_a and db_b, insert

config.set_section_option('db_a', 'sqlalchemy.url', <db_uri_a>)
config.set_section_option('db_b', 'sqlalchemy.url', <db_uri_b>)

where <db_uri_a> and <db_uri_b> are the database URI for db_a and db_b, respectively, and can be loaded from the environment or config file.

Note

Make sure to also indicate in the alembic.ini file that the parameters specified there are overwritten in in the env.py file. The section in which the sqlalchemy.urls are specified could even be removed entirely for all DB's for which the URI is overwritten in env.py.
This will hopefully save you or collaborators some confusion when returning to the project later.

Pl

Answer 4

Just to add to the existing answers, the alembic tutorial has this to say:

• sqlalchemy.url - A URL to connect to the database via SQLAlchemy. This configuration value is only used if the env.py file calls upon them; in the “generic” template, the call to config.get_main_option("sqlalchemy.url") in the run_migrations_offline() function and the call to engine_from_config(prefix="sqlalchemy.") in the run_migrations_online() function are where this key is referenced. If the SQLAlchemy URL should come from some other source, such as from environment variables or a global registry, or if the migration environment makes use of multiple database URLs, the developer is encouraged to alter the env.py file to use whatever methods are appropriate in order to acquire the database URL or URLs.

Felicidadfelicie

Answer 5

1. create a file called .env and store the url in it with:

DB_URL = <your_database_url>

2. Modify sqlalchemy.url in alembic.ini file as follow:

sqlalchemy.url = %(DB_URL)s

3. Load DB_URL into the env.py generated by alembic with:

from dotenv import load_dotenv
load_dotenv()

config = context.config

config.set_main_option("DB_URL", os.environ["DB_URL"])

4. Continue with creating migration versions (e.g. alembic revision --autogenerate -m "Baseline")

Desexualize

Answer 6

from dotenv import load_dotenv
load_dotenv()

env.py(filename)

def get_url():
   return os.getenv("PERSISTENCE_DSN")


def run_migrations_online():
   connectable = create_engine(get_url())
   with connectable.connect() as connection:
       """
       Configure migration context
       1. Pass our models metadata
       2. Set schema for alembic_version table
       3. Load all available schemas
       """
       context.configure(
           connection=connection,
           target_metadata=target_metadata,
           version_table_schema=target_metadata.schema,
           include_schemas=True,
           include_name=include_name,

       )
       with context.begin_transaction():
           """
           By default search_path is setted to "$user",public 
           that why alembic can't create foreign keys correctly
           """
           context.execute('SET search_path TO public')
           context.run_migrations().env(filename)
 
    PERSISTENCE_DSN='postgresql://test:[email protected]:5432/test'

Tautomerism

Answer 7

I believe the best way would be to get the DB URI used inside the project. Get it however it has been used there.

Like my fastapi project used to load it from .env and then creating the sqlalchemy Base and all. I just used the same function here.

    from backend.database import get_db_uri
    
    # this is the Alembic Config object, which provides
    # access to the values within the .ini file in use.
    config = context.config
    
    
    config.set_main_option('sqlalchemy.url', get_db_uri())

Bahadur