Table 'XXX' is already defined for this MetaData instance #1457

mybigman · 2022-05-24T06:21:41Z

mybigman
May 24, 2022

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

# using code from - https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/?h=hero#the-herocreate-data-model

from typing import List, Optional

from fastapi import FastAPI
from sqlmodel import Field, Session, SQLModel, create_engine, select


class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)


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


class HeroCreate(HeroBase):
    pass


class HeroRead(HeroBase):
    id: int


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


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


app = FastAPI()


@app.on_event("startup")
def on_startup():
    create_db_and_tables()


@app.post("/heroes/", response_model=HeroRead)
def create_hero(hero: HeroCreate):
    with Session(engine) as session:
        db_hero = Hero.from_orm(hero)
        session.add(db_hero)
        session.commit()
        session.refresh(db_hero)
        return db_hero


@app.get("/heroes/", response_model=List[HeroRead])
def read_heroes():
    with Session(engine) as session:
        heroes = session.exec(select(Hero)).all()
        return heroes

Description

First attempt fails with.

sqlalchemy.exc.InvalidRequestError: Table 'hero' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

Adding this fixes this problem, however reading various sites this is not recommended?

class Hero(HeroBase, table=True):
    __table_args__ = {"extend_existing": True} # < new
    id: Optional[int] = Field(default=None, primary_key=True)

Second attempt fails with.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "ix_hero_name" already exists

[SQL: CREATE INDEX ix_hero_name ON hero (name)]

Moving the create_db_and_tables() out of the functions startup event fixes this problem and everything works as expected

create_db_and_tables()
@app.on_event("startup")
def on_startup():
    print("startup")
    # create_db_and_tables()

Am I missing something that is causing this behavior?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10.4

Additional Context

No response

Answered by meirdev

Nov 27, 2022

The problem is because uvicorn is loading the file again, you can see this by adding print:

from sqlmodel import Field, SQLModel

print("here")

class Hero(SQLModel, table=True):

You can solve this by calling app directly:

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8001)

Or, by separating the code into two files:

main.py:

import uvicorn

if __name__ == "__main__":
    uvicorn.run("app:app", host="0.0.0.0", port=8001)

app.py:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

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

View full answer

ArVar · 2022-09-07T07:58:50Z

ArVar
Sep 7, 2022

Similar problem when trying to set a timing decorator. Must be something general in the context of wrapper functions or decorators respectively. This is apperently not limited to SQLModel, since I'm not using SQLModel but declarative_base from SQLAlchemy.

0 replies

daniil-berg · 2022-09-07T11:49:18Z

daniil-berg
Sep 7, 2022

I cannot reproduce this error. Works fine for me.

What version of SQLAlchemy is installed in the environment where you experienced this error?

Or has this issue been resolved?

0 replies

AlekseyFedorovich · 2022-11-07T15:16:38Z

AlekseyFedorovich
Nov 7, 2022

I had the same problem.
Surprisingly it was caused by missing the package 'psycopg2' (I'm using a Postgresql db).

0 replies

l00p1n6 · 2022-11-27T12:45:41Z

l00p1n6
Nov 27, 2022

I have the same problem with even less code:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

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

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run("main:app", host="0.0.0.0", port=8001)

The same ERROR pops when I run the program with
python main.py

But everything looks OK if I run it with
uvicorn main:app

Here is pipenv graph, if it helps:

aiosqlite==0.17.0
  - typing-extensions [required: >=3.7.2, installed: 4.4.0]
fastapi==0.87.0
  - pydantic [required: >=1.6.2,<2.0.0,!=1.8.1,!=1.8,!=1.7.3,!=1.7.2,!=1.7.1,!=1.7, installed: 1.10.2]
    - typing-extensions [required: >=4.1.0, installed: 4.4.0]
  - starlette [required: ==0.21.0, installed: 0.21.0]
    - anyio [required: >=3.4.0,<5, installed: 3.6.2]
      - idna [required: >=2.8, installed: 3.4]
      - sniffio [required: >=1.1, installed: 1.3.0]
sqlmodel==0.0.8
  - pydantic [required: >=1.8.2,<2.0.0, installed: 1.10.2]
    - typing-extensions [required: >=4.1.0, installed: 4.4.0]
  - SQLAlchemy [required: >=1.4.17,<=1.4.41, installed: 1.4.41]
    - greenlet [required: !=0.4.17, installed: 2.0.1]
  - sqlalchemy2-stubs [required: Any, installed: 0.0.2a29]
    - typing-extensions [required: >=3.7.4, installed: 4.4.0]
uvicorn==0.20.0
  - click [required: >=7.0, installed: 8.1.3]
    - colorama [required: Any, installed: 0.4.6]
  - h11 [required: >=0.8, installed: 0.14.0]

0 replies

meirdev · 2022-11-27T13:07:09Z

meirdev
Nov 27, 2022

The problem is because uvicorn is loading the file again, you can see this by adding print:

from sqlmodel import Field, SQLModel

print("here")

class Hero(SQLModel, table=True):

You can solve this by calling app directly:

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8001)

Or, by separating the code into two files:

main.py:

import uvicorn

if __name__ == "__main__":
    uvicorn.run("app:app", host="0.0.0.0", port=8001)

app.py:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

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

app = FastAPI()

0 replies

talhaanwarch · 2023-01-30T07:02:23Z

talhaanwarch
Jan 30, 2023

meirdev thanks issue resolved

0 replies

wilson-romero · 2023-04-29T22:05:29Z

wilson-romero
Apr 29, 2023

Thanks

0 replies

mielvds · 2023-09-25T14:01:51Z

