Using mapped_column instead of __table__args__ for foreign keys

[LasseMunk]

Hi,

Is it possible to have sqlacodegen output foreign keys in the format of

fk_id: Mapped[int] = mapped_column(Integer, ForeignKey('...'))

instead of

__table__args__ = (ForeignKeyConstraint(['...'])) instead of __table__args__ = (ForeignKeyConstraint(['...']))

? :)

I'm generating from a MySQL database using the latest sqlacodegen, using the --generator declerative flag. I have tried all the options I could find in the test cases and in the docs, but I don't seem to hit the right one, even though I see there are test cases which use this format

:)

Best, Lasse

[sheinbergon]

What are you looking to gain working that way? What's wrong with sqlacodegen's current behavior?

[LasseMunk]

Hi thank you for your reply :)

I don't think there are any kind "performance gains" or anything.

However what I propose is supported in sqlalchemy, there are testcases in codegens repo which suggests it's a known syntax :)

In my opinion what I suggest is very much easier to read and is similar to syntax you would find in other frameworks like .NET / Entity framework so it would be easier for enterprise teams knowing those kinds of frameworks to start new projects using SQLAlchemy :)

It would be a great option to have :)

Hope it makes sense ! :)

[agronholm]

I would have to refresh my memory, but IIRC we render the foreign keys in the column if possible. Could you paste the schema of the affected tables (or at least a reproducing case) so we could take a closer look?

[LasseMunk]

I sorry for the late reply. I had to wait until monday to get approval for posting this.. big corp ndr bullshit... anyway I have tried to get a table schema that shows that the foreign key is actually defined

I'm using the docker image MySQL 8.0 for my database.

I have changed the names from something else to 'airplanes' / 'airplane'. I don't think it should matter at all.

I ran

SHOW CREATE TABLE airplanes;

which outputs

CREATE TABLE `airplanes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `alias` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `address` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `monitored_with_map` tinyint(1) NOT NULL,
  `airplane_brand_id` int unsigned DEFAULT NULL,


  PRIMARY KEY (`id`),
  KEY `airplanes_name_index` (`name`),
  KEY `airplanes_alias_index` (`alias`),
  KEY `airplanes_airplane_brand_id_foreign` (`airplane_brand_id`),
  KEY `monitored_with_map` (`monitored_with_map`),
  CONSTRAINT `airplanes_airplane_brand_id_foreign` FOREIGN KEY (`airplane_brand_id`) REFERENCES `airplane_brands` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6171 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

And generates this SQLAlchemy file

import datetime
from typing import Optional

from sqlalchemy import TIMESTAMP, ForeignKeyConstraint, Index, text
from sqlalchemy.dialects.mysql import INTEGER, TINYINT, VARCHAR
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class AirplaneBrands(Base):
    __tablename__ = "airplane_brands"

    id: Mapped[int] = mapped_column(INTEGER, primary_key=True)
    value: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    text_display: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    description: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    default: Mapped[int] = mapped_column(TINYINT, nullable=False, server_default=text("'0'"))
    created_at: Mapped[datetime.datetime | None] = mapped_column(TIMESTAMP)
    updated_at: Mapped[datetime.datetime | None] = mapped_column(TIMESTAMP)
    deleted_at: Mapped[datetime.datetime | None] = mapped_column(TIMESTAMP)

    airplanes: Mapped[list["Airplanes"]] = relationship("Airplanes", back_populates="airplane_brand")


class Airplanes(Base):
    __tablename__ = "airplanes"
    __table_args__ = (
        ForeignKeyConstraint(
            ["airplane_brand_id"], ["airplane_brands.id"], name="airplanes_airplane_brand_id_foreign"
        ),
        Index("monitored_with_map", "monitored_with_map"),
        Index("airplanes_alias_index", "alias"),
        Index("airplanes_name_index", "name"),
        Index("airplanes_airplane_brand_id_foreign", "airplane_brand_id"),
    )

    id: Mapped[int] = mapped_column(INTEGER, primary_key=True)
    name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    alias: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    monitored_with_map: Mapped[int] = mapped_column(TINYINT(1), nullable=False)
    address: Mapped[str | None] = mapped_column(VARCHAR(20))
    airplane_brand_id: Mapped[int | None] = mapped_column(INTEGER)

    airplane_brand: Mapped[Optional["AirplaneBrands"]] = relationship("AirplaneBrands", back_populates="airplanes")

I would have hoped it would create something like this output

class Airplanes(Base):
    __tablename__ = "airplanes"
    __table_args__ = (
        Index("monitored_with_map", "monitored_with_map"),
        Index("airplanes_alias_index", "alias"),
        Index("airplanes_name_index", "name"),
        Index("airplanes_airplane_brand_id_foreign", "airplane_brand_id"),
    )

    id: Mapped[int] = mapped_column(INTEGER, primary_key=True)
    name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    alias: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    monitored_with_map: Mapped[int] = mapped_column(TINYINT(1), nullable=False)
    address: Mapped[str | None] = mapped_column(VARCHAR(20))
    
    airplane_brand_id: Mapped[int | None] = mapped_column(INTEGER, ForeignKey("airplane_brands.id"))
    airplane_brand: Mapped[Optional["AirplaneBrands"]] = relationship("AirplaneBrands", back_populates="airplanes")

Does it make sense ? :)

[agronholm]

Thanks! And if you need a "why", there's the fact that Tidelift provides a channel that will get priority bugfixes and feature requests for subscribers.

[sheinbergon]

@LasseMunk it won't make any of us rich, that's for sure. It does provide a very strong incentive to keep pushing this repo forward

[LasseMunk]

I get it - no need for explaining ! :)

[LasseMunk]

I just visited tidelift and searched for your package. I don't really see anything except for this link which doesn't state so much :)

Also I think it would be difficult to convince someone about making a subscription, I could maybe convince someone to "buymeacoffee" one time amount or something like that.

I am more than happy to help out, but i'm 100% sure that If I were to send the link to someone with finance, that person would spend no more than 2-5 minutes on it so it should be easy.

https://tidelift.com/lifter/search/pypi/sqlacodegen

looks like this for me

[agronholm]

The sponsor link on the GitHub page:

links to a page where you can become a subscriber.