SQLAlchemy is the most widely used ORM (Object Relational Mapper) in the Python ecosystem. If you work with relational databases, mastering SQLAlchemy is an essential skill that separates beginner developers from professionals. In this complete guide, you'll learn everything from fundamental concepts to advanced data modeling and query techniques.

According to the official SQLAlchemy documentation, the library provides a full set of high-performance database access patterns tailored for Python. Whether you're a web developer, data analyst, or software engineer, SQLAlchemy will transform how you interact with databases.

What Is an ORM and Why Use SQLAlchemy?

An ORM (Object Relational Mapper) is a technique that lets you work with relational databases using objects and methods from your programming language instead of writing raw SQL. SQLAlchemy takes this concept to a professional level by offering two distinct usage modes:

• SQLAlchemy Core: A focused abstraction built around the SQL expression language, ideal for those who prefer more control over queries
• SQLAlchemy ORM: A complete object-relational mapping layer that turns table rows into Python objects

The Real Python SQLAlchemy tutorial highlights that the library is widely adopted by companies like Reddit, Dropbox, and Yelp precisely for its flexibility and performance.

SQLAlchemy Advantages

• Database independence: The same code works with SQLite, PostgreSQL, MySQL, Oracle, and more
• Security: Built-in protection against SQL injection through parameterized queries
• Productivity: Less boilerplate code compared to raw SQL
• Migrations: Integration with Alembic for schema versioning
• Performance: Lazy and eager loading optimize queries

Installation and Setup

Installing SQLAlchemy is straightforward with pip:

pip install sqlalchemy

To install with support for a specific database:

# For PostgreSQL
pip install sqlalchemy psycopg2
For MySQL
pip install sqlalchemy pymysql
SQLite only (already included in Python)
pip install sqlalchemy

You can verify the installation on the official SQLAlchemy PyPI repository to ensure you have the latest version.

Connecting to the Database

The first step is to create a connection engine. SQLAlchemy uses a connection string called a database URL:

from sqlalchemy import create_engine
SQLite (local database)
engine = create_engine('sqlite:///my_database.db', echo=True)
PostgreSQL
engine = create_engine('postgresql://user:password@localhost:5432/mydb')
MySQL
engine = create_engine('mysql+pymysql://user:password@localhost:3306/mydb')

The echo=True parameter displays all executed SQL queries, essential for debugging and learning.

Defining Models with Declarative Base

With SQLAlchemy ORM, you define your tables as Python classes. The most modern approach uses the declarative system:

from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
Base = declarative_base()
class Product(Base):
tablename = 'products'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(200), nullable=False)
price = Column(Float, nullable=False)
category = Column(String(100))
created_at = Column(DateTime, default=datetime.utcnow)

def __repr__(self):
    return f"<Product(name='{self.name}', price={self.price})>"

Creating Tables

# Create all defined tables
Base.metadata.create_all(engine)

SQLAlchemy automatically generates CREATE TABLE statements based on your classes. Check the declarative tables documentation for more details.

Sessions: The Heart of Operations

The Session is the central mechanism for all database operations in SQLAlchemy ORM. It manages transactions and maintains an object cache:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

Always use sessions with context managers to ensure connections are properly closed:

with Session() as session:
    # your operations here
    pass

CRUD Operations with SQLAlchemy

CREATE — Inserting Data

with Session() as session:
    product = Product(
        name='Dell XPS Notebook',
        price=8999.90,
        category='Electronics'
    )
    session.add(product)
    session.commit()
    print(f"Product created with ID: {product.id}")

To insert multiple records:

products = [
    Product(name='Gaming Mouse', price=249.90, category='Peripherals'),
    Product(name='Mechanical Keyboard', price=599.90, category='Peripherals'),
    Product(name='27" 4K Monitor', price=3499.90, category='Monitors'),
]
session.add_all(products)
session.commit()

READ — Querying Data

