Introduction
SQLAlchemy model instances are not directly JSON-serializable because they are Python objects with internal state, relationships, and metadata. The common approaches are: a custom to_dict() method on models, __dict__ filtering, the marshmallow-sqlalchemy library for schema-based serialization, or Pydantic with sqlmodel for type-safe serialization. For simple cases, converting column values to a dictionary and passing it to json.dumps() works. For complex cases with relationships and nested objects, use a serialization library.
Method 1: Custom to_dict() Method
1from sqlalchemy import Column, Integer, String, DateTime, create_engine
2from sqlalchemy.orm import declarative_base, Session
3from datetime import datetime
4import json
5
6Base = declarative_base()
7
8class User(Base):
9 __tablename__ = 'users'
10
11 id = Column(Integer, primary_key=True)
12 name = Column(String(100))
13 email = Column(String(200))
14 created_at = Column(DateTime, default=datetime.utcnow)
15
16 def to_dict(self):
17 return {
18 col.name: getattr(self, col.name)
19 for col in self.__table__.columns
20 }
21
22# Usage
23engine = create_engine('sqlite:///app.db')
24Base.metadata.create_all(engine)
25
26with Session(engine) as session:
27 user = session.query(User).first()
28 user_dict = user.to_dict()
29 print(json.dumps(user_dict, default=str))
30 # {"id": 1, "name": "Alice", "email": "[email protected]", "created_at": "2025-01-15 10:30:00"}
__table__.columns iterates over all column definitions. default=str in json.dumps() handles non-serializable types like datetime by converting them to strings.
Method 2: Reusable Mixin
1import json
2from datetime import datetime, date
3from decimal import Decimal
4
5class SerializerMixin:
6 def to_dict(self, exclude=None, include_relationships=False):
7 exclude = exclude or set()
8 result = {}
9
10 for col in self.__table__.columns:
11 if col.name in exclude:
12 continue
13 value = getattr(self, col.name)
14 result[col.name] = self._serialize_value(value)
15
16 if include_relationships:
17 for rel in self.__mapper__.relationships:
18 if rel.key in exclude:
19 continue
20 related = getattr(self, rel.key)
21 if related is None:
22 result[rel.key] = None
23 elif isinstance(related, list):
24 result[rel.key] = [item.to_dict() for item in related]
25 else:
26 result[rel.key] = related.to_dict()
27
28 return result
29
30 @staticmethod
31 def _serialize_value(value):
32 if isinstance(value, (datetime, date)):
33 return value.isoformat()
34 if isinstance(value, Decimal):
35 return float(value)
36 if isinstance(value, bytes):
37 return value.decode('utf-8', errors='replace')
38 return value
39
40 def to_json(self, **kwargs):
41 return json.dumps(self.to_dict(**kwargs))
42
43class User(SerializerMixin, Base):
44 __tablename__ = 'users'
45 id = Column(Integer, primary_key=True)
46 name = Column(String(100))
47 email = Column(String(200))
48
49# Usage
50user = session.query(User).first()
51print(user.to_json())
52print(user.to_dict(exclude={'email'}))
A mixin class provides reusable serialization across all models.
Method 3: Using marshmallow-sqlalchemy
pip install marshmallow-sqlalchemy
1from marshmallow_sqlalchemy import SQLAlchemyAutoSchema
2
3class UserSchema(SQLAlchemyAutoSchema):
4 class Meta:
5 model = User
6 include_relationships = True
7 load_instance = True
8
9# Serialize single object
10user_schema = UserSchema()
11user = session.query(User).first()
12result = user_schema.dump(user)
13print(json.dumps(result))
14
15# Serialize multiple objects
16users_schema = UserSchema(many=True)
17users = session.query(User).all()
18result = users_schema.dump(users)
19print(json.dumps(result))
20
21# Exclude fields
22class UserPublicSchema(SQLAlchemyAutoSchema):
23 class Meta:
24 model = User
25 exclude = ('email',)
26
27public_data = UserPublicSchema().dump(user)
marshmallow-sqlalchemy auto-generates schema fields from the model definition and handles nested relationships, validation, and custom field transformations.
Method 4: Pydantic with SQLModel
1from sqlmodel import SQLModel, Field, Session, create_engine, select
2from datetime import datetime
3from typing import Optional
4
5class User(SQLModel, table=True):
6 id: Optional[int] = Field(default=None, primary_key=True)
7 name: str
8 email: str
9 created_at: datetime = Field(default_factory=datetime.utcnow)
10
11# SQLModel objects are automatically JSON-serializable
12engine = create_engine("sqlite:///app.db")
13SQLModel.metadata.create_all(engine)
14
15with Session(engine) as session:
16 user = session.exec(select(User)).first()
17 # Direct JSON serialization
18 print(user.model_dump_json())
19 # {"id": 1, "name": "Alice", "email": "[email protected]", ...}
20
21 # To dict
22 print(user.model_dump())
23 # {'id': 1, 'name': 'Alice', 'email': '[email protected]', ...}
24
25 # Exclude fields
26 print(user.model_dump_json(exclude={'email'}))
SQLModel combines SQLAlchemy models with Pydantic schemas, giving you automatic serialization without additional schema classes.
Method 5: Flask-SQLAlchemy with jsonify
1from flask import Flask, jsonify
2from flask_sqlalchemy import SQLAlchemy
3
4app = Flask(__name__)
5app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
6db = SQLAlchemy(app)
7
8class User(db.Model):
9 id = db.Column(db.Integer, primary_key=True)
10 name = db.Column(db.String(100))
11 email = db.Column(db.String(200))
12
13 def to_dict(self):
14 return {col.name: getattr(self, col.name) for col in self.__table__.columns}
15
16@app.route('/users')
17def get_users():
18 users = User.query.all()
19 return jsonify([u.to_dict() for u in users])
20
21@app.route('/users/<int:user_id>')
22def get_user(user_id):
23 user = User.query.get_or_404(user_id)
24 return jsonify(user.to_dict())
Serializing Query Results (Row Objects)
1from sqlalchemy import text
2
3# Raw SQL queries return Row objects, not model instances
4with Session(engine) as session:
5 result = session.execute(text("SELECT id, name FROM users"))
6 rows = result.fetchall()
7
8 # Row objects support _asdict()
9 data = [dict(row._mapping) for row in rows]
10 print(json.dumps(data))
11 # [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
12
13 # With labeled columns
14 result = session.execute(text("SELECT id, name as username FROM users"))
15 data = [dict(row._mapping) for row in result]
Common Pitfalls
DateTime not JSON-serializable: json.dumps() raises TypeError on datetime objects. Use default=str, .isoformat(), or a custom encoder to convert dates to strings.
Lazy-loaded relationships cause N+1 queries: Accessing user.orders in a loop triggers a separate query per user. Use joinedload() or selectinload() when serializing relationships to avoid performance issues.
Exposing sensitive fields: A generic to_dict() that serializes all columns may expose passwords, tokens, or internal IDs. Always explicitly exclude sensitive fields or use a whitelist approach.
Detached instance errors: Accessing model attributes after the session is closed raises DetachedInstanceError. Either serialize inside the session context or use expire_on_commit=False on the session.
Circular references in relationships: Bidirectional relationships (User has Orders, Order has User) cause infinite recursion in recursive to_dict(). Set a depth limit or exclude back-references.
Summary
Custom to_dict() method on models — simplest approach for basic serialization
SerializerMixin — reusable mixin with relationship support and type handling
marshmallow-sqlalchemy — full-featured schema-based serialization with validation
SQLModel (Pydantic + SQLAlchemy) — automatic serialization without extra schema classes
Use default=str in json.dumps() to handle datetime and other non-serializable types
Always exclude sensitive fields and handle lazy-loaded relationships explicitly
