SQLModel is a library for interacting with SQL databases from Python code, with Python objects.

Overview

SQLModel

SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness.

Test Publish Coverage Package version


Documentation: https://sqlmodel.tiangolo.com

Source Code: https://github.com/tiangolo/sqlmodel


SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.

SQLModel is based on Python type annotations, and powered by Pydantic and SQLAlchemy.

The key features are:

  • Intuitive to write: Great editor support. Completion everywhere. Less time debugging. Designed to be easy to use and learn. Less time reading docs.
  • Easy to use: It has sensible defaults and does a lot of work underneath to simplify the code you write.
  • Compatible: It is designed to be compatible with FastAPI, Pydantic, and SQLAlchemy.
  • Extensible: You have all the power of SQLAlchemy and Pydantic underneath.
  • Short: Minimize code duplication. A single type annotation does a lot of work. No need to duplicate models in SQLAlchemy and Pydantic.

SQL Databases in FastAPI

SQLModel is designed to simplify interacting with SQL databases in FastAPI applications, it was created by the same author. 😁

It combines SQLAlchemy and Pydantic and tries to simplify the code you write as much as possible, allowing you to reduce the code duplication to a minimum, but while getting the best developer experience possible.

SQLModel is, in fact, a thin layer on top of Pydantic and SQLAlchemy, carefully designed to be compatible with both.

Requirements

A recent and currently supported version of Python (right now, Python supports versions 3.6 and above).

As SQLModel is based on Pydantic and SQLAlchemy, it requires them. They will be automatically installed when you install SQLModel.

Installation

$ pip install sqlmodel
---> 100%
Successfully installed sqlmodel

Example

For an introduction to databases, SQL, and everything else, see the SQLModel documentation.

Here's a quick example.

A SQL Table

Imagine you have a SQL table called hero with:

  • id
  • name
  • secret_name
  • age

And you want it to have this data:

id name secret_name age
1 Deadpond Dive Wilson null
2 Spider-Boy Pedro Parqueador null
3 Rusty-Man Tommy Sharp 48

Create a SQLModel Model

Then you could create a SQLModel model like this:

from typing import Optional

from sqlmodel import Field, SQLModel


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

That class Hero is a SQLModel model, the equivalent of a SQL table in Python code.

And each of those class attributes is equivalent to each table column.

Create Rows

Then you could create each row of the table as an instance of the model:

hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

This way, you can use conventional Python code with classes and instances that represent tables and rows, and that way communicate with the SQL database.

Editor Support

Everything is designed for you to get the best developer experience possible, with the best editor support.

Including autocompletion:

And inline errors:

Write to the Database

You can learn a lot more about SQLModel by quickly following the tutorial, but if you need a taste right now of how to put all that together and save to the database, you can do this:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine


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


hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)


engine = create_engine("sqlite:///database.db")


SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)
    session.commit()

That will save a SQLite database with the 3 heroes.

Select from the Database

Then you could write queries to select from that same database, for example with:

from typing import Optional

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


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


engine = create_engine("sqlite:///database.db")

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Spider-Boy")
    hero = session.exec(statement).first()
    print(hero)

Editor Support Everywhere

SQLModel was carefully designed to give you the best developer experience and editor support, even after selecting data from the database:

SQLAlchemy and Pydantic

That class Hero is a SQLModel model.

But at the same time, it is a SQLAlchemy model . So, you can combine it and use it with other SQLAlchemy models, or you could easily migrate applications with SQLAlchemy to SQLModel.

And at the same time, it is also a Pydantic model . You can use inheritance with it to define all your data models while avoiding code duplication. That makes it very easy to use with FastAPI.

License

This project is licensed under the terms of the MIT license.

