Flask Database Integration (SQLAlchemy)

📦 Setup

pip install flask-sqlalchemy

from flask_sqlalchemy import SQLAlchemy
 
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///myapp.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)

DB	URI
SQLite	sqlite:///myapp.db
PostgreSQL	postgresql://user:pass@localhost/mydb
MySQL	mysql://user:pass@localhost/mydb

🏗️ Model

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

Type	Use
db.Integer	whole numbers
db.String(n)	text, max length n
db.Text	unbounded text
db.Boolean	True/False
db.DateTime	date + time
db.Float	decimal

🗂️ Create tables

with app.app_context():
    db.create_all()

➕ Create (insert)

new_user = User(username="amit", email="amit@example.com")
db.session.add(new_user)
db.session.commit()

🔍 Read (query)

User.query.all()
User.query.first()
User.query.get(1)
User.query.get_or_404(1)                              # 404 if missing
User.query.filter_by(username="amit").first()             # exact match shorthand
User.query.filter(User.email.like("%@example.com")).all()    # flexible filter
User.query.order_by(User.username).all()
User.query.limit(10).all()
 
from sqlalchemy import and_, or_
User.query.filter(and_(User.username=="amit", User.email.like("%@x.com"))).all()
User.query.filter(or_(User.username=="amit", User.username=="sara")).all()

✏️ Update

user = User.query.filter_by(username="amit").first()
user.email = "new@example.com"
db.session.commit()      # no separate .update() call — just mutate + commit

🗑️ Delete

db.session.delete(user)
db.session.commit()

🔗 Relationships

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship("Post", backref="author", lazy=True)
 
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)

user.posts              # all posts by this user
post.author              # backref — the owning user

lazy="joined" avoids N+1 query issues when looping over relationships at scale.

🔄 Migrations (Flask-Migrate)

pip install flask-migrate

from flask_migrate import Migrate
migrate = Migrate(app, db)

flask db init                              # once per project
flask db migrate -m "add email column"        # generate migration
flask db upgrade                                  # apply
flask db downgrade                                    # revert last

🔗 Next

Flask Authentication & Login · Flask REST APIs & JSON