forked from mtusi2026/mtusi2026lab2
36 lines
1.5 KiB
Python
36 lines
1.5 KiB
Python
"""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))
|