8da149b84e
- Add asyncpg connection pool (db/pool.py) with JSONB codec registration - Add schema.sql with users, clients, dropdown_categories, export_templates, sheets tables - Add migrate_json.py one-time migration script for existing JSON data - Rewrite user_store, sheets/manager, api/clients, api/dropdowns, api/export as async DB-backed - Update all callers (auth, sheets, admin, ai_command, export) to await async functions - Add postgres:16-alpine service to docker-compose with named volume and health check - App container depends_on postgres; DATABASE_URL injected via env - Schema applied automatically on startup; global categories seeded if DB is empty Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
44 lines
1.1 KiB
Python
44 lines
1.1 KiB
Python
"""
|
|
asyncpg connection pool with JSONB codec registration.
|
|
Call init_pool() once at startup and close_pool() at shutdown.
|
|
"""
|
|
|
|
import json
|
|
import logging
|
|
import asyncpg
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
_pool: asyncpg.Pool | None = None
|
|
|
|
|
|
async def _init_conn(conn: asyncpg.Connection):
|
|
"""Register JSONB/JSON codecs so Python dicts/lists are passed transparently."""
|
|
await conn.set_type_codec('jsonb', encoder=json.dumps, decoder=json.loads, schema='pg_catalog')
|
|
await conn.set_type_codec('json', encoder=json.dumps, decoder=json.loads, schema='pg_catalog')
|
|
|
|
|
|
async def init_pool(dsn: str):
|
|
global _pool
|
|
_pool = await asyncpg.create_pool(
|
|
dsn,
|
|
min_size=2,
|
|
max_size=10,
|
|
command_timeout=30,
|
|
init=_init_conn,
|
|
)
|
|
logger.info("PostgreSQL pool initialized")
|
|
|
|
|
|
async def close_pool():
|
|
global _pool
|
|
if _pool:
|
|
await _pool.close()
|
|
_pool = None
|
|
logger.info("PostgreSQL pool closed")
|
|
|
|
|
|
def get_pool() -> asyncpg.Pool:
|
|
if _pool is None:
|
|
raise RuntimeError("Database pool not initialized — call init_pool() first")
|
|
return _pool
|