12.5. Creating Records

Why Creating Records Matters

In almost every backend application you will need to store new data in a database. When a user signs up, creates a blog post, adds a product to a catalog, or submits a form, the backend has to:

Receive data from the client.
Validate and transform it.
Insert it into the database.
Return a meaningful response.

In this chapter you will see how to do step 3 with an ORM, focusing on SQLAlchemy style patterns, which are common in Python backends. You will learn how to:

Turn input data into ORM model instances.
Add them to the database session.
Commit them safely.
Handle common pitfalls like missing fields or duplicate data.

We will assume you already know what an ORM is and what a database session is, and that you have models defined. Here we focus only on creating records.

Basic Pattern for Creating a Record

When you use an ORM, creating a record usually follows this pattern:

Create a model instance with the desired values.
Add it to the database session.
Commit the session.
(Optionally) refresh and return the created object.

In SQLAlchemy style code, this often looks like:

python

new_user = User(
    email="alice@example.com",
    name="Alice",
)
session.add(new_user)
session.commit()
session.refresh(new_user)

After commit(), the object now exists in the database. After refresh(), any fields that the database generated automatically, such as an id, are available on the instance.

Core pattern for creating records with an ORM

Instantiate the model: obj = Model(**data)
Add to the session: session.add(obj)
Commit the transaction: session.commit()
Refresh if you need updated fields: session.refresh(obj)

Creating a Simple Record

Let us start with a very simple example. Suppose you have a User model:

python

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    name = Column(String, nullable=False)

You have a session object, typically from SessionLocal() or similar.

Inserting a Single Record

python

def create_user(session, email: str, name: str) -> User:
    user = User(email=email, name=name)  # 1. create instance
    session.add(user)                    # 2. stage for insert
    session.commit()                     # 3. write to DB
    session.refresh(user)                # 4. load generated fields
    return user

What happens here:

Before commit, user has no id, because the database has not inserted it yet.
After commit, the insert is done.
session.refresh(user) asks the database for the current data of that row, so user.id gets populated.

If you print before and after:

python

user = User(email="bob@example.com", name="Bob")
print("Before commit:", user.id)  # Usually None
session.add(user)
session.commit()
session.refresh(user)
print("After commit:", user.id)   # Now has an integer

Working with Default Values and Auto-Generated Fields

Many columns have default values, or values that the database generates automatically:

Auto-incrementing id.
Timestamps such as created_at or updated_at.
Status fields with a default value.
UUIDs generated by the database.

You usually do not pass these fields when you construct the object. The ORM and database handle them.

Example: Model with Defaults

python

from sqlalchemy import Column, Integer, String, DateTime, func, Boolean
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    published = Column(Boolean, nullable=False, default=False)
    created_at = Column(DateTime, server_default=func.now(), nullable=False)

Creating a Post Without Defaults

python

def create_post(session, title: str) -> Post:
    post = Post(title=title)  # Do not pass 'published' or 'created_at'
    session.add(post)
    session.commit()
    session.refresh(post)
    return post

After commit:

post.published will be False, from the default.
post.created_at will be set to the current time by the database.

If you check:

python

post = create_post(session, "My first post")
print(post.published)   # False
print(post.created_at)  # Datetime value from DB

Let the database handle:

Auto-increment primary keys.
created_at and updated_at timestamps.
Boolean flags with defaults.
Do not manually set them unless you have a good reason.

Creating Multiple Records at Once

Sometimes you need to insert many rows, for example:

Seeding initial data like roles or categories.
Bulk import from a CSV file.
Creating test data.

You can create multiple instances and add them in one batch.

Example: Inserting Several Users

python

def create_many_users(session, users_data: list[dict]) -> list[User]:
    users = [User(**data) for data in users_data]  # Create many objects
    session.add_all(users)                         # Add all to session
    session.commit()                               # One commit
    for user in users:
        session.refresh(user)                      # Refresh if needed
    return users

Usage:

python

users_data = [
    {"email": "a@example.com", "name": "User A"},
    {"email": "b@example.com", "name": "User B"},
    {"email": "c@example.com", "name": "User C"},
]
users = create_many_users(session, users_data)
for u in users:
    print(u.id, u.email)

Performance Note

add_all still sends each row through the ORM.
For very large inserts, ORMs often provide bulk methods such as session.bulk_save_objects or session.bulk_insert_mappings. These can be faster but skip some ORM features like events or identity tracking.

Example of a bulk insert with mappings:

python

def bulk_insert_users(session, users_data: list[dict]):
    session.bulk_insert_mappings(User, users_data)
    session.commit()

Use bulk inserts mostly for imports or seeding, not for everyday API operations that need full ORM behavior.

Handling Relationships When Creating Records

Creating records often involves relationships between tables, such as:

A user has many posts.
An order has many order items.
A comment belongs to a post.

You need to create records that correctly reference each other.

We will look at typical patterns with foreign keys and ORM relationships.

Creating a Child Record with a Foreign Key

Suppose you have:

python

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, nullable=False)
    posts = relationship("Post", back_populates="author")
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    body = Column(String, nullable=False)
    author_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    author = relationship("User", back_populates="posts")

Option 1: Set the Foreign Key Field Directly

python

def create_post_for_user(session, user_id: int, title: str, body: str) -> Post:
    post = Post(
        title=title,
        body=body,
        author_id=user_id,  # directly set FK
    )
    session.add(post)
    session.commit()
    session.refresh(post)
    return post

This works if you already know user_id. It is simple and common in REST APIs, where the route might be /users/{user_id}/posts.

