SQLAlchemy

serialization

JSON

Python

database

How to serialize SqlAlchemy result to JSON?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

Databases Python

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

python

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

python

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

bash

pip install marshmallow-sqlalchemy

python

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

python

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

python

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)

python

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

System design interview problems on this topic

All system design practice problems

Course

Beginner

27 lessons

10 hours

System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

How to serialize SqlAlchemy result to JSON?

System Design practice on Codemia

Introduction

Method 1: Custom to_dict() Method

Method 2: Reusable Mixin

Method 3: Using marshmallow-sqlalchemy

Method 4: Pydantic with SQLModel

Method 5: Flask-SQLAlchemy with jsonify

Serializing Query Results (Row Objects)

Common Pitfalls

Summary

Related reading

System design interview problems on this topic

System Design Fundamentals

System Design practice on Codemia