Reference

Database reference

NeoSQL connects to 10 DBMSs. This page collects, in tables, what differs between them: the shape of the connection details, the hierarchy, the explorer objects, specific features and tools. How to create a connection is described in Create and edit a connection, and what a connection is and where it connects from in Connections and execution targets.

Supported DBMSs

All drivers are built into the app, so nothing is downloaded. Drivers of other versions can be added in the desktop app (Driver management).

DBMSDefault portDriver (version)URL shapeEnvironment
PostgreSQL5432org.postgresql.Driver (42.7.3)jdbc:postgresql://host:port/databaseWeb and desktop
MariaDB3306org.mariadb.jdbc.Driver (3.4.1)jdbc:mariadb://host:port/database. The database can be omittedWeb and desktop
MySQL3306com.mysql.cj.jdbc.Driver (8.3.0). com.mysql.jdbc.Driver (5.1.49) for MySQL 5 is also includedjdbc:mysql://host:port/database. The database can be omittedWeb and desktop
Oracle1521oracle.jdbc.OracleDriver (23.3). Driver types thin, oci, oci8Service Name jdbc:oracle:thin:@//host:port/service, SID jdbc:oracle:thin:@host:port:SIDWeb and desktop
SQL Server1433com.microsoft.sqlserver.jdbc.SQLServerDriver (12.6.1)jdbc:sqlserver://host:port;database=database. Properties are joined with ;Web and desktop
Databricks443com.databricks.client.jdbc.Driver (3.4.2)Built by the connection dialog from the host and the SQL WarehouseWeb and desktop
Snowflake443net.snowflake.client.api.driver.SnowflakeDriver (4.3.4)jdbc:snowflake://host/. The warehouse and role are added as URL parametersWeb and desktop
BigQuery443com.google.cloud.bigquery.jdbc.BigQueryDriver (1.6.0)jdbc:bigquery://https://host:portWeb and desktop
SQLiteNoneorg.sqlite.JDBC (3.45.3.0)jdbc:sqlite:file pathDesktop
H2 Server9092org.h2.Driver (2.1.214)TCP jdbc:h2:tcp://host:port/database, TCP (SSL) jdbc:h2:ssl://…. An empty user means saWeb and desktop
H2 EmbeddedNoneorg.h2.Driver (2.1.214)File jdbc:h2:file:file path, In-Memory jdbc:h2:mem:nameDesktop
  • Supabase is connected with the Supabase mode of a PostgreSQL connection.
  • The web app connects to databases from the NeoSQL server with built-in drivers. Use file databases (SQLite and H2 Embedded), localhost and private addresses in the desktop app on a device that can reach them. See Web and desktop for details.
  • The extra fields some DBMSs have in the connection dialog (Oracle role, Databricks warehouse, BigQuery service account key and so on) are described under "Connection fields" in Create and edit a connection.

Hierarchy and sessions

DBMSHierarchySwitching databasesSwitching the session schema
PostgreSQLConnection › database › schemaA separate connection per databaseSET search_path
SQL ServerConnection › database › schemaUSEUses the login user's default schema
SnowflakeConnection › database › schemaYesYes
DatabricksConnection › catalog › schemaYesYes
BigQueryConnection › project › datasetYesYes
MySQL, MariaDBConnection › database (the same slot as a schema)USE
OracleConnection › schema (user)ALTER SESSION SET CURRENT_SCHEMA
SQLiteConnection › schema (main)
H2Connection › schemaSET SCHEMA
  • In SQL Server, objects in a schema other than the default must be written as schema.table in SQL. DDL generated by NeoSQL always includes the schema name.
  • Only DBMSs with the database level have database nodes in the explorer and show the Explorer context menuCreate Schema... menu and theExplorer context menuDrop Database... menu. In Oracle, creating a database means creating a user.

How the hierarchy affects SQL tabs and ERDs is described in Connections and execution targets.

Default connection properties

The values filled in the property table of the connection dialog when you create a new connection. You can change or remove them.

DBMSDefault properties
MySQLcharacterEncoding=UTF-8, tinyInt1isBit=false, connectTimeout=20000
MariaDBconnectTimeout=20000
PostgreSQLloginTimeout=20, connectTimeout=20
Oracleoracle.jdbc.fanEnabled=false
SQL ServerloginTimeout=20
SnowflakeCLIENT_TELEMETRY_ENABLED=false
SQLite, H2None
  • If you do not set a connection timeout, the local server (the NeoSQL server in the web app) adds a value suited to the DBMS (except SQLite and H2).
  • SQL Server connects with encrypt=false when SSL is off and you have not set encrypt.
  • For Oracle, the local server (the NeoSQL server in the web app) adds remarksReporting=true so column and table descriptions are read, plus properties for read performance.
  • SSL-related properties are set on the SSL tab instead of the property table. Settings on the SSL tab take precedence over the same values in the property table.

