Repository: ploomber/jupysql-plugin (language: python)

Issue:
connector widget: form resets when switching db
if I fill out the form for a new connection:

![image](https://github.com/ploomber/jupysql-plugin/assets/989250/51b44a10-6d4c-4e6a-9a18-480bc938ce72)

and then switch the db from the dropdown, the form resets. it'll be better if it'd kept the values. note that some forms contain different fields (eg duckdb and sqlite only contain alias and database), so we should only fill out the ones that match


Discussion / hints:
one important detail is the alias: if the dropdown is changed, we should only keep the alias if the user modified it. if it didn't, then we should change it for the default alias that corresponds to the selected db in the dropdown

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 49f3d6de..c58a8109 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -3,6 +3,7 @@
 ## 0.2.2
 
 * Added Oracle, Microsoft SQLServer, Redshift in DB templates (#72)
+* Auto save form fields when switching connection labels (#71)
 
 ## 0.2.1
 
diff --git a/src/widgets/connector.ts b/src/widgets/connector.ts
index ad3b0bb8..e1e9a9be 100644
--- a/src/widgets/connector.ts
+++ b/src/widgets/connector.ts
@@ -90,7 +90,8 @@ export class ConnectorView extends DOMWidgetView {
 
     /**
      * Draws the connection list
-     * 
+     *
+     *
      * @param connection : The availble connections
      */
     drawConnectionsList(connections: Array<Connection>) {
@@ -115,7 +116,7 @@ export class ConnectorView extends DOMWidgetView {
 
                     <i class="restart-kernel" style = "display: none;">
                         * No connections found in connections file. You may need to restart the kernel.
-                    </i>                    
+                    </i>
                 </div>
 
                 <div id="connectionsButtonsContainer" class="block">
@@ -129,7 +130,6 @@ export class ConnectorView extends DOMWidgetView {
                         </div>
                     </div>
                 </div>
-         
             </div>
 
             <div class="user-error-message lm-Widget p-Widget lm-Panel p-Panel jp-OutputArea-child" style = "display: none">
@@ -230,7 +230,7 @@ export class ConnectorView extends DOMWidgetView {
 
     /**
      * Connects to a database
-     * 
+     *
      * @param connection - connection object
      */
     handleConnectionClick(connection: Connection) {
@@ -316,7 +316,7 @@ export class ConnectorView extends DOMWidgetView {
         deleteConnectionMessage.id = "deleteConnectionMessage";
 
         // const warningMessage = `<h4>Delete connection from ini file</h4>
-        // <div>Are you sure you want to delete <strong>${connection["name"]}</strong>?<div> 
+        // <div>Are you sure you want to delete <strong>${connection["name"]}</strong>?<div>
         // <div>Please note that by doing so, you will permanently remove <strong>${connection["name"]}</strong> from the ini file.<div>`
 
         const warningMessage = `<h4>Delete ${connection["name"]} from ini file</h4>
@@ -378,17 +378,51 @@ export class ConnectorView extends DOMWidgetView {
 
         const connectionTemplate = this.connectionsTemplates[key];
 
+        // capture any user inputs before dropdown changed
+        const userInputData: { [key: string]: any } = {};
+
+        // get previous selected connection
+        const previousSelect = sessionStorage.getItem("selectConnection");
+
+        // when the database selection dropdown changes we need to capture any inputs
+        // entered by the user in the previous form and save them in the session.
+        // Only the fields which have been changed by the user are saved. This saved
+        // data can be used to auto-populate the new form.
+        if (previousSelect) {
+          const prevConnectionTemplate = this.connectionsTemplates[previousSelect];
+          const { fields } = prevConnectionTemplate;
+          fields.forEach((field: { id: string; default?: string }) => {
+            const id = field.id;
+            const defaultValue = field.hasOwnProperty("default")
+              ? field["default"]
+              : "";
+            const formField = <HTMLSelectElement>this.el.querySelector(`#${id}`);
+            if (formField && formField.value != defaultValue) {
+              userInputData[id] = formField.value;
+            }
+          });
+        }
+
+        // save the previous form details
+        sessionStorage.setItem("fieldInputs", JSON.stringify(userInputData));
+
+        // save new DB selection
+        sessionStorage.setItem("selectConnection", key);
+
         this.drawConnectionDetailsForm(connectionTemplate);
-    }
+
+        }
 
     /**
      * Draws a form to create or edit connections
-     * 
+     *
      * @param connectionTemplate - new connection template
      */
     drawConnectionDetailsForm(connectionTemplate: ConnectionTemplate, connectionAlias: string = "") {
         const { fields } = connectionTemplate;
 
+        const savedFields = JSON.parse(sessionStorage.getItem("fieldInputs"));
+
         const connectionFormContainer = this.el.querySelector("#connectionFormContainer");
         connectionFormContainer.innerHTML = "";
 
@@ -417,19 +451,31 @@ export class ConnectorView extends DOMWidgetView {
             input.name = field.id;
             input.className = "field";
 
+            // check for saved values
+            const savedInput = savedFields ? savedFields[field.id] || "" : "";
+
 
             // when creating the connection alias field, set the default value
             // to "default" if there are no connections, this will ensure that
             // the notebook automatically reconnects to the database if the
             // kernel is restarted
             if (field.id == "connectionName" && this.connections.length === 0) {
-                input.value = "default"
+                if (savedInput) {
+                  input.value = savedInput;
+                } else {
+                  input.value = "default";
+                }
+            }
 
-                // otherwise, set the default value if there's one
-            } else if (field.default !== undefined) {
-                input.value = field.default;
+            // check if any user inputs saved
+            else if (savedInput) {
+                input.value = savedInput;
             }
 
+           // otherwise, set the default value if there's one
+           else if (field.default !== undefined) {
+                input.value = field.default;
+            }
 
             input.setAttribute("type", field.type);
 
@@ -473,12 +519,13 @@ export class ConnectorView extends DOMWidgetView {
     }
 
     /**
-     * Submits new connection form 
-     * 
+     * Submits new connection form
+     *
      * @param event - Submit event
      */
     handleSubmitNewConnection(event: SubmitEvent) {
         event.preventDefault();
+        sessionStorage.clear()
 
         let allFieldsFilled = true;
 
@@ -520,7 +567,7 @@ export class ConnectorView extends DOMWidgetView {
 
     /**
      * Sends form data to the backend
-     * 
+     *
      * @param formData - FormData object
      */
     sendFormData(formData: { [key: string]: string }) {
@@ -536,7 +583,7 @@ export class ConnectorView extends DOMWidgetView {
 
     /**
      * Handle messages from the backend
-     * 
+     *
      * @param content - The method to invoke with data
      */
     handleMessage(content: any) {
@@ -576,7 +623,7 @@ export class ConnectorView extends DOMWidgetView {
 
     /**
      * Marks active connection button
-     * 
+     *
      * @param connectionName - Active connection name
      */
     markConnectedButton(connectionName: string) {
@@ -601,4 +648,3 @@ export class ConnectorView extends DOMWidgetView {
     }
 
 }
-


Test patch:
diff --git a/ui-tests/tests/widget.test.ts b/ui-tests/tests/widget.test.ts
index bded7d63..ff6fb4d5 100644
--- a/ui-tests/tests/widget.test.ts
+++ b/ui-tests/tests/widget.test.ts
@@ -179,6 +179,337 @@ ConnectorWidget()`)
 }
 
 
+const embeddedDatabaseLabels = [ "DuckDB", "SQLite" ];
+
+for (const label of embeddedDatabaseLabels) {
+    test(`test only relevant fields are auto-populated in embedded dbs
+          if dropdown selection changes: ${label}`, async ({ page }) => {
+        await displayWidget(page);
+
+        await page.locator("#createNewConnection").click();
+        await page
+          .locator("#selectConnection")
+          .selectOption({ label: "PostgreSQL" });
+
+        await page.locator("#connectionName").fill("somealias");
+        await page.locator("#username").fill("someuser");
+        await page.locator("#password").fill("somepassword");
+        await page.locator("#host").fill("localhost");
+        await page.locator("#port").fill("3308");
+        await page.locator("#database").fill("somedb");
+        await page.locator("#selectConnection").selectOption({ label: label });
+
+        expect(
+          await page.locator(`#connectionName`).evaluate((select) => select.value)
+        ).toBe("somealias");
+
+        expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("somedb");
+
+        const username = page.locator("#username");
+        expect(await username.count()).toBe(0);
+
+        const password = page.locator("#password");
+        expect(await password.count()).toBe(0);
+
+        const host = page.locator("#host");
+        expect(await host.count()).toBe(0);
+
+        const port = page.locator("#port");
+        expect(await port.count()).toBe(0);
+      });
+}
+
+
+const autoPopulatedFields = [
+  { label: "MySQL", port: "3306" },
+  { label: "MariaDB", port: "3306" },
+  { label: "Snowflake", port: "443" },
+  { label: "Oracle", port: "1521" },
+  { label: "MSSQL", port: "1433" },
+  { label: "Redshift", port: "5439" }
+];
+
+for (const { label, port } of autoPopulatedFields) {
+      test(`test fields are auto-populated if dropdown selection changes: ${label}`, async ({
+        page,
+      }) => {
+            await displayWidget(page);
+
+            await page.locator("#createNewConnection").click();
+            await page
+              .locator("#selectConnection")
+              .selectOption({ label: "PostgreSQL" });
+            await page.locator("#username").fill("someuser");
+            await page.locator("#password").fill("somepassword");
+            await page.locator("#host").fill("localhost");
+            await page.locator("#port").fill("5432");
+            await page.locator("#database").fill("somedb");
+            await page.locator("#selectConnection").selectOption({ label: label });
+
+            expect(await page.locator(`#port`).evaluate((select) => select.value)).toBe(
+              port
+            );
+            expect(
+              await page.locator(`#connectionName`).evaluate((select) => select.value)
+            ).toBe("default");
+            expect(
+              await page.locator(`#username`).evaluate((select) => select.value)
+            ).toBe("someuser");
+            expect(
+              await page.locator(`#password`).evaluate((select) => select.value)
+            ).toBe("somepassword");
+            expect(await page.locator(`#host`).evaluate((select) => select.value)).toBe(
+              "localhost"
+            );
+            expect(
+              await page.locator(`#database`).evaluate((select) => select.value)
+            ).toBe("somedb");
+      });
+}
+
+const autoPopulatedFieldsExistingConnection = [
+  { label: "MySQL", connectionName: "mysql", port: "3306" },
+  { label: "MariaDB", connectionName: "mariadb", port: "3306" },
+  { label: "Snowflake", connectionName: "snowflake", port: "443" },
+  { label: "Oracle", connectionName: "oracle", port: "1521" },
+  { label: "MSSQL", connectionName: "mssql", port: "1433" },
+  { label: "Redshift", connectionName: "redshift", port: "5439" },
+];
+
+for (const { label, connectionName, port } of autoPopulatedFieldsExistingConnection) {
+   test(`test fields are auto-populated if dropdown selection changes, existing connection
+       present, default DB alias displayed: ${label}`, async ({
+        page,
+      }) => {
+        await createNewNotebook(page);
+
+        await page.notebook.enterCellEditingMode(0);
+        const cell = await page.notebook.getCell(0);
+        await cell?.type(`
+from pathlib import Path
+Path('connections.ini').write_text("""
+[first]
+drivername = sqlite
+database = :memory:
+
+[second]
+drivername = sqlite
+database = :memory:
+""")
+%load_ext sql
+%config SqlMagic.dsn_filename = 'connections.ini'
+from jupysql_plugin.widgets import ConnectorWidget
+ConnectorWidget()`);
+        await page.notebook.run();
+
+        await page.locator("#createNewConnection").click();
+        await page
+          .locator("#selectConnection")
+          .selectOption({ label: "PostgreSQL" });
+        await page.locator("#username").fill("someuser");
+        await page.locator("#password").fill("somepassword");
+        await page.locator("#host").fill("localhost");
+        await page.locator("#port").fill("5432");
+        await page.locator("#database").fill("somedb");
+        await page.locator("#selectConnection").selectOption({ label: label });
+
+        expect(
+          await page.locator(`#connectionName`).evaluate((select) => select.value)
+        ).toBe(connectionName);
+        expect(await page.locator(`#port`).evaluate((select) => select.value)).toBe(
+          port);
+        expect(
+          await page.locator(`#username`).evaluate((select) => select.value)
+        ).toBe("someuser");
+        expect(
+          await page.locator(`#password`).evaluate((select) => select.value)
+        ).toBe("somepassword");
+        expect(await page.locator(`#host`).evaluate((select) => select.value)).toBe("localhost");
+        expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("somedb");
+      });
+}
+
+const autoPopulatedFieldsAliasModified = [
+  { label: "MySQL", port: "3306" },
+  { label: "MariaDB", port: "3306" },
+  { label: "Snowflake", port: "443" },
+  { label: "Oracle", port: "1521" },
+  { label: "MSSQL", port: "1433" },
+  { label: "Redshift", port: "5439" },
+];
+
+for (const { label, port } of autoPopulatedFieldsAliasModified) {
+   test(`test fields are auto-populated if dropdown selection changes and default
+      alias modified by user: ${label}`, async ({
+        page,
+      }) => {
+        await displayWidget(page);
+
+        await page.locator("#createNewConnection").click();
+        await page
+          .locator("#selectConnection")
+          .selectOption({ label: "PostgreSQL" });
+        await page.locator("#connectionName").fill("somealias");
+        await page.locator("#username").fill("someuser");
+        await page.locator("#password").fill("somepassword");
+        await page.locator("#host").fill("localhost");
+        await page.locator("#port").fill("5432");
+        await page.locator("#database").fill("somedb");
+        await page.locator("#selectConnection").selectOption({ label: label });
+
+        expect(
+          await page.locator(`#connectionName`).evaluate((select) => select.value)
+        ).toBe("somealias");
+        expect(await page.locator(`#port`).evaluate((select) => select.value)).toBe(
+          port
+        );
+        expect(
+          await page.locator(`#username`).evaluate((select) => select.value)
+        ).toBe("someuser");
+        expect(
+          await page.locator(`#password`).evaluate((select) => select.value)
+        ).toBe("somepassword");
+        expect(await page.locator(`#host`).evaluate((select) => select.value)).toBe(
+          "localhost"
+        );
+        expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("somedb");
+      });
+}
+
+const autoPopulatedFieldsAliasModifiedExistingConnection = [
+  { label: "MySQL", port: "3306" },
+  { label: "MariaDB", port: "3306" },
+  { label: "Snowflake", port: "443" },
+  { label: "Oracle", port: "1521" },
+  { label: "MSSQL", port: "1433" },
+  { label: "Redshift", port: "5439" }
+];
+
+for (const { label, port }
+  of autoPopulatedFieldsAliasModifiedExistingConnection) {
+   test(`test fields are auto-populated if dropdown selection changes, there is an
+            existing connection and default alias modified by user: ${label}`, async ({
+        page,
+      }) => {
+        await createNewNotebook(page);
+
+        await page.notebook.enterCellEditingMode(0);
+        const cell = await page.notebook.getCell(0);
+        await cell?.type(`
+from pathlib import Path
+Path('connections.ini').write_text("""
+[first]
+drivername = sqlite
+database = :memory:
+
+[second]
+drivername = sqlite
+database = :memory:
+""")
+%load_ext sql
+%config SqlMagic.dsn_filename = 'connections.ini'
+from jupysql_plugin.widgets import ConnectorWidget
+ConnectorWidget()`);
+        await page.notebook.run();
+
+        await page.locator("#createNewConnection").click();
+        await page
+          .locator("#selectConnection")
+          .selectOption({ label: "PostgreSQL" });
+        await page.locator("#connectionName").fill("somealias");
+        await page.locator("#username").fill("someuser");
+        await page.locator("#password").fill("somepassword");
+        await page.locator("#host").fill("localhost");
+        await page.locator("#port").fill("5432");
+        await page.locator("#database").fill("somedb");
+        await page.locator("#selectConnection").selectOption({ label: label });
+
+        expect(
+          await page.locator(`#connectionName`).evaluate((select) => select.value)
+        ).toBe("somealias");
+        expect(await page.locator(`#port`).evaluate((select) => select.value)).toBe(
+          port
+        );
+        expect(
+          await page.locator(`#username`).evaluate((select) => select.value)
+        ).toBe("someuser");
+        expect(
+          await page.locator(`#password`).evaluate((select) => select.value)
+        ).toBe("somepassword");
+        expect(await page.locator(`#host`).evaluate((select) => select.value)).toBe(
+          "localhost"
+        );
+        expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("somedb");
+      });
+}
+
+const databaseLabels = ["MySQL", "MariaDB", "Snowflake", "Oracle", "MSSQL", "Redshift"];
+
+for (const label of databaseLabels) {
+   test(`test fields are auto-populated if dropdown selection changes and default
+      port modified by user: ${label}`, async ({
+        page,
+      }) => {
+        await displayWidget(page);
+
+        await page.locator("#createNewConnection").click();
+        await page
+          .locator("#selectConnection")
+          .selectOption({ label: "PostgreSQL" });
+        await page.locator("#username").fill("someuser");
+        await page.locator("#password").fill("somepassword");
+        await page.locator("#host").fill("localhost");
+        await page.locator("#port").fill("3308");
+        await page.locator("#database").fill("somedb");
+        await page.locator("#selectConnection").selectOption({ label: label });
+
+        expect(
+          await page.locator(`#connectionName`).evaluate((select) => select.value)
+        ).toBe("default");
+        expect(await page.locator(`#port`).evaluate((select) => select.value)).toBe(
+          "3308"
+        );
+        expect(
+          await page.locator(`#username`).evaluate((select) => select.value)
+        ).toBe("someuser");
+        expect(
+          await page.locator(`#password`).evaluate((select) => select.value)
+        ).toBe("somepassword");
+        expect(await page.locator(`#host`).evaluate((select) => select.value)).toBe(
+          "localhost"
+        );
+        expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("somedb");
+      });
+}
+
+
+test('test user inputs discarded after create button clicked', async ({ page }) => {
+    await displayWidget(page);
+
+    await page.locator('#createNewConnection').click();
+    await page.locator('#selectConnection').selectOption({ label: 'DuckDB' });
+    await page.locator('#database').fill('duck.db');
+    await page.locator('#createConnectionFormButton').click();
+
+    // Create new connection again
+    await page.locator('#createNewConnection').click();
+    await page.locator('#selectConnection').selectOption({ label: 'PostgreSQL' });
+
+    expect(
+          await page.locator(`#database`).evaluate((select) => select.value)
+        ).toBe("");
+});
+
 test('test create new connection shows error if unable to connect', async ({ page }) => {
     await displayWidget(page);