2

A relative sqlalchemy path to a sqlite database can be written as:

sqlite:///folder/db_file.db

And an absolute one as:

sqlite:////home/user/folder/db_file.db

Is it possible to write a path relative to home? Like this:

sqlite:///~/folder/db_file.db

Or even better, can the path contain environment variables?

sqlite:////${MY_FOLDER}/db_file.db

This is the context of an alembic.ini file. So if the previous objectives are not possible directly, may I be able to cheat using variable substitution?

[alembic]
script_location = db_versions
sqlalchemy.url = sqlite:///%(MY_FOLDER)s.db
...
jabozzo
  • 591
  • 1
  • 6
  • 17
  • I have the same issue - have you maybe found a solution or workaround to this? Ideal option for me would be also to contain the environment variables in the URL. – michalrudko Jan 12 '20 at 11:08
  • 1
    I gave up and modified `env.py` to ignore the `alembic.ini` url and use an url imported from a package of my own. In the first lines of `env.py` I write: `import my_package.config as config_; url = config_.DATABASE_URL`, then in `run_migrations_offline` I commented out `#  url = config.get_main_option("sqlalchemy.url")` and in `run_migrations_online` I manually set the url in the first argument of `engine_from_config` by doing the following: `options = config.get_section(config.config_ini_section); options["sqlalchemy.url"] = url; connectable = engine_from_config(options, ...` – jabozzo Jan 13 '20 at 01:47

3 Answers3

3

I have gone around this issue by modifying the values in the config object just after env.py imports it:

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

# import my custom configuration
from my_app import MY_DB_URI
# overwrite the desired value
config.set_main_option("sqlalchemy.url", MY_DB_URI)

Now config.get_main_option("sqlalchemy.url") returns the MY_DB_URI you wanted.

dtgoitia
  • 31
  • 5
1

As others have pointed out, one key is 3 slashes for relative, 4 for absolute.

But it took for me than just that...

Had trouble with just a string, I had to do this:

db_dir = "../../database/db.sqlite"
print(f'os.path.abspath(db_dir): {str(os.path.abspath(db_dir))}')
SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.abspath(db_dir)  # works
# SQLALCHEMY_DATABASE_URI = "sqlite:///" + db_dir  # fails
val
  • 139
  • 1
  • 6
-1

From the alembic documentation (emphasis mine):

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.

So for this case, the sqlalchemy url format can be circunvented and generated by python itself.

jabozzo
  • 591
  • 1
  • 6
  • 17