SQLAlchemy 2.0 Released

[zzzeek]

I would urge people who have had issues with the documentation to give the 2.0 documentation a try. Many aspects of it have been completely rewritten, both to correctly describe things in terms of the new APIs as well as to modernize a lot of old documentation that was written many years ago.

First off, SQLAlchemy's docs are pretty easy to get to, for a direct link just go to:

https://docs.sqlalchemy.org/

It's an esoteric URL I know! ;)

from there, docs that are new include:

- the Quickstart, so one can see in one quick page what SQLAlchemy usually looks like: https://docs.sqlalchemy.org/en/20/orm/quickstart.html

- the Unified Tutorial, which is a dive into basically every important concept across Core / ORM : https://docs.sqlalchemy.org/en/20/tutorial/index.html

- the ORM Querying guide, which is a "how to" for a full range of SQL generation: https://docs.sqlalchemy.org/en/20/orm/queryguide/index.html

it's still a lot to read of course but part of the idea of SQLAlchemy 2.0 was to create a straighter and more consistent narrative, while it continues to take on a very broad-based problem space. If you compare the docs to those of like, PostgreSQL or MySQL, those docs have a lot of sections and text too (vastly more). It's a big library.

[mmerickel]

SQLAlchemy and your efforts are so amazing and appreciated. Thank you and the team for all of your hard work on 2.0 as well as the heroic effort that was put into 1.4 as a stepping stone for existing codebases!

[v3ss0n]

So confused after reading unified tutorial. It's so confusing and too many styles to declare ORM

>>> class User(Base): ... __tablename__ = "user_account" ... ... id: Mapped[int] = mapped_column(primary_key=True) ... name: Mapped[str] = mapped_column(String(30)) ... fullname: Mapped[Optional[str]] ... ... addresses: Mapped[List["Address"]] = relationship(back_populates="user") ... ... def __repr__(self) -> str: ... return f"User(id={self.id!r}, name={self.name!r},

VS

class User(Base): __tablename__ = "user_account"

    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(30), nullable=False)
    fullname = mapped_column(String)

    addresses = relationship("Address", back_populates="user")

    # ... definition continues

Vs

user_table = Table( "user", mapper_registry.metadata, Column("id", Integer, primary_key=True), Column("name", String(50)), Column("fullname", String(50)), Column("nickname", String(12)), )

Vs

class Customers(Base): __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

Why so many styles? Why can't be like pydantic or Django and use just one style for ORM?

it's breaking zen of python, it makes searching examples on Google very hard ( esp with Google getting really crappy)

[mixmastamyk]

Great tool. I think this is why folks have trouble finding the docs:

Whole entire site is docs everywhere, including home page. Links to "docs" are tiny on the home page. Main menu calls them "library" instead of docs, why?

Majority of the stuff is focused on migration rather than new users. These two should be separated into separate tracks immediately. There is an attempt, but it gets lost somehow.

Also the release page linked here and "what's new in 2.0" go straight into the deep end, "inside baseball" style. These kind of pages should be very light. Tons of history-I'm personally not interested when trying to get something done. Unsolicited advice, move that into another blog post. ;-)

[thisismyswamp]

As a first time reader of these docs, they don't seem at all approachable.

For example: for the quickstart, the premise is simple - show me quickly how I can create a class, map it to the database, create some examples, and delete them. Something like what mongoose does here: https://mongoosejs.com/docs/

Instead, SQLAlchemy goes from how to declare models (with a wall of code followed by a wall of text), jumps into creating "engines", shows some generated SQL code for creating tables, and so on.

Sessions are simply used without being introduced, and many questions arise - am I hoarding a database connection as long as I am holding onto the session? Why should I do anything other than executing queries while that is happening? Then that means the `with` expression is useless, unless I want to explicitly run a multiple-query transaction - but why should this be the default?

It also comes across as clunky to be importing a `select` statement at the top level. Why not call select on the table objects themselves? And what's up with `select(...).select_from(...)`?

[elephantum]

Just want to thank you for all the work you do! We are heavy users of sqlalchemy, our life would be much harder without it!

[westurner]

Pandas had a docs sprint awhile back. Are the DOCs issues labeled?

[robertlagrant]

This has been a long time coming. Many congratulations.

[oblio]

Does it fully support types?

Edit:

Oh, it seems to, that's big. For comparison, Hibernate, the Java mammoth ORM, as far as I know, never fully moved to Java 5 generics (released 18 years ago).

Also dataclasses and enums. Also big, since there used to be a lot of duplication in configuration, SQLAlchemy doing its thing and the rest of the codebase just using now standard Python 3 features.

Cool!

[luhn]

Not only are the ORM objects typed, the query results are typed too. Last time I checked on 2.0 progress there wasn't a feasible way to implement this, so I'm thrilled to see that it made the release.

    # (variable) stmt: Select[Tuple[int, str]]
    stmt = select(User.id, User.name)
    
    with Session(e) as sess:
        for row in sess.execute(stmt):
            # (variable) row: Row[Tuple[int, str]]
            print(row)
    
        # (variable) users: Sequence[User]
        users = sess.scalars(select(User)).all()
    
        # (variable) users_legacy: List[User]
        users_legacy = sess.query(User).all()

https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html...

[siddboots]

It’s honestly something of a marvel to see a big ol’ framework like SQLAlchemy successfully manage these big structural api changes. The changes that have lead up to v1.4 and v2.0 have been very significant improvements to the user interface, and have kept the library up to date with the language in a way that is rare to see for a code base of such complexity. Also the new unified bulk insert interface is a big win for my ETL workflows.

