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
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
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)
)
...
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))
...
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)
)
...
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()
...
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)
...
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.
Top comments (9)
Thanks,
I found it very useful and, incidentally, I found answer to how to use a different column name in the DDBB table (I have been looking for a couple of days to do so).
Wow, you are welcome, buddy
Another workaround:
email: str = Field(sa_column_kwargs={'unique': True})
Bummer! What if we add two users with same email address with different case?! It still creates those 2 records thinking both are different as it's case sensitive.
user1 = User(name="Kyei Samuel", email="kyeisamuel931@gmail.com")
user2 = User(name="Rex Osei", email="Kyeisamuel931@gmail.com")
Very helpful ! thank you
You are welcome😊
Really helpful, thank you a lot
You are welcome 😊
This is great, thank you. Can you show how to make the sqlmodel field have min_length and max_length? Without the need of creating an additional model scheme.