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.

[ajlive]

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))

[tiangolo]

Thanks for the help and discussion here everyone! 👏 🙇

Thanks for reporting back and closing the issue @taranlu-houzz 👍

[jpribyl]

I know this is closed already but just in case anyone else winds up here-- this worked without any type: ignore comments for me

    __tablename__: typing.ClassVar[
        typing.Union[str, typing.Callable[..., str]]
    ] = "your_table_name"

[saurabhyadavz]

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):

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

If someone is trying to change __tablename__ instead of creating a subclass from Product if the schema is same, then can use the following way to do it.

Product.__table__.name = "custom_table_name"

[AAraKKe]

I know setting the table name through __tablename__ does the trick, but it is very much not like using pydantic.

Is it possible to replicate the Config in Pydantic so that we can pass extra parameters related to the database? That way, we can create a Config inner class to which we can add any configuration we need that is not supported by Pydantic, like the table name. Seems like a weird mix to be able to treat the objects as pedantic objects but at the same time have these old-looking APIs from SQLalchemy.

If that is something you would be willing to consider, I could try to take a look and see how hard it would be to implement.

[pengwon]

I know setting the table name through __tablename__ does the trick, but it is very much not like using pydantic.

Is it possible to replicate the Config in Pydantic so that we can pass extra parameters related to the database? That way, we can create a Config inner class to which we can add any configuration we need that is not supported by Pydantic, like the table name. Seems like a weird mix to be able to treat the objects as pedantic objects but at the same time have these old-looking APIs from SQLalchemy.

If that is something you would be willing to consider, I could try to take a look and see how hard it would be to implement.

does it implement?

[niderhoff]

I know this is closed already but just in case anyone else winds up here-- this worked without any type: ignore comments for me

    __tablename__: typing.ClassVar[
        typing.Union[str, typing.Callable[..., str]]
    ] = "your_table_name"

sorry, for me your version does not work:

├╴  "__tablename__" overrides symbol of same name in class "SQLModel"
│      Variable is mutable so its type is invariant
│        Override type "str | ((...) -> str)" is not the same as base type "declared_attr[Unknown]" basedpyright >> (reportIncompatibleVariableOverride) [31, 5]

[yoavben]

in jpa I have two entities.
one has entity has two field: bess_id and data_type_name
the other entity named BessAttr has a promary key identifier
the data_type_name is an fk to identifier
how would you name a jpa repository to get a list of BessAttr by bess_id and what repository will you use the BessAttr repossitory or the Bess Repository