Databases

Databases are a core part of QueryDesk — they represent the database connections that you can query and manage. On this page, we'll dive into the different database endpoints you can use to manage databases programmatically. We'll look at how to query, create, update, and delete databases.

Workflows can also be managed with Terraform using the official provider: https://registry.terraform.io/providers/devhub-tools/devhub/latest/docs/resources/querydesk_database

The database model

The database model contains all the information about your database connections, such as the connection details, credentials, and configuration settings. It also contains references to the associated credentials and permissions.

Properties

Name
id
Type
string
Description
Unique identifier for the database.
Name
api_id
Type
string
Description
External API identifier for the database.
Name
name
Type
string
Description
The display name for the database.
Name
adapter
Type
string
Description
The database adapter type (postgres, mysql, clickhouse).
Name
hostname
Type
string
Description
The hostname or IP address for the database connection.
Name
port
Type
integer
Description
The port number for the database connection.
Name
database
Type
string
Description
The database name to connect to.
Name
ssl
Type
boolean
Description
Whether SSL is enabled for the connection.
Name
restrict_access
Type
boolean
Description
Whether access to this database should be explicitly granted to users.
Name
group
Type
string
Description
The group this database belongs to, used for UI grouping.
Name
slack_channel
Type
string
Description
The Slack channel to send query request notifications to.
Name
agent_id
Type
string
Description
The agent ID for connecting to databases inside private networks.
Name
credentials
Type
array
Description
Array of database credentials with connection details.
Name
inserted_at
Type
timestamp
Description
Timestamp of when the database was created.
Name
updated_at
Type
timestamp
Description
Timestamp of when the database was last updated.

POST/api/v1/querydesk/databases

Create a database

This endpoint allows you to add a new database to your QueryDesk. To add a database, you must provide the connection details and at least one credential.

Required attributes

Name
name
Type
string
Description
The display name for the database.
Name
adapter
Type
string
Description
The database adapter type (postgres, mysql, clickhouse).
Name
hostname
Type
string
Description
The hostname or IP address for the database connection.
Name
database
Type
string
Description
The database name to connect to.
Name
credentials
Type
array
Description
Array of database credentials with connection details.

Optional attributes

Name
port
Type
integer
Description
The port number for the database connection.
Name
ssl
Type
boolean
Description
Whether SSL is enabled for the connection.
Name
restrict_access
Type
boolean
Description
Whether access to this database should be explicitly granted to users.
Name
group
Type
string
Description
The group this database belongs to, used for UI grouping.
Name
slack_channel
Type
string
Description
The Slack channel to send query request notifications to.
Name
agent_id
Type
string
Description
The agent ID for connecting to databases inside private networks.

Request

POST

/api/v1/querydesk/databases

curl https://api.devhub.sh/api/v1/querydesk/databases \
  -H "x-api-key: dh_xxx" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Production Database",
    "adapter": "postgres",
    "hostname": "db.example.com",
    "port": 5432,
    "database": "myapp_production",
    "ssl": true,
    "restrict_access": false,
    "group": "Production",
    "credentials": [
      {
        "username": "app_user",
        "password": "secure_password",
        "reviews_required": 0,
        "default_credential": true
      }
    ]
  }'

Response

{
  "id": "db_abc123def456",
  "api_id": null,
  "name": "Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": false,
  "group": "Production",
  "slack_channel": null,
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 0,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}

GET/api/v1/querydesk/databases/:id

Retrieve a database

This endpoint allows you to retrieve a database by providing its ID. Refer to the list at the top of this page to see which properties are included with database objects.

Request

GET

/api/v1/querydesk/databases/db_abc123def456

curl https://api.devhub.sh/api/v1/querydesk/databases/db_abc123def456 \
  -H "x-api-key: dh_xxx"

Response

{
  "id": "db_abc123def456",
  "api_id": null,
  "name": "Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": false,
  "group": "Production",
  "slack_channel": null,
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 0,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}

PATCH/api/v1/querydesk/databases/:id

Update a database

This endpoint allows you to perform an update on a database. You can update any of the database properties including connection details, credentials, and configuration settings.

Optional attributes

Name
name
Type
string
Description
The display name for the database.
Name
adapter
Type
string
Description
The database adapter type (postgres, mysql, clickhouse).
Name
hostname
Type
string
Description
The hostname or IP address for the database connection.
Name
port
Type
integer
Description
The port number for the database connection.
Name
database
Type
string
Description
The database name to connect to.
Name
ssl
Type
boolean
Description
Whether SSL is enabled for the connection.
Name
restrict_access
Type
boolean
Description
Whether access to this database should be explicitly granted to users.
Name
group
Type
string
Description
The group this database belongs to, used for UI grouping.
Name
slack_channel
Type
string
Description
The Slack channel to send query request notifications to.
Name
agent_id
Type
string
Description
The agent ID for connecting to databases inside private networks.
Name
credentials
Type
array
Description
Array of database credentials with connection details.