Hats of to everyone be involved!

[bratao]

I learned the hard way that I need to always use SQLAlchemy. I used other Python ORMs before and has been bitten hard with random bugs.

[deaddodo]

I will always stand behind SQLAlchemy being one of the best designed ORMs around. It generates some of the best backend SQL for the chosen dialect and it’s separation behind low level “core” concepts and high level ORM concepts makes for the cleanest separation of concerns. Even if it can’t generate the best SQL for you, it’s bog-simple to fiddle with the core components to hint it in the proper direction.

[habitue]

Yeah, ORMs are ten thousand edge cases, it really lays to use a battle hardened one that has been lovingly maintained and improved constantly for over a decade.

Hats off to Mike, most projects that are this age are decrepit and you have kept it fresh.

[wendyshu]

Increased static type coverage: https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html...

[toinbis]

Love the fact that Release notes (https://www.sqlalchemy.org/blog/2023/01/26/sqlalchemy-2.0.0-...) give decent credits to SQLModel (https://sqlmodel.tiangolo.com/) for some of the typing innovations.

[airocker]

I was just working on sqlalchemy. zzzeek's help if you get stuck somewhere is outstanding. Async is a godsent feature for us.

[wendyshu]

Out of curiosity, what's the benefit of async for you?

[v3ss0n]

I would request an experience sqla developer to write a blog post, tutorial or even a paid course for new sqla, doing general queries and necessary db operation, using 2.0 specific, best ORM way of doing things ( only one ORM style).

And it should become default go-to doc for every new comers

[kerkeslager]

SQLAlchemy's lightweight SQL wrapping was a revelation when I came across it, but I've come to rely a lot on Django's migration tooling, enough so to choose Django for projects. What do people using SQLAlchemy use for migrations?

[deaddodo]

Alembic is the most common tool paired with SQLAlchemy.

[v3ss0n]

Alembic

[robertlagrant]

I just asked ChatGPT "How can I migrate database structures using SQLAlchemy?" and it gave an amazing answer. Alembic with examples.

[kh1]

If you use pandas with SQLAlchemy, you should be aware that pandas might not be fully compatible with SQLAlchemy 2.0 yet.

Some github issues:

https://github.com/pandas-dev/pandas/issues/40686

https://github.com/pandas-dev/pandas/issues/40460

I'm not actively following how compatible pandas has become, but you should absolutely test your code if you want to upgrade to 2.0.

[akprasad]

Congratulations on the release. I've been a happy user of SQLAlchemy for over a decade, and I'm looking forward to giving this version a spin.

[v3ss0n]

For a decade I avoided sqlalchemy, we finally got to use it last year and found it quite overwhelmingly complex and 1.4 documentation is messy. We had to use mixed styles.

I checked unified doc and it had improved but , still wall of texts.. quite hard to read. Readability count's.

[thisismyswamp]

Absolutely agree, coming from the Node.js world documentation for Python packages (especially more established ones) is largely unapproachable, wording is unnecessarily obtuse and descriptions go on for too long. Code is presented in walls instead of being elegantly interspersed with short descriptions.

[whoopdeepoo]

What do you use instead?

[thisismyswamp]

It doesn't matter how good a library is, if care is not taken to make the documentation clean, accessible, elegant, concise, its legs are cut off from the start.

Documentation authors should get out of their own heads while writing, and try as hard as possible to attain the perspective of a complete newcomer who will have no idea why a library needed to be split into Core and ORM in the first place.

[westurner]

From https://docs.sqlalchemy.org/en/20/dialects/ :

> Currently maintained external dialect projects for SQLAlchemy include: [...]

Is there a list of async [SQLA] DB adapters?

The SQLAlchemy 2.0 Release Docs: https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.htm...

[whoopdeepoo]

Incredible work, the best python database tool just got even better!

[trevor-e]

Sorry for detracting from the release news but SQLAlchemy has to have the least helpful website of any major library I can think of. I'm trying to find basic 101 code examples and it's just one wall of text after another. I found how to cite SQLAlchemy in a research paper and have yet to find a single code example.

[akprasad]

Under the Current Releases section, click the docs link then ORM Quick Start. But it's tough to find at a glance.

[wendyshu]

Yes, that's probably my biggest complaint about the library. The docs are there but hard to navigate. Sometimes crucial tips are buried in an aside on one of the many pages that talk about the same topic. It's difficult to get started and it's also difficult to find the ultimate explanation of what's going on.

[NegativeLatency]

I miss ActiveModel and ActiveRecord so much (after recently switching jobs to a python shop), docs, api etc were just so polished and battle hardened (yes they have their problems, but I was pretty proficient in using them well)

[epgui]

Check out pugsql, we switched from SQLAlchemy to that because the former was overkill, and way too complex.

[airocker]

I generally try StackOverflow/Google, then documentation and then discussion issues on SQLAlchemy. IT has worked well for me.

[throwme_123]

When I see such a poor website, I also expect the codebase to be clunky and cluttered.

Better alternative: - https://pugsql.org/

[aflukasz]

Congratulations!

Given how popular SQLAlchemy is, I wonder how many projects are going to wake up on Monday with failing tests (or production) due to not pinning versions and ignoring deprecation warnings...

[Dowwie]

Can someone share a summary as to what the caching strategy is and how it contributed to such gains?

[vladkovalev]

Fantastic, been waiting for this!

[wpwpwpw]

just want to thank the developers and community for their amazing effort. i just started webdev in python (which i did not previously used) and sqlalchemy's docs are amazing. again, thank you!