Home About Me

Flask-SQLAlchemy in Practice: Configuration, Models, and Session Workflows

2026-03-14

Flask-SQLAlchemy lets a Flask application work with SQLAlchemy through a familiar db object. The first steps are install, point Flask at a database, and define a model. After that, the session handles inserts, deletes, queries, and updates.

Install and configure

pip install Flask-SQLAlchemy

A minimal Flask setup looks like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy # database dependency

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

The key Flask-SQLAlchemy configuration option here is SQLALCHEMY_DATABASE_URI, which tells the extension how to connect to the database. Common URI formats include:

Postgres:

postgresql://scott:tiger@localhost/mydatabase

MySQL:

mysql://scott:tiger@localhost/mydatabase

Oracle:

oracle://scott:[email protected]:1521/sidname

SQLite (note the four leading slashes):

sqlite:////absolute/path/to/foo.db

SQLALCHEMY_ECHO is another useful setting. When it is True, SQLAlchemy logs every statement sent to standard output (stderr), which helps a lot during debugging. Flask-SQLAlchemy recognizes more configuration keys than these, but SQLALCHEMY_DATABASE_URI and SQLALCHEMY_ECHO are the ones you will usually touch first.

Declaring models

A model subclasses db.Model. Each field is a db.Column, and the most common column types are:

<table> <thead> <tr> <th>Type</th> <th>Purpose</th> </tr> </thead> <tbody> <tr> <td>Integer</td> <td>An integer</td> </tr> <tr> <td>String (size)</td> <td>A string with a length limit</td> </tr> <tr> <td>Text</td> <td>Longer unicode text</td> </tr> <tr> <td>DateTime</td> <td>A date and time represented as a Python datetime object</td> </tr> <tr> <td>Float</td> <td>Stores floating-point values</td> </tr> <tr> <td>Boolean</td> <td>Stores boolean values</td> </tr> <tr> <td>PickleType</td> <td>Stores a persistent Python object</td> </tr> <tr> <td>LargeBinary</td> <td>Stores arbitrary large binary data</td> </tr> </tbody> </table>

The User example above shows the basic shape: an integer primary key, unique string fields for username and email, an initializer, and a readable __repr__.

Inserting, deleting, querying, and updating

Add a record by creating an instance, adding it to the session, and committing:

user = User('jack',''[email protected]')
db.session.add(user)
db.session.commit()

Delete an existing record with the same session-commit pattern:

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

Queries go through Model.query. For example:

user = User.query.filter_by('jack').first()
user.email # outputs [email protected]

Primary-key lookups use get(), while all() returns every row. The update flow also uses a session: load the record, assign a new value, then commit. The snippet below is included as written; if you adapt it, check whether the final delete belongs there or whether you only need to commit the changed field.

user = User.query.get(1)
user.email = '[email protected]'
db.session.delete(user)
db.session.commit()

For the full set of recognized configuration keys, see the Flask-SQLAlchemy documentation.

Related Posts