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

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)

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

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

Hint

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

Practice

1. What is the main purpose of using a test database in Flask testing?

easy

A. To store user sessions permanently

B. To isolate tests so they don't affect the real data

C. To speed up the app in production

D. To replace the main database in deployment

Solution

1. Step 1: Understand test database role

   A test database is used to keep test data separate from real data to avoid unwanted changes.

2. Step 2: Identify isolation benefit

   This isolation ensures tests do not affect the real app data or state.

3. Final Answer:

   To isolate tests so they don't affect the real data -> Option B

4. Quick Check:

   Test database isolates data = C [OK]

Hint: Test DB keeps test data separate from real data [OK]

Common Mistakes:

• Thinking test DB speeds up production
• Confusing test DB with session storage
• Assuming test DB replaces main DB in deployment

2. Which Flask testing method is used to prepare the test database before each test runs?

easy

A. tearDown

B. initTest

C. setUp

D. runTest

Solution

1. Step 1: Recall Flask test lifecycle methods

   In Flask testing, setUp runs before each test to prepare the environment.

2. Step 2: Match method to database preparation

   Preparing the test database (like creating tables) happens in setUp.

3. Final Answer:

   setUp -> Option C

4. Quick Check:

   Prepare DB before test = setUp [OK]

Hint: Use setUp to prepare before each test [OK]

Common Mistakes:

• Confusing tearDown with setup
• Using runTest which is not for setup
• Assuming initTest is a Flask method

3. Given this test code snippet, what will print(len(users)) output?

def test_user_creation(self):
    user = User(name='Anna')
    db.session.add(user)
    db.session.commit()
    users = User.query.all()
    print(len(users))

medium

A. 1

B. 0

C. Error: session not initialized

D. Multiple users depending on previous tests

Solution

1. Step 1: Analyze user addition and commit

   The code adds one user and commits to the test database.

2. Step 2: Query all users after commit

   Querying all users returns a list with the one added user.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   One user added and committed = 1 [OK]

Hint: Commit before query to see added users [OK]

Common Mistakes:

• Forgetting to commit before query
• Assuming previous tests add users
• Thinking session is not initialized

4. What is wrong with this test code snippet?

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

medium

A. Missing call to create_all() in tearDown

B. tearDown should not remove session

C. drop_all() should be in setUp, not tearDown

D. No call to rollback() before drop_all()

Solution

1. Step 1: Understand tearDown purpose

   tearDown cleans up after tests by removing session and dropping tables.

2. Step 2: Identify missing rollback before drop_all()

   Without rollback, pending changes may not be discarded, potentially causing errors or inconsistent state.

3. Final Answer:

   No call to rollback() before drop_all() -> Option D

4. Quick Check:

   Rollback before drop_all to discard changes [OK]

Hint: Always rollback before dropping tables in tearDown [OK]

Common Mistakes:

• Thinking create_all belongs in tearDown
• Believing session removal is wrong in tearDown
• Ignoring rollback before drop_all

5. You want to test a Flask app that uses a database. Which sequence correctly sets up and cleans the test database for each test?

hard

A. Call db.create_all() in setUp, run test, then db.drop_all() in tearDown

B. Call db.drop_all() in setUp, run test, then db.create_all() in tearDown

C. Call db.session.commit() in setUp, then db.session.remove() in tearDown

D. Call db.create_all() and db.drop_all() both in setUp

Solution

1. Step 1: Understand test setup and cleanup

   Creating tables before each test ensures a fresh database state.

2. Step 2: Understand test teardown

   Dropping tables after each test cleans up to avoid data carryover.

3. Step 3: Match correct sequence

   Calling create_all() in setUp and drop_all() in tearDown is the correct pattern.

4. Final Answer:

   Call db.create_all() in setUp, run test, then db.drop_all() in tearDown -> Option A

5. Quick Check:

   Create tables before, drop after test = A [OK]

Hint: Create tables before test, drop after test [OK]

Common Mistakes:

• Dropping tables before test instead of after
• Calling create_all and drop_all both in setUp
• Confusing commit with setup/teardown