SQLAlchemy ORM: Architecture, Models, Sessions, and Production Best Practices


A database is more than a place to store application data. It is part of the architecture that determines how a system protects consistency, handles concurrent requests, evolves its schema, and performs as data grows. Python developers often use a relational database such as PostgreSQL, MySQL, or SQLite, but the way application code communicates with that database has a major effect on maintainability.
SQLAlchemy is a Python SQL toolkit and Object Relational Mapper (ORM) that provides both a high-level object-oriented interface and a powerful SQL expression system. It does not hide SQL or remove the need to understand relational database design. Instead, it gives developers a structured way to describe tables, relationships, queries, transactions, and connection management in Python while retaining control over the SQL that is executed.
This guide focuses on SQLAlchemy 2.0 style, which uses typed declarative mappings, the select() construct, and explicit session patterns. Older tutorials often show APIs such as declarative_base(), Column-based mappings, and session.query(). Those APIs remain available for compatibility, but new applications should generally learn the current 2.0 approach.
By the end, you will understand the major architectural layers, create a small data model, manage sessions and transactions safely, query related records, avoid common performance problems, introduce migrations, test database code, and decide when asynchronous database access is appropriate.
SQLAlchemy has two closely related parts. SQLAlchemy Core provides database connectivity, SQL expression construction, execution, and result handling. SQLAlchemy ORM builds on Core to map Python classes to relational tables and manage mapped objects.
The ORM is useful when application code naturally works with domain objects such as customers, invoices, products, and orders. A mapped Python object represents a row, and relationships can represent foreign-key associations. The ORM tracks changes to objects and coordinates persistence through a Session.
Core remains useful when a query is highly specialized, when working with tables that do not need object mappings, or when bulk operations are better expressed directly as SQL statements. You can use Core and ORM together rather than treating them as competing approaches.
An ORM is not a replacement for database fundamentals. Developers still need to understand primary keys, foreign keys, unique constraints, nullability, indexes, transactions, isolation, joins, and query plans. A concise Python expression can still generate an expensive SQL query. The database remains responsible for enforcing data integrity and executing the statements.
SQLAlchemy also does not automatically design a good architecture. A project can become difficult to maintain if every route constructs its own engine, sessions are shared globally, database queries are mixed into unrelated business logic, or models become containers for every application concern. Good results come from using the toolkit with clear boundaries and deliberate transaction ownership.
A typical SQLAlchemy application contains several layers that work together.
A database driver is the DBAPI implementation that communicates with a database server. Examples include psycopg for PostgreSQL, PyMySQL for MySQL, and SQLite's driver for local databases. The SQLAlchemy dialect adapts SQLAlchemy's operations to the target database's SQL syntax, data types, and driver behavior.
The database URL identifies the dialect, driver, host, database, and connection options. A PostgreSQL URL might use a form such as postgresql+psycopg://user:password@host:5432/appdb. Keep credentials outside source control and use environment variables or a secrets manager in deployed environments.
The Engine is the central entry point for database connectivity. It combines a dialect with a connection pool and provides connections when SQL needs to be executed. Applications normally create an Engine once per database configuration, rather than creating a new Engine for every request.
A connection pool reuses established connections, reducing the cost of repeatedly opening network connections. Pool size, overflow, timeout, and connection health settings should reflect the database's connection limits and the application's concurrency model. Increasing the pool size without understanding the database capacity can make an overloaded system less stable.
SQLAlchemy Core lets developers build SQL statements using Python expressions. A select statement can express filtering, joins, ordering, grouping, and subqueries without concatenating user input into raw SQL strings.
The expression system is valuable even when using the ORM. Modern ORM queries are commonly written with select() and executed through a Session. This shared foundation makes it possible to move between mapped objects and lower-level SQL operations when necessary.
The ORM mapping layer connects Python classes to database tables. Declarative mappings describe columns, constraints, and relationships in the class definition. SQLAlchemy uses this metadata to construct SQL and translate result rows into Python objects.
Relationships describe how mapped entities are connected. A one-to-many relationship might connect a customer to orders, while a many-to-many relationship might connect articles to tags through an association table. The foreign-key constraints in the database remain the source of relational integrity.
The Session is the interface through which ORM objects are loaded, changed, added, and deleted. It maintains an identity map so that a given database identity is represented by one mapped object within that Session. It also tracks changes and coordinates the unit of work: pending modifications are flushed to the database in an ordered way.
A Session is not a global cache and should not be treated as a thread-safe or task-safe shared object. It represents mutable state and a transaction context. A common production pattern is one Session per request or unit of work, with a clear point at which the transaction is committed or rolled back.
Install SQLAlchemy and the driver that matches your database. For a PostgreSQL application using psycopg 3, a typical installation is:
python -m pip install "sqlalchemy>=2.0,<3.0" "psycopg[binary]"
For SQLite, SQLAlchemy can use Python's built-in sqlite3 driver, so an additional driver package is not normally required. For MySQL, install and configure a compatible driver such as PyMySQL or mysqlclient.
Pin dependencies in your project's dependency file and use a virtual environment. The exact driver choice should be based on deployment support, operational requirements, and the database provider's recommendations. A driver installed locally but missing from the production dependency list will cause connection failures after deployment.
For a larger application, separate configuration from database construction. Read the database URL from an environment variable, validate that it exists in production, and avoid printing credentials in logs. A development SQLite URL and a production PostgreSQL URL can be different while the model and query code remains largely portable.
SQLAlchemy 2.0 encourages typed declarative mappings using DeclarativeBase, Mapped, and mapped_column. This approach makes the mapping explicit and works naturally with Python type annotations.
The following example defines a small customer and order domain. The models are intentionally compact, but they illustrate primary keys, foreign keys, nullability, defaults, and relationships.
from __future__ import annotations
from datetime import datetime
from sqlalchemy import ForeignKey, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(320), unique=True, index=True)
full_name: Mapped[str] = mapped_column(String(160))
created_at: Mapped[datetime] = mapped_column(
server_default=func.now()
)
orders: Mapped[list[Order]] = relationship(
back_populates="customer",
cascade="all, delete-orphan",
)
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
reference: Mapped[str] = mapped_column(String(40), unique=True)
customer_id: Mapped[int] = mapped_column(
ForeignKey("customers.id"),
index=True,
)
status: Mapped[str] = mapped_column(String(30), default="pending")
customer: Mapped[Customer] = relationship(back_populates="orders")
The Base class provides a shared metadata collection for mapped tables. Each model declares its table name, and mapped_column() describes the database column and its Python type. SQLAlchemy infers many column types from the Mapped annotation, although explicit types are useful when specifying lengths, precision, or database-specific behavior.
The Customer.orders and Order.customer attributes describe the object relationship. The foreign key is defined on the orders table, while back_populates connects the two sides of the relationship. The cascade option shown is a domain decision, not a universal default: delete-orphan means an order removed from a customer's collection is marked for deletion. Do not use cascading deletes unless that behavior matches the business rules and database design.
A unique constraint on email prevents duplicate customer email addresses at the database level. The index can help lookup performance, but indexes have storage and write costs. If a unique constraint already creates an index in the target database, adding a redundant index may be unnecessary. Review the generated schema for the database you actually use.
The server_default on created_at is evaluated by the database. By contrast, a Python-side default such as default="pending" is applied by SQLAlchemy when it constructs an insert. These are not identical: a server default can also apply to inserts made outside the ORM, provided the insert omits that column.
Use primary keys to identify rows, foreign keys to enforce valid references, unique constraints for values that must not be duplicated, and check constraints for simple database-enforced rules. For example, an inventory quantity may need to be non-negative. Business validation should still provide useful application errors, but the database constraint protects integrity when multiple application instances or external tools write to the same database.
Index columns that support common filters, joins, and ordering. Do not index every column automatically. A composite index can help queries that filter by a particular combination of columns, but column order matters and should reflect real query patterns. Use the database's query-plan tools to verify that an index improves the workload.
The Engine should normally be created during application startup or in a dedicated database module. The session factory is configured from that Engine and creates independent Session instances for individual units of work.
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = os.environ["DATABASE_URL"]
engine = create_engine(
DATABASE_URL,
pool_pre_ping=True,
pool_size=5,
max_overflow=10,
)
SessionLocal = sessionmaker(
bind=engine,
autoflush=True,
expire_on_commit=False,
)
The example uses a synchronous driver and synchronous Engine. pool_pre_ping checks a pooled connection before using it, which can help detect connections that the database or network has closed while idle. It adds a small check on checkout, so choose pool options based on the actual deployment environment.
The pool settings are illustrative, not universal production values. A small database plan may have a low connection limit, and multiple application workers each create their own pool. Calculate the potential total connections across workers, background jobs, migrations, and administrative clients before increasing pool capacity.
Avoid constructing an Engine inside a route handler. Doing so can create many pools and connections, consume database resources, and make lifecycle management difficult. Also avoid creating one Session at module import time and sharing it between requests.
For a small local experiment, SQLAlchemy can create tables from model metadata:
Base.metadata.create_all(engine)
This is convenient for learning and disposable test databases. It is not a substitute for schema migrations in a production application. create_all() creates missing tables but does not provide a versioned, reviewable plan for changing existing tables when a model evolves.
A Session tracks ORM objects and coordinates database work. It should have a clear owner and a limited lifetime. The context manager pattern ensures the Session is closed even if an exception occurs.
For a read-only operation, a Session can be scoped like this:
from sqlalchemy import select
with SessionLocal() as session:
customer = session.scalar(
select(Customer).where(Customer.email == "[email protected]")
)
For a unit of work that writes data, use an explicit transaction boundary:
with SessionLocal.begin() as session:
customer = Customer(
email="[email protected]",
full_name="Alex Morgan",
)
session.add(customer)
The sessionmaker.begin() context manager creates a Session, begins a transaction, commits when the block succeeds, rolls back if an exception escapes, and closes the Session. This keeps transaction ownership visible and reduces the chance of leaving a transaction open accidentally.
A lower-level pattern is also possible when an application needs more control:
with SessionLocal() as session:
try:
customer = Customer(
email="[email protected]",
full_name="Sam Lee",
)
session.add(customer)
session.commit()
except Exception:
session.rollback()
raise
After a flush failure, the Session must be rolled back before it can be used again. Re-raising the exception is important: silently swallowing a database error can make the application report success when the transaction did not complete.
A flush sends pending inserts, updates, and deletes to the database within the current transaction. It does not commit the transaction. SQLAlchemy may flush automatically before a query or before a commit, and developers can request a flush explicitly when they need generated identifiers or constraint checks to occur before the transaction ends.
A commit finalizes the current transaction. Once committed, the database changes become durable according to the database's transaction guarantees. A rollback abandons uncommitted changes. These operations should correspond to meaningful application units of work, such as creating an order and its line items together.
Do not commit after every individual field change or repository call. If a business operation spans several database writes, committing halfway through can leave partial state if a later step fails. Prefer one transaction around the complete operation. For work that spans multiple independent services, a database transaction alone cannot provide distributed atomicity; patterns such as an outbox or saga may be needed.
A Session is mutable and stateful. Do not share one Session among concurrent threads, and do not share an AsyncSession among concurrent asyncio tasks. The practical rule is Session per thread or unit of work, and AsyncSession per task. Each concurrent operation should own its own transaction context.
This matters in web servers, background workers, scheduled jobs, and applications using asyncio.gather(). A global Engine is generally appropriate; a global active Session is not. Pass a Session into the functions that need it, or provide it through a well-defined dependency boundary.
The modern ORM query style uses select() and executes the statement through a Session. This is the recommended style for new SQLAlchemy code.
from sqlalchemy import select
with SessionLocal() as session:
statement = (
select(Customer)
.where(Customer.full_name.ilike("%alex%"))
.order_by(Customer.created_at.desc())
)
customers = session.scalars(statement).all()
select(Customer) asks for mapped Customer objects. where() adds a condition, order_by() controls sorting, and scalars() extracts the first selected element from each row. The resulting list contains mapped objects.
For a single record, use scalar_one_or_none() when zero or one result is expected:
with SessionLocal() as session:
customer = session.scalar(
select(Customer).where(Customer.email == "[email protected]")
)
Use scalar_one() or one() when the application expects exactly one result and wants an exception if the query returns zero or multiple rows. Use first() only when the application intentionally wants the first row and has applied suitable ordering. Choosing the result method carefully makes assumptions explicit.
To select particular columns rather than full mapped objects:
with SessionLocal() as session:
rows = session.execute(
select(Customer.id, Customer.email)
.where(Customer.email.is_not(None))
).all()
The SQL expression API also supports joins, aggregates, subqueries, and database functions. You can use func.count() for counts, group_by() for aggregation, and join() to combine tables. When queries become complex, inspect the generated SQL and the database execution plan instead of assuming the ORM expression is efficient.
Build filters with SQLAlchemy expressions. Avoid constructing SQL by concatenating user-controlled strings. SQLAlchemy's expression system binds values as parameters, helping protect against SQL injection when used correctly.
For pagination, order results by a stable key and apply limit() and offset() for simple use cases. Large offsets can become expensive because the database may still need to scan or skip many rows. For large or frequently changing datasets, keyset pagination using the last seen indexed value is often more efficient and avoids some page-shifting behavior.
Always validate page sizes and filter inputs at the API boundary. Parameter binding prevents SQL injection, but it does not make an application-level filter sensible, authorize access to a record, or protect against a user requesting an unbounded result set.
Relationships make it possible to navigate from one mapped object to related objects. They also introduce a common performance trap: the N+1 query problem. An application may load a list of customers with one query and then trigger one additional query for each customer's orders when the relationship is accessed.
SQLAlchemy provides loader options to control how related data is retrieved. selectinload() is often a strong default for collections because it loads the parent rows and then retrieves related rows in a separate query using an IN condition.
from sqlalchemy import select
from sqlalchemy.orm import selectinload
with SessionLocal() as session:
statement = (
select(Customer)
.options(selectinload(Customer.orders))
.order_by(Customer.id)
)
customers = session.scalars(statement).all()
for customer in customers:
print(customer.email, len(customer.orders))
joinedload() uses a SQL join to load related data in the same query. It can be useful for many-to-one relationships or small related result sets. For collection relationships, joined eager loading can multiply rows in the result set; in those cases the ORM result may need unique() when consuming mapped entities. selectinload() is frequently easier to reason about for collections.
The best loader strategy depends on the relationship cardinality, result size, and query shape. Lazy loading can be appropriate when related data is rarely needed, but it can produce hidden queries during serialization or template rendering. In API applications, define which related data the response needs and load it deliberately before leaving the database layer.
Do not return ORM objects blindly from a web endpoint and assume every relationship can be accessed safely after the Session closes. Convert the required fields into response schemas while the necessary data is available, and make the loading strategy explicit. This also helps prevent accidental exposure of fields that should not appear in an API response.
FastAPI's dependency injection system can provide a Session to each request. The dependency should create a new Session, yield it, and close it after the request's database work is complete.
from collections.abc import Generator
from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session
app = FastAPI()
def get_db() -> Generator[Session, None, None]:
with SessionLocal() as session:
yield session
@app.get("/customers/{customer_id}")
def read_customer(
customer_id: int,
db: Session = Depends(get_db),
):
customer = db.get(Customer, customer_id)
if customer is None:
return {"detail": "Customer not found"}
return {
"id": customer.id,
"email": customer.email,
"full_name": customer.full_name,
}
This example illustrates session injection and response construction. In a complete API, return an appropriate HTTP 404 response when the record is missing, validate request and response data with Pydantic schemas, and define transaction ownership consistently. A route that creates or updates records should commit within a clearly defined unit of work and handle integrity errors appropriately.
For a larger codebase, keep route handlers focused on HTTP concerns. A service layer can coordinate business operations, while repositories or focused data-access functions can encapsulate reusable query logic. Avoid adding layers merely for ceremony: the goal is to make transaction boundaries, authorization, and domain rules easier to understand and test.
If you are building an API with FastAPI, the site's guides to API design fundamentals, Swagger and OpenAPI documentation, and FastAPI authentication with JWT and OAuth2 provide useful complementary context.
SQLAlchemy supports asynchronous database access through create_async_engine(), async_sessionmaker(), and AsyncSession. This can be useful in an asyncio application when the database driver supports asynchronous I/O and the application benefits from non-blocking database waits.
Install a compatible async driver. For PostgreSQL, one common option is asyncpg. The connection URL uses the corresponding SQLAlchemy dialect and driver.
python -m pip install "sqlalchemy>=2.0,<3.0" asyncpg
A basic asynchronous configuration looks like this:
from sqlalchemy.ext.asyncio import (
async_sessionmaker,
create_async_engine,
)
ASYNC_DATABASE_URL = "postgresql+asyncpg://user:password@localhost/appdb"
async_engine = create_async_engine(
ASYNC_DATABASE_URL,
pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(
async_engine,
expire_on_commit=False,
)
An asynchronous unit of work can then use an async context manager:
async def create_customer(email: str, full_name: str) -> int:
async with AsyncSessionLocal.begin() as session:
customer = Customer(email=email, full_name=full_name)
session.add(customer)
await session.flush()
return customer.id
The function flushes before returning so the database-generated primary key is available. The context manager commits when the block succeeds and rolls back when an exception escapes.
Async ORM code requires special attention to relationship loading. Traditional lazy loading can trigger implicit I/O when an attribute is accessed, which is not compatible with ordinary synchronous attribute access in many asyncio contexts. Prefer explicit eager loading, awaitable attribute patterns where appropriate, and response construction that does not unexpectedly trigger database access.
Do not share one AsyncSession across concurrent tasks. If several coroutines run through asyncio.gather(), each task should have its own AsyncSession and transaction. Async support is not automatically faster: it introduces additional complexity, and a synchronous application with moderate database load may be simpler and perfectly adequate. Choose async because the application's concurrency and I/O profile justify it, not because asynchronous code is assumed to be superior.
Dispose of the asynchronous Engine during application shutdown using await async_engine.dispose() so pooled connections are released cleanly. The same lifecycle principle applies to synchronous engines: application startup and shutdown should own the database resources.
A production database schema changes over time. New features add columns, constraints, indexes, and tables; existing fields may need to be renamed, split, or removed. A migration system records these changes as ordered, version-controlled operations.
Alembic is the migration tool commonly used with SQLAlchemy. It can compare model metadata with a database schema and generate candidate migration scripts. These autogenerated scripts must be reviewed: Alembic cannot always infer the developer's intention, especially for column renames, data transformations, or changes requiring staged deployment.
A typical workflow is:
Do not rely on create_all() to evolve production schemas. It does not replace versioned migrations, data backfills, or coordinated application releases. A migration that removes a column may break an older application version that is still running. For zero-downtime deployments, use an expand-and-contract approach: add compatible schema first, deploy code that uses it, migrate data if needed, and remove obsolete schema only after old code no longer depends on it.
Migration scripts should be committed with the application code. Keep production migration execution separate from ordinary web worker startup where possible, so multiple workers do not race to alter the same schema. Back up important data and define a recovery plan before applying destructive changes.
SQLAlchemy can support applications ranging from small services to substantial production systems, but performance depends on database design, query shape, connection management, and workload characteristics.
Enable SQL logging in development to understand which statements the ORM emits. For targeted profiling, use SQLAlchemy engine events or application instrumentation to record query durations and identify repeated statements. Avoid logging sensitive parameter values in production.
Use the database's own query-plan tools to inspect slow statements. Look for full table scans, inefficient joins, missing indexes, large sorts, and unexpectedly high row counts. ORM-level timing alone cannot explain why a database query is slow.
Loading full objects with many columns and relationships can be wasteful when an endpoint needs only a small subset. Select specific columns, use load_only() where appropriate, and avoid loading large collections unless the response requires them. Apply pagination and enforce reasonable result limits.
A pool is a finite resource. Long-running transactions hold connections and can reduce the number available to other requests. Keep transactions short, avoid waiting on external network calls while a transaction is open, and close Sessions promptly. If an application uses multiple worker processes, account for each process's pool when estimating total database connections.
For PostgreSQL deployments with many application connections, a pooler such as PgBouncer may be useful. It introduces its own configuration and transaction-pooling considerations, so verify compatibility with the application's use of prepared statements and connection-level state.
For large imports or updates, ORM object-by-object persistence can create substantial overhead. SQLAlchemy supports bulk-oriented insert and update patterns through Core and ORM-enabled DML statements. Choose the approach based on whether the operation needs ORM identity tracking, relationship cascades, or per-object events. Faster bulk operations may bypass some ORM behaviors, so correctness must be tested.
Caching can reduce repeated database reads, but it introduces invalidation and consistency problems. Start with query optimization and appropriate indexes. Add application caching for data that is read frequently and changes predictably, and define how updates invalidate or refresh cached values. A cache should not become the only place where critical business data is stored.
Read replicas can help read-heavy workloads, but replication lag means a recent write may not be visible immediately on a replica. Route consistency-sensitive reads to the primary when necessary. Retry only operations that are safe to retry, and distinguish transient connection failures from integrity errors or invalid data. Retrying a non-idempotent operation without an idempotency strategy can create duplicate records.
SQLAlchemy's bound parameters help protect values in SQL expressions, but application security still depends on how queries and permissions are designed.
Never build SQL statements by concatenating untrusted values. When dynamic identifiers such as table names or sort directions are required, use a strict allowlist because identifiers generally cannot be bound in the same way as ordinary values. Validate inputs at the API boundary and enforce authorization in the application, not merely by hiding database IDs.
Use least-privilege database credentials. The application account should have only the permissions it needs, and migration credentials can be separated from normal runtime credentials. Keep secrets out of Git, container images, logs, and error responses. Use encrypted connections when traffic crosses untrusted networks, and configure backups and restore testing for important databases.
Enforce important invariants in the database as well as the application. Unique constraints, foreign keys, check constraints, and not-null constraints protect data when multiple application instances, administrative scripts, or external services write to the same database. Catch expected integrity errors and translate them into safe, useful application responses without exposing raw database details.
Database code should be tested against the behaviors it promises. Unit tests can cover business rules without a real database by injecting a repository or session abstraction, but integration tests are needed to verify actual SQL, constraints, transactions, and relationship behavior.
SQLite in-memory databases can be useful for fast tests, but they are not a perfect substitute for PostgreSQL or MySQL. Differences in SQL syntax, data types, locking, JSON behavior, constraints, and transaction semantics can hide production bugs. If the application runs on PostgreSQL, include integration tests against PostgreSQL, often through a disposable container or dedicated test database.
A test fixture should create a database engine, prepare the schema, provide a Session for each test, and reliably roll back or clean up. Be careful with in-memory SQLite connection behavior when tests use multiple threads or connections. For tests involving migrations, test the migration path itself rather than creating the latest schema directly from model metadata.
Test important failure cases: duplicate unique values, missing foreign keys, transaction rollback, concurrent updates, pagination boundaries, and queries that load relationships. Performance-sensitive endpoints should have tests or profiling checks that detect accidental N+1 query regressions.
A global Session can mix identity-map state and transaction work across requests. It is also unsafe for concurrent use. Create a new Session for each request or unit of work, and close it reliably.
Committing after every small write can split one business operation into partially durable steps. Define the transaction around the full operation and commit only when the work is complete.
Flush sends pending statements but does not make the transaction durable. If a later operation fails, a rollback can still undo the flushed work. Understand the distinction when handling generated IDs and database constraints.
Older examples often use session.query(). SQLAlchemy 2.0 style uses select() and Session.execute() or Session.scalars(). The newer style is consistent across Core and ORM and is the recommended foundation for new code.
Accessing relationships in a loop can silently issue many additional queries. Use selectinload() or joinedload() intentionally, inspect emitted SQL, and test response paths with realistic data.
create_all() can create missing tables, but it does not safely manage the evolution of existing production schemas. Use Alembic revisions and review every migration.
Async database access helps when the application has significant concurrent I/O and uses a compatible async driver. It does not make an expensive query cheaper or eliminate database bottlenecks. Keep synchronous and asynchronous patterns consistent within each code path.
A broad exception handler that logs and continues can leave a Session in a failed transaction state. Roll back after failures, re-raise unexpected exceptions, and translate only known errors into application-level responses.
For a small service, a single database module, declarative models, a session factory, and a few focused query functions may be enough. As the domain grows, separate HTTP handlers from business operations and database access so each part has a clear responsibility.
A practical layered structure might look like this:
app/
api/
routes/
services/
repositories/
models/
schemas/
db/
base.py
session.py
main.py
alembic/
versions/
tests/
The API layer handles HTTP requests, authentication dependencies, validation, and response codes. The service layer coordinates business operations and transaction boundaries. Repository functions encapsulate database queries when that abstraction improves reuse or clarity. Models describe persistence, schemas define external data contracts, and the db package owns Engine and Session configuration.
Do not force every project into a repository pattern or create a class for every query. A direct select() in a small service can be clearer than several layers of indirection. The right architecture is the simplest one that keeps responsibilities understandable, transactions safe, and tests practical.
For a complete backend workflow, combine these database practices with thoughtful API design fundamentals, clear OpenAPI documentation standards, and reliable Docker deployment practices for FastAPI.
The best way to learn SQLAlchemy is to build a small application and expand it deliberately.
The official SQLAlchemy 2.0 ORM documentation is the primary reference for mappings, relationships, querying, and sessions. The SQLAlchemy Unified Tutorial introduces Core and ORM concepts step by step. For schema evolution, consult the Alembic documentation.
SQLAlchemy is much more than a convenient way to turn database rows into Python objects. Its Engine, dialects, connection pool, SQL Expression Language, ORM mappings, identity map, and unit-of-work system provide a flexible foundation for database-driven Python applications.
The most reliable applications use SQLAlchemy deliberately: create the Engine once, scope Sessions to individual units of work, define explicit transaction boundaries, use the 2.0 select() API, load relationships intentionally, manage schema changes with Alembic, and test against the production database engine. Security and performance still depend on sound relational design, measured query behavior, and clear application boundaries.
Start with a small synchronous application, learn how SQL is generated, and keep the architecture simple. As the workload and domain grow, introduce migrations, testing, profiling, async access, and additional architectural layers only when they solve a demonstrated problem.

A production-style FastAPI REST API for hospital management featuring JWT authentication, role-based access control, PostgreSQL, Docker, and secure healthcare workflows.
Read
Learn how to manage SQLAlchemy database schema changes with Alembic, from setup and autogeneration to data migrations, testing, deployment, and safe rollbacks.
Read
Learn how Redis improves backend performance with cache-aside patterns, TTLs, data structures, rate limiting, background processing, and production-ready reliability, security, and monitoring practices.
Read