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).
| DBMS | Default port | Driver (version) | URL shape | Environment |
|---|---|---|---|---|
| PostgreSQL | 5432 | org.postgresql.Driver (42.7.3) | jdbc:postgresql://host:port/database | Web and desktop |
| MariaDB | 3306 | org.mariadb.jdbc.Driver (3.4.1) | jdbc:mariadb://host:port/database. The database can be omitted | Web and desktop |
| MySQL | 3306 | com.mysql.cj.jdbc.Driver (8.3.0). com.mysql.jdbc.Driver (5.1.49) for MySQL 5 is also included | jdbc:mysql://host:port/database. The database can be omitted | Web and desktop |
| Oracle | 1521 | oracle.jdbc.OracleDriver (23.3). Driver types thin, oci, oci8 | Service Name jdbc:oracle:thin:@//host:port/service, SID jdbc:oracle:thin:@host:port:SID | Web and desktop |
| SQL Server | 1433 | com.microsoft.sqlserver.jdbc.SQLServerDriver (12.6.1) | jdbc:sqlserver://host:port;database=database. Properties are joined with ; | Web and desktop |
| Databricks | 443 | com.databricks.client.jdbc.Driver (3.4.2) | Built by the connection dialog from the host and the SQL Warehouse | Web and desktop |
| Snowflake | 443 | net.snowflake.client.api.driver.SnowflakeDriver (4.3.4) | jdbc:snowflake://host/. The warehouse and role are added as URL parameters | Web and desktop |
| BigQuery | 443 | com.google.cloud.bigquery.jdbc.BigQueryDriver (1.6.0) | jdbc:bigquery://https://host:port | Web and desktop |
| SQLite | None | org.sqlite.JDBC (3.45.3.0) | jdbc:sqlite:file path | Desktop |
| H2 Server | 9092 | org.h2.Driver (2.1.214) | TCP jdbc:h2:tcp://host:port/database, TCP (SSL) jdbc:h2:ssl://…. An empty user means sa | Web and desktop |
| H2 Embedded | None | org.h2.Driver (2.1.214) | File jdbc:h2:file:file path, In-Memory jdbc:h2:mem:name | Desktop |
- 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),
localhostand 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
| DBMS | Hierarchy | Switching databases | Switching the session schema |
|---|---|---|---|
| PostgreSQL | Connection › database › schema | A separate connection per database | SET search_path |
| SQL Server | Connection › database › schema | USE | Uses the login user's default schema |
| Snowflake | Connection › database › schema | Yes | Yes |
| Databricks | Connection › catalog › schema | Yes | Yes |
| BigQuery | Connection › project › dataset | Yes | Yes |
| MySQL, MariaDB | Connection › database (the same slot as a schema) | USE | |
| Oracle | Connection › schema (user) | ALTER SESSION SET CURRENT_SCHEMA | |
| SQLite | Connection › schema (main) | ||
| H2 | Connection › schema | SET SCHEMA |
- In SQL Server, objects in a schema other than the default must be written as
schema.tablein 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 menu and the 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.
| DBMS | Default properties |
|---|---|
| MySQL | characterEncoding=UTF-8, tinyInt1isBit=false, connectTimeout=20000 |
| MariaDB | connectTimeout=20000 |
| PostgreSQL | loginTimeout=20, connectTimeout=20 |
| Oracle | oracle.jdbc.fanEnabled=false |
| SQL Server | loginTimeout=20 |
| Snowflake | CLIENT_TELEMETRY_ENABLED=false |
| SQLite, H2 | None |
- 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=falsewhen SSL is off and you have not setencrypt. - For Oracle, the local server (the NeoSQL server in the web app) adds
remarksReporting=trueso 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
| DBMS | SSL settings |
|---|---|
| PostgreSQL, MySQL | Use all five modes and a client certificate. |
| MariaDB | Use disable, require, verify-ca or verify-full, with a client certificate. |
| SQL Server | Use disable, require or verify-full. |
| Oracle | Use tcps in the URL and a client certificate. |
| Databricks, Snowflake, BigQuery | Connect 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.
| DBMS | Under a schema | Under a database | Server objects |
|---|---|---|---|
| PostgreSQL | Foreign tables, indexes, Routines, sequences, triggers, types | Extensions, foreign servers (user mappings), foreign data wrappers | Users (roles), tablespaces, sessions, locks, global status, configuration, available extensions |
| MariaDB | Indexes, Routines, triggers, events, sequences | Users, engines, sessions, locks, global status, global variables, character sets | |
| MySQL | Indexes, Routines, triggers, events | Same as MariaDB | |
| Oracle | Indexes, 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 Server | External tables, indexes, sequences, Routines, triggers, synonyms, types | Users, external resources (external data sources, external file formats, database scoped credentials) | Logins, sessions, locks, global status, server configuration |
| SQLite | Indexes, triggers | ||
| H2 | Indexes, sequences | Sessions, global status (file DBs only), settings | |
| Databricks, Snowflake, BigQuery | Tables and views only | None |
How to use the tree and open objects is described in Object explorer and Object tabs.
DBMS-specific features
| Feature | DBMSs | Where it is described |
|---|---|---|
| RLS policies and per-role simulation | PostgreSQL (including Supabase) | RLS matrix and simulator |
| SRID, geometry type and dimension of spatial columns | All three for PostgreSQL and H2. SRID only for MySQL 8 or later and MariaDB | Table tab |
| Viewing and entering spatial values (WKT) | PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, H2 | Editing result data |
| User-defined types | PostgreSQL (enum, domain, composite, range, multirange), Oracle (object, collection), SQL Server (alias types, table types; CLR types view only) | Object tabs |
| Events | MySQL, MariaDB | Object tabs |
| Packages | Oracle | Object tabs |
| Schema comparison | Between the same DBMS. PostgreSQL has extra RLS and Publication tabs | Schema compare |
Execution plans and the server monitor
| DBMS | Statement sent for the execution plan | Server monitor |
|---|---|---|
| MySQL, MariaDB | EXPLAIN FORMAT=JSON | Sessions, locks, global status, settings |
| PostgreSQL | EXPLAIN (FORMAT JSON) | Sessions, locks, global status, settings |
| Oracle | EXPLAIN PLAN … FOR, then a PLAN_TABLE query | Sessions, locks, global status, settings |
| SQL Server | SET SHOWPLAN_ALL ON | Sessions, locks, global status, settings |
| H2 | EXPLAIN | Sessions, global status (file DBs only), settings |
| Databricks | EXPLAIN | |
| Snowflake | EXPLAIN USING TABULAR | |
| SQLite | EXPLAIN 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.
| DBMS | Backup tool (file) | Reload tool |
|---|---|---|
| PostgreSQL | pg_dump (.dump) | pg_restore |
| MySQL | mysqldump (.sql) | mysql |
| MariaDB | mariadb-dump (.sql) | mariadb |
| Oracle | expdp (.dmp). A Data Pump directory is required on the server | impdp |
| SQLite | sqlite3 (.sql). Use it with file databases | sqlite3 |
| H2 | No tool; SCRIPT TO (.sql) | No tool |
| SQL Server | No tool; BACKUP DATABASE (.bak, saved on the server) | No tool |
The menu, the menu, and the 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.
Related screens
- Connections and execution targets: the hierarchy, where web and desktop connect from, and private connections.
- Create and edit a connection: the dialog where you choose a DBMS and enter connection details.
- Driver management: built-in drivers and drivers you add.
- SSL/TLS and SSH tunnels: SSL, SSH tunnels and proxies.
- Object explorer: the tree and context menus per DBMS.
- Data export, import and native backup: export, import, backup and reload.