Flask SQLAlchemy Setup

Flask-SQLAlchemy is an extension that connects Flask to databases through an ORM (Object-Relational Mapper). An ORM lets you interact with database tables using Python classes and objects instead of writing raw SQL. This makes database code cleaner, safer, and easier to switch between databases.

The ORM Concept

Without an ORM, you write SQL like this:

SELECT * FROM users WHERE id = 5

With SQLAlchemy, you write Python:

User.query.get(5)

Both do the same thing. SQLAlchemy translates your Python into SQL behind the scenes.

Python code: User.query.filter_by(name='Alice').all()
                    │
          SQLAlchemy translates
                    │
SQL sent:   SELECT * FROM user WHERE name = 'Alice'
                    │
Returns:    List of User objects (not raw rows)

Installing Flask-SQLAlchemy

pip install flask-sqlalchemy

Configuring the Database

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

db = SQLAlchemy(app)

The SQLALCHEMY_DATABASE_URI string specifies the database type and location:

The three slashes in sqlite:/// mean a relative path from your project directory. Four slashes (sqlite:////) means an absolute path.

Defining a Model

A model is a Python class that maps to a database table. Each class attribute maps to a column:

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  = db.Column(db.DateTime, default=db.func.now())

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

Column Options

Creating the Tables

After defining your models, create the actual database tables with one command:

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

SQLAlchemy reads all your model classes and generates the CREATE TABLE SQL automatically. Run this once when setting up the project. It does not overwrite existing tables — it only creates tables that do not yet exist.

The Application Context

SQLAlchemy needs Flask's application context active to know which database configuration to use. When running code outside a request (like in a setup script), wrap it in with app.app_context():.

Inside a request:  app context is active automatically
Outside a request: wrap code in: with app.app_context():

Verifying the Setup

Check the database was created by looking for the myapp.db file in your project folder. You can also inspect it using SQLite tools like DB Browser for SQLite or the command line:

sqlite3 myapp.db
.tables       -- shows all tables
.schema user  -- shows the user table structure

Complete Setup File Example

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

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)
    email    = db.Column(db.String(120), unique=True, nullable=False)

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

if __name__ == '__main__':
    app.run(debug=True)

Summary

Flask-SQLAlchemy wraps your database interactions in Python classes called models. Configure the database URI in your app config, create a db = SQLAlchemy(app) instance, and define models as classes that inherit from db.Model. Call db.create_all() once to generate the tables. SQLAlchemy works with SQLite, PostgreSQL, and MySQL — switch databases by changing just the URI string.