Using SQLite with Flask

Flask is a lightweight and flexible framework for building web applications in Python. One of its strengths is how easily it integrates with various databases, including SQLite. If you're building a small to medium-sized application, SQLite is often a perfect choice due to its simplicity, zero configuration, and serverless architecture. In this article, we'll explore how to set up and use SQLite with Flask, covering everything from basic connections to more advanced patterns.

Setting Up SQLite in Flask

To get started, you'll need to install Flask if you haven't already. You can do this using pip:

pip install Flask

SQLite comes bundled with Python, so you don't need to install anything extra for the database itself. However, to interact with SQLite in a more convenient way within Flask, we often use an extension like Flask-SQLAlchemy. This provides an ORM (Object-Relational Mapping) layer, making database operations more Pythonic and less error-prone.

Install Flask-SQLAlchemy with:

pip install Flask-SQLAlchemy

Now, let's set up a basic Flask application with SQLite. Create a new Python file, for example, app.py, and add the following code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.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}>'

Here, we've configured the database URI to point to an SQLite file named app.db in the same directory as our script. We also define a simple User model with id, username, and email fields. The SQLALCHEMY_TRACK_MODIFICATIONS setting is turned off to avoid unnecessary overhead.

To create the database and tables, open a Python shell in the same directory as your app.py and run:

from app import db
db.create_all()

This will generate the app.db file and the user table based on your model.

Performing Basic Database Operations

With the setup complete, let's look at how to perform create, read, update, and delete (CRUD) operations using our model.

Adding a new user:

from app import db, User
new_user = User(username='johndoe', email='john@example.com')
db.session.add(new_user)
db.session.commit()

Querying users:

users = User.query.all()
user = User.query.filter_by(username='johndoe').first()

Updating a user:

user = User.query.filter_by(username='johndoe').first()
user.email = 'newemail@example.com'
db.session.commit()

Deleting a user:

user = User.query.filter_by(username='johndoe').first()
db.session.delete(user)
db.session.commit()

Remember to always call db.session.commit() to save changes to the database. Without it, your modifications won't persist.

• Create: Use db.session.add() followed by db.session.commit()
• Read: Use User.query with methods like all(), first(), or filter_by()
• Update: Modify the object attributes and commit the session
• Delete: Use db.session.delete() and commit

Handling Database Migrations

As your application evolves, you might need to change your database schema—for example, adding a new column or modifying an existing one. While you could drop and recreate the entire database, that's not practical in production. This is where database migrations come in.

We use Flask-Migrate, an extension that handles SQLAlchemy database migrations for Flask applications using Alembic. Install it with:

pip install Flask-Migrate

Then, update your app.py to include the migration setup:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
import os

app = Flask(__name__)
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'app.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# ... your models here

Now, to initialize migrations (do this once):

flask db init

This creates a migrations folder in your project. Whenever you change your models, generate a migration script:

flask db migrate -m "Description of changes"

Then apply the migration to update your database:

flask db upgrade

This process allows you to evolve your database schema without losing data.

Structuring Larger Applications

For larger applications, you might want to organize your code into multiple modules. A common practice is to separate models, routes, and configuration into different files. Here's a sample structure:

/myapp
    /migrations
    app.py
    config.py
    models.py
    routes.py

In config.py, you can define your configuration:

import os
basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

In models.py, define your models:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class User(db.Model):
    # ... same as before

In app.py, create and configure your application:

from flask import Flask
from config import Config
from models import db
from routes import bp

app = Flask(__name__)
app.config.from_object(Config)
db.init_app(app)
app.register_blueprint(bp)

# ... other setup like migrate

And in routes.py, define your routes using a Blueprint:

from flask import Blueprint, request, jsonify
from models import db, User

bp = Blueprint('main', __name__)

@bp.route('/users', methods=['POST'])
def create_user():
    data = request.get_json()
    new_user = User(username=data['username'], email=data['email'])
    db.session.add(new_user)
    db.session.commit()
    return jsonify({'message': 'User created!'}), 201

@bp.route('/users', methods=['GET'])
def get_users():
    users = User.query.all()
    return jsonify([{'id': u.id, 'username': u.username, 'email': u.email} for u in users])

This modular approach keeps your code clean and manageable as it grows.

Error Handling and Best Practices

When working with databases, error handling is crucial. Operations can fail for various reasons—duplicate entries, network issues (though less relevant for SQLite), or invalid data. Always wrap database operations in try-except blocks.

For example:

try:
    db.session.commit()
except Exception as e:
    db.session.rollback()
    print(f"An error occurred: {e}")

This ensures that if something goes wrong, you roll back the session to avoid partial updates.

Another best practice is to use database transactions appropriately. Group related operations into a single transaction to maintain data consistency. In Flask-SQLAlchemy, the session automatically provides a transaction, which you commit or rollback as a unit.

Also, be mindful of performance. While SQLite is efficient, poorly designed queries can still cause slowdowns. Use indexing on columns you frequently query:

class User(db.Model):
    # ...
    index = db.Index('idx_username', 'username')

And always close your sessions when done. Though Flask-SQLAlchemy manages sessions per request in web applications, it's good practice in scripts or other contexts to explicitly close sessions if they're no longer needed.

• Use try-except blocks around database operations
• Group related changes in transactions
• Add indexes to frequently queried columns
• Close sessions when they're no longer needed

Integrating with Flask Views

