Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 72 ยท Databases

Flask with PostgreSQL and MySQL

Move a Flask app from SQLite to PostgreSQL or MySQL. Choose a driver, build a safe SQLAlchemy URL, tune the connection pool, run the database in Docker, and avoid dialect traps.

SQLite is perfect for learning and for small apps, but a busy production site usually wants a server database: many processes connecting at once, user accounts and permissions, replication, online backups. The two most popular open-source choices are PostgreSQL and MySQL (and its sibling MariaDB). The good news is that if you use Flask-SQLAlchemy, moving to either is mostly a change of one connection string and one installed driver. Your models and queries stay the same.

This lesson covers the parts that are not automatic: choosing a driver, building the URL safely, tuning the pool, running a database locally with Docker, and the small differences between databases that catch people out.

The browser editors cannot connect to a database server, so the examples use SQLAlchemy features that work without one (building URLs, compiling SQL for each dialect, a connection pool on a file database). The Docker and shell parts are read-only. Everything shown is what you would run on your own machine.

Drivers: the missing piece

SQLAlchemy speaks SQL but needs a driver (a DBAPI library) to talk to each server. You install the driver separately and name it in the URL.

Database Common driver Install URL scheme
PostgreSQL psycopg 3 pip install "psycopg[binary]" postgresql+psycopg://
PostgreSQL psycopg2 (older) pip install psycopg2-binary postgresql+psycopg2:// or postgresql://
MySQL / MariaDB PyMySQL (pure Python) pip install pymysql mysql+pymysql://
MySQL mysqlclient (C, faster) pip install mysqlclient mysql+mysqldb://

Without a driver you get ModuleNotFoundError: No module named 'psycopg2' at the first connection, which is the most common first error. The -binary extras ship precompiled wheels and are convenient for development and containers; some teams build from source in production.

The connection URL

A SQLAlchemy URL has the shape dialect+driver://user:password@host:port/database?options. Parse one to see its parts:

Python Reading and building URLs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The password above contains @, / and #, which have special meaning in a URL. Pasting such a password into a string breaks the URL in confusing ways (a wrong host, or "port must be an integer"). Either percent-encode it or, better, build the URL with URL.create(...) from separate environment variables. In Flask, pass the object (or its string) to configuration:

Python
import os
from sqlalchemy.engine import URL

app.config["SQLALCHEMY_DATABASE_URI"] = URL.create(
    "postgresql+psycopg",
    username=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    host=os.environ.get("DB_HOST", "localhost"),
    port=int(os.environ.get("DB_PORT", 5432)),
    database=os.environ["DB_NAME"],
)

Never commit the password. Use environment variables or a secret store, as in Configuration. Remember to log the URL only with the password hidden.

Connection pooling