SQLAlchemy offers a rich API for queries. Here are the most common patterns:

# Fetch all records
all_products = session.query(Product).all()
Fetch by ID
product = session.query(Product).get(1)
First record matching the filter
product = session.query(Product).filter_by(name='Gaming Mouse').first()
Filter with conditions
expensive_products = session.query(Product).filter(
Product.price >= 1000
).all()

UPDATE — Updating Data

with Session() as session:
    product = session.query(Product).get(1)
    product.price = 8499.90
    product.name = 'Dell XPS 15 Notebook'
    session.commit()

DELETE — Removing Data

with Session() as session:
    product = session.query(Product).get(2)
    session.delete(product)
    session.commit()

Table Relationships

One of SQLAlchemy's most powerful features is managing relationships between tables. Let's build a complete example with customers and orders.

One-to-Many

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Customer(Base):
tablename = 'customers'
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)
email = Column(String(200), unique=True, nullable=False)

orders = relationship('Order', back_populates='customer')
class Order(Base):
tablename = 'orders'
id = Column(Integer, primary_key=True)
date = Column(DateTime, default=datetime.utcnow)
total_amount = Column(Float)
customer_id = Column(Integer, ForeignKey('customers.id'))

customer = relationship('Customer', back_populates='orders')

Now you can navigate relationships naturally:

# Access orders from a customer
customer = session.query(Customer).get(1)
for order in customer.orders:
    print(f"Order #{order.id} - ${order.total_amount}")
Access customer from an order
order = session.query(Order).get(1)
print(f"Customer: {order.customer.name}")

Many-to-Many

For many-to-many relationships, use an association table:

from sqlalchemy import Table, Text
Association table
order_product_table = Table(
'order_products', Base.metadata,
Column('order_id', Integer, ForeignKey('orders.id')),
Column('product_id', Integer, ForeignKey('products.id')),
)
class Product(Base):
tablename = 'products'
id = Column(Integer, primary_key=True)
name = Column(String(200))
price = Column(Float)
description = Column(Text)

orders = relationship('Order', secondary=order_product_table, back_populates='products')
class Order(Base):
tablename = 'orders'
id = Column(Integer, primary_key=True)
date = Column(DateTime, default=datetime.utcnow)
total_amount = Column(Float)
customer_id = Column(Integer, ForeignKey('customers.id'))

customer = relationship('Customer', back_populates='orders')
products = relationship('Product', secondary=order_product_table, back_populates='orders')

For more on relationships, visit the official SQLAlchemy relationships documentation.

Advanced Queries

Complex Filters

from sqlalchemy import and_, or_, not_
Using AND, OR, NOT
products = session.query(Product).filter(
and(
Product.price >= 100,
Product.price <= 5000,
or(
Product.category == 'Electronics',
Product.category == 'Peripherals'
)
)
).all()

Explicit Joins

# JOIN between tables
results = session.query(Order, Customer).join(
    Customer, Order.customer_id == Customer.id
).all()
for order, customer in results:
print(f"Order {order.id} - {customer.name} - ${order.total_amount}")

Aggregations and Group By

from sqlalchemy import func
Total sales per customer
results = session.query(
Customer.name,
func.count(Order.id).label('total_orders'),
func.sum(Order.total_amount).label('total_value')
).join(Order).group_by(Customer.id).all()
for name, total, value in results:
print(f"{name}: {total} orders - ${value:.2f}")

SQLAlchemy supports all standard SQL functions through func. Check the SQLAlchemy SQL functions documentation for the complete list.

Subqueries

# Subquery: customers with orders above average
subquery = session.query(
    func.avg(Order.total_amount)
).scalar_subquery()
above_avg = session.query(Customer).join(Order).filter(
Order.total_amount > subquery
).distinct().all()

Lazy Loading vs Eager Loading

A crucial performance concept is understanding when and how related data is loaded:

