Testing with database in Flask

Introduction

Testing with a database helps ensure your app works well with stored data. It checks if saving, reading, and updating data behaves as expected.

When you want to check if your app correctly saves user info.

When you need to verify that data updates happen without errors.

When you want to test queries that fetch data from the database.

When you want to catch bugs related to database connections or transactions.

Syntax

Flask

import unittest
from yourapp import create_app, db

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

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

Use setUp to prepare a fresh test database before each test.

Use tearDown to clean up after each test to avoid leftover data.

Examples

This test adds a user and checks if the user count is 1.

Flask

def test_add_user(self):
    user = User(username='testuser')
    db.session.add(user)
    db.session.commit()
    self.assertEqual(User.query.count(), 1)

This test saves a user and then queries to confirm it exists.

Flask

def test_query_user(self):
    user = User(username='alice')
    db.session.add(user)
    db.session.commit()
    found = User.query.filter_by(username='alice').first()
    self.assertIsNotNone(found)

Sample Program

This example creates a simple Flask app with an in-memory database. It tests adding a user and then querying that user to confirm it was saved correctly.

Flask

import unittest
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['TESTING'] = True

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)

class TestDatabase(unittest.TestCase):
    def setUp(self):
        self.app = app
        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_add_and_query_user(self):
        user = User(username='tester')
        db.session.add(user)
        db.session.commit()
        found = User.query.filter_by(username='tester').first()
        self.assertIsNotNone(found)
        self.assertEqual(found.username, 'tester')

if __name__ == '__main__':
    unittest.main()

Important Notes

Use an in-memory SQLite database for fast, isolated tests.

Always clean up the database after each test to avoid interference.

Use Flask's app.app_context() to access app resources during tests.

Summary

Testing with a database ensures your app handles data correctly.

Use setUp and tearDown to prepare and clean the test database.

Use simple queries to check if data is saved and retrieved as expected.