0

I want to create a table with schema "fooschema" and name "footable". Based on this GitHub issue and to some extent the docs, I tried

fooMetadata = MetaData(schema="fooschema")

class Foo(SQLModel, table=True):
    __tablename__ = "footable"
    metadata = fooMetadata

    id_: int = Field(primary_key=True)

engine = create_engine("<url>")

Foo.metadata.create_all(engine)
with Session(engine) as session:
    row = Foo(id_=0)
    session.add(row)
    session.commit()
    session.refresh(row)

and tried replacing metadata = fooMetadata with __table_args__ = {"schema": "fooSchema"}, and replacing Foo.metadata.create_all with SQLModel.metadata.create_all but I'm always getting an error like

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "fooschema.footable" does not exist

or

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema "fooschema" does not exist

Oddly __table_args__ works for reading an existing table, just not creating it.

joel
  • 6,359
  • 2
  • 30
  • 55
  • Well, does that schema exist already? I doubt SQLAlchemy will just create one for you _implicitly_. – Daniil Fajnberg Jan 29 '23 at 00:03
  • @DaniilFajnberg no, but with the name `create_all`, I thought it might, well, create all, especially since the metadata explicitly includes the schema. – joel Jan 29 '23 at 10:02

1 Answers1

0

I solved this by explicitly creating the schema with

    with engine.connect() as connection:
        connection.execute(CreateSchema("fooschema"))
        connection.commit()

Note this is sort of answered in this answer but it's not particularly clear and this question is about SQLModel specifically.

joel
  • 6,359
  • 2
  • 30
  • 55
  • Remember that SQLModel is essentially still just a thin wrapper around SQLAlchemy (and Pydantic), which means that fundamental DB operations are almost always the same as with SQLAlchemy. Unless there is a built-in mechanism in SQLAlchemy to implicitly create missing schemas, I highly doubt that SQLModel will support that anytime soon. – Daniil Fajnberg Jan 29 '23 at 11:10