Direct Transaction Engine Connection

Clients connect to NuoDB databases by asking Admin Processes (APs) for connections to Transaction engines (TEs).

  • This allows the APs to perform load-balancing and/or to direct client connections to specific TEs.

  • It also means the clients do not have to keep track of which TE processes are available. The APs do so on their behalf (since the APs have to track TEs anyway).

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

It is sometimes useful to connect directly to a specific TE, especially during development and testing. For example, to examine local System tables such as LOCALATOMS or LOCALCONNECTIONS.

The recommended way to connect to a specific TE is by using its start ID. A TE’s start ID appears in the output from nuocmd show domain:

$ nuocmd --api-server server-0:8888 show domain
server version: 6.0-1-fc6a857de9, server license: Enterprise
server time: 2023-11-18T23:18:04.218, client token: ...
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 = 12] [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 = 14] [server_id = nuoadmin-0] [pid = 401] [node_id = 2] [last_ack =  1.68] MONITORED:RUNNING

Direct connection can then be made using the load-balancing capability of the APs.

jdbc:com.nuodb://<ap-host>:<ap-port>/database?LBQuery=random(start_id(14))

As there is only one possible match, specifying the random selector (as shown) or the round_robin selector will both work. This is just one example of using AP load-balancing.

Using direct Property

It is also possible to connect directly to a TE, by-passing the APs, usually when multiple networks are involved (see When To Use Direct Connections below). To connect directly to a TE, you must specify the host and port number of the TE in the URL and adding the connection property direct=true.

The syntax for setting up a direct JDBC connection is:

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

The TE’s host and port can also be seen in the output from nuocmd show domain. In the example above, the TE is on server-0 listening on port 48007. The direct connection JDBC URL is therefore:

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

For redundancy (in case a TE has stopped or is not responding), 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-port2>,.../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.

When To Use Direct Connections

If the client is on a different network to the NuoDB processes, the address of the TE returned by the AP may not be accessible by the client. Instead a direct connection to the TE must be used, by-passing the APs. For this to work the TEs must be accessible from the client in some way. There are several possible scenarios:

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

  • 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.

  • If you are using SSH tunnelling to connect across networks, the connection URL must specify localhost and the tunnel port.

Running NuoDB in Containers

The example in this section uses the Docker containerized environment.

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 the following (on Windows replace \ by ^):

docker run -d --name test-te-1 \
   --hostname test-te-1 \
   --network nuodb-net \     (1)
   --publish 58001:48006 \   (2)
   nuodb/nuodb:latest nuodocker \
       --api-server nuoadmin1:8888 \
       start te --db-name testdb \
                --server-id nuoadmin1
1 Note the use of a dedicated internal network setup by Docker.
2 TE running on port 48006 in its container is mapped to port 58001 on your local machine.

Trying to connect in the usual way via an AP will fail because the AP will return the internal IP address of a TE which your application, running outside the container, cannot access.

Options:

  1. Build a container for your application and run it on the same network (nuodb-net).

    • The application can connect via APs in the usual way because it is on the same network segment.

    • If your end goal is to run your application under Kubernetes, you will need to containerize it eventually so this is probably your best option.

  2. Connect directly to a TE using its mapped port number.

    • 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 the router forwards your requests to one of the TEs sitting behind it.

When installing a NuoDB database under Kubernetes using our database Helm chart:

  • 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 (it is typically the only service with a public IP address).

If you run your clients inside the same Kubernetes cluster as NuoDB, direct access is not required, so 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 forwarded 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.

# Format: 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