Direct Transaction Engine Connection

Clients connect to NuoDB databases by asking APs for connections to TEs. This allows the APs to perform load-balancing and/or to direct client connections to specific TEs.

The typical connection string is:

jdbc:com.nuodb://<ap-host>:<ap-port>/database

By default, the direct connection property is set to false, indicating a connection via AP(s).

Connecting Directly to a TE

When set to true, the property indicates the Connection will be made directly to a TE. The syntax for setting up a direct JDBC connection is:

jdbc:com.nuodb://<te-host>:<te-port>/database?direct=true

To find the TE’s host and port, use the show domain command:

$ nuocmd --api-server server-0:8888 show domain
server version: 4.1.1.vee-7-10a2724dbd, server license: Community
server time: 2020-11-18T23:18:04.218, client token: a8eecfc4e85d4c2ea3689a387511851e411d8393
Servers:
  [nuoadmin-0] server-0/203.0.113.10:48005 [last_ack = 1.94] [member = ADDED] [raft_state = ACTIVE] (LEADER, Leader=nuoadmin-0, log=0/18/18) Connected *
Databases:
  test [state = RUNNING]
    [SM] server-0/203.0.113.10:48006 [start_id = 0] [server_id = nuoadmin-0] [pid = 397] [node_id = 1] [last_ack =  3.61] MONITORED:RUNNING
    [TE] server-0/203.0.113.10:48007 [start_id = 1] [server_id = nuoadmin-0] [pid = 401] [node_id = 2] [last_ack =  1.68] MONITORED:RUNNING

The JDBC URL can then be constructed using the TE’s port.

jdbc:com.nuodb://server-0:48007/test?schema=test&direct=true

Multiple TEs can be specified using a comma-separated list in the JDBC URL as follows:

jdbc:com.nuodb://<te-host1>:<te-port1>,<te-host2>:<te-por2t>,.../database?direct=true
For a list of drivers that support the use of direct=true, see section Connection Properties.

Universal SQL clients, such as DBeaver, DBVisualizer and SQuirreL, are all written in Java and support the connection URLs described by this section.

Using Load Balancing

Where possible, direct connection should be made using the load-balancing capability of the APs. This can be more robust if a particular TE is no longer available.

Simply specify a load-balancing query string. For example:

  • LBQuery=random(hostname(server-0)) would select any TE on server-0 at random.

  • LBQuery=round_robin(option(mem 8G)) would select each TE in turn that was started with the 8G memory option.

  • LBQuery=random(start-id(1)) would select the TE with start-id equal to 1. As there is only one possible match, random or round_robin are equivalent here. Note that this option is only valid as long as the specific TE is running.

The connection string to use is:

jdbc:com.nuodb://<ap-host>:<ap-port>/database?LBQuery=...

Why Direct Connection?

If the client is on a different network to the NuoDB processes, the address of the TE returned by the AP may not be accessable by the client. Instead a direct connection to the TE must be used, by-passing the APs.

  • For tunnelling, the host in the connection URL specifies localhost and the tunnel port.

  • If your TEs are only accessible via a load-balancer (typical in a Cloud and/or Kubernetes deployment), the host and port specified in the connection URL is for the load-balancer.

  • When running NuoDB in containers under Docker, the connection URL specifies localhost and whatever local port the TE is mapped to.

Running in Containers

Running NuoDB on your local machine in containers is very convenient, however the IP addresses of the containers may be on an internal network setup by Docker. Port mapping is required to access the APs and TEs (clients never connect to SMs).

Suppose you setup a TE in a container by running (on Windows replace \ by ^):

docker run -d --name test-te-1 \
   --hostname test-te-1 \
   --network nuodb-net \
   --publish 58001:48006 \
   nuodb/nuodb-ce:latest nuodocker \
       --api-server nuoadmin1:8888 \
       start te --db-name testdb \
                --server-id nuoadmin1

The TE’s port (48006) has been mapped to local port 58001. So to connect to the TE, the connection string should be:

jdbc:com.nuodb://localhost:58001/database?direct=true

If using nuosql:

nuosql database@localhost:58001 --user <user> --password <pwd> --direct

Using a Load Balancer

When running NuoDB in a Cloud, either on VMs or in Kubernetes, external clients (not running on the same network) cannot access NuoDB directly.

Instead setup a load-balancer with an external (public) IP address. The connection string should be:

jdbc:com.nuodb://<router-ip-address>:<router-port>/database?direct=true

To the client the router looks like the one and only TE, but behind the scenes it forwards your requests to one of the TEs stitting behind it.

When installing NuoDB into Kubernetes using our Helm charts:

  • Set te.externalAccess.enabled=true and te.externalAccess.internalIP=false to define the necessary load-balancer.

  • kubectl get services will show you the router and its IP address.

If you run your clients inside the same Kubernetes cluster as NuoDB, direct access is not required, connect via APs in the usual way.

SSH Tunneling

The ssh command allows you to establish a tunnel into a remote machine and to execute commands on that remote machine. The direct property enables a connection string to be set up using a tunnel you have created for a specific TE.

Direct localhost Tunnel

In this example, a direct tunnel is established between a local machine and a target Amazon EC2 NuoDB TE instance, going from port 58001 on localhost to an open port, 48006, of the EC2 host (203.0.113.251) as user ec2-user.

localhost here refers to the far end of the tunnel - all commands received at the EC2 host are forwared to port 48006 on that same host (if you compare to the jump box example below, the EC2 host is both Host A and Host B).
$ ssh -N -L 58001:localhost:48006 ec2-user@203.0.113.251

To connect: jdbc:com.nuodb://localhost:58001/database?direct=true

direct tunnel

Using a Jump Server

In this example,:

  • An Amazon EC2 instance (Host A on ec2-203-0-113-82.compute-1.amazonaws.com) acts as the jump server, accessed via user ec2-user.

  • The jump server opens an SSH connection to a known host (Host B on 203.0.113.251), that has an open port on 48006 and creates a port on your localhost on port 58001.

# ssh -N -L 58001:<Host B>:48006 ec2-user@<Host A>

$ ssh -N -L 58001:203.0.113.251:48006 ec2-user@ec2-203-0-113-82.compute-1.amazonaws.com

To connect: jdbc:com.nuodb://localhost:58001/database?direct=true

jump box tunnel