• Lazy Loading (default): Relationships are loaded only when accessed. Can cause N+1 queries if used carelessly
• Eager Loading: Loads everything in a single query using joinedload or subqueryload

from sqlalchemy.orm import joinedload
Eager loading with JOIN
customers = session.query(Customer).options(
joinedload(Customer.orders)
).all()
Now all orders are loaded in the same query
for customer in customers:
print(f"{customer.name}: {len(customer.orders)} orders")

Migrations with Alembic

Alembic is the official migration tool for SQLAlchemy, created by the same author. It allows you to version your database schema:

pip install alembic
alembic init alembic

Configure the alembic.ini file with your database URL and start creating migrations:

# Create a new automatic migration
alembic revision --autogenerate -m "create_products_table"
Apply the migration
alembic upgrade head

For a complete guide, visit the official Alembic documentation.

SQLAlchemy Best Practices

1. Always Use Context Managers for Sessions

# Correct
with Session() as session:
    session.add(product)
    session.commit()
Incorrect (may leak connection)
session = Session()
session.add(product)
session.commit()

2. Indexes for Performance

class Product(Base):
    __tablename__ = 'products'
id = Column(Integer, primary_key=True)
name = Column(String(200), index=True)  # Index for name searches
category = Column(String(100), index=True)

3. Use Enum for Fixed Fields

from enum import Enum as PyEnum
from sqlalchemy import Enum as SAEnum
class OrderStatus(PyEnum):
PENDING = 'pending'
PAID = 'paid'
SHIPPED = 'shipped'
DELIVERED = 'delivered'
class Order(Base):
tablename = 'orders'
status = Column(SAEnum(OrderStatus), default=OrderStatus.PENDING)

4. Avoid N+1 Queries

The N+1 problem occurs when you load N records and then make N more queries for each relationship. Use joinedload or subqueryload to prevent this.

SQLAlchemy with FastAPI

Integrating SQLAlchemy with FastAPI is a powerful combination for building high-performance REST APIs. The recommended structure uses dependency injection to manage sessions:

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
app = FastAPI()
def get_db():
db = Session()
try:
yield db
finally:
db.close()
@app.get("/products/")
def list_products(db: Session = Depends(get_db)):
products = db.query(Product).all()
return products

To dive deeper into API creation, check our guide on FastAPI Python: Building RESTful APIs.

SQLAlchemy vs Other Tools

Async Support with SQLAlchemy

Starting from version 1.4, SQLAlchemy offers native async/await support, enabling non-blocking database operations:

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
import asyncio
async_engine = create_async_engine('sqlite+aiosqlite:///my_database.db')
AsyncSessionLocal = sessionmaker(
asyncengine, class=AsyncSession
)
async def get_products():
async with AsyncSessionLocal() as session:
result = await session.execute('SELECT * FROM products')
return result.fetchall()
asyncio.run(get_products())

Asynchronous programming is an advanced topic worth exploring. Check our guide on Python Async/Await: Asynchronous Programming.

Conclusion

SQLAlchemy is far more than a simple ORM — it's a complete ecosystem for working with relational databases in Python. From small SQLite projects to enterprise PostgreSQL systems, the library provides the tools needed to build robust, secure, and performant data layers.

What we covered in this guide:

• Installation and configuration with different databases
• Model definition using the declarative approach
• Complete CRUD operations
• One-to-many and many-to-many relationships
• Advanced queries with joins, subqueries, and aggregations
• Loading strategies (lazy vs eager)
• Migrations with Alembic
• Integration with FastAPI
• Async support

To continue your studies, I highly recommend reading the official SQLAlchemy 2.0 documentation, one of the best technical documentation sets in the Python ecosystem. Practice by creating models, experiment with different query types, and most importantly, build real projects.

SQLAlchemy also integrates seamlessly with tools like Pytest for automated testing and mypy for type checking, ensuring even more robust code.

Start using SQLAlchemy in your Python projects today and elevate your data layer to a professional level!