SSL

DBMSSSL settings
PostgreSQL, MySQLUse all five modes and a client certificate.
MariaDBUse disable, require, verify-ca or verify-full, with a client certificate.
SQL ServerUse disable, require or verify-full.
OracleUse tcps in the URL and a client certificate.
Databricks, Snowflake, BigQueryConnect over TLS.

The SSL tab cannot control the MySQL 5 driver. The meaning of each mode and the per-DBMS extra settings are described in SSL/TLS and SSH tunnels.

Objects shown in the explorer

Every DBMS has tables and views under each schema (materialized views appear together with views). Functions and procedures are grouped into a single Routines node.

DBMSUnder a schemaUnder a databaseServer objects
PostgreSQLForeign tables, indexes, Routines, sequences, triggers, typesExtensions, foreign servers (user mappings), foreign data wrappersUsers (roles), tablespaces, sessions, locks, global status, configuration, available extensions
MariaDBIndexes, Routines, triggers, events, sequencesUsers, engines, sessions, locks, global status, global variables, character sets
MySQLIndexes, Routines, triggers, eventsSame as MariaDB
OracleIndexes, sequences, packages, Routines, synonyms, types, triggers, database links, scheduler (jobs, programs, schedules)Tablespaces, users, roles, sessions, locks, global status, system parameters, public synonyms, public database links
SQL ServerExternal tables, indexes, sequences, Routines, triggers, synonyms, typesUsers, external resources (external data sources, external file formats, database scoped credentials)Logins, sessions, locks, global status, server configuration
SQLiteIndexes, triggers
H2Indexes, sequencesSessions, global status (file DBs only), settings
Databricks, Snowflake, BigQueryTables and views onlyNone

How to use the tree and open objects is described in Object explorer and Object tabs.

DBMS-specific features

FeatureDBMSsWhere it is described
RLS policies and per-role simulationPostgreSQL (including Supabase)RLS matrix and simulator
SRID, geometry type and dimension of spatial columnsAll three for PostgreSQL and H2. SRID only for MySQL 8 or later and MariaDBTable tab
Viewing and entering spatial values (WKT)PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, H2Editing result data
User-defined typesPostgreSQL (enum, domain, composite, range, multirange), Oracle (object, collection), SQL Server (alias types, table types; CLR types view only)Object tabs
EventsMySQL, MariaDBObject tabs
PackagesOracleObject tabs
Schema comparisonBetween the same DBMS. PostgreSQL has extra RLS and Publication tabsSchema compare

Execution plans and the server monitor

DBMSStatement sent for the execution planServer monitor
MySQL, MariaDBEXPLAIN FORMAT=JSONSessions, locks, global status, settings
PostgreSQLEXPLAIN (FORMAT JSON)Sessions, locks, global status, settings
OracleEXPLAIN PLAN … FOR, then a PLAN_TABLE querySessions, locks, global status, settings
SQL ServerSET SHOWPLAN_ALL ONSessions, locks, global status, settings
H2EXPLAINSessions, global status (file DBs only), settings
DatabricksEXPLAIN
SnowflakeEXPLAIN USING TABULAR
SQLiteEXPLAIN QUERY PLAN

Described in Execution plan and Sessions, locks and server status.

Backup and reload

Desktop app only. For DBMSs that need a tool, that DBMS's client tool must be installed on this device.

DBMSBackup tool (file)Reload tool
PostgreSQLpg_dump (.dump)pg_restore
MySQLmysqldump (.sql)mysql
MariaDBmariadb-dump (.sql)mariadb
Oracleexpdp (.dmp). A Data Pump directory is required on the serverimpdp
SQLitesqlite3 (.sql). Use it with file databasessqlite3
H2No tool; SCRIPT TO (.sql)No tool
SQL ServerNo tool; BACKUP DATABASE (.bak, saved on the server)No tool

The Explorer context menuNative Backup... menu, the Explorer context menuData Export... menu, and the Explorer context menuData Import... menu are described in Data export, import and native backup.

Things to keep in mind

  • Use SQLite, H2 Embedded, localhost and private-address connections in the desktop app. In account projects, these are saved as private connections visible only to their creator.
  • In PostgreSQL, an error inside a transaction puts the whole transaction in a failed state. Roll back and run again.
  • MySQL, MariaDB, Oracle, H2, Snowflake and BigQuery commit an open transaction when you run DDL.
  • In SQL Server, the SQL Editor's row limit applies not only to queries but also to the number of rows changed by UPDATE and DELETE.