Testing with database in Flask - Mini Project: Build & Apply

Testing with database in Flask

📖 Scenario: You are building a simple Flask app that stores user names in a database. You want to write tests to check that your app correctly adds and retrieves users from the database.

🎯 Goal: Create a Flask app with a database setup, configure a test database, write a test to add a user, and complete the test setup to run the test.

📋 What You'll Learn

Create a Flask app with SQLAlchemy database

Configure a test database URI

Write a test function to add a user to the test database

Complete the test setup with assertions and cleanup

💡 Why This Matters

🌍 Real World

Testing database operations is essential in web apps to ensure data is saved and retrieved correctly without affecting real data.

💼 Career

Many developer jobs require writing tests for database interactions to maintain app quality and prevent bugs.

1

Create Flask app and database model

Create a Flask app called app and set up SQLAlchemy with db. Define a User model with an id column as primary key and a username column as string.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Your code here

Need a hint?

Use Flask(__name__) to create the app and SQLAlchemy(app) for the database. Define User as a class inheriting from db.Model.

2

Configure test database URI

Add a configuration variable app.config['TEST_DATABASE_URI'] and set it to 'sqlite:///:memory:' for an in-memory test database.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
# Your code here

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

Need a hint?

Set app.config['TEST_DATABASE_URI'] to use SQLite in-memory database for fast tests.

3

Write test function to add a user

Write a test function called test_add_user() that sets the config to the test database URI using app.config['SQLALCHEMY_DATABASE_URI'] = app.config['TEST_DATABASE_URI'], uses with app.app_context():, creates the test database with db.create_all(), adds a User with username='testuser', commits the session, and queries the user back with User.query.filter_by(username='testuser').first().

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

# Write test_add_user function below
# Your code here

Need a hint?

Inside test_add_user(), set the database URI to the test URI, use with app.app_context():, create tables, add a user, commit, and query the user.

4

Complete the test with assertions and cleanup

Add the final lines to test_add_user() to assert that queried_user is not None and that queried_user.username equals 'testuser'. Also, add db.drop_all() at the end to clean up.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

def test_add_user():
    app.config['SQLALCHEMY_DATABASE_URI'] = app.config['TEST_DATABASE_URI']
    with app.app_context():
        db.create_all()
        user = User(username='testuser')
        db.session.add(user)
        db.session.commit()
        queried_user = User.query.filter_by(username='testuser').first()
    # Add assertions and cleanup below
    # Your code here

Need a hint?

Use assert statements to check the user was added and then call db.drop_all() to remove tables after the test.