Application Architecture
Visulate for Oracle creates 3 docker containers to deliver a browser UI and REST endpoints for one or more Oracle databases. The UI Container exposes an Angular UI which makes API calls to REST endpoints exposed by the API Server Container and the SQL Query Engine Container.
The API Server is an Express JS instance. It connects to one or more registered databases using node-oracledb. Database connections are registered by adding an entry to a configuration file that the API Server reads during initialization. It creates a connection pool for each entry in the config file.
API Server
The API Server has SQL for each database object that queries the appropriate dictionary views (e.g. DBA_TABLES and other views for table objects or DBA_SOURCE for packages). It also queries database’s dependency model to identify dependencies to and from the object (e.g a view “uses” the tables it is based on and is “used by” a procedure that selects from it).
The API Server exposes REST endpoints that allow users initiate queries for a given database + object description. Most API calls include a query against DBA_OBJECTS. For example a call to the /api/{db}/{owner}/{type}/{name}/{status} endpoint returns the result of running the following query in the specified database:
select object_id, object_name, object_type, owner
from dba_objects
where owner = :owner
and object_type like :type
and object_name like :name ESCAPE \_
and status like :status
order by object_name
Results are returned in JSON format. An Open API Specification for the API Server is available in Github.
AI Integration
The API Server can be configured to connect to a Google Gemini LLM. When this feature is enabled Visulate presents a chatbot that can be used for database analysis or code generation. Visulate facilitates this by assembling metadata for use as grounding information by the LLM. This metadata includes details of the current database object and all of its related objects.
UI
An Angular UI makes API calls to the API Server and displays the result.
SQL Query Engine
The SQL Query Engine allows users to run ad-hoc queries and download the result as CSV or JSON. It is a WSGI Flask instance which uses python-oracledb to make database connections. Access to this feature is controlled by a configuration file called endpoints.json. This allows a DBA to limit the list of database environments that allow SQL access. For example, they may wish to allow access for development databases but not production.
The SQL Query Engine exposes a REST API to generate CSV or JSON from user supplied SQL. An HTTP POST request is made to the SQL Query endpoint passing the database credentials as a basic auth header and a SQL query in the request body. A typical request might look like this:
echo -n hr:hr-password| base64
aHI6aHItcGFzc3dvcmQ=
curl \
-L 'https://demo115.visulate.net/sql/vis115' \
-H 'Authorization: Basic aHI6aHItcGFzc3dvcmQ=' \
-H 'Content-Type: application/json' \
-H 'Accept: text/csv' \
-d @- << EOF
{ "sql": "select * from EMPLOYEES where rownum < :maxrows",
"binds": {"maxrows": 10 },
"options": {"download_lobs": "N", "csv_header": "N", "cx_oracle_object": null}
}
EOF
The endpoints.json file populates a Python dictionary of endpoint:connect string pairs. The Query Engine accepts the endpoint as a path parameter (vis115 in the example above) and uses the dictionary to lookup its connect string. It combines this with the username and password passed in the header to establish a database connection. The SQL Query Engine does not use connection pooling. Each request makes a new database connection, executes the query, streams the result and then closes the connection.
Deployment
Visulate for Oracle provides 2 deployment options. A Compute Engine deployment configures all of the application components on a single Google Compute Engine (GCE) virtual machine. A Kubernetes deployment configures a Google Kubernetes Engine (GKE) application with separate services and deployments for the API, UI and Query Engine components.
Compute Engine deployments are easier to setup and manage. Kubernetes deployments provide more control over individual resources. We recommend most users start with a Compute Engine deployment.
Compute Engine Architecture
Web users connect to the application via an Nginx reverse proxy container listening on port 80. The connection can be via a load balancer or direct if the VM has been configured with a public IP address. Configuring a load balancer is the recommended approach. The Visulate VM is provisioned with a private IP address and no public IP address. The private IP is added to a Network Endpoint Group (NEG) for inclusion in a load balancer backend configuration. The load balanced backend service uses Identity Aware Proxy (IAP) to control access to the application.
Database registration is performed by editing files in an OS directory that has been volume mounted to the API and SQL Query containers.
Kubernetes Architecture
Web users connect to the application via a ClusterIP resource. This exposes an nginx deployment which proxies requests to the UI, API or SQL Service as required. The proxy service exposes an Network Endpoint Group (NEG) for inclusion in a load balancer backend configuration. The load balanced backend service uses Identity Aware Proxy (IAP) to control access to the application.
Database registration is performed using a Secret. The Secret manifest delivers the database.js configuration file that the Express server reads during initialization as part of the API Server deployment. A similar mechanism is used to deliver the SQL Query Engine’s endpoints.json file.
The UI and API Server deployment manifests include sidecar containers to support Google Cloud Platform (GCP) integration. The UI and API Server manifests use sidecar containers to make log file contents available to Cloud Logging. The API Server manifest creates 2 additional sidecars to support GCP Marketplace. The SQL Query Engine sends its logs to stderr and stdout instead of writing to a file. This avoids the need for a sidecar to populate Cloud Logging