Helper to reset the sequences on a postgres database before running CI to ensure that ids will be very different between tables.

no_overlap_ids.py

from sqlalchemy import select, text


def reset_sequences(engine, schema="public", gap=10000):
    """
    Reset all sequences in a given schema to 10000, 20000, etc. so that ids won't overlap easily.
    Ensures we don't get "lucky" and crossing ids between tables works because they are both id=1,
    passing a test that should fail.
    """
    with engine.connect() as conn:
        # Get the list of all sequence names in the schema
        query = (
            select(text("sequence_name"))
            .select_from(text("information_schema.sequences"))
            .where(text("sequence_schema = :schema"))
            .offset(1)  # we can skip the first table, it gets to start from 1
        )

        sequence_names = conn.execute(query, schema=schema).scalars()

        alter_sequence_statements = [
            f"ALTER SEQUENCE {sequence_name} RESTART WITH {1 + idx * gap}"
            for idx, sequence_name in enumerate(sequence_names)
        ]

        # Reset each sequence
        conn.execute(text(";\n".join(alter_sequence_statements)))