CRUD - Create Record

Creating records means:

• instantiate a model
• add it to the session
• commit

diagram add, flush, commit: three different moments mermaid

  flowchart TD
  A["obj = Model(...)"] --> B["transient -- the session has never seen it"]
  B --> C["db.session.add(obj)"]
  C --> D["pending -- in the session, not in the database"]
  D --> E["db.session.flush()"]
  E --> F["INSERT sent inside the open transaction"]
  F --> G["the primary key is now populated"]
  G --> H{"then what?"}
  H -->|"db.session.commit()"| I["COMMIT -- permanent, visible to everyone"]
  H -->|"db.session.rollback()"| J["ROLLBACK -- the INSERT is undone"]
  D -.->|"commit() flushes for you"| F

Adding an object only puts it in the session. Nothing is sent to the database until a flush, which is when the INSERT actually runs and the primary key comes back. Commit flushes and then makes the transaction permanent. That is why an id is still None right after add, and why a rollback can throw away work that had already been flushed.

The id is None until something reaches the database — verified

With Flask-SQLAlchemy 3.1.1:

python

a = Author(name="Ada")
a.id                    # None
db.session.add(a)
a.id                    # None   <- add() alone sends nothing
db.session.commit()
a.id                    # 1      <- now the INSERT has run

This is the reason user.id comes back None in code that adds a row and immediately tries to use its id — for a foreign key, a redirect URL, a log line. commit() fixes it, and so does db.session.flush() if you want the id without ending the transaction yet.

The session also keeps an identity map: within one session, two lookups of the same row return the same Python object, not two copies.

python

db.session.get(Author, 1) is db.session.get(Author, 1)   # True

So mutating one reference is visible through the other — usually convenient, occasionally surprising.

Example

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
 
db = SQLAlchemy(app)
 
 
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
 
 
with app.app_context():
    db.create_all()
 
    user = User(username="ravi")
    db.session.add(user)
    db.session.commit()
 
    print(user.id)  # id is available after commit

Session basics

• db.session holds pending changes
• commit() finalizes the transaction

If something fails, you can:

python

db.session.rollback()

Common pitfalls

• Forgetting commit() (data doesn’t persist)
• Not handling IntegrityError (unique constraint violations)
• Doing heavy DB work inside request handlers without optimization