Get database url from .env instead of .ini file

[Zaffer]

Surely it is better practice to load the sqlalchemy URL from the .env file compared to the .ini file.

Can we please make a env.py template that simply loads in the URL from a more robust .env variable instead of needing to save the url into a .ini file that will be part of the repo and have secrets stored in it...

[CaselIT]

Please note that you are free to modify the env file however it pleases you, the templates are just example that can be used as base

[zzzeek]

from https://alembic.sqlalchemy.org/en/latest/tutorial.html#the-migration-environment, relevant emphasis highlighted:

env.py - This is a Python script that is run whenever the alembic migration tool is invoked. At the very least, it contains instructions to configure and generate a SQLAlchemy engine, procure a connection from that engine along with a transaction, and then invoke the migration engine, using the connection as a source of database connectivity.

The env.py script is part of the generated environment so that the way migrations run is entirely customizable. The exact specifics of how to connect are here, as well as the specifics of how the migration environment are invoked. The script can be modified so that multiple engines can be operated upon, custom arguments can be passed into the migration environment, application-specific libraries and models can be loaded in and made available.

[mbanerjeepalmer]

I know this is an old issue and not particularly active, so clearly not that important.

But I want to echo that this is a strange default. I would happily PR a change if the maintainers are open to it.

[zzzeek]

environment variables are not a best practice by today's standards either

[mbanerjeepalmer]

Agree. But I think a widely-accepted ranking is:

1. Secrets manager: 'best practice'.
2. .env: Fine for 95% of cases.
3. Commit secrets to repo: Never do this.

I understand you're not keen for this to be changed, so I'll drop it.

But for anyone coming here from Google, you might find the following snippets helpful as one reasonable way to get started.

This is the specific part of the docs that explains how to change config.

You can see it being used with environment variables here.

db_user = os.environ.get('DB_USER', 'payloadtracker')
db_password = os.environ.get('DB_PASSWORD', 'payloadtracker')
db_host = os.environ.get('DB_HOST', 'localhost')
db_port = os.environ.get('DB_PORT', '5432')
db_name = os.environ.get('DB_NAME', 'payloadtracker')


config.set_main_option(
    'sqlalchemy.url',
    'postgresql://{}:{}@{}:{}/{}'.format(db_user,
                                         db_password,
                                         db_host,
                                         db_port,
                                         db_name))

The later sections are unchanged from the default:

    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,

        config.get_section(config.config_ini_section),

Searching run_migrations_online yields many different ways to do this. But remember, never commit secrets to your repo!

[CaselIT]

Don't do that string formatting, since care must be taken to property escape special characters, that are common at least in passwords.

Construct the url directly if you have the single parts: https://docs.sqlalchemy.org/en/20/core/engines.html#sqlalchemy.engine.URL.create