Key Takeaways

• SQLAlchemy is both an ORM and a SQL toolkit — pick one, use both, whatever works for your situation.
• Learn Engine, Session, and Declarative Base first. Seriously, before you write anything.
• Runs on PostgreSQL, MySQL, SQLite, Oracle — you can swap databases without torching the whole codebase.

Introduction

Month two or three into a backend project is when it usually happens. Not a crash, not a catastrophic failure — just this slow creep of annoyance. The raw SQL strings you threw in early on? They start multiplying. You rename one column and suddenly something is broken in three places you forgot even existed. You spend twenty minutes ctrl+F-ing through files trying to find where you wrote that query six weeks ago.

It's not a crisis. It's just painful in a boring, grinding kind of way.

That's usually when people go looking for something better and end up at SQLAlchemy. It's been around since 2006. In Python terms that's ancient history — and yet it's still the default choice for serious backend work. Not because it's easy, it's not, but because it never forces your hand. You want ORM abstractions? Fine. You want to drop down and write actual SQL because the ORM is acting strange? Also fine, you can do that in the same codebase, same connection, no hacks needed. That kind of flexibility is rarer than it sounds.

Here's what you actually need to get started.

What Even Is SQLAlchemy?

Honestly, it's two tools that ship as one.

The first is the SQL Expression Language — SQL, but expressed as Python. You get precise query control without ever touching raw string concatenation. The second is the ORM, which sits on top and removes the database from your mental model almost entirely. Classes become tables. Objects become rows. You think in Python, SQLAlchemy handles the translation.

Most people start with the ORM, fall back to the Expression Language when things get weird, and bounce between both depending on what a given query needs. Some teams skip the ORM altogether and live in the Expression Language. Totally valid either way, nobody's going to argue with you.

Why Bother With It at All?

For a script or a tiny side project — genuinely, don't bother. Plain sqlite3 is sitting right there in the standard library and it'll do the job without any setup.

But once you've got real scale, real team size, a schema that keeps shifting — the "SQL strings everywhere" approach starts falling apart. Fast. That's where SQLAlchemy starts paying rent.

Installation

pip install sqlalchemy

Plus a driver for your database:

pip install psycopg2      # PostgreSQL
pip install pymysql       # MySQL

SQLite ships with Python so nothing extra needed there.

Four Things to Understand Before You Write a Single Line

Most SQLAlchemy confusion is from jumping into code examples cold, without any mental picture of the parts involved. Read this first and a lot of the friction disappears.

Engine — Your actual database connection. Manages the connection pool, sits under everything, you set it up once and mostly forget it's there. Nothing else functions without it.

Session — Where you do your work. Queries, inserts, updates — all go through a session. Key thing: changes are held in memory until you call commit(). Nothing hits the database for real until that point. This is intentional. Means you can back out of a half-finished operation if something breaks mid-way.

Declarative Base — A base class. Your model classes inherit from it. It's what lets SQLAlchemy know these Python classes are supposed to map to database tables. You make it once, inherit from it everywhere.

Model — A class that represents a table. One class, one table. One instance of that class, one row. That's the whole idea.

Building Something Step by Step

Step 1 — Connect to a Database

from sqlalchemy import create_engine

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

Production would look more like postgresql://user:pass@localhost/mydb — same pattern. SQLite is just the easiest starting point locally since there's nothing to install or configure.

Step 2 — Define a Model

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id   = Column(Integer, primary_key=True)
    name = Column(String)
    age  = Column(Integer)

__tablename__ is what the table gets called in the actual database. The attributes are columns. Read it a couple times and the pattern sticks pretty naturally.

Step 3 — Create the Tables

Base.metadata.create_all(engine)

Looks at everything hanging off Base, runs the CREATE TABLE statements for you. No hand-writing DDL.

Step 4 — Insert, Query, Update

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# Add a record
new_user = User(name="John Doe", age=30)
session.add(new_user)
session.commit()

# Fetch records
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

# Update something
user = session.query(User).filter_by(name="John Doe").first()
user.age = 31
session.commit()

Change something, commit. That's the loop. Forget the commit and the change silently disappears — you'll do this at least once and spend a baffled ten minutes before figuring out why. It happens to everyone, don't stress it.

Step 5 — Relationships

Here's where it gets genuinely useful. Define a relationship once and stop writing joins every time you need linked data:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = 'posts'

    id      = Column(Integer, primary_key=True)
    title   = Column(String)
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", back_populates="posts")

User.posts = relationship("Post", order_by=Post.id, back_populates="user")

Now user.posts gives you the list. post.user gives you the author. The join happens behind the scenes. You just access properties like it's a regular Python object, because it is.

SQLAlchemy vs the Other Options

Django ORM vs SQLAlchemy is a question that comes up constantly. Here's a straight answer:

If you're in a Django project, just use Django ORM — no argument there, it integrates perfectly and fighting it makes no sense. Peewee is a solid pick for smaller stuff, fast to get going. But outside Django, or whenever query complexity is a real concern, SQLAlchemy is the one that ages well. It's the tool you won't find yourself trying to route around two years in.

Where to Go From Here

The complexity reputation SQLAlchemy has is real but exaggerated. Most projects use a narrow slice of it — models, sessions, queries, relationships. That slice is well-built and once it clicks, it stays clicked.

Start with the ORM. Get something working. When the ORM starts making a particular query harder than it should be — and that will happen eventually — reach for the Expression Language and write the SQL directly. That's the actual workflow, not a theoretical fallback. Experienced people do exactly this, switching between layers depending on what the problem needs.

Runs on a local SQLite file on your machine. Runs on a Postgres cluster with serious traffic. The mental model you build in the first week transfers cleanly years later. That doesn't happen by accident, and it's one of the main reasons the library has lasted this long.

At Innostax, we work through this kind of thing regularly — picking the right database layer, structuring backends that stay maintainable, building data models that hold up under pressure. If you're working through the same questions for your own project, reach out at innostax.com/contact.

Originally published on the Innostax Engineering Blog.