Flask with Database - SQLite and MySQL Integration

A Flask app without a database cannot do much in the real world. Almost every meaningful web application needs to store data — user accounts, blog posts, orders, messages, settings. Without a database, every piece of data disappears the moment you restart the server.

This guide walks through connecting Flask to a real database, step by step. We start with SQLite which needs zero setup and is perfect for learning and local development. Then we look at MySQL for production. We use Flask-SQLAlchemy to interact with the database using clean Python code instead of raw SQL, and Flask-Migrate to safely change the database structure over time.

By the end you will have a complete app that creates, reads, updates and deletes records — the four operations every database-backed app needs.

SQLite vs MySQL — Which One to Use

SQLite stores your entire database in a single file on your computer. There is no server to install, no password to configure and no port to worry about. It just works the moment you start your Flask app. This makes it ideal for learning, quick prototypes and local development.

MySQL is a full database server used by millions of production websites. It handles many users writing at the same time, scales to huge datasets and is the right choice for anything real users will access over the internet.

The great thing is that Flask-SQLAlchemy works identically with both. The only line that changes in your entire codebase is the database connection string. All your models, all your queries, all your routes stay exactly the same.

ℹ️ Standard workflow: develop locally with SQLite, then switch to MySQL when you deploy to a server. Because the code does not change at all, this swap takes about 30 seconds.

Project Setup

Install the packages you need. Open your terminal and run:

Terminal — installing required packages
# Core packages
pip install flask flask-sqlalchemy flask-migrate

# Only needed if you use MySQL
pip install pymysql

# Recommended folder structure
myapp/
  app.py          # Flask app and configuration
  models.py       # database models (tables)
  routes.py       # URL handlers
  templates/      # Jinja2 HTML templates
  instance/       # SQLite .db file lives here (auto-created)
  migrations/     # migration history (created by flask db init)

Flask-SQLAlchemy — Python Instead of Raw SQL

SQLAlchemy is an ORM, which stands for Object Relational Mapper. The idea is simple: instead of writing raw SQL like INSERT INTO users (name, email) VALUES (?, ?), you create a Python object and SQLAlchemy writes the SQL for you. Your tables become Python classes and your rows become objects.

This means less SQL to remember, safer queries by default, and code that reads almost like plain English.

Connecting to SQLite

Python — Flask app setup with SQLite
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)

# sqlite:///  (three slashes) + filename = file in the instance/ folder
app.config['SQLALCHEMY_DATABASE_URI']        = 'sqlite:///app.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # saves memory

db      = SQLAlchemy(app)
migrate = Migrate(app, db)

Connecting to MySQL

To switch from SQLite to MySQL you change exactly one line. Every other line in your project stays the same.

Python — switching to MySQL with one line
# Format: mysql+pymysql://username:password@host:port/database_name
app.config['SQLALCHEMY_DATABASE_URI'] = \
    'mysql+pymysql://root:mypassword@localhost:3306/myapp_db'

# In production — NEVER put passwords in code directly
# Load them from environment variables instead
import os
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get(
    'DATABASE_URL',
    'sqlite:///app.db'   # fallback to SQLite when env var is not set
)

# Create the MySQL database first (run in your MySQL shell):
# CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Defining Models — Tables as Python Classes

A model is a Python class that inherits from db.Model. Each class represents one table. Each db.Column attribute inside it becomes one column. SQLAlchemy reads your models and knows exactly how to create and query those tables.

Column Types

Python — a User model with all the common column types
from datetime import datetime
from app import db

class User(db.Model):
    __tablename__ = 'users'   # optional — defaults to 'user' without this

    # Integer auto-increments. primary_key=True is the unique row ID
    id         = db.Column(db.Integer,     primary_key=True)

    # String has a max length. unique prevents duplicate values
    username   = db.Column(db.String(80),  unique=True,  nullable=False)
    email      = db.Column(db.String(120), unique=True,  nullable=False)

    # Text has no length limit — good for long content like bios or posts
    bio        = db.Column(db.Text)

    # Boolean stores True or False with a default value
    is_active  = db.Column(db.Boolean,  default=True)

    # Float stores decimal numbers
    score      = db.Column(db.Float,    default=0.0)

    # DateTime auto-fills the current time when a record is created
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # onupdate refreshes the timestamp every time the row is saved
    updated_at = db.Column(db.DateTime, default=datetime.utcnow,
                                         onupdate=datetime.utcnow)

    def __repr__(self):
        return f'<User {self.username}>'

    # Returns a plain dict — useful for jsonify() in API routes
    def to_dict(self):
        return {
            'id':         self.id,
            'username':  self.username,
            'email':     self.email,
            'is_active': self.is_active,
            'created_at':self.created_at.isoformat()
        }

Relationships — Linking Two Tables Together

In most apps, tables are connected. A user can have many posts. An order can have many items. You define these connections using db.ForeignKey on the child table and db.relationship on the parent. After that you can navigate between related records just like accessing an attribute.

