DEV Community

Cover image for How To Make a Field Unique With SQLModel
Kyei Samuel
Kyei Samuel

Posted on

How To Make a Field Unique With SQLModel

Creating unique fields in SQLmodel is very simple and I would like to make this post too very simple for you by showing you the codes first and then proceed to explaining what it does. I am assuming you know how to create database and tables, and how to populate them with data using sqlmodel already. Let's begin.

If you have not read about SQLmodel, don't worry.
SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.
feel free to check it out here - SQLModel Documentation

There are two approaches in achieving this. Let's take a look at the model we will be working with.

from sqlmodel import SQLModel, Field
from typing import Optional

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    email: str
Enter fullscreen mode Exit fullscreen mode

Let's make the email field unique in this model or table

1. Using the __table_args__

from sqlmodel import SQLModel, Field
from sqlalchemy import UniqueConstraint
from typing import Optional

class User(SQLModel, table=True):
    __table_args__ = (UniqueConstraint("email"),)
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    email: str
Enter fullscreen mode Exit fullscreen mode

creating the database and tables we get the output below.
how to create database and tables

...

CREATE TABLE user (
        id INTEGER,
        name VARCHAR NOT NULL,
        email VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (email)
)

...
Enter fullscreen mode Exit fullscreen mode

2. Using the sa_column

...

from sqlmodel import SQLModel, Field
from sqlalchemy import UniqueConstraint, Column, String
from typing import Optional


class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    email: str = Field(sa_column=Column("email", String, unique=True))

...
Enter fullscreen mode Exit fullscreen mode

Again, creating the database and tables we get the output below.

...

CREATE TABLE user (
        id INTEGER,
        name VARCHAR NOT NULL,
        email VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (email)
)
...
Enter fullscreen mode Exit fullscreen mode

Now let's attempt to add two users with the same email to the user table

...

with Session(engine) as session:
        user1 = User(name="Kyei Samuel", email="kyeisamuel931@gmail.com")
        user2 = User(name="Rex Osei", email="kyeisamuel931@gmail.com")

        session.add(user1)
        session.add(user2)

        session.commit()
...
Enter fullscreen mode Exit fullscreen mode

Notice the error we get in the terminal

...

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.email
[SQL: INSERT INTO user (email, name) VALUES (?, ?)]
[parameters: ('kyeisamuel931@gmail.com', 'Rex Osei')]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

...
Enter fullscreen mode Exit fullscreen mode

This is an Integrity error and it occurred because of a failure of a unique constraint on the email field of the user model.
This shows that the unique constraint was successfully created.

Now between these two methods, the one to choose depends on your preference. I personally do not know the effect of using any of these methods so feel free to choose one.๐Ÿ˜Š

What is going on?

Note that SQLModel is based on both SQLAlchemy and Pydantic. This means that a SQLModel is actually a SQLAlchemy model and also a pydantic model. Therefore, if there is anything SQLModel is not providing directly, we can always access SQLAlchemy directly and implement that feature. That is why we were able to import "UniqueConstraint", "Column" as well as "String" from sqlalchemy to make the email field unique.

What is __table_args__?

It is a class attribute in SQLModel(ofcourse SQLAlchemy) and it takes table arguments other than the name, metadata, and mapped
Column. This attribute accommodates both positional as well as keyword arguments that are normally sent to the Table constructor. The attribute can be specified in one of two forms, dictionary and tuple.

What is sa_column?

It is a parameter of the Field function in SQLModel and it helps us to define a field or column in the SQLAlchemy way using the Column class from SQLAlchemy. This actually means that sa_column takes an instance of the Column class as its value. With this, we are able to define a field while having access to all the parameters provided by SQLAlchemy Column class. That is why we were able to set the unique parameter of the email field to True.

Conclusion

Alright, that's it for this post, I hope you understood everything and also you were able to implement it in your code. This is my first post, kindly feel free to share your comments and your advices are warmly welcome๐Ÿ˜Š. Thank you.

Discussion (5)

Collapse
kiotokun profile image
kiotokun

Another workaround:

email: str = Field(sa_column_kwargs={'unique': True})

Collapse
aprilahijriyan profile image
Aprila Hijriyan

Very helpful ! thank you

Collapse
rexosei profile image
Kyei Samuel Author

You are welcome๐Ÿ˜Š

Collapse
bguernouti profile image
Guernouti Billel

Really helpful, thank you a lot

Collapse
rexosei profile image
Kyei Samuel Author

You are welcome ๐Ÿ˜Š