CRUD Operations

CRUD Operations

Every database app boils down to four operations: Create, Read, Update, and Delete. CRUD for short. Flask-SQLAlchemy makes all of these straightforward. Once you understand the pattern, you can build any data-driven feature.

Creating Records

To add a new record, create an instance of your model and add it to the session. The session is like a staging area — changes aren't saved until you commit.

@app.route('/add_user', methods=['POST'])
def add_user():
    username = request.form['username']
    email = request.form['email']

    new_user = User(username=username, email=email)
    db.session.add(new_user)
    db.session.commit()

    return f'User {username} created!'

You can also pass values directly to the constructor if your model's __init__ accepts them, or set attributes one by one before adding.

Reading Records

Reading is where queries come in. SQLAlchemy gives you several ways to fetch data.

# Get all users
users = User.query.all()

# Get a specific user by ID
user = User.query.get(1)

# Filter results
active_users = User.query.filter_by(is_active=True).all()

# More complex filters
recent_users = User.query.filter(User.created_at > some_date).order_by(User.created_at.desc()).all()

# Get first result or None
user = User.query.filter_by(username='alice').first()

The query object is your gateway to reading data. You can chain filters, order results, and limit how many you get back.

Updating Records

Updating is just modifying attributes and committing. SQLAlchemy tracks changes automatically.

@app.route('/update_user/<int:id>', methods=['POST'])
def update_user(id):
    user = User.query.get_or_404(id)
    user.email = request.form['email']
    db.session.commit()
    return f'User {user.username} updated!'

The get_or_404() shortcut fetches the record or automatically returns a 404 error if it doesn't exist. Super handy.

Deleting Records

Deleting follows the same pattern — fetch, delete, commit.

@app.route('/delete_user/<int:id>', methods=['POST'])
def delete_user(id):
    user = User.query.get_or_404(id)
    db.session.delete(user)
    db.session.commit()
    return f'User {user.username} deleted!'

Pagination

When you have thousands of records, you don't want to load them all at once. Pagination gives you results page by page.

@app.route('/users')
def users():
    page = request.args.get('page', 1, type=int)
    users = User.query.paginate(page=page, per_page=10)

    return render_template('users.html', users=users)

The paginate() method returns an object with items, pages, has_next, has_prev, and other useful properties. It's all built in — no need to write limit/offset queries yourself.

Why db.session Matters

The session is your transaction manager. It tracks all changes, handles rollback if something goes wrong, and ensures data consistency. Always commit after making changes, and remember that nothing is permanent until you call db.session.commit().