Add the `--wipe` option to `edgedb restore`

[elprans]

Restoring a backup into a non-empty database is a fairly common operation. Currently one has to make the database empty by either dropping/recreating it or migrating to an empty schema, neither of which are particularly friendly. An explicit --wipe option would be nice to have.

[divan]

There was an option allow-non-empty before, but it was removed in https://github.com/edgedb/edgedb-cli/pull/424. What was the reason?

PS. Would love to have it (or --wipe) back. I was writing restore script (to sync server's DB with local dev instance) and stuck with that. Now I'm trying to find the right way to "clean the database" - not obvious too. Found some references for edgedb database drop command in comments to some issue, but seems like it was removed too...

[tailhook]

Restoring a backup into a non-empty database is a fairly common operation. Currently one has to make the database empty by either dropping/recreating it or migrating to an empty schema, neither of which are particularly friendly. An explicit --wipe option would be nice to have.

@elprans, what is the strategy for this command? Is it "create tmp database; reconnect; drop the target one; create target one reconnect; drop tmp database"?

There was an option allow-non-empty before, but it was removed in #424. What was the reason?

It stopped working once we start having migrations. I.e. if your database is non-empty, then you have some migrations (you couldn't have any data in the database before you've created some objects). And for the dump to have some data, it has to have some migrations. And applying migrations on top of other migrations is not an options, since each chain of migrations starts from the initial.

Found some references for edgedb database drop command in comments to some issue, but seems like it was removed too...

I don't remember it removed. It probably was never implemented. We can do that. In the meantime, you can use query:

edgedb query "drop database my_database"

[divan]

@tailhook thanks for a quick response. I was about to comment that drop database doesn't work for me either, because I have only one database in instance (default edgedb, I think that would be a common case) and this command returns edgedb error: ExecutionError: cannot drop the currently open database 'edgedb'. I couldn't figure out how to run query without connecting to database.

[tailhook]

@tailhook thanks for a quick response. I was about to comment that drop database doesn't work for me either, because I have only one database in instance (default edgedb, I think that would be a common case) and this command returns edgedb error: ExecutionError: cannot drop the currently open database 'edgedb'. I couldn't figure out how to run query without connecting to database.

Currently you can't connect without choosing a database, but you can do something like this:

edgedb query "create database tmp"
edgedb -d tmp query "drop database edgedb" "create database edgedb"
edgedb query "drop database tmp"

[divan]

Thanks. That worked.

[elprans]

@elprans, what is the strategy for this command? Is it "create tmp database; reconnect; drop the target one; create target one reconnect; drop tmp database"?

Yeah. Ideally, we should implement RESET SCHEMA TO initial;, though (in a future version)

[bavellone]

edgedb query "create database tmp" edgedb -d tmp query "drop database edgedb" "create database edgedb" edgedb query "drop database tmp"

Currently struggling with restoring to the default edgedb db in dev. So much ceremony for such a simple and common use-case. Would love to be spending my time on my application instead of workarounds. Really like EdgeDB other than these rough edges.

[elprans]

The plan is to implement https://github.com/edgedb/edgedb-cli/issues/839 soon. Necessary server-side changes are already done.

[tailhook]

@elprans, what is the reason of closing this? Separate wipe command is another step anyways. Restore with --wipe makes a lot of sense anyways.