mielvds
Sep 25, 2023

I was also getting this error when I imported the *.py file with model definitions in my unittests, which is located under models/crud/. I was not even importing the class that has table=True. Running the app with uvicorn actually worked fine.

In the end I figured out that I was importing from app.models.crud instead of models.crud and that fixed the issue

0 replies

raysry · 2024-01-20T16:56:22Z

raysry
Jan 20, 2024

Similar problem with streamlit
when file changed and streamlit app hotreload, error "Table 'XXX' is already defined for this MetaData instance" raised
anyone have idea?

0 replies

thaithamtawan · 2024-02-26T06:47:22Z

thaithamtawan
Feb 26, 2024

this work for me. try adding this.
doc

from sqlmodel import SQLModel

SQLModel.__table_args__ = {'extend_existing': True}

0 replies

Yangeok · 2024-05-11T09:25:11Z

Yangeok
May 11, 2024

When the import path is incorrect, I encountered the following error:

# occurred error
from foo.bar import Something

# worked out well
from src.foo.bar import Something

0 replies

alexjolig · 2024-05-15T10:30:21Z

alexjolig
May 15, 2024

I'm having the problem even without FastAPI:

main.py

from typing import Optional
import os

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, TIMESTAMP, select

from job import Job


class Parent(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    deleted_at: str | None = Field(sa_type=TIMESTAMP, default=None)
    parent_id: int | None = Field(
        foreign_key='parent.id',  # notice the lowercase "n" to refer to the database table name
        default=None,
        nullable=True
    )
    parent: Optional["Parent"] = Relationship(
        back_populates='children',
        sa_relationship_kwargs=dict(
            remote_side='Parent.id'  # notice the uppercase "N" to refer to this table class
        )
    )
    children: list["Parent"] = Relationship(back_populates='parent')
    jobs: list["Job"] = Relationship(back_populates="parent")

    @classmethod
    def get_name(cls, parent):
        return parent.name

sqlite_file_name = "database.db"
try:
    os.remove(sqlite_file_name)
except FileNotFoundError:
    pass
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url)

SQLModel.metadata.create_all(engine)

parent1 = Parent(id=1, name="John")
parent2 = Parent(id=2, name="David", parent_id=1)
job1 = Job(name="Police", parent_id=1)
job2 = Job(name="Nurse", parent_id=1)

with Session(engine) as s:
    s.add(parent1)
    s.add(parent2)
    s.add(job1)
    s.add(job2)
    s.commit()
    s.refresh(parent1)
    s.refresh(parent2)
    s.refresh(job1)

    print(f"parent1: {parent1}, Job is: {','.join([job.name for job in parent1.jobs])}")
    print(f"parent2: {parent2}, Job is: {parent2.jobs[0].name if parent2.jobs else None}")
    print(f"grandparent: {parent2.parent.name} is parent of {parent1.children[0].name}")
    print(job1.get_parent_name())

job.py

from sqlmodel import Field, Relationship, SQLModel

class Job(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    parent_id: str = Field(foreign_key="parent.id")
    parent: "Parent" = Relationship(back_populates="jobs")

    def get_parent_name(self):
        from main import Parent
        p = Parent(name = self.parent.name)
        return p.name

By running python main.py I get the same error

Python version: 3.10
OS: MacOS

0 replies

ben05allen · 2024-05-15T11:36:25Z

ben05allen
May 15, 2024

Works with this edit to the get_parent_name method:

class Job(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    parent_id: str = Field(foreign_key="parent.id")
    parent: "Parent" = Relationship(back_populates="jobs")

    def get_parent_name(self):
        p = self.parent
        return p.name

0 replies

ReRubis · 2024-05-18T19:53:37Z

ReRubis
May 18, 2024

If you encounter such error, it might be because the file with models gets executed twice.

Add a debug print on top of the file to check it.

print("----")

As mentioned above the cause of such behavior might be uvicorn.

In my case it was relative imports.
Will suggest to use only absolute ones.

0 replies

h4gen · 2024-06-13T16:07:24Z

h4gen
Jun 13, 2024

this work for me. try adding this. doc
from sqlmodel import SQLModel

SQLModel.__table_args__ = {'extend_existing': True}

Thanks, that worked for me!

0 replies

juanfrilla · 2024-11-17T20:17:45Z

juanfrilla
Nov 17, 2024

The problem is because uvicorn is loading the file again, you can see this by adding print:

from sqlmodel import Field, SQLModel

print("here")

class Hero(SQLModel, table=True):

You can solve this by calling app directly:

app = FastAPI()

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8001)

Or, by separating the code into two files:

main.py:

import uvicorn

if __name__ == "__main__":
    uvicorn.run("app:app", host="0.0.0.0", port=8001)

app.py:

from typing import Optional

import uvicorn as uvicorn
from fastapi import FastAPI
from sqlmodel import Field, SQLModel

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

app = FastAPI()

this inspired me to solve my problem, I was importing the models bad, I also added the traceback to see the imported bad models like this:

import sys
import traceback

print(f"app.models loaded as: {__name__}, id: {id(sys.modules[__name__])}")
traceback.print_stack()
```

0 replies

adith-p · 2025-10-09T16:01:24Z

adith-p
Oct 9, 2025

Ya so faced the issue today the issue was with how i was starting the fastapi server, for anyone else specify the file where app lives, i took it for granted when fastapi dev ran the server irrespective of specifying the main.py, tiny things matter

0 replies

Uh oh!

Table 'XXX' is already defined for this MetaData instance #1457

Uh oh!

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Replies: 17 comments

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!