Let's see how to integrate SQLite operations into Flask view functions. Suppose we want to create a simple user registration and listing page.

First, create a template for listing users. Save this as templates/users.html:

<!DOCTYPE html>
<html>
<head>
    <title>Users</title>
</head>
<body>
    <h1>Users</h1>
    <ul>
    {% for user in users %}
        <li>{{ user.username }} - {{ user.email }}</li>
    {% endfor %}
    </ul>
    <a href="{{ url_for('add_user') }}">Add New User</a>
</body>
</html>

And a form for adding users in templates/add_user.html:

<!DOCTYPE html>
<html>
<head>
    <title>Add User</title>
</head>
<body>
    <h1>Add User</h1>
    <form method="POST">
        <label>Username: <input type="text" name="username" required></label><br>
        <label>Email: <input type="email" name="email" required></label><br>
        <input type="submit" value="Add User">
    </form>
</body>
</html>

Now, add the corresponding view functions in your routes:

from flask import render_template, request, redirect, url_for

@bp.route('/')
def list_users():
    users = User.query.all()
    return render_template('users.html', users=users)

@bp.route('/add', methods=['GET', 'POST'])
def add_user():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        new_user = User(username=username, email=email)
        db.session.add(new_user)
        db.session.commit()
        return redirect(url_for('list_users'))
    return render_template('add_user.html')

This gives you a basic web interface to interact with your SQLite database.

Advanced Query Techniques

As your application grows, you'll need more complex queries. Flask-SQLAlchemy provides a powerful query interface. Here are some examples:

Pagination:

page = request.args.get('page', 1, type=int)
users = User.query.paginate(page=page, per_page=10)

Joins (if you have related models):

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User', backref=db.backref('posts', lazy=True))

# Get all posts with user information
posts = Post.query.join(User).all()

Aggregations:

from sqlalchemy import func

user_count = db.session.query(func.count(User.id)).scalar()
average_posts = db.session.query(func.avg(func.count(Post.id))).join(User).group_by(User.id).scalar()

These techniques help you build more efficient and powerful applications.

Testing Your Application

Testing is a critical part of application development. When testing Flask applications with SQLite, you can use an in-memory database for faster tests.

In your test setup:

import unittest
from app import create_app, db

class TestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app('testing')
        self.app_context = self.app.app_context()
        self.app_context.push()
        db.create_all()

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.app_context.pop()

    def test_user_creation(self):
        from models import User
        user = User(username='testuser', email='test@example.com')
        db.session.add(user)
        db.session.commit()
        self.assertEqual(User.query.count(), 1)

Create a testing configuration in config.py:

class TestingConfig(Config):
    TESTING = True
    SQLALCHEMY_DATABASE_URI = 'sqlite:///:memory:'

This uses an in-memory SQLite database that's created and destroyed for each test, ensuring tests don't interfere with each other.

Deployment Considerations

When deploying your Flask application with SQLite, remember that SQLite is serverless and stores the database in a file on disk. This has implications for deployment:

• The database file must be writable by the web server process
• Concurrent writes are limited (SQLite handles one writer at a time)
• File system permissions must be set correctly
• Backups are crucial—you're responsible for backing up the database file

For low to medium traffic sites, SQLite often works well. But for high-traffic applications or those requiring concurrent writes from multiple processes, you might want to consider PostgreSQL or MySQL instead.

To backup your SQLite database, you can simply copy the database file while no writes are occurring, or use the .backup command:

import sqlite3

def backup_db(source_path, dest_path):
    source = sqlite3.connect(source_path)
    backup = sqlite3.connect(dest_path)
    with backup:
        source.backup(backup)
    backup.close()
    source.close()

• Ensure the database file is writable by your web server
• Be mindful of concurrent write limitations
• Implement regular backups of your database file
• Consider alternative databases for high-traffic applications

Optimizing SQLite Performance

While SQLite is generally fast, there are several ways to optimize its performance in your Flask application:

Use connection pooling: Though SQLite is file-based, SQLAlchemy provides connection pooling. Configure it in your app:

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 10,
    'max_overflow': 20,
}

Enable WAL mode: Write-Ahead Logging can improve concurrency:

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.close()

Tune cache size: Increase SQLite's cache size for better performance:

@event.listens_for(Engine, "connect")
def set_sqlite_cache_size(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA cache_size = -10000")  # 10MB cache
    cursor.close()

These optimizations can significantly improve performance, especially for read-heavy applications.

Common Pitfalls and Solutions

When working with SQLite and Flask, you might encounter some common issues:

Database is locked errors: This happens when multiple processes try to write simultaneously. Solutions include: - Implement retry logic for write operations - Use WAL mode as mentioned above - Consider a different database if concurrent writes are frequent

Memory issues with large datasets: SQLite stores the entire database in a single file, which can become large. Regularly vacuum the database to reclaim space:

db.session.execute('VACUUM')
db.session.commit()

Migration problems: If migrations fail, you might need to manually intervene. Always backup your database before running migrations.

By understanding these potential issues and their solutions, you can build more robust Flask applications with SQLite.

Remember, SQLite is a powerful tool that's often underestimated. For many applications—especially those with low to medium traffic or single-user applications—it provides an excellent balance of simplicity, reliability, and performance. The key is understanding its strengths and limitations, and designing your application accordingly.

As you continue working with Flask and SQLite, you'll develop a sense for when SQLite is the right choice and when you might need something more robust. The patterns and techniques we've covered here will serve you well in most common scenarios.