How to specify a different name for the db table that a SQLModel refers to?

[taranlu-houzz]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import SQLModel, Field

# If the actual db table is called `assets`, for example, how can I keep the following model named `Product` but have it refer to the correct table?

class Product(SQLModel, table=True):

    id: Optional[int] = Field(None, primary_key=True)
    name: str

Description

• I have an existing database with table names that are different than what I would like my model names to be.
• The documentation here under the "SQL Table Names" section notes that there will be information on how to do this in the "Advanced Guide," but that section has not been worked on yet...

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.10.0

Additional Context

No response

[taranlu-houzz]

I tried manually setting __table__ and __tablename__ on the models, but that did not do anything (would be super hacky anyway...).

[taranlu-houzz]

Seems that setting __tablename__ as part of the class definition worked (at least, it seems to have created the table with the correct name when testing with sqlite):

class Product(SQLModel, table=True):

    __tablename__ = "assets"

    id: Optional[int] = Field(None, primary_key=True)
    name: str

I tried this based on reading this in the sqlalchemy docs: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html

Just to confirm though: is this the recommended way to deal with the scenario?

[chriswhite199]

@taranlu-houzz SQLModel is essentially a combination of SQLAlchemy (for ORM) and Pydantic (for validation).

While not explicitly documented in SQLModel, yes you right that using the __tablename__ attribute will amend the auto generated table name for a class.

You can also see in the doc tests that this attribute is asserted:

https://github.com/tiangolo/sqlmodel/search?q=__tablename__

Finally, in the main codebase, you can see that unless set otherwise, the __tablename__ attribute defaults to the class name lowercased:

https://github.com/tiangolo/sqlmodel/blob/f0487546914afea403898fd85757230dc0a0bdb9/sqlmodel/main.py#L634-L636

[taranlu-houzz]

@chriswhite199 Thanks for the confirmation. I'd say my questions is resolved now.

[maresb]

If you're using Pylance/Pyright, you should do

__tablename__: str = name_in_database

If you don't include the : str annotation, you'll see the error

Expression of type "str" cannot be assigned to declared type "declared_attr"
"str" is incompatible with "declared_attr"

Credit to @indivisible for #98 (comment)

[nuno-andre]

A very convenient way to change the table name generation is overriding the __tablename__ class method. E.g. to convert the class name to snake casing:

from sqlmodel import SQLModel as _SQLModel
from sqlalchemy.orm import declared_attr
from .utils import snake_case


class SQLModel(_SQLModel):
    @declared_attr
    def __tablename__(cls) -> str:
        return snake_case(cls.__name__)


class HTTPProxy(SQLModel):
    ...

print(HTTPProxy.__tablename__)
#> http_proxy

# utils.py
from functools import partial
import re

_snake_1 = partial(re.compile(r'(.)((?<![^A-Za-z])[A-Z][a-z]+)').sub, r'\1_\2')
_snake_2 = partial(re.compile(r'([a-z0-9])([A-Z])').sub, r'\1_\2')


def snake_case(string: str) -> str:
    return _snake_2(_snake_1(string)).casefold()

A SQLModel.Config.tablename_generator prop that accepts a callable (like Pydantic's BaseModel.Config.alias_generator) would be great feature.

[cassieopea]

If you're using Pylance/Pyright, you should do

__tablename__: str = name_in_database

I'm using Pyright and this doesn't work. When I try it I get the error

"__tablename__" incorrectly overrides property of same name in class "SQLModel" Pylance(reportIncompatibleMethodOverride)

Not sure why this is happening to me but not others. Perhaps I'm on a newer version of Pyright. There are two fixes. First, use declared_attr:

class Lineitem(SQLModel, table=True):
    @declared_attr
    def __tablename__(cls):
        return "invoice_lineitems"

Second, just tell Pyright to ignore it:

    __tablename__: str = (  # pyright: ignore [reportIncompatibleMethodOverride]
        "invoices_legacy"
    )

or just:

    __tablename__: str = "invoices_legacy"  #  type: ignore

I choose this last method because the others are far too verbose for something as simple as declaring the table name. This may seem hacky, but although I have to tell Pyright to ignore a number of things in sqlmodel, it's not half the file like I have to type: ignore with sqlalchemy.

As I mentioned in #98 (comment), a more intuitive solution that would make Pyright happy might be putting the table name declaration in a Meta as ormar does:

class Lineitem(SQLModel, table=True):
    class Meta:
        tablename: str = "invoice_lineitems"

[kingyue737]

I tried

class SQLModel(_SQLModel):
    @declared_attr
    def __tablename__(cls) -> str:
        return snake_case(cls.__name__)

But mypy complained that

Signature of "__tablename__" incompatible with supertype "SQLModel"

[nuno-andre]

@kingyue737 In the base code, typing is ignored.

class SQLModel(_SQLModel):
    @declared_attr  # type: ignore
    def __tablename__(cls) -> str:
        return snake_case(cls.__name__)

[jonra1993]

What do you think of using the Pascal case instead lowercase or snake case? It can be more intuitive on foreign_key due to already class name is in pascal and cruds are in pascal either.

class SQLModel(_SQLModel):
    @declared_attr  # type: ignore
    def __tablename__(cls) -> str:
        return cls.__name__
        
class RoleBase(SQLModel):
    name: str
    description: str

class Role(RoleBase, table=True):
    id: uuid_pkg.UUID = Field(default_factory=uuid4, primary_key=True, index=True, nullable=False)
    users: List["User"] = Relationship(back_populates="role", sa_relationship_kwargs={"lazy": "selectin"})
    created_by_id: Optional[UUID] = Field(default=None, foreign_key="User.id")
    created_by: "User" = Relationship(sa_relationship_kwargs={"lazy":"selectin", "primaryjoin":"Role.created_by_id==User.id"})    

class UserBase(SQLModel):
    first_name: str
    last_name: str
    email: EmailStr
         
class User(UserBase, table=True): 
    id: uuid_pkg.UUID = Field(default_factory=uuid4, primary_key=True, index=True, nullable=False)
    role_id: Optional[UUID] = Field(default=None, foreign_key="Role.id")
    role: Optional["Role"] = Relationship(back_populates="users", sa_relationship_kwargs={"lazy": "selectin"})

users =  await db_session.execute(select(User).where(User.email == email))