SQLAlchemy Basics
Writing raw SQL queries works, but it gets messy fast. Flask-SQLAlchemy is an extension that gives you a clean, Pythonic way to talk to your database. Instead of writing strings of SQL, you work with Python objects and let the library handle the translation behind the scenes. It's an ORM — Object Relational Mapper — and once you get the hang of it, you'll never want to go back.
Setting Up Flask-SQLAlchemy
First, install the extension and set up your database URI. The URI tells Flask where your database lives. For development, SQLite is perfect because it's just a file on your computer and needs no server.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)That's the basic setup. The SQLALCHEMY_DATABASE_URI string format varies by database — SQLite uses sqlite:/// followed by the file path, while PostgreSQL would use something like postgresql://user:password@localhost/dbname.
Creating Tables
Once you've defined your models (which we'll cover next), you create all the tables in your database with one simple call. This is where the magic starts to happen.
with app.app_context():
db.create_all()The with app.app_context() part is important — Flask needs an application context to access the database configuration. If you're working inside a route, the context is already there. If you're running this from a script, you need to create it manually.
Why Use an ORM?
With raw SQL, you'd write something like INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'). With SQLAlchemy, you create a Python object and add it to the session. The ORM figures out the SQL for you. You also get type safety, cleaner code, and the ability to switch databases without rewriting your queries. It's a huge productivity boost.
Development vs Production
SQLite is fantastic for development — it's fast, requires no setup, and lives in a single file you can easily back up or reset. But for production, you'll want something more robust like PostgreSQL or MySQL. They handle concurrent users better, support advanced features, and scale properly. The beauty of SQLAlchemy is that you usually just change the database URI and your code works the same way.