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_databasewhere id = 'fad76135-48bb-49c8-a274-a9db584e1dc3';
select *from digitalocean_databasewhere 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_rolefrom 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_rolefrom 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_sslfrom digitalocean_databasewhere not connection_ssl or not private_connection_ssl;
select name, connection_sslfrom digitalocean_databasewhere 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_urifrom digitalocean_database;
select name, connection_uri, private_connection_urifrom 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_databasegroup by engine, versionorder by count desc;
select engine, version, count(id)from digitalocean_databasegroup by engine, versionorder 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;
Query examples
- database_cluster_1_year_count
- database_cluster_24_hours_count
- database_cluster_30_90_days_count
- database_cluster_30_days_count
- database_cluster_90_365_days_count
- database_cluster_by_creation_month
- database_cluster_by_engine
- database_cluster_by_region
- database_cluster_count
- database_cluster_engine_version
- database_cluster_firewall_enabled
- database_cluster_firewall_enabled_count
- database_cluster_firewall_rules
- database_cluster_input
- database_cluster_maintenance_window
- database_cluster_node_count
- database_cluster_private_connection
- database_cluster_ssl_enabled
- database_cluster_ssl_enabled_count
- database_cluster_tags
- database_clusters_for_kubernetes_cluster
- database_clusters_for_network_vpc
- droplet_droplets_for_database_cluster
- kubernetes_clusters_for_database_cluster
- network_vpc_association
- network_vpcs_for_database_cluster
Schema for digitalocean_database
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
akas | jsonb | Array of globally unique identifier strings (also known as) for the resource. | |
connection_database | text | The name of the default database. | |
connection_host | text | A public FQDN pointing to the database cluster's current primary node. | |
connection_password | text | The randomly generated password for the default user. | |
connection_port | bigint | The port on which the database cluster is listening. | |
connection_ssl | boolean | A boolean value indicating if the connection should be made over SSL. | |
connection_uri | text | A 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_user | text | The default user for the database. | |
created_at | timestamp with time zone | Time when the database was created. | |
db_names | jsonb | An array of strings containing the names of databases created in the database cluster. | |
engine | text | A slug representing the database engine used for the cluster. The possible values are: "pg" for PostgreSQL, "mysql" for MySQL, and "redis" for Redis. | |
firewall_rules | jsonb | A list of rules describing the inbound source to a database. | |
id | text | = | A unique ID that can be used to identify and reference a database cluster. |
maintenance_window_day | text | The day of the week on which to apply maintenance updates (e.g. "tuesday"). | |
maintenance_window_description | jsonb | A list of strings, each containing information about a pending maintenance update. | |
maintenance_window_hour | text | The hour in UTC at which maintenance updates will be applied in 24 hour format (e.g. "16:00:00"). | |
maintenance_window_pending | boolean | A boolean value indicating whether any maintenance is scheduled to be performed in the next window. | |
name | text | A unique, human-readable name referring to a database cluster. | |
num_nodes | bigint | The number of nodes in the database cluster. | |
private_connection_database | text | The name of the default database. | |
private_connection_host | text | The private FQDN pointing to the database cluster's current primary node. | |
private_connection_password | text | The randomly generated password for the default user. | |
private_connection_port | bigint | The port on which the database cluster is listening. | |
private_connection_ssl | boolean | A boolean value indicating if the connection should be made over SSL. | |
private_connection_uri | text | A 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_user | text | The default user for the database. | |
private_network_uuid | text | A string specifying the UUID of the VPC to which the database cluster is assigned. | |
region_slug | text | The unique slug identifier for the region the database is deployed in. | |
size_slug | text | The slug identifier representing the size of the nodes in the database cluster. | |
status | text | A string representing the current status of the database cluster. Possible values include creating, online, resizing, and migrating. | |
tags | jsonb | A map of tags for the resource. | |
tags_src | jsonb | An array of tags that have been applied to the database cluster. | |
title | text | Title of the resource. | |
urn | text | The uniform resource name (URN) for the database. | |
users | jsonb | An array containing objects describing the database's users. | |
version | text | A string representing the version of the database engine in use for the cluster. |