Files

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))