Tortoise ORM fails to generate tables for non-default schemas

[Pritish053]

Describe the bug Tortoise ORM has issues generating schemas when a specific schema other than the default public schema is specified. When attempting to use a schema other than public, the ORM does not create the tables automatically.

To Reproduce

from fastapi import FastAPI
from pydantic import BaseModel
from tortoise import Tortoise, fields, models
from tortoise.contrib.fastapi import register_tortoise

app = FastAPI()

class NameCreate(BaseModel):
    name: str

class Name(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=255)
    timestamp = fields.DatetimeField(auto_now_add=True)

    class Meta:
        table = "names"
        schema = "pgdev"

@app.on_event("startup")
async def init_db():
    await Tortoise.init(
        db_url="postgres://postgres:pgpass@localhost:5455/pgdatabase",
        modules={"models": ["main"]}
    )
    await Tortoise.generate_schemas()

@app.post("/names/")
async def create_name(name: NameCreate):
    name_obj = await Name.create(name=name.name)
    return {"message": f"Hello, {name_obj.name}"}

register_tortoise(
    app,
    db_url="postgres://postgres:pgpass@localhost:5455/pgdatabase",
    modules={"models": ["main"]},
    generate_schemas=True,
    add_exception_handlers=True,
)

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

Expected behavior The names table should be created within the pgdev schema, and the record should be inserted successfully.

Actual behaviour tortoise.exceptions.OperationalError: relation "pgdev.names" does not exist

Environment

•   Tortoise-ORM Version: 0.21.3
•   Tortoise Version: 0.1.1
•   Python Version: 3.10
•   Database: PostgreSQL
•   asyncpg Version: 0.29.0
•   OS: macOS 14.5 (23F79), Apple Silicon

[waketzheng]

For newer version of fastapi that >= 0.100, should use lifespan instead: https://github.com/tortoise/tortoise-orm/blob/develop/examples/fastapi/main.py

Otherwise, init_db and register_tortoise do the same action, remove one of them.

[Pritish053]

Hey @waketzheng Thanks for the input but still it doesn't create the table inside the non-public schema. tortoise.exceptions.OperationalError: relation "pgdev.names" does not exist

[abondar]

Hi!

It is indeed doesn't work, mostly because no one developed it to do so

schema param was added just to solve compatibility issue (mostly with oracle iirc), and it didn't go further than that

As workaround - if you are working with postgres - you can modify search_path for your pg user, but it is obviously limited solution if you want to work with several overlapping schemas simultaneously

Or you can look into contributing to tortoise to improve schema handling

[yogsagot]

Run into the same issue, even debugged into BaseSchemaGenerator code to discover _meta.schema is being ignored whatsoever.

Willing to contribute to code, will contact you if I make any progress.

[yogsagot]

Tried to create a pull request today but I need permissions to do so.

[abondar]

Usually you do it by pushing changes to your fork and then creating PR from your fork

Doesn't it work like that for you?

[yogsagot]

Created pull request. Even thou I sense there is additional work to do.