Option 2: Use the Relationship Attribute

You can also assign the parent object instead of the foreign key.

python

def create_post_for_user(session, user_email: str, title: str, body: str) -> Post:
    user = session.query(User).filter_by(email=user_email).one()
    post = Post(
        title=title,
        body=body,
        author=user,  # assign relationship
    )
    session.add(post)
    session.commit()
    session.refresh(post)
    return post

Here the ORM automatically sets author_id to user.id.

Creating Parent and Child Records Together

You can create a parent and its children in one go. The ORM will take care of inserting them in the correct order.

python

def create_user_with_posts(session, email: str, posts_data: list[dict]) -> User:
    user = User(
        email=email,
        posts=[Post(**p) for p in posts_data]  # create children via relationship
    )
    session.add(user)
    session.commit()
    session.refresh(user)
    return user

Usage:

python

user = create_user_with_posts(
    session,
    "carol@example.com",
    posts_data=[
        {"title": "First post", "body": "Hello"},
        {"title": "Second post", "body": "More text"},
    ],
)
print(user.id)
print(len(user.posts))         # 2
print(user.posts[0].author_id) # same as user.id

The ORM:

Inserts the users row.
Gets the id of the new user.
Inserts rows in posts with author_id equal to that id.

When creating related records:

You can set foreign key fields directly, for example author_id.
Or you can set relationship attributes, for example author or posts.
The ORM will handle insert order and foreign key values for related objects in the same session.

Avoiding Common Pitfalls

Creating records is easy, but several mistakes appear often in real projects. Here are typical problems and how to avoid them.

Forgetting to Commit

If you add an object but forget session.commit(), nothing reaches the database.

Bad:

python

user = User(email="x@example.com", name="X")
session.add(user)
# missing session.commit()

If the session is closed or rolled back, the new user disappears.

Better:

python

user = User(email="x@example.com", name="X")
session.add(user)
session.commit()
session.refresh(user)

Forgetting to Add Before Commit

Another common error is to call commit without adding anything.

Bad:

python

user = User(email="x@example.com", name="X")
# forgot session.add(user)
session.commit()

Result: the user is never inserted. Always ensure you call add or add_all (or another method that registers the object) before commit.

Not Handling Constraint Errors

If your table has constraints, for example:

email is UNIQUE.
Some columns are NOT NULL.

The database will reject invalid inserts.

Example of a unique constraint:

python

email = Column(String, unique=True, nullable=False)

Trying to insert two users with the same email:

python

user1 = User(email="dup@example.com", name="One")
user2 = User(email="dup@example.com", name="Two")
session.add_all([user1, user2])
session.commit()  # will raise an IntegrityError

You should catch this kind of error and respond appropriately in your application.

Example handle:

python

from sqlalchemy.exc import IntegrityError
def safe_create_user(session, email: str, name: str) -> User | None:
    user = User(email=email, name=name)
    session.add(user)
    try:
        session.commit()
    except IntegrityError:
        session.rollback()
        return None
    session.refresh(user)
    return user

Now callers can see that a duplicate was not created.

Mixing Session Lifetimes and Objects

You should not:

Use objects created in one session with another session.
Keep ORM instances around for a very long time while sessions are closed.

Bad:

python

user = create_user(session1, "a@example.com", "A")
session1.close()
# Later, in another part of the code:
session2.add(user)  # can cause errors or warnings

Better:

Return plain data (for example Pydantic models or dictionaries) from functions.
Use a short-lived session per request in web applications, and not keep ORM instances past that request.

Patterns for Create Functions in a Backend

In a real backend, you usually wrap creation logic in functions or classes. Here is a simple pattern that fits many codebases.

Example: Repository-Style Create

python

class UserRepository:
    def __init__(self, session):
        self.session = session
    def create(self, email: str, name: str) -> User:
        user = User(email=email, name=name)
        self.session.add(user)
        self.session.commit()
        self.session.refresh(user)
        return user

Usage in a route handler (pseudocode):

python

def create_user_endpoint(request_body, session):
    repo = UserRepository(session)
    user = repo.create(
        email=request_body.email,
        name=request_body.name,
    )
    return {
        "id": user.id,
        "email": user.email,
        "name": user.name,
    }

You can extend this pattern with:

Input models for validation.
Error handling for duplicates.
Logging or events.

Summary

You have seen how to:

Create a new ORM instance and add it to the session.
Commit and refresh to get auto-generated fields like primary keys.
Use default and auto-generated values without manually setting them.
Insert multiple records with add_all or bulk methods.
Create records that belong to other records through relationships.
Avoid common mistakes such as missing commit or ignoring constraint errors.
Wrap creation logic in functions or repository classes.

These patterns are the foundation for building endpoints like "create user", "create post", "create order", and any other "create" operation in your backend.

12.5. Creating Records

Why Creating Records Matters

Basic Pattern for Creating a Record

Creating a Simple Record

Inserting a Single Record

Working with Default Values and Auto-Generated Fields

Example: Model with Defaults

Creating a Post Without Defaults

Creating Multiple Records at Once

Example: Inserting Several Users

Performance Note

Handling Relationships When Creating Records

Creating a Child Record with a Foreign Key

Option 1: Set the Foreign Key Field Directly

Option 2: Use the Relationship Attribute

Creating Parent and Child Records Together

Avoiding Common Pitfalls

Forgetting to Commit

Forgetting to Add Before Commit

Not Handling Constraint Errors

Mixing Session Lifetimes and Objects

Patterns for Create Functions in a Backend

Example: Repository-Style Create

Summary

Comments

Where to Move