How to define a UniqueConstraint on two or more columns with SQLModel?

Question

With SQLAlchemy it is possible to create a constraint on two or more columns using the UniqueConstraint declaration.

What would be the most idiomatic way of doing that using SQLModel?

For example:

from sqlmodel import SQLModel, Field


class SubAccount(SQLModel):
    id: int = Field(primary_key=True)
    name: str
    description: str
    user_id: int = Field(foreign_key="user.id")

How can I enforce that name should be unique for a given user_id ?

Peipeiffer

Answer 1

As of now, exactly the same way. The SQLModel metaclass inherits a great deal from the SQLAlchemy DeclarativeMeta class. In many respects, you can treat an SQLModel subclass the way you would treat your SQLAlchemy ORM base class. Example:

from sqlmodel import Field, SQLModel, UniqueConstraint, create_engine


class User(SQLModel, table=True):
    id: int = Field(primary_key=True)
...


class SubAccount(SQLModel, table=True):
    __table_args__ = (
        UniqueConstraint("name", "user_id", name="your_unique_constraint_name"),
    )
    id: int = Field(primary_key=True)
    name: str
    description: str
    user_id: int = Field(foreign_key="user.id")


if __name__ == '__main__':
    engine = create_engine("sqlite:///:memory:", echo=True)
    SQLModel.metadata.create_all(engine)

SQLModel even re-imports compatible classes/functions from SQLAlchemy explicitly to indicate that they can be used the way you expect it from SQLAlchemy.

This is not to say that this works with everything. Far from it. The project is still in its earliest stages. But in this case it does.

Here is the corresponding SQL output for creating the table:

CREATE TABLE subaccount (
    id INTEGER NOT NULL, 
    name VARCHAR NOT NULL, 
    description VARCHAR NOT NULL, 
    user_id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT your_unique_constraint_name UNIQUE (name, user_id), 
    FOREIGN KEY(user_id) REFERENCES user (id)
)

Neckline