Request

PATCH

/api/v1/querydesk/databases/db_abc123def456

curl -X PATCH https://api.devhub.sh/api/v1/querydesk/databases/db_abc123def456 \
  -H "x-api-key: dh_xxx" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Updated Production Database",
    "slack_channel": "#db-alerts",
    "restrict_access": true
  }'

Response

{
  "id": "db_abc123def456",
  "api_id": null,
  "name": "Updated Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": true,
  "group": "Production",
  "slack_channel": "#db-alerts",
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 0,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T11:45:00Z"
}

DELETE/api/v1/querydesk/databases/:id

Delete a database

This endpoint allows you to delete databases from your QueryDesk. Note: This will also delete all associated credentials and permissions.

Request

DELETE

/api/v1/querydesk/databases/db_abc123def456

curl -X DELETE https://api.devhub.sh/api/v1/querydesk/databases/db_abc123def456 \
  -H "x-api-key: dh_xxx"

Response

{
  "id": "db_abc123def456",
  "api_id": null,
  "name": "Updated Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": true,
  "group": "Production",
  "slack_channel": "#db-alerts",
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 1,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T11:45:00Z"
}

PUT/api/v1/querydesk/databases/setup

Setup a database

This endpoint allows you to setup a database with a specific API ID. If a database with the given API ID already exists, it will be updated. Otherwise, a new database will be created.

Required attributes

Name
id
Type
string
Description
The API ID for the database.
Name
adapter
Type
string
Description
The database adapter type (postgres, mysql, clickhouse).
Name
hostname
Type
string
Description
The hostname or IP address for the database connection.
Name
database
Type
string
Description
The database name to connect to.
Name
username
Type
string
Description
The username for the database connection.
Name
password
Type
string
Description
The password for the database connection.

Optional attributes

Name
name
Type
string
Description
The display name for the database. Defaults to the database name if not provided.
Name
port
Type
integer
Description
The port number for the database connection.
Name
group
Type
string
Description
The group this database belongs to, used for UI grouping.
Name
agent_id
Type
string
Description
The agent ID for connecting to databases inside private networks.
Name
ssl
Type
string
Description
Set to "enabled" to enable SSL for the connection.
Name
ssl_ca_cert
Type
string
Description
Base64 encoded CA certificate for SSL connections.
Name
ssl_key
Type
string
Description
Base64 encoded client key for SSL connections.
Name
ssl_cert
Type
string
Description
Base64 encoded client certificate for SSL connections.
Name
reviews_required
Type
integer
Description
Number of reviews required for queries on this database. Defaults to 0.

Request

PUT

/api/v1/querydesk/databases/setup

curl -X PUT https://api.devhub.sh/api/v1/querydesk/databases/setup \
  -H "x-api-key: dh_xxx" \
  -H "Content-Type: application/json" \
  -d '{
    "id": "prod-db-001",
    "name": "Production Database",
    "adapter": "postgres",
    "hostname": "db.example.com",
    "port": 5432,
    "database": "myapp_production",
    "username": "app_user",
    "password": "secure_password",
    "ssl": "enabled",
    "reviews_required": 1
  }'

Response

{
  "id": "db_abc123def456",
  "api_id": "prod-db-001",
  "name": "Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": false,
  "group": null,
  "slack_channel": null,
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 1,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}

DELETE/api/v1/querydesk/databases/remove/:api_id

Remove a database by API ID

This endpoint allows you to delete a database by providing its API ID instead of the internal ID.

Request

DELETE

/api/v1/querydesk/databases/remove/prod-db-001

curl -X DELETE https://api.devhub.sh/api/v1/querydesk/databases/remove/prod-db-001 \
  -H "x-api-key: dh_xxx"

Response

{
  "id": "db_abc123def456",
  "api_id": "prod-db-001",
  "name": "Production Database",
  "adapter": "postgres",
  "hostname": "db.example.com",
  "port": 5432,
  "database": "myapp_production",
  "ssl": true,
  "restrict_access": false,
  "group": null,
  "slack_channel": null,
  "agent_id": null,
  "credentials": [
    {
      "id": "crd_xyz789",
      "username": "app_user",
      "hostname": null,
      "reviews_required": 1,
      "default_credential": true
    }
  ],
  "inserted_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}