Python Databases | SQLite, PostgreSQL, and ORMs Explained

Introduction

A database is the core technology for storing and managing data safely. Anyone can write INSERT INTO users ... after a few minutes of tutorial-following, but knowing which database to reach for, how to talk to it safely, and why a query that works fine in development falls over in production is what separates a script from an application. This post continues the Python series by covering the two database engines you will meet constantly as a Python developer — SQLite and PostgreSQL — plus the SQLAlchemy ORM that sits between your Python objects and the SQL that actually runs.

The code samples below are intentionally close to what you would copy into a real project, but the value of this post is in the reasoning between the code blocks: when SQLite is the right choice rather than a shortcut, why raw SQL and ORM code each have failure modes the other doesn’t, and what tends to break once your database has more than one user.

---

SQLite vs. PostgreSQL: a design decision, not a detail

Two very different engines that share a SQL dialect

SQLite and PostgreSQL both speak SQL and both have first-class Python support, so it is tempting to treat the choice as interchangeable — “just swap the connection string later.” In practice they solve different problems, and picking the wrong one shows up as a production incident, not a compile error.

SQLite is embedded, not networked. There is no server process: sqlite3.connect('mydb.db') opens a file on disk, and the SQLite library links directly into your Python process. That has real consequences:

• Single-writer concurrency. SQLite allows many concurrent readers, but only one writer can hold the database at a time. In the legacy rollback-journal mode, a writer blocks all readers too; in the more common WAL (write-ahead log) mode, readers can proceed concurrently with a writer, but writers still serialize against each other. For a CLI tool, a desktop app, or a low-traffic prototype, this is invisible. For a web API handling concurrent POST requests, it becomes sqlite3.OperationalError: database is locked.
• No network round trip. Because there’s no server, a query executes with essentially zero connection overhead. This makes SQLite an excellent choice for embedded devices, test suites (an in-memory :memory: database resets instantly between tests), and command-line tools that need to persist state without asking the user to install and run a database server.
• Feature ceiling. SQLite has a limited type system (it uses “type affinity” rather than strict column types), no built-in user/permission system, and no native support for things like JSONB indexing, full-text search extensions comparable to PostgreSQL’s, or logical replication.

PostgreSQL is client-server. Your Python process connects over a TCP socket (or Unix socket) to a separate postgres server process that can serve many clients simultaneously. This buys you:

• Real concurrent writers, coordinated through PostgreSQL’s MVCC (multi-version concurrency control) engine rather than a single file lock.
• A much larger feature set: rich data types (JSONB, arrays, ranges), full-text search, partial and expression indexes, row-level security, extensions like PostGIS, and mature replication for high availability.
• A cost: you now have infrastructure to run, back up, patch, and monitor, and every query pays a network round trip.

A practical rule of thumb: reach for SQLite when your application is the only writer (a desktop app, a CLI tool, a single-process background job, most test suites) or when you explicitly want a zero-ops embedded store. Reach for PostgreSQL as soon as multiple processes or multiple users need to write concurrently — which describes almost every web application running in production with more than a handful of users. Many teams prototype against SQLite and switch the connection string to PostgreSQL before shipping; that works only if you avoid SQLite-specific SQL and test against PostgreSQL before launch, since subtle differences (case-sensitivity of LIKE, AUTOINCREMENT vs. SERIAL, transaction isolation defaults) can bite you at the worst possible time.

Using SQLite

import sqlite3
# Connect
conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()
# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER
    )