Opening a database connection is slow (TCP, TLS, authentication). SQLAlchemy therefore keeps a pool of open connections and lends one to each request, then takes it back. Tuning the pool matters once you have traffic, because a database server accepts only a limited number of connections in total (PostgreSQL's default max_connections is 100).

flowchart LR
    R1["request 1"] --> P["Connection pool<br/>pool_size=5<br/>max_overflow=10"]
    R2["request 2"] --> P
    R3["request n"] --> P
    P --> DB[("PostgreSQL / MySQL<br/>max_connections")]

Every web worker process has its own pool, so total connections = workers x (pool_size + max_overflow).

You set the options through SQLALCHEMY_ENGINE_OPTIONS:

  • pool_size: connections kept open (default 5). max_overflow: extra temporary connections allowed (default 10).
  • pool_timeout: seconds to wait for a free connection before raising an error (default 30).
  • pool_recycle: close and replace connections older than this many seconds. MySQL servers drop idle connections (wait_timeout), so set it lower than the server's value, for example 280.
  • pool_pre_ping=True: test a connection before using it, so a connection killed by a restart or firewall is replaced rather than raising an error mid-request. Recommended for most deployments.

The pool behaves the same for every database, so you can watch it work on a small file database:

Python Watch a connection pool

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The fourth request waited for pool_timeout and then failed. In production that shows up as slow pages followed by QueuePool limit of size 5 overflow 10 reached errors. The usual causes are leaked sessions or long requests, not a pool that is too small. Flask-SQLAlchemy returns the connection at the end of each request, so do not keep sessions in globals.

With gunicorn, multiply by workers: 4 workers with the defaults can open up to 4 x (5 + 10) = 60 connections. If you run many workers or containers, put a pooler such as PgBouncer (PostgreSQL) or ProxySQL (MySQL) in front of the database.

Running a database with Docker

You do not need to install a server on your laptop. Docker gives you a throwaway one that matches production. This read-only compose.yaml runs PostgreSQL next to your app:

YAML
services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: shop
      POSTGRES_PASSWORD: dev-password
      POSTGRES_DB: shop
    ports:
      - "127.0.0.1:5432:5432"      # only reachable from your own machine
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U shop"]
      interval: 5s
      retries: 10

volumes:
  pgdata:

Start it with docker compose up -d db, then connect with postgresql+psycopg://shop:[email protected]:5432/shop. For MySQL use the mysql:8.4 image with MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD, port 3306, and a pymysql URL. The named volume keeps the data when the container is recreated. Inside Compose, the app reaches the database by service name (host=db), not localhost. The Dockerizing lesson shows the app side.

Create the tables with migrations, not create_all(), on a shared database. Migrations with Flask-Migrate works the same on PostgreSQL and MySQL.

Differences that surprise people

SQLAlchemy hides most dialect differences, but you can still hit these:

  • Case sensitivity. PostgreSQL string comparisons are case-sensitive (LIKE), MySQL's default collations are not. SQLAlchemy's ilike() becomes ILIKE on PostgreSQL and lower(...) LIKE lower(...) elsewhere.
  • Identifier quoting. PostgreSQL folds unquoted names to lower case and quotes with "double quotes", MySQL quotes with `backticks`. A column named order needs quoting; SQLAlchemy does it for you.
  • Strictness. PostgreSQL rejects an over-long string or a bad type; MySQL in permissive modes may truncate silently. Leave MySQL's STRICT_TRANS_TABLES mode on.
  • Character set. Use utf8mb4 in MySQL (the utf8 alias is only 3 bytes and cannot store many emoji).
  • Types. PostgreSQL has real JSONB, arrays and enums; MySQL has JSON. Use the generic sa.JSON when you can.
  • Insert IDs and RETURNING. PostgreSQL returns new ids with RETURNING; MySQL uses lastrowid. The ORM abstracts both.
  • Locks and transactions. DDL is transactional in PostgreSQL but implicitly commits in MySQL, which matters for migrations.

You can see SQLAlchemy writing different SQL for the same Python code, without any server, by compiling a statement against each dialect:

Python One query, three dialects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the quoting of order and the different ILIKE handling. If you write raw SQL (Raw SQL with SQLAlchemy in Flask) you give up this translation and must write for your database.

Testing against the real thing

Using SQLite in tests and PostgreSQL in production hides bugs: case rules, constraints, JSON behavior and locking all differ. Run your test suite against the same engine as production, with a disposable Docker container or a CI service, at least before releases (see Testing Flask Apps).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What do you need besides SQLAlchemy to connect to PostgreSQL?

    SQLAlchemy generates SQL but delegates network communication to a DBAPI driver, which you install and select in the URL.
  2. 2. Your password contains the characters @ and /. What is the safest way to build the URL?

    URL.create escapes special characters. Raw characters like @ and / are parsed as URL syntax and break the connection string.
  3. 3. With 4 gunicorn workers and the default pool (size 5, overflow 10), how many connections can the app open at most?

    Each worker process has its own pool: 4 x (5 + 10) = 60. Compare that number with the database's max_connections.
  4. 4. Which pool option replaces connections that a restart or firewall has silently killed?

    pool_pre_ping tests each connection before use and transparently reconnects if it is dead.

Summary

  • Switching databases means installing a driver and changing the URL (postgresql+psycopg://..., mysql+pymysql://...); models and queries stay the same.
  • Build URLs with URL.create from environment variables, and hide passwords when logging.
  • Tune SQLALCHEMY_ENGINE_OPTIONS: pool_size, max_overflow, pool_recycle (MySQL), pool_pre_ping. Remember that workers multiply the connection count; use PgBouncer or ProxySQL when it grows.
  • Use Docker Compose for a local database, reach it by service name inside Compose, and use migrations for the schema.
  • Know the dialect differences (case rules, quoting, strictness, utf8mb4, JSON types) and run tests on the production engine.

Next: drop below the ORM when you need to, in Raw SQL with SQLAlchemy in Flask.