Python — one-to-many: one User has many Posts
class User(db.Model):
    id       = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

    # backref='author' means every Post object gets a .author shortcut
    # lazy='dynamic' means posts are not loaded until you ask for them
    posts = db.relationship('Post', backref='author', lazy='dynamic')

class Post(db.Model):
    id      = db.Column(db.Integer,    primary_key=True)
    title   = db.Column(db.String(200), nullable=False)
    body    = db.Column(db.Text)

    # This column stores the ID of the user who wrote this post
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

# Usage after the relationship is set up:
# user.posts.all()       — all posts written by this user
# post.author.username   — username of the author of this post

Creating the Tables

Once your models are written, you tell SQLAlchemy to create the matching tables in the database. For a fresh project, one call to db.create_all() is all you need. For an existing project with data already in it, use Flask-Migrate instead (covered below).

Python — creating all tables from your models
# Run this once to create all tables
with app.app_context():
    db.create_all()
    print("All tables created.")

# Or add it to the bottom of app.py so it runs on startup
if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True)

# db.create_all() is safe to call multiple times.
# It only creates tables that do not already exist.
# It will NOT drop or modify tables that already have data.

⚠️ db.create_all() does not update existing tables. If you add a new column to a model after the table was already created, create_all() will not add that column. Use Flask-Migrate for all schema changes after initial creation.

CRUD Operations — Create, Read, Update, Delete

CRUD stands for the four things every database-backed app needs to do: add new records, read them back, change them and remove them. Here is how each works with SQLAlchemy.

Create — Adding New Records

Python — saving new records to the database
from app import db
from models import User

# Step 1: create a Python object with the data you want to save
new_user = User(
    username='shashank',
    email='shashank@example.com'
)

# Step 2: add it to the session (a staging area, nothing saved yet)
db.session.add(new_user)

# Step 3: commit — this is when the data actually writes to the database
db.session.commit()

# After commit, SQLAlchemy fills in the auto-generated ID
print(f"Saved with ID: {new_user.id}")  # e.g. 1

# Save multiple records at once with add_all
db.session.add_all([
    User(username='priya',  email='priya@example.com'),
    User(username='raj',    email='raj@example.com'),
    User(username='ananya', email='ananya@example.com'),
])
db.session.commit()

Read — Querying the Database

SQLAlchemy has a full query API. You can get everything, filter by any column, sort results and paginate — all in clean Python without writing a single SQL statement.

Python — querying records with SQLAlchemy
# Get every row in the table
all_users = User.query.all()

# Get one row by its primary key ID
user = User.query.get(1)             # returns None if not found
user = db.get_or_404(User, 1)        # returns a 404 response if not found

# Filter by an exact column value
user   = User.query.filter_by(username='shashank').first()
users  = User.query.filter_by(is_active=True).all()

# Filter with operators (greater than, like, etc)
high_score = User.query.filter(User.score > 50).all()
search     = User.query.filter(User.username.like('sha%')).all()

# Sort results
newest = User.query.order_by(User.created_at.desc()).all()
by_name = User.query.order_by(User.username.asc()).all()

# Count rows without loading all data
total = User.query.count()

# Paginate — only load 20 rows at a time
page    = request.args.get('page', 1, type=int)
results = User.query.paginate(page=page, per_page=20, error_out=False)
print(results.items)     # list of User objects for this page
print(results.total)     # total rows across all pages
print(results.has_next)  # True if there is a next page

Update — Changing Existing Records

Python — updating records in the database
# Load the record, change the attribute, commit
user           = User.query.get(1)
user.email     = 'updated@example.com'
user.is_active = False
db.session.commit()   # both changes saved in one commit

# Update many records at once without loading each one individually
User.query.filter_by(is_active=False).update({'score': 0})
db.session.commit()

Delete — Removing Records

Python — deleting records
# Delete one record
user = User.query.get(1)
db.session.delete(user)
db.session.commit()

# Delete many records at once
User.query.filter_by(is_active=False).delete()
db.session.commit()

✅ Always remember to commit. db.session.add() and db.session.delete() stage your changes. Nothing actually writes to the database until you call db.session.commit(). Think of the session as a shopping cart and commit as checking out.

Flask-Migrate — Changing Your Schema Safely

When your app is live with real data, you cannot just delete the database and recreate it every time you add a new column. Flask-Migrate solves this. It tracks every change you make to your models and generates migration files — small scripts that update the database structure without touching existing data.

Think of it like git for your database schema. Every model change gets a commit. You can see the full history of every change and roll back if something goes wrong.

Terminal — Flask-Migrate commands you need to know
# 1. Initialise migrations — run ONCE when starting the project
flask db init
# Creates a migrations/ folder that tracks all schema history

# 2. After changing a model — generate a migration script
flask db migrate -m "add bio column to users table"
# SQLAlchemy compares your current models to the database
# and writes the SQL needed to bring it up to date

# 3. Apply the migration to the actual database
flask db upgrade
# The new column (or table, or index) now exists in the database

# Undo the last migration if something went wrong
flask db downgrade

# Show all past migrations
flask db history

