The SQL editor opens a separate database connection for each SQL tab and handles transactions inside that connection. Knowing when changes are committed to the database, what runs when you click a button, and when you can edit results helps you avoid mistakes on a production database that cannot be undone. Which connection and schema a SQL tab runs against is explained in Connections and execution targets.
One SQL tab is one database session
Each SQL tab uses its own database session (connection). If you open two SQL tabs on the same connection, the database has two connections.
- Changes not yet committed in one tab exist only in that tab's session. Other tabs, other users and applications do not see them until you commit. Depending on the DBMS, locks taken in the meantime can make work elsewhere wait.
- Data you edit in the result grid and value filters on results also run in that tab's session.
- SQL sent by an external MCP client runs in a separate SQL tab opened for MCP, in that tab's session.
- Changing a tab's execution target (connection, database, schema) closes the previous session and opens a new one.
- Sessions are opened by the local server on your computer in the desktop app, and by the NeoSQL server in the web app.
Sessions that are not used for a while are closed. In the desktop app, you set the time per connection profile environment, between 30 and 720 minutes, in the tab. The web app has no such option; the connection idle time is fixed at 30 minutes. Connections without a profile use the values of the Custom environment.
| Environment | Connection idle timeout default | Transaction idle timeout default |
|---|---|---|
| Local | 240 minutes | 120 minutes |
| Dev | 180 minutes | 90 minutes |
| Staging | 120 minutes | 60 minutes |
| Prod | 60 minutes | 30 minutes |
| Custom | 60 minutes | 30 minutes |
Auto Commit and transactions
A SQL tab opens with the Auto Commit switch on.
| Auto Commit on (initial state) | Auto Commit off | |
|---|---|---|
| When you run a statement | It is committed to the database immediately | It stays in the transaction |
| Undo | Not possible | Roll back with the Rollback button |
| Commit | Nothing more to do | Committed only when you click the Commit button |
- Turning off the Auto Commit switch (1) shows the Commit and Rollback buttons (2).
- While there are uncommitted changes, the toolbar shows the Uncommitted badge (3) and the work tab shows the TX badge (4).
- If you turn the switch back on while there are uncommitted changes, you are asked whether to commit first and then switch.
- If a transaction stays open with no activity for the set time (the Transaction idle timeout option in the table above), NeoSQL rolls it back. If the connection drops and uncommitted changes are rolled back, a notice appears above the editor.
- BigQuery connections in normal mode commit every statement automatically, so the switch cannot be turned off.
DDL can commit your transaction
DDL such as CREATE, ALTER and DROP behaves differently with transactions depending on the DBMS. This is the same even when the Auto Commit switch is off.
| DBMS | When you run DDL |
|---|---|
| MySQL, MariaDB, Oracle, Snowflake | Changes not yet committed are committed along with it. The DDL cannot be undone either |
| Databricks, BigQuery | Each DDL statement is committed immediately and cannot be undone |
| PostgreSQL, SQL Server, SQLite | Most DDL also takes part in the transaction and can be undone until you commit |
Closing a tab or changing its execution target
NeoSQL never commits or rolls back an open transaction on its own. If a transaction is still open when you do one of the following, you are asked what to do.
| What you do | Dialog | Choices |
|---|---|---|
| Close a SQL tab | The Uncommitted Transaction dialog | The Cancel, Rollback and Commit buttons |
| Change the tab's execution target | The Resolve transaction before changing target dialog | The Cancel, Roll back and change and Commit and change buttons |
- If there is unsaved SQL, you are also asked whether to save it before the tab closes. Save a SQL document with
Ctrl+S(Cmd+Son macOS).
What gets executed
| Button | Shortcut | What runs |
|---|---|---|
| The Execute button | Ctrl+Enter (Cmd+Enter on macOS) | The selection if there is one; otherwise the one statement at the cursor |
| The Run Script button | Ctrl+Shift+Enter (Cmd+Shift+Enter on macOS) | The statements in the selection if there is one; otherwise every statement in the tab, in order |
- Statements are split at
;. Depending on the DBMS, SQL Server'sGO, Oracle's/and MySQL/MariaDB'sDELIMITERare also read as separators, and a;inside a procedure body does not split the statement. - If you select several statements and click the Execute button, the selected text is sent in one go. To run several statements one after another, use the Run Script button.
- The Run Script button creates a result tab per statement. When an error occurs, it stops there and lets you choose the Stop, Retry or Skip button in the SQL execution error dialog. Statements already run are committed if the Auto Commit switch is on; if it is off, they remain in the transaction and can be undone together.
Bind parameters
If your SQL has placeholders for values, the Bind Parameters dialog opens when you run it and asks for the values. Use them to run the same SQL several times with different values.
| Syntax | Example | How the value is passed |
|---|---|---|
? | WHERE customer_id = ? | Bound |
:name | WHERE status = :status | Bound. The same name is entered once |
MyBatis #{name} | WHERE order_id = #{orderId} | Bound |
MyBatis ${name} | ORDER BY ${column} | Inserted into the SQL text as-is |
- The dialog's preview only shows the SQL with the values filled in. Actual execution uses PreparedStatement binding, which sends the SQL and the values separately, so quotes inside a value cannot break the SQL.
${}placeholders, and values whose type you set to the Expression type, are inserted into the SQL text as-is. The dialog marks them with the Inline badge. Use them for database expressions such asnow(), and check yourself that the value is valid SQL. The Expression type is available only for?and:name.- The result tab and the dangerous SQL check use the preview statement with the values filled in.
- The values you enter are remembered only within the same SQL tab. They are gone when you close the tab.
Result sets and row counts
The result of a SELECT is fetched only up to a set number of rows at a time. Choose 200 (the default), 500, 1000 or 5000 in the row count field on the result tab bar.
- If there are more rows, the (truncated) label and the Load More button appear below the result. The Load More button runs the SQL again from the start to fetch more rows. If the data changed in the meantime, the result changes too.
- Filtering and sorting work in two ways.
| Client mode | Server mode | |
|---|---|---|
| Applies to | The rows already fetched | The whole result in the database |
| How | Filters and sorts on screen | Runs the SQL again with conditions (WHERE, ORDER BY) added |
A WHERE you type yourself | Not used | Applied |
- If the result is truncated and you filter in the Client mode, only the fetched rows are filtered.
- Features that need to run the SQL again (the Server mode, the Load More button and editing results) are available only for SELECTs confirmed not to change data when run again. For queries that advance a sequence or take locks through functions, and for results of statements other than SELECT, they are locked and only the Client mode is available. A statement whose syntax NeoSQL does not recognize is run again only when it starts with
SELECT,WITH,VALUESorTABLE(one starting with another word, such asCALLorEXEC, is locked).
When you can edit results
The result grid opens ready for editing when all of the conditions below are met. Double-click a cell to edit it.
| Condition | Why |
|---|---|
| Each column's source table is known | Columns whose source table is unknown, such as columns built from expressions or functions, cannot be edited. In a joined result, each cell changes its own source table. Adding, duplicating and deleting rows work only when the result comes from one table |
| The row to change can be identified | Update and delete statements find the row with WHERE. The primary key of the table to change must be in the result |
| The query is safe to run again | The result is queried again after the changes are applied |
- For DBMSs that do not enforce primary keys, such as Snowflake, Databricks and BigQuery, choose the Row identity columns yourself with the Edit options button.
- Your edits do not go to the database right away. The Preview Changes button opens the Preview Data Changes dialog, which shows the SQL to be run, and nothing runs until you click the Execute button. Deletes run first, then updates, then inserts.
- The changes run in this tab's session, so in a tab with the Auto Commit switch off they are committed only when you click the Commit button.
- The screen is explained in Editing result data.
Dates and time zones
Dates and times in results are shown with the Timezone option and the Timestamp Format, Date Format and Time Format options in the
tab. These are personal settings that do not affect team members, and the default is this computer's time zone.| Column type | Example | Display |
|---|---|---|
| Types with a time zone | PostgreSQL TIMESTAMPTZ, SQL Server DATETIMEOFFSET, Oracle TIMESTAMP WITH TIME ZONE | Converted to the time in the chosen time zone |
| Types without a time zone | DATE, DATETIME, TIMESTAMP | Shown exactly as stored in the database. Changing the time zone does not change them |
- When you edit results, values in columns with a time zone are written into the SQL with the chosen time zone's offset.
Things to keep in mind
- UPDATE and DELETE statements run in a tab with the Auto Commit switch on cannot be undone. When you change data in a production database, turn the switch off, run the statement, check the result and then commit.
- An open transaction left for a long time can hold locks that block other users' work, and it is rolled back after the time set in the Transaction idle timeout option.
- Changes made in the result grid go through the connection profile's protection mode but not the dangerous SQL check. On a production database, always check the preview SQL. The scope of each safeguard is explained in Safeguards for production databases.
- If the result does not include the primary key columns of the table to change, rows cannot be updated or deleted. Select the primary key columns too.
Related pages
- SQL editor: the editor, its toolbar and its shortcuts.
- Bind parameters: the dialog where you enter values.
- Result grid: result tabs, filters and row counts.
- Run your first query and edit the results: the steps to run a query, edit the results and commit.