String fields and column length

Some databases have a limit on the length of string columns (e.g., VARCHAR(255) in MySQL) and fail with an error if you try to create a string column without specifying a length.

SQLModel handles this automatically depending on the database dialect you are using. 😎

For databases that require a length for string columns, SQLModel will automatically set a default length (e.g., 255 for MySQL) if you do not specify one.

Python 3.10+

# Code above omitted 👆

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

# Code below omitted 👇

👀 Full file preview

Python 3.10+

from sqlmodel import Field, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

🤓 Other versions and variants

Python 3.9+

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine


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


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

If you run this code with MySQL, SQLModel will create the name column as VARCHAR(255):

CREATE TABLE hero (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
)

But you can always override this by specifying a custom length if needed:

Python 3.10+

# Code above omitted 👆

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=100)

# Code below omitted 👇

👀 Full file preview

Python 3.10+

from sqlmodel import Field, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=100)


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

🤓 Other versions and variants

Python 3.9+

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine


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


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

CREATE TABLE hero (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        PRIMARY KEY (id)
)

This works thanks to AutoString type that SQLModel uses for all string fields by default.

But if you specify the database type of column explicitly, SQLModel will not be able to set the length automatically, and you will need to specify it manually:

Python 3.10+

from sqlmodel import Field, SQLModel, String, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(sa_type=String)

# Code below omitted 👇

👀 Full file preview

Python 3.10+

from sqlmodel import Field, SQLModel, String, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(sa_type=String)


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

🤓 Other versions and variants

Python 3.9+

from typing import Optional

from sqlmodel import Field, SQLModel, String, create_engine


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


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

The code example above will fail on databases that require a length for string columns:

sqlalchemy.exc.CompileError: (in table 'hero', column 'name'): VARCHAR requires a length on dialect mysql

To fix it, you need to specify the length explicitly as follows:

Python 3.10+

from sqlmodel import Field, SQLModel, String, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(sa_type=String(length=255))

# Code below omitted 👇

👀 Full file preview

Python 3.10+

from sqlmodel import Field, SQLModel, String, create_engine


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(sa_type=String(length=255))


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

🤓 Other versions and variants

Python 3.9+

from typing import Optional

from sqlmodel import Field, SQLModel, String, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(sa_type=String(length=255))


database_url = "mysql://user:password@localhost/dbname"

engine = create_engine(database_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


if __name__ == "__main__":
    create_db_and_tables()

This will give:

CREATE TABLE hero (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
)