Concepts

Connections and execution targets

Everything you do with a database in NeoSQL starts with a connection. Knowing what a connection holds, where your SQL and ERDs run, and where connection credentials are stored helps you avoid surprises such as SQL running against the wrong schema, or a company database that the web app cannot see.

A connection is a DBMS plus connection details

A connection holds the type of DBMS and, if you need it, the details for reaching that database (host, port, user, password and so on). The connection details are optional. If there is no database yet, or you want to design first, you can draw an ERD with a connection that only has a DBMS selected.

TaskConnection setup
Design an ERDSelect a DBMS. Tables use the selected DBMS's types.
Bring tables from the real database into an ERDUse a connection with database connection details.
Apply an ERD design to the databaseUse a connection with database connection details.
SQL editorUse a connection with database connection details.
  • When you create a connection with the Connection... button above the explorer, it starts with only the DBMS (1) selected. The connection detail fields appear only after you click the Connect to DB button (2).
  • Saving without connection details asks you once to confirm that you can add them later. You can reopen a saved connection with theExplorer context menuEdit... menu and add connection details at any time.
  • When you add connection details later, the Attach ERDs to this connection dialog opens and asks which schema of the real database each ERD drawn with this connection should be attached to. ERDs you do not choose are left as they are.

Execution targets: connection, database and schema

SQL and ERDs are tied to one connection, and they also remember which database and schema inside it they target. The shape of this hierarchy differs by DBMS.

DBMSHierarchy of the execution target
PostgreSQL, SQL Server, SnowflakeConnection › database › schema
DatabricksConnection › catalog › schema
BigQueryConnection › project › dataset
MySQL, MariaDBConnection › database (the same slot as a schema)
OracleConnection › schema (user)
SQLite, H2Connection › schema
DocumentTied toWhere to change it
SQL tabA connection with connection details, one database (if the DBMS has that level) and one schemaThe SQL Editor Settings dialog that opens when you click the connection name in the SQL editor toolbar
ERDA connection, one database (if the DBMS has that level), and one schema or the All (allow all schemas) optionDecided when you create the ERD
  • The schemas you can choose as a target are only those selected in the Schema tab of the connection dialog. The explorer shows the same schemas.
  • An ERD is drawn within one database. You cannot create an ERD that spans several databases.
  • SQL Server cannot change the session's default schema. A SQL tab's schema is therefore fixed to the login user's default schema, and objects in other schemas must be written as schema.table in your SQL.

The web and desktop apps connect from different places

The program that actually opens the database connection is not the screen but a server running behind it. The web app and the desktop app have this server in different places.

Web appDesktop app
Where the connection is made fromThe NeoSQL serverA local server on your computer (installed with the app)
Databases it can reachDatabases reachable from the internetEvery database your computer can reach (company network, VPN, localhost, files on your computer)
SQLite, H2 Embedded, localhostNot availableAvailable
Custom JDBC driversCannot be added (bundled drivers only)Can be added
  • In the web app, trying to save or test a SQLite, H2 Embedded or localhost connection shows the Desktop-only Connection notice and stops. The NeoSQL server has neither the files on your computer nor your localhost.
  • If your database firewall only accepts connections from inside the company or from developer PCs, use the desktop app. The full comparison is in Web and desktop.

Where connection credentials are stored

Secrets such as passwords, certificates and SSH keys are stored in different places depending on the project type. Project types are explained in Sign-in and project types.

Local project (desktop)Account project
Stored inThe OS secure storage of this computer (macOS Keychain, Windows DPAPI and so on)Encrypted on the NeoSQL server
Leaves the deviceNoStored on the server, and received by the server that makes the connection (the local server on desktop, the NeoSQL server on the web) when it connects
With team membersNot applicableThe database user, password and SSL certificates are stored once on the connection and shared. SSH and proxy credentials are entered by each member
  • If the OS secure storage is not available and you save secrets, the Credentials will not be encrypted warning appears. If you continue, the secrets are stored on this computer in plain text.
  • In an account project, copying a connection does not copy secrets such as passwords and keys; enter them again in the copy. In a local project, secrets are copied too.
  • Encryption methods and zero-knowledge encryption are explained in Security and where your data goes.

Public and private connections

Connections in an account project are public by default and visible to all team members. A connection tied to the user's own computer, however, would not point to the same database on another member's computer, so it is stored as a private connection that only its creator can see. You do not choose this; it is decided automatically from the connection details.

Becomes a private connection whenExample
It is a SQLite connectionEvery SQLite file
It is an H2 file or in-memory database (H2 not in server mode)jdbc:h2:file:
The host points to this computerlocalhost, 127.0.0.1, ::1, 0.0.0.0
  • Private network addresses (10.x, 192.168.x and so on) are not treated as private connections, because team members often use the same database on the same company network.
  • If you change a public connection's details so that it becomes private, the team ERDs that used it become your personal ERDs. If there are other team members, or team ERDs that will change, you are asked to confirm before saving.
  • Local projects do not have this distinction, because they have no team members.

Secure connections: encryption is not the same as verifying the server

Turning on the SSL/TLS switch in the SSL/SSH tab of the connection dialog encrypts what you exchange with the database. Encryption alone, however, does not tell you whether the other end is really your database server: an encrypted connection can also be made with a server that intercepts the traffic. The mode decides how far each of these is checked.

ModeEncryptionServer certificate checkHost name check
DisableNoNoNo
PreferIf the server supports itNoNo
RequireYesNoNo
Verify CAYesYesNo
Verify FullYesYesYes
  • When you first turn SSL on, the mode is Require. The Require mode only encrypts. For a production database, add a certificate in the CA Certificate field and use the Verify Full mode.
  • When you connect through an IP address, an alias or an SSH tunnel, the host name in the certificate differs from the address you connect to, and the Verify Full mode fails. Use the Verify CA mode in that case. Conversely, if Verify CA works but only Verify Full fails, the problem is the host name.
  • The available modes differ by DBMS. For example, MariaDB has no Prefer mode and SQL Server has no Verify CA mode. In Oracle, tcps in the JDBC URL field decides SSL instead of a mode, and SQLite and H2 have no SSL. Differences by DBMS are covered in Database reference.

An SSH tunnel lets you reach a database that is not directly reachable from outside, by going through an SSH server (a bastion host). NeoSQL connects to the SSH server first and forwards the connection to the database address as seen from that SSH server (the Remote Host field).

  • The Strict host key checking option is off at first. While it is off, NeoSQL does not notice if the SSH server is replaced, so turn it on for production environments.
  • In the web app, the SSH tunnel is also opened by the NeoSQL server. The SSH server must be reachable from the internet.
  • Each field is explained in SSL/TLS and SSH tunnels.

Things to keep in mind

  • Changing the host or database of a saved connection shows the Change connection target confirmation. Tables that do not exist in the new database disappear from the project, and changes not yet applied to the database are discarded. This connection's ERDs are kept as they were in a backup connection. To use a different database, creating a new connection is safer.
  • Configure SSL in the SSL/SSH tab. If you put SSL-related values in the JDBC URL field or the Properties tab, a notice asks you to manage them in that tab.
  • If the web app cannot reach a database, the path from the NeoSQL server to that database (DNS, firewall, company network) is blocked. Connect to company-network databases with the desktop app.