Concepts

SQL execution and transactions

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 Project settingsSQL Editor 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.

EnvironmentConnection idle timeout defaultTransaction idle timeout default
Local240 minutes120 minutes
Dev180 minutes90 minutes
Staging120 minutes60 minutes
Prod60 minutes30 minutes
Custom60 minutes30 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 statementIt is committed to the database immediatelyIt stays in the transaction
UndoNot possibleRoll back with the Rollback button
CommitNothing more to doCommitted 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.

DBMSWhen you run DDL
MySQL, MariaDB, Oracle, SnowflakeChanges not yet committed are committed along with it. The DDL cannot be undone either
Databricks, BigQueryEach DDL statement is committed immediately and cannot be undone
PostgreSQL, SQL Server, SQLiteMost 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 doDialogChoices
Close a SQL tabThe Uncommitted Transaction dialogThe Cancel, Rollback and Commit buttons
Change the tab's execution targetThe Resolve transaction before changing target dialogThe 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+S on macOS).

What gets executed

ButtonShortcutWhat runs
The Execute buttonCtrl+Enter (Cmd+Enter on macOS)The selection if there is one; otherwise the one statement at the cursor
The Run Script buttonCtrl+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's GO, Oracle's / and MySQL/MariaDB's DELIMITER are 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.

SyntaxExampleHow the value is passed
?WHERE customer_id = ?Bound
:nameWHERE status = :statusBound. 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 as now(), 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 modeServer mode
Applies toThe rows already fetchedThe whole result in the database
HowFilters and sorts on screenRuns the SQL again with conditions (WHERE, ORDER BY) added
A WHERE you type yourselfNot usedApplied
  • 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, VALUES or TABLE (one starting with another word, such as CALL or EXEC, 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.

ConditionWhy
Each column's source table is knownColumns 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 identifiedUpdate 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 againThe 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

Project settingsSQL Editor tab. These are personal settings that do not affect team members, and the default is this computer's time zone.
Column typeExampleDisplay
Types with a time zonePostgreSQL TIMESTAMPTZ, SQL Server DATETIMEOFFSET, Oracle TIMESTAMP WITH TIME ZONEConverted to the time in the chosen time zone
Types without a time zoneDATE, DATETIME, TIMESTAMPShown 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.