from pathlib import Path import asyncpg MIGRATIONS_DIR = Path(__file__).resolve().parents[2] / 'migrations' async def apply_migrations(pool: asyncpg.Pool) -> None: """Применить каждую нумерованную SQL-миграцию один раз в транзакции под advisory lock.""" async with pool.acquire() as connection: await connection.execute('SELECT pg_advisory_lock($1)', 762019002) try: await connection.execute( 'CREATE TABLE IF NOT EXISTS schema_migrations (' ' version integer PRIMARY KEY,' ' applied_at timestamptz NOT NULL DEFAULT now())' ) applied = { row['version'] for row in await connection.fetch('SELECT version FROM schema_migrations') } for path in sorted(MIGRATIONS_DIR.glob('[0-9]*_*.sql')): version = int(path.name.split('_', 1)[0]) if version in applied: continue async with connection.transaction(): await connection.execute(path.read_text(encoding='utf-8')) await connection.execute( 'INSERT INTO schema_migrations (version) VALUES ($1)', version ) finally: await connection.execute('SELECT pg_advisory_unlock($1)', 762019002)