Alembic cascade delete and upgrade and downgrade order

Question

`Firstly I want to apologize if I don't understand how Pydentic works, I am new to FastAPI and Pydantic.

The first problem is the cascade dele of the tables. At every relationship parameter cascade="all, delete-orphan" is added but when I run alembic upgrade head command in which is delete table, the error appears:

sqlalchemy.exc.InternalError: (psycopg2.errors.DependentObjectsStillExist) cannot drop table termin because other objects depend on it
DETAIL:  constraint zavrseniTermin_idTermina_fkey on table "zavrseniTermin" depends on table termin
constraint sportistaTermin_idTermina_fkey on table "sportistaTermin" depends on table termin
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

With the different table name and constraint depending on the delete order in alembic version file, the error is the same.

Model file:

from sqlalchemy import Boolean, Date, ForeignKey, Column, Text, Integer, String, Time, Float, LargeBinary
from sqlalchemy.orm import relationship
from passlib.context import CryptContext
from database.database import Base

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

class Sport(Base):
    __tablename__ = "sport"

    idSporta = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    imeSporta = Column(String,  unique=True)

    termin = relationship("Termin", back_populates="sport", cascade="all, delete-orphan")
    dostupniSportovi = relationship("DostupniSportovi", back_populates="sport", cascade="all, delete-orphan")

class RadnoVrijeme(Base):
    __tablename__ = "radnoVrijeme"

    idRadnogVremena = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    pocetak = Column(Time)
    kraj = Column(Time)

    dvorana = relationship("Dvorana", back_populates="radnoVrijeme", cascade="all, delete-orphan")

class Banka(Base):
    __tablename__ = "banka"
    
    idRacuna = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    balans = Column(Float, nullable=False)
    ziroRacun = Column(String, nullable=False, unique=True)
    pin = Column(String, nullable=False)

    user = relationship("User", back_populates="banka", cascade="all, delete-orphan")

    def verifyPin(self, pin: int):
        return pwd_context.verify(pin, self.pin)

    def getHashedPin(self, pin: int):
        return pwd_context.hash(pin)

class User(Base):
    __tablename__ = 'user'

    idUsera = Column(Integer, primary_key=True, unique=True, index=True, nullable=False)
    username = Column(String, unique=True, index=True, nullable=False)
    sifra = Column(String, nullable=False)
    rijeci = Column(String, default=None)
    verifikovan = Column(Boolean, default=False)
    mail = Column(String, unique=True, nullable=False)
    referalCode = Column(String, unique=True, default=None)
    ime = Column(String, nullable=False)
    prezime = Column(String, nullable=False)
    profilna = Column(Integer, ForeignKey('slike.idSlike'))
    spol = Column(String, nullable=False)
    godine = Column(Integer, default=None)
    opis = Column(Text, default=None)
    visina = Column(Integer, default=None)
    tezina = Column(Float, default=None)
    coins = Column(Float, default=None)
    racun = Column(Integer, ForeignKey('banka.idRacuna'), unique=True)

    banka = relationship("Banka", back_populates="user", cascade="all, delete-orphan")
    admin = relationship("Admin", back_populates="user", cascade="all, delete-orphan")
    vlasnik = relationship("Vlasnik", back_populates="user", cascade="all, delete-orphan")
    sportista = relationship("Sportista", back_populates="user", cascade="all, delete-orphan")

    prijateljstvaUser1 = relationship("Prijateljstva", foreign_keys="Prijateljstva.idUser1", back_populates="user1", overlaps="prijateljstvaUser1", cascade="all, delete-orphan")
    prijateljstvaUser2 = relationship("Prijateljstva", foreign_keys="Prijateljstva.idUser2", back_populates="user2", overlaps="prijateljstvaUser2", cascade="all, delete-orphan")

    slika = relationship("Slike", back_populates="profilna", cascade="all, delete-orphan")

    def verifyPassword(self, password: str):
        return pwd_context.verify(password, self.sifra)

    def getHashedPassword(self, password: str):
        return pwd_context.hash(password)   

class Admin(Base):
    __tablename__ = "admin"

    idAdmina = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idUsera = Column(Integer, ForeignKey('user.idUsera'))  

    user = relationship("User", back_populates="admin", cascade="all, delete-orphan")

class Vlasnik(Base):
    __tablename__ = "vlasnik"

    idVlasnika = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idUsera = Column(Integer, ForeignKey('user.idUsera'))  
    kontakt = Column(String)

    user = relationship("User", back_populates="vlasnik", cascade="all, delete-orphan")
    posjedovanje = relationship("Posjedovanje", back_populates="vlasnik", cascade="all, delete-orphan")

class Sportista(Base):
    __tablename__ = "sportista"

    idSportiste = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idUsera = Column(Integer, ForeignKey('user.idUsera'))  
    iskustvo = Column(String)
    dostupnost = Column(Boolean)

    sportistaTermin = relationship("SportistaTermin", back_populates="sportista", cascade="all, delete-orphan")
    user = relationship("User", back_populates="sportista", cascade="all, delete-orphan")

class Prijateljstva(Base):
    __tablename__ = "prijateljstva"

    idPrijateljstva = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idUser1 = Column(Integer, ForeignKey('user.idUsera'))
    idUser2 = Column(Integer, ForeignKey('user.idUsera'))

    user1 = relationship("User", foreign_keys=[idUser1], back_populates="prijateljstvaUser1", overlaps="prijateljstvaUser1", cascade="all, delete-orphan")
    user2 = relationship("User", foreign_keys=[idUser2], back_populates="prijateljstvaUser2", overlaps="prijateljstvaUser2", cascade="all, delete-orphan")

class Dvorana(Base):
    __tablename__ = "dvorana"

    idDvorane = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    otvoreno = Column(Integer, ForeignKey('radnoVrijeme.idRadnogVremena')) 
    lokacija = Column(String)
    slika = Column(Integer, ForeignKey('slike.idSlike'))
    pocetnaCijena = Column(Float)
    ocjena = Column(Float)
    brojOcjena = Column(Integer)

    radnoVrijeme = relationship("RadnoVrijeme", back_populates="dvorana", cascade="all, delete-orphan")
    posjedovanje = relationship("Posjedovanje", back_populates="dvorana", cascade="all, delete-orphan")
    termin = relationship("Termin", back_populates="dvorana", cascade="all, delete-orphan")
    dostupnost = relationship("Dostupnost", back_populates="dvorana", cascade="all, delete-orphan")
    turnir = relationship("Turnir", back_populates="dvorana", cascade="all, delete-orphan")
    dostupniSportovi = relationship("DostupniSportovi", back_populates="dvorana", cascade="all, delete-orphan")

    izgled = relationship("Slike", back_populates="slika_dvorane", cascade="all, delete-orphan")

class Posjedovanje(Base):
    __tablename__ = "posjedovanje"

    idPosjeda = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idVlasnika = Column(Integer, ForeignKey('vlasnik.idVlasnika'))  
    idDvorane = Column(Integer, ForeignKey('dvorana.idDvorane'))  

    vlasnik = relationship("Vlasnik", back_populates="posjedovanje")
    dvorana = relationship("Dvorana", back_populates="posjedovanje")

class Turnir(Base):
    __tablename__ = "turnir"

    idTurnira = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idDvorane = Column(Integer, ForeignKey('dvorana.idDvorane'))  
    ekipa = Column(String)
    velicina = Column(Integer)

    dvorana = relationship("Dvorana", back_populates="turnir", cascade="all, delete-orphan")

class Dostupnost(Base):
    __tablename__ = "dostupnost"

    idDostupnosti = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idDvorane = Column(Integer, ForeignKey('dvorana.idDvorane'))  
    datum = Column(Date)
    odKad = Column(Time)
    doKad = Column(Time)
    statusDostupnosti = Column(Boolean)

    dvorana = relationship("Dvorana", back_populates="dostupnost", cascade="all, delete-orphan")

class DostupniSportovi(Base):
    __tablename__ = "dostupniSportovi"

    idDostupnogSporta = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idDvorane = Column(Integer, ForeignKey('dvorana.idDvorane'))  
    idSporta = Column(Integer, ForeignKey('sport.idSporta'))  

    sport = relationship("Sport", back_populates="dostupniSportovi", cascade="all, delete-orphan")
    dvorana = relationship("Dvorana", back_populates="dostupniSportovi", cascade="all, delete-orphan")

class Termin(Base):
    __tablename__ = "termin"

    idTermina = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idSporta = Column(Integer, ForeignKey('sport.idSporta'))  
    idDvorane = Column(Integer, ForeignKey('dvorana.idDvorane'))  
    konacnaCijena = Column(Float)
    potrebno = Column(Integer)
    datum = Column(Date)
    sati = Column(Time)
    promoCode = Column(String, default=None)

    sport = relationship("Sport", back_populates="termin", cascade="all, delete-orphan")
    sportistaTermin = relationship("SportistaTermin", back_populates="termin", cascade="all, delete-orphan")
    zavrseniTermin = relationship("ZavrseniTermin", back_populates="termin", cascade="all, delete-orphan")
    dvorana = relationship("Dvorana", back_populates="termin", cascade="all, delete-orphan")

class ZavrseniTermin(Base):
    __tablename__ = "zavrseniTermin"

    idZavrsenogTermina = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idTermina = Column(Integer, ForeignKey('termin.idTermina'))  
    ocjena = Column(Float)

    termin = relationship("Termin", back_populates="zavrseniTermin", cascade="all, delete-orphan")

class SportistaTermin(Base):
    __tablename__ = "sportistaTermin"

    idSportistaTermin = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    idTermina = Column(Integer, ForeignKey('termin.idTermina'))  
    idSportiste = Column(Integer, ForeignKey('sportista.idSportiste'))  
    statusTermina = Column(String)

    termin = relationship("Termin", back_populates="sportistaTermin", cascade="all, delete-orphan")
    sportista = relationship("Sportista", back_populates="sportistaTermin", cascade="all, delete-orphan")

class Slike(Base):
    __tablename__ = "slike"

    idSlike = Column(Integer, primary_key=True, unique=True, autoincrement=True)
    slika = Column(LargeBinary)

    profilna = relationship("User", back_populates="slika", cascade="all, delete-orphan")
    slika_dvorane = relationship("Dvorana", back_populates="izgled", cascade="all, delete-orphan")

The second problem is the order of upgrade and downgrade functions. Firstly I ran alembic revision --autogenerate -m "prazna" command when the database was empty and it ran and it created a file in alembic version folder with the code:

"""prazna

Revision ID: cebf6dd2e8e4
Revises: 
Create Date: 2024-04-25 14:42:00.696612

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'cebf6dd2e8e4'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

After that I ran alembic upgrade head and it created alembic table in a database with row ID text inside it after that I started the server and the Model file created tables inside a database. After that I ran alembic revision --autogenerate -m "kreirana" command which created a new alembic version file:

"""kreirana

Revision ID: 45512bf01ead
Revises: cebf6dd2e8e4
Create Date: 2024-04-25 14:44:18.787147

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '45512bf01ead'
down_revision: Union[str, None] = 'cebf6dd2e8e4'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('posjedovanje')
    op.drop_table('turnir')
    op.drop_table('termin')
    op.drop_table('admin')
    op.drop_table('dvorana')
    op.drop_table('sportistaTermin')
    op.drop_index('ix_user_idUsera', table_name='user')
    op.drop_index('ix_user_username', table_name='user')
    op.drop_table('user')
    op.drop_table('dostupniSportovi')
    op.drop_table('slike')
    op.drop_table('sportista')
    op.drop_table('zavrseniTermin')
    op.drop_table('sport')
    op.drop_table('prijateljstva')
    op.drop_table('vlasnik')
    op.drop_table('dostupnost')
    op.drop_table('banka')
    op.drop_table('radnoVrijeme')
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('radnoVrijeme',
    sa.Column('idRadnogVremena', sa.INTEGER(), server_default=sa.text('nextval(\'"radnoVrijeme_idRadnogVremena_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('pocetak', postgresql.TIME(), autoincrement=False, nullable=True),
    sa.Column('kraj', postgresql.TIME(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('idRadnogVremena', name='radnoVrijeme_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('banka',
    sa.Column('idRacuna', sa.INTEGER(), server_default=sa.text('nextval(\'"banka_idRacuna_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('balans', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('ziroRacun', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('pin', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('idRacuna', name='banka_pkey'),
    sa.UniqueConstraint('ziroRacun', name='banka_ziroRacun_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('dostupnost',
    sa.Column('idDostupnosti', sa.INTEGER(), server_default=sa.text('nextval(\'"dostupnost_idDostupnosti_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idDvorane', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('datum', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('odKad', postgresql.TIME(), autoincrement=False, nullable=True),
    sa.Column('doKad', postgresql.TIME(), autoincrement=False, nullable=True),
    sa.Column('statusDostupnosti', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idDvorane'], ['dvorana.idDvorane'], name='dostupnost_idDvorane_fkey'),
    sa.PrimaryKeyConstraint('idDostupnosti', name='dostupnost_pkey')
    )
    op.create_table('vlasnik',
    sa.Column('idVlasnika', sa.INTEGER(), server_default=sa.text('nextval(\'"vlasnik_idVlasnika_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idUsera', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('kontakt', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idUsera'], ['user.idUsera'], name='vlasnik_idUsera_fkey'),
    sa.PrimaryKeyConstraint('idVlasnika', name='vlasnik_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('prijateljstva',
    sa.Column('idPrijateljstva', sa.INTEGER(),       server_default=sa.text('nextval(\'"prijateljstva_idPrijateljstva_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idUser1', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('idUser2', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idUser1'], ['user.idUsera'], name='prijateljstva_idUser1_fkey'),
    sa.ForeignKeyConstraint(['idUser2'], ['user.idUsera'], name='prijateljstva_idUser2_fkey'),
    sa.PrimaryKeyConstraint('idPrijateljstva', name='prijateljstva_pkey')
    )
    op.create_table('sport',
    sa.Column('idSporta', sa.INTEGER(), server_default=sa.text('nextval(\'"sport_idSporta_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('imeSporta', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('idSporta', name='sport_pkey'),
    sa.UniqueConstraint('imeSporta', name='sport_imeSporta_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('zavrseniTermin',
    sa.Column('idZavrsenogTermina', sa.INTEGER(), server_default=sa.text('nextval(\'"zavrseniTermin_idZavrsenogTermina_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idTermina', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('ocjena', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idTermina'], ['termin.idTermina'], name='zavrseniTermin_idTermina_fkey'),
    sa.PrimaryKeyConstraint('idZavrsenogTermina', name='zavrseniTermin_pkey')
    )
    op.create_table('sportista',
    sa.Column('idSportiste', sa.INTEGER(), server_default=sa.text('nextval(\'"sportista_idSportiste_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idUsera', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('iskustvo', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('dostupnost', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idUsera'], ['user.idUsera'], name='sportista_idUsera_fkey'),
    sa.PrimaryKeyConstraint('idSportiste', name='sportista_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('slike',
    sa.Column('idSlike', sa.INTEGER(), server_default=sa.text('nextval(\'"slike_idSlike_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('slika', postgresql.BYTEA(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('idSlike', name='slike_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('dostupniSportovi',
    sa.Column('idDostupnogSporta', sa.INTEGER(), server_default=sa.text('nextval(\'"dostupniSportovi_idDostupnogSporta_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idDvorane', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('idSporta', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idDvorane'], ['dvorana.idDvorane'], name='dostupniSportovi_idDvorane_fkey'),
    sa.ForeignKeyConstraint(['idSporta'], ['sport.idSporta'], name='dostupniSportovi_idSporta_fkey'),
    sa.PrimaryKeyConstraint('idDostupnogSporta', name='dostupniSportovi_pkey')
    )
    op.create_table('user',
    sa.Column('idUsera', sa.INTEGER(), server_default=sa.text('nextval(\'"user_idUsera_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('username', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('sifra', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('rijeci', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('verifikovan', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('mail', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('referalCode', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('ime', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('prezime', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('profilna', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('spol', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('godine', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('opis', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('visina', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('tezina', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('coins', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('racun', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['profilna'], ['slike.idSlike'], name='user_profilna_fkey'),
    sa.ForeignKeyConstraint(['racun'], ['banka.idRacuna'], name='user_racun_fkey'),
    sa.PrimaryKeyConstraint('idUsera', name='user_pkey'),
    sa.UniqueConstraint('mail', name='user_mail_key'),
    sa.UniqueConstraint('racun', name='user_racun_key'),
    sa.UniqueConstraint('referalCode', name='user_referalCode_key'),
    postgresql_ignore_search_path=False
    )
    op.create_index('ix_user_username', 'user', ['username'], unique=True)
    op.create_index('ix_user_idUsera', 'user', ['idUsera'], unique=True)
    op.create_table('sportistaTermin',
    sa.Column('idSportistaTermin', sa.INTEGER(), server_default=sa.text('nextval(\'"sportistaTermin_idSportistaTermin_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idTermina', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('idSportiste', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('statusTermina', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idSportiste'], ['sportista.idSportiste'], name='sportistaTermin_idSportiste_fkey'),
    sa.ForeignKeyConstraint(['idTermina'], ['termin.idTermina'], name='sportistaTermin_idTermina_fkey'),
    sa.PrimaryKeyConstraint('idSportistaTermin', name='sportistaTermin_pkey')
    )
    op.create_table('dvorana',
    sa.Column('idDvorane', sa.INTEGER(), server_default=sa.text('nextval(\'"dvorana_idDvorane_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('otvoreno', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('lokacija', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('slika', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('pocetnaCijena', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('ocjena', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('brojOcjena', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['otvoreno'], ['radnoVrijeme.idRadnogVremena'], name='dvorana_otvoreno_fkey'),
    sa.ForeignKeyConstraint(['slika'], ['slike.idSlike'], name='dvorana_slika_fkey'),
    sa.PrimaryKeyConstraint('idDvorane', name='dvorana_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('admin',
    sa.Column('idAdmina', sa.INTEGER(), server_default=sa.text('nextval(\'"admin_idAdmina_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idUsera', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idUsera'], ['user.idUsera'], name='admin_idUsera_fkey'),
    sa.PrimaryKeyConstraint('idAdmina', name='admin_pkey')
    )
    op.create_table('termin',
    sa.Column('idTermina', sa.INTEGER(), server_default=sa.text('nextval(\'"termin_idTermina_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idSporta', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('idDvorane', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('konacnaCijena', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('potrebno', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('datum', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('sati', postgresql.TIME(), autoincrement=False, nullable=True),
    sa.Column('promoCode', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idDvorane'], ['dvorana.idDvorane'], name='termin_idDvorane_fkey'),
    sa.ForeignKeyConstraint(['idSporta'], ['sport.idSporta'], name='termin_idSporta_fkey'),
    sa.PrimaryKeyConstraint('idTermina', name='termin_pkey')
    )
    op.create_table('turnir',
    sa.Column('idTurnira', sa.INTEGER(), server_default=sa.text('nextval(\'"turnir_idTurnira_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idDvorane', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('ekipa', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('velicina', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idDvorane'], ['dvorana.idDvorane'], name='turnir_idDvorane_fkey'),
    sa.PrimaryKeyConstraint('idTurnira', name='turnir_pkey')
    )
    op.create_table('posjedovanje',
    sa.Column('idPosjeda', sa.INTEGER(), server_default=sa.text('nextval(\'"posjedovanje_idPosjeda_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('idVlasnika', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('idDvorane', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['idDvorane'], ['dvorana.idDvorane'], name='posjedovanje_idDvorane_fkey'),
    sa.ForeignKeyConstraint(['idVlasnika'], ['vlasnik.idVlasnika'], name='posjedovanje_idVlasnika_fkey'),
    sa.PrimaryKeyConstraint('idPosjeda', name='posjedovanje_pkey')
    )
    # ### end Alembic commands ###

Maybe I understand this in the wrong way but should creating tables be in the upgrade function and deleting tables in the downgrade function?

If it is important versions are:

alembic==1.13.1
fastapi==0.110.0
SQLAlchemy==2.0.28
uvicorn==0.28.0
Python 3.12.3

I am using PostgreSQL database

Project folder architecture

I typed replacing parameter cascade="all, delete-orphan" with cascade="all, delete", but it didn't work. I also tried adding ondelete="CASCADE" parameter where foreign keys were created, also didn't work.`

Answer 1

You have to modify the env.py(inside alembic folder) file as below:

from database.database import Base
## you also have to import all the models which inherit from Base
from src.models import * # change this to adapt to your needs

/* generated code here */

target_metadata = Base.metadata

/* generated code here */

This will create or modify your tables based on your models (if declared properly, check the docs for this)