Configuring Database URI

Your database connection is configured via:

• SQLALCHEMY_DATABASE_URI

SQLite (easy for learning)

python

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"

Meaning:

• store the database file app.db in your project directory

Postgres (common in production)

A typical URI format:

• postgresql://username:password@host:port/database_name

Example:

python

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://myuser:mypassword@localhost:5432/mydb"

Security best practice

Never hardcode passwords in code.

Use environment variables:

python

import os
app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get("DATABASE_URL")

A note about DATABASE_URL formats

Some platforms provide DATABASE_URL like:

• postgres://...

SQLAlchemy expects postgresql://....

You may need to normalize it depending on your deployment platform.

A relative SQLite path is not relative to your working directory

This surprises people the first time they go looking for the file:

config.py

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///demo.db"

measured — where the file actually lands

uri            sqlite:///demo.db
engine.url     sqlite:///<root_path>/instance/demo.db

Flask-SQLAlchemy resolves a relative SQLite path against app.instance_path, not the current directory. That is deliberate: instance/ is the folder meant for data that must not be committed, and it keeps the database out of your package.

diagram Diagram mermaid

  flowchart TD
  U["SQLALCHEMY_DATABASE_URI"] --> S{"scheme"}
  S -->|"sqlite:/// + relative"| I["resolved inside instance/"]
  S -->|"sqlite://// + absolute"| A["exactly that path — note FOUR slashes"]
  S -->|"postgresql+psycopg://"| P["host, port, credentials"]
  S -->|"mysql+pymysql://"| M["host, port, credentials"]

form	meaning
sqlite:///demo.db	relative, lands in instance/demo.db
sqlite:////tmp/demo.db	four slashes: an absolute path
sqlite://	in-memory, discarded when the process ends
postgresql+psycopg://user:pw@host:5432/dbname	driver, credentials, host, database

Keep credentials out of the source

env.py

import os
 
app.config["SQLALCHEMY_DATABASE_URI"] = os.environ["DATABASE_URL"]

Using os.environ[...] rather than .get(...) is deliberate: a missing variable should stop the app at startup, not produce a confusing failure later against the wrong database.

A URI in a repository is a leaked password

postgresql+psycopg://user:hunter2@db.example.com/prod in a committed config file is a credential in your git history, where it stays after you delete the line. Read it from the environment, and rotate anything that has ever been committed.

Settings worth knowing

options.py

app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_pre_ping": True,      # test a pooled connection before using it
    "pool_recycle": 280,        # drop connections older than this many seconds
}

pool_pre_ping is the fix for the classic “MySQL server has gone away” after an idle period: the pool hands out a connection the server closed hours ago. Pre-ping checks it first and quietly replaces it.

See it move

sketch Where each URI form points p5.js Pause Restart

A relative SQLite path resolves inside instance/. Four slashes make it absolute. An in-memory database vanishes with the process.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. With SQLALCHEMY_DATABASE_URI set to sqlite:///demo.db, where is the file created?

   A in the current working directory B inside the application's instance/ folder C next to the module that created the Flask object D in a temporary directory
   pch.quizShowAnswer

   B — inside the application's instance/ folder — Measured: engine.url resolved to <root_path>/instance/demo.db. Flask-SQLAlchemy treats a relative SQLite path as relative to instance_path, which is the folder meant for uncommitted data.

2. What is the difference between sqlite:///demo.db and sqlite:////tmp/demo.db?

   A nothing, the extra slash is ignored B three slashes then a name is relative; four slashes introduces an absolute path C the second form is read-only D the second form is an in-memory database
   pch.quizShowAnswer

   B — three slashes then a name is relative; four slashes introduces an absolute path — The fourth slash is the leading slash of the absolute path. sqlite:// with nothing after it is an in-memory database that disappears with the process.

3. Why read the database URI with os.environ[...] rather than os.environ.get(...)?

   A get is deprecated B a missing variable should stop the app at startup rather than silently falling back to some other database C get cannot read database URLs D environ is faster
   pch.quizShowAnswer

   B — a missing variable should stop the app at startup rather than silently falling back to some other database — A silent default means the app can come up pointed at the wrong database. Failing loudly at startup is the safer behaviour, and credentials should never be committed as a fallback.