How do I set an Alchemy Alembic SQLite URL based on the environment?

Setting an Alchemy Alembic SQLite URL based on the environment is crucial for developing and deploying applications effectively. Here's how you can achieve this:

1. Environment Variables:

The most common and recommended way is to use environment variables. You can set an environment variable, such as DATABASE_URL, and then use it in your application. This approach is flexible because it allows you to easily switch configurations without changing the code directly.

2. Example Using Python and os Module:

Here’s an example using Python's os module to read the environment variable and construct the SQLAlchemy URL:

import os
from sqlalchemy import create_engine

def get_database_url():
   """Retrieves the database URL from the environment."""
   default_url = "sqlite:///./database.db" # Default if no env var
   return os.getenv("DATABASE_URL", default_url)

def create_db_engine():
   """Creates an SQLAlchemy engine based on the environment."""
   url = get_database_url()
   return create_engine(url)

if __name__ == '__main__':
   engine = create_db_engine()
   print(f"Database Engine URL: {engine.url}")


3. Setting Environment Variables:

- For Development:

In your development environment, you can set the variable directly in your terminal before running your application:

export DATABASE_URL="sqlite:///./dev_database.db" (Linux/macOS)

or

set DATABASE_URL="sqlite:///./dev_database.db" (Windows)

- For Production:

On production servers, you would typically set these variables using your hosting provider's interface or via configuration management tools. The production URL might be similar to sqlite:///./production_database.db, or another location entirely.

4. Integrating with Alembic:

Alembic also needs the database URL, which is usually configured in the alembic.ini file. You can set this dynamically within the env.py file in the alembic directory:

Example in alembic/env.py:

from sqlalchemy import create_engine
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
import os

def get_database_url():
   """Retrieves the database URL from the environment."""
   default_url = "sqlite:///./database.db" # Default if no env var
   return os.getenv("DATABASE_URL", default_url)

# 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)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

# other values from the config, defined by the needs of env.py,
# can be acquired: config.get_main_option("my_option").

def run_migrations_offline() -> None:
   """Run migrations in 'offline' mode."""
   url = get_database_url() # Get DB URL from env
   context.configure(url=url, target_metadata=target_metadata, literal_binds=True, compare_type=True)

   with context.begin_transaction():
      context.run_migrations()

def run_migrations_online() -> None:
   """Run migrations in 'online' mode."""
   connectable = create_engine(get_database_url()) # Get DB URL from env

   with connectable.connect() as connection:
      context.configure(connection=connection, target_metadata=target_metadata, compare_type=True)

      with context.begin_transaction():
         context.run_migrations()

if context.is_offline_mode():
   run_migrations_offline()
else:
   run_migrations_online()


By setting the DATABASE_URL environment variable and retrieving it using methods like os.getenv(), you can dynamically manage your SQLite database URL for both your SQLAlchemy code and Alembic migrations.

Learn more about this