Skip to main content

​
Overview

The Furniture Store Backend uses MySQL as its database system with SQLAlchemy as the ORM (Object-Relational Mapping) layer. The application uses PyMySQL as the MySQL database driver.

​
MySQL Connection Setup

​
Prerequisites

1

Install MySQL Server

Ensure MySQL 5.7+ or MySQL 8.0+ is installed on your system.
# Check MySQL version
mysql --version
2

Create Database

Create a database for the application:
CREATE DATABASE furniture_store_dev CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3

Create Database User

Create a dedicated user with appropriate permissions:
CREATE USER 'furniture_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON furniture_store_dev.* TO 'furniture_user'@'localhost';
FLUSH PRIVILEGES;

​
Connection String Format

The application constructs the SQLAlchemy database URI automatically from environment variables: 
SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

​
Connection String Components

​
Protocol
string
mysql+pymysql - Specifies MySQL database with PyMySQL driver
​
DB_USER
string
Database username for authentication
​
DB_PASSWORD
string
Database password for authentication
​
DB_HOST
string
Database server hostname (e.g., localhost, 127.0.0.1, or remote host)
​
DB_PORT
string
Database server port (default: 3306)
​
DB_NAME
string
Name of the database to connect to

​
Example Configurations

DB_USER=dev_user
DB_PASSWORD=dev_password
DB_HOST=localhost
DB_PORT=3306
DB_NAME=furniture_store_dev

​
SQLAlchemy Configuration

The application uses Flask-SQLAlchemy for database operations. Configuration is defined in config.py:
config.py
class Config:
    # Database connection URI
    SQLALCHEMY_DATABASE_URI = (
        f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    )
    
    # Disable modification tracking to save resources
    SQLALCHEMY_TRACK_MODIFICATIONS = False

​
Configuration Options

The database connection string. This is the primary configuration option that tells SQLAlchemy how to connect to your database.Format: dialect+driver://username:password@host:port/database
Set to False to disable Flask-SQLAlchemy’s modification tracking system.Why disable it?
  • Reduces memory overhead
  • Improves performance
  • The feature is deprecated and will be removed in future versions
This setting is recommended by Flask-SQLAlchemy documentation unless you specifically need the modification tracking feature.

​
Database Extensions Initialization

Extensions are initialized in app/extensions.py:
app/extensions.py
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from flask_wtf.csrf import CSRFProtect

db = SQLAlchemy()
migrate = Migrate()
csrf = CSRFProtect()
These extensions are then initialized with the Flask application:
app/__init__.py
from flask import Flask
from config import Config
from .extensions import csrf, db, migrate

def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)
    
    # Initialize extensions with the app
    db.init_app(app)
    migrate.init_app(app, db)
    csrf.init_app(app)
    
    # Import models to register them with SQLAlchemy
    from . import models
    
    return app
The db.init_app() pattern allows you to create the database object separately from the Flask app, enabling the application factory pattern.

​
Database Initialization

​
Creating Tables

After configuring the database connection, you need to create the database tables. The application uses Flask-Migrate (Alembic) for this: 
# Initialize migrations (first time only)
flask db init

# Create initial migration
flask db migrate -m "Initial migration"

# Apply migrations to create tables
flask db upgrade
See the Migrations page for detailed information about database migrations.
While not recommended for production, you can create tables manually using SQLAlchemy: 
from app import create_app
from app.extensions import db

app = create_app()

with app.app_context():
    db.create_all()
    print("All tables created successfully!")
Using db.create_all() does not track schema changes. Always use migrations for production applications.

​
Testing Database Connection

The application includes a built-in connection test in run.py:
run.py
from app import create_app
from app.extensions import db

app = create_app()

# Test database connection
with app.app_context():
    try:
        connection = db.engine.connect()
        print("Database connection successful!")
        connection.close()
    except Exception as e:
        print(f"Database connection failed: {e}")
Run this test: 
python run.py

​
Expected Output

Database connection successful!
 * Serving Flask app 'run'
 * Debug mode: on

​
Troubleshooting

Error: Can't connect to MySQL serverSolutions:
  • Verify MySQL server is running: sudo systemctl status mysql
  • Check DB_HOST and DB_PORT values in .env
  • Ensure firewall allows connections on port 3306
  • For remote connections, verify the host is accessible
Error: Access denied for userSolutions:
  • Verify DB_USER and DB_PASSWORD are correct
  • Check user has proper privileges: SHOW GRANTS FOR 'user'@'host';
  • Ensure user is created for correct host (e.g., localhost vs %)
  • Try connecting manually: mysql -u username -p -h host database
Error: Unknown databaseSolutions:
  • Verify database exists: SHOW DATABASES;
  • Create the database: CREATE DATABASE database_name;
  • Check DB_NAME in .env matches the actual database name
Error: No module named 'pymysql'Solution:
pip install pymysql
Or install from requirements.txt:
pip install -r requirements.txt
Error: Incorrect string value or encoding errorsSolutions:
  • Ensure database uses UTF-8: ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Add charset to connection string:
SQLALCHEMY_DATABASE_URI = (
    f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}?charset=utf8mb4"
)

​
Database Connection Pooling

SQLAlchemy automatically manages connection pooling. For production environments, you may want to configure pool settings:
config.py
class Config:
    SQLALCHEMY_DATABASE_URI = (
        f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    )
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    
    # Optional: Connection pool settings
    SQLALCHEMY_ENGINE_OPTIONS = {
        'pool_size': 10,
        'pool_recycle': 3600,
        'pool_pre_ping': True,
    }
​
pool_size
int
Number of connections to keep open (default: 5)
​
pool_recycle
int
Number of seconds after which to recycle connections (prevents timeout issues)
​
pool_pre_ping
bool
Test connections before using them (prevents “MySQL server has gone away” errors)

​
Security Best Practices

Use Strong Passwords

Always use strong, unique passwords for database users

Principle of Least Privilege

Grant only necessary permissions to application users

Secure Connections

Use SSL/TLS for remote database connections

Regular Backups

Implement automated database backup strategies

​
Next Steps

Migrations

Learn how to manage database schema changes with Flask-Migrate

Environment Setup

Configure environment variables and application settings

Build docs developers (and LLMs) love

Get started for freeTalk to us