Database scrubbing

[kpdemetriou]

How would one go about scrubbing an entire EdgeDB database programmatically (without actually dropping the database)? Would the following suffice? Alternatively, is it possible to connect to EdgeDB without opening a database in order to DROP the only available database programmatically?

log.warning("Dropping all EdgeDB objects due to scrubbing configuration")

drop_object_types = await client.query("SELECT name := schema::ObjectType.name FILTER name LIKE 'default::%';")
drop_scalar_types = await client.query("SELECT name := schema::ScalarType.name FILTER name LIKE 'default::%';")
drop_functions = await client.query("SELECT name := schema::Function.name FILTER name LIKE 'default::%';")
drop_aliases = await client.query("SELECT name := schema::Alias.name FILTER name LIKE 'default::%';")
drop_properties = await client.query("SELECT name := schema::Property.name FILTER name LIKE 'default::%';")
drop_constraints = await client.query("SELECT name := schema::Constraint.name FILTER name LIKE 'default::%';")
drop_annotations = await client.query("SELECT name := schema::Annotation.name FILTER name LIKE 'default::%';")
drop_extensions = await client.query("SELECT name := schema::Extension.name FILTER name LIKE 'default::%';")

pending_drop = []
pending_drop += [(name, "TYPE") for name in drop_object_types]
pending_drop += [(name, "SCALAR TYPE") for name in drop_scalar_types]
pending_drop += [(name, "FUNCTION") for name in drop_functions]
pending_drop += [(name, "ALIAS") for name in drop_aliases]
pending_drop += [(name, "ABSTRACT PROPERTY") for name in drop_properties]
pending_drop += [(name, "ABSTRACT CONSTRAINT") for name in drop_constraints]
pending_drop += [(name, "ABSTRACT ANNOTATION") for name in drop_annotations]
pending_drop += [(name, "EXTENSION") for name in drop_extensions]

while pending_drop:
    name, item_type = pending_drop.pop()

    try:
        await client.execute(f"DROP {item_type} {name};")
    except Exception as e:
        log.warning(f"Failed to drop EdgeDB {item_type.lower()} `{name}`; queuing to retry...")
        pending_drop.insert(0, (name, item_type))

[CodesInChaos]

So far, I worked around this by connecting to a different database, dropping and re-creating the target database, and then connecting the the target database.

---

The following should work similar to what you're doing:

START MIGRATION TO {}
POPULATE MIGRATION;
COMMIT MIGRATION;

But last time I tried that (about a year ago), this often failed due to EdgeDB bugs, which should be fixed nowadays.

However manual migrations like that (or your code) might mess with edgedb cli's built in migration handling.

[elprans]

Hi @kpdemetriou. The CLI migration state is only an issue if you are actually using the edgedb project / edgedb migrate workflow. If all you do is run DDL directly (including START MIGRATION) then there should be no worry and the above advice to START MIGRATION TO {} is sound.

[CodesInChaos]

@elprans What I'd like to do here is reset the CLI migration state, so it'll think it's in the initial state (since that's what migrating to an empty schema does), instead of the last migration that was executed.

(I intend to open a separate thread to discuss enabling exploratory schema changes without messing up CLI migrations, but don't have the time to write a proper post at the moment)

[kpdemetriou]

@elprans Thanks for the reply. What I was saying is that it sounds reasonable to me that a database might undergo both DDL and CLI-initiated migrations, perhaps from multiple machines, and therefore the edgedb project / edgedb migrate workflows are prone to breakage under a wider array of circumstances than just this particular scrubbing case (given my understanding).

Would it not be more robust to store the migration state server-side and have clients perform serializable migrations that update this state?

[CodesInChaos]

@kpdemetriou I think it is stored in the database, since dropping the db reset it. (there is the schema::Migration collection, but deleting that one isn't enough)

[kpdemetriou]

Right, you mentioned so in your first message. Given the persistance of the issue, EdgeDB CLI either requires additional state that is NOT stored server-side, or somehow the server-side information is underutilized or incomplete. I'm happy to raise an Issue about this, but I'll wait for @elprans to confirm that I'm not missing something obvious.