Walkthroughs

Run your first query and edit the results

You query the demo store's customer table in the SQL editor, change one customer's grade in the result grid and commit it to the database, and export the result to a CSV file. It takes about 10 minutes. The editing step actually runs an UPDATE, so do it on a practice database.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

The last step, exporting the result to a file, works only in the desktop app. In the web app, copy the result instead.

Before you start

  • You need a connection with connection details. If you do not have one, first do 'Creating a connection' in Design your first ERD and apply it to a database. The tables created there are empty, so instead of 'Editing a cell' below, try adding a row with the Add button.
  • The example customer table has the columns customer_id (primary key), email, name and grade.
  • The editing steps are easier if you know that one SQL tab is one database session and what the Auto Commit switch does (SQL execution and transactions).

Opening an SQL tab

  1. In the explorer, right-click the main schema and choose the Explorer context menuNew SQL Editor... menu. Result: The Create SQL Editor dialog opens. The Connection and Schema fields are filled in from where you right-clicked.
  2. Enter a title in the dialog.
  3. Click the OK button. Result: An SQL document is created in the project and the SQL tab opens. The execution target (6) in the toolbar shows shop-db / main, and the database session the tab uses is set to the connection and schema.

Running your first query

  1. Type the following SQL into the editor.

    SELECT customer_id, email, name, grade
    FROM customer;
    
  2. Place the cursor inside the statement. Result: A green bar appears on the left of that statement, showing which statement will run.

  3. Click the Execute button or press Ctrl+Enter (Cmd+Enter on macOS). Result: Only the statement at the cursor runs in this tab's session, and one result tab appears. Even if the tab holds several statements, the others do not run. To run them all, use the Run Script button (SQL editor).

Looking at the result

  1. Check the number of fetched rows in the status row (9). Result: The result table fetches only as many rows as chosen in the row count field (3). If there are more results, the (truncated) mark and the Load More button appear.
  2. Hover over a column header (6) to see its type and description, and click the header to sort. Result: Sorting and filtering initially work only within the fetched rows (Result grid).
  3. Click a cell. Result: Its value appears at full size in the Value panel (8) on the right.

Turning off Auto Commit

Turn off the Auto Commit switch before editing. If you edit with it on, changes are committed the moment they run and cannot be undone. With it off, executed changes stay inside a transaction, so you can look at the result and then commit or roll back.

  1. Turn off the Auto Commit switch (1) in the toolbar. Result: The Commit and Rollback buttons (2) appear, and this tab's session switches to manual commit. Other SQL tabs and other people's sessions are not affected.

Editing a cell

  1. Double-click the grade cell of the Demo Customer 02 row.
  2. Enter SILVER and press Enter. Result: The cell changes color (1), and a change summary (6) appears in the status row. Nothing has gone to the database yet. The edited value exists only on screen, and you can discard it with the Discard button (5) in the status row.

This result is editable because every column comes from the single table customer and that table's primary key, customer_id, is in the result. NeoSQL finds the row to change by its primary key value. If you leave customer_id out of the SQL, the cells cannot be edited. The full conditions are covered in Editing result data.

Previewing and executing

  1. Click the Preview Changes button in the status row. Result: The Preview Data Changes dialog opens.
  2. Check that there is one UPDATE customer SET grade = ... WHERE customer_id = ... statement. Make sure the WHERE points to a single row by its primary key.
  3. Click the Execute button. Result: The UPDATE runs in this tab's session, and NeoSQL runs the original SQL again to refresh the result. Because Auto Commit is off, the change is not committed yet; the Uncommitted mark appears in the toolbar and the TX mark on the work tab.

Committing

  1. Check in the refreshed result that grade is SILVER.
  2. If it is right, click the Commit button. If you made a mistake, click the Rollback button. Result: The Commit button makes the change permanent in the database and visible to other sessions, and the Rollback button undoes all changes in this transaction. Either way, the Uncommitted mark disappears.

If you leave a transaction uncommitted for a long time, NeoSQL may roll it back. You are also asked when you close the tab or change the execution target (SQL execution and transactions).

Editing a joined result

You can also edit the cells of a result that joins several tables. NeoSQL finds the original table of each column and runs the UPDATE against that table.

  1. Run the following SQL.

    SELECT o.order_id, o.status, c.customer_id, c.name
    FROM orders o
    JOIN customer c ON c.customer_id = o.customer_id;
    

    Result: The result contains both the primary key of orders (order_id) and the primary key of customer (customer_id), so the columns of both tables are editable. Adding, duplicating and deleting rows work only when the result comes from a single table, so those buttons do not appear for this result.

  2. Edit a status cell and a name cell, then open the preview. Result: The status cell becomes an UPDATE on orders and the name cell an UPDATE on customer, so the statements are split between the two tables.

Exporting the result

  1. Go back to the customer result tab and click the Export button in the result tools. Result: The Export Data dialog opens.
  2. In the Format step of the step indicator (1), choose CSV, then move on with the Next button while checking the options and preview.
  3. In the last step, click the Download button. Result: In the initial state (Client mode), the rows fetched on screen are saved to the file. To include rows that were not fetched, choose Server mode in the result's Filter/Sort row before exporting (Exporting results).

In the web app, the Export button only shows the This feature is only available in the desktop app. notice. Select rows, right-click and copy them with the Copy as CSV item or the Copy item.

What's next