How to Create a Model in Flask with SQLAlchemy

In Flask, you create a model by defining a Python class that inherits from db.Model using Flask-SQLAlchemy. This class represents a database table, and its attributes define the table columns.

Syntax

To create a model in Flask, you define a class that inherits from db.Model. Each attribute of the class represents a column in the database table. You specify the column type and options using db.Column.

• class ModelName(db.Model): Defines the model class.
• id = db.Column(db.Integer, primary_key=True): Defines a primary key column.
• other_field = db.Column(db.String(100)): Defines a string column with max length 100.

python

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

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)

Example

This example shows a complete Flask app that creates a User model, initializes the database, and adds a user record.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.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)

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

with app.app_context():
    db.create_all()  # Create tables
    new_user = User(username='alice', email='[e-mail]')
    db.session.add(new_user)
    db.session.commit()
    print(User.query.all())

Output

[<User alice>]

Common Pitfalls

• Forgetting to call db.create_all() to create tables before using models.
• Not setting SQLALCHEMY_DATABASE_URI correctly causes connection errors.
• Missing nullable=False can allow empty fields unexpectedly.
• Not using app.app_context() when working with the database outside request context.

python

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Wrong: Missing primary key
class Product(db.Model):
    name = db.Column(db.String(50))

# Right: Add primary key
class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

Quick Reference

Here is a quick summary of key points when creating models in Flask:

• Import and initialize SQLAlchemy with your Flask app.
• Define model classes inheriting from db.Model.
• Use db.Column to define fields with types and constraints.
• Call db.create_all() inside app context to create tables.
• Use db.session to add, update, and commit changes.

Key Takeaways

Create models by defining classes inheriting from db.Model with columns as attributes.

Always set a primary key column in your model for proper database behavior.

Initialize SQLAlchemy with your Flask app and configure the database URI.

Call db.create_all() inside app context to create tables before using models.

Use db.session to add and commit data changes to the database.