What is the best practice for defining an auto-incrementing primary key using Tiangolo's SqlModel library?

Question

I am leveraging Tiangolo's SqlModel library for a FastAPI + PostgreSQL application.

As best as I can tell, primary keys are defined as follows:

class SomeSchema(SqlModel, table=True):
    __tablename__ = 'some_schema'
    id: Optional[int] = Field(default=None, primary_key=True)

Using this pattern, I receive SQL Alchemy warnings:

SAWarning: Column 'some_schema.id' is marked as a member of the primary key for table 'some_schema', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed. Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.

Unlike sqlalchemy Column objects, the sqlmodel Field object has no autoincrement option. The sqlmodel documentation says nothing about auto-incrementing ID columns.

What is the best practice for defining an id column as an auto-incrementing primary key using this library?

There's an extensive article on exactly this subject on the website for that library: have you read [that article](https://sqlmodel.tiangolo.com/tutorial/automatic-id-none-refresh/)? — Grismar, Aug 31 '22 at 23:21
@Grismar I have read that article. Perhaps I am not comprehending it well, but I don't see how it is relevant to my problem. Where does it show how to define a column as an auto-incrementing primary key? — muad-dweeb, Sep 01 '22 at 00:06

score 0 · Answer 1 · answered Sep 01 '22 at 01:04

The documentation provides an example that shows how to implement this, which boils down to:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)

heroes = [
    Hero(name="Deadpond", secret_name="Dive Wilson"),
    Hero(name="Spider-Boy", secret_name="Pedro Parqueador"),
    Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
]

with Session(engine) as session:
    for hero in heroes:
        session.add(hero)

    session.commit()

    for hero in heroes:
        session.refresh(hero)
        print(hero)

The output, the first time you run it:

age=None id=1 name='Deadpond' secret_name='Dive Wilson'
age=None id=2 name='Spider-Boy' secret_name='Pedro Parqueador'
age=48 id=3 name='Rusty-Man' secret_name='Tommy Sharp'

And the second time:

age=None id=4 name='Deadpond' secret_name='Dive Wilson'
age=None id=5 name='Spider-Boy' secret_name='Pedro Parqueador'
age=48 id=6 name='Rusty-Man' secret_name='Tommy Sharp'

And that's just a slimmed down version of the more complete example with logging on the site itself. Note how id is a basic auto-incrementing integer primary key.

It provides an explanation for the why of having a default None value there in a separate article. It's unclear where you're running into the warning you mentioned exactly, but that warning does not occur when running the above example (also not when echo=True is passed when creating the engine). If you have a more specific problem / question, you should update your question.

What is the best practice for defining an auto-incrementing primary key using Tiangolo's SqlModel library?

1 Answers1