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.
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
customertable has the columnscustomer_id(primary key),email,nameandgrade. - 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
- In the explorer, right-click the
mainschema and choose the menu. Result: The Create SQL Editor dialog opens. The Connection and Schema fields are filled in from where you right-clicked. - Enter a title in the dialog.
- 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
Type the following SQL into the editor.
SELECT customer_id, email, name, grade FROM customer;Place the cursor inside the statement. Result: A green bar appears on the left of that statement, showing which statement will run.
Click the Execute button or press
Ctrl+Enter(Cmd+Enteron 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
- 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.
- 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).
- 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.
- 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
- Double-click the
gradecell of theDemo Customer 02row. - Enter
SILVERand pressEnter. 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
- Click the Preview Changes button in the status row. Result: The Preview Data Changes dialog opens.
- Check that there is one
UPDATE customer SET grade = ... WHERE customer_id = ...statement. Make sure theWHEREpoints to a single row by its primary key. - Click the Execute button. Result: The
UPDATEruns 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
- Check in the refreshed result that
gradeisSILVER. - 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.
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 ofcustomer(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.Edit a
statuscell and anamecell, then open the preview. Result: Thestatuscell becomes anUPDATEonordersand thenamecell anUPDATEoncustomer, so the statements are split between the two tables.
Exporting the result
- Go back to the
customerresult tab and click the Export button in the result tools. Result: The Export Data dialog opens. - In the Format step of the step indicator (1), choose CSV, then move on with the Next button while checking the options and preview.
- 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
- SQL editor: autocomplete, formatting, changing the execution target and saving.
- Bind parameters: run SQL with values filled into
?or:nameplaceholders. - Dangerous SQL confirmation: the confirmation shown before running statements such as an
UPDATEwithoutWHERE. - Connect to a production database safely: settings that prevent mistakes on a production database.