Repository: diesel-rs/diesel (language: rust)

Issue:
`diesel database reset` errors with `Failed to execute a database query`
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:** `rustc 1.83.0-nightly (506f22b46 2024-09-19)`
- **Diesel:** `2.2.4`
- **Database:** `postgres`
- **Operating System:** Linux

### Feature Flags

- **diesel:** `["postgres"]`

## Problem Description


### What are you trying to accomplish?

Run `diesel database reset`.


### What is the expected output?

Database is successfully reset.


### What is the actual output?

```
Dropping database: postgres
Failed to execute a database query: cannot drop the currently open database
```



### Are you seeing any additional errors?

No.

### Steps to reproduce

`.env`
```bash
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/postgres"
```

`diesel.toml`
```toml
[print_schema]
file = "src/schema.rs"
custom_type_derives = ["diesel::query_builder::QueryId", "Clone"]

[migrations_directory]
dir = "migrations"
```

I only have one migration file:

`up.sql`
```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
);
```
`down.sql`
```sql
DROP TABLE IF EXISTS users;
```

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) and the [discussion forum](https://github.com/diesel-rs/diesel/discussions) for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:
Given that we have [tests](https://github.com/diesel-rs/diesel/blob/master/diesel_cli/tests/database_reset.rs) for this functionality and that these tests pass in the CI this seems to be a problem with your environment. Are you sure that your user is allowed to drop the database? Did you double check that nothing else is currently connected to the database?

Closed as this seems to be an setup issue. 
The issue was that I was using the default `postgres` database. It looks like that dropping a database requires you to connect to a different database, and `postgres` is usually chosen for this.

https://www.postgresql.org/docs/current/app-dropdb.html
> If not specified, the `postgres` database will be used; if that does not exist (or is the database being dropped), `template1` will be used

Not sure why `template1` wasn't automatically used though.
Thanks for clarifying. Yes it seems like using the Postgres database is the problematic part here. We explicitly connect to that database to drop database, so it seems to be sensible to get that error message there. It might be useful to check for this case and connect to template1 instead if the user tries to drop the Postgres database. The relevant code is here: https://github.com/diesel-rs/diesel/blob/master/diesel_cli/src/database.rs#L295-L296
I'd like to work on this please.

That would be great :+1:
@weiznich I've started working on this issue, and I think it would be better to check for the database inside the `change_database_of_url` method. What do you think?


Fix patch:
diff --git a/diesel_cli/src/database.rs b/diesel_cli/src/database.rs
index 32046ee3854d..ba11f40ab404 100644
--- a/diesel_cli/src/database.rs
+++ b/diesel_cli/src/database.rs
@@ -292,7 +292,13 @@ fn drop_database(database_url: &str) -> Result<(), crate::errors::Error> {
     match Backend::for_url(database_url) {
         #[cfg(feature = "postgres")]
         Backend::Pg => {
-            let (database, postgres_url) = change_database_of_url(database_url, "postgres")?;
+            let (current_database, _) = get_database_and_url(database_url)?;
+            let default_database = if current_database.eq("postgres") {
+                "template1"
+            } else {
+                "postgres"
+            };
+            let (database, postgres_url) = change_database_of_url(database_url, default_database)?;
             let mut conn = PgConnection::establish(&postgres_url).map_err(|e| {
                 crate::errors::Error::ConnectionError {
                     error: e,
@@ -422,6 +428,16 @@ fn change_database_of_url(
     database_url: &str,
     default_database: &str,
 ) -> Result<(String, String), crate::errors::Error> {
+    let (database, base) = get_database_and_url(database_url)?;
+    let mut new_url = base
+        .join(default_database)
+        .expect("The provided database is always valid");
+    new_url.set_query(base.query());
+    Ok((database, new_url.into()))
+}
+
+#[cfg(any(feature = "postgres", feature = "mysql"))]
+fn get_database_and_url(database_url: &str) -> Result<(String, url::Url), crate::errors::Error> {
     let base = url::Url::parse(database_url)?;
     let database = base
         .path_segments()
@@ -429,11 +445,7 @@ fn change_database_of_url(
         .last()
         .expect("The database url has at least one path segment")
         .to_owned();
-    let mut new_url = base
-        .join(default_database)
-        .expect("The provided database is always valid");
-    new_url.set_query(base.query());
-    Ok((database, new_url.into()))
+    Ok((database, base))
 }
 
 #[cfg(feature = "sqlite")]


Test patch: