SQLAlchemy

SQLAlchemy is a popular Python library for working with relational databases. It provides tools for connecting Python applications to databases, executing SQL queries, managing transactions, and working with database data using Python objects.

SQLAlchemy supports both SQL expression language and Object-Relational Mapping (ORM). This allows developers to work with databases using either SQL-like expressions or Python classes and objects.

What Is SQLAlchemy?

SQLAlchemy is a database toolkit and Object-Relational Mapper (ORM) for Python.

It provides an abstraction layer between a Python application and a relational database. Instead of writing every database operation manually, developers can use SQLAlchemy's APIs to create queries, manage database connections, map tables to Python classes, and perform database operations.

SQLAlchemy can work with several relational databases, including:

• PostgreSQL
• MySQL
• SQLite
• Microsoft SQL Server
• Oracle

The database-specific communication is handled through database drivers and SQLAlchemy dialects.

Why Use SQLAlchemy?

SQLAlchemy provides several features that make database development easier and more maintainable.

Common benefits include:

• Connect Python applications to relational databases.
• Execute SQL queries from Python.
• Work with databases using Python objects.
• Define database tables using Python classes.
• Perform CRUD operations.
• Manage database transactions.
• Build complex queries programmatically.
• Reuse database models across an application.
• Work with relationships between tables.
• Support different relational database systems.

SQLAlchemy is commonly used in web applications, APIs, backend services, and other Python applications that require persistent data storage.

SQLAlchemy Core and ORM

SQLAlchemy provides two major ways to interact with databases:

1. SQLAlchemy Core
2. SQLAlchemy ORM

SQLAlchemy Core

SQLAlchemy Core provides lower-level database functionality and a SQL expression language.

It allows developers to construct SQL statements using Python objects rather than manually concatenating SQL strings.

For example, a query can be represented using SQLAlchemy expressions and then executed against a database connection.

Core is useful when you need detailed control over SQL statements and database operations.

SQLAlchemy ORM

SQLAlchemy ORM allows database tables to be represented as Python classes.

For example, a database table named users can be represented by a Python class called User.

Instead of directly working with rows and columns, application code can work with Python objects.

ORM is especially useful for applications with multiple related entities and business logic.

Installing SQLAlchemy

SQLAlchemy can be installed using pip.

pip install sqlalchemy

If your application uses a specific database, you may also need the appropriate database driver.

For example, SQLite is available through Python's standard library, while databases such as PostgreSQL and MySQL generally require their corresponding Python database drivers.

Creating a Database Engine

The SQLAlchemy engine is responsible for connecting an application to a database.

A simple SQLite engine can be created as follows:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///example.db")

Here, example.db is the SQLite database file.

For other database systems, the connection URL contains the required database server, database name, username, password, and driver information.

Defining a Model

With SQLAlchemy ORM, a database table can be represented using a Python class.

For example:

from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

The User class represents the users table, while the class attributes represent columns in that table.

Creating Database Tables

After defining the models, SQLAlchemy can create the corresponding tables.

Base.metadata.create_all(engine)

This creates the tables represented by the metadata if they do not already exist.

For production applications, database migration tools such as Alembic are commonly used instead of relying solely on create_all().

SQLAlchemy Sessions

A session is used by the ORM to manage interactions between Python objects and the database.

For example:

from sqlalchemy.orm import Session

with Session(engine) as session:
    user = User(name="John")
    session.add(user)
    session.commit()

The session tracks changes to ORM objects and coordinates database operations and transactions.

CRUD Operations with SQLAlchemy

CRUD stands for:

• Create
• Read
• Update
• Delete

These operations form the foundation of most database-driven applications.

Create

A new object can be added to the database using a session.

with Session(engine) as session:
    user = User(name="John")
    session.add(user)
    session.commit()

Read

SQLAlchemy provides APIs for querying database records.

from sqlalchemy import select

with Session(engine) as session:
    statement = select(User)
    users = session.scalars(statement).all()

Update

An ORM object can be modified and committed through the session.

with Session(engine) as session:
    user = session.get(User, 1)

    if user:
        user.name = "David"
        session.commit()

Delete

An object can be removed using session.delete().

with Session(engine) as session:
    user = session.get(User, 1)

    if user:
        session.delete(user)
        session.commit()

SQLAlchemy Relationships

Relational databases commonly contain relationships between tables.

For example, an application might contain:

• Users
• Orders
• Products

A user can have multiple orders, and an order can contain multiple products.

SQLAlchemy ORM provides relationship mappings such as:

• One-to-one
• One-to-many
• Many-to-one
• Many-to-many

Relationships allow related database records to be represented naturally using Python objects.

Transactions in SQLAlchemy

A database transaction groups multiple operations into a single unit of work.

If all operations succeed, the transaction can be committed. If something goes wrong, the transaction can be rolled back.

For example:

with Session(engine) as session:
    try:
        user = User(name="John")
        session.add(user)

        session.commit()
    except Exception:
        session.rollback()
        raise

Using transactions correctly is important for maintaining database consistency.

SQLAlchemy Querying

SQLAlchemy provides a powerful expression system for building database queries.

For example, records can be filtered using where():

from sqlalchemy import select

statement = select(User).where(User.name == "John")

The statement can then be executed using a session.

More complex queries can include:

• Filtering
• Sorting
• Pagination
• Aggregation
• Grouping
• Joins
• Subqueries
• Functions

SQLAlchemy Joins

Joins are used to retrieve related information from multiple tables.

SQLAlchemy supports common SQL join operations, including:

• Inner joins
• Left outer joins
• Explicit joins
• Relationship-based joins

For example:

statement = select(User).join(User.orders)

The exact query depends on how the models and relationships are defined.

SQLAlchemy and FastAPI

SQLAlchemy is frequently used with Python web frameworks such as FastAPI.

A typical application architecture may contain:

FastAPI
   ↓
API / Route
   ↓
Service Layer
   ↓
SQLAlchemy ORM
   ↓
Database

SQLAlchemy handles database interaction while FastAPI handles HTTP requests and responses.

A database session is commonly created for each request and then closed after the request has been processed.

SQLAlchemy and Async Programming

SQLAlchemy also provides support for asynchronous database operations.

The asynchronous API uses components such as:

• AsyncEngine
• AsyncSession

For example:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "sqlite+aiosqlite:///example.db"
)

Asynchronous SQLAlchemy can be useful when building applications that already use Python's async and await programming model.

SQLAlchemy Migrations

Changing database structures is a normal part of application development.

For example, you may need to:

• Add a column.
• Remove a column.
• Create a new table.
• Rename a column.
• Add an index.
• Modify a database constraint.

SQLAlchemy itself focuses on database interaction and ORM functionality. Alembic is commonly used alongside SQLAlchemy to manage database schema migrations.

A typical workflow is:

Change Model
     ↓
Create Migration
     ↓
Review Migration
     ↓
Run Migration
     ↓
Database Updated

SQLAlchemy Best Practices

When using SQLAlchemy in production applications, follow good database-development practices.

Use Parameterized Queries

Avoid constructing SQL statements by concatenating user-provided values. Use SQLAlchemy's expression APIs and parameters to safely pass values to queries.

Manage Sessions Carefully

Sessions should have a clearly defined lifecycle. In web applications, a common approach is to create a session for a request and close it when the request finishes.

Use Transactions

Group related database operations into appropriate transactions so that partial updates do not leave the database in an inconsistent state.

Avoid Unnecessary Queries

Pay attention to how relationships are loaded. Poor query design can result in many unnecessary database requests.

Use Migrations

Use a migration system such as Alembic when evolving the database schema in a real application.

Keep Database Logic Organized

For larger applications, separate database models, queries, business logic, and API code into appropriate layers.

SQLAlchemy Learning Path

A practical learning path can be followed in this order:

1. Understand relational databases and SQL.
2. Install SQLAlchemy.
3. Learn database engines and connection URLs.
4. Learn SQLAlchemy Core.
5. Learn SQLAlchemy ORM.
6. Create declarative models.
7. Learn sessions and transactions.
8. Practice CRUD operations.
9. Learn filtering and ordering.
10. Learn joins and relationships.
11. Learn aggregation and complex queries.
12. Learn asynchronous SQLAlchemy.
13. Learn Alembic migrations.
14. Integrate SQLAlchemy with FastAPI.
15. Build a complete database-driven Python application.

Conclusion

SQLAlchemy provides a powerful and flexible way to work with relational databases in Python. It supports both SQL expression-based development and ORM-based application development.

By learning engines, models, sessions, CRUD operations, queries, relationships, transactions, and migrations, you can build Python applications that interact with databases in a structured and maintainable way.

For beginners, it is useful to first understand basic SQL and relational database concepts before moving deeply into SQLAlchemy ORM. Once the fundamentals are clear, SQLAlchemy becomes much easier to understand and use effectively.