turbot/digitalocean
steampipe plugin install digitalocean

Table: digitalocean_database - Query DigitalOcean Databases using SQL

DigitalOcean Databases is a service offered by DigitalOcean that provides fully managed databases. It provides a scalable, reliable, and secure environment for developers to manage and scale their databases. The service supports multiple database engines, including PostgreSQL, MySQL, and Redis.

Table Usage Guide

The digitalocean_database table provides insights into the databases in DigitalOcean. As a database administrator or developer, you can explore specific details about each database, including its configurations, associated users, regions, and more. Utilize this table to manage and monitor your databases effectively, ensuring optimal performance and security.

Examples

List all databases

Explore the full range of databases within your DigitalOcean environment. This can aid in assessing overall usage and identifying any databases that may need attention or adjustment for optimal performance.

select
*
from
digitalocean_database;
select
*
from
digitalocean_database;

Get database by ID

Explore the specific details of a particular DigitalOcean database by identifying it through its unique ID. This can be useful for understanding the properties and configurations of a specific database within your DigitalOcean environment.

select
*
from
digitalocean_database
where
id = 'fad76135-48bb-49c8-a274-a9db584e1dc3';
select
*
from
digitalocean_database
where
id = 'fad76135-48bb-49c8-a274-a9db584e1dc3';

All database users by instance

Explore which users are associated with each instance in your DigitalOcean database. This is useful for understanding user roles and responsibilities within your database management system.

select
db.name as db_name,
u ->> 'name' as user_name,
u ->> 'role' as user_role
from
digitalocean_database as db,
jsonb_array_elements(users) as u;
select
db.name as db_name,
json_extract(u.value, '$.name') as user_name,
json_extract(u.value, '$.role') as user_role
from
digitalocean_database as db,
json_each(db.users) as u;

Databases not using SSL

Explore which DigitalOcean databases are not using SSL, helping to identify potential security vulnerabilities in your database connections. This can be beneficial in enhancing your data security measures.

select
name,
connection_ssl
from
digitalocean_database
where
not connection_ssl
or not private_connection_ssl;
select
name,
connection_ssl
from
digitalocean_database
where
not connection_ssl
or not private_connection_ssl;

Get database connection

Explore which DigitalOcean databases you are connected to, by obtaining the connection URIs. This can be beneficial in managing your connections and ensuring the security of your private connections. WARNING: DigitalOcean returns the database password as metadata. Use with care!

select
name,
connection_uri,
private_connection_uri
from
digitalocean_database;
select
name,
connection_uri,
private_connection_uri
from
digitalocean_database;

Databases by engine version

Explore the distribution of your DigitalOcean databases by identifying the number of databases operating on different engine versions. This can help you manage and plan upgrades, ensuring your systems remain up-to-date and secure.

select
engine,
version,
count(id)
from
digitalocean_database
group by
engine,
version
order by
count desc;
select
engine,
version,
count(id)
from
digitalocean_database
group by
engine,
version
order by
count(id) desc;

Get database firewall trusted sources

Explore which trusted sources are allowed through your database firewall. This is useful for reviewing your security settings and ensuring only authorized sources have access.

select
name as "Name",
firewall ->> 'type' as "Firewall Source",
firewall ->> 'value' as "Source ID"
from
digitalocean_database,
jsonb_array_elements(firewall_rules) as firewall;
select
name as "Name",
json_extract(firewall.value, '$.type') as "Firewall Source",
json_extract(firewall.value, '$.value') as "Source ID"
from
digitalocean_database,
json_each(firewall_rules) as firewall;

Schema for digitalocean_database

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
connection_databasetextThe name of the default database.
connection_hosttextA public FQDN pointing to the database cluster's current primary node.
connection_passwordtextThe randomly generated password for the default user.
connection_portbigintThe port on which the database cluster is listening.
connection_sslbooleanA boolean value indicating if the connection should be made over SSL.
connection_uritextA connection string in the format accepted by the psql command. This is provided as a convenience and should be able to be constructed by the other attributes.
connection_usertextThe default user for the database.
created_attimestamp with time zoneTime when the database was created.
db_namesjsonbAn array of strings containing the names of databases created in the database cluster.
enginetextA slug representing the database engine used for the cluster. The possible values are: "pg" for PostgreSQL, "mysql" for MySQL, and "redis" for Redis.
firewall_rulesjsonbA list of rules describing the inbound source to a database.
idtext=A unique ID that can be used to identify and reference a database cluster.
maintenance_window_daytextThe day of the week on which to apply maintenance updates (e.g. "tuesday").
maintenance_window_descriptionjsonbA list of strings, each containing information about a pending maintenance update.
maintenance_window_hourtextThe hour in UTC at which maintenance updates will be applied in 24 hour format (e.g. "16:00:00").
maintenance_window_pendingbooleanA boolean value indicating whether any maintenance is scheduled to be performed in the next window.
nametextA unique, human-readable name referring to a database cluster.
num_nodesbigintThe number of nodes in the database cluster.
private_connection_databasetextThe name of the default database.
private_connection_hosttextThe private FQDN pointing to the database cluster's current primary node.
private_connection_passwordtextThe randomly generated password for the default user.
private_connection_portbigintThe port on which the database cluster is listening.
private_connection_sslbooleanA boolean value indicating if the connection should be made over SSL.
private_connection_uritextA connection string in the format accepted by the psql command. This is provided as a convenience and should be able to be constructed by the other attributes.
private_connection_usertextThe default user for the database.
private_network_uuidtextA string specifying the UUID of the VPC to which the database cluster is assigned.
region_slugtextThe unique slug identifier for the region the database is deployed in.
size_slugtextThe slug identifier representing the size of the nodes in the database cluster.
statustextA string representing the current status of the database cluster. Possible values include creating, online, resizing, and migrating.
tagsjsonbA map of tags for the resource.
tags_srcjsonbAn array of tags that have been applied to the database cluster.
titletextTitle of the resource.
urntextThe uniform resource name (URN) for the database.
usersjsonbAn array containing objects describing the database's users.
versiontextA string representing the version of the database engine in use for the cluster.