#Nested transaction using get_session()/advanced_alchemy from litestar

1 messages · Page 1 of 1 (latest)

glacial quiver
#

Hey there 👋

litestar pulls the advanced_alchemy package which I use to retrieve my session in another layer than the route functions using get_session()
Though i'm not so sure how to deal with nested transaction with it. Not the best place to ask for help since it's SQLAlchemy focus, let me know if i should move it.

You can check the code on the screen capture I linked.

So create_account is called from the POST route and use the session using get_session().
create_account calls create_token which retrieves the session and commit a new token associated with the new account.
After creating and committing the token, the account is committed.

I want to ensure that my token creation is rollbacked when an error happens in the create_account function or even better in the route calling create_account.

I read the SQLAlchemy documentation which say to use begin_nested but I also read on other place that you shouldn't deal with this when using get_session so i'm a bit confused now. Could someone help on that ?

Thanks 🙂

carbon.png
prisma sigil
#

Well, I must ask, is this for user login?

#

There are several examples of this floating around that are much better starting points that I can link you to.

#

Also, is there a reason not to use dependency injection here for the session?

#

Also, begin nested is probably not going to do what you want

#

Ok, so a couple of points to get you going in the right direction on this

#

When you use the Adv. Alchemy plugin, you automatically get a database session that is scoped for the request

royal dustBOT
#

src/app/domain/system/controllers.py line 40

async def check_system_health(self, db_session: AsyncSession, task_queues: TaskQueues) -> Response[SystemHealth]:
prisma sigil
#

the db_session is automatically injected here and is a fresh session

#

additionally, when a request is returned, the autocommit handler will automatically commit any response that is in the 2xx range and rollback and 4xx+ error codes

#

now, with the understanding that the session is auto-injected, you can layer in the repositories and quite a bit of other functionality here

royal dustBOT
#

src/app/domain/accounts/controllers/access.py line 67

async def signup(self, users_service: UserService, data: DTOData[AccountRegister]) -> User:
prisma sigil
#

hope this helps

royal dustBOT
#

src/app/lib/db/base.py line 90

before_send_handler=autocommit_before_send_handler,
glacial quiver
royal dustBOT
#

advanced_alchemy/config/asyncio.py line 74

async def get_session(
glacial quiver
#

Also, is there a reason not to use dependency injection here for the session?

No reason not to do this actually, i'll look into it

prisma sigil
#

do you mind sharing the code you have in your screenshot?

#

it's hard for me to help with code samples otherwise

glacial quiver
#

Sure ! Do you mind if I add you on my private repo ?

prisma sigil
#

nope. that's fine by me

#

cofin is my github user

glacial quiver
prisma sigil
#

just accepted it. I'll take a look and push some updates to a new branch for you to review

#

thanks for being patient

prisma sigil
#

@glacial quiver there's a significant chunk of this that doesn't really use any of the Litestar SQLAlchemy features. I'm going to modify them in my PR so that you can see what it might look like.

prisma sigil
#

@glacial quiver I've pushed several updates to the db_sessions branch. I tried to keep it as close to the model you had but baked in quite a few Litestar/Adv features

#

This included quite a bit of refactoring to the alembic config. If you look at the models.py you'll see a comment with the commands I ran to generate the migrations and apply them.

#

If you do it this way, Alembic will leverage the plugin config to get the database connection.

#

also, each service will use the transaction DI parameter so it's all using the same DB session.

#

If any of the service calls fail, it issues a rollback

#

otherwise it'll commit

#

Please feel free to ask questions about any of the changes.

glacial quiver
#

Just looked at the branch you created !Thank you so much for taking the time to help me on this, greatly appreciated 🙂
Got a few questions after a first read:

1️⃣ Is autogenerated considered good practice in alembic ?
Never used it much in Java, Ruby or past python experience. I always crafted my migrations by hand without autogenerating it

2️⃣ So now alembic is being taken care of by Litestar through the alembic config.
I didn't see in the documentation that it was possible, did I miss it or is it a WIP you have somewhere on a GH issue ?
How is alembic aware of what have changed, is there an autoconfig somewhere in Litestar ? I didn't find any config relating to this in the branch you made

3️⃣ The repository handle crud operations with some queries inherited from SQLAlchemyAsyncRepositoryService in the services class.
Is it good practice to create all my queries with one method per query inside a service (i.e. PermissionService having a more complex query)
It's very similar to what you can do with Spring Data JPA creating repositories, never saw that in python !

4️⃣ Alembic seems to autogenerate GUID PK in migration files. However we use UUIDBase in models which map a UUID PK.
Do you know the reason behind that ? Is it alembic doing it or does Litestar override some stuff during a migration autogeneration ?

#

Again, thanks so much for dedicating some of your time with all my questions 🙏

prisma sigil
#

Hope you don't mind me answering these in multiple messages

#
  1. I use a mix of autogenerate. It will do a good job of generating the shell of the changes you have, but it's not perfect. But it's nearly always given a better starting point than writing by hand.
#
  1. This is an advanced alchemy feature that is not documented yet. There really is no magic we are doing to alembic other than wrapping a click CLI around all of the alembic commands. If you are interested, I'll show you where we extend the AlembicCommands class to include the DB_URL/engine from the SQLAlchemy config
#
  1. well, it helps in the sense you don't have a ton of custom code. I try to do that it this way, but it doesn't always follow this pattern. For instance, if you look at the fullstack repository. You'll see that when you create a "Team" model, it automatically appends the currently logged in user to the Team Permissions table as an owner of the team. Additionally, if you pass in a list of tags, it'll automatically create the tags as well.
#

The service is something we built into SQLAlchemy and it really simplifies the CRUD operations

#

for instance, we'll automatically use the most efficient methods for bulk insert, update, delete, and upsert.

#
  1. Advanced Alchemy includes a built in ORM metadata config that looked exactly like what you already had. I swapped out yours for this metadata (this handles the naming conventions, type mapping ,etc). There are also some integrated helper classes that I've created to make working with models a bit easier. There's a UUID Audit base that uses a GUID data type. This is a custom type that returns a binary GUID for engines that don't support a native UUID and the normal UUID for engines that do support it.
#

In the cases where you need to set SQLAlchemy join options, you pass in the statement into the DI function in dependencies.py

royal dustBOT
#

src/app/domain/accounts/dependencies.py line 29

statement=select(User)
prisma sigil
royal dustBOT
#

src/app/domain/teams/services.py line 73

async def create(self, data: Team | dict[str, Any]) -> Team:
prisma sigil
#

when you create the team, it automatically adds you as the owner and appends in the tags

royal dustBOT
#

src/app/domain/teams/services.py line 85

tags_service = await anext(provide_tags_service(db_session=cast("AsyncSession", self.repository.session)))
prisma sigil
royal dustBOT
#

advanced_alchemy/base.py line 159

class UUIDBase(UUIDPrimaryKey, CommonTableAttributes, DeclarativeBase):
prisma sigil
#

you'll notice that you probably also have a extra column called _sa_orm_sentinel or something like that

#

this is a something we automatically create for you that is required by SQLAlchemy

#

It needs a deterministic identifier when operating on multiple records. You would have gotten an error without this column being declared for certain DBMS operations.