"""Apply numbered SQL files once. Re-running records nothing new and inserts no rows.""" import logging from pathlib import Path import asyncpg logger = logging.getLogger('messenger') _MIGRATIONS = Path(__file__).resolve().parents[1] / 'migrations' _BOOTSTRAP = """ CREATE TABLE IF NOT EXISTS schema_migrations ( version TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() ) """ async def apply_migrations(pool: asyncpg.Pool) -> None: async with pool.acquire() as conn: await conn.execute(_BOOTSTRAP) rows = await conn.fetch('SELECT version FROM schema_migrations') applied = {row['version'] for row in rows} for path in sorted(_MIGRATIONS.glob('*.sql')): version = path.name if version in applied: logger.info('migration %s already applied', version) continue sql = path.read_text(encoding='utf-8') async with conn.transaction(): await conn.execute(sql) await conn.execute( 'INSERT INTO schema_migrations (version) VALUES ($1)', version, ) logger.info('applied migration %s', version)