Does sqlalchemy.sql.functions really need now() and current_timestamp()? · sqlalchemy/sqlalchemy

相关文章推荐

刚毅的鼠标 · Oracle ...· 2 年前 ·

兴奋的西装 · 浅谈数据库Join的实现原理_网易订阅· 2 年前 ·

英姿勃勃的刺猬 · NodeJS sequelize auto ...· 2 年前 ·

淡定的乒乓球 · DevExpress ...· 3 年前 ·

直爽的柑橘 · Spring Boot 启动错误：To ...· 3 年前 ·

I just got burned not realizing that alembic revision --autogenerate doesn't generate dialect agnostic migrations.

Specifically I used sqlalchemy.sql.functions.now() ;
for Postgres this is now() , but for MS SQL it is CURRENT_TIMESTAMP due to 👇🏻
sqlalchemy/lib/sqlalchemy/dialects/mssql/base.py Lines 1789 to 1790 0a57293

Since MS SQL doesn't have a now() it means that if you alembic revision --autogenerate while pointing at Postgres you get a migration file which only works on Postgres, but if you are pointing at MS SQL you get a migration file which works on both.

What's frustrating is that:

There's already a sqlalchemy.sql.functions.current_timestamp() , which generates a CURRENT_TIMESTAMP for both dialects (i.e. it generates a dialect agnostic migration file), and:

Per this discussion :

CURRENT_TIMESTAMP, transaction_timestamp() and now() do exactly the same.

So my questions are:
Do we really need sqlalchemy.sql.functions.now() ?
What purpose does it serve beyond potentially confusing people? 🙈

Moreover, even if someone really did want to generate now() , they could use func anyway:

Any name can be given to func . If the function name is unknown to SQLAlchemy, it will be rendered exactly as is.

Since MS SQL doesn't have a now() it means that if you alembic revision --autogenerate while pointing at Postgres you get a migration file which only works on Postgres, but if you are pointing at MS SQL you get a migration file which works on both.

what do you mean by this? If the alembic migration uses func.now() it will render correctly when executing in mssql. Could you provide a migration example?

it will render correctly when executing in mssql

Yes ⬆️ is true if you --autogenerate with sqlalchemy.url = mssql+pymssql... , i.e. you get:

    sa.Column('created', sa.DateTime(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
But in our development environments we use sqlalchemy.url = postgresql://, and then alembic revision --autogenerate and check generated migration in. Which (because Postgres) causes:
    sa.Column('created', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
⬆️ this doesn't work in MS SQL.
We're now using functions.current_timestamp() (which generates CURRENT_TIMESTAMP regardless of dialect), but that raises the question of what purpose functions.now() serves (other than confusing people like me!).
        Alembic autogenerate has limited options for rendering server_default.  we dont have a full blown "render an entire SQLAlchemy expression in Python code" feature so those necessarily have to come out as text() with the SQL rendered inside them as far as what autogenerate can do automatically.
For this particular issue, you basically just need to check the rendered migrations as you would normally do anyway (don't assume an autogen migration is perfect as generated, alembic does not guarantee autogens that dont need manual inspection) and either identify that SQL Server doesn't have a "now()" function, or just replace server_default=text() calls with a SQL Core construct like func.now().  cross-generating migrations for MSSQL + Postgresql is very doable, i did a large project with this base, but you need to always go over the migrations as there are lots of concepts that don't match across these DBs like date and time functions, unique constraints, etc.
cross-generating migrations for MSSQL + Postgresql is very doable, i did a large project with this base
That's reassuring to hear 😁
I concur that checking autogenerated migrations is sensible, but I'm still not sure if/why functions.now() exists when functions.current_timestamp() is semantically the same and guaranteed to be dialect agnostic 🤔
but I'm still not sure if/why functions.now() exists when functions.current_timestamp() is semantically the same and guaranteed to be dialect agnostic
I don't think there is a particular reason. This is the original issue: #943 added 14 years ago and closed by a0ffeb5
        now() is also easier to type and current_timestamp might not have been as available then.  it would be many years before alembic would be invented and overall this is just a small inconvenience with the autogen of server defaults.
that said if "func.now()" actually rendered current_timestamp on Postgresql that would also have prevented you from having this particular issue but overall there's no guarantee of PG autogen migrations working on other DBs without manual intervention.