Comments
  • SQLAlchemy version 1.4.36 breaks SQLModel relationships

    SQLAlchemy version 1.4.36 breaks SQLModel relationships

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    👆 Not quite true - this is definitely related to SQLAlchemy!

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from typing import Optional
    
    from sqlmodel import Field, Relationship, SQLModel
    
    
    class City(SQLModel, table=True):
        name: str = Field(primary_key=True)
        heroes: "Hero" = Relationship(back_populates="city")
    
    class Hero(SQLModel, table=True):
        name: str = Field(primary_key=True)
        city_name: Optional[str] = Field(default=None,foreign_key="city.name")
        city: Optional[City] = Relationship(back_populates="heroes",
                                  sa_relationship_kwargs=dict(cascade="all,delete")
                                  )
    
    
    if __name__ == "__main__":
    
        gotham = City(name="Gotham")
        batman = Hero(name="Batman", city=gotham)
    
        assert batman.name == 'Batman' # This is fine
        assert batman.city == gotham # This now breaks
    

    Description

    Our CI suddenly started failing, despite local SQLModel working fine. The issues turns out to be the transitive dependency on SQLAlchemy, which is weakly pinned: Github Actions pulled the latest version (1.4.36) and most of our tests started failing.

    https://github.com/sqlalchemy/sqlalchemy/releases/tag/rel_1_4_36

    The problem seems to be related to how relationships are defined, but I haven't yet dug into the SQLAlchemy changes enough to understand why that is.

    I'm opening this issue chiefly to help anybody else who is confused by why suddenly their tests are failing. I'm happy to help fix it if it's affecting others too.

    For the time being we have just pinned SQLAlchemy==1.4.34 in our requirements.txt.

    Operating System

    Linux, macOS

    Operating System Details

    Replicated locally and on Github Actions, both running in Docker

    SQLModel Version

    0.0.6

    Python Version

    3.9.10

    Additional Context

    We were previously running SQLAlchemy 1.4.34 locally and that works fine. Pinning to 1.4.36 breaks SQLModel.

    bug answered 
    opened by archydeberker 21
  • SQLModel doesn't raise ValidationError

    SQLModel doesn't raise ValidationError

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from typing import Optional
    
    from pydantic import BaseModel
    from sqlmodel import Field, SQLModel
    
    class User1(SQLModel, table=True):
        id: Optional[int] = Field(primary_key=True)
        name: str
    
    class User2(BaseModel):
        name: str
    
    User1()
    User2()
    

    Description

    • Create SQLModel
    • Create equivalent pydantic model
    • Create instance of SQLModel without providing required arguments
    • Create instance of pydantic model without providing required arguments

    The pydantic model raises an ValidationError whereas the SQLModel doesn't raise a ValidationError even though an required argument is not provided. I would expect a ValidationError if not all required arguments is provided.

    Operating System

    macOS

    Operating System Details

    No response

    SQLModel Version

    0.0.4

    Python Version

    Python 3.8.5

    Additional Context

    No response

    question answered 
    opened by elben10 18
  • Optional[Dict[str, str]] field handle error, (sqlite3.InterfaceError) Error binding parameter 4 - probably unsupported type.

    Optional[Dict[str, str]] field handle error, (sqlite3.InterfaceError) Error binding parameter 4 - probably unsupported type.

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    class TaskCreate(SQLModel):
        name: str
        report_id: UUID
        target: Optional[HttpUrl]
        version: str
        md5: Optional[Dict[str, str]]
        onsite: Optional[bool] = Field(default=False)
        envs: Optional[Dict[str, str]]
    
        # foreign key
        client_id: Optional[int] = Field(default=None, foreign_key="client.id")
    
    
    @router.post("/", response_model=Task)
    def create_tasks(
            *,
            db: Session = Depends(get_session),
            task_in: TaskCreate
    ):
        """
        Create a task.
        """
        db_task = Task.from_orm(task_in)
        db.add(db_task)
        db.commit()
        db.refresh(db_task)
        return db_task
    

    Description

    {
      "name": "task1",
      "report_id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "target": "http://a.com",
      "version": "v1.0",
      "md5": {
        "app.zip": "1231231231231234"
      },
      "onsite": false,
      "envs": {
        "apache": "5.0"
      },
      "client_id": 0
    }
    

    In the docs, I'm trying to create task with the above data, but I find that it can't be created with json embeded, leading to the following error:

    INFO:     127.0.0.1:56340 - "POST /tasks/ HTTP/1.1" 500 Internal Server Error
    ERROR:    Exception in ASGI application
    Traceback (most recent call last):
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
        self.dialect.do_execute(
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 376, in run_asgi
        result = await app(self.scope, self.receive, self.send)
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
        return await self.app(scope, receive, send)
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
        await super().__call__(scope, receive, send)
    ......
     File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
        raise exception
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
        self.dialect.do_execute(
      File "/Users/m0nst3r/dev/py/tespla/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 4 - probably unsupported type.
    [SQL: INSERT INTO task (name, report_id, target, version, md5, onsite, envs, client_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
    [parameters: ('task1', '3fa85f6457174562b3fc2c963f66afa6', HttpUrl('http://a.com', scheme='http', host='a.com', tld='com', host_type='domain'), 'v1.0', {'app.zip': '1231231231231234'}, 0, {'apache': '5.0'}, 0)]
    (Background on this error at: https://sqlalche.me/e/14/rvf5)
    
    

    But if I quote the embeded json to the following:

    {
      "name": "task1",
      "report_id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "target": "http://a.com",
      "version": "v1.0",
      "md5": "{
        \"app.zip\": \"1231231231231234\"
      }",
      "onsite": false,
      "envs": "{
        \"apache\": \"5.0\"
      }",
      "client_id": 0
    }
    

    it's showing: INFO: 127.0.0.1:57231 - "POST /tasks/ HTTP/1.1" 422 Unprocessable Entity

    It only works when no embeded json inside, but I need the md5 and envs fields.

    Appreciate!

    Operating System

    macOS

    Operating System Details

    pip freeze
    anyio==3.4.0
    arrow==1.2.1
    asgiref==3.4.1
    binaryornot==0.4.4
    certifi==2021.10.8
    chardet==4.0.0
    charset-normalizer==2.0.10
    click==8.0.3
    cookiecutter==1.7.3
    dnspython==2.2.0
    email-validator==1.1.3
    fastapi==0.70.1
    h11==0.12.0
    httptools==0.3.0
    idna==3.3
    Jinja2==3.0.3
    jinja2-time==0.2.0
    jose==1.0.0
    MarkupSafe==2.0.1
    poyo==0.5.0
    pydantic==1.8.2
    python-dateutil==2.8.2
    python-dotenv==0.19.2
    python-slugify==5.0.2
    PyYAML==6.0
    requests==2.27.1
    six==1.16.0
    sniffio==1.2.0
    SQLAlchemy==1.4.29
    sqlalchemy2-stubs==0.0.2a19
    sqlmodel==0.0.6
    starlette==0.16.0
    text-unidecode==1.3
    typing_extensions==4.0.1
    urllib3==1.26.8
    uvicorn==0.16.0
    uvloop==0.16.0
    watchgod==0.7
    websockets==10.1
    

    SQLModel Version

    0.0.6

    Python Version

    Python 3.9.9

    Additional Context

    No response

    question 
    opened by mr-m0nst3r 15
  • FastAPI and Pydantic - Relationships Not Working

    FastAPI and Pydantic - Relationships Not Working

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from typing import List, Optional
    
    from fastapi import Depends, FastAPI, HTTPException, Query
    from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select
    
    
    class TeamBase(SQLModel):
        name: str
        headquarters: str
    
    
    class Team(TeamBase, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
    
        heroes: List["Hero"] = Relationship(back_populates="team")
    
    
    class TeamCreate(TeamBase):
        pass
    
    
    class TeamRead(TeamBase):
        id: int
    
    
    class TeamUpdate(SQLModel):
        id: Optional[int] = None
        name: Optional[str] = None
        headquarters: Optional[str] = None
    
    
    class HeroBase(SQLModel):
        name: str
        secret_name: str
        age: Optional[int] = None
    
        team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    
    
    class Hero(HeroBase, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
    
        team: Optional[Team] = Relationship(back_populates="heroes")
    
    
    class HeroRead(HeroBase):
        id: int
    
    
    class HeroCreate(HeroBase):
        pass
    
    
    class HeroUpdate(SQLModel):
        name: Optional[str] = None
        secret_name: Optional[str] = None
        age: Optional[int] = None
        team_id: Optional[int] = None
    
    
    class HeroReadWithTeam(HeroRead):
        team: Optional[TeamRead] = None
    
    
    class TeamReadWithHeroes(TeamRead):
        heroes: List[HeroRead] = []
    
    
    sqlite_file_name = "database.db"
    sqlite_url = f"sqlite:///{sqlite_file_name}"
    
    connect_args = {"check_same_thread": False}
    engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)
    
    
    def create_db_and_tables():
        SQLModel.metadata.create_all(engine)
    
    
    def get_session():
        with Session(engine) as session:
            yield session
    
    
    app = FastAPI()
    
    
    @app.on_event("startup")
    def on_startup():
        create_db_and_tables()
    
    
    @app.post("/heroes/", response_model=HeroRead)
    def create_hero(*, session: Session = Depends(get_session), hero: HeroCreate):
        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(
        *,
        session: Session = Depends(get_session),
        offset: int = 0,
        limit: int = Query(default=100, lte=100),
    ):
        heroes = session.exec(select(Hero).offset(offset).limit(limit)).all()
        return heroes
    
    
    @app.get("/heroes/{hero_id}", response_model=HeroReadWithTeam)
    def read_hero(*, session: Session = Depends(get_session), hero_id: int):
        hero = session.get(Hero, hero_id)
        if not hero:
            raise HTTPException(status_code=404, detail="Hero not found")
        return hero
    
    
    @app.patch("/heroes/{hero_id}", response_model=HeroRead)
    def update_hero(
        *, session: Session = Depends(get_session), hero_id: int, hero: HeroUpdate
    ):
        db_hero = session.get(Hero, hero_id)
        if not db_hero:
            raise HTTPException(status_code=404, detail="Hero not found")
        hero_data = hero.dict(exclude_unset=True)
        for key, value in hero_data.items():
            setattr(db_hero, key, value)
        session.add(db_hero)
        session.commit()
        session.refresh(db_hero)
        return db_hero
    
    
    @app.delete("/heroes/{hero_id}")
    def delete_hero(*, session: Session = Depends(get_session), hero_id: int):
    
        hero = session.get(Hero, hero_id)
        if not hero:
            raise HTTPException(status_code=404, detail="Hero not found")
        session.delete(hero)
        session.commit()
        return {"ok": True}
    
    
    @app.post("/teams/", response_model=TeamRead)
    def create_team(*, session: Session = Depends(get_session), team: TeamCreate):
        db_team = Team.from_orm(team)
        session.add(db_team)
        session.commit()
        session.refresh(db_team)
        return db_team
    
    
    @app.get("/teams/", response_model=List[TeamRead])
    def read_teams(
        *,
        session: Session = Depends(get_session),
        offset: int = 0,
        limit: int = Query(default=100, lte=100),
    ):
        teams = session.exec(select(Team).offset(offset).limit(limit)).all()
        return teams
    
    
    @app.get("/teams/{team_id}", response_model=TeamReadWithHeroes)
    def read_team(*, team_id: int, session: Session = Depends(get_session)):
        team = session.get(Team, team_id)
        if not team:
            raise HTTPException(status_code=404, detail="Team not found")
        return team
    
    
    @app.patch("/teams/{team_id}", response_model=TeamRead)
    def update_team(
        *,
        session: Session = Depends(get_session),
        team_id: int,
        team: TeamUpdate,
    ):
        db_team = session.get(Team, team_id)
        if not db_team:
            raise HTTPException(status_code=404, detail="Team not found")
        team_data = team.dict(exclude_unset=True)
        for key, value in team_data.items():
            setattr(db_team, key, value)
        session.add(db_team)
        session.commit()
        session.refresh(db_team)
        return db_team
    
    
    @app.delete("/teams/{team_id}")
    def delete_team(*, session: Session = Depends(get_session), team_id: int):
        team = session.get(Team, team_id)
        if not team:
            raise HTTPException(status_code=404, detail="Team not found")
        session.delete(team)
        session.commit()
        return {"ok": True}
    

    Description

    Is realationships working for anyone? I either get null or an empty list.

    OK, so, I've copied the last full file preview at the - https://sqlmodel.tiangolo.com/tutorial/fastapi/relationships/ Run it and it creates the Db and the foreign key Then I've insert the data into the Db.

    Checking the docs UI everything looks great Screenshot 2021-08-26 at 23 33 55

    But when I do a request for a hero, team is null Screenshot 2021-08-26 at 23 36 39

    Really not sure what going on, especially when all I have just is copied the code example with no changes?

    Operating System

    Linux

    Operating System Details

    No response

    SQLModel Version

    0.0.4

    Python Version

    3.8.2

    Additional Context

    No response

    question 
    opened by Chunkford 15
  • How to make enum column to work with SQLModel?

    How to make enum column to work with SQLModel?

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from sqlmodel import SQLModel, Field, JSON, Enum, Column
    from typing import Optional
    from pydantic import BaseModel
    from datetime import datetime
    
    class TrainingStatus(str, enum.Enum):
        scheduled_for_training = "scheduled_for_training"
        training = "training"
        trained = "trained"
    
    class model_profile(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        training_status: Column(Enum(TrainingStatus))
        model_version: str
    

    Description

    I am trying to create an enum column and use the value in the database. But I am getting this error:

    RuntimeError: error checking inheritance of Column(None, Enum('scheduled_for_training', 'training', 'trained', name='trainingstatus'), table=None) (type: Column)
    

    Does anyone knows how to help me? I tried

    training_status: Column('value', Enum(TrainingStatus))
    

    but it doesn't seem to work as I don't understand where the 'value' should be coming from 😓 I would really appreciate any input

    Operating System

    macOS

    Operating System Details

    No response

    SQLModel Version

    0.0.4

    Python Version

    3.7.9

    Additional Context

    No response

    question answered 
    opened by haja-k 14
  • Fix nullable field issue (#420)

    Fix nullable field issue (#420)

    UPDATE

    changes added to https://github.com/tiangolo/sqlmodel/pull/423

    Motivation

    Columns that are defined as Optional but are set = Field(nullable=False) are made nullable. This is a bug that was introduced in https://github.com/tiangolo/sqlmodel/commit/9830ee0d8991ffc068ffb72ccead2427c84e58ee#r82434170 and described in #420.

    Actions Taken

    • Added a test to confirm the regression
    • Fixed test by re-ordering the code that determines if a field is nullable.

    Co-authored-by: Jonas Krüger Svensson [email protected], building off his PR: https://github.com/tiangolo/sqlmodel/pull/423

    opened by br-allstreet 13
  • JSON and Array types for sqlmodel

    JSON and Array types for sqlmodel

    First Check

    • [x] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from typing import Optional
    
    from sqlmodel import Field, SQLModel, JSON, ARRAY
    
    
    class Hero(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        name: str
        secret_name: JSON
        age: Optional[int] = None
    

    Description

    Got following error: new_cls = super().__new__(cls, name, bases, dict_used, **config_kwargs) File "pydantic/main.py", line 299, in pydantic.main.ModelMetaclass.__new__ File "pydantic/fields.py", line 411, in pydantic.fields.ModelField.infer File "pydantic/fields.py", line 342, in pydantic.fields.ModelField.__init__ File "pydantic/fields.py", line 456, in pydantic.fields.ModelField.prepare File "pydantic/fields.py", line 670, in pydantic.fields.ModelField.populate_validators File "pydantic/validators.py", line 715, in find_validators RuntimeError: no validator found for <class 'sqlalchemy.sql.sqltypes.JSON'>, seearbitrary_types_allowedin Config

    Operating System

    macOS

    Operating System Details

    No response

    SQLModel Version

    0.0.4

    Python Version

    3.8.0

    Additional Context

    No response

    question 
    opened by mv-yurchenko 13
  • `Field(nullable=True)` is ignored due to recent changes released in version 0.0.7

    `Field(nullable=True)` is ignored due to recent changes released in version 0.0.7

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from typing import Optional
    
    from sqlmodel import Field, Session, SQLModel, create_engine
    
    class Hero(SQLModel, table=True):
      id: Optional[int] = Field(default=None, primary_key=True)
      # This should not be nullable
      name: Optional[str] = Field(default=None, nullable=False)
    
    hero = Hero()
    engine = create_engine("sqlite:///database.db")
    
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
      session.add(hero)
      session.commit()
      session.refresh(hero)
      print(hero)
    

    Description

    • Create a hero model with a non-nullable column by assigning it to Field(nullable=True)
    • Create a hero instance without setting the column
    • Save the instance to the DB. Should get an error but the hero instance saves successfully.

    I believe this issue was introduced here: https://github.com/tiangolo/sqlmodel/commit/9830ee0d8991ffc068ffb72ccead2427c84e58ee#r82434170

    Operating System

    macOS

    Operating System Details

    No response

    SQLModel Version

    0.0.7

    Python Version

    Python 3.10.5

    Additional Context

    No response

    bug answered 
    opened by br-allstreet 11
  • How to create a time Field for a model like django DateTimeField

    How to create a time Field for a model like django DateTimeField

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from datetime import datetime
    from typing import Optional
    from sqlmodel import Field, SQLModel, DateTime
    
    
    class Image_Save_Record(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
    
        camera_ip: str
        camera_channel: int
        file_path: str
        file_name: str
        create_time: DateTime 
        update_time: DateTime
    

    Description

    when i create a Image_Save_Record instance and commit it to db, the create_time field value should be that system time current.

    # django version
    create_time = models.DateTimeField(auto_now_add=True)
    

    when i change the Image_Save_Record instance which i created before and commit it to db, the update_time field value should be that system time current.

    # django version
    update_time = models.DateTimeField(auto_now=True)
    

    Is there sqlmodel have option to do the same like this?

    Operating System

    Windows

    Operating System Details

    No response

    SQLModel Version

    0.0.6

    Python Version

    3.7.9

    Additional Context

    No response

    question 
    opened by regainOWO 11
  • Create Relationships with Unique Fields (UniqueViolationError)

    Create Relationships with Unique Fields (UniqueViolationError)

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    # From the SQLModel Tutorial (https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/create-and-update-relationships/)
    
    from typing import List, Optional
    from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
    
    class Team(SQLModel, table=True):
        # id: Optional[int] = Field(default=None, primary_key=True)
        id: int = Field(default=None, primary_key=True)  # NEW
        name: str = Field(index=True)
    
        heroes: List["Hero"] = Relationship(back_populates="team")
    
    class Hero(SQLModel, table=True):
        # id: Optional[int] = Field(default=None, primary_key=True)
        id: int = Field(default=None, primary_key=True)  # NEW
        name: str = Field(index=True)
    
        # team_id: Optional[int] = Field(default=None, foreign_key="team.id")
        # team: Optional[Team] = Relationship(back_populates="heroes")
        team_id: int = Field(default=None, foreign_key="team.id")  # NEW
        team: Team = Relationship(back_populates="heroes")  # NEW
    
    from sqlalchemy.ext.asyncio import AsyncSession # ADDED: 2022_02_24
    
    # def create_heroes():
    async def create_heroes(session: AsyncSession, request: Hero):  # NEW, EDITED: 2022_02_24
        # with Session(engine) as session: # EDITED
            # team_preventers = Team(name="Preventers", headquarters="Sharp Tower")  # REMOVE
            # team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")  # REMOVE
            assigned_team = Team(name=request.team_to_assign)
    
            new_hero = Hero(
                name=request.hero_name,
                team=assigned_team
            )
            session.add(new_hero)
            await session.commit() # EDITED: 2022_02_24
            await session.refresh(new_hero) # EDITED: 2022_02_24
            return new_hero # ADDED: 2022_02_24
    
    
    # Code below omitted 👇
    

    Description

    I'm following the SQLModel tutorial as I implement my own version. I have a model very similar to the above example (derived from the Hero/Team example given in the tutorial on how to implement One-To-Many relationships with SQLModel.

    When I use this approach, it does write the required Team and Hero objects to my database. However, it does not check the Team table to ensure that the "team_to_assign" from the request object does not already exist. So, if I use the "create_heroes" function (in two separate commits) to create two Heroes who are on the same team, I get two entries for the same team in the Team table. This is not desirable. If the team already exists, the Hero being created should use the id that already exists for that team.

    When I implement "sa_column_kwargs={"unique": True}" within the "name" Field of the Team table, I can no longer create a new Hero if they are to be connected to a Team that already exists. I get the error:

    sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "ix_team_name" DETAIL: Key (name)=(team_name) already exists. [SQL: INSERT INTO "team" (name) VALUES (%s) RETURNING "team".id]

    I was hoping that would somehow tell SQLModel to skip the insertion of a Team that already exists and get the appropriate Team id instead. Clearly it just stops it from happening. SQLModel doesn't appear to check that a Team already exists before inserting it into the Team table.

    Am I missing something about how to handle this with SQLModel, or am I meant to employ my own logic to check the Team table prior to generating the Hero object to insert?

    Thanks for your time!

    Operating System

    Linux

    Operating System Details

    No response

    SQLModel Version

    0.0.6

    Python Version

    3.10

    Additional Context

    Using async libraries: SQLAlchemy = {extras = ["asyncio"], version = "^1.4.31"} asyncpg = "^0.25.0"

    question 
    opened by njdowdy 11
  • How to specify a different name for the db table that a SQLModel refers to?

    How to specify a different name for the db table that a SQLModel refers to?

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] 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

    question 
    opened by taranlu-houzz 11
  • engine future side effect when False

    engine future side effect when False

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    engine = create_engine(settings.DATABASE_URI, echo=True, connect_args=connect_args,future=False)
    
    data_df = pd.read_sql(table_name, con=engine, parse_dates=['datetime'])
    

    Description

    I am using FASTAPI and SQLModel with Pandas. I create dataframes and convert them to json before returning them. I need to set the future = False otherwise pandas is unable to read sql. Will this have any side effects on the framework?

    Operating System

    Linux

    Operating System Details

    No response

    SQLModel Version

    0.0.8

    Python Version

    3.8.10

    Additional Context

    No response

    question 
    opened by priamai 0
  • ⬆ Bump actions/checkout from 3.1.0 to 3.3.0

    ⬆ Bump actions/checkout from 3.1.0 to 3.3.0

    Bumps actions/checkout from 3.1.0 to 3.3.0.

    Release notes

    Sourced from actions/checkout's releases.

    v3.3.0

    What's Changed

    New Contributors

    Full Changelog: https://github.com/actions/checkout/compare/v3.2.0...v3.3.0

    v3.2.0

    What's Changed

    New Contributors

    Full Changelog: https://github.com/actions/checkout/compare/v3.1.0...v3.2.0

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies github_actions 
    opened by dependabot[bot] 0
  • ⬆ Bump dawidd6/action-download-artifact from 2.24.2 to 2.24.3

    ⬆ Bump dawidd6/action-download-artifact from 2.24.2 to 2.24.3

    Bumps dawidd6/action-download-artifact from 2.24.2 to 2.24.3.

    Commits
    • bd10f38 Merge pull request #218 from dawidd6/dependabot-npm_and_yarn-adm-zip-0.5.10
    • 61a654a build(deps): bump adm-zip from 0.5.9 to 0.5.10
    • dcadc4b Merge pull request #211 from koplo199/master
    • ceeb280 Remove unnecessary semicolon
    • 806bb52 Catch 'Artifact has expired' error
    • See full diff in compare view

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies github_actions 
    opened by dependabot[bot] 0
  • What is the purpose of parse_obj's second argument:

    What is the purpose of parse_obj's second argument: "update"?

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    SomeModel.parse_obj({"one": 2}, {"three": 4})
    

    Description

    SQLModel's parse_obj supports the update argument which is not supported by pydantic's BaseModel. However, I do not see it ever used in docs, github issues, or in source code.

    Could someone explain why it was added? What was the actual use case? I understand what it does, but I don't understand why it does it.

    Operating System

    Linux, Windows, macOS, Other

    Operating System Details

    No response

    SQLModel Version

    0.0.8

    Python Version

    3.11

    Additional Context

    No response

    question 
    opened by Ovsyanka83 0
  • Why does sqlmodel define `class_registry` at the top level of its main.py?

    Why does sqlmodel define `class_registry` at the top level of its main.py?

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [x] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    from sqlmodel import class_registry
    

    Description

    Why does sqlmodel define class_registry at the top level of its main.py? It seems to be related to SQLAlchemy but it does not seem to be used anywhere.

    Operating System

    Linux, Windows, macOS, Other

    Operating System Details

    No response

    SQLModel Version

    0.0.8

    Python Version

    3.11

    Additional Context

    No response

    question 
    opened by Ovsyanka83 0
  • General practices to convert existing SQLAlchemy tables into SQLModel

    General practices to convert existing SQLAlchemy tables into SQLModel

    First Check

    • [X] I added a very descriptive title to this issue.
    • [X] I used the GitHub search to find a similar issue and didn't find it.
    • [X] I searched the SQLModel documentation, with the integrated search.
    • [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
    • [X] I already read and followed all the tutorial in the docs and didn't find an answer.
    • [X] I already checked if it is not related to SQLModel but to Pydantic.
    • [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

    Commit to Help

    • [X] I commit to help with one of those options 👆

    Example Code

    class MetricBase(SQLModel):
        id: Optional[int] = Field(default=None, primary_key=True)
        fact_name: str
        dimensions: Optional[List] = Field(default=[])
        measures: Optional[List] = Field(default=[])
        params: Optional[Dict]
    

    Description

    It would be nice to show a few examples about how to model arrays and json SQL columns. In general what principles should I follow to convert from a SQLAlchemy Table definition?

    Operating System

    Linux

    Operating System Details

    Ubuntu 21.0

    SQLModel Version

    0.0.8

    Python Version

    3.8.10

    Additional Context

    For example I am trying to convert this existing table:

    
    from sqlalchemy import Column, Integer, String, DateTime, BigInteger, SmallInteger,LargeBinary, ForeignKey, Table, Float,Boolean
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.dialects.postgresql import JSONB,JSON, ARRAY
    
    class Metrics(Base):
        __tablename__ = 'metrics'
        __table_args__ = {'extend_existing':True}
        
        # billing item id
        id = Column(BigInteger, autoincrement=True, primary_key=True)
    
        # the fact name
        fact_name = Column(String,nullable=False)
        dimensions = Column(ARRAY(String))
        measures = Column(ARRAY(String))
        sql_query = Column(String)
        rest_query = Column(String)
        params_query = Column(JSON)
        chart_types = Column(JSON)
        chart_title = Column(String)
    
    question 
    opened by priamai 6
Releases(0.0.8)
  • 0.0.8(Aug 30, 2022)

    Fixes

    • 🐛 Fix auto detecting and setting nullable, allowing overrides in field. PR #423 by @JonasKs.
    • ♻️ Update expresion.py, sync from Jinja2 template, implement inherit_cache to solve errors like: SAWarning: Class SelectOfScalar will not make use of SQL compilation caching. PR #422 by @tiangolo.

    Docs

    • 📝 Adjust and clarify docs for docs/tutorial/create-db-and-table.md. PR #426 by @tiangolo.
    • ✏ Fix typo in docs/tutorial/connect/remove-data-connections.md. PR #421 by @VerdantFox.
    Source code(tar.gz)
    Source code(zip)
  • 0.0.7(Aug 28, 2022)

    Features

    • ✨ Allow setting unique in Field() for a column. PR #83 by @raphaelgibson.
    • ✨ Update GUID handling to use stdlib UUID.hex instead of an int. PR #26 by @andrewbolster.
    • ✨ Raise an exception when using a Pydantic field type with no matching SQLAlchemy type. PR #18 by @elben10.
    • ⬆ Upgrade constrain for SQLAlchemy = ">=1.4.17,<=1.4.41". PR #371 by @RobertRosca.
    • ✨ Add new Session.get() parameter execution_options. PR #302 by @tiangolo.

    Fixes

    • 🐛 Fix type annotations for Model.parse_obj(), and Model.validate(). PR #321 by @phi-friday.
    • 🐛 Fix Select and SelectOfScalar to inherit cache to avoid warning: SAWarning: Class SelectOfScalar will not make use of SQL compilation caching. PR #234 by @rabinadk1.
    • 🐛 Fix handling validators for non-default values. PR #253 by @byrman.
    • 🐛 Fix fields marked as "set" in models. PR #117 by @statt8900.
    • 🐛 Fix Enum handling in SQLAlchemy. PR #165 by @chriswhite199.
    • 🐛 Fix setting nullable property of Fields that don't accept None. PR #79 by @van51.
    • 🐛 Fix SQLAlchemy version 1.4.36 breaks SQLModel relationships (#315). PR #322 by @byrman.

    Docs

    • 📝 Update docs for models for updating, id should not be updatable. PR #335 by @kurtportelli.
    • ✏ Fix broken variable/typo in docs for Read Relationships, hero_spider_boy.id => hero_spider_boy.team_id. PR #106 by @yoannmos.
    • 🎨 Remove unwanted highlight in the docs. PR #233 by @jalvaradosegura.
    • ✏ Fix typos in docs/databases.md and docs/tutorial/index.md. PR #35 by @prrao87.
    • ✏ Fix typo in docs/tutorial/relationship-attributes/define-relationships-attributes.md. PR #239 by @jalvaradosegura.
    • ✏ Fix typo in docs/tutorial/fastapi/simple-hero-api.md. PR #80 by @joemudryk.
    • ✏ Fix typos in multiple files in the docs. PR #400 by @VictorGambarini.
    • ✏ Fix typo in docs/tutorial/code-structure.md. PR #344 by @marciomazza.
    • ✏ Fix typo in docs/db-to-code.md. PR #155 by @gr8jam.
    • ✏ Fix typo in docs/contributing.md. PR #323 by @Fardad13.
    • ✏ Fix typo in docs/tutorial/fastapi/tests.md. PR #265 by @johnhoman.
    • ✏ Fix typo in docs/tutorial/where.md. PR #286 by @jalvaradosegura.
    • ✏ Fix typos in docs/tutorial/fastapi/update.md. PR #268 by @cirrusj.
    • ✏ Fix typo in docs/tutorial/fastapi/simple-hero-api.md. PR #247 by @hao-wang.
    • ✏ Fix typos in docs/tutorial/automatic-id-none-refresh.md, docs/tutorial/fastapi/update.md, docs/tutorial/select.md. PR #185 by @rootux.
    • ✏ Fix typo in docs/databases.md. PR #177 by @seandlg.
    • ✏ Fix typos in docs/tutorial/fastapi/update.md. PR #162 by @wmcgee3.
    • ✏ Fix typos in docs/tutorial/code-structure.md, docs/tutorial/fastapi/multiple-models.md, docs/tutorial/fastapi/simple-hero-api.md, docs/tutorial/many-to-many/index.md. PR #116 by @moonso.
    • ✏ Fix typo in docs/tutorial/fastapi/teams.md. PR #154 by @chrisgoddard.
    • ✏ Fix typo variable in example about relationships and back_populates, always use hero instead of owner. PR #120 by @onionj.
    • ✏ Fix typo in docs/tutorial/fastapi/tests.md. PR #113 by @feanil.
    • ✏ Fix typo in docs/tutorial/where.md. PR #72 by @ZettZet.
    • ✏ Fix typo in docs/tutorial/code-structure.md. PR #91 by @dhiraj.
    • ✏ Fix broken link to newsletter sign-up in docs/help.md. PR #84 by @mborus.
    • ✏ Fix typos in docs/tutorial/many-to-many/create-models-with-link.md. PR #45 by @xginn8.
    • ✏ Fix typo in docs/tutorial/index.md. PR #398 by @ryangrose.

    Internal

    • ♻ Refactor internal statements to simplify code. PR #53 by @yezz123.
    • ♻ Refactor internal imports to reduce redundancy. PR #272 by @aminalaee.
    • ⬆ Update development requirement for FastAPI from ^0.68.0 to ^0.68.1. PR #48 by @alucarddelta.
    • ⏪ Revert upgrade Poetry, to make a release that supports Python 3.6 first. PR #417 by @tiangolo.
    • 👷 Add dependabot for GitHub Actions. PR #410 by @tiangolo.
    • ⬆️ Upgrade Poetry to version ==1.2.0b1. PR #303 by @tiangolo.
    • 👷 Add CI for Python 3.10. PR #305 by @tiangolo.
    • 📝 Add Jina's QA Bot to the docs to help people that want to ask quick questions. PR #263 by @tiangolo.
    • 👷 Upgrade Codecov GitHub Action. PR #304 by @tiangolo.
    • 💚 Only run CI on push when on master, to avoid duplicate runs on PRs. PR #244 by @tiangolo.
    • 🔧 Upgrade MkDocs Material and update configs. PR #217 by @tiangolo.
    • ⬆ Upgrade mypy, fix type annotations. PR #218 by @tiangolo.
    Source code(tar.gz)
    Source code(zip)
  • 0.0.6(Dec 28, 2021)

    Breaking Changes

    SQLModel no longer creates indexes by default for every column, indexes are now opt-in. You can read more about it in PR #205.

    Before this change, if you had a model like this:

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

    ...when creating the tables, SQLModel version 0.0.5 and below, would also create an index for name, one for secret_name, and one for age (id is the primary key, so it doesn't need an additional index).

    If you depended on having an index for each one of those columns, now you can (and would have to) define them explicitly:

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

    There's a high chance you don't need indexes for all the columns. For example, you might only need indexes for name and age, but not for secret_name. In that case, you could define the model as:

    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)
    

    If you already created your database tables with SQLModel using versions 0.0.5 or below, it would have also created those indexes in the database. In that case, you might want to manually drop (remove) some of those indexes, if they are unnecessary, to avoid the extra cost in performance and space.

    Depending on the database you are using, there will be a different way to find the available indexes.

    For example, let's say you no longer need the index for secret_name. You could check the current indexes in the database and find the one for secret_name, it could be named ix_hero_secret_name. Then you can remove it with SQL:

    DROP INDEX ix_hero_secret_name
    

    or

    DROP INDEX ix_hero_secret_name ON hero;
    

    Here's the new, extensive documentation explaining indexes and how to use them: Indexes - Optimize Queries.

    Docs

    Source code(tar.gz)
    Source code(zip)
  • 0.0.5(Dec 13, 2021)

    Features

    Docs

    • ✏ Update decimal tutorial source for consistency. PR #188 by @tiangolo.

    Internal

    • 🔧 Split MkDocs insiders build in CI to support building from PRs. PR #186 by @tiangolo.
    • 🎨 Format expression.py and expression template, currently needed by CI. PR #187 by @tiangolo.
    • 🐛Fix docs light/dark theme switcher. PR #1 by @Lehoczky.
    • 🔧 Add MkDocs Material social cards. PR #90 by @tiangolo.
    • ✨ Update type annotations and upgrade mypy. PR #173 by @tiangolo.
    Source code(tar.gz)
    Source code(zip)
  • 0.0.4(Aug 25, 2021)

  • 0.0.3(Aug 24, 2021)

  • 0.0.2(Aug 24, 2021)

Owner
Sebastián Ramírez
Creator of FastAPI and Typer. Dev at @explosion. APIs, Deep Learning/ML, full-stack distributed systems, SQL/NoSQL, Python, Docker, JS, TypeScript, etc
Sebastián Ramírez
MongoEngine flask extension with WTF model forms support

Flask-MongoEngine Info: MongoEngine for Flask web applications. Repository: https://github.com/MongoEngine/flask-mongoengine About Flask-MongoEngine i

MongoEngine 815 Jan 03, 2023
Sqlalchemy seeder that supports nested relationships.

sqlalchemyseed Sqlalchemy seeder that supports nested relationships. Supported file types json yaml csv Installation Default installation pip install

Jedy Matt Tabasco 10 Aug 13, 2022
The ormar package is an async mini ORM for Python, with support for Postgres, MySQL, and SQLite.

python async mini orm with fastapi in mind and pydantic validation

1.2k Jan 05, 2023
A database migrations tool for TortoiseORM, ready to production.

Aerich Introduction Aerich is a database migrations tool for Tortoise-ORM, which is like alembic for SQLAlchemy, or like Django ORM with it's own migr

Tortoise 596 Jan 06, 2023
Pony Object Relational Mapper

Downloads Pony Object-Relational Mapper Pony is an advanced object-relational mapper. The most interesting feature of Pony is its ability to write que

3.1k Jan 01, 2023
ORM for Python for PostgreSQL.

New generation (or genius) ORM for Python for PostgreSQL. Fully-typed for any query with Pydantic and auto-model generation, compatible with any sync or async driver

Yan Kurbatov 3 Apr 13, 2022
A PostgreSQL or SQLite orm for Python

Prom An opinionated lightweight orm for PostgreSQL or SQLite. Prom has been used in both single threaded and multi-threaded environments, including en

Jay Marcyes 18 Dec 01, 2022
A Python Library for Simple Models and Containers Persisted in Redis

Redisco Python Containers and Simple Models for Redis Description Redisco allows you to store objects in Redis. It is inspired by the Ruby library Ohm

sebastien requiem 436 Nov 10, 2022
The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy The Python SQL Toolkit and Object Relational Mapper Introduction SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that giv

mike bayer 3.5k Dec 29, 2022
Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.

dataset: databases for lazy people In short, dataset makes reading and writing data in databases as simple as reading and writing JSON files. Read the

Friedrich Lindenberg 4.2k Dec 26, 2022
A simple project to explore the number of GCs when doing basic ORM work.

Question: Does Python do extremely too many GCs for ORMs? YES, OMG YES. Check this out Python Default GC Settings: SQLAlchemy - 20,000 records in one

Michael Kennedy 26 Jun 05, 2022
Rich Python data types for Redis

Created by Stephen McDonald Introduction HOT Redis is a wrapper library for the redis-py client. Rather than calling the Redis commands directly from

Stephen McDonald 281 Nov 10, 2022
An async ORM. 🗃

ORM The orm package is an async ORM for Python, with support for Postgres, MySQL, and SQLite. ORM is built with: SQLAlchemy core for query building. d

Encode 1.7k Dec 28, 2022
A curated list of awesome tools for SQLAlchemy

Awesome SQLAlchemy A curated list of awesome extra libraries and resources for SQLAlchemy. Inspired by awesome-python. (See also other awesome lists!)

Hong Minhee (洪 民憙) 2.5k Dec 31, 2022
Tortoise ORM is an easy-to-use asyncio ORM inspired by Django.

Tortoise ORM was build with relations in mind and admiration for the excellent and popular Django ORM. It's engraved in it's design that you are working not with just tables, you work with relational

Tortoise 3.3k Jan 07, 2023
Solrorm : A sort-of solr ORM for python

solrorm : A sort-of solr ORM for python solrpy - deprecated solrorm - currently in dev Usage Cores The first step to interact with solr using solrorm

Aj 1 Nov 21, 2021
A very simple CRUD class for SQLModel! ✨

Base SQLModel A very simple CRUD class for SQLModel! ✨ Inspired on: Full Stack FastAPI and PostgreSQL - Base Project Generator FastAPI Microservices I

Marcelo Trylesinski 40 Dec 14, 2022
Twisted wrapper for asynchronous PostgreSQL connections

This is txpostgres is a library for accessing a PostgreSQL database from the Twisted framework. It builds upon asynchronous features of the Psycopg da

Jan Urbański 104 Apr 22, 2022
A dataclasses-based ORM framework

dcorm A dataclasses-based ORM framework. [WIP] - Work in progress This framework is currently under development. A first release will be announced in

HOMEINFO - Digitale Informationssysteme GmbH 1 Dec 24, 2021
Global base classes for Pyramid SQLAlchemy applications.

pyramid_basemodel pyramid_basemodel is a thin, low level package that provides an SQLAlchemy declarative Base and a thread local scoped Session that c

Grzegorz Śliwiński 15 Jan 03, 2023