Should I code Postgres to run into an exception?-CodePudding

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:

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 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.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"
    )

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?

CodePudding user response：

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 DoesNotExist as 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 email and not about some other error.