Understanding the Difference Between server_default=func.now() and server_default=text('now()') in SQLAlchemy
server_default=func.now() vs server_default=text('now()')
This article discusses the differences between using server_default=func.now() and server_default=text('now()') in SQLAlchemy to specify a server-side default value for a column in a database table. Understanding these differences will help you choose the right approach for your specific database and application needs.
Introduction
Both server_default=func.now() and server_default=text('now()') can be used to specify a server-side default value for a column in SQLAlchemy, but they differ in how the default value is generated and which database backends they support.
server_default=func.now()
The func.now() approach uses SQLAlchemy's database function abstraction to generate the current timestamp. This method is database-dialect aware and automatically generates the correct SQL expression for your specific database backend.
from sqlalchemy import Column, DateTime, func
class MyModel(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, onupdate=func.now())Database-Specific Behavior
SQLAlchemy automatically translates func.now() to the appropriate database function:
- PostgreSQL: Generates
now() - MySQL: Generates
CURRENT_TIMESTAMP - SQLite: Generates
CURRENT_TIMESTAMP - SQL Server: Generates
GETDATE()
server_default=text('now()')
The text('now()') approach uses a plain SQL string expression. This gives you direct control over the SQL that's generated, but requires you to write database-compatible SQL yourself.
from sqlalchemy import Column, DateTime, text
class MyModel(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
created_at = Column(DateTime, server_default=text('now()'))
updated_at = Column(DateTime, onupdate=text('now()'))Note: The text() function creates a SQLAlchemy TextClause object that represents the raw SQL expression. This approach is not automatically translated for different database backends.
When to Use Which
Use func.now() When:
- • You need portability across different databases
- • You want SQLAlchemy to handle database-specific differences automatically
- • You're building a multi-database application
- • You prefer a declarative, database-agnostic approach
Use text('now()') When:
- • You're working with a single, specific database
- • You need a custom SQL expression that func.now() doesn't support
- • You want full control over the generated SQL
- • You're using database-specific features or functions
Quick Comparison
| Feature | func.now() | text('now()') |
|---|---|---|
| Database Portability | ✅ Yes | ❌ No |
| Auto Translation | ✅ Yes | ❌ No |
| Custom SQL | ❌ Limited | ✅ Full control |
| Type Safety | ✅ Higher | ⚠️ Lower |
Summary
server_default=func.now() is the recommended approach for most applications as it provides database portability and automatic translation. Use server_default=text('now()') when you need specific database functions or custom SQL expressions that aren't supported by SQLAlchemy's function abstraction.
The choice between the two methods ultimately depends on your specific use case, database requirements, and whether you need multi-database support.