"""A bounded pool and atomic, versioned migrations.""" import hashlib import os from pathlib import Path import psycopg from psycopg.rows import dict_row from psycopg_pool import AsyncConnectionPool def setting(name): path = os.getenv(name + '_FILE') return Path(path).read_text().strip() if path else os.environ[name] def make_pool(): return AsyncConnectionPool(setting('DATABASE_URL'), min_size=1, max_size=10, timeout=3, max_waiting=32, open=False, kwargs={'row_factory': dict_row, 'connect_timeout': 3, 'options': '-c statement_timeout=3000 -c lock_timeout=2000 -c timezone=UTC'}) async def migrate(pool): async with pool.connection() as conn: await conn.execute('SELECT pg_advisory_xact_lock(202602)') await conn.execute('CREATE TABLE IF NOT EXISTS schema_migrations (version text PRIMARY KEY, checksum text NOT NULL)') for path in sorted((Path(__file__).parent / 'migrations').glob('*.sql')): content = path.read_text() checksum = hashlib.sha256(content.encode()).hexdigest() row = await (await conn.execute('SELECT checksum FROM schema_migrations WHERE version=%s', (path.name,))).fetchone() if row: if row['checksum'] != checksum: raise RuntimeError('Applied migration was modified') else: await conn.execute(content) await conn.execute('INSERT INTO schema_migrations VALUES (%s,%s)', (path.name, checksum))