SQLAlchemy 2.0 support

[toinbis]

Hey there!

SQLAlchemy has already released a beta3 for 2.0, which would indicate the final 2.0 release is approaching quickly.

Currently sqlalchemy-bigquery support only <=1.4.27, if I am not mistaken. Am wonder is there a plan to support SQLAlchemy 2.0?

Thanks!

[tswast]

Thanks for the report! I think we're good now for 1.4.x versions, but I noticed in https://github.com/googleapis/python-bigquery-sqlalchemy/pull/513 that there are some failures we'll need to address for 2.x support. We'll keep this issue open while we figure out what's needed.

[Giuzzilla]

I think that a first step to go forward with this would be forcing the usage of SQLAlchemy >= 1.4 first (instead of >=1.2), this would allow using the new 2.0 syntax within the library (e.g. positional args in select statements vs iterables). https://docs.sqlalchemy.org/en/14/changelog/migration_20.html

[SlavaSkvortsov]

Hello!

Do you have any plans to unblock SQLAlchemy 2.0? It would be really helpful :)

Thank you!

[sar009]

Hi @tswast, SQLAlchemy 2.0 is out of beta now, can bigquery-sqlalchemy support 2.0

[meredithslota]

Hi folks — we don't have support for 2.0 planned yet, as there are some competing priorities internally. Apologies on the delays; we do not have an ETA yet for this.

[alexjironkin]

This is now a blocker for us to migrate to newer versions of both flask_sqlalchemy and sqlalchemy. When will you guys be able to look into supporting sqlalchemy v2? After all 1.4 is now a legacy version receiving minimal support.

[bpafoshizle]

Would be great to have this. This is preventing using langchain and llama-index for LLM development against big query as those require SQLAlchemy > 2.0. This means we may be pulling data out of BQ into e.g. postgres to build our LLM apps.

[richard-sparq]

@bpafoshizle 100% same here

[sharoonthomas]

@meredithslota @tswast I have a patch for supporting 2.0, but there are quite a few backward incompatible changes.

Do you have a vision on how you want to support 2.0? Are you thinking of a new major version of sqlalchemy-bigquery that only supports sqlalchemy 2.0+? If yes, this makes the patch simpler. Supporting both <2.0 and >=2.0 seems incredibly hard with the select() syntax change.

[tswast]

Thanks @sharoonthomas for taking the initiative on this! I'm just a 20% contributor on this project these days, but I'll share internally with the current maintainers to review.

I would suggest a new major version for such a change. Please prefix your commit message with feat!:, such as feat!: support SQLAlchemy 2.0, drop support for 1.x. We use release-please to manage our releases & changelog: https://github.com/googleapis/release-please/blob/main/README.md#how-should-i-write-my-commits

[sharoonthomas]

Thank you @tswast. Updated the commit message.

[Giuzzilla]

Hey @sharoonthomas, thanks for your work on this, just a little note: SQLAlchemy 1.4.x supports the new select syntax (it's intended to be a bridge release for 2.x). Not sure what other changes you had to do, but maybe you can consider relaxing the constraint to >= 1.4.

[chalmerlowe]

A pre-release version of python-bigquery-sqlalchemy has been released with coverage for 1.4 AND 2.0. Gonna close this issue as complete.

As feedback on the pre-release rolls in, we will update that branch and ultimately merge with main when ready.