TL/DR: if you are an Oracle Cloud Infrastructure (OCI) customer running Oracle Database or MySQL in OCI, use Database Tools (DBTools) private endpoints and connections to simplify database access in the cloud. DBTools connections support a number of use cases, for example:
- They are great for developers, consultants, analysts, support staff, and other cases where you need ad-hoc, short-lived, connections to run SQL.
- They are useful for executing SQL statements against a remote database using a well established REST API (See: ORDS REST-enabled SQL service).
- They are great for generally storing and later reading database connection configuration data in a secure way.
Here is a real-time example (recorded at ~10 frames per second) of opening a SQL worksheet from a connection and running statements against a database.
Want to learn more? Keep reading! There are lots of links below to additional resources that may be helpful or interesting.
Introduction
Connecting to a database running in a remote network can be complex. There are two general cases I would consider.
- The database client has direct access to the database server because the network is pre-configured to route packets correctly (or the server has a public IP address).
- The database client does not have direct access, in which case a temporary network (VPN), a bastion or jump host, etc. may be required.
For example, in the first scenario, application servers often need direct access the database to run SQL or PL/SQL code. Data transformation systems may need a direct connection to a database to pump large volumes of data.
In the other case, what do remote users need? Spoiler alert, it depends!
During a project, or while building a service, we endure setting up a network path to allow servers to speak directly to the database, and for good reason. Long running pools of persistent connections to a database can improve performance. The benefits of DBTools connections in these "direct access" scenarios are not as obvious but I will mention examples below and in more detail in future posts.
Should we require extra complexity for all ad-hoc or short lived remote connections? Perhaps! Although it would be nice if we had some options.
Keep in mind that even if the cloud-hosted database is addressable network-to-network, we still have to define and securely distribute connection details for database users and keep everyone up to date when things like wallets, keys, connection strings, etc. change.
This is a problem that DBTools connections solve.
OCI Database Tools Service
I believe there are many use cases where it would be nice if we could simplify access to a database. Here are some people-focused examples:
- Grant access to business users or application developers to use connections to export small data sets or run SQL statements during development.
- Grant temporary access to support engineers to production systems to debug issues.
- Permit consultants to access databases to reduce friction during a project and speed up development.
- Run SQL statements on databases that exist in a different OCI tenancy.
These are all real-world examples where DB Tools connections and private endpoints are helping customers connect to Oracle Database and MySQL in OCI. DBTools can be found in the OCI console under Developer Services -> Database Tools. Our primary resources and a shortcut to the SQL Worksheet are right there.
I'll just mention the highlights in this post but if this sounds useful for your use cases then you should definitely check out the latest documentation for the service and setup your connections.
OCI Private Endpoints
Before we look at DBTools connections background on private endpoints (PE) may be useful. Skip ahead if you already understand PE concepts.
OCI supports virtual cloud networks (VCN) with public or private subnets. OCI resources can have virtual network interface cards (VNIC) and when attached to a subnet should have an assigned IP address. Within a private subnet, these VNICs will have private IP addresses. The same goes for compute instances running database servers. If they are in a private subnet they will have a private IP address.
For example, when running Autonomous Database Serverless (ADB-S) with "private access" the ADB-S service creates a PE in a customer subnet which presents the ADB-S database as if running inside the customer VCN. Customers are then able to send TCP traffic to the database via PE.
Database Tools Private Endpoints
DBTools PEs are backed by the same OCI primitives described above and they allow database connections to be used directly from OCI interfaces such as the console, the CLI and the software development kits (SDK). There is some support for using DBTools connections in tools like SQLcl and as our developer tools become more cloud-aware, I would expect to see this support expanded.
Note: to create Database Tools private endpoints you must either have an active paid OCI subscription or promotional (trial) subscription. At the time of this writing, creating PEs is not supported with the "always-free" tier.
Here is a link to the documentation.
By attaching a connection to a PE, connections can be used to send SQL statements from the DBTools service (using our data plane) to a database that exists inside a private subnet within a customer VCN.
This makes databases with private IP addresses available to users of an OCI tenancy and access is customizable using policies. If you are using connections supported by the DBTools data plane then no special networking, bastions, or jump hosts are required to get started.
They just work.
Note: DBTools does not support TCP connections to a database from outside of OCI. From the outside world, communication with the database is done via REST calls over HTTP using the DBTools data plane.
The DBTools data plane makes the actual TCP connection to the underlying database, optionally using DBTools PEs we reviewed above.
My team wrote up some examples and provided useful architecture diagrams for reference here:
Another thing to keep in mind is that a single DBTools PE is able to service multiple connections and can be used to communicate with different databases in the same subnet or across subnets within the same VCN.
Database Tools Connections
Database connections in general are setup using specific types of information. Typically connections consist of a connection string, database user identification such as a username and password, and other details such as wallets or keys.
Connections in the OCI DBTools service are no different. They contain the information a JDBC driver would need to know in order to connect to a database.
There are a lot of bullets in our documentation for setting up a connection mainly for the sake of being thorough. Jeff Smith did a great job writing up a human readable example here:
thatjeffsmith
Connection setup is wizard-driven and if you are using an OCI database system such as ADB or MySQL Database Service (MDS), the UI tries to automate a lot of the setup by looking up information for you and allowing you to create the necessary secrets in a vault that are later referenced by a connection.
Database Tools SQL Worksheet
Once connection details are setup in OCI you have several options for actually using the connection. If the connection is meant to be used with the DBTools data plane then the most obvious path for database developers, analysts, and other roles filled by humans, is our SQL Worksheet.
Our UI teams built a nice interface on top of DBTools connections called "SQL Worksheet" that is now based on SQL Developer Web. You will find examples of our original SQL Worksheet around the web but for the latest and greatest details I suggest the features listed here:
Wrapping up
As food for thought, here are some ideas of things you can do with a DBTools connection that may or may not be using the DBTools data plane to send SQL statements to a database.
Suppose you have developed a service that requires database connection details at startup so that applications can be properly bootstrapped (or connection pools can be created) using a direct connection to a database. Each connection in DBTools is associated with a unique ID that can be referenced to get these connection details.
A fully automated approach might include setting up a Terraform configuration that creates connections and later extracts connection details from Terraform state for the DBTools connection resource.
Without Terraform, you could do the same setup with Python, Java, GoLang, etc. The OCI SDK supports a number of different programming languages that are just a front-end for the OCI REST APIs.
Here are some SDK examples for reference:
DBTools also supports generic JDBC connections where the connection details to be provided may not fit into our data plane supported world. With a generic JDBC connection you can store just about any JDBC connection details and download them where required for use later.
Any of the above could be also be configured using multiple connections (dev, test, prod, etc.) for different stages of your application. Note, the DBTools service is regional which means connections are created per region.
I am only scratching the surface here for what is possible. Hopefully these examples gives your imagination some fuel for further exploration.
Thank you for reading and following along. I look forward to sharing more information about connections and private endpoints with you in the future.
Until next time.