async def test_migrate_sqlite(migrations, sqlite_conn_factory,
sqlite_connection_details):
args = _create_sqlite_args(sqlite_connection_details["db_name"]) + [
"-m",
migrations["sqlite_a"],
]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
assert stdout.decode("utf-8") == ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_accounts...ok\n"
"0003_record...ok\n")
# Check tables
conn = sqlite_conn_factory()
async with conn:
table_query = Query(
"SELECT name FROM sqlite_master WHERE type='table'")
tables = await conn.fetch_all(table_query)
assert [t["name"]
for t in tables] == ["applied_migration", "account", "record"]
# Check for account records
conn = sqlite_conn_factory()
async with conn:
account_query = Query("SELECT * FROM account")
accounts = await conn.fetch_all(account_query)
assert [a["name"] for a in accounts] == ["A Star", "B East", "C Me"]
async def test_apply_migrations_successful(capsys, migrations,
mysql_connection_details,
mysql_conn_factory):
# Apply migrations
conn = mysql_conn_factory()
await apply_migrations(migrations["mysql_a"], conn)
# Check tables
conn = mysql_conn_factory()
async with conn:
table_query = Query(
"SELECT table_name FROM information_schema.tables "
"WHERE table_type = 'base table' AND table_schema = $db",
values={"db": mysql_connection_details["db_name"]},
)
tables = await conn.fetch_all(table_query)
assert sorted([t["table_name"] for t in tables]) == [
"animal",
"applied_migration",
"exhibit",
]
# Check for animal entries
conn = mysql_conn_factory()
async with conn:
animal_query = Query("SELECT * FROM animal")
animals = await conn.fetch_all(animal_query)
assert [a["name"] for a in animals] == ["bear", "penguin", "turkey"]
# Check that migrations were recorded
conn = mysql_conn_factory()
async with conn:
migrations_query = Query("SELECT * FROM applied_migration")
applied_migrations = await conn.fetch_all(migrations_query)
assert len(applied_migrations) == 4
for migration in applied_migrations:
date_applied = migration["date_applied"].replace(tzinfo=timezone.utc)
assert date_applied.isoformat(
) == f"{datetime.utcnow().isoformat()}+00:00"
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_animals...ok\n"
"0003_exhibit...ok\n"
"0004_add_exhibit_id_to_animal...ok\n")
assert captured.out == expected_output
assert captured.err == ""
async def postgresql_conn_factory(get_postgresql_conn):
try:
yield get_postgresql_conn
finally:
conn = get_postgresql_conn()
async with conn:
await conn.execute(Query("DROP SCHEMA public CASCADE"))
await conn.execute(Query("CREATE SCHEMA public"))
async def test_apply_migrations_successful(capsys, migrations,
sqlite_conn_factory):
# Apply migrations
conn = sqlite_conn_factory()
await apply_migrations(migrations["sqlite_a"], conn)
# Check tables
conn = sqlite_conn_factory()
async with conn:
table_query = Query(
"SELECT name FROM sqlite_master WHERE type='table'")
tables = await conn.fetch_all(table_query)
assert [t["name"]
for t in tables] == ["applied_migration", "account", "record"]
# Check that account has records
conn = sqlite_conn_factory()
async with conn:
account_query = Query("SELECT * FROM account")
accounts = await conn.fetch_all(account_query)
assert [a["name"] for a in accounts] == ["A Star", "B East", "C Me"]
# Check that migrations were recorded
conn = sqlite_conn_factory()
async with conn:
migrations_query = Query("SELECT * FROM applied_migration")
applied_migrations = await conn.fetch_all(migrations_query)
assert len(applied_migrations) == 3
for migration in applied_migrations:
date_applied = datetime.fromisoformat(migration["date_applied"])
assert date_applied.isoformat(
) == f"{datetime.utcnow().isoformat()}+00:00"
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_accounts...ok\n"
"0003_record...ok\n")
assert captured.out == expected_output
assert captured.err == ""
async def test_apply_migrations_dry_run(capsys, migrations,
postgresql_conn_factory):
# Apply migrations in dry run mode
conn = postgresql_conn_factory()
await apply_migrations(migrations["postgresql_a"], conn, dry_run=True)
# Check that there aren't any tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["table_name"] == "applied_migration"
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_initial_data...ok\n"
"0003_record...ok\n"
"Successfully applied migrations in dry run mode.\n")
assert captured.out == expected_output
assert captured.err == ""
async def test_migration_postgresql_dry_run(migrations,
postgresql_conn_factory,
postgresql_connection_details,
postgresql_db):
args = _create_db_args(postgresql_connection_details) + [
"-m",
migrations["postgresql_a"],
"--dry-run",
]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
# Check output
assert stdout.decode("utf-8") == (
"Applying migrations\n"
"0001_initial...ok\n"
"0002_add_initial_data...ok\n"
"0003_record...ok\n"
"Successfully applied migrations in dry run mode.\n")
# Check that no tables were created
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["table_name"] == "applied_migration"
async def test_apply_migrations_dry_run(capsys, migrations,
sqlite_conn_factory):
# Apply migrations in dry run mode
conn = sqlite_conn_factory()
await apply_migrations(migrations["sqlite_a"], conn, dry_run=True)
# Check that there aren't any tables
conn = sqlite_conn_factory()
async with conn:
table_query = Query(
"SELECT name FROM sqlite_master WHERE type='table'")
tables = await conn.fetch_all(table_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["name"] == "applied_migration"
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_accounts...ok\n"
"0003_record...ok\n"
"Successfully applied migrations in dry run mode.\n")
assert captured.out == expected_output
assert captured.err == ""
async def test_apply_migrations_dry_run(capsys, migrations,
mysql_connection_details,
mysql_conn_factory):
# Apply migrations in dry run mode
conn = mysql_conn_factory()
await apply_migrations(migrations["mysql_a"], conn, dry_run=True)
# Check that db is empty
conn = mysql_conn_factory()
async with conn:
table_query = Query(
"SELECT table_name FROM information_schema.tables "
"WHERE table_type = 'base table' AND table_schema = $db",
values={"db": mysql_connection_details["db_name"]},
)
tables = await conn.fetch_all(table_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["table_name"] == "applied_migration"
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_animals...ok\n"
"0003_exhibit...ok\n"
"0004_add_exhibit_id_to_animal...ok\n"
"Successfully applied migrations in dry run mode.\n")
assert captured.out == expected_output
assert captured.err == ""
async def test_apply_migrations_with_empty_statement_successful(
capsys, migrations, postgresql_conn_factory):
"""
If sql file ends w/ empty line, sqlparse returns an empty string as a statement.
Test that migration goes through and empty statement is filtered out
"""
conn = postgresql_conn_factory()
await apply_migrations(migrations["postgresql_b"], conn)
# Query for tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 3
for table in tables:
assert table["table_name"] in [
"applied_migration",
"state_machine",
"state_history",
]
# Check output
captured = capsys.readouterr()
expected_output = "Applying migrations\n0001_initial...ok\n"
assert captured.out == expected_output
assert captured.err == ""
async def test_migrate_mysql_dry_run(migrations, mysql_conn_factory,
mysql_connection_details):
args = _create_db_args(mysql_connection_details) + [
"-m",
migrations["mysql_a"],
"--dry-run",
]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
# Check output
assert stdout.decode(
"utf-8") == "Dry run mode is not supported with MySQL.\n"
# Check that no migrations were applied
conn = mysql_conn_factory()
async with conn:
table_query = Query(
"SELECT table_name FROM information_schema.tables "
"WHERE table_type = 'base table' AND table_schema = $db",
values={"db": mysql_connection_details["db_name"]},
)
tables = await conn.fetch_all(table_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["table_name"] == "applied_migration"
async def test_apply_migrations_none(capsys, migrations,
postgresql_conn_factory):
"""Ensure friendly message is output when migrations directory is empty"""
# Apply migrations
conn = postgresql_conn_factory()
await apply_migrations(migrations["postgresql_c"], conn)
# Check that there aren't any tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["table_name"] == "applied_migration"
# Check output
captured = capsys.readouterr()
expected_output = "No migrations to apply. Migrations directory is empty.\n"
assert captured.out == expected_output
assert captured.err == ""
async def mysql_conn_factory(get_mysql_conn):
try:
yield get_mysql_conn
finally:
conn = get_mysql_conn()
async with conn:
await conn.execute(Query(CLEANUP_QUERY))
async def test_apply_migrations_empty_or_invalid_migration(
migrations_dir,
expected_tables,
expected_output,
successful_migration_name,
capsys,
migrations,
postgresql_conn_factory,
):
# Attempt to run migrations
conn = postgresql_conn_factory()
await apply_migrations(migrations[migrations_dir], conn)
# Query for tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 2
for table in tables:
assert table["table_name"] in expected_tables
# Check output
captured = capsys.readouterr()
assert captured.out == expected_output
assert captured.err == ""
# Check that successful migrations were recorded
conn = postgresql_conn_factory()
async with conn:
migrations_query = Query("SELECT * FROM applied_migration")
applied_migrations = await conn.fetch_all(migrations_query)
assert len(applied_migrations) == 1
assert applied_migrations[0]["name"] == successful_migration_name
async def _record_migration(self, migration: Migration):
query = Query(
f"INSERT INTO {MIGRATION_TABLE_NAME} (date_applied, name) "
f"VALUES ($date, $migration_name)",
values={
"date": datetime.now(tz=timezone.utc),
"migration_name": migration.name,
},
)
await self._connection.execute(query)
async def test_migrate_mysql(migrations, mysql_conn_factory,
mysql_connection_details):
args = _create_db_args(mysql_connection_details) + [
"-m",
migrations["mysql_a"],
]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
# Check output
assert stdout.decode("utf-8") == ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_animals...ok\n"
"0003_exhibit...ok\n"
"0004_add_exhibit_id_to_animal...ok\n")
# Check tables
conn = mysql_conn_factory()
async with conn:
table_query = Query(
"SELECT table_name FROM information_schema.tables "
"WHERE table_type = 'base table' AND table_schema = $db",
values={"db": mysql_connection_details["db_name"]},
)
tables = await conn.fetch_all(table_query)
assert sorted([t["table_name"] for t in tables]) == [
"animal",
"applied_migration",
"exhibit",
]
# Check data
conn = mysql_conn_factory()
async with conn:
animal_query = Query("SELECT name FROM animal")
animals = await conn.fetch_all(animal_query)
assert [a["name"] for a in animals] == ["bear", "penguin", "turkey"]
async def test_migrate_postgresql(migrations, postgresql_conn_factory,
postgresql_connection_details,
postgresql_db):
args = _create_db_args(postgresql_connection_details) + [
"-m",
migrations["postgresql_a"],
]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
# Check output
assert stdout.decode("utf-8") == ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_initial_data...ok\n"
"0003_record...ok\n")
# Check tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert sorted([t["table_name"] for t in tables]) == [
"account",
"app_user",
"applied_migration",
"record",
]
# Check that data migration was successful
conn = postgresql_conn_factory()
async with conn:
app_users_query = Query("SELECT email FROM app_user")
app_users = await conn.fetch_all(app_users_query)
assert len(app_users) == 1
assert app_users[0]["email"] == "*****@*****.**"
async def _apply_migration_from_sql_file(self, path: str) -> bool:
with open(path, "r") as f:
contents = f.read()
statements = [s for s in sqlparse.split(contents) if s != ""]
if not statements:
raise ValueError("empty migration")
try:
for statement in statements:
await self._connection.execute(Query(statement))
except Exception as e:
logger.warning("Error running migration %s: %s", path, e)
raise MigrationFailed from e
return True
async def _migrations_to_apply(
self, migrations: List[Migration]) -> List[Migration]:
"""Takes migration paths and uses migration file names to search for entries in
'applied_migration' table
"""
to_apply = []
for migration in migrations:
query = Query(
f"SELECT id FROM {MIGRATION_TABLE_NAME} WHERE name = $migration_name",
values={"migration_name": migration.name},
)
applied_migration = await self._connection.fetch_all(query)
if not applied_migration:
to_apply.append(migration)
return to_apply
async def test_migrate_sqlite_dry_run(migrations, sqlite_conn_factory,
sqlite_connection_details):
args = _create_sqlite_args(
sqlite_connection_details["db_name"]) + ["--dry-run"]
p = Popen(args, stdout=PIPE)
stdout, _ = p.communicate()
assert stdout.decode("utf-8") == (
"Dry run mode is not currently supported with SQLite.\n")
# Check that no tables were created
conn = sqlite_conn_factory()
async with conn:
table_query = Query(
"SELECT name FROM sqlite_master WHERE type='table'")
tables = await conn.fetch_all(table_query)
assert len(tables) == 1 # applied_migration expected
assert tables[0]["name"] == "applied_migration"
async def test_apply_migrations_db_conn(migrations, postgresql_conn_factory,
postgresql_connection_details):
"""Test instantiating PostgreSQLConnection with an asyncpg connection instance"""
async with postgresql_db_conn(**postgresql_connection_details) as db_conn:
postgres_conn = PostgreSQLConnection(connection=db_conn)
await apply_migrations(migrations["postgresql_b"], postgres_conn)
# Query for tables
conn = postgresql_conn_factory()
async with conn:
tables_query = Query(
"SELECT table_name FROM information_schema.tables"
" WHERE table_schema='public' AND table_type='BASE TABLE';")
tables = await conn.fetch_all(tables_query)
assert len(tables) == 3
for table in tables:
assert table["table_name"] in [
"applied_migration",
"state_history",
"state_machine",
]
async def test_apply_migrations_successful(capsys, migrations,
postgresql_conn_factory):
"""Apply migrations and then check that friendly message is output if
migrations are up-to-date"""
# Apply migrations
conn = postgresql_conn_factory()
await apply_migrations(migrations["postgresql_a"], conn)
# Check that record table was created
record_query = Query(
"SELECT column_name FROM information_schema.columns "
"WHERE table_schema = $schema AND table_name = $table_name",
values={
"schema": "public",
"table_name": "record"
},
)
conn = postgresql_conn_factory()
async with conn:
record_columns = await conn.fetch_all(record_query)
assert len(record_columns) == 2
assert record_columns[0]["column_name"] == "id"
assert record_columns[1]["column_name"] == "user_id"
# Check that migrations were recorded
conn = postgresql_conn_factory()
async with conn:
migrations_query = Query("SELECT * FROM applied_migration")
applied_migrations = await conn.fetch_all(migrations_query)
assert len(applied_migrations) == 3
for migration in applied_migrations:
assert (migration["date_applied"].isoformat() ==
f"{datetime.utcnow().isoformat()}+00:00")
# Check output
captured = capsys.readouterr()
expected_output = ("Applying migrations\n"
"0001_initial...ok\n"
"0002_add_initial_data...ok\n"
"0003_record...ok\n")
assert captured.out == expected_output
assert captured.err == ""
# Apply migrations again - we expect friendly output to let us know that migrations
# are all up to date
conn = postgresql_conn_factory()
await apply_migrations(migrations["postgresql_a"], conn)
captured = capsys.readouterr()
expected_output = "All synced! No new migrations to apply! 🥳\n"
assert captured.out == expected_output
assert captured.err == ""
from migri.elements import Query
q1 = Query("INSERT INTO mytable (a) VALUES ($a), ($b)", values={"a": 150, "b": 300})
q2 = Query("UPDATE tbl SET info=$info WHERE id=$id", values={"id": 39, "info": "ok"})
q3 = Query("SELECT * FROM school")
q4 = Query(
"SELECT * FROM val WHERE (value < $value_a AND status = $status) "
"OR (value > $value_b AND status = $status)",
values={"value_a": 20, "value_b": 100, "status": "ok"},
)
QUERIES = [q1, q2, q3, q4]
