How to make sure sqlmodel doesn't re-use numeric ID's?

FilipeMarch commented 2 years ago

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 Session, SQLModel, Field, Relationship, create_engine
from typing import Optional, Union, List

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

class Transaction(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    worker_id: Optional[int] = Field(index=True, foreign_key="worker.id")

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)
SQLModel.metadata.create_all(engine)

# Create a worker
with Session(engine) as session:
    worker = Worker(name='Worker 1')
    session.add(worker)
    session.commit()
    session.refresh(worker)

# Create a transaction pointing to this worker
with Session(engine) as session:
    transaction = Transaction(worker_id=worker.id)
    session.add(transaction)
    session.commit()
    session.refresh(transaction)

# Delete the worker
with Session(engine) as session:
    session.delete(worker)
    session.commit()

# Print all transactions and workers on database
with Session(engine) as session:
    transactions = session.query(Transaction).all()
    workers = session.query(Worker).all()
    print(transactions)
    print(workers)

# Create a new worker. This worker should not have ID 1
with Session(engine) as session:
    worker = Worker(name='Worker 2')
    session.add(worker)
    session.commit()
    session.refresh(worker)

Description

Create a worker
Create a transaction pointing to this worker
Delete the worker
Now the transaction is pointing to a non-existent worker
Create a new worker
The new worker has the same ID as the deleted worker, i.e., ID = 1. I was expecting a new worker to have ID = 2. I want to have proper auto-incrementation (where values are never reused).

Operating System

Linux

Operating System Details

Arch Linux containing python-sqlalchemy 1.4.39-1

SQLModel Version

0.0.6

Python Version

3.10.5

Additional Context

I am having the same problem as this stackoverflow post

The problem is solved by

[...] setting sqlite_autoincrement=True for a table [...]

SQLAlchemy documentation

First, I don't know how to set sqlite_autoincrement with sqlmodel. I tried different things but didn't work. Second, I tried to create a Relationship containing sa_relationship_kwargs={ "cascade": "all,delete" }, but cascade simply does not work. When I delete the worker neither the transactions pointing to this worker are deleted, nor they become None. They are simply pointing to a non-existent worker.

Then if I create a new worker, the new worker inherits the transactions from the deleted worker, this does not make any sense I would appreciate any idea on how to solve this. If I am able to use sqlite_autoincrement then new workers would never reuse the ID from previous workers, this would be a good solution.

FilipeMarch commented 2 years ago

Relationship does not work

from typing import Optional, List
from sqlmodel import Field, SQLModel, create_engine, Session, Relationship

class Worker(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    transactions: List["Transaction"] = Relationship(
        sa_relationship_kwargs={
            "cascade": ""
        }
    )

class Transaction(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    worker_id: Optional[int] = Field(index=True, foreign_key="worker.id")

sqlite_file_name = "database/database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=False)
SQLModel.metadata.create_all(engine)

# Create a worker
with Session(engine) as session:
    worker = Worker(name='Worker 1')
    session.add(worker)
    session.commit()
    session.refresh(worker)

# Create a transaction pointing to this worker
with Session(engine) as session:
    transaction = Transaction(worker_id=worker.id)
    session.add(transaction)
    session.commit()
    session.refresh(transaction)

# Delete the worker
with Session(engine) as session:
    session.delete(worker)
    session.commit()

# Print all transactions on database
with Session(engine) as session:
    transactions = session.query(Transaction).all()
    print(transactions)

The output:

>>> [Transaction(id=1, worker_id=1)]

Result is the same even using "cascade": "all, delete, delete-orphan"

meirdev commented 1 year ago

Your models should look like this:

class Worker(SQLModel, table=True):
    __table_args__ = {'sqlite_autoincrement': True}

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    transactions: List["Transaction"] = Relationship(back_populates="worker", sa_relationship_kwargs={ "cascade": "all,delete" })

class Transaction(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    worker_id: Optional[int] = Field(index=True, foreign_key="worker.id")

    worker: Optional[Worker] = Relationship(back_populates="transactions")

FilipeMarch commented 1 year ago

Hello, @meirdev, thanks for the help, but this does not work here For me the result is the same:

from typing import Optional, List
from sqlmodel import Field, SQLModel, create_engine, Session, Relationship

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

    transactions: List["Transaction"] = Relationship(
        back_populates = "worker", 
        sa_relationship_kwargs = {"cascade": "all,delete"}
        )

    __table_args__ = {'sqlite_autoincrement': True}

class Transaction(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    worker_id: Optional[int] = Field(index=True, foreign_key="worker.id")

    worker: Optional[Worker] = Relationship(back_populates = "transactions")

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=False)
SQLModel.metadata.create_all(engine)

# Create a worker
with Session(engine) as session:
    worker = Worker(name='Worker 1')
    session.add(worker)
    session.commit()
    session.refresh(worker)

# Create a transaction pointing to this worker
with Session(engine) as session:
    transaction = Transaction(worker_id=worker.id)
    session.add(transaction)
    session.commit()
    session.refresh(transaction)

# Delete the worker
with Session(engine) as session:
    session.delete(worker)
    session.commit()

# Print all transactions on database
with Session(engine) as session:
    transactions = session.query(Transaction).all()
    print(transactions)

The output:

[Transaction(id=1, worker_id=1)]

meirdev commented 1 year ago

Use sqlalchemy <= 1.4.35, there are issues with version X>1.4.35 related to the Relationship field

FilipeMarch commented 1 year ago

Ok, I downgraded to sqlalchemy==1.4.30 and now it is working, thanks @meirdev

tiangolo / sqlmodel