Advanced alchemy - multiple databases

[crude nymph]

I've been looking at the fullstack implementation of LiteStar and thought I'd try to copy a couple of features in use. I noticed Advanced Alchemy makes use of their own orm_registry object, which stores all the model information. This is also used in alembic to build up the database.

I want to split my business data from my authentication data, and so have two different databases in play. How can I have two different registries?

The only progress I've made is building two different AsyncConfig objects.

alchemy_auth = SQLAlchemyAsyncConfig(
    engine_instance=settings.db_auth.get_engine(),
    before_send_handler=autocommit_before_send_handler,
    session_config=AsyncSessionConfig(expire_on_commit=False),
    alembic_config=AlembicAsyncConfig(
        script_config=settings.db_auth.migration_config,
        script_location=settings.db_auth.migration_path,
    ),
)

alchemy_business = SQLAlchemyAsyncConfig(
    engine_instance=settings.db_business.get_engine(),
    before_send_handler=autocommit_before_send_handler,
    session_config=AsyncSessionConfig(expire_on_commit=False),
    alembic_config=AlembicAsyncConfig(
        script_config=settings.db_business.migration_config,
        script_location=settings.db_business.migration_path,
    ),
)

[flat pelican]

Notes for Advanced alchemy - multiple databases

At your assistance

@crude nymph

No Response?

If no response in a reasonable time, ping @Member.

Closing

To close, type !solve or byte solve.

MCVE

Please include an MCVE so that we can reproduce your issue locally.

[crude nymph]

(More context)

My auth and business models have been separated into different folders.

db
|- auth
|   |- fixtures
|   |- migrations
|   |- models
|   |   |- base.py
|   |   |- ...
|
|- business
|   |- ...

In base.py for auth, I've essentially just copied the declarative base used for the fullstack.

from __future__ import annotations

from typing import Any
from advanced_alchemy.base import orm_registry
from sqlalchemy.orm import DeclarativeBase


class SQLQuery(DeclarativeBase):
    """Base for all SQLAlchemy custom mapped objects."""

    __allow_unmapped__ = True
    registry = orm_registry

    def to_dict(self, exclude: set[str] | None = None) -> dict[str, Any]:
        """Convert model to dictionary.

        Returns:
            dict[str, Any]: A dict representation of the model
        """
        exclude = exclude.union("_sentinel") if exclude else {"_sentinel"}
        return {
            field.name: getattr(self, field.name)
            for field in self.__table__.columns
            if field.name not in exclude
        }

[errant solar]

there's a function in AA to create a registry

if you don't want to share models between the 2 databases, you'll want to configure the second config with a different metadata

ultimately, @prime quail and I want to have a mechanism for using multiple engines out of the box. I'm not sure if it'll end up being to configure 2 plugins with different session injection keys or wht thoughh

[errant solar]

Also, this declarative base is used for a special case in fullstack, although it'll work for other uses.

I use it so that i can set any arbitrary SQL query as a SQLA object

and you can then use those objects in relationships

Here's an example of how you might use the above SQLQuery class

class UserWorkspaceCount(MappedAsDataclass, SQLQuery):
    """Member Count."""

    __table__ = (
        sa.select(User.id.label("user_id"), sa.func.count(WorkspaceMember.id).label("active_workspaces"))
        .join_from(User, WorkspaceMember, onclause=User.id == WorkspaceMember.user_id, isouter=True)
        .group_by(User.id)
    ).alias("user_workspace_count")
    user_id: UUID
    active_workspaces: int

now you can use UserWorkspaceCount as if it was a table

[crude nymph]

Aah very nice.

Whats the method for creating a 2nd registry?

[errant solar]

from advanced_alchemy.base import create_registry

etl_registry = create_registry()
duckdb = SQLAlchemySyncConfig(
    session_dependency_key="local_db_session",
    connection_string="duckdb:///:memory:",
    metadata=etl_registry.metadata,
)

[crude nymph]

Thanks 🙂 So I'd just be using that everywhere instead of the static orm_registry I assume. Are there any functions I should worry about that will be using the static one instead of my defined ones?

[dreamy iron]

Hi!
@crude nymph can you share some information about how you implement multi-db configuration (migration, session handling and querying data)?

[errant solar]

I'll add some details for you

take this example

db1_config = SQLAlchemyConfig(session_dependency_key="local_db_session",...)
db2_config = SQLAlchemyConfig(session_dependency_key="db_session",...)

local_alchemy = SQLAlchemyPlugin(config=db1_config)
alchemy = SQLAlchemyPlugin(config=db2_config)

app = Litestar(
    plugins=[local_alchemy, alchemy],
)

to use session one in a route, use local_db_session as your dependency key

to use the other one, use db_session in your route handler

same for your dependencies

async def provide_roles_service(db_session: AsyncSession | None = None) -> AsyncGenerator[RoleService, None]:
    """Provide roles service.

    Args:
        db_session (AsyncSession | None, optional): current database session. Defaults to None.

    Returns:
        RoleService: A role service object
    """
    async with RoleService.new(
        session=db_session,
        statement=select(Role).options(selectinload(Role.users).options(joinedload(UserRole.user, innerjoin=True))),
    ) as service:
        yield service

to make the roles service use local_db_session instead, just change the parameter name

[dreamy iron]

Nice!

thanks

But what about migrations?

[errant solar]

well, there's nothing stopping you from using alembic migrations here. I haven't ported the multi-database engine template over, but it can still be done by following the alembic docs

The key is to to split your registry like i mentioned abov

this will keep objects specified for one database separated from the other

the alembic config for AA accepts a metadata setting or reads one that you provided in the sqlalchemy config

we should be able to add a flag in the CLI to specify which metadata to use for the litestar database wrapper as well

Also, there's another use case that we plan to build right in that uses multiple databases. And that's support for read only connections. You could specify a read pool of servers to use for querying and that's what would be use for all lookups, while the other main connection is only used for DML/DDL

To do this, the repo and service need to be aware of the connections, so there's an update required for that

[dreamy iron]

Hi @errant solar !
I have one interesting case.
My dependencies for databases override each other depending on the position in the plugins.
Example: I have db1 and db2, and each has different configs (I set custom: metadata, engine_instance, engine_dependency_key, engine_app_state_key, and session_dependency_key), and depending on the order in plugins I have access only to the last.
No matter what session_dependency_key is used.

[errant solar]

I think i know what's happening here. I may need to make an update to AA

@prime quail, i think this has to do with the get plugin lookup. I think it's returning the first one that matches a certain type.

@dreamy iron As a test, can you can make a custom implementation fo the SQLAlchemyPlugin and test it out?

class MySecondDatabasePlugin(SQLAlchemyPlugin)

and then configure the second plugin to use the one you extended

if this works, I think we can update it so this extending isn't required

[dreamy iron]

Sure, np.

[dreamy iron]

@errant solar Same problem (

[errant solar]

Ok. I think it’s still related. I’ll have to take a look when I get back to my laptop. I’ve been messaging from my phone, so I probably missed one thing in my suggestion for you.

[dreamy iron]

Thanks!!

[dreamy iron]

Hi @errant solar!
Any updates about a fix for multi-config issues?

[errant solar]

yeah, I have an idea for a fix for this. It's probably going to be this weekend before I get a chance to look into it though.