Databases and Connections

The connection object is the link between the models and the database, and its lifecycle - when it opens, how it is shared, when it closes - is the operational core of a Peewee app. SQLite is permissive (a connection is cheap, in-process), but the networked backends (PostgreSQL, MySQL) make the lifecycle real: a connection is a server-side resource, connections are not thread-safe, and a long-running app needs a pool. This chapter covers the connection classes and their parameters, the connect/close lifecycle, the thread-safety rules, and the closing/atomic context managers that make the common patterns safe.

The Connection Classes

from peewee import SqliteDatabase, PostgresqlDatabase, MySQLDatabase

# each backend class takes the parameters its driver expects
sqlite_db = SqliteDatabase("app.db", pragma={"journal_mode": "WAL"})

postgres_db = PostgresqlDatabase(
    "appdb", user="app", password="secret",
    host="db.internal", port=5432,
)

mysql_db = MySQLDatabase(
    "appdb", user="app", password="secret",
    host="db.internal", port=3306,
)

# the model points at the connection via Meta.database
class Note(Model):
    text = TextField()
    class Meta:
        database = sqlite_db

The three backend classes - SqliteDatabase, PostgresqlDatabase, MySQLDatabase - are the connection objects, and each takes the parameters its driver needs. SQLite's extra pragma argument sets the SQLite pragmas (the journal_mode WAL is the standard durability/performance setting for a file database). The model's Meta.database binds the model to a specific connection, which is what allows multiple databases in one app (a read replica, a separate analytics database) - each model names the database it lives in, and the query goes to the right connection automatically. The single-connection case (one db in database.py, all models point at it) is the common shape, and the multi-database case is the same mechanism with more connections.

Connect, Close, and Autoconnect

db = SqliteDatabase("app.db")

# explicit lifecycle - the production shape:
db.connect()
try:
    n = Note.create(text="hello")
    print(Note.select().count())
finally:
    db.close()

# autoconnect: Peewee opens lazily on the first query if not connected,
# which is convenient for scripts and a leak risk for long-running apps
# - the first query opens a connection that nothing closes

# the closing() context manager: connect if needed, always close after
with closing(db):
    print(Note.select().count())   # db is closed when the block exits

The explicit connect()/close() pair is the production pattern, with the close in a finally (or the closing(db) context manager) so an error cannot leak the connection. Autoconnect - Peewee opening the connection on the first query if you never called connect() - is the script convenience and the long-running-app footgun, because the lazily-opened connection is never closed by the app. The closing() context manager is the safe short form: it connects if not already connected and guarantees the close on exit. The rule is the file-handling rule from the storage tutorials applied to connections: open explicitly, close on every path, and for a server let the pool own the connections entirely.

Thread Safety: The Rules

• A connection is not thread-safe. A single database connection shared across threads is a race - two threads issuing queries on the same socket corrupt each other. The rule: one connection per thread, or a pool that hands each thread its own.
• SQLite is in-process - its "connection" is a handle to a file (or memory), and the threading model is more permissive than a network backend, but the one-connection-per-thread rule still applies for correctness.
• The pool is the server answer - the playhouse-pooling chapter's PooledDatabase hands each thread a connection from the pool and recycles it, which is the standard shape for a multi-threaded web app.
• The model queries go to the connection - Note.select() uses the connection bound in Meta.database, so the thread-safety is a property of how that connection is managed, not of the query.

The thread rule is the operational core: the query API is the same whether the connection is raw or pooled, but the connection management is what makes a multi-threaded app correct. A script that uses one connection on the main thread is fine; a web server that handles requests on many threads needs the pool. The atomic() context manager (the transactions chapter) is the other lifecycle tool - it wraps a block in a transaction on the current connection - and together closing and are the two context managers that cover the connection and transaction lifecycles.

Parameters and the Environment

import os

def make_db():
    # build the connection from the environment - the database.py pattern
    url = os.environ.get("DATABASE_URL", "sqlite:///app.db")
    if url.startswith("postgres"):
        # parse the URL into the PostgresqlDatabase parameters
        return PostgresqlDatabase.from_url(url)
    if url.startswith("mysql"):
        return MySQLDatabase.from_url(url)
    return SqliteDatabase(url.split("///")[-1])

db = make_db()
# the models import this single db; switching backends is a config change

The from_url classmethod builds a connection from a database_url string (postgres://user:pass@host:port/db), which is the standard way to carry the connection config in the environment. The database.py pattern - a make_db() that reads the environment and returns the right connection object, exposed as a single db - is what makes the backend a config choice: the models are written once against db, and the environment selects SQLite for development, PostgreSQL for staging, MySQL for the other deployment. The parameters are the driver's parameters (host, port, user, password, the database name), and the URL form is just a compact encoding of them. Keeping the construction in one function is the same "one place owns the connection" rule as the project shape from the installation chapter.

Summary

The connection object - SqliteDatabase, PostgresqlDatabase, MySQLDatabase - is the link the models query through, bound per model via Meta.database. Manage the lifecycle explicitly: connect()/close() with the close in a finally, or the closing(db) context manager that guarantees it, and treat autoconnect as a script convenience, not a server pattern. Respect the thread rule - a connection is not thread-safe, one per thread, and the pool (the playhouse-pooling chapter) for a multi-threaded app. Build the connection from the environment with from_url in a single database.py function, so the backend is a config change and the models never know which database they are on. With the connection under control, the next chapter uses it to perform the CRUD that is the ORM's daily work.