SQLAlchemy/FastAPI: TestClient how to test Sequence (using sqlite)

Question

I'm using the FastAPI TestClient as described here. Works fine except for creating a resource since I'm using a sequence for the ID columns:

ItemID = Column(
    BigInteger,
    Sequence("item_id_seq"),
    index=True,
    primary_key=True,
    nullable=False,
)

I know sqlite does not support sequencer. So it is just ignored. But when creating an item I get a NULL constraint exception since the ID is never set.

Is there a workaround? Is there an alternative SQL database for such tests?

Is there a reason not to use `AUTOINCREMENT`: https://stackoverflow.com/questions/2817038/how-can-i-create-a-sequence-in-sqlite — rfkortekaas, Jul 05 '21 at 09:03
Thanks for the eye opener. We don't really need the sequence using autoincrement works fine w/ both sqlite and MS SQL server. Nevertheless it does not work with BigInteger and sqlite. But here is a solution to handle both: https://stackoverflow.com/a/23175518/2606766 — HeyMan, Jul 05 '21 at 09:57
@rfkortekaas thanks a lot. If rephrase your comment as an answer we could mark the question as solved. — HeyMan, Jul 05 '21 at 09:58

score 0 · Accepted Answer · answered Jul 09 '21 at 09:57

Based on the comment of @rfkortekaas (all credits go to him): We now use AUTOINCREMENT instead of a Sequence. Since this is the default when the primary_key is an integer the following is sufficient:

ItemID = Column(
    BigIntegerType,
    index=True,
    primary_key=True,
)

Since this does not apply for BigInteger and sqlite we use our own data type (BigIntegerType) thus using BigInteger on our MS SQL Server in production and Integer for sqlite for testing:

BigIntegerType = BigInteger()
BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), "sqlite")

SQLAlchemy/FastAPI: TestClient how to test Sequence (using sqlite)

1 Answers1