Unable to insert using ORM: Instance <...> has a NULL identity key

[rdong8]

Example:

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    __table_args__ = {"schema": "app"}

    # Not a real column - just here to satisfy SQLAlchemy's requirement for a primary key for every table
    id: Mapped[int] = orm.mapped_column(
        BigInteger, primary_key=True, autoincrement=True
    )

    email: Mapped[Optional[str]] = orm.mapped_column(String)

The following code fails:

engine = create_engine("bigquery://", echo=True)
Base.metadata.create_all(engine)

row = User(email="[email protected]")

with Session(engine) as session:
    with session.begin():
        session.add(row)

FlushError: Instance <User at 0xffff90209d30> has a NULL identity key.  If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.  Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

[tswast]

FWIW: BigQuery does support primary key columns now (not enforced, but used for optimization), but I don't think anything like auto-increment has been implemented in the backend. https://cloud.google.com/blog/products/data-analytics/join-optimizations-with-bigquery-primary-and-foreign-keys