Question:I want to optimize the amount of calls my API makes to the database. But is it okay to let Postgres run in to an Unique Constraint error. For example when registering users I have two options:
This would make two calls to the database, but the exceptions would occur in Python. Note that no Error or Exception is thrown at the postgres end. Postgres simply returns nill, which at the python end gets interpreted as DoesNotExist. Another solution would be this:
from app.models import Users from tortoise.exceptions import DoesNotExist try: await Users.get(email=email) raise HTTPException( status_code=HTTP_400_BAD_REQUEST, detail="User already exists" ) except DoesNotExist: user = await Users.create(email, hashed_pw)
This would only make a single database call, however an error would occur at the postgres database. Obviously it seems to me the second implementation would be more efficient, but is it okay to just create an exception at the postgres end?
from app.model import Users from asyncpg.exceptions import UniqueViolationError try: user = await Users.create(email, hashed_pw) except UniqueViolationError: raise HTTPException( status_code=HTTP_400_BAD_REQUEST, detail="User already exists" )
Answer:Your first code would only work reliably if you have transactions or locking involved. Otherwise, if two requests to create a user with the same email (I know that this is unlikely in this case) hit your API, then both could get
DoesNotExistas a result and would executed
await Users.create(email, hashed_pw). That kind of pattern is often discouraged.
Also using exceptions for control flow is also something that is often seen as bad practice.
Your second solution is fine, there is no problem with trying to create an entry and use the expectation that is emitted by postgres to tell the request that the user already exists.
The first one would be fine if you have transactions and if you have a function like
await Users.exists(email=email)that returns true or false.
Personally, I would prefer the second one, because the unique constraint already does the check you want to do, but you need to ensure that you actually check if the error message you get is about the unique key for
If you have better answer, please add a comment about this, thank you!