Update database schema automatically

[flansen]

** Please make sure you read the contribution guide and file the issues in the right place. **
Contribution guide.

Is your feature request related to a problem? Please describe.

We have setup our google-adk application with a database using DatabaseSessionService in a cloud deployment with a PostgreSQL database.

There were two adk version updates (1.14.0 and 1.17.0) that enforced schema changes.
We as the users are obliged to manually update the schema and have to catch updates to existing tables ourselves.

This is also stated in 1.14.0 Release Notes:

NOTE: This requires DB migration, run ALTER TABLE events ADD COLUMN custom_metadata JSON; to migrate existing database tables.

With 1.17.0, the required changes were not even mentioned and I had to manually find out what changes to the DB schema are necessary.

The existing migration mechanisms - to my understanding - does only create new tables.
It does not update existing tables by either editind or deleting columns.

Describe the solution you'd like

The db schema should be automatically updated by the google-adk.
That must be done on startup, but only if a DB is configured for sessions.

Describe alternatives you've considered

%

Additional context

Our solution looks like this:

if session_db_config is None:
    logger.info("using no database")
    session_db_url = None
else:
    try:
        ensure_schema_created(session_db_config)
    except Exception as e:
        logger.error(f"Error creating schema: {e}, ignoring and continuing...", exc_info=e)
    try:
        migrate_if_needed(session_db_config)
    except Exception as e:
        logger.error(f"Error migrating tables: {e}, ignoring and continuing...", exc_info=e)
    session_db_url = session_db_config.get_url(schema=SCHEMA_NAME)

app = get_fast_api_app(
    agents_dir=str(agents_dir),
    web=True,
    session_service_uri=session_db_url,
)

def migrate_if_needed(db_config: DBConfig):
    engine = create_engine(db_config.get_url(schema=SCHEMA_NAME))
    with engine.begin() as connection:
        # required for 1.14.0 migration
        # https://github.com/google/adk-python/releases/tag/v1.14.0
        connection.execute(text("ALTER TABLE sessions.events ADD COLUMN IF NOT EXISTS custom_metadata JSONB"))
        # required for 1.17.0 migration
        # https://github.com/google/adk-python/commit/6ab1498aa0bad783e17e97a2ceede97b4bd3016f
        connection.execute(text("ALTER TABLE sessions.events ADD COLUMN IF NOT EXISTS usage_metadata JSONB"))
        connection.execute(text("ALTER TABLE sessions.events ADD COLUMN IF NOT EXISTS citation_metadata JSONB"))
    logger.info("Altered events table to add custom_metadata column")
    engine.dispose()

[gundeep-dialpad]

@flansen can you please share that sql you had to run to migrate to 1.17.0? or where can I find it. Thanks

[flansen]

@gundeep-dialpad this should do it, I will update my initial post as well :):

# required for 1.17.0 migration
# https://github.com/google/adk-python/commit/6ab1498aa0bad783e17e97a2ceede97b4bd3016f
connection.execute(text("ALTER TABLE sessions.events ADD COLUMN IF NOT EXISTS usage_metadata JSONB"))
connection.execute(text("ALTER TABLE sessions.events ADD COLUMN IF NOT EXISTS citation_metadata JSONB"))

[dandolf81]

Our service account functional users are not granted the ability to execute DDL statements. There has got to be a better way to manage or publish these requisite schema changes within the release documentation.