SQLAlchemy TEXT constraint examples

queries.sql

-- №1
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY, 
	name TEXT NOT NULL CHECK (length(name) <= 100), 
	PRIMARY KEY (id)
)


-- №2
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY, 
	name TEXT NOT NULL CONSTRAINT name_length_check CHECK (length(name) <= 100), 
	PRIMARY KEY (id)
);


-- №3
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY, 
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (length(name) <= 100)
);


-- №4
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY, 
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (length(name) <= 100)
);


-- №5
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY, 
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT name_length_check CHECK (length(name) <= 100)
);

sqla-text-constraint-example.py

from sqlalchemy import (
    Text,
    Identity,
    create_engine,
    CheckConstraint,
    func,
    text,
    BigInteger,
)

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


engine = create_engine(
    "postgresql+psycopg://postgres:postgres@localhost:5432/postgres",
    echo=True,
)


class Base(DeclarativeBase):
    pass


# !!! выбрать только один из вариантов Constraint!


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(
        BigInteger,
        Identity(always=True),
        primary_key=True,
    )
    name: Mapped[str] = mapped_column(
        Text,
        # №1 можно тут, но только в виде строчки, так как колонки ещё нет
        CheckConstraint("length(name) <= 100"),
        # №2 либо через строчку, но через обертку к имени. и не забываем имя для ограничения
        CheckConstraint(
            func.length(text("name")) <= 100,
            name="name_length_check",
        ),
        nullable=False,
    )
    __table_args__ = (
        # №3 можно просто в виде строчки
        CheckConstraint("length(name) <= 100"),
        # №4 можно выражением:
        CheckConstraint(func.length(name) <= 100),
        # №5 и не забываем указать имя:
        CheckConstraint(
            func.length(name) <= 100,
            name="name_length_check",
        ),
    )


sql_1 = """
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY,  
	name TEXT NOT NULL CHECK (length(name) <= 100), 
	PRIMARY KEY (id)
)
"""

sql_2 = """
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY,  
	name TEXT NOT NULL CONSTRAINT name_length_check CHECK (length(name) <= 100), 
	PRIMARY KEY (id)
);
"""

sql_3 = """
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY,  
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (length(name) <= 100)
);
"""

sql_4 = """
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY,  
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (length(name) <= 100)
);
"""

sql_5 = """
CREATE TABLE users (
	id BIGINT GENERATED ALWAYS AS IDENTITY,  
	name TEXT NOT NULL, 
	PRIMARY KEY (id), 
	CONSTRAINT name_length_check CHECK (length(name) <= 100)
);
"""


def main() -> None:
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)


if __name__ == "__main__":
    main()