# Show which migration the database is currently on
flask db current

ℹ️ Commit migrations to git. The migrations/ folder should always be in your repository. It is the record of every schema change and ensures every developer and every server runs the same database structure.

Full Working CRUD App

Here is a complete Flask app with SQLite that covers every operation. It is a simple notes API — create a note, list all notes, get one, update it or delete it:

Python — complete Flask notes CRUD app with SQLite
from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI']       = 'sqlite:///notes.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# Model
class Note(db.Model):
    id         = db.Column(db.Integer,    primary_key=True)
    title      = db.Column(db.String(200), nullable=False)
    content    = db.Column(db.Text,       default='')
    created_at = db.Column(db.DateTime,   default=datetime.utcnow)

    def to_dict(self):
        return {
            'id':         self.id,
            'title':     self.title,
            'content':   self.content,
            'created_at':self.created_at.isoformat()
        }

# C — Create a note  POST /notes
@app.route('/notes', methods=['POST'])
def create_note():
    data = request.get_json()
    if not data or not data.get('title'):
        return jsonify({'error': 'Title is required'}), 422
    note = Note(title=data['title'], content=data.get('content', ''))
    db.session.add(note)
    db.session.commit()
    return jsonify(note.to_dict()), 201

# R — List all notes  GET /notes
@app.route('/notes', methods=['GET'])
def list_notes():
    notes = Note.query.order_by(Note.created_at.desc()).all()
    return jsonify([n.to_dict() for n in notes])

# R — Get one note  GET /notes/7
@app.route('/notes/<int:note_id>', methods=['GET'])
def get_note(note_id):
    note = db.get_or_404(Note, note_id)
    return jsonify(note.to_dict())

# U — Update a note  PATCH /notes/7
@app.route('/notes/<int:note_id>', methods=['PATCH'])
def update_note(note_id):
    note = db.get_or_404(Note, note_id)
    data = request.get_json()
    if 'title'   in data: note.title   = data['title']
    if 'content' in data: note.content = data['content']
    db.session.commit()
    return jsonify(note.to_dict())

# D — Delete a note  DELETE /notes/7
@app.route('/notes/<int:note_id>', methods=['DELETE'])
def delete_note(note_id):
    note = db.get_or_404(Note, note_id)
    db.session.delete(note)
    db.session.commit()
    return ('', 204)   # 204 No Content — success, nothing to return

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True)

SQLite vs MySQL at a Glance

Feature	SQLite	MySQL
Setup needed	None — just a file	Install MySQL server separately
Best for	Development and learning	Production with real users
Concurrent writes	Limited (file locking)	Handles many at once
Max data size	A few GB comfortably	Terabytes with proper config
Connection string	sqlite:///app.db	mysql+pymysql://user:pass@host/db
Code changes needed	Zero — only the URI line changes
Flask-Migrate	Works identically with both

⚡ Key Takeaways

Use SQLite during development (zero setup, just a file). Switch to MySQL for production. Only the SQLALCHEMY_DATABASE_URI string changes — all your models and queries stay identical.
Never put database passwords in your code. Store them in environment variables and load them with os.environ.get().
A model is a Python class that inherits from db.Model. Each db.Column attribute defines one column. Use nullable=False to require a value and unique=True to prevent duplicates.
Every write operation follows the same three steps: create or modify the Python object, db.session.add() for new records, then db.session.commit() to save. Nothing is written until commit.
Query with .all(), .first(), .filter_by(), .filter(), .order_by() and .paginate(). Use db.get_or_404() in route handlers so missing records automatically return a 404.
Define relationships with db.ForeignKey on the child model and db.relationship on the parent. Add a backref so you can navigate in both directions.
Use db.create_all() to create tables for a new project. Do not use it after the app is live with data — use Flask-Migrate instead.
Flask-Migrate has three commands you will use regularly: flask db init once at the start, flask db migrate after every model change, and flask db upgrade to apply the change.
Always commit the migrations/ folder to git. It is the complete history of every database schema change and keeps all environments in sync.

Tags: Flask SQLAlchemy SQLite MySQL CRUD Backend

Shashank Shekhar

Founder & Creator — Hoopsiper.com

Full stack developer and educator. Building Hoopsiper to help developers learn faster through practical, no-fluff coding guides on JavaScript, AI/ML, Python and modern web development.

Flask with DatabaseSQLite and MySQL Integration

SQLite vs MySQL — Which One to Use

Project Setup

Flask-SQLAlchemy — Python Instead of Raw SQL

Connecting to SQLite

Connecting to MySQL

Defining Models — Tables as Python Classes

Column Types

Relationships — Linking Two Tables Together

Creating the Tables

CRUD Operations — Create, Read, Update, Delete

Create — Adding New Records

Read — Querying the Database

Update — Changing Existing Records

Delete — Removing Records

Flask-Migrate — Changing Your Schema Safely

Full Working CRUD App

SQLite vs MySQL at a Glance

Mastering Flask Routing

RESTful API Best Practices

Database Connection Management

Flask with Database
SQLite and MySQL Integration