''')
# Insert row
cursor.execute(
    'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
    ('Alice', '[email protected]', 25)
)
conn.commit()
# Query
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
for user in users:
    print(user)
# Close
conn.close()

A few details here matter more than they look. First, notice the ? placeholders in the INSERT statement rather than an f-string like f"INSERT INTO users VALUES ('{name}')". This is parameterized SQL, and it is not a style preference — it is the primary defense against SQL injection. When you interpolate user-supplied strings directly into a query, a value like '); DROP TABLE users; -- becomes executable SQL instead of inert data. The DB-API driver sends the query text and the parameters separately to the database engine, so the parameter is always treated as a literal value, never as code. This applies identically to psycopg2/psycopg for PostgreSQL and to SQLAlchemy’s text() queries — any time you build SQL from user input, use placeholders, never string formatting.

Second, sqlite3 does not autocommit by default for data-modifying statements (in the classic sqlite3 module behavior) — you must call conn.commit() explicitly, or your INSERT is rolled back (or simply lost) when the connection closes. Forgetting this is one of the most common “my data isn’t saving” bugs beginners hit. Third, always close the connection (or use it as a context manager with with sqlite3.connect(...) as conn:) — leaving connections open is how you eventually run into “too many open files” errors in long-running processes, and in SQLite specifically it can also prolong lock contention.

---

Raw SQL vs. ORM: a trade-off you will relitigate constantly

Before going further into SQLAlchemy, it’s worth being explicit about why an ORM (Object-Relational Mapper) exists and where it stops helping.

The case for raw SQL. SQL is a mature, declarative language that databases are extremely good at optimizing. For a complex reporting query — multiple joins, window functions, GROUP BY with HAVING — writing it directly in SQL is often more readable than the equivalent chain of ORM method calls, and you can hand the exact query to a DBA or paste it into EXPLAIN ANALYZE without translation. Raw SQL also avoids the “leaky abstraction” problem where you write a seemingly simple ORM query and only discover at runtime, by inspecting the query log, that it generated five joins or a Cartesian product you didn’t intend.

The case for an ORM. An ORM lets you describe your schema once as Python classes and get: automatic parameterization (so the injection risk from the previous section is handled for you by default), portability across database backends (the same model code runs against SQLite in tests and PostgreSQL in production, modulo dialect-specific features), and less repetitive boilerplate for standard CRUD operations. It also integrates naturally with Python’s type system and IDE autocompletion, which raw SQL strings cannot offer.

The N+1 query problem is the ORM’s signature footgun. It happens when you fetch a list of parent rows, then access a related attribute on each one in a loop, and the ORM silently issues one additional query per row to fetch that relationship:

# This looks like one query. It's actually 1 + N queries.
users = session.query(User).all()          # 1 query
for user in users:
    print(user.name, len(user.posts))       # N additional queries — one per user!

With 3 users this is invisible. With 3,000 users, it’s 3,001 round trips to the database and a page load that times out. The fix — shown later in this post — is to tell the ORM up front to fetch the related rows in the same query (joinedload) or in one batched follow-up query (selectinload), instead of lazily fetching them one at a time. This is exactly the kind of bug that raw SQL cannot have (because you’d write the join yourself and see it in the query text), which is why experienced teams often reach for raw SQL or a query builder for read-heavy, performance-sensitive endpoints while keeping the ORM for straightforward CRUD.

In short: neither approach is strictly better. Prefer the ORM for typical CRUD and for schema consistency across a codebase; drop to raw SQL (SQLAlchemy still lets you run text() queries inside the same session) when a query’s shape is genuinely relational-reporting in nature, or when you need to verify exactly what SQL is being sent.

---

SQLAlchemy ORM

Install and setup

pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Engine
engine = create_engine('sqlite:///mydb.db')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

The engine object is not a live connection — it’s a factory that knows how to connect (the URL tells it which driver and database to use) and it manages a connection pool internally, which matters a great deal once you move to PostgreSQL (more on that below). Base is the declarative base class that all your model classes will inherit from; SQLAlchemy uses it to collect metadata about every table you define so it can generate CREATE TABLE statements or compare against migrations. Session is a factory for session objects — a session is the unit-of-work object that tracks changes to Python objects and translates them into SQL when you call commit().

One subtlety worth flagging for anyone moving this code into a real web app: a single global session object like the one above is not thread-safe to share across concurrent requests. Flask-SQLAlchemy and similar integrations solve this with a scoped_session, which gives each thread (or, more precisely, each request context) its own session transparently. If you’re wiring SQLAlchemy into a web framework yourself rather than using an integration, look up scoped_session before you ship — reusing one session across concurrent requests is a classic source of intermittent, hard-to-reproduce data corruption bugs.

Defining models

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100), unique=True)
    age = Column(Integer)
    
    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.email}')>"
# Create tables
Base.metadata.create_all(engine)

Each Column declaration doubles as documentation and as an enforced database constraint — nullable=False becomes a NOT NULL constraint, unique=True becomes a UNIQUE index, and String(100) becomes VARCHAR(100). Defining a __repr__ method is a small habit worth adopting on every model: when you’re debugging a query in a REPL or a log line, <User(name='Alice', email='[email protected]')> is far more useful than the default <__main__.User object at 0x7f...>. Base.metadata.create_all(engine) is convenient for prototypes and tests because it inspects your model classes and issues the necessary CREATE TABLE statements, but it only creates missing tables — it does not alter existing ones. The moment you need to add a column to a table that already has production data in it, create_all cannot help you; that’s what the migrations section near the end of this post addresses.

---

CRUD operations

Create

# Single row
new_user = User(name='Alice', email='[email protected]', age=25)
session.add(new_user)
session.commit()
# Multiple rows
users = [
    User(name='Bob', email='[email protected]', age=30),
    User(name='Carol', email='[email protected]', age=28)
]
session.add_all(users)
session.commit()

session.add() does not talk to the database immediately — it stages the object in the session’s identity map. SQLAlchemy uses a unit-of-work pattern: it accumulates pending inserts, updates, and deletes, then flushes them as SQL when you call commit() (or earlier, if autoflush triggers before a query). This batching is why add_all() followed by one commit() is more efficient than calling add() and commit() separately for each row — the latter forces a distinct transaction (and, on PostgreSQL, a distinct round trip and fsync) per row.

Read

# All rows
all_users = session.query(User).all()
for user in all_users:
    print(user.name, user.email)
# Filter
young_users = session.query(User).filter(User.age < 30).all()
# One row
user = session.query(User).filter(User.email == '[email protected]').first()
print(user.name)
# Count
count = session.query(User).count()
print(f"Total users: {count}")

filter() builds up a SQL WHERE clause lazily — nothing executes until you call a terminal method like .all(), .first(), or .count(). This laziness is convenient (you can compose filters conditionally before deciding how to execute the query) but it’s also a common source of confusion: printing a query object itself does not run it, and calling .count() issues a separate SELECT COUNT(*) rather than counting an already-fetched list, so don’t call both .all() and .count() on the same query when only one is needed.

Update

# Update via object
user = session.query(User).filter(User.name == 'Alice').first()
user.age = 26
session.commit()
# Bulk update
session.query(User).filter(User.name == 'Alice').update({'age': 27})
session.commit()

These two update styles are not interchangeable, and the difference matters. The “update via object” form loads the row into Python, mutates the attribute, and lets SQLAlchemy’s change tracking generate an UPDATE for that specific object when you commit — this triggers any ORM-level events, validators, or relationship cascades you’ve configured. The “bulk update” form (.update({...})) compiles directly to a single UPDATE ... WHERE ... statement without loading matching rows into Python objects at all. That makes it far more efficient for updating many rows at once, but it bypasses ORM-level hooks (before_update events, hybrid property setters, cascades) because no Python objects are ever instantiated. Use the bulk form for genuinely bulk operations; use the object form when per-row business logic needs to run.

Delete

# Delete by object
user = session.query(User).filter(User.name == 'Alice').first()
session.delete(user)
session.commit()
# Bulk delete
session.query(User).filter(User.age < 20).delete()
session.commit()

The same distinction applies to deletes: session.delete(obj) respects configured cascades (e.g., deleting a User can be set up to delete their Post rows too), while a bulk .delete() issues a raw DELETE FROM ... WHERE ... and will leave orphaned rows unless the database itself enforces ON DELETE CASCADE at the foreign-key level. If you rely on bulk deletes for cleanup jobs, make sure the cascade behavior you expect is defined at the schema level, not only in your Python model.

---

Transactions and isolation

Every session.commit() you’ve seen so far is implicitly wrapping your changes in a transaction — a set of operations that either all succeed together or all fail together (the “atomicity” in ACID). This matters as soon as an operation involves more than one write that must stay consistent, such as debiting one account and crediting another.

# Transactions
try:
    session.add(user)
    session.commit()
except Exception as e:
    session.rollback()
    raise

If commit() raises (a constraint violation, a lost connection, a deadlock), the session is left in a state where you must call rollback() before it can be used again — otherwise every subsequent operation on that session will fail with an error about a pending rollback. Wrapping writes in try/except/rollback() like this, or using SQLAlchemy’s with session.begin(): context manager which does it automatically, is not optional defensive style — skip it and a single failed write can leave your application unable to make any further queries on that session until it’s discarded.

Isolation level is the other half of transaction correctness: it controls what a transaction can see of other transactions’ uncommitted or concurrently-committed changes. PostgreSQL defaults to READ COMMITTED — a query sees data committed before that specific statement began, so two statements in the same transaction can see different snapshots if another transaction commits in between. SQLite, in contrast, effectively serializes writers and gives each transaction a consistent snapshot for its duration. If you migrate from SQLite to PostgreSQL, don’t assume all your transaction-dependent logic behaves identically — code that “happened to work” because SQLite serializes everything can expose race conditions under PostgreSQL’s more permissive default isolation level. For financial or inventory-style logic where a stale read is unacceptable, look at SELECT ... FOR UPDATE (row locking) or explicitly requesting SERIALIZABLE isolation, and be ready to retry on serialization failures.

---

Relationships

One-to-many

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    posts = relationship('Post', back_populates='author')
class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship('User', back_populates='posts')
# Usage
user = User(name='Alice')
post1 = Post(title='First post', content='Body', author=user)
post2 = Post(title='Second', content='Body2', author=user)
session.add_all([user, post1, post2])
session.commit()
# Navigate
user = session.query(User).first()
for post in user.posts:
    print(post.title)

The ForeignKey('users.id') column is what the database enforces — it guarantees user_id always points to a real row in users (or NULL). The relationship() calls on both sides are a purely Python-level convenience layered on top of that foreign key: they let you write user.posts or post.author instead of writing the join yourself every time. back_populates keeps both sides in sync in memory — setting post1.author = user automatically appends post1 to user.posts without an extra query, which is why the example above can build the objects and add them to the session without manually setting user_id.

This is also exactly where the N+1 problem from section 2 shows up in practice. The final loop, for post in user.posts, uses SQLAlchemy’s default lazy loading — accessing .posts for the first time issues a fresh SELECT * FROM posts WHERE user_id = ? at that moment. Do this inside an outer loop over many users and you get one query per user, as discussed earlier. There isn’t a single “correct” fix — it depends on access pattern — which is why SQLAlchemy exposes multiple loading strategies rather than one default that tries to guess.

---

Eager loading and connection pooling

Avoiding N+1 with eager loading

# Avoid N+1 queries
from sqlalchemy.orm import joinedload
users = session.query(User).options(
    joinedload(User.posts)
).all()

joinedload tells SQLAlchemy to fetch User and its related Post rows in a single query using a SQL LEFT OUTER JOIN, so user.posts is already populated in memory by the time you iterate — no additional query fires per user. This is ideal for one-to-one or many-to-one relationships, or one-to-many where the “many” side is small, because the join keeps everything in one round trip. For a one-to-many relationship where each parent has many children (say, a user with thousands of posts), a join duplicates the parent row once per child, which can be wasteful; selectinload(User.posts) is usually the better choice there — it issues exactly two queries total (one for all matching users, one for all their posts via WHERE user_id IN (...)), avoiding both the N+1 problem and the row-duplication cost of a join. Knowing which of these two to reach for, rather than always defaulting to lazy loading, is one of the highest-leverage things you can learn about SQLAlchemy performance.

Why connection pooling matters — and when it doesn’t

For SQLite, “connection pooling” is largely irrelevant: there’s no server to negotiate a TCP handshake with, so opening a connection is cheap. For PostgreSQL, every new connection means the Postgres server forks or allocates a new backend process, negotiates authentication, and sets up session state — commonly tens of milliseconds, and PostgreSQL also caps the total number of concurrent connections it will accept (max_connections, often 100 by default). A naive web app that opens a fresh connection per request will exhaust that limit under real traffic and start rejecting connections outright.

This is exactly what create_engine()’s connection pool solves: it keeps a set of already-open connections around and hands them out to whichever part of your code needs one, returning them to the pool instead of closing them when a request finishes. The relevant knobs are pool_size (how many connections to keep open) and max_overflow (how many extra, temporary connections to allow under a traffic spike beyond pool_size):

engine = create_engine(
    'postgresql://user:pass@localhost/mydb',
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True,  # check connection liveness before use
)

pool_pre_ping=True is worth calling out specifically: without it, a connection that has silently gone stale (the database restarted, a load balancer idle-timed it out) will fail with a confusing error the next time your code tries to use it. Enabling it adds a lightweight liveness check before each checkout, trading a small amount of latency for far fewer “connection already closed” errors in production. If you’re deploying to a platform with a hard connection cap (many managed Postgres tiers cap total connections aggressively), consider an external pooler such as PgBouncer in front of Postgres as well — SQLAlchemy’s pool manages connections within one process, but if you run many worker processes (Gunicorn workers, serverless invocations), each process gets its own pool, and the sum across all of them can still exceed what the database allows.

---

Practical example: Flask + SQLAlchemy API

from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
db = SQLAlchemy(app)
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text)
    
    def to_dict(self):
        return {
            'id': self.id,
            'title': self.title,
            'content': self.content
        }
# Create tables
with app.app_context():
    db.create_all()
@app.route('/api/posts', methods=['GET'])
def get_posts():
    posts = Post.query.all()
    return jsonify([post.to_dict() for post in posts])
@app.route('/api/posts', methods=['POST'])
def create_post():
    data = request.get_json()
    post = Post(title=data['title'], content=data['content'])
    db.session.add(post)
    db.session.commit()
    return jsonify(post.to_dict()), 201
if __name__ == '__main__':
    app.run(debug=True)

Flask-SQLAlchemy quietly solves the thread-safety concern raised in section 3: db.session here is a scoped_session bound to Flask’s request context, so each incoming request gets a session that is isolated from every other concurrent request, and Flask-SQLAlchemy tears it down (returning its connection to the pool) automatically when the request finishes — you don’t need to call session.close() yourself in each view function. The with app.app_context(): block around db.create_all() exists because SQLAlchemy’s engine and metadata are registered per-application, and create_all() needs that application context to know which database to talk to; this pattern trips up newcomers who call db.create_all() at module import time and get a RuntimeError: No application found.

One thing worth noticing about create_post(): it reads data['title'] directly, which raises an unhandled KeyError (returning a 500 error to the client) if title is missing from the request body. For a toy example this is fine; in production code you’d validate the incoming payload first — with data.get('title') plus an explicit 400 response, or with a schema validation library — rather than let a malformed request produce a stack trace.

flowchart LR
    A["Client request<br/>POST /api/posts"] --> B["Flask view function"]
    B --> C["db.session<br/>(scoped per request)"]
    C --> D{"Connection pool"}
    D -->|"checkout"| E["PostgreSQL / SQLite"]
    E -->|"commit"| C
    C -->|"connection returned"| D
    B --> F["JSON response"]

The diagram above summarizes the request lifecycle described in this section: each request gets its own logical session, but that session borrows a physical connection from the shared pool only for the duration of the request and returns it immediately afterward — which is precisely why pool sizing (section 7) matters once traffic is concurrent rather than sequential.

---

Schema migrations: the gotcha create_all() can’t fix

Base.metadata.create_all(engine) is fine for a brand-new database, but it never issues ALTER TABLE. The moment your schema needs to change on a database that already has rows in it — adding a column, renaming one, changing a type, adding an index — you need a migration tool. For SQLAlchemy, that’s almost always Alembic.

pip install alembic
alembic init migrations
# Edit migrations/env.py to point at your Base.metadata
alembic revision --autogenerate -m "add age column to users"
alembic upgrade head

Alembic tracks schema changes as an ordered chain of versioned Python scripts, each with an upgrade() and downgrade() function, so the same sequence of changes can be replayed identically in development, staging, and production. --autogenerate compares your current models against the live database schema and drafts a migration script for you — but it is a draft, not a guarantee, and several gotchas catch teams that trust it blindly:

• Renames look like drop-and-add. Alembic’s autogenerate diff has no way to know that renaming Column('username') to Column('handle') was a rename rather than “delete one column, add an unrelated one.” Left as generated, that migration will silently drop the column and lose every existing value in it. Always review autogenerated migrations for renames and rewrite them as explicit op.alter_column(..., new_column_name=...) calls.
• SQLite’s ALTER TABLE is limited. SQLite historically could not drop columns, change column types, or add certain constraints via ALTER TABLE at all; Alembic works around this for SQLite by rebuilding the table (batch_alter_table), copying data into a new table, and swapping it in. This works, but it’s slower and needs to be wrapped explicitly with with op.batch_alter_table(...) — a migration that autogenerates cleanly against PostgreSQL may need adjustment to run against SQLite.
• Ordering across deploys. In a rolling deploy where old and new application code run simultaneously for a period, a migration that drops a column the old code still reads will break it before the rollout finishes. The safe pattern is to make schema changes backward-compatible in stages: add the new column and dual-write in one deploy, backfill and switch reads over in a second, and only drop the old column in a third — rather than doing it all in one migration.
• Autogenerate misses data migrations. Structural changes (add column) are detected; changes that require moving or transforming data (splitting a full_name column into first_name/last_name) are not — you write that logic by hand inside the migration script.

Treat every autogenerated migration as a pull request to review, not a command to run blindly, especially once a schema has real production data behind it.

---

Troubleshooting common errors

• sqlite3.OperationalError: database is locked — another connection is holding a write transaction open. Keep transactions short, avoid holding a connection open across slow I/O (like an HTTP call) between BEGIN and COMMIT, and enable WAL mode (PRAGMA journal_mode=WAL;) to let reads proceed concurrently with a writer.
• sqlalchemy.exc.TimeoutError: QueuePool limit ... overflow ... — your app is requesting more concurrent connections than pool_size + max_overflow allows. Either raise the pool limits (if the database can handle more connections) or find the code path that’s holding sessions open longer than necessary — a common cause is forgetting to close a session in a background job or a script.
• FATAL: too many connections for role (PostgreSQL) — the sum of connections from all your app’s worker processes exceeds the database’s max_connections. Lower per-process pool_size, reduce the number of worker processes, or put a pooler like PgBouncer in front of the database.
• sqlalchemy.orm.exc.DetachedInstanceError — you’re accessing a lazy-loaded relationship attribute on an object after its session has already closed (very common in Flask views that return an ORM object to a background thread, or in __repr__ calls after the request ends). Either access the needed attributes before the session closes, or eager-load them with joinedload/selectinload so they’re already populated.
• Silent data loss after “successful” inserts with raw sqlite3 — almost always a missing conn.commit().

---

Next in the series

• Python web deployment
• Pandas for data analysis

---

Related Articles

• Django basics | Get started with Python’s full-stack web framework
• Python REST APIs | Build API Servers with Flask and Django
• Node.js Database Integration: MongoDB, PostgreSQL, and MySQL

---

Frequently Asked Questions (FAQ)

Q. SQLite works fine in my tests — why would it behave differently in production?

A. Test suites usually run one process against an in-memory or throwaway SQLite database with effectively no concurrent writers, which hides exactly the single-writer locking behavior described in section 1. If production runs multiple worker processes or threads writing concurrently, switch your CI/staging environment to PostgreSQL (or at least run a concurrency test against it) before you rely on SQLite’s behavior matching production.

Q. My ORM query is slow even though the “same” SQL runs fast in psql — why?

A. Compare the actual generated SQL, not the SQL you assume the ORM writes. Turn on echo=True in create_engine() (or log SQLAlchemy’s sqlalchemy.engine logger) and check for accidental N+1 loops from lazily-loaded relationships, as covered in sections 2 and 7 — this is the single most common cause of “the ORM is slow” reports that turn out to be dozens or hundreds of hidden queries rather than one slow one.

Q. Where can I read more about the underlying concepts?

A. For SQLAlchemy specifics, the official SQLAlchemy documentation is unusually thorough, including a dedicated performance and N+1 guide. For PostgreSQL’s transaction and isolation-level behavior, the PostgreSQL documentation on transaction isolation is the authoritative source referenced in section 5.

---