Setting PostgreSQL schema

[Chunkford]

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 datetime import datetime
from typing import Optional, List
from sqlmodel import Field, SQLModel


class Campaign(SQLModel, table=True):
    campaign_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        schema_extra={'schema': 'test'},
    )
    created_at: Optional[datetime] = Field(
        default=None,
        schema_extra={'schema': 'test'}
    )
    updated_at: Optional[datetime] = Field(
        default=None,
        schema_extra={'schema': 'test'}
    )
    name: str = Field(
        schema_extra={'schema': 'test'}
    )
    description: Optional[str]= Field(
        default=None,
        schema_extra={'schema': 'test'}
    )
    template_id: int = Field(
        schema_extra={'schema': 'test'}
    )
    orders_needed: int = Field(
        schema_extra={'schema': 'test'}
    )
    postcode_areas: List[str] = Field(
        schema_extra={'schema': 'test'}
    )

Description

I've created a model to import and I'm trying to connect to PostgreSQL to an already created database.
But I have my tables in a different schema called test and not public.
After looking through the code I was hoping the input schema_extra would do the trick

https://github.com/tiangolo/sqlmodel/blob/02da85c9ec39b0ebb7ff91de3045e0aea28dc998/sqlmodel/main.py#L158

But, I must be wrong.

This is the SQL being outputted:

SELECT campaign.campaign_id, campaign.created_at, campaign.updated_at, campaign.name, campaign.description, campaign.template_id, campaign.orders_needed, campaign.postcode_areas 
FROM campaign

Ideally I want it to say:

SELECT campaign.campaign_id, campaign.created_at, campaign.updated_at, campaign.title, campaign.description, campaign.template_id, campaign.orders_needed, campaign.postcode_areas 
FROM test.campaign AS campaign

Is there a way to map a model to a different schema other than the default public schema?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.3

Python Version

3.8.10

Additional Context

No response

[kbrown1224]

I took a stab at this and I think I found something that works. Not sure that its the optimal method though. I don't have a way to test if it works on linux right now, but this worked on MacOS Catalina, python 3.8.7. I will try to test out on linux, and python 3.8.10 tomorrow, and try to make it closer to your example.

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine
from sqlalchemy import MetaData

# Assuming 'my_schema' is an existing schema in your database
meta = MetaData(schema="my_schema")

# I used pg8000 here, but use whatever postgres driver you need
pg_url = "postgresql+pg8000://{}:{}@{}:{}/{}".format(
    "my_user", "my_pwd", "localhost", 5432, "my_db"
)

engine = create_engine(pg_url, echo=True)


class Hero(SQLModel, table=True):
    # Set the metadata in your class here
    metadata = meta
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


meta.create_all(engine)

[Chunkford]

I've been getting confused,
SQLModel I thought purely uses Pydantic sugar but I can use __table_args__ = {'schema': 'test'} in the model's class, which is an SQLAlchemy attribute, which makes things interesting.

This works for me:

class Campaign(SQLModel, table=True):
    __table_args__ = {'schema': 'test'}
    campaign_id: Optional[int] = Field(
        default=None,
        primary_key=True
    )
    created_at: Optional[datetime] = Field(
        default=None
    )
    ...

[sujanb]

Both the solutions mentioned by @Chunkford and @kbrown1224 work for me. I opted for this solution.

from sqlmodel import MetaData, SQLModel


class Base(SQLModel, table=False):
    metadata = MetaData(schema="karma")

The only difference from @kbrown1224 being that I'm importing from sqlmodel instead of sqlalchemy.

The problem I faced with @Chunkford's solution is that, my children models want to superclass Base and they will need to override __table_args__ in which case, the schema defined would be lost due to override.

[jordan-carson]

There seems to be an issue here. If I set a schema with MetaData, and with table_args, if the schema used does not exist no error is raised, nor does SQLModel create the database schema.

from datetime import datetime
from typing import Optional
from sqlmodel import Field, SQLModel, MetaData

meta = MetaData(schema="new_schema")


class CollectionsModel(SQLModel, table=True,):
    __table_args__ = {"schema": "other_schema"}
    id: int = Field(default=None, primary_key=True, nullable=False)
    name: str

Do we have a way to create database schema before creating tables in SQLModel? I would suspect a "create_all" method to create everything, including schema.

[Zaffer]

schema_extra on Field() is for Pydantic to add extra information to the JSON string or dict emitted, for example, by FastAPI /docs.

[lucaslima18]

I have one similar Issue here. i use the MetaData to refference my schema, but i did have succes only in one of my models (in other with the same code, just change the table name and schema) its works. this is the code:

`from typing import Optional

from sqlalchemy import MetaData
from sqlmodel import Field, SQLModel

class TestModel(SQLModel, table=True):

__tablename__ = "test",
metadata = MetaData(schema="api")

id: Optional[int] = Field(default=None, primary_key=True)
index: int = Field(default=None)
users: str = Field(default=None)
trabalhos: str = Field(default=None)
cpf: str = Field(default=None)`

with that model i drop this error:

File "/home/amorim/Documentos/work/caravel/.venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 99, in _get_table_key return schema + "." + name TypeError: can only concatenate str (not "tuple") to str

the table and schema exists on the database, i honest dont have idea what happend here!

[smiling-watermelon]

@lucaslima18, I assume you have figured this out in a span of a year+, but the issue is that you have:

__tablename__ = "test",

Note that this coma at the end makes it equivalent to:

__tablename__ = ("test", )

Or, indeed it is a tuple you are passing to __tablename__, and not a string.

[SamRWest]

I've been getting confused, SQLModel I thought purely uses Pydantic sugar but I can use __table_args__ = {'schema': 'test'} in the model's class, which is an SQLAlchemy attribute, which makes things interesting.

This works for me:

class Campaign(SQLModel, table=True):
    __table_args__ = {'schema': 'test'}
    campaign_id: Optional[int] = Field(
        default=None,
        primary_key=True
    )
    created_at: Optional[datetime] = Field(
        default=None
    )
    ...

This worked for me also using sqlmodel==0.0.22. Note that I only had to define table_args on my base SQLModel class though.
I also had to create the schema myself before creating the tables though, like this:

db_schema = 'your_schema_name'

class BaseModel(SQLModel, table=False):
    """Base class for all SQLModel subclasses"""

    # This sets the database schema in which to create tables for all subclasses of BaseModel
    __table_args__ = {"schema": db_schema}


class RealModel(SQLModel, table=True):
    # this gets created in `db_schema` automatically


def create_db_and_tables(self, db_schema: str) -> None:
    """Create the empty tables for each SQLModel classes defined.
    :param db_schema: the name of the database schema to use.  It will be created if it doesn't exist already.
    """
    # Create schema if it doesn't exist already
    with self.engine.connect() as conn:
        if not conn.dialect.has_schema(conn, db_schema):
            logger.warning(f"Schema '{db_schema}' not found in database. Creating...")
            conn.execute(sa.schema.CreateSchema(db_schema))
            conn.commit()
    
    # Then create the tables
    SQLModel.metadata.create_all(self.engine)