Skip to main content
The Maths Society Platform supports both PostgreSQL and SQLite databases with flexible configuration options.

​
Quick Setup

The easiest way to configure your database is using individual environment variables: 
DATABASE_TYPE=postgresql
DB_USERNAME=postgres
DB_PASSWORD=your_password
DB_HOST=localhost
DB_NAME=mathsoc

​
Database URI Priority

The platform checks for database configuration in this order:
  1. DATABASE_URL (if set, overrides all other settings)
  2. Individual variables (DATABASE_TYPE, DB_USERNAME, etc.)
  3. Defaults (PostgreSQL on localhost)

​
Configuration Options

​
Option 1: Direct DATABASE_URL

​
DATABASE_URL
string
Complete database connection URI. When set, this takes precedence over all other database settings.PostgreSQL format:
DATABASE_URL=postgresql://username:password@host:port/database
SQLite format:
DATABASE_URL=sqlite:///path/to/database.db
This is commonly set automatically by hosting platforms like Heroku, Railway, or Render.

​
Option 2: Individual Variables

​
DATABASE_TYPE
string
default:"postgresql"
Database engine to use.Supported values:
  • postgresql - Recommended for production
  • sqlite - Good for development and testing
Example:
DATABASE_TYPE=postgresql
​
DB_USERNAME
string
default:"postgres"
Database user for authentication.Example:
DB_USERNAME=mathsoc_user
Not used when DATABASE_TYPE=sqlite
​
DB_PASSWORD
string
default:""
Database password for authentication.Example:
DB_PASSWORD=secure_password_here
Never commit database passwords to version control. Always use environment variables or secrets management.
​
DB_HOST
string
default:"localhost"
Database host address.Examples:
# Local development
DB_HOST=localhost

# Remote database
DB_HOST=db.example.com

# Docker service
DB_HOST=postgres
​
DB_NAME
string
default:"myapp"
Database name to connect to.Example:
DB_NAME=mathsoc

​
Database Examples

​
PostgreSQL (Production)

Using DATABASE_URL: 
DATABASE_URL=postgresql://mathsoc_user:[email protected]:5432/mathsoc_prod
Using individual variables: 
DATABASE_TYPE=postgresql
DB_USERNAME=mathsoc_user
DB_PASSWORD=secure_pass
DB_HOST=db.example.com
DB_NAME=mathsoc_prod
Generated URI: 
postgresql://mathsoc_user:[email protected]/mathsoc_prod

​
SQLite (Development)

Using DATABASE_URL: 
DATABASE_URL=sqlite:///absolute/path/to/database.db
Using individual variables: 
DATABASE_TYPE=sqlite
DB_NAME=mathsoc_dev
Generated URI: 
sqlite:///path/to/project/mathsoc_dev.db
For SQLite, the database file is created in the project’s base directory (where config.py lives).

​
Testing Environment

The testing configuration automatically uses an in-memory SQLite database: 
SQLALCHEMY_DATABASE_URI = 'sqlite:///:memory:'
No configuration needed - perfect for fast, isolated tests.

​
Initializing the Database

Flask-Migrate uses Alembic to manage database schema migrations: 
# Set the Flask app
export FLASK_APP=run:app  # Unix/Mac
set FLASK_APP=run:app     # Windows

# Run migrations
flask db upgrade

​
Using init_db.py (Development Only)

For quick development setup with sample data: 
python init_db.py
The init_db.py script creates a default admin user with password admin123. Change this immediately and never use this script in production.

​
Database Settings

The following SQLAlchemy settings are configured automatically:
​
SQLALCHEMY_TRACK_MODIFICATIONS
boolean
default:"false"
Disabled by default to save resources. The Flask-SQLAlchemy event system is not needed for this application.
​
SQLALCHEMY_DATABASE_URI
string
Automatically generated from your environment variables using the get_database_uri() method.Environment-specific behavior:
  • Development: Uses configured database (PostgreSQL or SQLite)
  • Testing: Uses in-memory SQLite (sqlite:///:memory:)
  • Production: Uses configured database (PostgreSQL recommended)

​
Production Recommendations

Use PostgreSQL in production. SQLite is not recommended for production use due to:
  • Limited concurrent write support
  • No built-in replication
  • File-based storage limitations

​
Best Practices

  1. Use managed database services:
    • AWS RDS
    • Google Cloud SQL
    • Azure Database for PostgreSQL
    • Railway, Render, or Supabase
  2. Set DATABASE_URL via platform secrets: 
    # Don't commit this to .env
DATABASE_URL=postgresql://user:pass@host/db
  3. Enable connection pooling for high-traffic applications
  4. Run migrations before deployment: 
    flask db upgrade
  5. Set up regular backups of your production database

​
Troubleshooting

​
Connection Issues

If you can’t connect to PostgreSQL: 
# Test connection manually
psql -h localhost -U postgres -d mathsoc

# Check if PostgreSQL is running
sudo systemctl status postgresql  # Linux
brew services list               # Mac

​
Migration Issues

If migrations fail: 
# Initialize migrations (first time only)
flask db init

# Create a new migration
flask db migrate -m "Description"

# Apply migrations
flask db upgrade

​
SQLite Permissions

Ensure the application has write permissions to the database directory: 
# Check permissions
ls -la *.db

# Fix if needed
chmod 644 mathsoc.db

​
Next Steps

Environment Variables

Configure environment-specific settings

Security Settings

Set up CSP, rate limiting, and HTTPS

Build docs developers (and LLMs) love

Get started for freeTalk to us