agilentics / boiler 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
"""Database engine factory.

Local dev:  psycopg2 over TCP (no INSTANCE_UNIX_SOCKET set)
Cloud SQL:  pg8000 over Unix socket (INSTANCE_UNIX_SOCKET set via app.yaml)

create_engine is lazy - it opens no connection until a session is used - so
importing this module never needs a live database. That is what lets the test
suite import the app and rebind db_session to an in-memory SQLite without a
Postgres anywhere in sight.

Usage:
    from utils.db_wrapper import db_session
    db_session.query(Organization).all()      # inside a Flask request
"""
import logging
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

import settings

logger = logging.getLogger(__name__)


def db_factory():
    cfg = settings.Config

    if cfg.INSTANCE_UNIX_SOCKET:
        logger.info("Creating Cloud SQL engine via Unix socket (pg8000)")
        url = (
            f"postgresql+pg8000://{cfg.DB_USER}:{cfg.DB_PASS}@/{cfg.DB_NAME}"
            f"?unix_sock=/cloudsql/{cfg.INSTANCE_UNIX_SOCKET}/.s.PGSQL.5432"
        )
    else:
        logger.info("Creating local PostgreSQL engine via TCP (psycopg2)")
        url = (
            f"postgresql+psycopg2://{cfg.DB_USER}:{cfg.DB_PASS}"
            f"@localhost:{cfg.DB_PORT}/{cfg.DB_NAME}"
        )

    # Sized against the worker's thread count, and against a budget.
    #
    # Each gunicorn thread holds one connection while it works (see gunicorn.py),
    # so the pool needs a slot per thread or threads queue on the pool instead of
    # the database - the bottleneck moves without moving. One spare for a burst,
    # and no more.
    #
    # This number and max_instances in app.yaml are ONE decision, not two:
    #
    #     (pool_size + max_overflow) x workers x max_instances <= the instance's
    #     max_connections, minus whatever else shares that instance
    #
    # Cloud SQL's default max_connections is 100 and a small instance is usually
    # shared. Raising either number without the other is what exhausts the
    # database - for every app on the instance at once, not just this one. The
    # SQLAlchemy default (5 + 10 overflow) is 15 per *process* and blows a shared
    # budget at two instances.
    threads = int(os.environ.get("WEB_THREADS", "4"))
    return create_engine(url, pool_pre_ping=True,
                         pool_size=threads, max_overflow=1,
                         # Cloud SQL drops idle connections; recycling under that
                         # keeps a pooled one from being handed out already dead.
                         pool_recycle=1800)


db_engine = db_factory()
SessionLocal = sessionmaker(bind=db_engine)

# Request-scoped session. main.py calls db_session.remove() on app-context
# teardown, so each request gets a clean session and connections go back to
# the pool. Without that teardown, scoped_session leaks a connection per request
# until the pool is exhausted.
db_session = scoped_session(SessionLocal)